Calling stored procedures in batch using JOOQ

[BoPe86]

I'm a JOOQ newbee. In my company i have been tasked with implementing JOOQ into our Java application while maintaining full backwards compatibility. That means (as a first step) implementing JOOQ only so it can, as a proxy, call any of existing 800 MySql stored procedures we have (big chunk of data processing logic is on DB side so that GUI can only show what it has been presented with) and return selected db data in form of JSON.

For me it's a great opportunity to learn about JOOQ and so far i have been successful: i managed to learn most basic stuff like generating classes according to DB structure, i managed to implement simple things for returning data of "select" stored procedures and so on. Now, i have a problem. Parts of my application require batch inserts in sense that instead of using CallableStatement.addBatch(...) and then CallableStatement.executeBatch() i have to use JOOQ.

I naively tried this for inserting 2 rows at once:

Insertdataprocedure p = new Insertdataprocedure();

p.setInName("John");

p.setInSurname("Doe");

p.setInName("Someone");

p.setInSurname("Else");

hoping "p" will just append parameters and execute procedure once while inserting 2 sets of data, but no luck :(

What's the proper way of calling stored procedures in batched manner using JOOQ?

Sounds like going against the purpose of using JOOQ, but at this moment i really have to use JOOQ just as a pass-through for calling existing stored procedures. Moving existing MySql procedures code to JOOQ would take months, it's scheduled for a later stage.

[Lukas Eder]

Hi,

Thanks for your questions. I'm happy to hear that you've been mostly successful with jOOQ thus far. I will comment inline:

2017-11-09 15:51 GMT+01:00 BoPe86 <bozid...@gmail.com>:

I naively tried this for inserting 2 rows at once:

Insertdataprocedure p = new Insertdataprocedure();

p.setInName("John");

p.setInSurname("Doe");

p.setInName("Someone");

p.setInSurname("Else");

hoping "p" will just append parameters and execute procedure once while inserting 2 sets of data, but no luck :(

That's an intereting assumption, but no, the "p" reference is a reference to a stored procedure call that you're going to make. It contains a set of input parameters and a set of output parameters, but no notion of sequential calls.

 

What's the proper way of calling stored procedures in batched manner using JOOQ?

There currently is none, and I must admit, I have not yet come across a use-case where stored procedures would be called in a batch. Usually, people would write anonymous blocks (e.g. PL/SQL BEGIN .. END blocks) that dynamically call a set of stored procedures. Alternatively, there would be a staging temp table that contains all the data, and then a procedural loop would batch those calls directly in the database.

Would you mind describing this use-case a bit? Are you trying to save bandwidth between client and server for consecutive procedure calls?

Thanks,

Lukas

[bozid...@gmail.com]

Thanks for your answer. The problem i'm trying to solve is that application i have will, in some cases, have to insert 1000 rows at once. Every 10 seconds. While using JDBC i discovered that calling "insert" 1000 times separately (even though each insert takes about 1ms) is way slower than jamming 1000 in-params into a single "insert" call.

[Lukas Eder]

Sure, but when you say "insert", why run through a stored procedure? jOOQ has support for batch statements [1] and a data loading API [2]. Or you can write a single insert as well by adding several rows to the VALUES clause.

Now, you probably can't do that because you have some logic in your stored procedure. This is why I asked for the use-case.

[1]: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution

[2]: https://www.jooq.org/doc/latest/manual/sql-execution/importing

2017-11-09 16:26 GMT+01:00 <bozid...@gmail.com>:

Thanks for your answer. The problem i'm trying to solve is that application i have will, in some cases, have to insert 1000 rows at once. Every 10 seconds. While using JDBC i discovered that calling "insert" 1000 times separately (even though each insert takes about 1ms) is way slower than jamming 1000 in-params into a single "insert" call.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[bozid...@gmail.com]

This is another thing i tried, but it failed as my "AbstractRoutines" are actually "insert":

BatchBindStep step = null;

    

for (AbstractRoutine<Void> procedure : procedures) {

  step = context.batch (context.select(procedure.asField()));

} 

step.execute();

[Lukas Eder]

Yeah, that doesn't make any sense. You cannot call a procedure like that in a SELECT statement in MySQL.

jOOQ doesn't see Routine and Query as the same thing, unlike JDBC, and I think it was a mistake for JDBC to do so.

But again, with some insight in your actual use-case (i.e. what is your procedure doing?), this discussion would be more clear to me, I suspect.

Thanks,

Lukas

--

[Lukas Eder]

For the record, this question was also asked on Stack Overflow:

https://stackoverflow.com/q/47185044/521799

[bozid...@gmail.com]

Yes, that's me as well because i really got stuck :)

In my current usecase i have code inherited from previous dev :\ Below please find a pseudo version, will not compile but should give an idea:

Please note: procedure name ("storedProcedureName") and actual list of parameters are specified from the outside, here i hardcoded them for sake of simplicity

LinkedList<Object[]> params = { ["John", "Doe"], ["Someone", "Else"], .... optional number of parameters, sometimes up to 1000 sets... };

String basicQuerry = "{call " + storedProcedureName + "(?, ?)}"; //this line is actually different, here i simplified because in example above stored procedure takes 2 input parameters.

cs = connection.prepareCall(basicQuerry);

for (int i = 0; i < params.size(); i++) {

Object[] parameters = params.get(i);

for(int i=1; i<=parameters.length; i++)

cs.setObject(i, parameters[i-1]);

cs.addBatch();

}

cs.executeBatch();

[Lukas Eder]

What does your *procedure* do? As in the procedural code inside of the procedure body.

2017-11-09 17:07 GMT+01:00 <bozid...@gmail.com>:

--

[bozid...@gmail.com]

SQL procedure looks like this:

PROCEDURE MainSchema.insertPerson(inName varchar(50),  inSurname varchar(50))

BEGIN

insert into Persons (Name, Surname, LogTime)

values (inName, inSurname, NOW());

       

        #there's auto-increment id as well, not important for this example

END

[Lukas Eder]

OK, interesting. If that's really all there is to it, a workaround would be to simply batch the insert and not use the stored procedure at all. Ideally, you'd be using the import API that I've mentioned earlier:

https://www.jooq.org/doc/latest/manual/sql-execution/importing

It allows for fine-tuning bulk / batch / commit sizes

--

[bozid...@gmail.com]

That's the simplest example i have, which needs batch inserts. There are more complex ones.

So, after all seems like i'll have to JOOQ-ize at least those SQL stored procedures which require batch insert.

[Lukas Eder]

I see - yeah, I'm afraid there's not much jOOQ can do for you right now - you'll need to find a workaround.

In any case, we'll think about it. This is certainly something we should add to the Routine or DSLContext API eventually:

https://github.com/jOOQ/jOOQ/issues/6813

Thanks,

Lukas

2017-11-09 17:33 GMT+01:00 <bozid...@gmail.com>:

That's the simplest example i have, which needs batch inserts. There are more complex ones.

So, after all seems like i'll have to JOOQ-ize at least those SQL stored procedures which require batch insert.

--

[bozid...@gmail.com]

Ah, i see! :) Thanks for considering that approach. Perhaps it could be useful for situations in which old and fully developed system that uses JDBC has to migrate to JOOQ step by step, while keeping backwards compatibility at all times. Hopefully that feature will be in some future release, i'll keep my eye on that.

In the meantime, i'll go with importing as per your suggestion above.

[Lukas Eder]

Sure it would be useful for those cases. Just that in around 10 years of doing jOOQ and many more years of working with JDBC, I simply haven't encountered the case where people wanted to batch stored procedure calls :)

Cheers,

Lukas

2017-11-09 18:09 GMT+01:00 <bozid...@gmail.com>:

Ah, i see! :) Thanks for considering that approach. Perhaps it could be useful for situations in which old and fully developed system that uses JDBC has to migrate to JOOQ step by step, while keeping backwards compatibility at all times. Hopefully that feature will be in some future release, i'll keep my eye on that.

In the meantime, i'll go with importing as per your suggestion above.

--