spring日记（八）：使用Spring JDBC访问数据库

本人博客已搬家，新地址为：http://yidao620c.github.io/

在xml中配置jdbcTemplate：

<context:component-scan base-package="com.springzoo"/>
     
<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"/>

下面是DAO的示例：

@Repository
public class ForumDao {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
 
    public void initDb() {
        String sql = "create table t_user(user_id int primary key,user_name varchar(60))";
        jdbcTemplate.execute(sql);
    }
 
    /**
     * 更新一条数据
     * 
     * @param forum
     */
    public void addForum(final Forum forum) {
        final String sql = "INSERT INTO t_forum(forum_name,forum_desc) VALUES(?,?)";
        Object[] params = new Object[] { forum.getForumName(),
                forum.getForumDesc() };
        // 方式1
        // jdbcTemplate.update(sql, params);
 
        // 方式2
        // jdbcTemplate.update(sql, params,new
        // int[]{Types.VARCHAR,Types.VARCHAR});
 
        // 方式3
        /*
         * jdbcTemplate.update(sql, new PreparedStatementSetter() { public void
         * setValues(PreparedStatement ps) throws SQLException { ps.setString(1,
         * forum.getForumName()); ps.setString(2, forum.getForumDesc()); } });
         */
 
        // 方式4
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn)
                    throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
                return ps;
            }
        }, keyHolder);
        forum.setForumId(keyHolder.getKey().intValue());
    }
 
    public void addForumByNamedParams(final Forum forum) {
        final String sql = "INSERT INTO t_forum(forum_name, forum_desc) VALUES(:forumName,:forumDesc)";
        SqlParameterSource sps = new BeanPropertySqlParameterSource(forum);
        namedParameterJdbcTemplate.update(sql, sps);
    }
 
    /**
     * 批量更新数据
     * 
     * @param forums
     */
    public void addForums(final List<Forum> forums) {
        final String sql = "INSERT INTO t_forum(forum_name,forum_desc) VALUES(?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return forums.size();
            }
 
            public void setValues(PreparedStatement ps, int index)
                    throws SQLException {
                Forum forum = forums.get(index);
                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
            }
        });
    }
 
    /**
     * 根据ID获取Forum对象
     * 
     * @param forumId
     * @return
     */
    public Forum getForum(final int forumId) {
        String sql = "SELECT forum_name,forum_desc FROM t_forum WHERE forum_id=?";
        final Forum forum = new Forum();
 
        jdbcTemplate.query(sql, new Object[] { forumId },
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        forum.setForumId(forumId);
                        forum.setForumName(rs.getString("forum_name"));
                        forum.setForumDesc(rs.getString("forum_desc"));
                    }
                });
        return forum;
    }
 
    public List<Forum> getForums(final int fromId, final int toId) {
        String sql = "SELECT forum_id,forum_name,forum_desc FROM t_forum WHERE forum_id between ? and ?";
        // 方法1：使用RowCallbackHandler接口
        /*
         * final List<Forum> forums = new ArrayList<Forum>();
         * jdbcTemplate.query(sql,new Object[]{fromId,toId},new
         * RowCallbackHandler(){ public void processRow(ResultSet rs) throws
         * SQLException { Forum forum = new Forum();
         * forum.setForumId(rs.getInt("forum_id"));
         * forum.setForumName(rs.getString("forum_name"));
         * forum.setForumDesc(rs.getString("forum_desc")); forums.add(forum);
         * }}); return forums;
         */
 
        return jdbcTemplate.query(sql, new Object[] { fromId, toId },
                new RowMapper<Forum>() {
                    public Forum mapRow(ResultSet rs, int index)
                            throws SQLException {
                        Forum forum = new Forum();
                        forum.setForumId(rs.getInt("forum_id"));
                        forum.setForumName(rs.getString("forum_name"));
                        forum.setForumDesc(rs.getString("forum_desc"));
                        return forum;
                    }
                });
 
    }
 
    int getForumNum() {
        return 0;
    }
}

返回数据库的自增主键方法：

Statement stmt = conn.createStatement();
String sql = "INSERT INTO t_topic(....) values(...)";
stmt.executeUpate(sql, Statement.RETURN_GENERATED_KYES);
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
    int key = rs.getInt();
}

spring利用这一技术，提供了一个可以返回新增记录主键的方法，就是上面的方式4：

