【十四】Springboot JdbcTemplate连数据库(未完待续，要和jdbc、mybats、hibernate一步一步的比较)

简述：

跟JDBC直接裸奔比起来：

1.不用自己获取数据库连接connection，不用自己管理数据库连接

2.可用直接在application.yml中配置datasource，配置数据库连接池。

3.自己手写PO

4.还是没得ORM映射。但是可用在PO中实现RowMapper或者RowCallbackHandler接口，一个PO写一次映射关系。也可以在jdbcTemplate调用的时候针对不同的查询实现不同的RowMapper匿名内部类。或者在jdbcTemplate调用的时候用BeanPropertyRowMapper。

代码：

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sid</groupId>
    <artifactId>test-aop</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
        <relativePath/>
    </parent>
    
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <!--<version>1.5.8.RELEASE</version>-->
        </dependency>
        

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <!--<version>1.5.8.RELEASE</version>-->
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <!--<version>1.5.8.RELEASE</version>-->
        </dependency>

        <!-- alibaba的druid数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

        <!-- alibaba的druid数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
        </dependency>
    </dependencies>

</project>

application.yml

server:
  port: 8080
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/sid?useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: root
    driver-class-name: com.mysql.jdbc.Driver
#    type: com.alibaba.druid.pool.DruidDataSource
#    druid:
#      initialSize: 1
#      maxActive: 20
#      minIdle: 1
#      maxWait: 60000
#      minEvictableIdleTimeMillis: 300000
#      validationQuery: SELECT 1 FROM DUAL
#      testWhileIdle: true
#      testOnBorrow: false
#      testOnReturn: false

PO:

package com.sid.model;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @program: test-aop
 * @description:
 * @author: Sid
 * @date: 2019-03-26 14:40
 * @since: 1.0
 **/
public class UserJdbcTemplate {

    private Integer id;
    private String mobilePhone;
    private Integer age;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getMobilePhone() {
        return mobilePhone;
    }

    public void setMobilePhone(String mobilePhone) {
        this.mobilePhone = mobilePhone;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }


}

dao:

package com.sid.mapper;

import com.sid.model.UserJdbcTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;


import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @program: test-aop
 * @description:
 * @author: Sid
 * @date: 2019-03-26 14:43
 * @since: 1.0
 **/
@Repository
public class JdbcTemplateDao {
    @Resource
    private JdbcTemplate jdbcTemplate;

    public  void add(UserJdbcTemplate g)  {
        String sql = "INSERT INTO user(id, mobile_phone, age) values("+"?,?,?)";
        int rows= jdbcTemplate.update(sql,g.getId(),g.getMobilePhone(),g.getAge());
    }

    public  void update(UserJdbcTemplate g)  {
        String sql = "UPDATE user set  mobile_phone=?, age=? where id=?";
        int rows= jdbcTemplate.update(sql,g.getMobilePhone(),g.getAge(),g.getId());
    }

    public  void delete(int id) {
        String sql = "delete from user where id=?";
        int rows= jdbcTemplate.update(sql,id);
    }

    public  List<UserJdbcTemplate> query() {
        String sql = "SELECT id,mobile_phone, age FROM user";
        List<UserJdbcTemplate> list= jdbcTemplate.query(sql,new BeanPropertyRowMapper(UserJdbcTemplate.class));
        return list;
    }

    public  UserJdbcTemplate queryForObject(int id) {
        String sql = "select * from  user where id = ? ";
        UserJdbcTemplate user= jdbcTemplate.queryForObject(sql, new RowMapper<UserJdbcTemplate>() {
            @Override
            public UserJdbcTemplate mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserJdbcTemplate user = new UserJdbcTemplate();
                user.setId(rs.getInt("id"));
                user.setAge(rs.getInt("age"));
                user.setMobilePhone(rs.getString("mobile_phone"));
                return user;
            }
        }, new Object[]{id});
        return user;
    }

    public  List<Integer> queryForList(int id) {
        String sql = "select age from  user where id = ? ";
        List<Integer> list= jdbcTemplate.queryForList(sql,new Object[]{id},Integer.class);
        return list;
    }

    /**
     * 是的，空欢喜一伙，这样会报错，这里传入的类只能是Integer.class String.class这样的，不能是自己定义的一个bean
     * 没有ORM映射！还是要自己搞！
    */
    public  List<UserJdbcTemplate> queryForListError(int id) {
        String sql = "select age from  user where id = ? ";
        List<UserJdbcTemplate> list= jdbcTemplate.queryForList(sql,new Object[]{id},UserJdbcTemplate.class);
        return list;
    }

    public  UserJdbcTemplate queryRowCallbackHandler(int id) {
        final UserJdbcTemplate user = new UserJdbcTemplate();
        String sql = "select * from  user where id = ? ";
        jdbcTemplate.query(sql,new Object[]{id}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                user.setId(rs.getInt("id"));
                user.setAge(rs.getInt("age"));
                user.setMobilePhone(rs.getString("mobile_phone"));
            }
        });
        return user;
    }
}