11.1 使用Spring JDBC访问数据库

参考《企业应用开发实战》第11章。

细节：
如果某参数不希望在方法中改变，可以声明为final

实战经验：
一般在配置文件中先声明一下 dataSource、jdbcTemplate等bean，之后在需要用的DAO类中，一般先写个基类BaseDao，在基类中定义一些通用的功能，例如声明JDBCTemplate、分页查询等多种。

@Autowired
private JdbcTemplate jdbcTemplate;

更改

jdbcTemplate的update语句：
比较好的写法是用 ?占位符

String sql = " INSERT INTO t_post(topicId,postText) VALUES(?,?)";               
Object[] params = new Object[]{post.getTopicId(),post.getPostText()};
jdbcTemplate.update(sql, params,new int[]{Types.VARCHAR,Types.VARCHAR});

将某个类的对象插到数据库中，想让主键值自动绑定到该对象上？方便后继的使用

//  ForumJdbcDao里的方法

    @Override
    public void addForum(Forum forum) {
        // TODO Auto-generated method stub
        final String sql = "INSERT INTO t_forum(forumName,forumDesc) VALUES(?,?)";
        // 创建一个主键执有者
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().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 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());
            }
        });
    }

查询

经过SQL语句查询得到的数据需要转化为类的对象并返回！
查询得到单条数据：
Spring会遍历查询的结果集，对结果集的每一行调用RowCallbackHandler回调接口处理数据
与RowCallbackHandler类似的RowMapper就不详说了（见书P371）

// 查询forum_name,forum_desc是为了后面返回Forum的对象
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;
    }

查询返回多条数据：

// 查询forum_id在一组区间内的多条数据
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 ?";

         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;

    }

查询单值数据

//1.查询一行数据并返回int型结果  
jdbcTemplate.queryForInt("select count(*) from test");  
//2. 查询一行数据并将该行数据转换为Map返回  
jdbcTemplate.queryForMap("select * from test where name='name5'");  
//3.查询一行任何类型的数据，最后一个参数指定返回结果类型  
jdbcTemplate.queryForObject("select count(*) from test", Integer.class);  
//4.查询一批数据，默认将每行数据转换为Map       
jdbcTemplate.queryForList("select * from test");  
//5.只查询一列数据列表，列类型是String类型，列名字是name  
jdbcTemplate.queryForList("  
select name from test where name=?", new Object[]{"name5"}, String.class);  
//6.查询一批数据，返回为SqlRowSet，类似于ResultSet，但不再绑定到连接上  
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from test");  

调用存储过程

具体见P375

先创建存储过程：

public int getUserTopicNum(final int userId) {
        // 调用存储过程
        String sql = "{call P_GET_TOPIC_NUM(?,?)}";

        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;

    }

BLOB/CLOB类型数据的操作

LOB代表大数据对象，其中BLOB用于存储大块的二进制数据例如图片、视频，CLOB用于存储长文本数据例如帖子。
数据库内部对应的类型是：Oracle对应BLOB/CLOB，MySQL对应的是BLOB/longtext。
longtext操作起来和简单类型一样，但是用户可能用流的方式操作LOB类型的数据。

写 BLOB/CLOB类型

例：
--- Post.java
包含多个属性，例如
    // 在数据库中postText是longtext（相当于CLOB），postAttach是BLOB
    private String postText;
    private byte[] postAttach;

--- PostDao.java

import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;

import chapter11.domain.Post;

@Repository
public class PostDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private LobHandler lobHandler;
    @Autowired
    private DataFieldMaxValueIncrementer incre; 

    public void addPost(final Post post){
        String sql = " INSERT INTO t_post(postId,userId,postText,postAttach)"
                   + " 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()); 
                    // 设置CLOB和BLOB字段
                    lobCreator.setClobAsString(ps, 3, post.getPostText());
                    lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());
                }
            });

    }

}

注意点：一是类的标注 @Repository，二是主键自增的写法，三是CLOB和BLOB字段

--- 配置文件

    <context:component-scan base-package="chapter11" /> 

    <context:property-placeholder location="classpath:jdbc.properties" />
    <bean id="dataSource" class="org.apache.commons.dbcp2.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"/>

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

    <bean id="nativeJdbcExtractor"      class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
        lazy-init="true" />

    <bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
        lazy-init="true" />

    <!-- 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"/>

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

    <bean id="bbtForum" class="chapter11.service.JdbcBbtForum"
          p:forumDao-ref="forumDao"/>

测试：
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext.xml"})
@TransactionConfiguration
@Transactional
public class MyTest {

    @Autowired
    private PostDao postDao;

    @Test
    @Rollback(false)
    public void testAddPost() throws Throwable{
        // 创建post
        Post post = new Post();
        post.setUserId(2);
        //  将图片转化为字节
        ClassPathResource res = new ClassPathResource("temp.jpg");
        byte[] mockImg = FileCopyUtils.copyToByteArray(res.getFile());
        post.setPostAttach(mockImg);
        post.setPostText("ceshi ");
        // 上面新建post时还没有对postId赋值默认初始值，在addPost方法中才对postId赋值
        postDao.addPost(post);
    }

}
注意点：第一行的@RunWith(SpringJUnit4ClassRunner.class)容易丢导致出错

读BLOB、CLOB数据：
（1）以块数据方式读取Lob数据
以String读取Clob字段，以byte[]读取Blob字段

// 在PostDao类中加上方法
// 查看用户发表的所有帖子
    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;
                    }
                });
    }

（2）以流数据方式读取Lob数据
如果数据很大，可以用流数据方式读取

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<Object>() {

            // 以流的方式处理LOB字段
            @Override
            protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
                // TODO Auto-generated method stub
                InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1);
                if(is!=null)
                    FileCopyUtils.copy(is, os);

            }

            protected void handleNoRowFound() throws LobRetrievalFailureException {
                System.out.println("Not Found result!");
            }
        });
    }