It is all about the JDBC Basics

We’re very happy to announce a guest post by Marco Behler, who has been blogging about jOOQ in the past.

img31Marco started out in programming (reverse-engineering, actually) and now mainly programmes on the JVM in his day-to-day work. He also always had a sweet tooth for strategy and marketing. Marco Behler GmbH is the result of that hybrid role.

It is all about the JDBC Basics

It is one of the days.

You are reading the Spring documentation’s @Transactional section and still don’t understand the difference between logical and physical transaction scopes. Simultaneously your app throws an
LazyInitializationException and you have no idea why. To top it off you see spontaneous database deadlocks in production and you suspect your connection pool is leaking connections..somehow.

Know what most likely would have helped instead of banging your head against the wall? Spending a couple (literally) of hours on learning the JDBC basics. Let’s find out why:

What are the JDBC basics?

The basics are opening up/closing database connections and then working with transactions. Also understanding how deadlocks, pessimistic and optimistic locking work on a plain JDBC level. A bit of isolation levels and savepoints and then directly on to connection pools and jdbc driver logging. That’s it. Seriously.

Why are the basics so important?

Everything you will encounter in frameworks like Spring, Hibernate, jOOQ etc. builds up on these basics. For example, there are a gazillion topics on the internet regarding Hibernate’s LazyInitializationException and I was scared of that particular exception myself many years ago. But what else would you expect trying to query the database without having a connection to the database open (which is basically all that this exeception is) ?

The same with Spring’s “transaction framework”. There is so much content, or shall we say (F)ear/(U)ncertainty/(D)oubt, out there on how to open up transactions with spring, be it programmatically, with annotations or xml. But what if you knew that under the hood, there is only one way (and actually one line of code) to open up transactions in the JDBC world?

Let me not even get started on the various (mis)configurations of connection pools you see in production in the wild. Or the unawareness of JDBC (driver) logging, which usually leads to debugging in the wild. All basics, which you can master in a couple of hours and which will help you for a lifetime!

Why do people not just learn the basics?

In every middle-sized project there is a ton of technologies involved and there usually is no clear-cut path on how to learn all of them or how they all work together. It simply takes a lot of time and effort to dig through everything.

There’s JPA sessions and JDBC connections and then Spring somehow provides those transactional proxies in 5 different ways and then some other colleague just put jOOQ into the mix, but then somehow my session doesn’t flush and my objects don’t get persisted and the HibernateTransactionManager is not working as expected.

With all of this, I would also hope for my database transactions just to commit – god forbid what happens on rollback :)

But in the end, everything technology mentioned is just a layer on top of JDBC. If you understand transactions or deadlocks or savepoints on the basic level, then Spring or Hibernate or jOOQ will not throw you off.

So what do you recommend ?

If you want to get miles ahead in your day-to-day database programming, you have to start with the basics. Step-by-Step. And then you will see most of your problems automatically evaporate.

Out of my extensive database programming experience, I created an ebook with a ton of ready-to-run exercises, which will take you from Java database novice to expert. At your own pace. You can literally copy the source code of every chapter into your IDE, run it and (hopefully) learn from it. It covers plain JDBC, Spring, Hibernate, jOOQ (soon) and also distributed transactions.

You can read the whole book for free online here, and I would love to get your feedback! I would really like to let the community feedback flow back into future editions of the book. In addition, If you like what you see and the exercises help you, you can also show your support by getting a paid digital version (pdf, epub, mobi).

In any case…

…learn your JDBC basics – and you will profit from them for a lifetime!

JavaEE or Spring? Neither! We Call Out For a Fresh Competitor!

If you’ve been following some key Java people on Twitter or reading the “news” on Reddit, you could not have missed the hilarious quarrel between some Spring and JavaEE evangelists.

First, Jürgen Höller’s provocative article:

“Happy second birthday, Java EE 7! How is it going in production?”

Then, Reza Rahman’s response:

