Spring学习笔记 Spring JDBC框架

ゝ一世哀愁。 2022-07-11 13:15 192阅读 0赞

## Spring JDBC简介 ##

先来看看一个JDBC的例子。我们可以看到为了执行一条SQL语句，我们需要创建连接，创建语句对象，然后执行SQL，然后操纵结果集获取数据。

try(Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)){
        List<User> users = new ArrayList<>();
        try (Statement statement = connection.createStatement()) {
            try (ResultSet rs = statement.executeQuery("SELECT *FROM user")) {
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt(1));
                    user.setUsername(rs.getString(2));
                    user.setPassword(rs.getString(3));
                    user.setNickname(rs.getString(4));
                    user.setBirthday(rs.getDate(5));
                    users.add(user);
                }
            }
        }
    }

其实这些步骤中有很多步骤都是固定的，Spring JDBC框架将这些操作封装起来， 我们只需要关注业务逻辑点即可。在Spring JDBC框架中，我们要做的事情如下：

*  定义连接字符串参数。
 *  指定SQL语句。
 *  声明参数和参数类型。
 *  每次迭代结果集的操作。

Spring会帮我们完成以下事情：

*  打开连接。
 *  准备和执行SQL语句。
 *  在需要的情况下迭代结果集。
 *  处理异常。
 *  操作事务。
 *  关闭结果集、语句和数据库连接。

## 使用JdbcTemplate ##

JdbcTemplate是Jdbc框架最重要的类，提供了较为底层的Jdbc操作。其它几个类都是在JdbcTemplate基础上封装了相关功能。

### 添加依赖 ###

要在Gradle项目中使用Spring JDBC框架，添加如下一段。由于Spring JDBC的主要类JdbcTemlate需要一个数据源用来初始化，所以还需要一个数据源的实现。

compile group: 'org.springframework', name: 'spring-jdbc', version: '4.3.5.RELEASE'
    compile group: 'org.apache.commons', name: 'commons-dbcp2', version: '2.1.1'

如果要使用Spring框架的其他功能，可能还需要添加对应的依赖。

### 创建Jdbc Template Bean ###

首先需要创建一个数据源Bean。为了将配置分离，我们先新建一个`jdbc.properties`文件。

jdbc.url=jdbc:mysql://localhost:3306/test
    jdbc.driverClassName=com.mysql.jdbc.Driver
    jdbc.username=root
    jdbc.password=12345678

然后创建一个Spring配置文件`jdbc.xml`。这里用到了`<context:property-placeholder>`节点来导入其它配置文件。然后用这些属性创建一个数据源Bean，然后再利用数据源Bean来创建一个JdbcTemplate。

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
        <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="${jdbc.driverClassName}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
        </bean>
    
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <constructor-arg ref="dataSource"/>
        </bean>
        <context:property-placeholder location="jdbc.properties"/>
    </beans>

### JdbcTemplate操作 ###

注册了JdbcTemplate之后，就可以将它注入到任何地方来使用了。首先它可以使用execute方法，执行任何SQL语句。这里创建了一个简单的MySQL用户表，只有主键和用户名。

jdbcTemplate.execute("CREATE TABLE user(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) UNIQUE)");

它还可以使用update方法执行增加、更新和删除操作。

jdbcTemplate.update("INSERT INTO user(name) VALUES(?)", "yitian");
    jdbcTemplate.update("INSERT INTO user(name) VALUES(?)", "zhang2");
    jdbcTemplate.update("UPDATE user SET name=? WHERE name=?", "zhang3", "zhang2");
    jdbcTemplate.update("DELETE FROM user WHERE name=?", "zhang3");

查询操作也很简单，使用queryForObject方法，传入SQL字符串和结果类型即可。

int count = jdbcTemplate.queryForObject("SELECT count(*) FROM user", Integer.class);
    System.out.println("记录数目是:" + count);
    String name = jdbcTemplate.queryForObject("SELECT name FROM user WHERE id=1", String.class);
    System.out.println("姓名是:" + name);

如果要查询整条记录也可以。Spring提供了一个接口RowMapper，只需要实现该接口的mapRow方法，即可将结果集的一条记录转化为一个Java对象，该方法的第二个参数是当前行的行数。下面是一个RowMapper实现。

public class UserRowMapper implements RowMapper<User> { 
    
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new User(rs.getInt(1), rs.getString(2));
        }
    }

User实体类对应于上面创建表时简单的用户表（其他方法已省略）。

public class User {
        private int id;
        private String name;
    }

