ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From CIJOML <cij...@volny.cz>
Subject Re: How to map Timestamp with time zone column?!
Date Tue, 18 Jul 2006 10:33:47 GMT
Hi All,

I fixed XML file, so now I have same number of columns as variables :)))

I also rewrite Kontakt.class to be unix_time as Date

Now I am trying insert this:

//KONTAKT - nejde vlozit TIME!!!
Kontakt newKontakt = new Kontakt();
newKontakt.setId_kontakt('3');
newKontakt.setId_administrator('3');
newKontakt.setId_lokace('4');
TimeZone tz = TimeZone.getTimeZone("CET");
Calendar cal = Calendar.getInstance(tz);
Date date = cal.getTime();
System.out.println(date);

newKontakt.setUnix_time(date);
newKontakt.setZaznam("Prvni zaznam");
newKontakt.setPoznamka("Fakt prvni pokus o poznamku");
sqlMap.insert("insertKontakt", newKontakt);


And now output is:

/opt/JBuilder2006/jdk1.5/bin/java -classpath "/home/cijoml/jbproject/iBATIStest/classes:/home/cijoml/jbproject/iBATIStest/ibatis-common-2.jar:/home/cijoml/jbproject/iBATIStest/ibatis-dao-2.jar:/home/cijoml/jbproject/iBATIStest/ibatis-sqlmap-2.jar:/home/cijoml/jbproject/iBATIStest/postgresql-8.1-405.jdbc2.jar:/home/cijoml/jbproject/iBATIStest/postgresql-8.1-405.jdbc3.jar:/opt/JBuilder2006/jdk1.5/lib/htmlconverter.jar:/opt/JBuilder2006/jdk1.5/lib/tools.jar:/opt/JBuilder2006/jdk1.5/lib/sa-jdi.jar:/opt/JBuilder2006/jdk1.5/lib/jconsole.jar:/opt/JBuilder2006/jdk1.5/lib/dt.jar:/opt/JBuilder2006/jdk1.5/jre/lib/ext/sunjce_provider.jar:/opt/JBuilder2006/jdk1.5/jre/lib/ext/localedata.jar:/opt/JBuilder2006/jdk1.5/jre/lib/ext/sunpkcs11.jar:/opt/JBuilder2006/jdk1.5/jre/lib/ext/dnsns.jar:/opt/JBuilder2006/jdk1.5/jre/lib/jce.jar:/opt/JBuilder2006/jdk1.5/jre/lib/rt.jar:/opt/JBuilder2006/jdk1.5/jre/lib/charsets.jar:/opt/JBuilder2006/jdk1.5/jre/lib/jsse.jar:/opt/JBuilder2006/jdk1.5/jre/lib/im/thaiim.jar:/opt/JBuilder2006/jdk1.5/jre/lib/im/indicim.jar:/opt/JBuilder2006/jdk1.5/jre/lib/deploy.jar:/opt/JBuilder2006/jdk1.5/jre/lib/plugin.jar:/opt/JBuilder2006/jdk1.5/jre/lib/javaws.jar"
 
MojeAppSqlConfig 
Tue Jul 18 12:27:11 CEST 2006
com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in files/Kontakt.xml.  
--- The error occurred while applying a parameter map.  
--- Check the insertKontakt-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: org.postgresql.util.PSQLException: ERROR: insert or update on 
table "kontakt" violates foreign key 
constraint "kontakt_lokace_id_lokace_fkey"
  Detail: Key (lokace_id_lokace)=(52) is not present in table "lokace".
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on 
table "kontakt" violates foreign key 
constraint "kontakt_lokace_id_lokace_fkey"
  Detail: Key (lokace_id_lokace)=(52) is not present in table "lokace".
	at 
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
	at 
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:442)
	at 
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:81)
	at 
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:58)
	at MojeAppSqlConfig.main(MojeAppSqlConfig.java:152)
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on 
table "kontakt" violates foreign key 
constraint "kontakt_lokace_id_lokace_fkey"
  Detail: Key (lokace_id_lokace)=(52) is not present in table "lokace".
	at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
	at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
	at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
	at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
	at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
	at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:346)
	at 
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:84)
	at 
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
	at 
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
	... 4 more

So time is OK, but when inserting, it looks for line 52?! I inserted line 4 in 
lokace_id!!!

Any fix? Explanation?

Thanks a lot

Michal