“The Ghosts of Java EE 7 in Production: Past, Present and Future”

And in between, there had been hundreds of equally amusing tweets.

If you haven’t already, take out your popcorn :)

EDIT: This discussion went on further than this blog post. There are also discussions going on:

Troll marketing

At Data Geekery, we’re certainly not unacquainted with the fun and effective technique that we like to refer to as “troll marketing”, e.g. when we recently ranted against the (ab-)use of annotations by both Java EE and Spring, or when we compared Hibernate with the good old Nokia 3310.

It’s childish, yes. But it is also effective. The only reason why it is effective is because by doing so, you’re stirring up a hornets’ nest. You have a point, after all, and you start triggering a very polarised discussion that has no right/wrong answer, which all participants will fail to accept, and thus fight. While they’re fighting, you can put your brand in each and every message. Bingo.

Spring folks do exactly the same thing. If you’ve heard a talk by Josh Long from Pivotal, you will certainly have noticed that much of the talk is just ranting about things like Internet Explorer. If you’re not the kind of person that takes everything personally, seriously, and/or literally, then such a talk can be quite amusing.

The interesting bit here is the fact that the “standards” evangelists react to Spring’s provocative article way too much. At the bottom of Reza’s article, there is a linked presentation by Bert Ertman, in favour of Java EE (and it wouldn’t be wrong to say that Reza thus endorses this talk’s content and style):

The key point I’m trying to make here is not that two opposing technology vendors try to get a hold of their communities, convincing people that their tech is better (that’s what engineers have been doing long before Tesla vs. Edison).

The key point I’m trying to make here is the fact that one of the vendors claims for themselves to be a “standard”, putting themselves (the JCP = Oracle) on eye level with real standards committees like ISO, DIN, w3c, IETF.

JavaEE is a Standard?

It is, in a way. It is a de facto standard, just like Spring. It is a very loose set of small interest groups that are governed mostly by Oracle (and other vendors) who try to promote and establish their interests and technology over alternative, “proprietary” tech.

JavaEE is a “standard” with a long history. It has always been a rather “weak” standard, which is why Spring was created in the first place. The fact that Reza from Oracle reacts so sensitively to Spring’s deliberate provocation can be interpreted as a sign that the “standard” is still weak and endangered by Spring as an alternative.

An interesting comparison could be made to Siebel vs. Salesforce. Siebel was the de facto “standard” in the CRM industry but Salesforce managed to harass Siebel so much that Siebel had to acknowledge Salesforce as a real competitor in its business long before they even remotely met on eye-level, revenue-wise. This was ultimately leading to Siebel’s demise and acquisition by – how ironic – Oracle, where much of the JCP is hosted.

The same thing has been happening with Java EE and Spring. Spring is now so extremely successful in the market, even if completely (= admittedly) proprietary, that Java EE will have to fight hard to get back on top of Spring. Much harder than merely calling out the old and lame “standards” argument. Java EE has to be better and faster than Spring, not just a “standard”. With all the JCP politics going on, I don’t see that happening, though.

So, will Spring win?

My prediction is: Nope.

This whole story just shows us that standards in our Java ecosystem are weak. There are an incredible amount of players on the market. Niche products like Hibernate and jOOQ can shake up the database access market segment. Niche products like GWT, Vaadin, ZK, Spring MVC, Play Framework, etc. can shake up the HTML UI market segment (no way JSF or JSR 371 can prevent further market fragmentation in this area).

Heck, language agnostic technologies like Akka or Vert.X show that not even the Servlet API is really sound and safe. And with Oracle having introduced Nashorn, the whole JavaScript ecosystem might just as well threaten Java EE. The only thing that can be taken for granted for another 5-10 years, I suspect, is the JVM and the Java SE JDK with its libraries, including JDBC.

Our industry is advancing so quickly right now, it seems to be impossible for a highly distributed, rather political committee like the JCP to keep up the pace and deliver the quality and innovation that we’re seeing elsewhere.

