Hi,
I'm having a problem inserting into a MySQL 5.0.27 database on Windows. I'm pretty new to Hibernate and MySQL, so I likely overlooked something in configuration. I've had no luck searching the FAQs, so hopefully someone can help here.
It seems that MySQL always refuses the SQL generated by Hibernate. However, if I copy and paste the SQL into a MySQL terminal and preface the column names with the table name (as in "route_by_originator.key"), the SQL is accepted.
I've tried installing MySQL with both strict SQL activated and deactivated, but I get the same results either way.
I've also tried the MySQLDialect, the MySQL5Dialect, and the MySQL5InnoDBDialect. They all seem to have the same issue.
Is there a way to configure hibernate to include the table name before the column name? Or does anyone know how to configure MySQL to be a bit more relaxed?
Thanks for your help.
Hibernate version: 3.2.0 GA
Mapping documents:
hibernate.cfg.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="show_sql">true</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.password">XXX</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/dojesb</property>
<property name="hibernate.connection.username">flindet</property>
<property name="hibernate.default_schema">dojesb</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
</session-factory>
</hibernate-configuration>
RouteByOriginator.hbm.xml
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="RouteByOriginator"
table="route_by_originator">
<id name="id"
column="id">
<generator class="increment" />
</id>
<property
name="key"
column="key" />
<property
name="senderClass"
column="sender_class" />
</class>
</hibernate-mapping>
Code:
Transaction tx = session.beginTransaction();
RouteByOriginator route = new RouteByOriginator();
route.setKey("CJIS");
route.setSenderClass("CjisMessageSender.class");
session.save(route);
tx.commit();
Full stack trace of any exception that occurs:Code:
Exception in thread "main" 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:249)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
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 Router.main(Router.java:28)
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 'key, sender_class, id) values ('CJIS', 'CjisMessageSender.class', 1)' at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1103)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:853)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
... 8 more
Name and version of the database you are using:MySQL 5.0.27
The generated SQL (show_sql=true):Code:
Hibernate: insert into dojesb.route_by_originator (key, sender_class, id) values (?, ?, ?)
Debug level Hibernate log excerpt:Code:
08:26:19,646 DEBUG SessionImpl:84 - opened session at timestamp: 11679279795
08:26:19,646 DEBUG JDBCTransaction:84 - begin
08:26:19,646 DEBUG ConnectionManager:84 - opening JDBC connection
08:26:19,646 DEBUG JDBCTransaction:84 - current autocommit status: false
08:26:19,656 DEBUG IncrementGenerator:84 - fetching initial value: select max(id) from dojesb.route_by_originator
08:26:19,656 DEBUG AbstractBatcher:84 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
08:26:19,656 DEBUG SQL:84 - select max(id) from dojesb.route_by_originator
Hibernate: select max(id) from dojesb.route_by_originator
08:26:19,736 DEBUG IncrementGenerator:84 - first free id: 1
08:26:19,736 DEBUG AbstractBatcher:84 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
08:26:19,736 DEBUG AbstractSaveEventListener:84 - generated identifier: 1, using strategy: org.hibernate.id.IncrementGenerator
08:26:19,766 DEBUG JDBCTransaction:84 - commit
08:26:19,766 DEBUG AbstractFlushingEventListener:84 - processing flush-time cascades
08:26:19,766 DEBUG AbstractFlushingEventListener:84 - dirty checking collections
08:26:19,766 DEBUG AbstractFlushingEventListener:84 - Flushed: 1 insertions, 0 updates, 0 deletions to 1 objects
08:26:19,766 DEBUG AbstractFlushingEventListener:84 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
08:26:19,776 DEBUG Printer:84 - listing entities:
08:26:19,776 DEBUG Printer:84 - RouteByOriginator{key=CJIS, senderClass=CjisMessageSender.class, id=1}
08:26:19,776 DEBUG AbstractBatcher:84 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
08:26:19,776 DEBUG SQL:84 - insert into dojesb.route_by_originator (key, sender_class, id) values (?, ?, ?)
Hibernate: insert into dojesb.route_by_originator (key, sender_class, id) values (?, ?, ?)
... [Exception here] ...
08:26:19,816 DEBUG AbstractBatcher:84 - Executing batch size: 1
08:26:19,816 DEBUG AbstractBatcher:84 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
08:26:19,826 DEBUG JDBCExceptionReporter:89 - Could not execute JDBC batch update [insert into dojesb.route_by_originator (key, sender_class, id) values (?, ?, ?)]
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 'key, sender_class, id) values ('CJIS', 'CjisMessageSender.class', 1)' at line 1
Login
FAQ
Search