当前位置:首页 > 开发 > 编程语言 > 编程 > 正文

使用Jdbc4操作Blob,Clob

发表于: 2014-01-27   作者:53873039oycg   来源:转载   浏览:
摘要:       今天放假前最后一天上班整理代码时候,发现了使用jdbc4操作blob的一段代码,而是把电脑里面所有操作blob的代码片段找出来测试下,就有了上面的博文题目,特此说明,我的代码不知道是从那些博文里攫取的,所以本篇博客也算不上原创,如果无意中摘取了您博文中的片段,请留言,我会把原链接加上去。谢谢。   &nbs

      今天放假前最后一天上班整理代码时候,发现了使用jdbc4操作blob的一段代码,而是把电脑里面所有操作blob的代码片段找出来测试下,就有了上面的博文题目,特此说明,我的代码不知道是从那些博文里攫取的,所以本篇博客也算不上原创,如果无意中摘取了您博文中的片段,请留言,我会把原链接加上去。谢谢。
     下面开始介绍下如何使用Jdbc4,Hibernate 4操作blob,clob。
      环境:我使用的是oracle数据库。
     Jdbc4是什么东西我就不介绍了,有兴趣的请自行谷歌,如何看使用的架包是否是jdbc4呢?打开odbc.jar,可以看到一个Manifest.MF文件,打开可以看到里面有一行Specification-Version: 4.0,有着一行就说明你可以使用jdbc4的特性了。
     首先在oracle数据库中新建一个表,如下:
    

create table T_BLOB_TEST
(
  ID      NUMBER(4) not null,
  IMAGE   BLOB,
  CONTENT CLOB
);

alter table T_BLOB_TEST
  add constraint PK_T_TEST_BLOB_ID primary key (ID);

 
    在建一个序列,这个不是必选项,可以不建。
   

create sequence STUDENT_ID_SEQUENCE
minvalue 1
maxvalue 999
start with 206
increment by 1
cache 20;

   
   (一)使用Jdbc4操作Blob,Clob
   下面开始使用Jdbc4往表里面插数据了。
    先写一个简单的获取连接的静态方法.
  

public static Connection getConnection() throws Exception {
        Connection con = null;
        // 注册JDBC驱动类
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe", "tmd", "tmd");
        return con;
    }

 
    先写插入数据的方法,Blob类型如图片的可以直接读取到byte数组中,Clob类型的如文本文件需读取到String变量中,所以要有一个读取文本文件为String的方法,如下:
      

public static String readContent(File file) throws Exception {
        InputStreamReader read = new InputStreamReader(
                new FileInputStream(file), "utf-8");// 考虑到编码格式
        StringBuffer result = new StringBuffer((int) file.length());
        BufferedReader bufferedReader = new BufferedReader(read);
        String lineTxt = null;
        while ((lineTxt = bufferedReader.readLine()) != null) {
            result.append(lineTxt);
        }
        return result.toString();
    }

 
    重点来了,插入方法如下所示:
   

public static void jdbcInsertBlobTest(Connection conn, long id,
            String imgPath, String filePath) throws Exception {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn
                    .prepareStatement("insert into t_blob_test(id,image,content) values (?,?,?)");
            pstmt.setLong(1, id);
            File f = new File(imgPath);
            Blob blob = conn.createBlob();
            InputStream in = new FileInputStream(f);
            //这个值应该至少是1
            OutputStream out = blob.setBinaryStream(1);
            byte[] temp = new byte[(int) f.length()];
            int length;
            while ((length = in.read(temp)) != -1) {
                out.write(temp, 0, length);
            }
            pstmt.setBlob(2, blob);
            f = new File(filePath);
            Clob clob = conn.createClob();
            String content = readContent(f);
            clob.setString(1, content);
            pstmt.setClob(3, clob);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
        }
    }
 测试方法如下:
   
Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            jdbcInsertBlobTest(conn, 4L, "F:/saveFile/pic/test4.jpg","f:/saveFile/pic/system.log");
        } finally {
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }

     插入了怎么把数据库里的数据拉到本地来呢?可以这样做。
    

