Spring JDBC

Spring JDBC

Spring JDBC是对原生JDBC的轻量级封装，在Spring Boot中使用Spring JDBC需要遵循以下步骤：

1、添加配置文件application.properties，在配置文件中定义数据源信息和连接池信息

#数据库连接信息
spring.datasource.name=sampleDs
spring.datasource.url=jdbc:mysql://localhost:3306/sampledb
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#数据库连接池配置
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=10
spring.datasource.tomcat.min-idle=8
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.validation-query=select 1

2、在DAO类中使用@Autowired注入JdbcTemplate对象或者NamedParameterTemplate对象：

@Repository
public class UserDao {
    //使用SpringBoot自动管理的Spring jdbc
    @Autowired
    private JdbcTemplate jdbcTemplate;
    //使用命名参数绑定的Spring jdbc
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    ...

}

下面分别介绍Spring JDBC提供的这两个类。

1、JdbcTemplate

    1.1、插入/修改/删除

    插入/修改/删除主要通过JdbcTemplate的一系列update方法实现：

@Repository
public class UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final String INSERTNEWUSER = "insert into t_user (user_name,password) " +
            "values(?,?) ";
    private static final String MODIFYPASSWORD= "UPDATE t_user SET password = ? " +
            "WHERE user_name = ?";
    
    @Transactional
    public void insert(User user){
        //方法1：使用update(String sql)
        jdbcTemplate.update("DELETE * FROM t_user WHERE user_name = 'admin'");
        //方法2：update(String sql,Object...args)
        jdbcTemplate.update(INSERTNEWUSER,user.getUserName(),"xxxx");
    }
}

    这两个方法都返回一个int，表示受影响的行数。

    还有一个特殊的重写可以获取数据库的表自增主键值：

public User insertWithKeyBack(final User user){
    //创建一个keyHolder以存储生产的自增长主键
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(INSERTNEWUSER);
            ps.setObject(1,user.getUserName());
            ps.setObject(2,user.getPassword());
            return ps;
        }
    },keyHolder);
    //使用KeyHolder的getKey方法获取数据库创建的自增长主键
    user.setUserId(keyHolder.getKey().intValue());
    return user;
}

 KeyHolder的三个方法：

1. int getKey()获取返回的单个数字
2. Map<String,Object> getKeys()获取返回的单个主键，key为列名，value为列值
3. List<Map<String,Object> getKeyList()获取返回的多个主键，存在多个Map中，一个Map对应一个主键。

    1.2、批量更改

如果需要一次性插入大量的数据，直接多次使用update()方法效果十分不理想，可以考虑使用JdbcTemplate的批量更改方法：

    

/**
 * 批量修改
 * @param users
 */
public void batchInsert(final List<User> users){
    final String sql = "INSERT INTO t_user(user_name,password) values(?,?) ";

    //使用匿名内部类,batchUpdate方法实现批量插入
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        //指定插入的数据
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
            User user = users.get(i);
            preparedStatement.setObject(1,user.getUserName());
            preparedStatement.setObject(2,user.getPassword());
        }
        //指定一批插入的记录数
        @Override
        public int getBatchSize() {
            return 0;
        }
    });
}

    1.3、查询

        1.3.1、单值数据查询

        JdbcTemplate有一系列的queryForXX方法用于单值数据查询：

private static final String MATCH_COUNT = "SELECT count(*) from t_user " +
        "where user_name = ? and password = ? ";
/**
 * 查询符合的项数
 * @param user
 * @return
 */
public int getMatchCount(User user){
    //查询方法，第一个参数为查询字符串，第二个参数为返回的类型（仅限基本类型和String），后面的参数为输入的参数
    return jdbcTemplate.queryForObject(MATCH_COUNT,Integer.TYPE,user.getUserName(),user.getPassword());
}

        1.3.2、一般查询

/**
 * 根据用户名查询用户
 * 使用query(String sql,RowCallbackHandler r)
 * @param user
 */
public List<User> getUsers(final User user){
    final String sql = "SELECT * FROM t_user(user_name,password) values(?,?)";
    
    //使用RowCallbackHandler
    final List<User> users = new ArrayList<>();
    jdbcTemplate.query(sql, new Object[]{user.getUserName(),user.getPassword()},new RowCallbackHandler() {
        @Override
        public void processRow(ResultSet resultSet) throws SQLException {
            User user = new User();
            user.setUserName(resultSet.getString("userName"));
            user.setPassword(resultSet.getString("password"));
            users.add(user);
        }
    });
    return users;
}

/**
 * 根据用户名查询用户
 * 使用query(String sql,RowMapper r)
 * @param user
 */
public List<User> getUsers2(final User user){
    final String sql = "SELECT * FROM t_user(user_name,password) values(?,?)";
    //使用RowMapper
    return jdbcTemplate.query(sql, new Object[]{user.getUserName(), user.getPassword()}, new RowMapper<User>() {
        @Override
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            User user = new User();
            user.setUserName(resultSet.getString("userName"));
            user.setPassword(resultSet.getString("password"));
            return user;
        }
    });
}

        RowMapper和RowCallbackHandler的区别：

1. 查询多行数据时，RowMapper可以直接返回List，RowCallBackHandler需要手动处理；
2. 在处理大结果集时，RowMapper采用批量化数据处理技术，会一次性将数据读取到内存中，而RowCallBackHandler和原生resultSet一样采用流化处理策略，一边获取数据一边处理，处理完即丢弃。在大结果集查询时，推荐使用RowMapper，效率更高。

2、NamedParameterTemplate

/**
 * 使用NamedParameterJdbcTemplate插入用户
 * @param user
 */
public void insertUser(User user){
    final String sql = "INSERT into t_user(user_name,password) " +
            "values (:userName,:password)";
    SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(user);
    namedParameterJdbcTemplate.update(sql,sqlParameterSource);
}

注意事项

Spring中，每次HTTP请求都会绑定一个线程，该请求的所有操作都在这个线程中完成。固如果希望在DAO中穿插着使用Spring JDBC和原生JDBC，在获取Connection时，不能直接从DataSource获取，因为直接从DataSource中获取取不到和当前线程绑定的Connection。而应该使用DataSourceUtils获取：

/**
 * 线程安全地使用原生JDBC
 */
public Connection getNativeConnection(){
    return DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
}