<?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.xsd">
    <!-- 扫描类包，将标注Spring注解的类自动转化Bean，同时完成Bean的注入 -->
    <context:component-scan base-package="com.xgj.dao.lob.nativeConn" />
    <!-- 不使用context命名空间，则需要定义Bean 
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
        <property name="locations" value="classpath:spring/jdbc.properties" /> 
    </bean> 
    -->
    <!-- 使用context命名空间，同上面的Bean等效.在xml文件中配置数据库的properties文件 -->
    <context:property-placeholder location="classpath:spring/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}" />
    <!-- 定义DBCP数据源的JDBC本地对象抽取器 -->
    <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"></bean>
    <!-- 配置Jdbc模板   设置抽取器 -->
    <bean id="jdbcTemplate"  lazy-init="true"
          class="org.springframework.jdbc.core.JdbcTemplate"
          p:dataSource-ref="dataSource"
          p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/>
</beans>

JdbcTemplate#execute(String sql,AbstractLobCreatingPreparedStatementCallback lcpsc) 

package com.xgj.dao.lob.dao;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import com.xgj.dao.lob.domain.Artisan;
/**
 * 
 * 
 * @ClassName: ArtisanLobDaoImp
 * 
 * @Description: @Repository标注的DAO层
 * 
 * @author: Mr.Yang
 * 
 * @date: 2017年9月28日 下午8:15:23
 */
@Repository
public class ArtisanLobDaoImp implements ArtisanLobDao {
    // 定义jdbcTemplate属性
    private JdbcTemplate jdbcTemplate;
    // 定义LobHander属性
    private LobHandler lobHandler;
    private static final String addArtisanLobSql = "insert into artisan_lob(artisan_id ,artisan_detail ,artisan_attach) values (?,?,?)";
    // 注入jdbcTemplate
    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    // 注入lobHandler
    @Autowired
    public void setLobHandler(LobHandler lobHandler) {
        this.lobHandler = lobHandler;
    }
    @Override
    public void addArtisanLob(final Artisan artisan) {
        jdbcTemplate.execute(addArtisanLobSql,
                new AbstractLobCreatingPreparedStatementCallback(
                        this.lobHandler) {
                    @Override
                    protected void setValues(PreparedStatement ps,
                            LobCreator lobCreator) throws SQLException,
                            DataAccessException {
                        // 设置ID
                        ps.setString(1, artisan.getArtisanId());
                        // 设置 CLOB 字段
                        lobCreator.setClobAsString(ps, 2,
                                artisan.getArtisanDetail());
                        // 设置 BLOB 字段
                        lobCreator.setBlobAsBytes(ps, 3,
                                artisan.getArtisanAttach());
                    }
                });
    }
}
/**
 * 
 * 
 * JdbcTemplate 中execute和update的区别：
 * 
 * execute不接受参数，无返回值，适用于create和drop table。
 * 
 * update可以接受参数，返回值为此次操作影响的记录数，适合于insert, update, 和delete等操作。
 * 
 */

<?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.xsd">
    <!-- 扫描类包，将标注Spring注解的类自动转化Bean，同时完成Bean的注入 -->
    <context:component-scan base-package="com.xgj.dao.lob" />
    <!-- 使用context命名空间 配置数据库的properties文件 -->
    <context:property-placeholder location="classpath:spring/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}" />
    <!--  nativeJdbcExtractor 和 oracleLobHandler Bean 都设置为 lazy-init="true"，
           这是因为 nativeJdbcExtractor 需要通过运行期的反射机制获取底层的 JDBC 对象，所以需要避免在 Spring 容器启动时就实例化这两个 Bean -->
    <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
          lazy-init="true"/>
    <bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" 
          lazy-init="true"
          p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/> <!-- 设置本地 Jdbc 对象抽取器 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="dataSource"/>
</beans>

// 注入lobHandler
    @Autowired
    public void setLobHandler(LobHandler lobHandler) {
        this.lobHandler = lobHandler;
    }