public static void jdbcJqueryBlobTest(PreparedStatement pstmt, long id,
            String filePath) throws Exception {
        ResultSet rs = null;
        OutputStream out = null;
        InputStream in = null;
        try {
            pstmt.setLong(1, id);
            rs = pstmt.executeQuery();
            /*
             * if(rs.next()){ Blob blob=rs.getBlob(1); blob.getBinaryStream();
             * out=new FileOutputStream(filePath); out.write(blob.getBytes(1l,
             * (int) blob.length())); out.close(); }
             */
            if (rs.next()) {
                in = rs.getBinaryStream("image");
                out = new FileOutputStream(filePath + "result_img.jpg");
                byte[] buffer = new byte[1024];// 每次读取1k
                for (int len = 0; (len = in.read(buffer)) > 0;) {
                    out.write(buffer, 0, len);
                }

                Clob clob = rs.getClob("content");
                getClobToFile(filePath + "result_clob.log", clob);
            }
        } finally {
            if (out != null) {
                out.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
    }

       注意上面注释的一段可是可以运行的,是另一种更简单的方法。
       删除方法很简单,如下所示:
    

pstmt = conn.prepareStatement("delete from t_blob_test where id=?");
                    
public static void jdbcDeleteBlobBeanById(PreparedStatement pstmt,Long id)throws Exception{
        pstmt.setLong(1, id);
        pstmt.executeUpdate();
    }

 
     使用Jdbc操作blob,clob已经写完了,请自行忽略上面有些单词写错了,今天有点不在状态,不想改了。
  
    -------------------------------------------我是分割线-----------------------------------------------------------------
    (二)使用Hibernate4操作Blob,Clob
    为什么要使用Hibernate4呢?因为我什么都想用最新的,但是今天在使用Hibernate4的时候就掉坑里去了,后面会说到,说明,我使用的Hibernate4版本是Hibernate 4.1.3的,在最新的Hibernate 4.3下面没测试过,我不保证下面的代码能在Hibernate 4.3下运行良好。
     先建JavaBean,这是我的习惯。
   

package com.bean;

import java.io.Serializable;
import java.sql.Blob;
import java.sql.Clob;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;
@Entity
@Table (name= "t_blob_test")
public class BlobBean implements Serializable{
    private static final long serialVersionUID = 1L;
    @GenericGenerator(name = "generator", strategy = "increment")
    @Id
    @GeneratedValue(generator = "generator")
    
    /*@Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "IdSeq")
    @SequenceGenerator(name="IdSeq", sequenceName="STUDENT_ID_SEQUENCE")*/
    private long id;
    private Blob image;
    private Clob content;
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public Blob getImage() {
        return image;
    }

    public void setImage(Blob image) {
        this.image = image;
    }

    public Clob getContent() {
        return content;
    }

    public void setContent(Clob content) {
        this.content = content;
    }

}

 
    如果你建立过序列,可以把Id上面的注释换成我注释掉的,否则就用我上面的,看个人爱好了。
    先写个获取Session的工具类,如下:
  

package com.hibernate.util;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class HibernateUtil {
    private static final SessionFactory sessionFactory;
    static {
        try {
            Configuration cfg = new Configuration()
                    .configure("resources/hibernate.cfg.xml");
            ServiceRegistry serviceRegistry = new ServiceRegistryBuilder()
                    .applySettings(cfg.getProperties()).buildServiceRegistry();

            sessionFactory = cfg.buildSessionFactory(serviceRegistry);
        } catch (Throwable e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    private HibernateUtil() {
    }

    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    /**
     * 获取session对象
     * 
     * @return
     */
    public static Session openSession() {
        Session session = null;
        if (null == session || false == session.isOpen()) {
            session = sessionFactory.openSession();
        }
        return session;
    }

    public static void closeSession(Session session) {
        try {
            if (null != session && session.isOpen()) {
                session.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

 
    然后就是Hibernate的配置文件了,我的配置文件放在resources下面。
   

<?xml version='1.0' encoding='UTF-8'?>  
<!DOCTYPE hibernate-configuration PUBLIC  
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"  
          "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- 数据库的驱动 -->
        <property name="connection.driver_class">
            oracle.jdbc.driver.OracleDriver
        </property>
        <!-- 数据库的URL -->
        <property name="connection.url">
            jdbc:oracle:thin:@localhost:1521:xe
        </property>
        <!-- 数据库的用户名 -->
        <property name="connection.username">tmd</property>
        <!-- 数据库的密码 -->
        <property name="connection.password">tmd</property>
        <!-- 数据库的方言 -->
        <property name="hibernate.dialect">
            org.hibernate.dialect.Oracle10gDialect
        </property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- 显示操作的sql语句 -->
        <property name="hibernate.show_sql">true</property>
        <!-- 格式sql语句 -->
        <property name="hibernate.format_sql">false</property>
        <!-- 自动创建和更新表结构 -->
        <property name="hibernate.hbm2ddl.auto">update</property>
        <!-- 映射文件引入 -->
        <mapping class="com.bean.BlobBean" />
    </session-factory>
</hibernate-configuration>

 
     然后是接口:
   

import com.bean.BlobBean;

public interface HibernateDao {
    public void saveBlobBean(BlobBean bean);
    
    public void deleteBlobBean(Long id);
    
    public BlobBean getBlobBeanById(Long id);
}
 实现类:
   
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.bean.BlobBean;
import com.hibernate.dao.HibernateDao;
import com.hibernate.util.HibernateUtil;

public class HibernateDaoImpl implements HibernateDao{

    public void saveBlobBean(BlobBean bean) {
        // 获取session对象
        Session session = HibernateUtil.openSession();
        // 打开事务
        Transaction ts = session.beginTransaction();
        try {
            // 保存
            session.save(bean);
            // 提交事务
            ts.commit();
        } catch (Exception e) {
            ts.rollback();
        }finally{
            HibernateUtil.closeSession(session);
        }
    }
    
    public void deleteBlobBean(Long id) {
        // 获取session对象
        Session session = HibernateUtil.openSession();
        // 打开事务
        Transaction ts = session.beginTransaction();
        try {
            // 保存
            session.delete(getBlobBeanById(id));
            // 提交事务
            ts.commit();
        } catch (Exception e) {
            ts.rollback();
        }finally{
            HibernateUtil.closeSession(session);
        }
    }

    
    public BlobBean getBlobBeanById(Long id) {
        Session session = HibernateUtil.openSession();
        BlobBean blobBean = null;
        Transaction ts = session.beginTransaction();
        try {
            blobBean = (BlobBean) session.get(BlobBean.class, id);
            ts.commit();
        } catch (Exception e) {
            ts.rollback();
        }
        return blobBean;
    }
}

     测试类,我就不分析了,全部贴出来
   

public class HibernateBlobBeanTest {
    private HibernateDao dao = new HibernateDaoImpl();
   
     public static void main(String[] args) throws Exception {
         HibernateBlobBeanTest t=new HibernateBlobBeanTest();
        // t.saveBlobBeanTest();
        // t.saveBlobBeanMethod2Test();
        // t.getBlobBeanTest(150L);
         //t.deleteBlobBeanTest(152L);
    }
    
    public void saveBlobBeanTest() throws Exception {
        BlobBean blobBean = new BlobBean();
        File file = new File("F:/saveFile/pic/test4.jpg");
        FileInputStream fis = new FileInputStream(file);
        File file1 = new File("f:/saveFile/pic/system.log");
        Reader reader = new FileReader(file1);
        Session session = HibernateUtil.openSession();
        blobBean.setImage(Hibernate.getLobCreator(session).createBlob(fis,
                file.length()));
        blobBean.setContent(Hibernate.getLobCreator(session).createClob(reader,
                file1.length()));
        dao.saveBlobBean(blobBean);
        HibernateUtil.closeSession(session);
    }

    public void saveBlobBeanMethod2Test() throws Exception {
        BlobBean blobBean = new BlobBean();
        File file = new File("F:/saveFile/pic/test4.jpg");
        FileInputStream fis = new FileInputStream(file);
        File file1 = new File("f:/saveFile/pic/system.log");
        Reader reader = new FileReader(file1);
        Session session = HibernateUtil.openSession();
        blobBean.setImage(session.getLobHelper().createBlob(fis, file.length()));
        blobBean.setContent(session.getLobHelper().createClob(reader,
                file1.length()));
        dao.saveBlobBean(blobBean);
        HibernateUtil.closeSession(session);
    }

    public void getBlobBeanTest(Long id) throws Exception {
        BlobBean blobBean = dao.getBlobBeanById(id);
        if (blobBean != null) {
            Blob blob = blobBean.getImage();
            // 根据blob对象的getBinaryStream()方法 获取输入流 对象
            InputStream is = blob.getBinaryStream();
            // 定义写出的文件
            File file = new File("f:/saveFile/pic/result_img.jpg");
            // 写出的输出流
            FileOutputStream fos = new FileOutputStream(file);
            // 缓冲区
            byte[] buffer = new byte[1024];
            // 读取的长度
            int len = 0;
            // 循环读取,直到文件结尾
            while ((len = is.read(buffer)) != -1) {
                // 写出
                fos.write(buffer, 0, len);
            }
            // 关闭流
            fos.close();
            is.close();

            // 获取Clob字段
            Clob clob = blobBean.getContent();
            // 根据clob对象的getCharacterStream() 获取字符输入流
            Reader r = clob.getCharacterStream();
            // 定义写出的文件
            File file1 = new File("f:/saveFile/pic/result_txt.log");
            // 创建输出流对象
            FileWriter fileWriter = new FileWriter(file1);
            // 缓冲区
            char[] cbuf = new char[1024];

            // 读取长度
            int len1 = 0;
            // 循环读取,直到文件结尾
            while ((len1 = r.read(cbuf)) != -1) {
                // 写出
                fileWriter.write(cbuf, 0, len1);
            }
            // 关闭流
            fileWriter.close();
            r.close();
        }
    }

    public void deleteBlobBeanTest(Long id) throws Exception {
        dao.deleteBlobBean(id);
    }

}

 
  使用Hibernate4操作Blob,Clob例子完。
 
    -------------------------------------我是分割线----------------------------------------------------------------------------
   (三)使用Spring3结合Hibernate4操作Blob,Clob
    也许你会问,前面不是介绍了使用Hibernate4操作Blob,Clob吗?结合Spring不就是依赖Spring注入下就完了吗,我个人认为,也行你单个框架使用的很好,但多个框架结合在一起的时候就不是1+1=2这么简单了。我来说下我今天遇到的坑。

    一开始我是打算使用最新版的架包的,Spring 4我不熟,所以没用,但Hibernate4我了解过,所以我一开始使用了Spring 3.2.4+Hibernate 4.3,配置好以后,已启动就报错了,错误信息如下:
     

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'txManager' defined in class path resource [resources/applicationContext.xml]: Invocation of init method failed; nested exception is java.lang.NoSuchMethodError: org.hibernate.engine.spi.SessionFactoryImplementor.getConnectionProvider()Lorg/hibernate/service/jdbc/connections/spi/ConnectionProvider;
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1482)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:521)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:295)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:223)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:292)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:323)
    ... 59 more
Caused by: java.lang.NoSuchMethodError: org.hibernate.engine.spi.SessionFactoryImplementor.getConnectionProvider()Lorg/hibernate/service/jdbc/connections/spi/ConnectionProvider;
    at org.springframework.orm.hibernate4.SessionFactoryUtils.getDataSource(SessionFactoryUtils.java:90)
    at org.springframework.orm.hibernate4.HibernateTransactionManager.afterPropertiesSet(HibernateTransactionManager.java:335)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1541)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1479)
    ... 66 more

   
  这是Hibernate的一个bug,解决方法是把hibernate版本降到4.1,详情请    http://stackoverflow.com/questions/16417217/transactionmanager-cannot-initialize
   下面简单的介绍下Spring结合Hibernate操作Blob,Clob
    先写个公共的Dao。
   

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Projections;
import org.springframework.beans.factory.annotation.Autowired;