实现了RowMapper接口之后，我们就可以查询一条记录并转化为Java对象了。

User user = jdbcTemplate.queryForObject("SELECT id,name FROM user WHERE id=?", new UserRowMapper(), 1); System.out.println(user);

查询多条记录也可以，这时候需要使用query方法。

List<User> users = jdbcTemplate.query("SELECT id,name FROM usr", new UserRowMapper());
    System.out.println(users);

还有一个通用方法queryForList，返回一个List，每一个元素都是一个Map，在Map中存放着列名和值组成的键值对。

List<Map<String, Object>> results = jdbcTemplate.queryForList("SELECT id,name FROM user");
    System.out.println(results);

## 使用NamedParameterJdbcTemplate ##

前面的JdbcTemplate提供了非常方便的JDBC操作封装，但是在绑定参数的时候只能采用通配符`?`方式以顺序方式绑定参数。如果SQL语句比较复杂，参数比较多，那么这种方式显得不太方便。因此Spring提供了一个更加方便的类NamedParameterJdbcTemplate，它可以以命名方式绑定SQL语句参数。NamedParameterJdbcTemplate在内部使用一个JdbcTemplate，你也可以调用getJdbcOperations方法获取底层的JdbcTemplate对象，然后用前面的方法进行基本操作。

创建NamedParameterJdbcTemplate和JdbcTemplate相同，只需要传入一个数据源即可。

NamedParameterJdbcTemplate和JdbcTemplate的大部分操作相同，这里仅介绍绑定命名参数的部分。首先，SQL语句必须使用`:参数名称`的形式作为参数。然后，我们创建一个MapSqlParameterSource对象，它的内部使用了一个Map保存的命名参数的名称和值。然后我们使用它的addValue方法传递需要的命名参数的名称和值，这个方法还可以接受第三个参数指定参数类型，这个类型以`java.sql.Types`的一些公共字段的形式给出。最后，将MapSqlParameterSource传递给相应的方法执行即可。

String sql = "SELECT id,name FROM user WHERE name=:name AND id<:user_id";
    MapSqlParameterSource namedParameters = new MapSqlParameterSource();
    namedParameters.addValue("name", "test");
    namedParameters.addValue("user_id", 100, Types.INTEGER);
    User user = namedParameterJdbcTemplate.queryForObject(sql, namedParameters, new UserRowMapper());
    System.out.println(user);

如果不想创建MapSqlParameterSource对象，还可以直接使用一个Map传递命名参数的名称和值。

Map<String, Object> map = new HashMap<>();
    map.put("user_id", 100);
    map.put("name", "test");
    List<User> users = namedParameterJdbcTemplate.query(sql, map, new UserRowMapper());
    System.out.println(users);

上面讨论的`MapSqlParameterSource`实际上实现了`SqlParameterSource`接口，上面的几个方法签名也是接受`SqlParameterSource`接口。这个接口表示用来传递命名参数和值的集合。除了`MapSqlParameterSource`之外，还有另外一个常用的实现，`BeanPropertySqlParameterSource`，这个类接受一个Java Bean对象，然后使用Bean的属性名和值作为命名参数的名称和值。这一点需要注意。

User bean = new User(100, "test");
    SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(bean);
    users = namedParameterJdbcTemplate.query(sql, parameterSource, new UserRowMapper());
    System.out.println(users);

## 使用SimpleJdbc类 ##

前面所说的JdbcTemplate封装了一些功能，让我们方便的使用JDBC。Spring还提供了几个更高级、功能更具体的SimpleJdbc类。这些类会读取JDBC的元数据Metadata，使用起来更加方便。

### SimpleJdbcInsert ###

SimpleJdbcInsert类用来插入数据。简单的使用方法如下。SimpleJdbcInsert需要一个数据源来创建，withTableName方法指定要插入的表名，usingGeneratedKeyColumns指定设置了主键自增的列名。其他使用方法和前面所说的类类似。executeAndReturnKey这个方法很特别，它会将数据插入数据库并返回该条记录对应的自增键。有时候我们可能希望使用自增主键来插入一条数据，由于主键是数据库自动生成的，我们必须再次查询数据库才能获得主键。这种情况下使用executeAndReturnKey非常方便。注意这个方法返回的是`java.lang.Number`类型，可以调用其`XXXvalue`方法转换成各种数值。

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
            .withTableName("user")
            .usingGeneratedKeyColumns("id");
    User user = new User();
    user.setName("test");
    Map<String, Object> params = new HashMap<>();
    params.put("names", user.getName());
    int id = simpleJdbcInsert.executeAndReturnKey(params).intValue();
    System.out.println("simpleJdbcInsert" + user);