In other words, the Java EE “standard” is nothing more than a 5-10 year-behind body of, admittedly, proven technologies. Proven some time ago. Given that a single player like Pivotal has successfully challenged the “standard” for more than 10 years now, I suspect that new players might soon enter the market with new alternatives.

Who will be the new players?

One of them will be the soon-to-be-renamed Typesafe. Why do you think they’re getting rebranded after all? And what do you think they’re trying to prove with Akka + Play + ConductR? They’re trying to prove that you can do software entirely differently on top of the JVM, that’s what they’re trying to prove.

Besides, they’re using the same low-content, high-emotion troll marketing technique that Pivotal / Spring had been so successful with. Observe their use of the evil term “monolith” everywhere (= Java EE, what else):

monolith

Another one of them might be… Microsoft! Why not? Their recent Open Sourcing moves might help establishing .NET on top of Linux, eventually. And once you’re on top of Linux, you might as well get on top of the JVM. The .NET ecosystem is much more coherent than Java’s and this will exert a lot of innovation pressure on our ecosystem.

microsoft+linux

All of this is happening while the JCP is still recovering from the politics behind the game-changer data interchange APIs JSR-353, JSR-367, and JSR-374

Interesting future

The above predictions might or might not become true. But one thing I’m very certain of. We will have even more competition and thus more choices for our infrastructure software in the near future. The current de facto standards will lose market shares. Tough luck for them, better tech for us.

While the evangelists are fighting their little personal fights, I’m looking forward to a very interesting future on the JVM.

Read about a concurring opinion by our friends at Takipi here

Do You Want to be a Better Software Developer?

Bloggers are a different breed. They’re spending a lot of time investigating issues in a systematic way that is presentable to others. And then they share – mostly just for the fun of it and for the rewarding feeling sharing gives them. Whenever we google for a technical issue, chances are high that we stumble upon such a blog post.

One of the best blogs out there is Petri Kainulainen’s Do You Want to be a Better Software Developer? Petri has also written a book about Spring Data, which is available from Amazon, O’Reilly and Packt Publishing.

Most recently, I have found his two Maven-related tutorials very useful and well-written:

Also, in 2013, he has written an extensive series of blog posts titled “What I Learned This Week”. Some examples:

Petri’s blog is certainly one that you should follow. His posts are very well structured and quite complete. Currently, he’s also writing an extensive series about jOOQ, which is a very useful additional resource for new jOOQ users.

Thanks for all this great content, Petri!

Using jOOQ with JAX-RS to Build a Simple License Server

In some use-cases, having a lean, single-tier server-side architecture is desireable. Typically, such architectures expose a RESTful API implementing client code and the UI using something like AngularJS.

In Java, the standard API for RESTful applications is JAX-RS, which is part of JEE 7, along with a standard JSON implementation. But you can use JAX-RS also outside of a JEE container. The following example shows how to set up a simple license server using these technologies:

  • Maven for building and running
  • Jetty as a lightweight Servlet implementation
  • Jersey, the JAX-RS (JSR 311 & JSR 339) reference implementation
  • jOOQ as a data access layer

For the example, we’ll use a PostgreSQL database.

Example code

Now, before you go and copy-paste all the code off this blog post, consider downloading it from here, instead:
https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-jax-rs-example

The sample code is licensed under the terms of the Apache Software License 2.0.

Creating the license server database

We’ll keep the example simple and use a LICENSE table to store all license keys and associated information, whereas a LOG_VERIFY table is used to log access to the license server. Here’s the DDL:

CREATE TABLE LICENSE_SERVER.LICENSE (
  ID           SERIAL8      NOT NULL,

  -- The date when the license was issued
  LICENSE_DATE TIMESTAMP    NOT NULL,
  -- The e-mail address of the licensee
  LICENSEE     TEXT         NOT NULL,
  -- The license key
  LICENSE      TEXT         NOT NULL,
  -- The licensed version(s), a regular expression
  VERSION      VARCHAR(50)  NOT NULL DEFAULT '.*',

  CONSTRAINT PK_LICENSE PRIMARY KEY (ID),
  CONSTRAINT UK_LICENSE UNIQUE (LICENSE)
);

