Replace stored procedure with MyBatis mapping

Question

I can not find an example of mapping in MyBatis that I can replace the below code with.

"if sqlcode <> 0" If no update takes place then do an insert

Any sugestions? :)

as
begin
  execute SetDefaultIsolationLevel
  update COMPANYLEVEL 
  set 
    companylevelid = @companylevelid, 
    companynameid = @companynameid, 
    level = @level, 
    memo = @memo,
    operator = @operator,
    changed = getdate(*)
  where
    companynameid = @companynameid
  if sqlcode <> 0
  BEGIN
    insert into COMPANYLEVEL 
        (companylevelid,companynameid,level,memo,operator,changed)
    values
        (@companylevelid,@companynameid,@level,@memo,@operator,getdate(*)) 
  END
  commit transaction
end

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

I don't think MyBatis has any mapping to say "try an update, if that fails do an insert". If you want that done in one round trip to the database, then a stored procedure is appropriate. You can call this stored procedure from MyBatis, but the if/else logic would be in the stored proc.

If you are trying to get rid of the stored proc, then you'll need a two step check in your code. An update in MyBatis returns the number of rows updated (via the JDBC driver), so if that is zero, then you can call a MyBatis insert mapping. In cases where an insert occurs, it would require two round trips to the database.

You could also do an "upsert" using a MERGE statement in a stored proc, but that of course isn't related to MyBatis other than MyBatis can call your stored proc. It looks like you are using Sybase? If so, I'm not sure if Sybase has upserts - link to research: Upsert (update or insert) in Sybase ASE?

edited May 23 '17 at 12:04

Community

1
1

answered Aug 11 '12 at 12:37

quux00

13,679
10
57
69

Thanks! Just a detial, you mean that an update in MyBatis has a return statement of 1 or 0 if I update just one row? If that is the case, do you have an example or can give me a link to one? – user1416776 Aug 13 '12 at 06:08
Ok, I got it... so simple :) http://edwin.baculsoft.com/2010/11/beginning-mybatis-3/ I would normaly prefere a stored procedur but I want to practice MyBatis. – user1416776 Aug 13 '12 at 06:41
Looks like you got it. If you want more examples of both insert and update and checking the return value, see Koan08 of the mybatis-koans: https://github.com/midpeter444/mybatis-koans/blob/master/src/test/java/net/thornydev/mybatis/test/koan08/Koan08.java – quux00 Aug 13 '12 at 12:58

Replace stored procedure with MyBatis mapping

1 Answers1