// 方式4
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
    public PreparedStatement createPreparedStatement(Connection conn)
            throws SQLException {
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, forum.getForumName());
        ps.setString(2, forum.getForumDesc());
        return ps;
    }
}, keyHolder);
forum.setForumId(keyHolder.getKey().intValue());

不过要注意的是，采用数据库的自增主键是不安全的，可以通过查询sequence或者在应用程序中提供主键两种方式保证安全性。

从功能上讲，RowCallbackHandler和RowMapper<T>没有太大区别，在返回多行时候RowMapper<T>更适合，因为不需要手动去往List里面添加。spring宣称RowCallbackHandler接口实现类可以使有状态的，如果它有状态我们就不能在多个地方重用。而RowMapper<T>实现类是没有状态的。

但是注意：如果处理大结果集的时候，使用RowMapper可能会内存泄露，最好使用RowCallbackHandler的实现类RowCountCallbackHandler，在processRow方法中处理结果集数据。一句话，RowMapper是将所有结果返回到一个List中，再去处理。而RowCallbackHandler是一行一行的去处理。

单值查询：

@Repository
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>() {
                    public Double mapRow(ResultSet rs, int index)
                            throws SQLException {
                        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;
    };
 
    public int getUserTopicNum(final int userId) {
        String sql = "{call P_GET_TOPIC_NUM(?,?)}";
        //方式1
/*      Integer num = jdbcTemplate.execute(sql,
                new CallableStatementCallback<Integer>() {
                    public Integer doInCallableStatement(CallableStatement cs)
                            throws SQLException, DataAccessException {
                        cs.setInt(1, userId);
                        cs.registerOutParameter(2, Types.INTEGER);
                        cs.execute();
                        return cs.getInt(2);
                    }
                });
        return num;*/
         
        //方式2
        CallableStatementCreatorFactory fac = new CallableStatementCreatorFactory(sql); 
        fac.addParameter(new SqlParameter("userId",Types.INTEGER)); 
        fac.addParameter(new SqlOutParameter("topicNum",Types.INTEGER)); 
        Map<String,Integer> paramsMap = new HashMap<String,Integer>();
        paramsMap.put("userId",userId);
        CallableStatementCreator csc = fac.newCallableStatementCreator (paramsMap); 
        Integer num = jdbcTemplate.execute(csc,new CallableStatementCallback<Integer>(){
            public Integer doInCallableStatement(CallableStatement cs) 
                throws SQLException, DataAccessException {
                cs.execute();
                return cs.getInt(2);
            }   
        });
        return num;
    }
 
    public int getUserTopicNum2(int userId) {
        return 0;
    };
 
    public SqlRowSet getTopicRowSet(int userId) {
        String sql = "SELECT topic_id,topic_title FROM t_topic WHERE user_id=?";
        return jdbcTemplate.queryForRowSet(sql,userId);
 
    };
}

处理BLOB/COLB的大数据：

spring定义了一个独立于java.sql.Blob/Clob接口，以统一方式操作各种数据库Lob类型的LobCreator接口。为了方便在PreparedStatement中使用LobCreator，可以直接使用JDBCTemplate#execute(String sql, AbstractLobCreatingPreparedStatementCallback lcpsc)方法。用来set大字段的值

LobHandler接口为操作大二进制字段和大文本字段提供了统一访问接口，不管底层数据库究竟以大对象还是以一般数据类型方式进行操作，此外，LobHandler还充当LobCreator的工厂类。一般使用DefaultLobHandler即可。用来get返回的大字段的值

xml配置，注意lazy-init要设置成true，因为nativeJdbcExtractor需要通过运行时反射机制获取底层JDBC对象：

 

<bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate"
      p:dataSource-ref="dataSource"/>
 
<bean id="nativeJdbcExtractor"
    class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
    lazy-init="true" />
 
<bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
    lazy-init="true" />

DAO中的用法，往数据库中插入或更新大数据操作：

@Repository
public class PostDao {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Autowired
    private LobHandler lobHandler;
 
    public void addPost(final Post post){
        String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"
                   + " VALUES(?,?,?,?)";
        jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
          protected void setValues(PreparedStatement ps,LobCreator lobCreator)
                  throws SQLException {
                                //1：固定主键
                                ps.setInt(1,1);
                   
                    ps.setInt(2, post.getUserId()); 
                    lobCreator.setClobAsString(ps, 3, post.getPostText());
                    lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());
                }
            });
 
    }
    .....
}