public abstract class AbstractBaseDao<Entity extends Serializable> {

    private Class<Entity> entityClass;

    @Autowired
    private SessionFactory sessionFactory;

    public AbstractBaseDao() {
        try {
            Type genType = getClass().getGenericSuperclass();
            Type[] params = ((ParameterizedType) genType)
                    .getActualTypeArguments();
            entityClass = (Class<Entity>) params[0];
        } catch (Exception e) {
            entityClass = null;
        }
    }

    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    public Entity findById(Serializable id) {
        return (Entity) getSession().get(entityClass, id);
    }

    public void save(Entity e) {
        getSession().save(e);
    }

    public void saveOrUpdate(Entity e) {
        getSession().saveOrUpdate(e);
    }

    public void update(Entity e) {
        getSession().update(e);
    }

    public void delete(Entity e) {
        getSession().delete(e);
    }

    public void delete(Long id) {
        getSession().delete(get(id));
    }

    public Entity get(Long id) {
        return (Entity) getSession().get(entityClass, id);
    }

    public List<Entity> listAll() {
        Criteria criteria = getSession().createCriteria(entityClass);
        criteria.setProjection(Projections.rowCount());
        return criteria.list();
    }

    public List<Entity> listAll(int pageNum) {
        return listAll(pageNum, PageUtil.DEFAULT_PAGE_SIZE);
    }

