总目录
- 1.数据库连接
- 1. 原生jdbc
- 2. spring封装的jdbc
- 3. orm框架:
- 3.1 mybatis
- 3.2 spring data jpa
- 3.3 hibernate
- 4.连接池:
- 4.1 c3p0连接池
- 4.2 druid连接池 [性能优于c3p0]
- 4.3 hikari连接池 [性能高于druid,高版本的springboot默认用这个连接池]
- 2. http请求
- 1. jdk原生的client
- 2. httpclient
- 3. resttemplate [springboot内置]
- 4. feign
代码地址:https://gitee.com/biturd/sb-summary/tree/master
1.数据库连接
1. 原生jdbc
Connection conn = null;
Statement st = null;
ResultSet rs = null;
sql = null;
try {
sql = "";
conn = JDBCUtils.getConnection();
// st = conn.createStatement();
ps = conn.prepareStatement();
rs = st.executeQuery("");
while (rs.next()){
System.out.println(rs.getObject(1)+"\t"
+rs.getObject("id")); // 建议用列名
}
}finally {
JDBCUtils.free(rs,st,conn);
}
2. spring封装的jdbc
@Repository
public class OrderDaoImpl implements OrderDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int insert(Order order) {
int update = jdbcTemplate.update("insert into t_order (id,money,user_id,receiver_address,receiver_name," +
"receiver_phone) values(?,?,?,?,?,?)",
order.getId(), order.getMoney(),
order.getUserId(), order.getReceiverAddress(),
order.getReceiverName(), order.getReceiverPhone());
return update;
}
@Override
public int update(Order order) {
int update = jdbcTemplate.update("update t_order set money = ?,user_id = ?,receiver_address=?," +
"receiver_name=?, receiver_phone=?" +
"where id = ?", order.getMoney(),
order.getUserId(), order.getReceiverAddress(),
order.getReceiverName(), order.getReceiverPhone(), order.getId());
return update;
}
@Override
public List<Order> selectAll() {
String sql = "select * from t_order";
List<Order> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Order.class));
return list;
}
@Override
public Order selectById(Long id) {
String sql = "select * from t_order where id = ?";
Order order = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Order.class), id);
return order;
}
}
3. orm框架:
3.1 mybatis
maven添加:
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
spring yml配置文件添加:
mybatis:
type-aliases-package: com.study.sbsummary.model
configuration:
cache-enabled: true
mapper-locations: classpath*:com/study/sbsummary/dao/*.xml
dao的xml示例: [需要启动类上增加扫描的包] 【@MapperScan(“com.study.sbsummary.dao”)】
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.sbsummary.dao.UserDao">
<resultMap id="BaseResultMap" type="com.study.sbsummary.model.User">
<result column="id" jdbcType="BIGINT" property="id" />
<result column="username" jdbcType="VARCHAR" property="username" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
</resultMap>
<insert id="insert" parameterType="com.study.sbsummary.model.User">
insert into t_user (id, username, `password`,
create_time)
values (#{id,jdbcType=BIGINT}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{createTime,jdbcType=TIMESTAMP})
</insert>
<delete id="delete" parameterType="long">
DELETE FROM t_user WHERE id = #{id}
</delete>
<update id="update" parameterType="user">
UPDATE t_user
SET username = #{username},password = #{password},
createTime = #{createTime}
WHERE id = #{id}
</update>
<select id="selectById" parameterType="int" resultType="user">
SELECT * FROM t_user WHERE id = #{id}
</select>
<select id="selectAll" resultType="com.study.sbsummary.model.User">
select * from t_user
</select>
</mapper>
3.2 spring data jpa
maven需要添加:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>2.4.2</version>
</dependency>
spring yml配置文件添加:
spring:
jpa:
database: MYSQL
hibernate:
naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy
ddl-auto: none
properties:
hibernate:
dialect: org.hibernate.dialect.MySQL5InnoDBDialect
show-sql: true
与mybatis不同的,model需要添加相关注解:
@Entity
@Table(name = "t_goods") // 指定关联的数据库的表名
@Data
public class Goods {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id; // 主键ID
private String name; // 商品名
private double price; // 商品价格
private int count; // 库存
}
dao层示例:
public interface GoodsDao extends JpaRepository<Goods,Long> {
@Query("delete from Goods g where g.id=:id")
void delById(Long id);
List<Goods> findAll();
@Query("select g from Goods g where g.id = :id")
Goods selectById(Long id);
}
3.3 hibernate
spring data jpa跟hibernate用法类似,具体以后再补充
4.连接池:
4.1 c3p0连接池
以后有时间再补充
4.2 druid连接池 [性能优于c3p0]
maven添加:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>0.2.9</version>
</dependency>
spring yml配置文件:
spring:
datasource:
// 数据源相关
password: root
username: root
url: jdbc:mysql://localhost:3306/nice?useUnicode=true&characterEncoding=gbk
platform: mysql
driver-class-name: com.mysql.cj.jdbc.Driver
// 连接池相关
type: com.alibaba.druid.pool.DruidDataSource
# 下面为连接池的补充设置,应用到上面所有数据源中
# 初始化大小,最小,最大
initialSize: 1
minIdle: 3
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 30000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#useGlobalDataSourceStat: true
datasource 的spring config bean。
@EnableTransactionManagement
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
@Data
public class DataSourceConfig {
private String url;
private String username;
private String password;
private String driverClassName;
private int initialSize;
private int minIdle;
private int maxActive;
private int maxWait;
private int timeBetweenEvictionRunsMillis;
private int minEvictableIdleTimeMillis;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private boolean poolPreparedStatements;
private int maxPoolPreparedStatementPerConnectionSize;
private String filters;
private String connectionProperties;
// 解决 spring.datasource.filters=stat,wall,log4j 无法正常注册
@Bean
@Primary // 在同样的DataSource中,首先使用被标注的DataSource
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
// configuration
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
System.err.println("druid configuration initialization filter: " + e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
}
4.3 hikari连接池 [性能高于druid,高版本的springboot默认用这个连接池]
后续补充
2. http请求
1. jdk原生的client
/**
* JDK网络类Java.net.HttpURLConnection
*
* @param pathUrl
*/
public static void doPostOrGet(String pathUrl, String data) {
OutputStreamWriter out = null;
BufferedReader br = null;
String result = "";
try {
URL url = new URL(pathUrl);
//打开和url之间的连接
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
//请求方式
conn.setRequestMethod("POST");
//conn.setRequestMethod("GET");
//设置通用的请求属性
conn.setRequestProperty("accept", "*/*");
conn.setRequestProperty("connection", "Keep-Alive");
conn.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)");
conn.setRequestProperty("Content-Type", "application/json;charset=utf-8");
//DoOutput设置是否向httpUrlConnection输出,DoInput设置是否从httpUrlConnection读入,此外发送post请求必须设置这两个
conn.setDoOutput(true);
conn.setDoInput(true);
/**
* 下面的三句代码,就是调用第三方http接口
*/
//获取URLConnection对象对应的输出流
out = new OutputStreamWriter(conn.getOutputStream(), "UTF-8");
//发送请求参数即数据
out.write(data);
//flush输出流的缓冲
out.flush();
/**
* 下面的代码相当于,获取调用第三方http接口后返回的结果
*/
//获取URLConnection对象对应的输入流
InputStream is = conn.getInputStream();
//构造一个字符流缓存
br = new BufferedReader(new InputStreamReader(is));
String str = "";
while ((str = br.readLine()) != null) {
result += str;
}
System.out.println(result);
//关闭流
is.close();
//断开连接,disconnect是在底层tcp socket链接空闲时才切断,如果正在被其他线程使用就不切断。
conn.disconnect();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.close();
}
if (br != null) {
br.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
2. httpclient
maven依赖:
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.2</version>
</dependency>
/**
* apache httpclient
*
* @param url
* @param json
* @return
*/
public static String doPostOrGet2(String url, JSONObject json) {
CloseableHttpClient httpClient = null;
try {
if (httpClient == null) {
httpClient = HttpClientBuilder.create().build();
}
HttpPost post = new HttpPost(url);
//api_gateway_auth_token自定义header头,用于token验证使用
post.addHeader("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.81 Safari/537.36");
StringEntity s = new StringEntity(json.toString());
s.setContentEncoding("UTF-8");
//发送json数据需要设置contentType
s.setContentType("application/x-www-form-urlencoded");
//设置请求参数
post.setEntity(s);
HttpResponse response = httpClient.execute(post);
if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
//返回json格式
String res = EntityUtils.toString(response.getEntity());
return res;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (httpClient != null) {
try {
httpClient.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
3. resttemplate [springboot内置]
/**
* RestTemplate
*
* @return
*/
public static Map doPostOrGet3(String url, MultiValueMap<String, String> params) {
RestTemplate client = new RestTemplate();
HttpHeaders headers = new HttpHeaders();
HttpMethod method = HttpMethod.POST;
// 以表单的方式提交
headers.setContentType(MediaType.APPLICATION_FORM_URLENCODED);
//将请求头部和参数合成一个请求
HttpEntity<MultiValueMap<String, String>> requestEntity = new HttpEntity<>(params, headers);
//执行HTTP请求,将返回的结构使用ResultVO类格式化
ResponseEntity<Map> response = client.exchange(url, method, requestEntity, Map.class);
return response.getBody();
}
4. feign
后续补充:
在往常的 HTTP 调用中,一直都是使用的官方提供的 RestTemplate 来进行远程调用,该调用方式将组装代码冗余到正常业务代码中,不够优雅,feign是声明式、模板化的HTTP客户端。使用feign非常简单,创建一个接口,并在接口上添加一些注解
feign底层可以自己选择实现方式[httpclient, okhttpclient, resttemplate],相当于一层抽象。