Monthly Archives: March 2014

Careful With Native SQL in Hibernate

I really like Hibernate, but I also don’t know a tool that would be nearly as powerful and deceptive at the same time. I could write a book on surprises in production and cargo cult programming related to Hibernate alone. It’s more of an issue with the users than with the tool, but let’s not get too ranty.

So, here’s a recent example.


We need a background job that lists all files in a directory and inserts an entry for each of them to a table.

Naive Solution

The job used to be written in Bash and there is some direct SQL reading from the table. So, blinders on and let’s write some direct SQL!

for (String fileName : folder.list()) {
    SQLQuery sql = session.getDelegate().createSQLQuery(
        "insert into dir_contents values (?)");
    sql.setString(0, fileName);

Does it work? Sure it does.

Now, what happens if there are 10,000 files in the folder? What if you also have a not so elegant domain model, with way too many entity classes, thousands of instances and two levels of cache all in one context?

All of a sudden this trivial job takes 10 minutes to execute, all that time keeping 2 or 3 CPUs busy at 100%.

What, for just a bunch of inserts?

Easy Fix

The problem is that it’s Hibernate. It’s not just a dumb JDBC wrapper, but it has a lot more going on. It’s trying to keep caches and session state up to date. If you run a bare SQL update, it has no idea what table(s) you are updating, what it depends on and how it affects everything, so just in case it pretty much flushes everything.

If you do this 10,000 times in such a crowded environment, it adds up.

Here’s one way to fix it – rather than running 10,000 updates with flushes, execute everything in one block and flush once.

session.doWork(new Work() {
    public void execute(Connection connection) throws SQLException {
        PreparedStatement ps = connection
                .prepareStatement("insert into dir_contents values (?)");
        for (String fileName : folder.list()) {
            ps.setString(1, fileName);

Other Solutions

Surprise, surprise:

  • Do use Hibernate. Create a real entity to represent DirContents and just use it like everything else. Then Hibernate knows what caches to flush when, how to batch updates and so on.
  • Don’t use Hibernate. Use plain old JDBC, MyBatis, or whatever else suits your stack or is there already.


Native SQL has its place, even if this example is not the best use case. Anyway, the point is: If you are using native SQL with Hibernate, mind the session state and caches.

ClojureScript Routing and Templating with Secretary and Enfocus

A good while ago I was looking for good ways to do client-side routing and templating in ClojureScript. I investigated using a bunch of JavaScript frameworks from ClojureScript, of which Angular probably gave the most promising results but still felt a bit dirty and heavy. I even implemented my own routing/templating mechanism based on Pedestal and goog.History, but something felt wrong still.

Things have changed and today there’s a lot buzz about React-based libraries like Reagent and Om. I suspect that React on the front with a bunch of “native” ClojureScript libraries may be a better way to go.

Before I get there though, I want to revisit routing and templating. Let’s see how we can marry together two nice libraries: Secretary for routing and Enfocus for templating.

Let’s say our app has two screens which fill the entire page. There are no various “fragments” to compose the page from yet. We want to see one page when we navigate to /#/add and another at /#/browse. The “browse” page will be a little bit more advanced and support path parameters. For example, for /#/browse/Stuff we want to parse the “Stuff” and display a header with this word.

The main HTML could look like:

<!DOCTYPE html>
	<div class="container-fluid">
		<div id="view">Loading...</div>

	<script src="js/main.js"></script>

Then we have two templates.


<h1>Add things</h1>
  <!-- boring, omitted -->


  <!-- boring, omitted -->

Now, all we want to do is to fill the #view element on the main page with one of the templates when location changes. The complete code for this is below.

(ns my.main
  (:require [secretary.core :as secretary :include-macros true :refer [defroute]]
            [ :as events]
            [enfocus.core :as ef])
  (:require-macros [enfocus.macros :as em])
  (:import goog.History

(em/deftemplate view-add "templates/add.html" [])

(em/deftemplate view-browse "templates/browse.html" [category]
  ["h1"] (ef/content category))

(defroute "/" []
  (.setToken (History.) "/add"))

(defroute "/add" []
    ["#view"] (ef/content (view-add))))

(defroute "/browse/:category" [category]
    ["#view"] (ef/content (view-browse category))))

(doto (History.)
    #(em/wait-for-load (secretary/dispatch! (.-token %))))
  (.setEnabled true))

What’s going on?

  1. We define two Enfocus templates. view-add is trivial and simply returns the entire template. view-browse is a bit more interesting: Given category name, alter the template by replacing content of h1 tag with the category name.
  2. Then we define Secretary routes to actually use those templates. All they do now is replace content of the #view element with the template. In case of the “browse” route, it passes the category name parsed from path to the template.
  3. There is a default route that redirects from / to /add. It doesn’t lead to, but only sets the fragment:
  4. Finally, we plug in Secretary to goog.History. I’m not sure why it’s not in the box, but it’s straightforward enough.
  5. Note that in the history handler there is the em/wait-for-load call. It’s necessary for Enfocus if you load templates with AJAX calls.

That’s it, very simple and straightforward.

Update: Fixed placement of em/wait-for-load, many thanks to Adrian!