mysql插入blob报错_[原]向MySQL数据库插入Blob数据的问题

public void insert() {

User user = new User();

Transaction tc = null;

try{

Session session = HibernateUtil.getSession();

tc = session.beginTransaction();

user.setName("The Name");

FileInputStream fin = new FileInputStream("rc/redheart.gif");

Blob image = Hibernate.createBlob(fin);

user.setImage(image);

File file = new File("rc/news.txt");

FileReader fr = new FileReader(file);

BufferedReader br = new BufferedReader(fr);

Clob info = Hibernate.createClob(br , (int)file.length());

user.setInfo(info);

session.save(user);

tc.commit();

}catch(Exception e){

if(tc != null){

tc.rollback();

}

System.err.println(e.getMessage());

}finally{

HibernateUtil.closeSession();

}

}

发生如下错误:

Hibernate: insert into db.myblobclob (name, image, info, id) values (?, ?, ?, ?)

00:33:45,671  WARN JDBCExceptionReporter:77 - SQL Error: 1064, SQLState: 42000

00:33:45,671 ERROR JDBCExceptionReporter:78 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??^5b??08""199G?"0Px8=?ü??Y??ó??l%P?[

¨???ó`-??F????:???S?a?@??Zu??' at line 1

00:33:45,687 ERROR AbstractFlushingEventListener:301 - Could not synchronize database state with session

org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)

at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)

at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)

at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)

at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)

at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)

at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)

at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)

at wintys.hibernate.blobclob.UserDAOBean.insert(UserDAOBean.java:41)

at wintys.hibernate.blobclob.UserTest.main(UserTest.java:18)

Caused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??^5b??08""199G?"0Px8=?ü??Y??ó??l%P?[

¨???ó`-??F????:???S?a?@??Zu??' at line 1

at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1693)

at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)

at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)

at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)

... 9 more

Could not execute JDBC batch update

Hibernate: select user0_.id as id0_, user0_.name as name0_, user0_.image as image0_, user0_.info as info0_ from db.myblobclob user0_

[原因]:

搜索了一下，错误原因可能为:"在定义字段时，不要和MYSQL的保留字段有相同的"。

检查了一下表中的字段名，没有发现问题:

CREATE TABLE myblobclob(

id          VARCHAR(100) NOT NULL,

name    VARCHAR(100),

image   BLOB,

info       TEXT,

PRIMARY KEY(id)

);

如果把Blob相关的程序注释了，Clob数据能够正常写入。原因当然出在Blob数据的写入程序中。后来发现，把Blob写入的图片数据换成文本，却可以正常写入。可见，是二进制数据的编码问题。

[解决]:

将原来的数据连接:

jdbc:mysql://localhost:3306/db

修改成:

注意，在将连接字符串放到CDATA中，因为&是XML中的转义字符。不然会提示错误:

Error parsing XML: /hibernate.cfg.xml(12) The reference to entity "characterEncoding" must end with the ';' delimiter.

也可以直接把&修改为&

即:

jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf-8

[参考资料]:

Mysql 中的blob相关问题 : http://fenghuang.javaeye.com/blog/363931

posted on 2009-06-03 23:45 天堂露珠 阅读(3703) 评论(1)  编辑  收藏 所属分类: Error