    public List<Entity> listAll(int pageNum, int pageSize) {
        Criteria criteria = getSession().createCriteria(entityClass);
        criteria.setFirstResult(PageUtil.getPageStart(pageNum, pageSize));
        return criteria.list();
    }

    public List<Entity> query(String hql, Object[] args) {
        Query query = getSession().createQuery(hql);
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                query.setParameter(i, args[i]);
            }
        }
        return query.list();
    }
}

 
   具体的Dao如下,接口我就不写了。
   

import org.springframework.stereotype.Repository;

import com.bean.BlobBean;
import com.common.AbstractBaseDao;
import com.dao.BlobDao;
@Repository
public class BlobDaoImpl extends AbstractBaseDao<BlobBean> implements BlobDao {
    public void saveBlobBean(BlobBean bean) {
        super.save(bean);
    }

    public BlobBean getBlobBean(Long id) {
        return super.get(id);
    }

    public void deleteBlobBean(Long id) {
        super.delete(id);
    }
}

 
   如何操作Blob,Clob主要在Service层方法,如下:
   

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;

import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bean.BlobBean;
import com.dao.BlobDao;
import com.service.BlobService;

@Service
@Transactional
public class BlobServiceImpl implements BlobService {
    @Autowired
    private BlobDao dao;

    @Autowired
    SessionFactory sessionFactory;

