Hi Kamal, Thank you very much for your message. I'll comment inline.
2017-05-15 19:03 GMT+02:00 Kamal raj <[email protected]>: > Hi All, > > I'm trying to build some dashboard application which allows the user to > connect to their database and run queries. The resultset then will be feed > into the charts for rendering. I want to understand few things: > > 1. I guess from the documentation we could able to connect to database on > fly and run queries from plain string. Do let me know are there any best > practices to handle database connections on fly. As you could guess, each > user might have different database sources configured. So imagine if I > could create say 100 connections for each user (on different DB's sources) > wont that be an overhead? Any suggesstions or patterns would be really > helpful here. > This isn't strictly related to jOOQ, which operates on user Connections / DataSources and doesn't manage them for you. I certainly wouldn't create 100 live connections per user, except if you have very very few users accessing your application in parallel. Ideally, you might have a connection pool, although in your case, the requirements are quite unique. Perhaps, this would be a good question for stackoverflow.com? We also have some interesting guest content on the jOOQ blog by some experts in that area: - https://blog.jooq.org/2017/02/21/jooq-tuesdays-brett-wooldridge-shows-what-it-takes-to-write-the-fastest-java-connection-pool/ - https://blog.jooq.org/2016/11/02/applying-queueing-theory-to-dynamic-connection-pool-sizing-with-flexypool/ > 2. Giving plain string directly to the Jooq system will be an security > issue by any chance? Do the developer need to do the sanitization of the > inputs? > Yes, of course. jOOQ protects you against SQL injection when you use the jOOQ DSL. When you're using jOOQ's "plain SQL" API More info here: - https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/ - https://www.jooq.org/doc/latest/manual/sql-building/bind-values/sql-injection/ - https://blog.jooq.org/2016/12/05/prevent-sql-injection-with-sql-builders-like-jooq/ - https://blog.jooq.org/2013/11/05/using-sql-injection-vulnerabilities-to-dump-your-database/ Note that all plain SQL API is annotated with the @PlainSQL annotation. You could use the checker framework to prevent compile time access to the plain SQL API in all areas of your code that should not access this API: - https://blog.jooq.org/2016/05/09/jsr-308-and-the-checker-framework-add-even-more-typesafety-to-jooq-3-9/ > 3. Since user will be able to connect to their own data source, I want to > show them their Tables on the UI. I want to filter out each table along > with its column details, like each column foriegn key constraints, their > type etc. Is that possible to do in all databases (remember user could > connect to any database that let's say Jooq supports) > Yes you could do that using DSLContext.meta(): - https://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#meta-- -- 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.
