Here's a more flushed out solution but can't figure out how to build the
Val object to put in the insert map. Keeps throwing exception that method
doesn't exist for that constructor. Probably some access thing with
private class.
if(ctx.batchQueries().length > 0 && ctx.batchQueries()[0] instanceof Insert){
Connection connection = ctx.connection();
try {
InsertQuery<?> query= (InsertQuery<?>) ctx.batchQueries()[0];
Field f =
query.getClass().getSuperclass().getSuperclass().getDeclaredField("table");
f.setAccessible(true);
Table table = (Table) f.get(query);
String tableName = table.getName();
int batchSize = (ctx.batchQueries().length > 1) ?
ctx.batchQueries().length : 1;
connection.createStatement().execute("LOCK TABLE sequence WRITE");
ResultSet resultSet = connection.createStatement().executeQuery("SELECT
value FROM sequence WHERE name='" + tableName + "'");
connection.createStatement().execute("INSERT INTO sequence (name,
value) VALUES ('"+ tableName +"',"+ batchSize +") ON DUPLICATE KEY UPDATE
value=value+"+batchSize);
connection.createStatement().execute("UNLOCK TABLES");
Integer id = (resultSet.next()) ? resultSet.getInt(1) : 1;
for(Query batchedQuery: ctx.batchQueries()) {
Method method =
batchedQuery.getClass().getDeclaredMethod("getValues");
method.setAccessible(true);
Map insertFields = (Map) method.invoke(query);
TableField primaryKey = (TableField)
table.getPrimaryKey().getFields().get(0);
//create value
Class<?> cl = Class.forName("org.jooq.impl.Val");
//TODO: figure out how to get this to work
Object val = cl.getConstructor(Integer.class,
DataType.class).newInstance(id, primaryKey.getDataType());
insertFields.putIfAbsent(primaryKey, val);
}
} catch (Exception e) {
log.error("Error while trying to update sequence table", e);
}
}
On Wednesday, October 5, 2016 at 2:16:03 PM UTC-4, Eric Newbury wrote:
>
> Here's my current code
>
> public void executeStart(ExecuteContext ctx) {
> if(ctx.query() instanceof Insert){
> Connection connection = ctx.connection();
>
> //TODO: figure out how to get the table name
> String tableName="domain";
> int batchSize = (ctx.batchQueries().length > 1) ?
> ctx.batchQueries().length : 1;
> try {
> connection.createStatement().execute("LOCK TABLE sequence WRITE");
> ResultSet resultSet =
> connection.createStatement().executeQuery("SELECT value FROM sequence WHERE
> name='" + tableName + "'");
> connection.createStatement().execute("INSERT INTO sequence (name,
> value) VALUES ("+ tableName +","+ batchSize +") ON DUPLICATE KEY UPDATE
> value=value+"+batchSize);
> connection.createStatement().execute("UNLOCK TABLES");
>
> Long id = null;
> if(resultSet.isBeforeFirst()){
> id = resultSet.getLong(0);
> }
> else{
> id = 1l;
> }
>
> //TODO: figure out how to set the id value of the queries being
> executed, as well as batched statments
>
> } catch (SQLException e) {
> log.error("Error while trying to update sequence table", e);
> }
> }
>
>
>
>
> On Wednesday, October 5, 2016 at 1:37:03 PM UTC-4, Eric Newbury wrote:
>>
>> One final question. From ExecuteContext, how can I update the query if
>> the id is null? Here are some thoughts: If I could get access to the
>> table, I get get the primary key and use that update the field on
>> ctx.record()
>> I don't see a way to getTable() though. And would updating the field on
>> record be enough? Would I also have to update query()?
>>
>> On Wednesday, October 5, 2016 at 1:20:19 PM UTC-4, Eric Newbury wrote:
>>>
>>> set session transaction read write
>>> SET autocommit=0
>>> select value from sequence where name='domain' for update
>>> update sequence set value=2 where value=1 and name='domain'
>>> commit
>>> SET autocommit=1
>>>
>>> Weird, it seems to still be trying to do transactional stuff even on
>>> myisaam.
>>>
>>>
>>> On Wednesday, October 5, 2016 at 12:39:04 PM UTC-4, Lukas Eder wrote:
>>>>
>>>> Hi Eric,
>>>>
>>>> You could make your JPA provider log all its SQL statements to see
>>>> what's going on. I have a slight feeling that they might correlate fetched
>>>> IDs to Java thread IDs to decrease the risk of race conditions. But I'm
>>>> curious myself, let us know if you find out.
>>>>
>>>> Cheers,
>>>> Lukas
>>>>
>>>> 2016-10-05 18:24 GMT+02:00 Eric Newbury <[email protected]>:
>>>>
>>>>> Haha, nope we are definitely not following a smart path with myIsaam
>>>>> and sequence tables in general. I think it came about to support multiple
>>>>> environments and avoiding clashing ids when replicating between them. The
>>>>> plan is to be off of it by the end of the month, but for the sake of
>>>>> supporting it now...
>>>>>
>>>>> Looks like I may want to go with an ExecuteListener then, and check
>>>>> for inserts with a null primary key value.
>>>>>
>>>>> I think I more meant to say, concurrency safety on the db side vs
>>>>> thread safety. i.e. if I read the current sequence value, then update
>>>>> it,
>>>>> but another application read it in between reading and updating, I'm in
>>>>> trouble. I can't SELECT FOR UDPATE because it's not innoDB and there are
>>>>> no transactions. Seems that there has to be a way since JPA supports it
>>>>> on
>>>>> myisaam and we haven't seen any issues. This seems to be less of a jooq
>>>>> question though and more just mysql, but throwing it out there in case
>>>>> you
>>>>> had an idea.
>>>>>
>>>>> On Wednesday, October 5, 2016 at 12:06:28 PM UTC-4, Lukas Eder wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> That's an interesting approach. I'd worry very much about data
>>>>>> integrity and race conditions in your case. Are you sure that's the best
>>>>>> way to generate IDs in your system? I'd say a UUID based approach might
>>>>>> be
>>>>>> safer (and easier)...
>>>>>>
>>>>>> A RecordListener is invoked only when you use any of
>>>>>> (Updatable)Record's insert(), update(), store(), refresh(), delete()
>>>>>> methods. It is not invoked, for instance, when you run ordinary INSERT
>>>>>> statements. Just to make sure you're aware of that.
>>>>>>
>>>>>> The insertStart() method receives a RecordContext argument, which is
>>>>>> a Scope, which contains a Configuration. From that, you can access all
>>>>>> of
>>>>>> your SPIs (such as ConnectionProvider), or simply wrap that
>>>>>> Configuration
>>>>>> in a new DSLContext using:
>>>>>>
>>>>>> @Override
>>>>>> public void insertStart(RecordContext ctx) {
>>>>>> DSL.using(ctx.configuration()).execute("...");
>>>>>> }
>>>>>>
>>>>>>
>>>>>> This approach is thread safe, after all, you're in the same thread as
>>>>>> the Record.insert() operation. But it would also be possible to design
>>>>>> your
>>>>>> SPIs in a thread safe way, otherwise.
>>>>>>
>>>>>> But again, I'm not quite convinced that you're following a good path
>>>>>> here, specifically on MyISAM
>>>>>> Hope this helps,
>>>>>> Lukas
>>>>>>
>>>>>> 2016-10-05 16:01 GMT+02:00 <[email protected]>:
>>>>>>
>>>>>>> Our legacy mysql database uses a table to store the next id for each
>>>>>>> table. We have been using spring JPA's TableGenerator strategy in the
>>>>>>> past, and would like to figure out how to use jooq with this. It looks
>>>>>>> like RecordListeners are the way to go, but how would I actually
>>>>>>> execute
>>>>>>> queries from within an insertStart method. There is no dsl context
>>>>>>> available, and also, I'm not sure how to ensure that it is thread safe.
>>>>>>> I
>>>>>>> was thinking using select for update would be a good way to go, but we
>>>>>>> are
>>>>>>> using myisaam (at least for the next few weeks until the upgrade
>>>>>>> *crossed
>>>>>>> fingers*) which doesn't support transactions. Makes me curious how
>>>>>>> hibernate does it without id collisions.
>>>>>>>
>>>>>>> I guess, it would be really handy to see a code example of how to
>>>>>>> support table based id generation. Looking forward to getting on auto
>>>>>>> increment soon, but for now, and for the sake of having a reference on
>>>>>>> the
>>>>>>> internet...
>>>>>>>
>>>>>>> Thanks a ton.
>>>>>>>
>>>>>>> --
>>>>>>> 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 [email protected].
>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>
>>>>>>
>>>>>> --
>>>>> 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 [email protected].
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
