BLOB数据类型文件[PDF]的存取(Spring+Mybatis+Db2+Maven)

本文的业务需求是将一个html文件以BLOB数据类型存入DB2数据库中，并将其取出存入一个简单的pdf文件中。才疏学浅，大神勿喷！

1、准备数据库DDL，并展示工程结构。

CREATE TABLE BLOB_FIELD
 ("ID"              VARCHAR(64)        NOT NULL,
  "TAB_NAME"        VARCHAR(64)        NOT NULL,
  "TAB_PKID_VALUE"  VARCHAR(64)        NOT NULL,
  "BLOB_COL_NAME"   VARCHAR(64)        NOT NULL,
  "BLOB_COL_VALUE"  BLOB(1048576)      NOT NULL  NOT LOGGED  NOT COMPACT  INLINE LENGTH 164
 );
ALTER TABLE BLOB_FIELD ADD CONSTRAINT "PK_BLOB_FIELD" PRIMARY KEY ("ID" );

2、POM文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.project</groupId>
  <artifactId>db2-blob</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <properties>
  	<spring.version>4.2.5.RELEASE</spring.version>
  	<mybatis.version>3.4.0</mybatis.version>
  	<mybatis-spring.version>1.3.0</mybatis-spring.version>
  	<db2jcc4.version>10.1</db2jcc4.version>
  	<druid.version>1.0.16</druid.version>
  	<itext.version>4.2.2</itext.version>
  	<commons-io.version>2.4</commons-io.version>
  	<log4j.version>1.2.17</log4j.version>
  	<junit.version>4.11</junit.version>
  </properties>
  <dependencies>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis</artifactId>
		<version>${mybatis.version}</version>
	</dependency>
	<dependency>
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis-spring</artifactId>
		<version>${mybatis-spring.version}</version>
	</dependency>
	<!-- db2 驱动 -->
	<dependency>
	    <groupId>com.ibm.db2.jcc</groupId>
	    <artifactId>db2jcc4</artifactId>
	    <version>${db2jcc4.version}</version>
	</dependency>
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid</artifactId>
		<version>${druid.version}</version>
	</dependency>
	<dependency>
	    <groupId>com.lowagie</groupId>
	    <artifactId>itext</artifactId>
	    <version>${itext.version}</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
	<dependency>
	    <groupId>commons-io</groupId>
	    <artifactId>commons-io</artifactId>
	    <version>${commons-io.version}</version>
	</dependency>
	<dependency>
		<groupId>log4j</groupId>
		<artifactId>log4j</artifactId>
		<version>${log4j.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-test</artifactId>
		<version>${spring.version}</version>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<version>${junit.version}</version>
		<scope>test</scope>
	</dependency>
  </dependencies>
  
</project>

3、spring.xml

<?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:context="http://www.springframework.org/schema/context" 
	xmlns:task="http://www.springframework.org/schema/task"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
						http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
						http://www.springframework.org/schema/context
	   					http://www.springframework.org/schema/context/spring-context-3.2.xsd">
    <context:component-scan base-package="com.project.db2.blob"></context:component-scan>
    <import resource="classpath:spring/spring-*.xml" />
	
</beans>

4、spring-mybatis.xml

<?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:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop" 
	xsi:schemaLocation="http://www.springframework.org/schema/beans
						http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
						http://www.springframework.org/schema/tx
						http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
						http://www.springframework.org/schema/context
						http://www.springframework.org/schema/context/spring-context-3.2.xsd
						http://www.springframework.org/schema/aop 
						http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
	<!-- 引入配置文件 -->
    <context:property-placeholder location="classpath:config/jdbc.properties"/>   

	<!-- 基于Druid数据库链接池的数据源配置 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <!-- 基本属性driverClassName、 url、user、password -->
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" /> 
    </bean> 
      
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="classpath:mybatis/mybatis-config.xml" />
		<property name="mapperLocations" value="classpath:mybatis/mapper/*Mapper.xml" /> 
	</bean>
	
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.project.db2.blob.dao" />
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
	</bean>
	<tx:annotation-driven transaction-manager="transactionManager"/> 
	
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"></property>
	</bean>	
</beans>

5、mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- default settings -->
<settings>
  <setting name="cacheEnabled" value="false"/>
  <setting name="lazyLoadingEnabled" value="false"/>
</settings>
	<typeAliases>
		<package name="com.project.db2.blob.entity"/>
 	</typeAliases>
 	
</configuration>

6、BlobFieldMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.project.db2.blob.dao.BlobFieldDao" >
  <resultMap id="BaseResultMap" type="BlobField" >
    <id column="ID" property="id" jdbcType="VARCHAR" />
    <result column="TAB_NAME" property="tabName" jdbcType="VARCHAR" />
    <result column="TAB_PKID_VALUE" property="tabPkidValue" jdbcType="VARCHAR" />
    <result column="BLOB_COL_NAME" property="blobColName" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="BlobField" extends="BaseResultMap" >
    <result column="BLOB_COL_VALUE" property="blobColValue" jdbcType="BLOB" />
  </resultMap>
  <sql id="Base_Column_List" >
    ID, TAB_NAME, TAB_PKID_VALUE, BLOB_COL_NAME
  </sql>
  <sql id="Blob_Column_List" >
    BLOB_COL_VALUE
  </sql>
  <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from BLOB_FIELD
    where ID = #{id,jdbcType=VARCHAR}
  </select>
  
  <insert id="insertSelective" parameterType="BlobField" >
    insert into BLOB_FIELD
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >ID,</if>
      <if test="tabName != null" >TAB_NAME,</if>
      <if test="tabPkidValue != null" >TAB_PKID_VALUE,</if>
      <if test="blobColName != null" >BLOB_COL_NAME,</if>
      <if test="blobColValue != null" >BLOB_COL_VALUE,</if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >#{id,jdbcType=VARCHAR},</if>
      <if test="tabName != null" >#{tabName,jdbcType=VARCHAR},</if>
      <if test="tabPkidValue != null" >#{tabPkidValue,jdbcType=VARCHAR},</if>
      <if test="blobColName != null" >#{blobColName,jdbcType=VARCHAR},</if>
      <if test="blobColValue != null" >#{blobColValue,jdbcType=BLOB},</if>
    </trim>
  </insert>
  
</mapper>

7、BlobFieldController.java

package com.project.db2.blob.controller;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.FileCopyUtils;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfWriter;
import com.project.db2.blob.entity.BlobField;
import com.project.db2.blob.service.BlobFieldService;

@Controller
public class BlobFieldController {

	@Autowired
	private BlobFieldService blobFieldService;
	
	public void addBlobField(){
		InputStream is = null;
		try {
		   is = Thread.currentThread().getContextClassLoader().getResourceAsStream("other/test.html");
		   byte[] bytes = FileCopyUtils.copyToByteArray(is);
		   BlobField blobField = new BlobField();
		   blobField.setId("20161210120131001");
		   blobField.setTabName("TAL_ATTACH");
		   blobField.setTabPkidValue("20161210115231001");
		   blobField.setBlobColName("attachment");
		   blobField.setBlobColValue(bytes);
		   //保存blob字段
		   blobFieldService.insertBlobField(blobField);
		 } catch (IOException e) {
		   e.printStackTrace();
		 } finally {
		   IOUtils.closeQuietly(is);
		 }
	}
	
	public void selectBlobField(String id) throws FileNotFoundException, DocumentException{
		
		BlobField blobField = blobFieldService.getBlobFieldById(id);
		Document document = new Document(PageSize.A4, 50, 50, 50, 50);
		PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream("D:\\ITextTest.pdf"));
		document.open();
		document.add(new Paragraph(new String(blobField.getBlobColValue())));
		document.close();
	}
}

8、BlobFieldTest.java

package com.project.db2.blob.test;

import java.io.FileNotFoundException;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.itextpdf.text.DocumentException;
import com.project.db2.blob.controller.BlobFieldController;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/spring.xml")
public class BlobFieldTest {
	
	@Autowired
	private BlobFieldController blobFieldController;
	
	@Test
	public void test01(){
		blobFieldController.addBlobField();
	}
	
	@Test
	public void test02() throws FileNotFoundException, DocumentException{
		blobFieldController.selectBlobField("20161210120131002");
	}
}

9、BlobField.java （getter()/setter()缺省）

public class BlobField {
	private String id;
	private String tabName;// 表名
	private String tabPkidValue;// 主键值
	private String blobColName;// 列名
	private byte[] blobColValue;// 列值 clob类型
}