Hello all,

I got the error message when I save a object to table. It seems that the error is rellated to the sql statement generated by Hibernate, as follows:

Hibernate: select bbill0_.id as id2_, bbill0_.calldate as calldate2_, bbill0_.src as src2_, bbill0_.english_name as english4_2_, bbill0_.dst as dst2_, bbill0_.calltype as calltype2_, bbill0_.billtype as billtype2_, bbill0_.billsec as billsec2_, bbill0_.fee as fee2_, bbill0_.desc as desc2_ from test.tb_bbill bbill0_
query Bbill all, size=0
Hibernate: select max(id) from tb_bbill
Hibernate: insert into test.tb_bbill (calldate, src, english_name, dst, calltype, billtype, billsec, fee, desc, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
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:202)
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:297)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:333)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at test.TestBbill.main(TestBbill.java:47)
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 ('2009-02-02 17:06:22', '801', 'Aiqinwang', '802', 'intercom', ' at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1257)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:943)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
... 8 more


I use MySQL 5, the table is as follow:


use test;

DROP TABLE IF EXISTS `tb_bbill`;
CREATE TABLE `tb_bbill` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`calldate` datetime ,
`src` varchar(30),
`english_name` varchar(40) NOT NULL,
`dst` varchar(30) NOT NULL,
`calltype` varchar(30) NOT NULL,
`billtype` varchar(30) NOT NULL,
`billsec` INT NOT NULL ,
`fee` FLOAT(10,2) NOT NULL,
`desc` varchar(30) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


the Java Object is as follow: Bbill.java

package mypackage.hibernate.vo;

import java.util.Date;
/**
* Bbill entity. @author MyEclipse Persistence Tools
*/

public class Bbill implements java.io.Serializable {


// Fields

private Long id;
private Date calldate;
private String src;
private String englishName;
private String dst;
private String calltype;
private String billtype;
private Integer billsec;
private Float fee;
private String desc;


// Constructors

/** default constructor */
public Bbill() {
}

/** minimal constructor */
public Bbill(Date calldate, String src, String englishName, String dst, String calltype, String billtype, Integer billsec, Float fee) {
this.calldate = calldate;
this.src = src;
this.englishName = englishName;
this.dst = dst;
this.calltype = calltype;
this.billtype = billtype;
this.billsec = billsec;
this.fee = fee;
}

/** full constructor */
public Bbill(Date calldate, String src, String englishName, String dst, String calltype, String billtype, Integer billsec, Float fee, String desc) {
this.calldate = calldate;
this.src = src;
this.englishName = englishName;
this.dst = dst;
this.calltype = calltype;
this.billtype = billtype;
this.billsec = billsec;
this.fee = fee;
this.desc = desc;
}


// Property accessors

public Long getId() {
return this.id;
}

public void setId(Long id) {
this.id = id;
}

public Date getCalldate() {
return this.calldate;
}

public void setCalldate(Date calldate) {
this.calldate = calldate;
}

public String getSrc() {
return this.src;
}

public void setSrc(String src) {
this.src = src;
}

public String getEnglishName() {
return this.englishName;
}

public void setEnglishName(String englishName) {
this.englishName = englishName;
}

public String getDst() {
return this.dst;
}

public void setDst(String dst) {
this.dst = dst;
}

public String getCalltype() {
return this.calltype;
}

public void setCalltype(String calltype) {
this.calltype = calltype;
}

public String getBilltype() {
return this.billtype;
}

public void setBilltype(String billtype) {
this.billtype = billtype;
}

public Integer getBillsec() {
return this.billsec;
}

public void setBillsec(Integer billsec) {
this.billsec = billsec;
}

public Float getFee() {
return this.fee;
}

public void setFee(Float fee) {
this.fee = fee;
}

public String getDesc() {
return this.desc;
}

public void setDesc(String desc) {
this.desc = desc;
}

}


the mapping file is as follow: Bbill.hbm.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="mypackage.hibernate.vo.Bbill" table="tb_bbill" catalog="test">
<id name="id" type="java.lang.Long">
<column name="id" />
<generator class="increment"></generator>
</id>
<property name="calldate" type="java.util.Date">
<column name="calldate" length="0" not-null="true" />
</property>
<property name="src" type="java.lang.String">
<column name="src" length="30" not-null="true" />
</property>
<property name="englishName" type="java.lang.String">
<column name="english_name" length="40" not-null="true" />
</property>
<property name="dst" type="java.lang.String">
<column name="dst" length="30" not-null="true" />
</property>
<property name="calltype" type="java.lang.String">
<column name="calltype" length="30" not-null="true" />
</property>
<property name="billtype" type="java.lang.String">
<column name="billtype" length="30" not-null="true" />
</property>
<property name="billsec" type="java.lang.Integer">
<column name="billsec" not-null="true" />
</property>
<property name="fee" type="java.lang.Float">
<column name="fee" precision="10" not-null="true" />
</property>
<property name="desc" type="java.lang.String">
<column name="desc" length="30" />
</property>
</class>
</hibernate-mapping>



My test code is as follow:

package test;

import java.util.Date;
import java.util.List;

import mypack.hibernate.HibernateSessionFactory;
import mypackage.hibernate.vo.Bbill;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class TestBbill
{
public static void main(String[] args)
{

//find all
String hql = "from Bbill";
Query queryObject = HibernateSessionFactory.getSession().createQuery(hql);
List<Bbill> list = queryObject.list();

System.out.println("query Bbill all, size=" +list.size());
for(int i=0; i<list.size();i++)
{
Bbill bbill = (Bbill)list.get(i);
System.out.println(bbill.getId());
System.out.println(bbill.getCalldate());
System.out.println(bbill.getEnglishName());
}

Bbill bbill = new Bbill();
bbill.setCalldate(new Date());
bbill.setSrc("801");
bbill.setEnglishName("Aiqinwang");
bbill.setDst("802");
bbill.setCalltype("intercom");
bbill.setBilltype("Long distance");
bbill.setBillsec(new Integer(60));
bbill.setFee(new Float(12.99f));
bbill.setDesc("HELLO");


Session session = HibernateSessionFactory.getSession();
Transaction tx = session.beginTransaction();
session.save(bbill);
tx.commit();


System.out.println(" Done");

}

}



It might related to the value I used to initiliaze bbill object, but i can't figure out what exactly it is.

Pls help, Thanks in advance!

Lucy

'desc' is a reserved word. If you want to use it as a column name it must be quoted with backticks. Eg:

hi,

I changed the table field `desc` to `remarks`, after doing that verything is fine.


I guess `desc`might be a keyword of SQL.



Lucy

Hi nordborg ,

You are right. I solved the problem before I see your post. But thank you very much , anyway!


Lucy

I still face another two problems with this case, my test code is following:


package test;

import bill.hibernate.Dao;
import bill.hibernate.mapping.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.Date;
import java.util.List;

public class TestBill
{

/**
* @param args
*/
public static void main(String[] args)
{

Dao dao = Dao.getInstance();

List list = dao.queryBillAll();
System.out.println("query Bill all, size=" +list.size());
for(int i=0; i<list.size();i++)
{
Bill bill = (Bill)list.get(i);
System.out.println(bill.getCalldate().toString());
}

Bill bill = new Bill();

//bill.setCalldate(new Date());

try {
DateFormat df = DateFormat.getDateInstance();
bill.setCalldate(df.parse("2009-01-31 00:00:10"));

} catch (ParseException e) {
e.printStackTrace();
}

bill.setSrc("617");
bill.setEnglishName("Aiqinwang");
bill.setDst("13397164148");
bill.setCalltype("outbound");
bill.setBilltype("long distance");
bill.setBillsec(new Integer(100));
bill.setFee(new Float(12.1f));
bill.setRemarks("domestic");


dao.saveObject(bill);

System.out.println( "Done");
}

}


first I save some objects with bill.setCalldate(new Date());

then I change the code to

try {
DateFormat df = DateFormat.getDateInstance();
bill.setCalldate(df.parse("2009-01-31 00:00:10"));

} catch (ParseException e) {
e.printStackTrace();
}

I check the MYSQL database, and found the calldate is
2009-01-31 00:00:00, not 2009-01-31 00:00:10, why this happen?


the other problem is the code generates the following output:

Hibernate: select bill0_.id as id5_, bill0_.calldate as calldate5_, bill0_.src as src5_, bill0_.english_name as english4_5_, bill0_.dst as dst5_, bill0_.calltype as calltype5_, bill0_.billtype as billtype5_, bill0_.billsec as billsec5_, bill0_.fee as fee5_, bill0_.remarks as remarks5_ from bill.tb_bill bill0_
query Bill all, size=5
2009-02-02 19:26:32.0
2009-02-02 19:26:50.0
2009-02-02 19:27:26.0
2009-02-02 19:27:59.0
2009-01-31 00:00:00.0
Hibernate: select max(id) from tb_bill
Hibernate: insert into bill.tb_bill (calldate, src, english_name, dst, calltype, billtype, billsec, fee, remarks, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Done


the statement System.out.println(bill.getCalldate().toString());
generate an extra '.0' at the end, ie: 2009-02-02 19:26:32.0

How to solve?

Thanks

Lucy

use

DateFormat df = DateFormat.getDateTimeInstance();

can solve the problem of 2009-01-31 00:00:00

The extra '0' is for the milliseconds. You can also use your Dateformat to format the time as you want:


You can customize your format using SimpleDateFormat. See herefor examples.