随笔 - 117  文章 - 72  trackbacks - 0

声明:原创作品(标有[原]字样)转载时请注明出处,谢谢。

常用链接

常用设置
常用软件
常用命令
 

订阅

订阅

留言簿(7)

随笔分类(130)

随笔档案(123)

搜索

  •  

积分与排名

  • 积分 - 141065
  • 排名 - 389

最新评论

[标题]:向MySQL数据库插入Blob数据的问题
[时间]:2009-6-3
[摘要]:在使用Hibernate向数据库插入Blob二进制数据时,发生如下错误:SQL Error: 1064, SQLState: 42000 。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
[关键字]:MySQL、Blob、图片、image、java、Hibernate、Clob、&
[环境]:5.1.34-community MySQL Community Server (GPL),Hibernate 3.2.5
[作者]:Winty (wintys@gmail.com) http://www.blogjava.net/wintys

[错误]:
    使用Hibernate向数据库插入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写入的图片数据换成文本,却可以正常写入。可见,是二进制数据的编码问题。

[解决]:
    将原来的数据连接:
<property name="connection.url">
    jdbc:mysql://localhost:3306/db
</property>
    修改成:
<property name="connection.url">
    <![CDATA[jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf-8]]>
</property>
    注意,在将连接字符串放到CDATA中,因为&是XML中的转义字符。不然会提示错误:
    Error parsing XML: /hibernate.cfg.xml(12) The reference to entity "characterEncoding" must end with the ';' delimiter.

    也可以直接把&修改为&amp;
    即:
    jdbc:mysql://localhost:3306/db?useUnicode=true&amp;characterEncoding=utf-8

[参考资料]:
Mysql 中的blob相关问题 : http://fenghuang.javaeye.com/blog/363931
posted on 2009-06-03 23:45 天堂露珠 阅读(3640) 评论(1)  编辑  收藏 所属分类: Error

FeedBack:
# re: [原]向MySQL数据库插入Blob数据的问题 2014-05-20 17:27 moyue
赞一个!  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航: