JdbcDaoSupport 操作CLOB、BLOB数据字段

一，首先在配置文件中注入

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

 

 

二，直接使用getJdbcTemplate().execute来使用Lobcreator

@Repository

public class ArticleDAO extends BaseDAO{

    @Resource

    private LobHandler lobHandler;

 

    /**

     * 对去重后数据进行持久化

     * @param map

     */

    @Transactional(propagation=Propagation.REQUIRED)

    public void insertArticls(final NewsArticle atc) {

        

        String showSql = "insert into tb_temp(tbid,title,publishTime,fromUrl,source,author,crawled_from,imgs,videos,content) " +

                " values(?,?,?,?,?,?,?,?,?,?)";

   

        

        getJdbcTemplate().execute(showSql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {

            

            @Override

            protected void setValues(PreparedStatement ps, LobCreator creator)

                    throws SQLException, DataAccessException {

                ps.setLong(1, getTableId("SEQ_TB_TEMP_TBID"));

                ps.setString(2, atc.getTitle());

                ps.setString(3, atc.getPublishTime());

                ps.setString(4, atc.getFromUrl());

                ps.setString(5, atc.getSource());

                ps.setString(6, atc.getAuthor());

                ps.setString(7, atc.getCrawled_from());

                ps.setString(8, atc.getImgs());

                ps.setString(9, atc.getVideos());

                creator.setClobAsString(ps, 10, atc.getContent()); //放在最后

                

            }

        });

        

          }

}

 

 

三，如果是批量操作，使用LobHandler来得到LobCreator

public class ArticleDAO extends BaseDAO{

    @Resource

    private LobHandler lobHandler;

 

    /**

     * 对去重后数据进行持久化

     * @param map

     */

    @Transactional(propagation=Propagation.REQUIRED)

    public void insertArticls(final Vector<NewsArticle> atc) {

        

        final String showSql = "insert into tb_temp(tbid,title,publishTime,fromUrl,source,author,crawled_from,imgs,videos,content) " +

                " values(?,?,?,?,?,?,?,?,?,?)";

        BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {

            public void setValues(PreparedStatement ps, int i)

                    throws SQLException {

                    LobCreator creator = lobHandler.getLobCreator();

                    ps.setLong(1, getTableId("SEQ_TB_TEMP_TBID"));

                    ps.setString(2, atc.get(i).getTitle());

                    ps.setString(3, atc.get(i).getPublishTime());

                    ps.setString(4, atc.get(i).getFromUrl());

                    ps.setString(5, atc.get(i).getSource());

                    ps.setString(6, atc.get(i).getAuthor());

                    ps.setString(7, atc.get(i).getCrawled_from());

                    ps.setString(8, atc.get(i).getImgs());

                    ps.setString(9, atc.get(i).getVideos());

                    creator.setClobAsString(ps, 10, atc.get(i).getContent());

            }

            public int getBatchSize() {

                return atc.size();

            }

        };

        

        this.getJdbcTemplate().batchUpdate(showSql, setter);

    }

}