Dne pátek 14 červenec 2006 15:04 CIJOML napsal(a):
> Hi All,
>
> I am trying map Timestamp with time zone column to ibatis, but without
> success. Database is Postgresql 8.1.4.
>
> <?xml version="1.0" encoding="UTF-8" ?>
> <!DOCTYPE sqlMap
>   PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
>   "http://www.ibatis.com/dtd/sql-map-2.dtd">
> <sqlMap namespace="Firma">
>
> <resultMap id="Kontakt" class="Kontakt">
> <result property="id_kontakt" column="id_kontakt" />
> <result property="id_administrator" column="administrator_id_administrator"
> /> <result property="id_lokace" column="lokace_id_lokace" />
> <result property="unix_time" column="unix_time" />
> <result property="zaznam" column="zaznam" />
> <result property="poznamka" column="poznamka" />
> </resultMap>
>
>    <select id="getKontakt" resultClass="Kontakt">
>          SELECT
> 	 ID_KONTAKT as id_kontakt,
> 	 ADMINISTRATOR_ID_ADMINISTRATOR as id_administrator,
>          LOKACE_ID_LOKACE as id_lokace,
>          UNIX_TIME as unix_time,
>          ZAZNAM as zaznam,
>          POZNAMKA as poznamka
>          FROM Kontakt
>          WHERE id_kontakt = #value#
>    </select>
> <!-- Use company object (JavaBean) properties as parameters for insert.
> Each of the
>     parameters in the #hash# symbols is a JavaBeans property. -->
> <insert id="insertKontakt" parameterClass="Kontakt">
>     <selectKey resultClass="int" keyProperty="id_kontakt">
>       SELECT nextval('kontakt_ID_kontakt_seq');
>     </selectKey>
>
>       INSERT INTO
>       KONTAKT (
>       ID_KONTAKT,ADMINISTRATOR_ID_ADMINISTRATOR,LOKACE_ID_LOKACE,
>       UNIX_TIME,ZAZNAM,POZNAMKA
>       )
>       VALUES (
>       #id_kontakt#,#id_kontakt#,#id_administrator#,#id_lokace#,
>       #unix_time#,#zaznam#,#poznamka#
>       ) <!-- PER_ID vlozeno automaticky -->
> </insert>
> <!-- Use company object (JavaBean) properties as parameters for update.
> Each of the
>     parameters in the #hash# symbols is a JavaBeans property. -->
> <update id="updateKontakt" parameterClass="Kontakt">
>       UPDATE Kontakt
>       SET ZAZNAM = #zaznam#
>       WHERE ID_KONTAKT = #id_kontakt#
> </update>
> <!-- Use company object (JavaBean) "id" properties as parameters for
> delete. Each of the
>     parameters in the #hash# symbols is a JavaBeans property. -->
> <delete id="deleteKontakt" parameterClass="Kontakt">
>       DELETE FROM Kontakt
>       WHERE ID_KONTAKT = #id_kontakt#
> </delete>
>
> </sqlMap>
>
> Well Also Kontakt class is ready with setters and getters and String
> unix_time.
>
> Now I call class:
> //KONTAKT
> Kontakt newKontakt = new Kontakt();
> newKontakt.setId_administrator('3');
> newKontakt.setId_lokace('4');
> newKontakt.setUnix_time("20006-04-02 18:34 CET");
> newKontakt.setZaznam("Prvni zaznam");
> newKontakt.setPoznamka("Fakt prvni pokus o poznamku");
> sqlMap.insert("insertKontakt", newKontakt);
>
>
> And error popups:
> /opt/JBuilder2006/jdk1.5/bin/java -classpath
> "/home/cijoml/jbproject/iBATIStest/classes:/home/cijoml/jbproject/iBATIStes
>t/ibatis-common-2.jar:/home/cijoml/jbproject/iBATIStest/ibatis-dao-2.jar:/ho
>me/cijoml/jbproject/iBATIStest/ibatis-sqlmap-2.jar:/home/cijoml/jbproject/iB
>ATIStest/postgresql-8.1-405.jdbc2.jar:/home/cijoml/jbproject/iBATIStest/post
>gresql-8.1-405.jdbc3.jar:/opt/JBuilder2006/jdk1.5/lib/htmlconverter.jar:/opt
>/JBuilder2006/jdk1.5/lib/tools.jar:/opt/JBuilder2006/jdk1.5/lib/sa-jdi.jar:/
>opt/JBuilder2006/jdk1.5/lib/jconsole.jar:/opt/JBuilder2006/jdk1.5/lib/dt.jar
>:/opt/JBuilder2006/jdk1.5/jre/lib/ext/sunjce_provider.jar:/opt/JBuilder2006/
>jdk1.5/jre/lib/ext/localedata.jar:/opt/JBuilder2006/jdk1.5/jre/lib/ext/sunpk
>cs11.jar:/opt/JBuilder2006/jdk1.5/jre/lib/ext/dnsns.jar:/opt/JBuilder2006/jd
>k1.5/jre/lib/jce.jar:/opt/JBuilder2006/jdk1.5/jre/lib/rt.jar:/opt/JBuilder20
>06/jdk1.5/jre/lib/charsets.jar:/opt/JBuilder2006/jdk1.5/jre/lib/jsse.jar:/op
>t/JBuilder2006/jdk1.5/jre/lib/im/thaiim.jar:/opt/JBuilder2006/jdk1.5/jre/lib
>/im/indicim.jar:/opt/JBuilder2006/jdk1.5/jre/lib/deploy.jar:/opt/JBuilder200
>6/jdk1.5/jre/lib/plugin.jar:/opt/JBuilder2006/jdk1.5/jre/lib/javaws.jar"
> MojeAppSqlConfig
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in files/Kontakt.xml.
> --- The error occurred while applying a parameter map.
> --- Check the insertKontakt-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: org.postgresql.util.PSQLException: ERROR: column "unix_time" is
> of type timestamp with time zone but expression is of type integer
> Caused by: org.postgresql.util.PSQLException: ERROR: column "unix_time" is
> of type timestamp with time zone but expression is of type integer
>
> When I call it manually, I am able insert it:
> insert into kontakt values('1','3','4','2006-04-05 15:18
> CET','test','test');
>
> How can I see string passed to the postgresql server?
> How to fix this mapping?
>
> Thanks a lot for quick help
>
> Michal
Mime
View raw message