Batch inserts with auto incremented keys

[Mark Spangler]

How would I get back all the inserted auto incremented id's on a batch insert using @SqlBatch? JDBI says that batch methods must either return void or int[] array with the array representing the number of rows modified within the batch.

[Brian McCallister]

On Tue, Jul 24, 2012 at 1:01 PM, Mark Spangler <mspa...@gmail.com> wrote:

How would I get back all the inserted auto incremented id's on a batch insert using @SqlBatch? JDBI says that batch methods must either return void or int[] array with the array representing the number of rows modified within the batch.

Right now, there is no way to do this in JDBI. How would you do this in JDBC?

-Brian

[Mark Spangler]

yeah i'm guessing it's only supported for single insert statements / it may be supported on specific drivers but i've never seen it / just thought i'd ask / thanks for the quick response

[Fredrik Hörte]

I'm looking for the same thing, though when just inserting a single record and get last_id (auto incremented id) back. Is it possible or do I need to write a "fluent" query?

[Mark Spangler]

you can annotate your method like so and it will return back the auto-incremented primary key:

@SqlUpdate

@GetGeneratedKeys

long insert(@BindBean MyObject myObject);

[Fredrik Hörte]

Brilliant! Works as expected, thanks!

[Plap]

Using MySQL I can do it with JDBC this way:

PreparedStatement insertBatch = null;

Connection connection = ....;

for (Event event : events) {

if (insertBatch == null){

insertBatch = connection.prepareStatement("insert into `event` (game, `type`, actor, target, arg1, arg2, arg3, created) " +

"values (?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

}

insertBatch.setObject(1, event.game);

insertBatch.setString(2, event.type);

insertBatch.setObject(3, event.actor);

insertBatch.setObject(4, event.target);

insertBatch.setString(5, event.arg1);

insertBatch.setObject(6, event.arg2);

insertBatch.setObject(7, event.arg3);

insertBatch.setTimestamp(8, new Timestamp(event.created.getTime()));

insertBatch.addBatch();

}

}

if (insertBatch != null){

insertBatch.executeBatch();

ResultSet generatedKeys = insertBatch.getGeneratedKeys();

for (Event event : events) {

if ( generatedKeys == null || ! generatedKeys.next()){

logger.warn("Unable to retrieve all generated keys");

}

event.id = generatedKeys.getLong(1);

}

logger.debug("events inserted");

}

Can we have a way to do it by fluent JDBI?

Thanks

JDBI is great!

-pl

[Brian McCallister]

You can, see

https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/TestUpdateGeneratedKeys.java

and

https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/sqlobject/TestGetGeneratedKeys.java

-Brian

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Plap]

Thanks for your reply.

Yes, I know I can do single insert statement retrieving generated key. Sorry but, as this topic states, I mean: how I can do batch inserts with retrieval of generated keys in JDBI? I see you asked:

How would you do this in JDBC?

 so I posted my JDBC with MySQL code. 

In my code I've got a for loop doing many insertBatch.addBatch(); and, at the end, a single insertBatch.executeBatch() that performs all the inserts and a single insertBatch.getGeneratedKeys() that gives back generated keys. 

Can we have JDBI fluent and Object API able to perform batch inserts with retrieval of generated keys?

[Brian McCallister]

Oh, doh. Sorry about that. I am en route to airport, will see if I can add that functionality while in the air, and cut a release this evening :

[Brian McCallister]

Okay, couldn;t do this on the flight as it is not supported in H2 (normal unit test server). Set up connector-mxj to use mysql for it today. It will be slightly awkward to do, so isn't done yet. Basically I need to change how prepared batches acturlaly execute to the the kind of munging done in the normal statements :-(

Do you need this REAL SOON, or … as is convenient?

-Brian

[Plap]

No, isn't needed soon, Thanks so much. It's a poc project without a due date. For now I can go on well using that JDBC code... I was just asking about it.

Note that JDBC provide 2 ways to do it and it seams DBs choose what one to implement. MySql as no need to know what are the columns (since there can be at least one auto-increment column per table). But I'm pretty sure Oracle needs to know column names or indexes from where to retrieve auto-generated keys.

• http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int) no columns
  
• http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int[]) column indexes
• http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20java.lang.String[]) column names

-pl.

[Brian McCallister]

I haven't had a chance to do it yet (not even started) -- so is wide open for anyone else to work on. I'll email the list when I get a chance to start on it.

-Brian

On Wed, Apr 24, 2013 at 7:56 AM, Paul Schorfheide <psch...@gmail.com> wrote:

Any update on this? I'm looking for something similar.

Thanks,

Paul

[Andrew Lee]

Oh no!, 

http://xkcd.com/979/

I require this functionality. 

Did you ever get round to this? 

[arte...@gmail.com]

I've tried to implement this. https://github.com/brianm/jdbi/pull/101
Not very good solution, but should works. I didn't test it on MySQL or Oracle databases though.

Artem

[Michael Spiegel]

+1 on this feature request. It would be very handy.

[Jairam Chandar]

