Any way to do a multi-row INSERT?

2731 views
Skip to first unread message

Denis Haskin

unread,
Aug 6, 2010, 8:37:17 AM8/6/10
to mybatis-user
Would there be any way in MyBatis to get a multi-row INSERT to
happen? e.g. "INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),
(7,8,9);"

Form what I've looked at so far, seems like the answer is "no" (which
I'll admit is sort of reasonable, given MyBatis' goals).

There doesn't seem to be any way to do raw SQL (except by just getting
the underlying Connection and doing it explicitly). The
@InsertProvider concept is a really neat one, but that only operates
on a single object at a time.

Maybe I'll try performance with a regular multi-statement INSERT
(we're doing essentially a bulk load). A MySQL expert I know claimed
that multi-row INSERTs had significant benefit in that case.

Thanks!

dwh

Chema

unread,
Aug 6, 2010, 9:22:03 AM8/6/10
to mybati...@googlegroups.com


El 06/08/2010, a las 14:37, Denis Haskin <de...@haskinferguson.net> escribió:

Would there be any way in MyBatis to get a multi-row INSERT to
happen?  e.g. "INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),
(7,8,9);"



And if you use the SELECT statement in conjunction with the INSERT sentence? 

Denis Haskin

unread,
Aug 6, 2010, 10:11:04 AM8/6/10
to mybatis-user
Sorry, I don't understand the question/comment? Are you asking a
about SELECT... INTO... statement?

Thanks,

dwh

Joseph Gooch

unread,
Aug 6, 2010, 10:39:52 AM8/6/10
to mybatis-user
MyBatis is going to pass the query to the JDBC driver for execution.
If your DBMS and JDBC driver support inserts of that syntax, then you
should be fine. (I assume you're thinking mysql)

If you don't have such a DBMS, you have insert into XXX select * from
YYY syntax, or even insert into XXX select YYY union ZZZ.... which
would also work.

To make a multi-insert statement for MySQL you might want to pass a
List<> of whatever your parameter object is, and construct it like
(assuming mybatis 3):

INSERT INTO tbl_name (A,B,C)
VALUES <foreach collection="_parameter" item="parm" open="(" close=")"
separator=",">#{parm.val1},#{parm.val2},#{parm.val3}</foreach>;

... and you'd probably want to ensure _parameter isn't null and has a
size>0.

Joe

Denis Haskin

unread,
Aug 6, 2010, 10:43:55 AM8/6/10
to mybatis-user
Whoa. Cool. So I would put something like what you have below into
my mapper XML file, and then do something like:
List<Message> messages = ...
session.insert("MessageMultiInsert", messages);
?

That would be sweet...

dwh

Joseph Gooch

unread,
Aug 6, 2010, 10:48:25 AM8/6/10
to mybatis-user
Yeppers. Then your parm.val1 references would be properties within
your Message class. i.e. val1 would need a getVal1() function.

I use something similar to deal with dynamic where clauses... You can
create an object that has columnname and value, and then use foreach
with separator=" AND " to build the where clause.

Joe

Denis Haskin

unread,
Aug 6, 2010, 10:54:49 AM8/6/10
to mybatis-user
Sorry, I guess I'm not complete following where _parameter comes
from. Can you clarify?

Thanks!

dwh

Denis Haskin

unread,
Aug 6, 2010, 11:02:30 AM8/6/10
to mybatis-user
And I assume then in this case that parameterType in the mapper XML
would be java.util.List and not my POJO?

dwh

Joseph Gooch

unread,
Aug 6, 2010, 11:04:32 AM8/6/10
to mybatis-user
When you supply a parameter object the properties are immediately
available... for instance, if you passed in a Message and not a
List<Message>... you could use #{propertyName} and it would resolve
from your Message object's getPropertyName() method. Or if you passed
in a HashMap, you could use #{key} and it would do a HashMap.get(key);

The original object is always stored in _parameter, so you can access
what was originally passed in. For instance, if you wanted to use
getClass() in your conditionals to compare the type that was passed in
from the function. Or, in this case, if you want to reference the
entire object instead of just a property of it.

In practice I do something a little different (so I don't depend on a
"reserved' param name), something like
class QueryOptions {
private int startRow;
private int maxRows;
private List<Message> msgs;

.... (getters and setters)
}

Then when you pass in a QueryOptions object, you can do things like
dynamically add a LIMIT ${startRow}, ${maxRows} to selects if startRow
and maxRow !=null... and you can reference your insert messages as
msgs instead of _parameter.

Joe

Joseph Gooch

unread,
Aug 6, 2010, 11:06:15 AM8/6/10
to mybatis-user
Yep.
Joe

Abhishek Kona

unread,
Feb 1, 2012, 3:26:22 PM2/1/12
to mybati...@googlegroups.com
Is there a way of doing the same thing using Annotations?

-Abhishek Kona
Reply all
Reply to author
Forward
0 new messages