Hi,
I have 2 tables with one to one relationships. But when saving the objects, I always got errors saying:
Quote:
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 'desc, id) values ('Test description here', 37)' at line 1
Tables: Test and TestInfo
Code:
DROP TABLE IF EXISTS `testing`.`test`;
CREATE TABLE `testing`.`test` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(45) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `testing`.`test_info`;
CREATE TABLE `testing`.`test_info` (
`id` int(10) unsigned NOT NULL default '0',
`desc` varchar(45) default NULL,
PRIMARY KEY (`id`),
CONSTRAINT `FK_test_info_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Here are the mapping files
Code:
<hibernate-mapping>
<class name="org.test.model.Test" table="test" catalog="testing">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="native"></generator>
</id>
<property name="name" type="java.lang.String">
<column name="name" length="45" />
</property>
<one-to-one name="testInfo" class="org.test.model.TestInfo" cascade="all"></one-to-one>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class name="org.test.model.TestInfo" table="test_info" catalog="testing">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="foreign">
<param name="property">test</param>
</generator>
</id>
<one-to-one name="test" class="org.test.model.Test" constrained="true"></one-to-one>
<property name="desc" type="java.lang.String">
<column name="desc" length="45" />
</property>
</class>
</hibernate-mapping>
Test.java - getters and setters are omitted
Code:
public class Test implements java.io.Serializable {
private Integer id;
private String name;
private TestInfo testInfo;
public Test() {
}
public Test(String name, TestInfo testInfo) {
this.name = name;
this.testInfo = testInfo;
}
......
}
TestInfo.java - getters and setters are emitted.
Code:
public class TestInfo implements java.io.Serializable {
private Integer id;
private Test test;
private String desc;
public TestInfo() {
}
public TestInfo(Test test) {
this.test = test;
}
public TestInfo(Test test, String desc) {
this.test = test;
this.desc = desc;
}
......
}
DAO:
Code:
public class TestDAO {
public void save(Test test)
{
Session session=HibernateSessionFactory.getSession();
Transaction tx=session.beginTransaction();
session.save(test);
tx.commit();
}
}
addTest servlet
Code:
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Test test = new Test();
Date date = new Date(System.currentTimeMillis());
test.setName(date.toString());
TestInfo testInfo = new TestInfo();
testInfo.setDesc("Test description here");
test.setTestInfo(testInfo);
testInfo.setTest(test);
TestDAO testDAO = new TestDAO();
testDAO.save(test);
}
HibernateSessionFactory.java is automatically generated by MyEclipse IDE. So i am not posting it here. But if it's relevant, i will post later on request.
If I took out the cascade = "all" attribute from <one-to-one> in Test.hbm.xml, errors would not show up. However new records will not insert into TestInfo table. I really need to use the cascade attribute and insert both table together.
I have used P6Spy and SQL Profiler to monitor the SQL statements. I got these:
Quote:
2009.09.25 04:04:53.250 47 1 statement insert into testing.test_info (desc, id) values (?, ?) insert into testing.test_info (desc, id) values ('Test description here', 37)
2009.09.25 04:04:53.000 0 1 statement insert into testing.test (name) values (?) insert into testing.test (name) values ('Fri Sep 25 16:04:52 BST 2009')
The SQL looks correct, and the stack trace points to the insert statement of testing.test_info.
The exception stack trace is here:
Quote:
SEVERE: Servlet.service() for servlet addTest threw exception
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 uk.ac.nesc.TestDAO.save(TestDAO.java:14)
at uk.ac.nesc.addTest.doPost(addTest.java:69)
at uk.ac.nesc.addTest.doGet(addTest.java:44)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
at java.lang.Thread.run(Thread.java:619)
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 'desc, id) values ('Test description here', 37)' at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1666)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1082)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
... 24 more
Could anyone please help me to point out the problem? Many thanks in advance.
Cheers,
Nathan
Login
FAQ
Search