I hooked up pgsql to the jgamestore app and ran the unit test against
the following sql map. It worked fine and populated the id just fine.
I think the key is to specify your jdbc types in the mapping
(#myInteger:INTEGER#).
<insert id="insert" parameterClass="Category">
<selectKey keyProperty="categoryId" resultClass="integer">
SELECT nextval('global_seq')
</selectKey>
INSERT INTO CATEGORY
(
categoryId,
title,
description,
parentCategoryId,
leftNode,
rightNode,
sequence,
ancestorTree
)
VALUES(
#categoryId:INTEGER#,
#title:VARCHAR#,
#description:VARCHAR#,
#parentCategoryId:INTEGER#,
#leftNode:INTEGER#,
#rightNode:INTEGER#,
#sequence:INTEGER#,
#ancestorTree:VARCHAR#
)
</insert>
Brandon
On 5/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi,
> Unfortunately, It still doesn't work. After many of combinations I forgot to
> put it, but it doesn't change anything at all.
>
> > The selectKey will simply retrieve the id and populate the addressId
> >in your Customer object. It is then your responsibility to insert the
> >proper id along with your insert.
>
> Exactly. So when I put the <selectKey>..</selectKey> statement. While
> invocation it should be executed
> exactly in the order pointed by positions/statements in <insert>..</insert>.
> So if I put property addressId into insert clause or not, it should be
> populated first????
>
> So First property addressId should be populated (I should see it in database
> log file, but i cannot, see below). It still isn't executed.
> Then insert should do the rest with correctly addressId populated.
> The other way is to do insert ( from sequence) and catch inserted id with
> <selectKey> using currval instead of nextval after inserting record into
> table.
> It also isn't executed.
>
> Maybe there is a problem between that, the database column addr_id is type of
> integer, and my resultClass is int (bean has Integer property). But I also
> tried resultClass as resultClass="integer". Nothing changed.
>
> Then I relised that the addressId is set to 0 (by struts), and It coulbe the
> problem, that SelectKey maybe is executed when
> property is null (I removed it from form, so it was null). But It also didn't
> changed anything
>
> At the end of mail, ther's a mention about failure of updating id column:
> "Failed to update - id [insertCustomerNew] - parameterObject ..."
> Why addressId isn't mentioned. I also have id column which is extended from
> ObjectData, but it doesn't seem to be populated.
>
> I still hope, You can help me
>
> Darek
>
> <insert id="insertCustomerNew" parameterClass="customer">
> <selectKey resultClass="int" keyProperty="addressId">
> select nextval('public.addresses_addr_id_seq'::text)
> </selectKey>
> INSERT INTO ADDRESSES (
> ADDR_ID
> ,STREET
> ,PLACENO
> ,FLATNO
> ,CITY
> ,ZIP
> ,C_STREET
> ,C_PLACENO
> ,C_FLATNO
> ,C_CITY
> ,C_ZIP
> )
> VALUES (
> #addressId#
> ,#addressStreet#
> ,#addressPlaceNo#
> ,#addressFlatNo#
> ,#addressCity#
> ,#addressPostalCode#
> ,#addressCstreet#
> ,#addressCplaceNo#
> ,#addressCflatNo#
> ,#addressCcity#
> ,#addressCpostalCode#
> )
> </insert>
>
> Bean of customerData known as customer parameter Class
> public class CustomerData extends ObjectData {
>
> private Integer stateId;
> private Integer compId;
>
> private Integer addressId;
> ....
>
> public Integer getAddressId() {
> System.out.println("getaddressId" + addressId);
> return addressId;
> }
>
> public void setAddressId(Integer addressId) {
> System.out.println("setaddressId" + addressId.intValue());
> this.addressId = addressId;
> }
>
> }
>
> # \d addresses ;
> Table "public.addresses"
> Column | Type | Modifiers
> -----------+-----------------------+-----------
> addr_id | integer | not null
> street | character varying(64) | not null
> placeno | character varying(15) | not null
> flatno | character varying(5) | not null
> city | character varying(30) | not null
> zip | character(6) | not null
> c_street | character varying(64) |
> c_placeno | character varying(15) |
> c_flatno | character varying(5) | not null
> c_city | character varying(30) |
> c_zip | character(6) |
> Indexes: addr_pk primary key btree (addr_id)
> Check constraints: "check_zip" (zip ~ '^[0-9]{2}-[0-9]{3}$'::text)
>
> Log from database
>
> with jdbc driver 8.0311
>
> enfo_id IN
> (
> SELECT enfo_id
> FROM entry_forms e
> WHERE not exists (select 1 FROM given_entry_skills ges WHERE
> enfo_id = e.enfo_id) )
> )
> --------------------------- HERE -------------------------------
> 2005-05-01 13:29:52 CEST LOG: statement: INSERT INTO ADDRESSES (
> ADDR_ID ,STREET ,PLACENO ,FLATNO ,CITY ,ZIP
> ,C_STREET ,C_PLACENO ,C_FLATNO ,C_CITY ,C_ZIP
> ) VALUES( $1 ,$2 ,$3 ,$4 ,$5 ,$6
> ,$7 ,$8 ,$9 ,$10 ,$11 )
> ----------------------------------------------------------
> 2005-05-01 13:29:52 CEST ERROR: duplicate key violates unique constraint
> "addr_pk"
> 2005-05-01 13:29:52 CEST LOG: statement: SELECT * FROM states
> ORDER BY name ASC
> 2005-05-01 13:29:52 CEST LOG: statement: SELECT * FROM COMPANIES
> ORDER BY name ASC
>
> with jdbc driver 7.4 build 215
> -------------------------------
>
> SELECT enfo_id
> FROM entry_forms e
> WHERE not exists (select 1 FROM given_entry_skills ges WHERE
> enfo_id = e.enfo_id) )
> )
>
> 2005-05-01 13:31:17 CEST LOG: statement: set datestyle to 'ISO'; select
> version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
> getdatabaseencoding() end;
> 2005-05-01 13:31:17 CEST LOG: duration: 2.466 ms
> 2005-05-01 13:31:17 CEST LOG: statement: set client_encoding = 'UNICODE'
> 2005-05-01 13:31:17 CEST LOG: duration: 1.400 ms
> 2005-05-01 13:31:17 CEST LOG: statement: begin;
> 2005-05-01 13:31:17 CEST LOG: duration: 0.220 ms
> 2005-05-01 13:31:17 CEST LOG: statement: rollback; begin;
> 2005-05-01 13:31:17 CEST LOG: duration: 0.196 ms
> --------------------------- HERE -------------------------------
> 2005-05-01 13:31:17 CEST LOG: statement: INSERT INTO ADDRESSES (
> ADDR_ID ,STREET ,PLACENO ,FLATNO ,CITY ,ZIP
> ,C_STREET ,C_PLACENO ,C_FLATNO ,C_CITY ,C_ZIP
> ) VALUES( 0 ,'7' ,'7' ,'7' ,'7'
> ,'86-300' ,'' ,'' ,'' ,'' ,'' )
> 2005-05-01 13:31:17 CEST ERROR: duplicate key violates unique constraint
> "addr_pk"
> ----------------------------------------------------------
> 2005-05-01 13:31:17 CEST LOG: statement: rollback; begin;
> 2005-05-01 13:31:17 CEST LOG: duration: 0.351 ms
> 2005-05-01 13:31:17 CEST LOG: statement: rollback; begin;
> 2005-05-01 13:31:17 CEST LOG: duration: 0.225 ms
> 2005-05-01 13:31:17 CEST LOG: statement: rollback; begin;
> 2005-05-01 13:31:17 CEST LOG: duration: 0.431 ms
> 2005-05-01 13:31:17 CEST LOG: statement: SELECT * FROM states
> ORDER BY name ASC
> 2005-05-01 13:31:17 CEST LOG: duration: 3.400 ms
> 2005-05-01 13:31:17 CEST LOG: statement: rollback; begin;
> 2005-05-01 13:31:17 CEST LOG: duration: 0.396 ms
> 2005-05-01 13:31:17 CEST LOG: statement: rollback; begin;
> 2005-05-01 13:31:17 CEST LOG: duration: 0.242 ms
> 2005-05-01 13:31:17 CEST LOG: statement: SELECT * FROM COMPANIES
> ORDER BY name ASC
>
> --------------------------- After removing hidden property addressId from
> form, it isn't set to 0 ------------------
> tatement: rollback; begin;
> 2005-05-01 13:52:24 CEST LOG: duration: 0.485 ms
> 2005-05-01 13:52:24 CEST LOG: statement: INSERT INTO ADDRESSES (
> ADDR_ID ,STREET ,PLACENO ,FLATNO ,CITY ,ZIP
> ,C_STREET ,C_PLACENO ,C_FLATNO ,C_CITY ,C_ZIP
> ) VALUES ( null ,'89' ,'89' ,'898' ,'9'
> ,'86-300' ,'' ,'' ,'' ,'' ,'' )
> 2005-05-01 13:52:24 CEST ERROR: null value in column "addr_id" violates
> not-null constraint
>
> In my tomcat logs, I have:
> --------------------------------
>
> DEBUG (CustomerNewActionDAO.java:57) -
> com.protex.persistence.actiondao.CustomerNewActionDAO:insertObject
> getAddressId:null
> ERROR (CustomerNewActionDAO.java:81) -
> com.protex.persistence.actiondao.CustomerNewActionDAO.insertObject error
> Failed to update - id [insertCustomerNew] - parameterObject [EMAIL PROTECTED]
> Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in com/protex/persistence/sqlmapdao/sql/Customer.xml.
> --- The error occurred while applying a parameter map.
> --- Check the insertCustomerNew-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: org.postgresql.util.PSQLException: ERROR: null value in column
> "addr_id" violates not-null constraint
>
> Caused by: org.postgresql.util.PSQLException: ERROR: null value in column
> "addr_id" violates not-null constraint
>
