Any way to do a multi-row INSERT?

[Denis Haskin]

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]

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]

Sorry, I don't understand the question/comment? Are you asking a
about SELECT... INTO... statement?

Thanks,

dwh

[Joseph Gooch]

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]

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]

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]

Sorry, I guess I'm not complete following where _parameter comes
from. Can you clarify?

Thanks!

dwh

[Denis Haskin]

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]

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]

Yep.
Joe

[Abhishek Kona]

Is there a way of doing the same thing using Annotations?

-Abhishek Kona