JPA Join Fetch

A couple of key points to have a successful join fetch in JPA (backed by Hibernate):

• Why do we want join fetch? JPA default fetch strategy is "Lazy", which has better performance under most circumstances. But, sometimes we do want to retrieve all children (and sometimes children's children). In that case, join fetch allows us to get everything in one Database round-trip instead of N (or N*N round-tips). This change can easily speed up those queries for 100 times (in my case it went from 20 seconds to 0.2 seconds).
• Make sure in your Entity classes, the child collections are "Set" not "List". If you get the error message: "Hibernate cannot simultaneously fetch multiple bags". This is the root cause.
• How to join multiple levels: 

        root
          .fetch([childAttributeName], JoinType.LEFT)
          .fetch([grandChildAttributeName], JoinType.LEFT);

• Fetch join will return multiple duplicated rows for the parent entity. This is usually undesirable, wrap the return set in a LinkedHashSet will get the unique parent entities in the original select order. (See this Stack Overflow post)

Read MS Access File into CFQuery

Here is code snippet that will read arbitrary Microsoft Access file, and return the result in CFQuery. No Coldfusion data source is required.

Environment:
- Coldfusion 9, 64-bit

Notice, in the snippet, there is a dump of all DB drivers loaded by Coldfusion. So, if you ever need access to other dynamic data connection, just swap out the connection string in the sample.