Digest for jooq-user@googlegroups.com - 18 updates in 3 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 18 updates in 3 topics

http://groups.google.com/group/jooq-user/topics mailing list
BoPe86 <[hidden email]>: Nov 09 06:51AM -0800

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 <[hidden email]>: Nov 09 04:17PM +0100

Hi,
 
Thanks for your questions. I'm happy to hear that you've been mostly
successful with jOOQ thus far. I will comment inline:
 
> 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
[hidden email]: Nov 09 07:26AM -0800

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 <[hidden email]>: Nov 09 04:39PM +0100

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
 
[hidden email]: Nov 09 07:39AM -0800


> 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 <[hidden email]>: Nov 09 04:43PM +0100

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 <[hidden email]>: Nov 09 08:01AM -0800

For the record, this question was also asked on Stack Overflow:
https://stackoverflow.com/q/47185044/521799
[hidden email]: Nov 09 08:07AM -0800

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 <[hidden email]>: Nov 09 05:10PM +0100

What does your *procedure* do? As in the procedural code inside of the
procedure body.
 
[hidden email]: Nov 09 08:21AM -0800

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 <[hidden email]>: Nov 09 05:24PM +0100

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
 
[hidden email]: Nov 09 08:33AM -0800

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 <[hidden email]>: Nov 09 05:41PM +0100

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
 
[hidden email]: Nov 09 09:09AM -0800

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 <[hidden email]>: Nov 09 06:11PM +0100

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
 
Lukas Eder <[hidden email]>: Nov 09 04:45PM +0100

Hi Niclas,
 
Is there still anything I can be of assistance with?
 
Thanks,
Lukas
 
[hidden email]: Nov 09 04:25AM -0800

One of my table is linked with VisitListener and it added some conditions
based on the table i had selectFrom in SelectQuery object.
In one of my scenarios, i don't want the records to be added to the
condition.
 
I cannot in VisitListenerImpl since it effects when the same table is
selected in separate scenarios.
Is there any way to removeConditons already added in SelectQuery object or
avoid VisitListener to invoke only when called from specific method.
Lukas Eder <[hidden email]>: Nov 09 04:05PM +0100

Hi there,
 
I'm sorry, but your question isn't really very clear.
 
- What do you mean by table being linked with VisitListener?
- Where did you add the conditions to?
- What do you mean by records being added to the condition?
 
Perhaps a minimal, complete, verifiable example in code would make it a bit
more clear?
https://stackoverflow.com/help/mcve
 
Thanks,
Lukas
 
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].