Hi Chook, Thanks for your interest in jOOQ.
2015-03-31 18:27 GMT+02:00 <[email protected]>: > Hi - I want to give jOOQ a chance while I refactor my Java-SQL code. I use > MySQL and Java 7. > > Throughout the years I've created a rather complex > StringRegex->PreparedStatement->ResultSet queries using only Java and JDBC. > > Why? > I have a few tables (say 5) for each customer (100s-1000s customers) and > for each table I have several proxy tables for different aggregations > (needed optimization) so I had to create a "prepared statement" for table > names. > I only query one customer a time, so the "transaction" doesn't care about > the tables of other customers. This separation also enables to scale the > database instance out. > There's an interesting bit about this kind of multi-tenancy architecture that I'm going to blog about, soon. While I don't know how MySQL would behave in productive instances, I can share a bit about Oracle and its awesome cursor cache. By duplicating semantically equivalent tables, you will enforce new hard parses for what would otherwise be identical queries on a per tenant basis. While this architecture may help scaling out rather easily, it can become an issue when scaling up because of cursor cache contention. Just some thoughts - this may or may not affect you. With 5 tenants, there would certainly not be a problem, but with 1000, it is certainly an issue to look into. 1) Is there something similar in jOOQ where I can put a parameterized table > name? > Yes! This is called runtime schema or table mapping in jOOQ: http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/runtime-schema-mapping/ For much more sophisticated use-cases, there is also the SQL transformation SPI where you can implement VisitListeners to manipulate individual SQL AST transformation steps: http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation/ I think the former would be sufficient in your case. 2) Today I have a request logic that will pick the correct table. Is there > a way to "combine" that with the jOOQ level? Or will I have to separate it > as a different layer? (also relevant to the optional multiple connection > pools) > You can inject both of the above behaviours into jOOQ, but you will have to do so on a layer above jOOQ. Ideally, your DAOs will get a pre-configured jOOQ DSLContext object, where you initialise the Configuration and Settings transparently: http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/ > 3) When inserting data, I insert to all of the tables using INSERT .. ON > DUPLICATE KEY UPDATE with several updates (comma separated). Is that > supported? > Yes. See: http://www.jooq.org/doc/latest/manual/sql-building/sql-statements/insert-statement/ Although, some syntax might not yet be supported. What exact query did you have in mind? > I'm excited to get started! but really wish to get some information before > diving in to the API and tradeoffs. > Feel free to ask, that's what we're here for. Cheers, Lukas -- 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.