CREATE TABLE LICENSE_SERVER.LOG_VERIFY (
  ID           SERIAL8      NOT NULL,

  -- The licensee whose license is being verified
  LICENSEE     TEXT         NOT NULL,
  -- The license key that is being verified
  LICENSE      TEXT         NOT NULL,
  -- The request IP verifying the license
  REQUEST_IP   VARCHAR(50)  NOT NULL,
  -- The version that is being verified
  VERSION      VARCHAR(50)  NOT NULL,
  -- Whether the verification was successful
  MATCH        BOOLEAN      NOT NULL,

  CONSTRAINT PK_LOG_VERIFY PRIMARY KEY (ID)
);

To make things a bit more interesting (and secure), we’ll also push license key generation into the database, by generating it from a stored function as such:

CREATE OR REPLACE FUNCTION
LICENSE_SERVER.GENERATE_KEY(
    IN license_date TIMESTAMP WITH TIME ZONE,
    IN email TEXT
) RETURNS VARCHAR
AS $$
BEGIN
    RETURN 'license-key';
END;
$$ LANGUAGE PLPGSQL;

The actual algorithm might be using a secret salt to hash the function arguments. For the sake of a tutorial, a constant string will suffice.

Setting up the project

We’re going to be setting up the jOOQ code generator using Maven

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.jooq</groupId>
  <artifactId>jooq-webservices</artifactId>
  <packaging>war</packaging>
  <version>1.0</version>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>2.0.2</version>
        <configuration>
          <source>1.7</source>
          <target>1.7</target>
        </configuration>
      </plugin>

      <plugin>
        <groupId>org.mortbay.jetty</groupId>
        <artifactId>maven-jetty-plugin</artifactId>
        <version>6.1.26</version>
        <configuration>
          <reload>manual</reload>
          <stopKey>stop</stopKey>
          <stopPort>9966</stopPort>
        </configuration>
      </plugin>

      <plugin>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen-maven</artifactId>
        <version>3.2.0</version>

        <!-- See GitHub for details -->
      </plugin>
    </plugins>
  </build>

  <dependencies>
    <dependency>
      <groupId>com.sun.jersey</groupId>
      <artifactId>jersey-server</artifactId>
      <version>1.0.2</version>
    </dependency>
    <dependency>
      <groupId>com.sun.jersey</groupId>
      <artifactId>jersey-json</artifactId>
      <version>1.0.2</version>
    </dependency>
    <dependency>
      <groupId>com.sun.jersey.contribs</groupId>
      <artifactId>jersey-spring</artifactId>
      <version>1.0.2</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
    </dependency>

    <dependency>
      <groupId>org.jooq</groupId>
      <artifactId>jooq</artifactId>
      <version>3.1.0</version>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.2-1003-jdbc4</version>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.16</version>
    </dependency>
  </dependencies>
</project>

With the above setup, we’re now pretty ready to start developing our license service as a JAX-RS service.

The license service class

Once we’ve run the jOOQ code generator using Maven, we can write the following service class:

/**
 * The license server.
 */
@Path("/license/")
@Component
@Scope("request")
public class LicenseService {

  /**
   * <code>/license/generate</code> generates
   * and returns a new license key.
   *
   * @param mail The input email of the licensee.
   */
  @GET
  @Produces("text/plain")
  @Path("/generate")
  public String generate(
    final @QueryParam("mail") String mail
  ) {
    return run(new CtxRunnable() {

      @Override
      public String run(DSLContext ctx) {
        Timestamp licenseDate = new Timestamp(
          System.currentTimeMillis());

        return
        ctx.insertInto(LICENSE)
           .set(LICENSE.LICENSE_, generateKey(
                inline(licenseDate), inline(mail)))
           .set(LICENSE.LICENSE_DATE, licenseDate)
           .set(LICENSE.LICENSEE, mail)
           .returning()
           .fetchOne()
           .getLicense();
      }
    });
  }