    public boolean saveBlobBean(String imagePath, String contentPath) {
        boolean flag = false;
        BlobBean blobBean = new BlobBean();
        try {
            File file = new File(imagePath);
            FileInputStream fis = new FileInputStream(file);
            File file1 = new File(contentPath);
            Reader reader = new FileReader(file1);
            blobBean.setImage(Hibernate.getLobCreator(
                    sessionFactory.getCurrentSession()).createBlob(fis,
                    file.length()));
            blobBean.setContent(Hibernate.getLobCreator(
                    sessionFactory.getCurrentSession()).createClob(reader,
                    file1.length()));
            dao.saveBlobBean(blobBean);
            flag = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    public boolean saveBlobBeanWithTx(String imagePath, String contentPath) {
        boolean flag = false;
        BlobBean blobBean = new BlobBean();
        try {
            File file = new File(imagePath);
            FileInputStream fis = new FileInputStream(file);
            File file1 = new File(contentPath);
            Reader reader = new FileReader(file1);
            // 必须开启事务
            Session session = sessionFactory.getCurrentSession();
            blobBean.setImage(session.getLobHelper().createBlob(fis,
                    file.length()));
            blobBean.setContent(session.getLobHelper().createClob(reader,
                    file1.length()));
            dao.saveBlobBean(blobBean);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    public boolean getBlobBean(Long id, String savePath) {
        boolean flag = false;
        BlobBean blobBean = dao.getBlobBean(id);
        if (blobBean == null) {
            return flag;
        }
        try {
            if (blobBean != null) {
                // 获取Blob字段
                Blob blob = blobBean.getImage();
                // 根据blob对象的getBinaryStream()方法 获取输入流 对象
                InputStream is = blob.getBinaryStream();
                // 定义写出的文件
                File file = new File(savePath + "result_img.jpg");
                // 写出的输出流
                FileOutputStream fos = new FileOutputStream(file);
                // 缓冲区
                byte[] buffer = new byte[1024];
                // 读取的长度
                int len = 0;
                // 循环读取,直到文件结尾
                while ((len = is.read(buffer)) != -1) {
                    // 写出
                    fos.write(buffer, 0, len);
                }
                // 关闭流
                fos.close();
                is.close();
                // 获取Clob字段
                Clob clob = blobBean.getContent();
                // 根据clob对象的getCharacterStream() 获取字符输入流
                Reader r = clob.getCharacterStream();
                // 定义写出的文件
                File file1 = new File(savePath + "result_clob.log");
                // 创建输出流对象
                FileWriter fileWriter = new FileWriter(file1);
                // 缓冲区
                char[] cbuf = new char[1024];
                // 读取长度
                int len1 = 0;
                // 循环读取,直到文件结尾
                while ((len1 = r.read(cbuf)) != -1) {
                    // 写出
                    fileWriter.write(cbuf, 0, len1);
                }
                // 关闭流
                fileWriter.close();
                r.close();
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    public boolean deleteBlobBean(Long id) {
        boolean flag = false;
        try {
            dao.deleteBlobBean(id);
            flag = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

}

 
   测试方法可以看我的附件。
   使用Spring结合Hibernate操作Blob,Clob例子完
 
   ------------------------------------------ 我是分割线----------------------------------------------------------------------
   (四)使用Mybatis操作Blob,Clob
    我个人习惯在使用Hibernate后喜欢看下Mybatis下怎么做,其实使用Mybatis操作Blob,Clob很简单,主要是2个转换器,官网介绍如下:

   http://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/BlobTypeHandler.html
   http://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/ClobTypeHandler.html
    也行是一段日子没用Mybatis了,今天一直在使用Blob,Clob类型测试,怎么都不成功,错误信息如下:
    

Caused by: org.apache.ibatis.reflection.ReflectionException: Could not set property 'image' of 'class com.bean.BlobBean' with value '[B@1ef3a22' Cause: java.lang.IllegalArgumentException: argument type mismatch
    at org.apache.ibatis.reflection.wrapper.BeanWrapper.setBeanProperty(BeanWrapper.java:172)
    at org.apache.ibatis.reflection.wrapper.BeanWrapper.set(BeanWrapper.java:54)
    at org.apache.ibatis.reflection.MetaObject.setValue(MetaObject.java:130)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyPropertyMappings(DefaultResultSetHandler.java:370)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:336)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:289)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:264)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:234)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:152)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:57)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)

 
     后来想起来,Mybatis配置时候是使用byte[]代表Blob,String类型代表Clob,而是修改JavaBean为:
   

import java.io.Serializable;

public class MybatisBlobBean implements Serializable {
    private static final long serialVersionUID = 1L;
    private long id;
    private byte[] image;
    private String content;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public byte[] getImage() {
        return image;
    }

    public void setImage(byte[] image) {
        this.image = image;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public static long getSerialversionuid() {
        return serialVersionUID;
    }

}

 
   主要配置如下:
   

<?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.mybatis.mapper.IBlobBeanMapper">

    <resultMap id="blobBeanResult" type="MybatisBlobBean">
        <result property="id" column="ID" />
        <result property="image" column="IMAGE" javaType="byte[]"
            jdbcType="BLOB" typeHandler="org.apache.ibatis.type.BlobTypeHandler" />
        <result property="content" column="CONTENT" javaType="java.lang.String"
            jdbcType="CLOB" typeHandler="org.apache.ibatis.type.ClobTypeHandler" />
    </resultMap>

    <select id="getMybatisBlobBeanById" parameterType="long" resultMap="blobBeanResult">
        select * from t_blob_test where id=#{id}
    </select>

    <select id="deleteMybatisBlobBeanById" parameterType="long">
        delete from
        t_blob_test where id=#{id}
    </select>

    <insert id="saveMybatisBlobBean" parameterType="java.util.Map">
        <selectKey resultType="long" keyProperty="id" order="BEFORE">   
            <![CDATA[SELECT STUDENT_ID_SEQUENCE.NEXTVAL AS ID FROM DUAL]]>
        </selectKey>
        insert into t_blob_test(id,image,content)
        values(#{id,jdbcType=NUMERIC},
        #{image,jdbcType=BLOB},
        #{content,jdbcType=CLOB}
        )
    </insert>
</mapper>

 
   测试方法如下:
   

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
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.AbstractJUnit4SpringContextTests;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.bean.MybatisBlobBean;
import com.mybatis.mapper.IBlobBeanMapper;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:com/mybatis/conf/mybatis_applicationContext.xml")
public class MybatisBlobBeanTest extends AbstractJUnit4SpringContextTests {

    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Test
    public void saveBlobBeanTest() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IBlobBeanMapper mapper = sqlSession.getMapper(IBlobBeanMapper.class);
        Map<String, Object> param = new HashMap<String, Object>();
        File file = new File("F:/saveFile/pic/test4.jpg");
        byte[] image = getBytesFromFile(file);
        file = new File("f:/saveFile/pic/system.log");
        String context = readContent(file);
        param.put("image", image);
        param.put("content", context);
        mapper.saveMybatisBlobBean(param);
    }

    @Test
    public void testGetBlobBeanById() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IBlobBeanMapper mapper = sqlSession.getMapper(IBlobBeanMapper.class);
        MybatisBlobBean blobBean = mapper.getMybatisBlobBeanById(146L);
        if (blobBean != null) {
            byte[] image = blobBean.getImage();
            // 根据blob对象的getBinaryStream()方法 获取输入流 对象
            InputStream is = new ByteArrayInputStream(image);
            // 定义写出的文件
            File file = new File("f:/saveFile/pic/result_img.jpg");
            // 写出的输出流
            FileOutputStream fos = new FileOutputStream(file);
            // 缓冲区
            byte[] buffer = new byte[1024];
            // 读取的长度
            int len = 0;
            // 循环读取,直到文件结尾
            while ((len = is.read(buffer)) != -1) {
                // 写出
                fos.write(buffer, 0, len);
            }
            // 关闭流
            fos.close();
            is.close();

            // 获取Clob字段
            String content=blobBean.getContent();
            // 根据clob对象的getCharacterStream() 获取字符输入流
            // 定义写出的文件
            File file1 = new File("f:/saveFile/pic/result_txt.log");
            OutputStreamWriter out=new OutputStreamWriter(new FileOutputStream(file1),"utf-8");
            out.write(content);
            out.close();
        }
    }

    @Test
    public void testDeleteBlobBeanById() throws Exception{
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IBlobBeanMapper mapper = sqlSession.getMapper(IBlobBeanMapper.class);
        mapper.deleteMybatisBlobBeanById(4L);
    }
    public static byte[] getBytesFromFile(File file) throws Exception {
        if (file.length() > Integer.MAX_VALUE) {
            throw new Exception("文件太大");
        }
        try {
            FileInputStream stream = new FileInputStream(file);
            ByteArrayOutputStream out = new ByteArrayOutputStream(
                    (int) file.length());
            byte[] b = new byte[(int) file.length()];
            for (int n; (n = stream.read(b)) != -1;) {
                out.write(b, 0, n);
            }
            stream.close();
            out.close();
            return out.toByteArray();
        } catch (IOException e) {
        }
        return null;
    }

    public static String readContent(File file) throws Exception {
        InputStreamReader read = new InputStreamReader(
                new FileInputStream(file), "utf-8");// 考虑到编码格式
        StringBuffer result = new StringBuffer((int) file.length());
        BufferedReader bufferedReader = new BufferedReader(read);
        String lineTxt = null;
        while ((lineTxt = bufferedReader.readLine()) != null) {
            result.append(lineTxt);
        }
        return result.toString();
    }
}

 
     其他的配置请见附件。附件没有上传Jar包,我用的是Hibernate 4.1.3+Spring .2.4+Junit 4.11+Mybatis 3.2,本来是打算上传了,Iteye上传附件上传了5分钟没反应,我的博文全丢了,只是我第二次编辑的结果,说多了都是泪,Jar请自己找吧,抱歉。
     全文全。本篇博文是我下午整理代码时候弄的,例子很简单,代码本人亲测通过,如果您觉得有什么不对的地方,欢迎指出,期待各位的留言。

使用Jdbc4操作Blob,Clob

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
这段时间经常接触到需要对clob和blob字段进行操作的情况,顺便总结一下。 很多情况下我们都需要对数
这段时间经常接触到需要对clob和blob字段进行操作的情况,顺便总结一下。 很多情况下我们都需要对数
JDBC读写Oracle10g的CLOB、BLOB 环境: Windows XP Professional 5.1 Build 2600 (Service Pack 3)
数据库脚本: create table testcb(id varchar ( 32 ) primary key ,name varchar ( 32 ),photo blo
数据库脚本: create table testcb(id varchar ( 32 ) primary key ,name varchar ( 32 ),photo blo
数据库脚本: create table testcb(id varchar ( 32 ) primary key ,name varchar ( 32 ),photo blo
数据库脚本: create table testcb(id varchar ( 32 ) primary key ,name varchar ( 32 ),photo blo
来自:http://esffor.iteye.com/blog/168264 数据库脚本: create table testcb(id varchar ( 32 )
来自:http://esffor.iteye.com/blog/168264 数据库脚本: create table testcb(id varchar ( 32 )
数据库脚本: create table testcb(id varchar ( 32 ) primary key ,name varchar ( 32 ),photo blo
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号