今天放假前最后一天上班整理代码时候,发现了使用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请自己找吧,抱歉。
全文全。本篇博文是我下午整理代码时候弄的,例子很简单,代码本人亲测通过,如果您觉得有什么不对的地方,欢迎指出,期待各位的留言。
相关推荐
接下来是一个具体的示例,展示了如何使用JDBC操作包含Blob和Clob字段的数据库表。 1. **创建包含Blob和Clob字段的表**: ```java String url = "jdbc:derby:clobberyclob;create=true"; Class.forName("org....
在Java编程中,当需要与数据库交互并处理这些大数据类型时,JDBC提供了接口和方法来操作Blob和Clob。 这篇博客文章(链接已提供)可能详细介绍了如何在Java应用程序中有效地使用JDBC来处理Blob和Clob对象。通常,这...
这是因为 WebLogic 服务器为了更好地管理和操作数据库连接,会使用自己的包装类 `weblogic.jdbc.wrapper.Clob_oracle_sql_CLOB` 来表示 CLOB 类型的数据,而不是直接使用 Oracle 提供的标准 `oracle.sql.CLOB` 类。...
JDBC读写Oracle的CLOB字段
对CLOB和BLOB数据类型的操作是非常重要的,特别是在使用Java数据库连接(JDBC)时。本文将详细介绍JDBC实现对CLOB和BLOB数据类型的操作,包括读取和写入操作。 CLOB数据类型 CLOB数据类型用于存储大型字符数据,如...
spring 中对大数据的处理,包括clob,blob的数据。比之jdbc下简便很多。
以上是Oracle Blob和Clob处理的一些核心知识点,实际应用中还需要结合具体的编程语言(如Java, PL/SQL等)和框架来编写代码实现文件上传下载、大数操作等功能。在处理大对象时,需要充分考虑性能、存储和安全等因素...
4. **使用JDBC操作`BLOB`** - 处理`BLOB`字段类似,但通常不需要插入空值。例如: ```java conn.setAutoCommit(false); String sql = "insert into photo(name,photo) values('jack',empty_blob())"; pstmt = ...
本篇文章将围绕"load blob clob"这一主题,结合anysql免费工具合集,详细介绍如何在Oracle中操作Blob和Clob字段,并分享一些实用技巧。 首先,Blob数据类型在Oracle中代表Binary Large Object,它可以存储任意形式...
使用JDBC(Java Database Connectivity)API建立与数据库的连接。首先,需要引入数据库驱动,例如MySQL、Oracle或PostgreSQL的驱动。然后,使用`DriverManager.getConnection()`方法创建连接。 2. **准备SQL语句**...
2. 配置Hibernate的JDBC连接,使用支持Clob和Blob高效操作的Oracle JDBC驱动。 3. 利用Hibernate提供的API进行增删改查操作,无需手动处理Clob和Blob的具体存储细节。 在实际开发中,了解并掌握这些知识点可以帮助...
在JDBC(Java Database Connectivity)中,我们可以使用特定的方法来操作这些类型的数据。 首先,我们需要建立与数据库的连接。在示例代码中,`JDBCUtils.getConnection()` 方法用于获取数据库连接。这个方法通常会...
标题"Clob-blob.rar_blob and clob_clob_java CLOB_java oracle cl_oracle"暗示了这个压缩包包含的资源是关于使用Java操作Oracle数据库中的CLOB和BLOB字段的示例代码。这个压缩包可能包含了一个名为`clob-blob.java`...
总的来说,Spring通过JDBC提供了一套完善的接口和工具类来处理CLOB和BLOB,使得开发者无需直接与JDBC API打交道,降低了数据库操作的复杂度。在实际项目中,根据业务需求,合理运用这些功能可以极大地提高开发效率和...
在数据库管理中,存储非结构化数据如图片、音频或视频文件时,通常会使用`CLOB`(Character Large Object)和`BLOB`(Binary Large Object)这两种数据类型。Oracle数据库系统支持这两种数据类型,用于存储大量文本...
在Java中,Blob和Clob是JDBC API提供的接口,但在Hibernate中,我们可以通过Session对象的save()或saveOrUpdate()方法来操作这些大数据对象。 首先,我们需要在实体类中定义对应的属性。例如,对于一个包含图片信息...