### SimpleJdbcCall ###

SimpleJdbcCall类用来调用存储过程的。使用方法类似。这里就直接给出Spring官方文档的示例代码了。

MySQL存储过程。

CREATE FUNCTION get_actor_name (in_id INTEGER) RETURNS VARCHAR(200) READS SQL DATA BEGIN DECLARE out_name VARCHAR(200);
        SELECT concat(first_name, ' ', last_name) INTO out_name FROM t_actor where id = in_id;
        RETURN out_name;
    END;

SimpleJdbcCall调用存储过程。

public class JdbcActorDao implements ActorDao { 
    
        private JdbcTemplate jdbcTemplate;
        private SimpleJdbcCall funcGetActorName;
    
        public void setDataSource(DataSource dataSource) {
            this.jdbcTemplate = new JdbcTemplate(dataSource);
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.setResultsMapCaseInsensitive(true);
            this.funcGetActorName = new SimpleJdbcCall(jdbcTemplate)
                    .withFunctionName("get_actor_name");
        }
    
        public String getActorName(Long id) {
            SqlParameterSource in = new MapSqlParameterSource()
                    .addValue("in_id", id);
            String name = funcGetActorName.executeFunction(String.class, in);
            return name;
        }
    
        // ... additional methods
    
    }

如果要从存储过程获取记录的话，可以这样。以下是一个MySQL存储过程。

CREATE PROCEDURE read_all_actors() BEGIN SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
    END;

相对应的Java代码。

public class JdbcActorDao implements ActorDao { 
    
        private SimpleJdbcCall procReadAllActors;
    
        public void setDataSource(DataSource dataSource) {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.setResultsMapCaseInsensitive(true);
            this.procReadAllActors = new SimpleJdbcCall(jdbcTemplate)
                    .withProcedureName("read_all_actors")
                    .returningResultSet("actors",
                    BeanPropertyRowMapper.newInstance(Actor.class));
        }
    
        public List getActorsList() {
            Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
            return (List) m.get("actors");
        }
    
        // ... additional methods
    
    }

## 将JDBC操作转化为Java对象 ##

`org.springframework.jdbc.object`包下提供了一组类，让我们用更加面向对象的方式来操作数据库。我们可以将SQL查询转化为一组业务对象，也可以方便的进行查询、更新和执行存储过程的操作。

### MappingSqlQuery ###

MappingSqlQuery是一个抽象类，继承自SQLQuery。我们在使用这个类的时候需要创建一个自定义类，继承自MappingSqlQuery，然后在其构造方法中初始化一个查询字符串，并在这里设置查询参数；然后需要实现该类的mapRow方法，将结果集的行转化为实体类对象。下面是一个例子。构造方法中定义的查询字符串会被创建为PreparedStatement，因此可以在查询字符串中使用占位符`?`。对于每个出现的占位符，我们都必须调用declareParameter方法声明参数，该方法接受一个SqlParameter对象，该对象需要参数名和类型两个参数。最后需要调用compile方法编译和准备查询。该类是线程安全的，因此可以安全的在多个线程之间共享对象。

public class UserMappingSqlQuery extends MappingSqlQuery<User> { 
        public UserMappingSqlQuery(DataSource ds) {
            super(ds, "SELECT id,name FROM user WHERE id=:id");
            super.declareParameter(new SqlParameter("id", Types.INTEGER));
            compile();
        }
    
        @Override
        protected User mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new User(rs.getInt(1), rs.getString(2));
        }
    }

然后我们创建一个对象，调用findObject方法并传入查询参数，即可获得结果对象。

@Test
    public void testMappingSqlQuery() {
        MappingSqlQuery<User> mappingSqlQuery = new UserMappingSqlQuery(dataSource);
        User user = mappingSqlQuery.findObject(1);
        logger.debug(user);
    }

如果查询要返回一组记录并传递多个查询参数。需要调用相应的execute方法。一下是另一个MappingSqlQuery，以及其测试代码。

public class UsersMappingSqlQuery extends MappingSqlQuery<User> { 
        public UsersMappingSqlQuery(DataSource ds) {
            super(ds, "SELECT id,name FROM user WHERE id<? AND name LIKE ?");
            super.declareParameter(new SqlParameter("id", Types.INTEGER));
            super.declareParameter(new SqlParameter("name", Types.VARCHAR));
            compile();
        }
    