Was this implemented in any release of JDBI? Would be brilliant to have it. 

[squar...@gmail.com]

Is it safe to assume this feature is dead? I can make JDBI work for every other need in my project, but it looks like the lack of this feature will mean I have to select another tool since I do require generated keys be returned following batch inserts. This makes me sad since I'm a big fan of this project, it's been great to work with thus far.

If anyone else has found a reasonable work around I'd greatly appreciate hearing about it.

[David]

To anyone that comes along after me.

I was looking for a way to return batch inserted auto generated keys while using the Object API. I've since discovered that this functionality is available via the Fluent API, so for anyone that doesn't mind mixing methods it looks like this can be done. The question still remains (for me at least) as to whether or not this is possible using the Object API - I've gotten exactly nowhere with that.

Relevant unit test:

https://github.com/jdbi/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/TestPreparedBatchGenerateKeys.java

This method works for me using Oracle 12c driver 12.1.0.2.0.

[Steven Schlansker]

Hi David,

Glad to hear jdbi meets most of your needs, although totally understandable that
you can't stick with the project unless it fits all of them!

The feature is not dead per se -- my sense is that it actually could be made to
work with the SqlObject feature as well as the already-working fluent version
with a relatively small contribution.

Unfortunately the core contributors right now are already spread fairly thin,
we are working towards a lot of internal cleanup and rewrite to present a new
version that is Java 8 optimized (the current codebase is a little old and creaky
around the edges, time for a refresh!). If you need an immediate solution, mixing Fluent
into your SqlObject based workflow just for the "advanced" cases that SqlObject cannot
handle is fairly common to projects that use jdbi. In fact, if you are on Java 8, you can
even possibly include them as a default method (usually you have to write two classes
and have one delegate to the other).

If you are willing to spend some time to flesh out the feature, I would be happy
to answer any questions you might have. If you cannot contribute it yourself,
you could take a look around the bug tracker and make sure there is a clear feature
request covering your use case, and I can try to make some time to implement
it in the coming weeks.

> For more options, visit https://groups.google.com/d/optout.

[David]

Steven,

I really appreciate the reply. 

While it is true that I'm able to retrieve generate ids following a batch insert via the fluent API, I'm not really able to get it to cleanly play well with the Object API transaction already in process. There are one to many relationships between items I'm persisting using the Object API and the two items I need to persist in batch.

At this point in time I've spent a few days hammering on it and I just don't see it working out. I've gone down too many rabbit holes chasing a workable solution. I think it's time to cut my losses and move on. 

I definitely see a lot of promise in JDBI (though I feel a need to mention how poor the documentation is considering the age of the project - better doc would most likely have literally saved me two or three days time) and I'll keep an eye on it for future use.

[Matthew Hall]

I agree with you on the state of our documentation. I started using JDBI about 18 months ago, and frequently had to turn to the source code for definitive answers where the documentation was silent.

Can I ask you a favor? I've been close to the project for long enough that I've lost the newcomer's perspective. Would you submit issues to Github for each area where you feel the documentation needs work?

Aside from that, I find our current documentation format very cumbersome. It would be less work to maintain documentation (and easier for folks to submit pull requests!) if we used a format supported natively by Github. AsciiDoc would probably be my pick.

[Steven Schlansker]

> On Mar 22, 2016, at 8:50 AM, Matthew Hall <quali...@gmail.com> wrote:
>
> I agree with you on the state of our documentation. I started using JDBI about 18 months ago, and frequently had to turn to the source code for definitive answers where the documentation was silent.

Yeah, I'm also in agreement here. Unfortunately writing docs is a lot less fun than writing code, but it's still on my TODO list to spruce up the docs a lot.

> Can I ask you a favor? I've been close to the project for long enough that I've lost the newcomer's perspective. Would you submit issues to Github for each area where you feel the documentation needs work?
>
> Aside from that, I find our current documentation format very cumbersome. It would be less work to maintain documentation (and easier for folks to submit pull requests!) if we used a format supported natively by Github. AsciiDoc would probably be my pick.

I'm OK with that, it also solves our problem of where to host the docs (right now it's on Brian's personal server I think) -- we could make a GitHub page instead.
Also, would it be interesting to merge the docs into the main repo, so that when we take PRs we can see doc changes alongside code changes?

[Matthew Hall]

> Also, would it be interesting to merge the docs into the main repo, so that when we take PRs we can see doc changes alongside code changes?

I could get behind that. Ideally whatever we do should make it easy to put site documents on e.g. jdbi.github.io

[David]

My advice, as a new user who had a bit of a struggle,  is to start over mostly from scratch. All existing documentation that I could find was either out of date (old version), incomplete, or barely skin deep. Typically it was just enough to get me moving in a certain direction upon which I would quickly become dependent on Stackoverflow, searching for specific code examples in this group and in existing unit tests, or diving in to the source code itself. I found myself going through this progression a number of times on a number of topics.

This is often the case with open source, and I still appreciate what exists and what is here, but I could definitely see this project going to the next level if solid documentation accompanied it.