空间站

package com.baobaotao.dao.jdbc;
            import java.sql.Connection;
            import COM.ibm.db2.jdbc.net.DB2Connection;
            import org.springframework.jdbc.core.support.JdbcDaoSupport;
            import org.springframework.jdbc.datasource.DataSourceUtils;
            public class PostJdbcDao extends JdbcDaoSupport implements PostDao {
            public void getNativeConn(){
            try {
            Connection conn = DataSourceUtils.getConnection(getJdbcTemplate()
            .getDataSource()); ① 使用 DataSourceUtils 从模板类中获取连接
            ② 使用模板类的本地 JDBC 抽取器获取本地的 Connection
            conn = getJdbcTemplate().getNativeJdbcExtractor().getNativeConnection(conn);
            DB2Connection db2conn = (DB2Connection) conn; ③ 这时可以强制进行类型转换了
            …
            } catch (Exception e) {
            e.printStackTrace();
            }
            }
            }
            

…
            <bean id="dataSource" class="org.apache.commons.dbcp.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>
            ① 定义 DBCP 数据源的 JDBC 本地对象抽取器
            <bean id="nativeJdbcExtractor"
            class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
            lazy-init="true" />
            <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource" />
            ② 设置抽取器
            <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
            </bean>
            <bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao">
            <property name="jdbcTemplate" ref="jdbcTemplate" />
            </bean>
            

package com.baobaotao.dao.jdbc;
            …
            import java.sql.PreparedStatement;
            import java.sql.SQLException;
            import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
            import org.springframework.jdbc.support.lob.LobCreator;
            import org.springframework.jdbc.support.lob.LobHandler;
            public class PostJdbcDao extends JdbcDaoSupport implements PostDao {
            private LobHandler lobHandler; ① 定义 LobHandler 属性
            public LobHandler getLobHandler() {
            return lobHandler;
            }
            public void setLobHandler(LobHandler lobHandler) {
            this.lobHandler = lobHandler;
            }
            public void addPost(final Post post) {
            String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"
            + " VALUES(?,?,?,?)";
            getJdbcTemplate().execute(sql,
            new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) { ②
            protected void setValues(PreparedStatement ps,LobCreator lobCreator)
            throws SQLException {
            ps.setInt(1, 1);
            ps.setInt(2, post.getUserId());
            ③ 设置 CLOB 字段
            lobCreator.setClobAsString(ps, 3, post.getPostText());
            ④ 设置 BLOB 字段
            lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());
            }
            });
            }
            …
            }
            

…
            <bean id="nativeJdbcExtractor"
            class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
            lazy-init="true"/>
            <bean id="oracleLobHandler"
            class="org.springframework.jdbc.support.lob.OracleLobHandler"
            lazy-init="true">
            <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/> ① 设置本地 Jdbc 对象抽取器
            </bean>
            <bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao">
            <property name="lobHandler" ref="oracleLobHandler"/> ② 设置 LOB 处理器
            </bean>
            

<bean id="defaultLobHandler"
            class="org.springframework.jdbc.support.lob.DefaultLobHandler"
            lazy-init="true"/>
            <bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao">
            <property name="lobHandler" ref=" defaultLobHandler"/>
            <property name="jdbcTemplate" ref="jdbcTemplate" />
            </bean>
            

package com.baobaotao.dao.jdbc;
            import org.springframework.core.io.ClassPathResource;
            import org.springframework.test.AbstractDependencyInjectionSpringContextTests;
            import org.springframework.util.FileCopyUtils;
            import com.baobaotao.dao.PostDao;
            import com.baobaotao.domain.Post;
            public class TestPostJdbcDaoextends AbstractDependencyInjectionSpringContextTests {
            private PostDao postDao;
            public void setPostDao(PostDao postDao) {
            this.postDao = postDao;
            }
            protected String[] getConfigLocations() {
            return new String[]{"classpath:applicationContext.xml"};
            }
            public void testAddPost() throws Throwable{
            Post post = new Post();
            post.setPostId(1);
            post.setUserId(2);
            ClassPathResource res = new ClassPathResource("temp.jpg"); ① 获取图片资源
            byte[] mockImg = FileCopyUtils.copyToByteArray(res.getFile());  ② 读取图片文件的数据
            post.setPostAttach(mockImg);
            post.setPostText("测试帖子的内容");
            postDao.addPost(post);
            }
            }
            

