SpringJDBC

SpringJDBC是对原生JDBC的一个封装，它不是一个ORM框架，但是对于一些小项目来说，它的所提供的功能就已经卓卓有余了，而且对于一些比价复杂的SQL 原声的JDBC还是有它一定的优势，所以今天我就将SpringJDBC的知识写一下。

首先Spring为各种持久化技术提供模板类，这样做有两点，第一点方面操作，第二点你让hibernate的session和connection等这些线程不安全的类 变成了线程安全了（因为使用了ThreadLocal类），这些就不在这里讨论了，如果对这方面有兴趣可以看一下《Spring 3.X企业应用开发实战》。

使用SpringJDBC的前提地下要配置数据源，下面我就列出数据源的完整配置，当然在配置文件中不需要将所有属性都设置上。

以下就是datasource.properties 的属性配置（这使用的是BasicDataSource 数据源，在Spring ORM 章节 我会采用 C3P0的 数据源 会有更加详细的C3P0配置）：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/springreview
username=root
password=tonyyan

#连接池创建的连接的默认auto-commit状态（默认值：true）
defalutAutoCommit=true

#连接池创建的连接的默认的read-only状态。如果没有设置则setReadOnly方法将不会被调用 （默认值：驱动默认）
defaultReadOnly=false

#连接池创建的连接的默认的TransactionIsolation状态，可选值如下（默认值：驱动默认）：
#NONE  READ_COMMITTED READ_UNCOMMITTED REPEATABLE_READ SERIZLIZABLE
defaultTransactionIsolation=READ_COMMITTED

#初始化连接，连接池启动时创建的初始化连接数量（默认值：0）
initialSize=0

#最大活动连接，连接池启动时创建的初始化连接数量（默认值：8）
maxActive=8

#最大空闲连接，连接池中容许保持空闲状态的最大连接数，超过的空闲连接将被释放，如果设置为负数表示不限制（默认值：8）
maxIdle=8

#最小空闲连接，连接池中容许保持空闲状态的最小连接数，超过的空闲连接将被释放，如果设置为负数表示不限制（默认值：0）
minIdle=0

#最大等待事件，当没有可用连接时，连接池等待连接被归还的最大时间（毫秒为单位），超过事件则抛出异常，如果设置-1表示无线等待（默认值：无限）
maxWait=1500

#SQL查询语句。用于连接有消息检查，必须放回至少一行的数据MYSQL中可以设置select 1 oracle可以设置select 1 from dual
validationQuery=select 1

#指明是否在从连接池中取出连接并连接前前进行检查，如果检查失败，则从连接池删除该连接并尝试取出另外一个新连接（默认值：TRUE） 必须设置validationQuery
testOnBorrow=false

#指明是否在连接使用完毕后将连接归还给连接池是进行有效性检查，如果失败，则从连接池中删除该连接（默认值：FALSE） 必须设置validationQuery
testOnReturn=false

#指明连接是否被空闲回收器进行检查，如果检查失败，则连接将从池中去除（默认值：FALSE） 必须设置validationQuery
testWhileIdle=true

#空闲连接回收器线程运行的周期（毫秒为单位）如果设置非整数，则不运行。（默认值：-1） 必须设置validationQuery
timeBetweenEvictionRunsMillis=3600000

#每次空闲连接回收器线程运行时检查的连接数量
numTestsPerEvictionRun=3

#连接在可被空闲接连回收器收前已经在池中空闲的时间（毫秒为单位）（默认值：1000*60*30）
minEvictableIdleTimeMillis=1800000

#开启池的prepared statement 池功能，设置为true CallableStatement 和 PreparedStatemen 都会被缓存起来（默认值：FALSE）
poolPreparedStatements=false

#PreparedStatement池能够同时分配的打开的statements的最大数量，如果设置为0表示不限制（默认值：无限）
maxOpenPreparedStatements=0

#标记是否删除泄露的连接，如果为true则如果可能存在泄露的连接将会被主动清除，
#这个机制在(getNumIdle()<2 and (getNumActive() > getMaxActive()-3))条件下触发。
removeAbandoned=false

#泄露的连接可以被回收的超时时间（秒为单位）（默认值：300）
removeAbandonedTimeout=72000

#标记当Statement或者连接被泄露时是否打印程序的stack traces日志。
#被泄露的statement和连接的日志添加在每个连接打开或者生成的statement，因为需要生成stack trace
logAbandoned=false

以下是Spring的配置文件：

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/aop
       http://www.springframework.org/schema/aop/spring-aop.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">


    <context:property-placeholder location="datasource.properties"/>

    <context:component-scan base-package="com.maxfunner"/>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${driverClassName}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" p:dataSource-ref="dataSource"/>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
          p:dataSource-ref="dataSource"/>

    <aop:config>
        <aop:pointcut id="allServices" expression="within(com.maxfunner.service..*)" />
        <aop:advisor advice-ref="txAdvice" pointcut-ref="allServices" />
    </aop:config>

    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="*" read-only="false" isolation="READ_COMMITTED" />
        </tx:attributes>
    </tx:advice>


</beans>

当然我都配置了Spring的声明式事务，将service所有类都进行声明式事务的织入。

以下将Spring的模板类和所有事物管理器（transaction manager）列出：

Template:

JDBC ---> org.springframework.jdbc.core.JdbcTemplate

Hibernate --->org.springframework.orm.hibernate5.HibernateTemplate （这里是hibernate5的template spring支持从hibernate3开始的所有template）

mybatis在mybatis的包中提供，其他我没有用过的就不写了。。。

PlatformTransactionManager 的实现类如下:

org.springframework.orm.hibernate5.HibernateTransactionManager  (这里是hibernate5的template spring支持从hibernate3开始的所有TransactionManager）

org.springframework.jdbc.datasource.DataSourceTransactionManager （SpringJDBC和mybatis使用这个）

其他jdo 和 JTA 我就不写了。。。

以下是一个使用SpringJDBC 创建表插入数据的实例：

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate template;

    public JdbcTemplate getTemplate() {
        return template;
    }

    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }

    public void initUserTable(){

        String createTableSql = "create table t_user(user_id int primary key auto_increment,username varchar(100),pwd varchar(100))";

        String dropOldTableSQL = "drop table if exists t_user";


        //使用JdbcTemplate执行删除原表
        template.execute(dropOldTableSQL);

        //使用JdbcTemplate创建表
        template.execute(createTableSql);

        //使用GeneratedKeyHolder获得新插入数据的主键
        KeyHolder keyHolder = new GeneratedKeyHolder();

        final String insertUserSQL = "insert into t_user(username,pwd) VALUES (?,?)";

        final User adminUser = new User("Admin","adminPwd");

        //使用JdbcTemplate去更新数据
        template.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                //通过connection获得PreparedStatement 同时指定需要返回主键RETURN_GENERATED_KEYS
                PreparedStatement statement = connection.prepareStatement(insertUserSQL, Statement.RETURN_GENERATED_KEYS);
                //传入参数
                statement.setString(1,adminUser.getUsername());
                statement.setString(2,adminUser.getPwd());
                return statement;
            }
        },keyHolder);


        //输出保存插入数据的主键到对象当中
        adminUser.setUserId(keyHolder.getKey().intValue());
        
        System.out.println("adminUser ->>>>>> " + adminUser);

        //需要批量处理的List
        final List<User> userList = new ArrayList<User>();
        userList.add(new User("TONY","TONYPWD"));
        userList.add(new User("asd","TONYPWD"));
        userList.add(new User("sds","TONYPWD"));
        userList.add(new User("fvcx","TONYPWD"));
        userList.add(new User("xwd","TONYPWD"));
        userList.add(new User("dfsd","TONYPWD"));
        userList.add(new User("cew","TONYPWD"));
        userList.add(new User("adfd","TONYPWD"));
        userList.add(new User("sdfwe","TONYPWD"));

        
        //使用JdbcTemplate的批量插入
        template.batchUpdate(insertUserSQL, new BatchPreparedStatementSetter() {

            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setString(1,userList.get(i).getUsername());
                preparedStatement.setString(2,userList.get(i).getPwd());
            }

            public int getBatchSize() {
                return userList.size();
            }
        });
    }

}

使用查询数据之RowMapper处理数据集实例：

public List<User> getAllUser(){
    String sql = "select user_id,username,pwd from t_user where user_id between ? and ?";
    List<User> userList = template.query(sql, new Object[]{1, 10}, new RowMapper<User>() {
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            User user = new User();
            user.setUserId(resultSet.getInt(1));
            user.setUsername(resultSet.getString(2));
            user.setPwd(resultSet.getString(3));
            return user;
        }
    });
    return userList;
}