以块数据方式读取数据库中大数据：

public List<Post> getAttachs(final int userId) {
    String sql = " SELECT post_id,post_attach FROM t_post where user_id =? and post_attach is not null ";
    return jdbcTemplate.query(sql, new Object[] { userId },
            new RowMapper<Post>() {
                public Post mapRow(ResultSet rs, int rowNum)
                        throws SQLException {
                    int postId = rs.getInt(1);
                    byte[] attach = lobHandler.getBlobAsBytes(rs, 2);
                    Post post = new Post();
                    post.setPostId(postId);
                    post.setPostAttach(attach);
                    return post;
                }
            });
 
}

以流数据方式读取Lob数据：

对于体积很大的Lob数据，比如超过50M，我们可以使用流的方式进行访问，减少内存占用。

public void getAttach(final int postId, final OutputStream os) {
    String sql = "SELECT post_attach FROM t_post WHERE post_id=? ";
    jdbcTemplate.query(sql, new Object[] {postId},
        new AbstractLobStreamingResultSetExtractor() {          
            protected void handleNoRowFound() throws LobRetrievalFailureException {
                    System.out.println("Not Found result!");
                }
            
            public void streamData(ResultSet rs) throws SQLException,IOException {
                    InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1);
                    if (is != null) {
                        FileCopyUtils.copy(is, os);
                    }
            }
        }
    );
 
}

自增键和行集RowSet：

之前说过依靠数据库提供的自增键功能，比如mysql的auto_increment是不安全的。spring允许用户在应用层产生主键值，为此定义了org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer接口，提供两种产生主键的方案：一个是通过序列sequence产生，另一个是通过表产生主键。

* AbstractDataFieldMaxValueIncrementer：使用sequence或者模拟序列表产生主键，被下面两个类继承。

* AbstractSequenceMaxValueIncrementer使用标准的sequence产生主键

* AbstractColumnMaxValueIncrementer使用模拟序列表产生主键，可以指定cacheSize一次性缓冲多少个主键值，减少数据库访问次数。

首先在DAO中加入这一行：

@Autowired
private DataFieldMaxValueIncrementer incre;

然后再xml中定义：

<!-- 1：基于数据库序列的自增器 -->
<!-- 
<bean id="incre" 
 class="org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer"
 p:incrementerName="seq_post_id"
 p:dataSource-ref="dataSource"/>  
-->
 
<!-- 1：基于数据表的自增器 -->
<bean id="incre"
    class="org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer"
    p:incrementerName="t_post_id"
    p:columnName="sequence_id"
    p:cacheSize="10"
    p:dataSource-ref="dataSource"/>

用法：

public void addPost(final Post post){
String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"
               + " VALUES(?,?,?,?)";
jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
      protected void setValues(PreparedStatement ps,LobCreator lobCreator)
      throws SQLException {
            //2：通过自增键指定主键值        
            ps.setInt(1, incre.nextIntValue());
                ps.setInt(2, post.getUserId()); 
                lobCreator.setClobAsString(ps, 3, post.getPostText());
                lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());
            }
        });
 
}

规划主键方案：

所有主键全部采用应用层主键产生方案，使用UUID或者DataFieldMaxValueIncrementer生产主键。

以行集返回数据：

RowSet会一次性装载所有的匹配数据，同时会断开数据库的连接。而不是像ResultSet那样，分批次返回一批数据，一批的行数为fetchSize。所以，对于大结果集的数据，使用SqlRowSet会造成很大的内存消耗。

其他的JDBCTemplate：

NamedParameterJDBCTemplate：提供命名参数绑定功能

SimpleJDBCTemplate：将常用的API开放出来

先看一下xml配置文件：

<bean id="namedParamJdbcTemplate"
      class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
      <constructor-arg ref="dataSource"/>
</bean>

用法如下：

public void addForumByNamedParams(final Forum forum) {
    final String sql = "INSERT INTO t_forum(forum_name, forum_desc) VALUES(:forumName,:forumDesc)";
    // SqlParameterSource sps = new BeanPropertySqlParameterSource(forum);
    MapSqlParameterSource sps = new MapSqlParameterSource().
        addValue("forumName", forum.getForumName()).
        addValue("forumDesc", forum.getForumDesc());
    namedParameterJdbcTemplate.update(sql, sps);
}