《Spring》（十六）---- JDBC

　　一般情况下，都是在DAO类中使用JdbcTemplate，JdbcTemplate在XML配置文件中配置好，直接在DAO中注入即可。

<?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:p="http://www.springframework.org/schema/p"
       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-3.1.xsd">
                              
       <context:component-scan base-package="com.ivy"/>
       
       <context:property-placeholder location="classpath:jdbc.properties"/>
       <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
               destroy-method="close"
               p:driverClassName="${jdbc.driverClassName}"
               p:url="${jdbc.url}"
               p:username="${jdbc.username}"
               p:password="${jdbc.password}"/>
           
           <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
               p:dataSource-ref="dataSource"/>
</beans>

在Spring配置文件中配置DAO一般分为4个步骤：

1. 定义DataSource
2. 定义JdbcTemplate
3. 声明一个抽象的bean，以便所有DAO复用配置JdbcTemplate属性的配置。
4. 配置具体的DAO

　　基本数据操作

• 更改数据

尽量使用可绑定参数的SQL，以便数据库可以复用SQL的执行计划，提高数据库的执行效率。

• 返回数据库的表自增主键值

例如：

final String sqlString = "INSERT INTO t_forum(forum_name, forum_desc) VALUES (?, ?)";
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            
            @Override
            public PreparedStatement createPreparedStatement(Connection conn)
                    throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sqlString);
                ps.setString(1, forum.getForumnName());
                ps.setString(2, forum.getForumnDesc());
                return ps;
            }
        }, keyHolder);
        
        forum.setForumnId(keyHolder.getKey().intValue());

• 批量更新

private final String BATCH_INSERT_SQL = "INSERT INTO t_forum(forum_name, forum_desc) VALUES (?, ?)";

public void addForums(final List<Forum> forums) {
        jdbcTemplate.batchUpdate(BATCH_INSERT_SQL, new BatchPreparedStatementSetter() {
            
            @Override
            public void setValues(PreparedStatement ps, int index) throws SQLException {
                Forum forum = forums.get(index);
                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
            }
            
            @Override
            public int getBatchSize() {
                return forums.size();
            }
        });
    }

• 查询数据

　　Spring提供了RowCallbackHandler回调接口，通过该接口可以定义如何从结果集中获取数据。

单条结果集处理：

private final String SELECT_SQL = "SELECT forum_name, forum_desc FROM t_forum WHERE forum_id=?";

public Forum getForum(final int forumId) {
        final Forum forum = new Forum();
        
        jdbcTemplate.query(SELECT_SQL, new Object[]{forumId}, new RowCallbackHandler() {
            
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                forum.setForumId(forumId);
                forum.setForumDesc(rs.getString("forum_desc"));
                forum.setForumName(rs.getString("forum_name"));
            }
        });
        return forum;
    }

多条数据结果集的处理：

private final String SELECT_MULTI_SQL = "SELECT forum_id,forum_name, forum_desc FROM t_forum WHERE forum_id between ? and ?";

public List<Forum> getForums(final int fromId, final int toId) {
        final List<Forum> forums = new ArrayList<>();
        
        jdbcTemplate.query(SELECT_MULTI_SQL, new Object[]{fromId, toId}, new RowCallbackHandler() {
            
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Forum forum = new Forum();
                forum.setForumId(rs.getInt("forum_id"));
                forum.setForumDesc(rs.getString("forum_desc"));
                forum.setForumName(rs.getString("forum_name"));
                forums.add(forum);
            }
        });
        return forums;
    }

使用RowMapper<T>处理结果集，RowMapper<T>更适合在多行结果集中使用：

private final String SELECT_MULTI_SQL = "SELECT forum_id,forum_name, forum_desc FROM t_forum WHERE forum_id between ? and ?";

public List<Forum> getForumsByRowMapper(final int fromId, final int toId) {
        
        return jdbcTemplate.query(SELECT_MULTI_SQL, new Object[]{fromId, toId}, new RowMapper<Forum>() {

            @Override
            public Forum mapRow(ResultSet rs, int index) throws SQLException {
                Forum forum = new Forum();
                forum.setForumId(rs.getInt("forum_id"));
                forum.setForumDesc(rs.getString("forum_desc"));
                forum.setForumName(rs.getString("forum_name"));
                return forum;
            }
            
        });
    }

RowCallbackHandler vs RowMapper<T>

　　从功能上来说，RowCallbackHandler和RowMapper<T>并没有太大的区别，它们都是用于定义结果集行的读取逻辑，将ResultSet中的数据映射到对象或List中。

　　当处理大结果集时，如果使用RowMapper，结果集中的所有数据最终都会映射并汇总成一个List<T>对象，占用大量的JVM内存，甚至可直接引发OOM，这时，应该使用RowCallbackHandler接口，用processRow()方法内部处理结果集数据。

• 查询单值数据

int类型的单值查询接口：int queryForInt(String sql)

private final String COUNT_SQL = "SELECT COUNT(*) FROM t_forum";
public int getForumNum() {
        return jdbcTemplate.queryForInt(COUNT_SQL);
    }

long类型的单值查询接口：long queryForLong(String sql)

其他类型的单值查询接口：<T> T queryForObject(String sql, Class<T> requiredType);

使用RowMapper获取单值对象：

public class TopicDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public double getReplyRate(int userId) {
        String sql = "SELECT topic_replies, topic_views FROM t_topic WHERE user_id=?";
        double rate = jdbcTemplate.queryForObject(sql, new Object[]{userId}, new RowMapper<Double>() {

            @Override
            public Double mapRow(ResultSet rs, int index) throws SQLException {
                // TODO Auto-generated method stub
                int replies = rs.getInt("topic_replies");
                int views = rs.getInt("topic_views");
                if (views > 0) {
                    return new Double((double)replies/views);
                } else {
                    return new Double(0.0);
                }
            }
            
        });
        return rate;
    }
}

调用存储过程

创建一个存储过程：

delimiter //

CREATE PROCEDURE P_GET_TOPIC_NUM(IN in_user_id INT, OUT out_num INT)
BEGIN
    SELECT COUNT(*) INTO out_num FROM t_topic WHERE user_id=in_user_id;
END
//
delimiter ;

public int getUserTopicNum(final int userId) {
        String sql = "{call P_GET_TOPIC_NUM(?,?)}";
        Integer num = jdbcTemplate.execute(sql, new CallableStatementCallback<Integer>() {

            @Override
            public Integer doInCallableStatement(CallableStatement cs)
                    throws SQLException, DataAccessException {
                // TODO Auto-generated method stub
                cs.setInt(1, userId);
                cs.registerOutParameter(2, Types.INTEGER);
                cs.execute();
                return cs.getInt(2);
            }
        });
        return num;
    }