[標題]:向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.tkk7.com/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.
也可以直接把&修改為&
即:
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
天堂露珠 閱讀(3982)
評論(1) 編輯 收藏 所屬分類:
Error