ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: ibatis 3.0 - @Insert with Insert select fails
Date Thu, 11 Mar 2010 15:14:52 GMT
@Insert("insert into USER (USERID, USERNAME, PASSWORD, SERVER_GROUP) " +
        "select #{trgUserId,javaType=int,jdbcType=INTEGER},
USERNAME PASSWORD, SERVER_GROUP " +
        "from USER where USERID
= #{srcUserId,javaType=int,jdbcType=INTEGER}")
void cloneUser(@Param("trgUserId") final int trgUserId,
@Param("srcUserId") final
int srcUserId);

The reason you need to do this is that Java cannot introspect upon parameter
names (which is really unfortunate).

But it's still better than using a HashMap...

Clinton

PS:  If you don't specify parameter names, they default to #{0}, #{1}, #{2}
etc...

On Wed, Mar 10, 2010 at 10:11 PM, marksomaha <mark.stevens@dtn.com> wrote:

>
> Clinton,
>
> No, I am not using @Param.  I've read the User Guide, but I don't know what
> the exact syntax to use.
> As I am a newbie to Ibatis, please provide an example of its use.
>
> Thanks
> Mark
>
>
> Clinton Begin wrote:
> >
> > Are you naming the parameters in your method using @Param?
> >
> > Clinton
> >
> > On Wed, Mar 10, 2010 at 6:14 PM, marksomaha <mark.stevens@dtn.com>
> wrote:
> >
> >>
> >> Follow-up:  Embarrassed to say the "Invalid table name" was my own typo.
> >> "Insert into USER..." should have been "Insert into USERS..."
> >>
> >> Now, it runs, but it is still not cloning the Users row.  In fact, it is
> >> quite interesting to see what Oracle thinks is being executed on the DB
> >> server.
> >>
> >> @Insert("insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP) "
> +
> >>            "select ${trgUserId}, USERNAME, PASSWORD, SERVER_GROUP " +
> >>            "from USERS where USERID =
> >> #{srcUserId,javaType=int,jdbcType=INTEGER}")
> >>    void cloneUser(final int trgUserId, final int srcUserId);
> >> Called with: mapper.cloneUser(6, 1);
> >>
> >> In Oracle it appears as:
> >> insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP) select
> null,
> >> USERNAME, PASSWORD, SERVER_GROUP from USERS where USERID = :1
> >>
> >> When I use the #{trgUserId} syntax, I get the following:
> >> @Insert("insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP) "
> +
> >>             "select #{trgUserId,javaType=int,jdbcType=INTEGER},
> USERNAME,
> >> PASSWORD, SERVER_GROUP " +
> >>             "from USERS where USERID =
> >> #{srcUserId,javaType=int,jdbcType=INTEGER}")
> >>    void cloneUser(final int trgUserId, final int srcUserId);
> >>
> >> In Oracle it appears as:
> >>        insert into USERS (USERID, USERNAME, PASSWORD, SERVER_GROUP)
> >> select
> >> :1,
> >> USERNAME, PASSWORD, SERVER_GROUP from USERS where USERID = :2
> >>
> >> Somehow, I'm not getting the correct values passed as bind variable
> >> values.
> >> Ideas?  I'm stumped!
> >> Mark
> >>
> >>
> >> nmaves wrote:
> >> >
> >> > Sure do :)  Great error message eh....
> >> >
> >> > use ${trgUserId} in the select portion and leave #{trgUserId} in the
> >> where
> >> > clause.   # infers a true jdbc parameter which are not allowed in the
> >> > select
> >> > portion of a statement.
> >> >
> >> > Nahtan
> >> >
> >> > On Wed, Mar 10, 2010 at 4:28 PM, marksomaha <mark.stevens@dtn.com>
> >> wrote:
> >> >
> >> >>
> >> >> I am trying to do a insert that copies rows from an existing user.
> >> >> Essentially cloning a user, but change userid for the new record.
> >> >>
> >> >> Here is the syntax:
> >> >>    @Insert("insert into USER (USERID, USERNAME, PASSWORD,
> >> SERVER_GROUP)
> >> "
> >> >> +
> >> >>            "select #{trgUserId,javaType=int,jdbcType=INTEGER},
> >> USERNAME,
> >> >> PASSWORD, SERVER_GROUP " +
> >> >>            "from USER where USERID =
> >> >> #{srcUserId,javaType=int,jdbcType=INTEGER}")
> >> >>    void cloneUser(final int trgUserId, final int srcUserId);
> >> >>
> >> >> When I try this syntax, I get the following error back:
> >> >>
> >> >> org.apache.ibatis.exceptions.IbatisException:
> >> >> ### Error updating database.  Cause: java.sql.SQLException:
> ORA-00903:
> >> >> invalid table name
> >> >>
> >> >> ### The error may involve testibatis3.UserMapper.cloneUser-Inline
> >> >> ### The error occurred while setting parameters
> >> >> ### Cause: java.sql.SQLException: ORA-00903: invalid table name
> >> >>
> >> >>        at
> >> >>
> >> >>
> >>
> org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
> >> >>        at
> >> >>
> >> >>
> >>
> org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)
> >> >>        at
> >> >>
> >> >>
> >>
> org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:87)
> >> >>        at
> >> >> org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:54)
> >> >>        at
> >> >> org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)
> >> >>
> >> >> Any ideas as to what would cause an "Invalid table name"?  The USERS
> >> >> table
> >> >> exists and I have verified the SQL statement in SQLPLUS.
> >> >> --
> >> >> View this message in context:
> >> >>
> >>
> http://old.nabble.com/ibatis-3.0---%40Insert-with-Insert-select-fails-tp27857358p27857358.html
> >> >> Sent from the iBATIS - User - Java mailing list archive at
> Nabble.com.
> >> >>
> >> >>
> >> >> ---------------------------------------------------------------------
> >> >> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> >> >> For additional commands, e-mail: user-java-help@ibatis.apache.org
> >> >>
> >> >>
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >>
> http://old.nabble.com/ibatis-3.0---%40Insert-with-Insert-select-fails-tp27857358p27858161.html
> >> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >>
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> >> For additional commands, e-mail: user-java-help@ibatis.apache.org
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/ibatis-3.0---%40Insert-with-Insert-select-fails-tp27857358p27859395.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>

Mime
View raw message