2

As we have seen that after you get a data source. We need to configure SQL dialects based on the database we use. After we select a particular dialect, How would that be used to make SQL queries specific to DB. Do frameworks like hibernate and JOOQ construct SQL queries in string based on the selected dialect ? If so which would be the most optimal way to support this in a framework of our own ?

Abhay Jatin Doshi
  • 464
  • 4
  • 17
  • I highly doubt that anyone can tell you the exact implementations of these ORM solutions unless you've somehow got your hands on open source versions of those. For EF, I know it does actually just create prepared statements (basically in string form), depending on the configuration and connection used, but the actual transformation is done by another library (the EF db provider). – DevilSuichiro Apr 29 '19 at 19:25
  • 1
    @DevilSuichiro: Clear your doubts, I'm here to answer. – Lukas Eder Apr 30 '19 at 07:13

1 Answers1

7

Do frameworks like hibernate and JOOQ construct SQL queries in string based on the selected dialect

Yes. In jOOQ, there's an internal StringBuilder that collects SQL fragments from your expression tree, which are generated for your target SQL dialect specifically. You can see how that works in action on this website: https://www.jooq.org/translate. Try translating for example this input: SELECT * FROM t LIMIT 1 (which could correspond to your jOOQ API usage ctx.selectFrom(T).limit(1). It translates to:

-- Oracle 12c and more
SELECT * FROM t FETCH NEXT 1 ROWS ONLY

-- Oracle 11g and less
SELECT *
FROM (
  SELECT x.*, rownum rn
  FROM (SELECT * FROM t) x
  WHERE rownum <= 1
)
WHERE rn > 0

If so which would be the most optimal way to support this in a framework of our own ?

You need:

  1. An expression tree representation of your SQL query.
  2. Optionally, you can parse a string to build this expression tree, like jOOQ's parser if you want to support actual SQL, or you can have your own language abstraction like Hibernate did with HQL / JPQL
  3. Traverse that expression tree using something like a visitor to collect the SQL strings and bind variables.

But!

Do not build your own when you have off the shelf products like jOOQ or to some lesser extent Hibernate that can do the same. Building such a generic SQL abstraction is really difficult, and unless you want to actually sell such a product (you probably don't given your question), investing this time into building this product is not worth it at all.

The above LIMIT emulation is one of the more simple examples from jOOQ. Here's a lot more to help you decide against rolling your own, and that answer is still just scratching the surface of what jOOQ does behind the scenes.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • I do agree with you stating that writing another framework for same thing an existing framework is already offering would be waste of time. The motivation behind asking this question was to know what internally happens in well known frameworks, Are there multiple ways with its own benefits that solve this dialect problem. If yes, which way should I consider for my app that I'm trying to build. Thanks, As only today I realised the number of problems we get when wrapping DB-specific SQL queries. @LukasEder – Abhay Jatin Doshi Apr 30 '19 at 11:53
  • @AbhayJatinDoshi: Yes, well, there is an endless stream of special cases just when it comes to SQL syntax support, or JDBC API support. This isn't even touching on the topic of having to manually rewrite a slow query for a specific RDBMS, because that RDBMS's optimiser is not very good :) – Lukas Eder Apr 30 '19 at 11:58