springboot整合 数据框架springjdbc、mybatis、jpa and druid 连接池 and http请求 httpclient、resttemplate

代码地址：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]，相当于一层抽象。