        @Override
        protected User mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new User(rs.getInt(1), rs.getString(2));
        }
    }

//获取多个对象
    mappingSqlQuery = new UsersMappingSqlQuery(dataSource);
    List<User> users = mappingSqlQuery.execute(100, "test");
    logger.debug(users);

### 使用SqlUpdate ###

这个类的使用方法和SqlQuery类似，但是由于它是一个具体类，因此不需要定义子类即可使用。下面是它的简单使用方法。为了更新具体的数据（例如一个Java Bean对象），你也可以继承该类，并提供自己的更新方法，就和上面一样。

@Test
    public void testSqlUpdate() {
        SqlUpdate sqlUpdate = new SqlUpdate(dataSource, "INSERT INTO user(name) VALUES(?)");
        sqlUpdate.declareParameter(new SqlParameter("name", Types.VARCHAR));
        sqlUpdate.compile();
        sqlUpdate.update("wang5");
    
        List<User> users = jdbcTemplate.query("SELECT id,name FROM user", new UserRowMapper());
        logger.debug(users);
    }

### 使用StoredProcedure ###

StoredProcedure是关系数据库中存储过程概念的抽象类，提供了一组方便的受保护方法。因此在使用该类的时候需要我们创建一个子类，继承该类。在使用这个类的时候我们需要使用setSql方法设置数据库中存储过程的名称。在传递参数的时候，使用SqlParameter传递IN参数，使用SqlOutParameter传递OUT参数，使用SqlInOutParameter传递INOUT参数。

以下是Spring官方文档的一个例子。

class GetSysdateProcedure extends StoredProcedure {
    
        private static final String SQL = "sysdate";
    
        public GetSysdateProcedure(DataSource dataSource) {
            setDataSource(dataSource);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("date", Types.DATE));
            compile();
        }
    
        public Date execute() {
            // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
            Map<String, Object> results = execute(new HashMap<String, Object>());
            Date sysdate = (Date) results.get("date");
            return sysdate;
        }
    }

## 其他知识 ##

### 提供SQL参数信息 ###

一般情况下Spring可以自行决定SQL参数的类型，但是有时候或者说最好由我们提供准确的SQL参数信息。

*  `JdbcTemplate`的很多查询和更新方法包含一个额外的参数，一个int数组，该数组应该是`java.sql.Types`指定的一些常量，表明SQL参数的类型。
 *  可以使用`SqlParameterValue`来设置参数的值，在创建该对象的时候提供参数的值和类型。
 *  如果使用具有命名参数功能的类时，使用`SqlParameterSource`类（`BeanPropertySqlParameterSource`或`MapSqlParameterSource`）来指定命名参数和其类型。

### 数据源 ###

我们在学习JDBC的时候，基本上都是从DriverManager类创建一个数据库连接。在实际环境中，我们应该使用数据源（DataSource）来创建数据库连接。数据源将创建数据库的职责和应用代码分离，数据源可以交给数据库管理员来设置，程序员只需要获取数据源对象，然后开发相关代码。

在上面的例子中我们使用的是Apache的`commons-dbcp2`数据源，Spring自己也实现了几个数据源方便我们开发和测试。

`DriverManagerDataSource`是一个简单的数据源，每次请求都会返回一个新的数据库连接。它使用数据库驱动来创建数据源，就像我们使用`DriverManager`那样。这是一个简单的测试类，可以帮助我们在不借助任何Java EE容器的情况下获取数据源。但是由于使用`commons-dbcp2`这样的成熟数据源也很容易，所以其实我们只需要使用`commons-dbcp2`即可。

`SingleConnectionDataSource`也是一个数据源，它包装了一个单独的数据库连接，在每次请求都会返回同一个数据库连接对象。和`DriverManagerDataSource`相比它更轻量，因为没有创建额外数据库连接的开销。

### 初始化数据源 ###

在创建数据源的时候我们可以在Spring配置文件中设置数据源的初始化脚本。

<jdbc:initialize-database data-source="dataSource">
        <jdbc:script location="classpath:com/foo/sql/db-schema.sql"/>
        <jdbc:script location="classpath:com/foo/sql/db-test-data.sql"/>
    </jdbc:initialize-database>