package com.xgj.dao.lob.dao;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.UUID;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.util.FileCopyUtils;
import com.xgj.dao.lob.domain.Artisan;
public class ArtisanDaoLobTest {
    ClassPathXmlApplicationContext ctx = null;
    @Before
    public void initContext() {
        // 启动Spring 容器
        ctx = new ClassPathXmlApplicationContext(
                "classpath:com/xgj/dao/lob/lobOperation.xml");
        System.out.println("initContext successfully");
    }
    @Test
    public void testCallProcWithCallableStatementCreator() throws IOException {
        ArtisanLobDaoImp artisanLobDaoImp = ctx.getBean("artisanLobDaoImp",
                ArtisanLobDaoImp.class);
        // 实例化Artisan
        Artisan artisan = new Artisan();
        // (maven工程资源放在了 resource同名目录下)
        // 设置主键
        artisan.setArtisanId(UUID.randomUUID().toString());
        // 读取文本文件，设置给artisanDetail
        ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        Resource resource = resourcePatternResolver
                .getResource("classpath:com/xgj/dao/lob/dao/artisanDetail.txt");
        InputStream ins = resource.getInputStream();
        // 将 InputStream 转成String
        // Scanner scanner = new Scanner(ins, "GB2312");
        // String artisanDetail = scanner.useDelimiter("\\A").next();
        String artisanDetail = inputStream2String(ins).toString();
        // 设置给artisanDetail
        artisan.setArtisanDetail(artisanDetail);
        // 读取图片信息，设置给artisanAttach
        ClassPathResource res = new ClassPathResource(
                "com/xgj/dao/lob/dao/281015.jpg");
        // 读取图片数据
        byte[] artisanAttach = FileCopyUtils.copyToByteArray(res
                .getInputStream());
        artisan.setArtisanAttach(artisanAttach);
        artisanLobDaoImp.addArtisanLob(artisan);
        System.out.println("ADD  BLOB  SUCCESSFULLY ");
        System.out.println("artisan ID:\n" + artisan.getArtisanId());
        System.out.println("artisan detail:\n" + artisan.getArtisanDetail());
        System.out.println("artisan attach length:\n"
                + artisan.getArtisanAttach().length);
    }
    @After
    public void closeContext() {
        if (ctx != null) {
            ctx.close();
        }
        System.out.println("close context successfully");
    }
    public StringBuilder inputStream2String(InputStream ins) throws IOException {
        StringBuilder stringBuilder = new StringBuilder();
        BufferedReader bufferedReader = new BufferedReader(
                new InputStreamReader(ins));
        boolean firstLine = true;
        String line = null;
        while ((line = bufferedReader.readLine()) != null) {
            if (!firstLine) {
                stringBuilder.append(System.getProperty("line.separator"));
            } else {
                firstLine = false;
            }
            stringBuilder.append(line);
        }
        return stringBuilder;
    }
}

@Test
    public void getArtisanListById() {
        List<Artisan> artisans = artisanLobDaoImp
                .selectArtisanById("15e396c9-9151-4689-bc4f-e1a7ba5bd55c");
        for (Artisan artisan : artisans) {
            System.out
                    .println("artisan detail:\n" + artisan.getArtisanDetail());
            System.out.println("artisan attach length:\n"
                    + artisan.getArtisanAttach().length);
        }
    }

/**
     * 
     * 
     * @Title: getAttach
     * 
     * @Description: 以流数据方式读取 LOB 数据
     * 
     * 
     * @return: void
     */
    public void getAttach(final String artisanId, final OutputStream os) {
        jdbcTemplate.query(selectAttachByIdSql, new Object[] { artisanId },
                new AbstractLobStreamingResultSetExtractor<Artisan>() { // 匿名内部类
                    // 处理未找到数据行的情况
                    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);
                        }
                    }
                });
    }

@Test
    public void getArtisanAttache() throws FileNotFoundException {
        // 定义输出目的
        OutputStream os = new FileOutputStream(new File("D:/downLoad.jpg"));
        artisanLobDaoImp.getAttach("15e396c9-9151-4689-bc4f-e1a7ba5bd55c", os);
    }