  /**
   * <code>/license/verify</code> checks if a given
   * licensee has access to version using a license.
   *
   * @param request
   *   The servlet request from the JAX-RS context.
   * @param mail
   *   The input email address of the licensee.
   * @param license
   *   The license used by the licensee.
   * @param version
   *   The product version being accessed.
   */
  @GET
  @Produces("text/plain")
  @Path("/verify")
  public String verify(
    final @Context HttpServletRequest request,
    final @QueryParam("mail") String mail,
    final @QueryParam("license") String license,
    final @QueryParam("version") String version
  ) {
    return run(new CtxRunnable() {
      @Override
      public String run(DSLContext ctx) {
        String v = (version == null
                 || version.equals(""))
          ? ""
          : version;

        return
        ctx.insertInto(LOG_VERIFY)
           .set(LOG_VERIFY.LICENSE, license)
           .set(LOG_VERIFY.LICENSEE, mail)
           .set(LOG_VERIFY.REQUEST_IP,
                request.getRemoteAddr())
           .set(LOG_VERIFY.MATCH, field(
               selectCount()
              .from(LICENSE)
              .where(LICENSE.LICENSEE.eq(mail))
              .and(LICENSE.LICENSE_.eq(license))
              .and(val(v).likeRegex(LICENSE.VERSION))
              .asField().gt(0)))
           .set(LOG_VERIFY.VERSION, v)
           .returning(LOG_VERIFY.MATCH)
           .fetchOne()
           .getValue(LOG_VERIFY.MATCH, String.class);
      }
    });
  }

  // [...]
}

The INSERT INTO LOG_VERIFY query is actually rather interesting. In plain SQL, it would look like this:

INSERT INTO LOG_VERIFY (
  LICENSE,
  LICENSEE,
  REQUEST_IP,
  MATCH,
  VERSION
)
VALUES (
  :license,
  :mail,
  :remoteAddr,
  (SELECT COUNT(*) FROM LICENSE
   WHERE LICENSEE = :mail
   AND LICENSE = :license
   AND :version ~ VERSION) > 0,
  :version
)
RETURNING MATCH;

Apart from the foregoing, the LicenseService also contains a couple of simple utilities:

/**
 * This method encapsulates a transaction and
 * initialises a jOOQ DSLcontext. This could also be
 * achieved with Spring and DBCP for connection
 * pooling.
 */
private String run(CtxRunnable runnable) {
  Connection c = null;

  try {
    Class.forName("org.postgresql.Driver");
    c = getConnection(
        "jdbc:postgresql:postgres",
        "postgres",
        System.getProperty("pw", "test"));
    DSLContext ctx =
    DSL.using(new DefaultConfiguration()
       .set(new DefaultConnectionProvider(c))
       .set(SQLDialect.POSTGRES)
       .set(new Settings()
       .withExecuteLogging(false)));

    return runnable.run(ctx);
  }
  catch (Exception e) {
    e.printStackTrace();
    Response.status(Status.SERVICE_UNAVAILABLE);
    return "Service Unavailable";
  }
  finally {
    JDBCUtils.safeClose(c);
  }
}
private interface CtxRunnable {
    String run(DSLContext ctx);
}

Configuring Spring and Jetty

All we need now is to configure Spring…

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

  <context:component-scan
     base-package="org.jooq.example.jaxrs" />

</beans>