使用查询数据之RowCallbackHandler处理数据集：

public List<User> getAllUser() {
    String sql = "select user_id,username,pwd from t_user where user_id between ? and ?";

    final List<User> userList = new ArrayList<User>();

    template.query(sql, new Object[]{1, 10}, new RowCallbackHandler() {
        public void processRow(ResultSet resultSet) throws SQLException {
            User user = new User();
            user.setUserId(resultSet.getInt(1));
            user.setUsername(resultSet.getString(2));
            user.setPwd(resultSet.getString(3));
            userList.add(user);
        }
    });
    
    return userList;
}

使用JdbcTemplate查询单条数据：

public int getUserCount() {
    String countSQL = "select count(*) from t_user";
    return this.template.queryForObject(countSQL, new RowMapper<Integer>() {
        public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
            return resultSet.getInt(1);
        }
    });
}

使用SpringTemplate 调用存储过程：

以下是我在MySQL创建的存储过程

delimiter //
create procedure P_USER_NAME(IN in_user_id int , OUT out_name varchar(100))
begin
select username into out_name  from t_user where user_id = in_user_id;
end

使用SpringTemplate进行调用：

public String getUserNameById(final int userId){
    String sql = "{call P_USER_NAME(?,?)}";
    String username = this.template.execute(sql, new CallableStatementCallback<String>() {
        public String doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException {
            callableStatement.setInt(1,userId);
            callableStatement.registerOutParameter(2,Types.VARCHAR);
            callableStatement.execute();
            return callableStatement.getString(2);
        }
    });
    return username;
}

当然我们可以返回一个ResultSet行集数据 但是我们返回的是Spring提供的SqlRowSet，用法基本上和resultSet差不多，但是需要注意的是resultSet是连接性行集，就是说是在连接过程中使用的行集合，但是Spring提供的是SqlRowSet是非连接性行集，所以不会像resultSet一样采取分批返回数据，sqlRowSet会一次性放回所有的行集（fetchsize）所以需要特别注意，如果一般小心就会导致JVM的内存销毁压力山大。以下是关于SqlRowSet的用法：

public SqlRowSet getAllUserRowSet(){
    String sql = "select user_id,username,pwd from t_user ";
    SqlRowSet rowSet = this.template.queryForRowSet(sql);
    while (rowSet.next()) {
        System.out.println("user_id : " + rowSet.getInt(1) +
                " , name : " + rowSet.getString(2) +
                " , pwd : " + rowSet.getString(3));
    }
    return rowSet
}

我们还可以使用另外一种JDBCTemplate去完成操作，NamedParameterJdbcTemplate。

使用NamedParameterJdbcTemplate 可以不使用？这种占位符，可以对占位符进行命名，减少出错机会。而且还可以BeanPropertySqlParameterSource将目标类的属性名与参数名进行对应，从而可以自动传参数，看看以下例子：

public void insertUser(User user){
    String sql = "insert into t_user(username,pwd) values(:username,:pwd)";
    SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(user);
    namedParameterJdbcTemplate.update(sql,parameterSource);
}

其中User类有三个属性 userId,username,pwd 所以BeanPropertySqlParameterSource自动取对象中的username 和 pwd值，但是如果要使用NamedParameterJdbcTemplate也需要在配置文件进行配置，配置如下：

<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <const

如果不使用BeanPropertySqlParameterSource还可以用以下方法进行相同的操作：

public void insertUser(User user){
    String sql = "insert into t_user(username,pwd) values(:username,:pwd)";
    Map<String,Object> params = new HashMap<String, Object>();
    params.put("username",user.getUsername());
    params.put("pwd",user.getPwd());
    namedParameterJdbcTemplate.update(sql,params);
}

可以看见我只使用了一个Map作为一个参数传入了 template的update方法当中，当然也可以使用MqlSqlParameterSource这个跟直接使用Map差别不大。

以OO方式访问数据库，就不再这里说了，因为对于我本人来说以上几种才是我用的比较多的方式，如果兴趣的读者可以参考《Spring 3.X企业应用开发实战》（近期看见出了4.x）我个人觉得这本书把spring的很多东西都说的很详细，如果有其他读者觉得有其他好书也可以在评论中评论跟我分享。