有时候我们希望在初始化数据的时候删除上一次的测试数据。但是如果数据库不支持类似`DROP TABLE IF EXISTS`这样的语法，那么我们就必须在初始化脚本中添加一些`DROP`语句。这些删除语句可能会失败（如果没有测试数据的情况下执行删除），这时候就可以忽略删除失败。当初始化脚本出现错误的时候就会抛出异常，但是如果设置了忽略删除失败，Spring就会直接忽略这些失败而不抛出异常。ignore-failures属性还可以取另外两个值`NONE`和`ALL`，分别表示不忽略失败和忽略所有失败。

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
        <jdbc:script location="..."/>
    </jdbc:initialize-database>

我们还可以设置初始化脚本的间隔符。

<jdbc:initialize-database data-source="dataSource" separator="@@">
        <jdbc:script location="classpath:com/foo/sql/db-schema.sql" separator=";"/>
        <jdbc:script location="classpath:com/foo/sql/db-test-data-1.sql"/>
        <jdbc:script location="classpath:com/foo/sql/db-test-data-2.sql"/>
    </jdbc:initialize-database>

### 工具类 ###

`org.springframework.jdbc.datasource.DataSourceUtils`，这是一个方便的工具类，包含了一组和数据源相关的工具方法。

`org.springframework.jdbc.support.JdbcUtils`类提供了一些方法来操作JDBC，在Spring内部使用，也可以用于自己的JDBC操作。

还有几个工具类主要由Spring内部使用，这里就不列举了。

### 嵌入式数据库支持 ###

我们在开发数据库应用的时候需要安装某种类型的数据库，比如MySQL等等。但是这样就需要额外的项目依赖。这样一个产品级的数据库软件动辄上G，安装、测试都不方便。这时候我们可以使用嵌入式数据库进行开发和测试。嵌入式数据库具有占用小、启动快、配置简单等特点，非常适合开发测试。而且由于嵌入式数据库系统占用低，在一些设备上还可以直接作为存储数据库使用。例如轻量级嵌入式数据库Sqlite，就安装在每个安卓手机中，用于存储数据。

在Spring中创建一个嵌入式数据库，在XML中添加如下一段。这样创建出来的数据库可以直接作为`javax.sql.DataSource`类型的Spring Bean使用。最好设置`generate-name="true"`生成一个唯一名称。默认情况下创建的是HSQL嵌入式数据库。当然别忘了添加相应嵌入式数据库的依赖项。

<jdbc:embedded-database id="dataSource" generate-name="true">
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:test-data.sql"/>
    </jdbc:embedded-database>

当然还可以以编程方式创建嵌入式数据库。下面是上面等价的编程方式。

@Configuration
    public class DataSourceConfig { 
    
        @Bean
        public DataSource dataSource() {
            return new EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .setType(H2)
                .setScriptEncoding("UTF-8")
                .ignoreFailedDrops(true)
                .addScript("schema.sql")
                .addScripts("user_data.sql", "country_data.sql")
                .build();
        }
    }

除了HSQL，Spring还支持H2和Derby两种嵌入式数据库（值得一提的是，现在的JDK分发包中附带了一个Java DB数据库，在安装了JDK之后可以在JDK安装目录中看到db文件夹，这里面存放的其实就是Derby数据库）。要指定数据库类型，在上面的XML片段中添加`embedded-database`属性并设置`HSQL`、`H2`或`Derby`。如果使用编程方式，在`EmbeddedDatabaseBuilder`上调用`setType(EmbeddedDatabaseType)`方法，该方法的参数指定一个枚举，指定这三种类型。

上面的`generate-name="true"`或者`generateUniqueName(true)`挺重要的。如果不指定这个属性。在多次调用嵌入式数据库之后，可能会生成多个数据库实例。为了避免这种情况发生，需要设置这个属性。设置之后，如果已经存在了数据库实例，就会使用这个已存在的实例，而不是设置新的实例。这个属性是在Spring 4.2中增加的。使用以下几个方法都可以设置该属性。

*  `EmbeddedDatabaseFactory.setGenerateUniqueDatabaseName()`
 *  `EmbeddedDatabaseBuilder.generateUniqueName()`
 *  `<jdbc:embedded-database generate-name="true" … >`

## 参考资料 ##

[http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle/\#jdbc][http_docs.spring.io_spring_docs_current_spring-framework-reference_htmlsingle_jdbc]

项目代码在[Csdn代码库][Csdn]，有兴趣的同学可以看看。

最后祝大家新年快乐！

[http_docs.spring.io_spring_docs_current_spring-framework-reference_htmlsingle_jdbc]: http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle/#jdbc
[Csdn]: https://code.csdn.net/u011054333/spring-core-sample/tree/master