… and Jetty …

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>classpath:applicationContext.xml</param-value>
  </context-param>
  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>
  <listener>
    <listener-class>org.springframework.web.context.request.RequestContextListener</listener-class>
  </listener>
  <servlet>
    <servlet-name>Jersey Spring Web Application</servlet-name>
    <servlet-class>com.sun.jersey.spi.spring.container.servlet.SpringServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>Jersey Spring Web Application</servlet-name>
    <url-pattern>/*</url-pattern>
  </servlet-mapping>
</web-app>

… and we’re done! We can now run the server with the following command:

mvn jetty:run

Or if you need a custom port:

mvn jetty:run -Djetty.port=8088

Using the license server

You can now use the license server at the following URLs

http://localhost:8088/jooq-jax-rs-example/license/generate?mail=test@example.com
-> license-key

http://localhost:8088/jooq-jax-rs-example/license/verify?mail=test@example.com&license=license-key&version=3.2.0
-> true

http://localhost:8088/jooq-jax-rs-example/license/verify?mail=test@example.com&license=wrong&version=3.2.0
-> false

Let’s verify what happened, in the database:

select * from license_server.license
-- id | licensee         | license     | version
------------------------------------------------
--  3 | test@example.com | license-key | .*

select * from license_server.log_verify
-- id | licensee         | license     | match
----------------------------------------------
--  2 | test@example.com | license-key | t
--  5 | test@example.com | wrong       | f

Downloading the complete example

The complete example can be downloaded for free and under the terms of the Apache Software License 2.0 from here:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-jax-rs-example

J2eeBasedPreAuthenticatedWebAuthenticationDetailsSource… WAT??

WAT? This hilarious talk about the incredible quirks of JavaScript had been going around on Twitter and other media. In case you haven’t already, take a look at it here:

https://www.destroyallsoftware.com/talks/wat

Speaking of WAT, let’s talk about Spring Security. Spring has undertaken great effort to replace the clumsy, verbose parts of J2EE to replace them with straightforward and intuitive APIs. In Spring Security, you will find great features like the eraseCredentialsAfterAuthentication property, which keeps you from accidentally letting credentials lie around. Accidentally. What, like printing them to the standard out, or to the HTML document?? If this happens to you accidentally, then you might have a lot of other problems, too. Frameworks helping people to avoid such accidents remind me of PHP and this interesting post about how fundamentally broken PHP is:

http://me.veekun.com/blog/2012/04/09/php-a-fractal-of-bad-design/

If you’ve ever heard about PHP’s abominable “magic quotes”, you’ll understand what I mean.

More examples

There’s more. Spring has incredibly specialised API types, such as:

  • J2eeBasedPreAuthenticatedWebAuthenticationDetailsSource
  • PreAuthenticatedGrantedAuthoritiesWebAuthenticationDetails
  • PreAuthenticatedGrantedAuthoritiesAuthenticationDetails
  • GrantedAuthorityFromAssertionAttributesUserDetailsService
  • MutableGrantedAuthoritiesContainer
  • MethodSecurityMetadataSourceBeanDefinitionParser
  • AbstractUserDetailsServiceBeanDefinitionParser

Wow. I didn’t know Java identifiers were even allowed to be that long. And there’s more. Consider the package names (line break introduced artificially for improved readability):

org.springframework.security.web
   .authentication.preauth.websphere
   .WebSpherePreAuthenticatedWebAuthenticationDetailsSource

Note the repetition of concepts in both package and class names:

  • 2x “web”
  • 4x “authentication” (out of which 2x “pre-authenticated”)
  • 2x “websphere

For all of you who weren’t aware that the class browsing frame in the regular Javadoc frameset has a horizontal scrollbar, I invite you to navigate through the incredible verbosity of Spring Security:

http://static.springsource.org/spring-security/site/docs/3.1.x/apidocs/index.html

</rant>

A nice way of using jOOQ with Spring

This blog post is outdated. For a more up-to-date example of how to integrate jOOQ with Spring, please consider the relevant sections of the jOOQ manual!

A nice way of using jOOQ with Spring was recently discussed on Stack Overflow by Adam Gent:
http://adamgent.com/post/31128631472/getting-jooq-to-work-with-spring-correctly

The essence of it was given here in this gist:

package com.snaphop.jooq;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.jooq.ExecuteContext;
import org.jooq.impl.DefaultExecuteListener;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;

/**
 * Example provided by Adam Gent
 */
