I am new to hibernate and an exception org.springframework.jdbc.BadSqlGrammarException when i am writing my function test case.

In my test case, i create a Person object and call dao to persist the data. But I got follow error.

Hibernate: select nextval ('hibernate_sequence')
Hibernate: /* insert com.slin.core.server.pojo.Person */ insert into person (date_Created, date_Updated, date_Last_Login, display_Name, date_Suspended, id) values (?, ?, ?, ?, ?, ?)
SEVERE: Batch entry 0 /* insert com.slin.core.server.pojo.Person */ insert into person (date_Created, date_Updated, date_Last_Login, display_Name, date_Suspended, id) values (2005-09-29 23:30:27.375000-0400, NULL, NULL, stevenlin, NULL, 21) was aborted. Call getNextException to see the cause.
SEVERE: ERROR: column "date_created" of relation "person" does not exist

following is sql which generate by the hibernate but for some reason, postgresql doesn't like the syntax. it looks completely valid to me. anybody knows y?

into person (date_Created, date_Updated, date_Last_Login, display_Name, date_Suspended, id) values (2005-09-29 23:30:27.375000-0400, NULL, NULL, stevenlin, NULL, 21)

i compile the sql in postgre admin tool and go following error message.
ERROR: syntax error at or near "23" at character 119

then i edit the sql and compiled it with no problem.

insert into person ("date_Created", "date_Updated", "date_Last_Login", "display_Name", "date_Suspended", "id") values (CURRENT_DATE, NULL, NULL, 'stevenlin', NULL, 15)

i am also new to postgresql, so i don't know why postgre required column name to be qouted? anybody knows why?


Following is my set for the table and configuration.

CREATE TABLE "public"."person" (
"id" INTEGER NOT NULL,
"date_Created" DATE NOT NULL,
"date_Updated" DATE,
"date_Last_Login" DATE,
"display_Name" VARCHAR NOT NULL,
"date_Suspended" DATE,
CONSTRAINT "user_pkey" PRIMARY KEY("id")
) WITH OIDS;


<?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" >
<hibernate-mapping package="com.core.server.pojo">

<class name="Person" table="person">
<id name="id" column="id" type="java.lang.Integer">
<generator class="native"/>
</id>

<property name="dateCreated" column="date_Created" type="java.util.Date" not-null="true" />
<property name="dateUpdated" column="date_Updated" type="java.util.Date" />
<property name="dateLastLogin" column="date_Last_Login" type="java.util.Date" />
<property name="displayName" column="display_Name" type="java.lang.String" not-null="true" />
<property name="dateSuspended" column="date_Suspended" type="java.util.Date" />

<set name="authProfileSet" inverse="true">
<key column="userId"/>
<one-to-many class="AuthProfile"/>
</set>

<set name="userAddressSet" inverse="true">
<key column="user_Id"/>
<one-to-many class="UserAddress"/>
</set>

<set name="userInfoSet" inverse="true">
<key column="user_Id"/>
<one-to-many class="UserInfo"/>
</set>

<set name="userAssociationSet" inverse="true">
<key column="source_id"/>
<one-to-many class="UserAssociation"/>
</set>

<set name="userAssociation1Set" inverse="true">
<key column="target_id"/>
<one-to-many class="UserAssociation"/>
</set>

<set name="userGroupMembershipSet" inverse="true">
<key column="user_id"/>
<one-to-many class="UserGroupMembership"/>
</set>

<set name="userGroupMembership1Set" inverse="true">
<key column="created_by"/>
<one-to-many class="UserGroupMembership"/>
</set>
</class>

</hibernate-mapping>

I'm no expert on Postgres, but I have noticed that where the pgadmin tool is used to create tables, if you make any characters in the table name upper case, it will take that literally and create the table with the specified case in its name. Subsequently, you must match this case in queries for them to work. But unquoted identifiers in queries seem to be interpretted as lower-case unless you quote them.

I ran into this program when I created all my tables with all upper-case letters in pgadmin, and ended up dropping and recreating the tables to end the aggravation. But there is a configuration option in your hibernate mapping files that will tell hibernate to quote identifiers. See this to get you started:

http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#mapping-quotedidentifiers

just in case you might not know, EMS postgre admin tool is much better than postgreadmin. check it out.

Since you quoted the identifiers in the DDL (create table statement), you must also quote them in the DML (insert statement). To tell Hibernate to quote identifiers, use backticks in the mapping.

ie. column="`date_Created`"