public List getAttachs(final int userId){
            String sql = "SELECT post_id,post_attach FROM t_post “+
            “where user_id =? and post_attach is not null ";
            return getJdbcTemplate().query(
            sql,new Object[] {userId},
            new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            int postId = rs.getInt(1);
            ① 以二进制数组方式获取 BLOB 数据。
            byte[] attach = lobHandler.getBlobAsBytes(rs, 2);
            Post post = new Post();
            post.setPostId(postId);
            post.setPostAttach(attach);
            return post;
            }
            });
            }
            

…
            public void getAttach(final int postId,final OutputStream os){ ① 用于接收 LOB 数据的输出流
            String sql = "SELECT post_attach FROM t_post WHERE post_id=? ";
            getJdbcTemplate().query(
            sql, new Object[] {postId},
            new AbstractLobStreamingResultSetExtractor() { ② 匿名内部类
            ③ 处理未找到数据行的情况
            protected void handleNoRowFound() throws LobRetrievalFailureException {
            System.out.println("Not Found result!");
            }
            ④ 以流的方式处理 LOB 字段
            public void streamData(ResultSet rs) throws SQLException, IOException {
            InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1);
            if (is != null) {
            FileCopyUtils.copy(is, os);
            }
            }
            }
            );
            }
            

package com.baobaotao.domain;
            …
            import javax.persistence.Basic;
            import javax.persistence.Lob;
            import javax.persistence. Column;
            @Entity(name = "T_POST")
            public class Post implements Serializable {
            …
            @Lob ①-1 表示该属性是 LOB 类型的字段
            @Basic(fetch = FetchType.EAGER) ①-2 不采用延迟加载机制
            @Column(name = "POST_TEXT", columnDefinition = "LONGTEXT NOT NULL") ①-3 对应字段类型
            private String postText;
            @Lob ②-1 表示该属性是 LOB 类型的字段
            @Basic(fetch = FetchType. LAZY) ②-2 采用延迟加载机制
            @Column(name = "POST_ATTACH", columnDefinition = "BLOB") ②-3 对应字段类型
            private byte[] postAttach;
            …
            ｝
            

<?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
            "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
            <hibernate-mapping auto-import="true" default-lazy="false">
            <class name="com.baobaotao.domain.Post" table="t_post">
            <id name="postId" column="post_id">
            <generator class="identity" />
            </id>
            <property name="userId" column="user_id"/>
            <property name="postText" column="post_text"
            type="org.springframework.orm.hibernate3.support.ClobStringType"/>①对应 CLOB 字段
            <property name="postAttach" column="post_attach"
            type="org.springframework.orm.hibernate3.support.BlobByteArrayType"/>② BLOB 字段
            <property name="postTime" column="post_time" type="date" />
            <many-to-one name="topic" column="topic_id" class="com.baobaotao.domain.Topic" />
            </class>
            </hibernate-mapping>
            

…
            <bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
            lazy-init="true" />
            <bean id="sessionFactory"
            class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
            <property name="lobHandler" ref="lobHandler" /> ① 设置 LOB 处理器
            …
            </bean>
            

<?xml version="1.0" encoding="UTF-8" ?>
            <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
            "http://ibatis.apache.org/dtd/sql-map-2.dtd">
            <sqlMap namespace="Post">
            <typeAlias alias="post" type="com.baobaotao.domain.Post"/>
            <resultMap id="result" class="post">
            <result property="postId" column="post_id"/>
            <result property="userId" column="user_id"/>
            <result property="postText" column="post_text" ① 读取 CLOB 类型数据
            typeHandler="org.springframework.orm.ibatis.support.ClobStringTypeHandler"/>
            <result property="postAttach" column="post_attach" ② 读取 BLOB 类型数据
            typeHandler="org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler"/>
            </resultMap>
            <select id="getPost" resultMap="result">
            SELECT post_id,user_id,post_text,post_attach,post_time
            FROM t_post  WHERE post_id =#postId#
            </select>
            <insert id="addPost">
            INSERT INTO t_post(user_id,post_text,post_attach,post_time)
            VALUES(#userId#,
            #postText,handler=org.springframework.orm.ibatis.support.ClobStringTypeHandler#, ③
            #postAttach,handler=org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler#, ④
            #postTime#)
            </insert>
            </sqlMap>
            

<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
            lazy-init="true" />
            <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
            <property name="dataSource" ref="dataSource" />
            <property name="lobHandler" ref="lobHandler" /> ①设置LobHandler
            <property name="configLocation"
            value="classpath:com/baobaotao/dao/ibatis/sql-map-config.xml" />
            </bean>