public class SpringExceptionTranslationExecuteListener
extends DefaultExecuteListener {

    @Override
    public void start(ExecuteContext ctx) {
        DataSource dataSource = ctx.getDataSource();
        Connection c = DataSourceUtils.getConnection(dataSource);
        ctx.setConnection(c);
    }

    @Override
    public void exception(ExecuteContext ctx) {
        SQLException ex = ctx.sqlException();
        Statement stmt = ctx.statement();
        Connection con = ctx.getConnection();
        DataSource dataSource = ctx.getDataSource();
        // This note and code below comes from
        // JdbcTemplate.execute(StatementCallback)
        // Release Connection early, to avoid potential connection pool
        // deadlock in the case when the exception translator hasn't
        // been initialized yet.
        JdbcUtils.closeStatement(stmt);
        stmt = null;
        DataSourceUtils.releaseConnection(con, dataSource);
        con = null;
        ctx.exception(getExceptionTranslator(dataSource)
                        .translate("jOOQ", ctx.sql(), ex));
    }

    /**
     * Return the exception translator for this instance.
     *
Creates a default {@link SQLErrorCodeSQLExceptionTranslator}
     * for the specified DataSource if none set, or a
     * {@link SQLStateSQLExceptionTranslator} in case of no DataSource.
     * @see #getDataSource()
     */
    public synchronized SQLExceptionTranslator
    getExceptionTranslator(DataSource dataSource) {
        // This method probably does not need to be synchronized but in
        // Spring it was because of a mutable field on the JdbcTemplate.
        // Also I have no idea how expensive it is to create a translator
        // as one will get created on every exception.
        final SQLExceptionTranslator exceptionTranslator;
        if (dataSource != null) {
            exceptionTranslator =
                new SQLErrorCodeSQLExceptionTranslator(dataSource);
        }
        else {
            exceptionTranslator = new SQLStateSQLExceptionTranslator();
        }
        return exceptionTranslator;
    }
}

See the relevant Stack Overflow answer for more details:
http://stackoverflow.com/a/12326885/521799

The Ollin Framework

I have recently discovered a simple Java database framework called “Ollin“. It can be found when searching jdbc fluent api on Google. It has a couple of transaction handling and ORM features, the way many other frameworks do this. But one specific functionality I found particularly interesting:

ValuedRowVisitor<Integer> rowCounter =
dbSession.createQuery("select * from app.employee")
         .forEachRow(new ValuedRowVisitor<Integer>() {
            private int counter;

            public Integer getValue() {
              return counter;
            }

            public void visit(ResultSetRow row) throws SQLException {
              counter++;
            }
         });
System.out.println("Count of rows: " + rowCounter.getValue());

In this example, you can pass an anonymous type implementing a RowVisitor base interface to a “closure-style” foreach method. Victor Herrera, the creator of Ollin designed this specifically for a use case he had where large amounts of rows (or records) needed to be processed and memory was getting low. This use case is already covered in jOOQ when using the ResultQuery.fetchLazy() method, and then iterate over the resulting Cursor. But the idea of “injecting” a custom handler seemed appealing to me, so with Victor’s permission, I have implemented a similar construct for jOOQ 1.6.4 with #728.

In jOOQ, the magic word is “fetch”, and as discussed recently, there is now the possibility of fetching things into custom types using ResultQuery.fetchInto(Class<?>). So why not also fetch results into custom “targets” like this:

create.selectFrom(T_BOOK)
      .orderBy(TBook.ID)
      .fetchInto(new RecordTarget<TBookRecord>() {
        @Override
        public void next(TBookRecord record) {
          // Do stuff
        }
      });

I’m thrilled to find good open source ideas on the web. I’m always open to new ideas and this Ollin Framework made my day, today :-)

See the framework here: http://code.google.com/p/ollin/