使用spring JdbcTemplate持久化数据

在原始的jdbc开发中，如果想对数据库进行操作，会写大量的样板代码，比如获取连接、释放资源等，使用spring的JdbcTemplate将可以改善这种情况，让程序员只关注具体的业务，对比一下使用传统jdbc和JdbcTemplate的代码量：

传统jdbc:

package com.llg.dao.test;

import com.llg.bean.Student;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestJdbc {

    public void addStudent(Student student){
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection("jdbc:mysql:///test", "root", "admin");
            String sql = "insert into t_student values(null,?,?)";
            statement = conn.prepareStatement(sql);
            statement.setString(1,student.getName());
            statement.setInt(2,student.getAge());
            statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JdbcTemplate:

package com.llg.dao.impl;

import com.llg.bean.Student;
import com.llg.dao.StudentDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.stereotype.Repository;


@Repository
public class StudentDaoImpl implements StudentDao {
    private static final String ADD_STUDENT = "insert into t_student values(null,?,?)";
  
    @Autowired
    private JdbcOperations jdbcOperations;

    @Override
    public void addStudent(Student student) {
        jdbcOperations.update(ADD_STUDENT, student.getName(), student.getAge());
    }
}

如何使用spring JdbcTemplate

maven依赖：

 <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.1.5.RELEASE</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.1.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.1.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.1.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.10</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.1.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.15</version>
    </dependency>
  </dependencies>

第一步：配置数据源

<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="url" value="jdbc:mysql:///test"/>
    <property name="username" value="root"/>
    <property name="password" value="admin"/>
</bean>

我使用的阿里的Druid连接池，其他方案同理

第二步：配置JdbcTemplate,并注入数据源

<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

就这么简单，使用如下：

package com.llg.dao.impl;

import com.llg.bean.Student;
import com.llg.dao.StudentDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

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

@Repository
public class StudentDaoImpl implements StudentDao {
    private static final String ADD_STUDENT = "insert into t_student values(null,?,?)";
    private static final String DELETE_STUDENT = "delete from t_student where id = ?";
    private static final String UPDATE_STUDENT = "update t_student set name = ? , age = ? where id = ?";
    private static final String GET_BY_ID = "select * from t_student where id = ?";


    //注入JdbcTemplate
    @Autowired
    private JdbcOperations jdbcOperations;

    //增
    @Override
    public void addStudent(Student student) {
        jdbcOperations.update(ADD_STUDENT, student.getName(), student.getAge());
    }

    //查
    @Override
    public Student getStudentById(Integer id) {
        Student student = jdbcOperations.queryForObject(GET_BY_ID, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student s = new Student();
                s.setId(resultSet.getInt("id"));
                s.setName(resultSet.getString("name"));
                s.setAge(resultSet.getInt("age"));
                return s;
            }
        },id);
        return student;
    }

    //改
    @Override
    public void updateStudent(Student student) {
        jdbcOperations.update(UPDATE_STUDENT,student.getName(),student.getAge(),student.getId());
    }

    //删
    @Override
    public void deleteStudent(Integer id) {
        jdbcOperations.update(DELETE_STUDENT,id);
    }

}

JdbcTemplate是JdbcOperations的实现类。

getStudentById()方法调用了jdbcOperations的queryForObject()方法，该方法有三个参数：

参数一：sql

参数二：一个RowMapper实例，RowMapper接口只有mapRow()一个方法，该方法有两个参数：ResultSet和当前遍历的索引

参数三：可变参数列表，用于填充sql占位符