ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Mapping Problem
Date Fri, 04 Feb 2005 16:43:21 GMT
Select key either goes at the beginning of your insert definition or
at the end. Think of it in a logical flow. If you insert your primary
key with your insert statement then you will want to retrieve that
before you execute your sql statement. Hence you would place the
selectKey at the beginning BEFORE your sql. If you DO NOT insert your
primary key in your insert statement and your database auto generates
a key for that column then you will want to retrieve that auto
generated key AFTER the sql statement has executed. Hence you would
want selectKey to be run AFTER your sql has executed. The whole idea
is to either prepopulate or post-populate the key value on your bean.
Also, think bean centric not sql centric. SQL is the mechanism by
which your beans consume or dump information to and from the database.


Ex.
<insert>
<selectKey>
...
</selectKey>
....sql....
</insert>

or

<insert>
...sql...
<selectKey>
...
</selectKey>
</insert>

Brandon

On Fri, 04 Feb 2005 16:01:03 +0000, Kris Jenkins
<krisajenkins@yahoo.co.uk> wrote:
> That ';' at the end of your insert statement is probably the problem.
> 
> HTH,
> Kris
> 
> Ben Gill wrote:
> 
> >I am still having problems to get just one insert working....
> >
> >My table is this:
> >
> >CREATE TABLE OFFENCES
> >(
> >  OFFENCE_UUID         NUMBER                   NOT NULL,
> >  STATUS               VARCHAR2(20)             NOT NULL,
> >  AUTHORITY            VARCHAR2(20),
> >  TICKET_NUMBER        VARCHAR2(20),
> >  OFFENCE              VARCHAR2(20),
> >  EMPLOYEE_UUID        VARCHAR2(20),
> >  DATETIME_OF_OFFENCE  DATE,
> >  DATETIME_OF_TICKET   DATE,
> >  PARKING_ATTENDANT    VARCHAR2(20),
> >  SITE                 NUMBER,
> >  FLEET_UUID           VARCHAR2(20),
> >  LOC_OF_OFFENCE       VARCHAR2(30),
> >  POSTCODE             VARCHAR2(8),
> >  CREDITCARD_AUTH      VARCHAR2(10),
> >  FINE                 NUMBER,
> >  AMOUNT_PAID          NUMBER,
> >  ORDER_NUMBER         VARCHAR2(10),
> >  ROUTE_ID             VARCHAR2(10),
> >  ENTERED_BY           VARCHAR2(50)             NOT NULL,
> >  ASSIGNED_TO          VARCHAR2(50),
> >  LAST_MODIFIED        DATE                     DEFAULT SYSDATE
> >NOT NULL,
> >  LAST_MODIFIED_BY     VARCHAR2(50)             NOT NULL,
> >  CREATION_DATE        DATE                     DEFAULT SYSDATE
> >NOT NULL
> >)
> >
> >
> >My object this:
> >
> >
> >private Integer id;
> >       private String status;
> >       private String authority;
> >       private String ticketNumber;
> >       private String offence;
> >       private String employeeID;
> >       private Date dateOfOffence;
> >       private Date dateOfTicket;
> >       private String parkingAttendant;
> >       private Integer site;
> >       private String fleetID;
> >       private String locOfOffence;
> >       private String postcode;
> >       private String creditCardAuth;
> >       private Integer fine;
> >       private Integer amountPaid;
> >       private String orderNumber;
> >       private String routeID;
> >       private String enteredBy;
> >       private String assignedTo;
> >       private Date lastModified;
> >       private String lastModifiedBy;
> >       private Date creationDate;
> >
> >
> >My sqlMap this:
> >
> > <typeAlias alias="offence" type="com.ocado.tracker.offences.pojo.Offence"/>
> >
> >       <parameterMap id="offenceMap" class="offence">
> >               <parameter property="id" jdbcType="NUMERIC"
> >javaType="integer"/>
> >               <parameter property="status" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="authority" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="ticketNumber" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="offence" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="employeeID" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="dateOfOffence" jdbcType="DATE"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="dateOfTicket" jdbcType="DATE"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="parkingAttendant" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="site" jdbcType="NUMERIC"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="fleetID" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="locOfOffence" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="postcode" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="creditCardAuth" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="fine" jdbcType="NUMERIC"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="amountPaid" jdbcType="NUMERIC"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="orderNumber" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="routeID" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="enteredBy" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="assignedTo" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="lastModified" jdbcType="DATE" />
> >               <parameter property="lastModifiedBy" jdbcType="VARCHAR"
> >nullValue="NO_ENTRY"/>
> >               <parameter property="creationDate" jdbcType="DATE"
> >nullValue="NO_ENTRY"/>
> >       </parameterMap>
> >
> >       <insert id="addOffence" parameterClass="offence"
> >               parameterMap="offenceMap">
> >           <selectKey resultClass="offence" keyProperty="id" >
> >                       SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
> >               </selectKey>
> >        insert into
> >        OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
> >                 OFFENCE,
> >                 EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
> >                 PARKING_ATTENDANT,
> >                 SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE,
> >                 CREDITCARD_AUTHORITY, FINE, AMOUNT_PAID,
> >                 ORDER_NUMBER,
> >                 ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
> >LAST_MODIFIED,
> >                 CREATION_DATE)
> >       values (
> >           #id#, 'new', #authority#, #ticketNumber#,
> >               #offence#, #employeeID#, #dateOfOffence#, #dateOfTicket#,
> >               #parkingAttendant#, #site#, #fleetID#, #locOfOffence#,
> >               #postcode#, #creditCardAuth#, #fine#, #amountPaid#,
> >#orderNumber#,
> >               #routeID#, #enteredBy#, #assignedTo#, #lastModifiedBy#,
> >               sysdate, sysdate);
> >    </insert>
> >
> >
> >My error this:
> >
> >04-02-05 15:39:20:569 - {DEBUG} sql.PreparedStatement Thread [main];
> >{pstm-1000
> >01} PreparedStatement:                insert into          OFFENCES
> >(OFFENCE_UUI
> >D, STATUS, AUTHORITY, TICKET_NUMBER,      OFFENCE,      EMPLOYEE_UUID,
> >DATETIME_
> >OF_OFFENCE, DATETIME_OF_TICKET,      PARKING_ATTENDANT,     SITE,
> >FLEET_UUID, LO
> >C_OF_OFFENCE, POSTCODE,      CREDITCARD_AUTHORITY, FINE, AMOUNT_PAID,
> >ORDER
> >_NUMBER,     ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
> >LAST_MODIFIED,
> >      CREATION_DATE)   values (      #id#, 'new', #authority#,
> >#ticketNumber#,
> >  #offence#, #employeeID#, #dateOfOffence#, #dateOfTicket#,
> >#parkingAttendant#
> >, #site#, #fleetID#, #locOfOffence#,    #postcode#, #creditCardAuth#,
> >#fine#, #a
> >mountPaid#, #orderNumber#,    #routeID#, #enteredBy#, #assignedTo#,
> >#lastModifie
> >dBy#,    sysdate, sysdate);
> >04-02-05 15:39:20:569 - {DEBUG} sql.PreparedStatement Thread [main];
> >{pstm-1000
> >01} Parameters: [123, aStatus, null, null, null, null, null, null, null,
> >null, n
> >ull, null, null, null, null, null, null, null, bgill, null, 2005-02-04,
> >bgill, n
> >ull]
> >04-02-05 15:39:20:569 - {DEBUG} sql.PreparedStatement Thread [main];
> >{pstm-1000
> >01} Types: [java.lang.Integer, java.lang.String, null, null, null, null,
> >null, n
> >ull, null, null, null, null, null, null, null, null, null, null,
> >java.lang.Strin
> >g, null, java.sql.Date, java.lang.String, null]
> >04-02-05 15:39:20:585 - {DEBUG} support.SQLErrorCodeSQLExceptionTranslator
> >Threa
> >d [main];  Unable to translate SQLException with errorCode '911', will now
> >try t
> >he fallback translator
> >04-02-05 15:39:20:585 - {DEBUG} support.SQLStateSQLExceptionTranslator
> >Thread [m
> >ain];  Translating SQLException with SQLState '42000' and errorCode '911'
> >and me
> >ssage [
> >--- The error occurred in com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
> >--- The error occurred while applying a parameter map.
> >--- Check the OffenceSQL.offenceMap.
> >--- Check the statement (update failed).
> >--- Cause: java.sql.SQLException: ORA-00911: invalid character
> >]; SQL was [] for task [SqlMapClient operation]
> >04-02-05 15:39:20:585 - {DEBUG} datasource.DataSourceUtils Thread [main];
> >Closi
> >ng JDBC connection
> >04-02-05 15:39:20:585 - {ERROR} dao.OffencesDAO Thread [main];  Exception
> >caught
> > calling ibatis [Bad SQL grammar [] in task 'SqlMapClient operation'; nested
> >exc
> >eption is com.ibatis.common.jdbc.exception.NestedSQLException:
> >--- The error occurred in com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
> >--- The error occurred while applying a parameter map.
> >--- Check the OffenceSQL.offenceMap.
> >--- Check the statement (update failed).
> >--- Cause: java.sql.SQLException: ORA-00911: invalid character
> >
> >Does anyone know what is wrong?  or how I can get more info out?
> >
> >Is there any way of dumping out the SQL statement it is trying to build?
> >
> >-----Original Message-----
> >From: Daniel H. F. e Silva [mailto:dhfs@yahoo.com]
> >Sent: 04 February 2005 13:31
> >To: ibatis-user-java@incubator.apache.org
> >Subject: RE: Mapping Problem
> >
> >
> >Hehe,
> >
> > That was not exactly my intention, but if it worked for you, nice!
> > I just meant to take your OFFENCES_SEQ.NEXTVAL and put it in place of #id#
> >at values list.
> > But, it is working!
> >
> >Cheers,
> > Daniel Silva.
> >
> >--- Ben Gill <ben.gill@ocado.com> wrote:
> >
> >
> >
> >>Thanks Daniel, that works...
> >>
> >>Seems strange to me though that it does, as surely the selectKey would
> >>
> >>
> >have
> >
> >
> >>set id to a java.lang.String anyhow...
> >>
> >>Working:
> >>
> >><typeAlias alias="offence"
> >>type="com.ocado.tracker.offences.forms.OffenceForm"/>
> >>
> >>      <insert id="addOffence" parameterClass="offence">
> >>
> >>
> >>        insert into
> >>        OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
> >>                OFFENCE,
> >>                EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
> >>                SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE, FINE,
> >>ORDER_NUMBER,
> >>                ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
> >>LAST_MODIFIED,
> >>                CREATION_DATE)
> >>      values (
> >>        <selectKey resultClass="java.lang.String" keyProperty="id" >
> >>                      SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
> >>              </selectKey>, 'new', #authority#, #ticketNumber#, #offence#,
> >>              #employeeID#, to_date(#dateOfOffence#,'dd/mm/YYYY HH24:mi'),
> >>              to_date(#dateOfTicket#,'dd/mm/YYYY HH24:mi'),
> >>              #site#, #fleetID#, #locOfOffence#, #postcode#, #fine#,
> >>              #orderNumber#, #routeID#, #enteredBy#, #assignedTo#,
> >>sysdate, sysdate);
> >>    </insert>
> >>
> >>Not working:
> >>
> >><typeAlias alias="offence"
> >>type="com.ocado.tracker.offences.forms.OffenceForm"/>
> >>
> >>      <insert id="addOffence" parameterClass="offence">
> >>
> >>              <selectKey resultClass="java.lang.String" keyProperty="id" >
> >>                      SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
> >>              </selectKey>
> >>
> >>        insert into
> >>        OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
> >>                OFFENCE,
> >>                EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
> >>                SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE, FINE,
> >>ORDER_NUMBER,
> >>                ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
> >>LAST_MODIFIED,
> >>                CREATION_DATE)
> >>      values (
> >>        #id#, 'new', #authority#, #ticketNumber#, #offence#,
> >>              #employeeID#, to_date(#dateOfOffence#,'dd/mm/YYYY HH24:mi'),
> >>              to_date(#dateOfTicket#,'dd/mm/YYYY HH24:mi'),
> >>              #site#, #fleetID#, #locOfOffence#, #postcode#, #fine#,
> >>              #orderNumber#, #routeID#, #enteredBy#, #assignedTo#,
> >>sysdate, sysdate);
> >>    </insert>
> >>
> >>Thanks anyhow...
> >>
> >>-----Original Message-----
> >>From: Daniel H. F. e Silva [mailto:dhfs@yahoo.com]
> >>Sent: 04 February 2005 12:39
> >>To: ibatis-user-java@incubator.apache.org
> >>Subject: RE: Mapping Problem
> >>
> >>
> >>Ben,
> >>
> >> Seems to me that you're using a sequence that gives you integer values,
> >>aren't you?
> >> So, why does your pk column has type VARCHAR(20)?
> >> If your database is Oracle, i bet the problem is happening due to
> >>converting an integer to a
> >>string and then back.
> >> What happens if you replace your #id# in the VALUES list with the call of
> >>the sequence?
> >>
> >>Cheers,
> >> Daniel Silva.
> >>
> >>
> >>--- Ben Gill <ben.gill@ocado.com> wrote:
> >>
> >>
> >>
> >>>Yeah I tried that... in fact I have tried a whole nuch of things but
> >>>
> >>>
> >>cannot
> >>
> >>
> >>>get it to work!!
> >>>
> >>>I am now using:
> >>>
> >>><typeAlias alias="offence"
> >>>type="com.ocado.tracker.offences.forms.OffenceForm"/>
> >>>
> >>>     <insert id="addOffence" parameterClass="offence">
> >>>             <selectKey resultClass="java.lang.String" keyProperty="id"
>
> >>>                     SELECT OFFENCES_SEQ.NEXTVAL AS ID FROM DUAL
> >>>             </selectKey>
> >>>
> >>>        insert into
> >>>        OFFENCES (OFFENCE_UUID, STATUS, AUTHORITY, TICKET_NUMBER,
> >>>               OFFENCE,
> >>>               EMPLOYEE_UUID, DATETIME_OF_OFFENCE, DATETIME_OF_TICKET,
> >>>               SITE, FLEET_UUID, LOC_OF_OFFENCE, POSTCODE, FINE,
> >>>ORDER_NUMBER,
> >>>               ROUTE_ID, ENTERED_BY, ASSIGNED_TO, LAST_MODIFIED_BY,
> >>>LAST_MODIFIED,
> >>>               CREATION_DATE)
> >>>     values (#id#, 'new', #authority#, #ticketNumber#, #offence#,
> >>>             #employeeID#, to_date(#dateOfOffence#,'dd/mm/YYYY HH24:mi'),
> >>>             to_date(#dateOfTicket#,'dd/mm/YYYY HH24:mi'),
> >>>             #site#, #fleetID#, #locOfOffence#, #postcode#, #fine#,
> >>>             #orderNumber#, #routeID#, #enteredBy#, #assignedTo#,
> >>>sysdate, sysdate);
> >>>    </insert>
> >>>
> >>>org.springframework.jdbc.UncategorizedSQLException: (SqlMapClient
> >>>operation): en
> >>>countered SQLException [
> >>>--- The error occurred in
> >>>
> >>>
> >>com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
> >>
> >>
> >>>--- The error occurred while applying a parameter map.
> >>>--- Check the addOffence-InlineParameterMap.
> >>>--- Check the parameter mapping for the 'id' property.
> >>>--- Cause: java.sql.SQLException: Invalid column type]; nested exception
> >>>
> >>>
> >>is
> >>
> >>
> >>>com.
> >>>ibatis.common.jdbc.exception.NestedSQLException:
> >>>--- The error occurred in
> >>>
> >>>
> >>com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
> >>
> >>
> >>>--- The error occurred while applying a parameter map.
> >>>--- Check the addOffence-InlineParameterMap.
> >>>--- Check the parameter mapping for the 'id' property.
> >>>--- Cause: java.sql.SQLException: Invalid column type
> >>>com.ibatis.common.jdbc.exception.NestedSQLException:
> >>>--- The error occurred in
> >>>
> >>>
> >>com/ocado/tracker/offences/dao/sql/OffenceSQL.xml.
> >>
> >>
> >>>--- The error occurred while applying a parameter map.
> >>>--- Check the addOffence-InlineParameterMap.
> >>>--- Check the parameter mapping for the 'id' property.
> >>>--- Cause: java.sql.SQLException: Invalid column type
> >>>Caused by: java.sql.SQLException: Invalid column type
> >>>        at
> >>>com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUp
> >>>date(GeneralStatement.java:86)
> >>>        at
> >>>com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExe
> >>>cutorDelegate.java:500)
> >>>        at
> >>>com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionI
> >>>mpl.java:89)
> >>>
> >>>-----Original Message-----
> >>>From: Aitor Imaz [mailto:aitor.imaz@gmail.com]
> >>>Sent: 04 February 2005 12:19
> >>>To: ibatis-user-java@incubator.apache.org
> >>>Subject: Re: Mapping Problem
> >>>
> >>>
> >>>I can see one thing, in your <selectKey> the resultClass attribute
> >>>should be a String if I'm not wrong. You should put there the class of
> >>>the primary key returned, not of the object itself. Maybe that's the
> >>>cause of the error (the selectkey mapping)?
> >>>
> >>>Hope it helps,
> >>>Aitor
> >>>
> >>>
> >>>_____________________________________________________________________
> >>>This message has been checked for all known viruses by the
> >>>MessageLabs Virus Control Centre.
> >>>
> >>>This message has been checked for all known viruses by the MessageLabs
> >>>
> >>>
> >>Virus Control Centre.
> >>
> >>
> >>>
> >>>*********************************************************************
> >>>
> >>>Notice:  This email is confidential and may contain copyright material
> >>>
> >>>
> >of
> >
> >
> >>Ocado Limited (the
> >>
> >>
> >>>"Company"). Opinions and views expressed in this message may not
> >>>
> >>>
> >>necessarily reflect the
> >>
> >>
> >>>opinions and views of the Company.
> >>>If you are not the intended recipient, please notify us immediately and
> >>>
> >>>
> >>delete all copies of
> >>
> >>
> >>>this message. Please note that it is your responsibility to scan this
> >>>
> >>>
> >>message for viruses.
> >>
> >>
> >>>Company reg. no. 3875000.
> >>>Ocado Limited
> >>>Titan Court
> >>>3 Bishops Square
> >>>Hatfield Business Park
> >>>Hatfield
> >>>Herts
> >>>AL10 9NE
> >>>
> >>>
> >>>
> >>>
> >=== message truncated ===
> >
> >
> >
> >
> >__________________________________
> >Do you Yahoo!?
> >Meet the all-new My Yahoo! - Try it today!
> >http://my.yahoo.com
> >
> >
> >
> >_____________________________________________________________________
> >This message has been checked for all known viruses by the
> >MessageLabs Virus Control Centre.
> >
> >This message has been checked for all known viruses by the MessageLabs Virus Control
Centre.
> >
> >
> >*********************************************************************
> >
> >Notice:  This email is confidential and may contain copyright material of Ocado Limited
(the "Company"). Opinions and views expressed in this message may not necessarily reflect
the opinions and views of the Company.
> >If you are not the intended recipient, please notify us immediately and delete all
copies of this message. Please note that it is your responsibility to scan this message for
viruses.
> >
> >Company reg. no. 3875000.
> >Ocado Limited
> >Titan Court
> >3 Bishops Square
> >Hatfield Business Park
> >Hatfield
> >Herts
> >AL10 9NE
> >
> >
> >*********************************************************************
> >
> >
> >
> 
> --
> Kris Jenkins
> Email:  kris@jenkster.com
> Blog:   http://cafe.jenkster.com/
> Wiki:   http://wiki.jenkster.com/
> 
>

Mime
View raw message