How to Execute Something Multiple Times in Java


When writing unit / integration tests, you often want to execute something multiple times, with different configurations / parameters / arguments every time. For instance, if you want to pass a “limit” or “timeout” or any other argument value of 1, 10, and 100, you could do this:

@Test
public void test() {
    runCode(1);
    runCode(10);
    runCode(100);
}

private void runCode(int argument) {

    // Run the actual test
    assertNotNull(new MyObject(argument).execute());
}

Exracting methods is the most obvious approach, but it can quickly get nasty, as these extracted methods are hardly re-usable outside of that single test-case and thus don’t really deserve being put in their own methods. Instead, just use this little trick:

@Test
public void test() {

    // Repeat the contents 3 times, for values 1, 10, 100
    for (int argument : new int[] { 1, 10, 100 }) {

        // Run the actual test
        assertNotNull(new MyObject(argument).execute());
    }

    // Alternatively, use Arrays.asList(), which has a similar effect:
    for (Integer argument : Arrays.asList(1, 10, 100)) {

        // Run the actual test
        assertNotNull(new MyObject(argument).execute());
    }
}

The Golden Rules of Code Documentation


Here’s another topic that is highly subjective, that leads to heated discussions, to religious wars and yet, there’s no objective right or wrong.

A previous post on my blog was reblogged to my blogging partner JavaCodeGeeks. The amount of polarised ranting this blog provoked on JCG is hilarious. Specifically, I like the fact that people tend to claim dogmatic things like:

If you need comments to clarify code, better think how to write code differently, so it is more understandable. You do not need yet another language (comments) to mess with the primary language (code).

Quite obviously, this person has written 1-2 “Hello world” applications, where this obviously holds true. My answer to that was:

How would you write this business logic down into code, such that you can live without comments?

A stock exchange order of clearing type code 27 needs to be grouped with all other subsequent orders of type code 27 (if and only if they have a rounding lot below 0.01), before actually unloading them within a time-frame of at most 35 seconds (fictional example in a real-life application).

Sure. Code can communicate “what” it does. But only comments can communicate “why” it does it! “why” is a broader truth that simply cannot be expressed in code. It involves requirements, feelings, experience, etc. etc.

So it’s time for me to write up another polarising blog post leading to (hopefully!) more heated discussions! It is about:

The Golden Rules of Code Documentation

Good documentation adds readability, transparency, stability, and trustworthiness to your application and/or API. But what is good documentation? What are constituents of good documentation?

Code is documentation

First off, indeed, code is your most significant documentation. Code holds the ultimate truth about your software. All other ways of describing what code does are only approximations for those who

  • Don’t know the code (someone else wrote it)
  • Don’t have time to read the code (it’s too complex)
  • Don’t want to read the code (who wants to read Hibernate or Xerces code to understand what’s going on??)
  • Don’t have access to the code (although they could still decompile it)

For all others, code is documentation. So, obviously, code should be written in a way that documents its purpose. So don’t write clever code, write elegant code. Here’s a good example of how to not document “purpose” (except for the few Perl native speakers):

`$=`;$_=\%!;($_)=/(.)/;$==++$|;($.,$/,$,,$\,$",$;,$^,$#,$~,$*,$:,@%)=(
$!=~/(.)(.).(.)(.)(.)(.)..(.)(.)(.)..(.)......(.)/,$"),$=++;$.++;$.++;
$_++;$_++;($_,$\,$,)=($~.$"."$;$/$%[$?]$_$\$,$:$%[$?]",$"&$~,$#,);$,++
;$,++;$^|=$";`$_$\$,$/$:$;$~$*$%[$?]$.$~$*${#}$%[$?]$;$\$"$^$~$*.>&$=`

Taken from:
http://fwebde.com/programming/write-unreadable-code/

Apparently, this prints “Just another Perl hacker.”. I certainly won’t execute this on my machine, though. Don’t blame me for any loss of data 😉

API is documentation

While API is still code, it is that part of the code that is exposed to most others. It should thus be:

  • Very simple
  • Very concise

Simplicity is king, of course. Conciseness, however, is not exactly the same thing. It can still be simple to use an API which isn’t concise. I’d consider using Spring’s J2eeBasedPreAuthenticatedWebAuthenticationDetailsSource simple. You configure it, you inject it, done. But the name hardly indicates conciseness.

This isn’t just about documentation, but about API design in general. It should be very easy to use your API, because then, your API clearly communicates its intent. And communicating one’s intent is documentation.

Good design (and thus documentation) rules to reach conciseness are these:

  • Don’t let methods with more than 3 arguments leak into your public API.
  • Don’t let methods / types with more than 3 words in their names leak into your public API.

Best avoid the above. If you cannot avoid such methods, keep things private. These methods are not reusable and thus not worth documenting in an API.

API should be documented in words

As soon as code “leaks” into the public API, it should be documented in human-readable words. True, java.util.List.add() is already quite concise. It clearly communicates its intent. But how does it behave and why? An extract from the Javadoc:

Lists that support this operation may place limitations on what elements may be added to this list. In particular, some lists will refuse to add null elements, and others will impose restrictions on the type of elements that may be added. List classes should clearly specify in their documentation any restrictions on what elements may be added.

So, there are some well-known lists, that “refuse to add null elements” there may be “restrictions on what elements may be added”. This can’t be understood from the API’s method signature only – unless you refuse to create a concise signature.

Tracking tools are documentation

Tracking tools are your human interface to your stakeholders. These help you discuss things and provide some historicised argumentation about why code is ultimately written the way it is. Keep things DRY, here. Recognise duplicates and try to keep only one simple and concise ticket per issue.

When modifying your code in a not-so-obvious way (because your stakeholders have not-so-obvious requirements), add a short comment to the relevant code section, referencing the tracking ID:

// [#1296] FOR UPDATE is simulated in some dialects
// using ResultSet.CONCUR_UPDATABLE
if (forUpdate && 
    !asList(CUBRID, SQLSERVER).contains(context.getDialect())) {

Yes, the code itself already explains that the subsequent section is executed only in forUpdate queries and only for the CUBRID and SQLSERVER dialects. But why? A future developer will gladly read up all they can find about issue #1296. If it is relevant, you should reference this ticket ID in:

  • Mailing lists
  • Source code
  • API documentation
  • Version control checkin comments
  • Stack Overflow questions
  • All sorts of other searchable documents
  • etc.

Version control is documentation

This part of the documentation is awesome! It documents change. In large projects, you may still be able to reconstruct why a co-worker who has long ago left the company did some weird change that you don’t understand right now. It is thus important to also include the aforementioned ticket ID in the change.

So, follow this rule: Is the change non-trivial (fixed spelling, fixed indentation, renamed local variable, etc.)? Then create a ticket and document this change with a ticket ID in your commit. Creating and referencing that ticket costs you only 1 minute, but it’ll save a future coworker hours of investigation!

Version numbering is documentation

A simple and concise version numbering system will help your users understand, which version they should upgrade to. A good example of how to do this correctly is semantic versioning. The golden rules here are to use an [X].[Y].[Z] versioning scheme that can be summarised as follows:

  • If a patch release includes bugfixes, performance improvements and API-irrelevant new features, [Z] is incremented by one.
  • If a minor release includes backwards-compatible, API-relevant new features, [Y] is incremented by one and [Z] is reset to zero.
  • If a major release includes backwards-incompatible, API-relevant new features, [X] is incremented by one and [Y], [Z] are reset to zero.

Follow these rules strictly, to communicate the change scope between your released versions.

Where things go wrong

Now here’s where it starts getting emotional…

Forget UML for documentation!

Don’t manually do big UML diagrams. Well, do them. They might help you understand / explain things to others. Create ad-hoc UML diagrams for a meeting, or informal UML diagrams for a high-level tutorial. Generate UML diagrams from relevant parts of your code (or entity diagrams from your database), but don’t consider them as a central part of your code documentation. No one will ever manually update UML diagrams with 100s of classes and 1000s of relations in them.

An exception to this rule may be UML-based model-driven architectures, where the UML is really part of the code, not the documentation.

Forget MS Word or HTML for documentation (if you can)!

Keep your documentation close to the code. It is almost impossible without an extreme amount of discipline, to keep external documentation in-sync with the actual code and/or API. If you can, auto-generate external documentation from the one in your code, to keep things DRY. But if you can avoid it, don’t write up external documentation. It’s hardly ever accurate.

Of course, you can’t always avoid external documentation. Sometimes, you need to write manuals, tutorials, how-tos, best practices, etc. Just beware that those documents are almost impossible to keep in-sync with the “real truth”: Your code.

Forget writing documentation early!

Your API will evolve. Hardly anyone writes APIs that last forever, like the Java APIs. So don’t spend all that time thinking about how to eternally link class A with type B and algorithm C. Write code, document those parts of the code that leak into the API, reference ticket IDs from your code / commits

Forget documenting boilerplate code!

Getters and setters, for instance. They usually don’t do more than getting and setting. If they don’t, don’t document it, because boring documentation gets stale and thus wrong. How many times have you refactored a property (and thus the getter/setter name), but not the Javadoc? Exactly. No one updates boilerplate API documentation.

/**
 * Returns the id
 *
 * @return The id
 */
public int getId() {
    return id;
}

Aaah, the ID! Surprise surprise.

Forget documenting trivial code!

Don’t do this:

// Check if we still have work
if (!jobs.isEmpty()) {

    // Get the next job for execution
    Job job = jobs.pollFirst();

    // ... and execute it
    job.execute();
}

Duh. That code is already simple and concise, as we’ve seen before. It needs no comments at all:

if (!jobs.isEmpty()) {
    Job job = jobs.pollFirst();
    job.execute();
}

TL;DR: Keep things simple and concise

Create good documentation:

  • by keeping documentation simple and concise.
  • by keeping documentation close to the code and close to the API, which are the ultimate truths of your application.
  • by keeping your documentation DRY.
  • by making documentation available to others, through a ticketing system, version control, semantic versioning.
  • by referencing ticket IDs throughout your available media.
  • by forgetting about “external” documentation, as long as you can.

Applications, APIs, libraries that provide you with good documentation will help you create better software, because well-documented applications, APIs, libraries are better software, themselves. Critically check your stack and try to avoid those parts that are not well-documented.

A Typesafety Comparison of SQL Access APIs


SQL is a very expressive and distinct language. It is one of the few declarative languages which are used by a broad audience in everyday work. As a declarative language, SQL allows to specify what we’re expecting as output, not how this output should be produced. As a side-effect of this, ad-hoc record data types are created by every statement. An example:

-- A (id: integer, title: varchar) type is created
SELECT id, title
FROM book;

The above statement generates a cursor whose records have a well-defined record type with these properties:

  • The degree of the record is 2
  • The column names are id and title
  • The column types are integer and varchar
  • The column id can be accessed at index 1. The column title can be accessed at index 2

In other words, SQL records combine features from records (access by name) and tuples (access by index). They can be seen like typesafe associative “map-arrays”, where map keys are formally bound to array indexes and their associated key/index type.

Another, more complex example shows how these ad-hoc record types can be reused within a SQL statement!

-- A (c: bigint) type is created
SELECT count(*) c
FROM book

-- A (..: integer, ..: integer) type is created and compared with...
WHERE (author_id, language_id) IN (

  -- ... another, compatible (..: integer, ..: integer) type
  SELECT a.id, a.language_id
  FROM author a
)

This query counts books written by authors in their native language.

In the above example, the projected record type is a bit simpler. It contains only one column. The interesting part is the row value expression IN comparison predicate, which compares two compatible (integer, integer) types. In SQL, you can typesafely create ad-hoc record types and immediately compare them with other ad-hoc record types. In these comparisons, column names are not important, but column indexes (and associated column types) are.

Comparing various SQL access APIs

The previous examples show how SQL allows for the formal declaration of record types including record degree, column names, column indexes, column types. While SQL is very expressive in that matter, many client languages accessing SQL are less expressive. When comparing expressiveness and typesafety, two features should be taken into consideration:

  1. Are the records produced into the client language typesafe?
  2. Are the SQL statements produced from the client language typesafe and syntax-safe?

Let’s have a look at various accessing techniques, and how expressive they are in terms of the above typesafety requirements:

JDBC: Least typesafety

JDBC offers the least expressiveness and typesafety. This isn’t surprising, as JDBC is a very low-level API. It offers:

  1. No typesafety whatsoever when accessing result records.
  2. No typesafety or syntax-safety whatsoever when producing SQL statements.

Here is an example:

PreparedStatement stmt = null;
ResultSet rs = null;

try {

  // SQL statements are just strings. Constructing them is not
  // typesafe or syntax-safe
  stmt = connection.prepareStatement(
    "SELECT id, title FROM book WHERE id = ?");

  // Bind values are set by index. There is no typesafety or
  // "index safety"
  stmt.setInt(1, 15);

  rs = stmt.executeQuery();
  while (rs.next()) {

    // There is no typesafety or "index safety" when accessing
    // result record values
    System.out.println(
      "ID: " + rs.getInt(1) + ", TITLE: " + rs.getString(2));
  }
}
finally {
  closeSafely(stmt, rs);
}

Now, this wasn’t surprising. JDBC makes up for the lack of typesafety by being absolutely general. It is possible to implement a JDBC driver for any type of relational database, no matter what kinds of SQL and JDBC features they really support.

JPA: Some typesafety

JPA has implemented quite a bit of typesafety mostly on top of JPQL, but also slightly on top of SQL. With JPA, you can have:

  1. Some typesafety when accessing records.
  2. Some typesafety and syntax-safety when producing JPQL statements through the CriteriaQuery API (not SQL statements).

Record access typesafety can be guaranteed when you project the outcome of your statements onto your JPA-annotated entities. While the mapping itself isn’t really typesafe, the outcome is, as a Java class is the closest match to a SQL record. A Java class, much like a SQL record, has:

  • A degree, expressed in the number of properties
  • Column names, expressed as property names
  • Column types, expressed as property types
  • But: No column indexes. Properties have no explicit order

JPA record mapping has additional features that exceed the expressiveness of SQL, as “flat”, tabular result sets can be mapped onto object hierarchies. In any case, you will have to create one record / entity type per query to profit from this typesafety. If you’re not projecting all columns from every table, but ad-hoc records (including values derived from functions), you will lose this typesafety again.

When it comes to statement typesafety, JPA offers the CriteriaQuery API to produce typesafe JPQL statements. The CriteriaQuery API is often criticised for its verboseness and for the fact that resulting client code is hard to read. Here is an example taken from the CriteriaQuery API docs:

CriteriaQuery<String> q = cb.createQuery(String.class);
Root<Order> order = q.from(Order.class);
q.select(order.get("shippingAddress").<String>get("state"));
 
CriteriaQuery<Product> q2 = cb.createQuery(Product.class);
q2.select(q2.from(Order.class)
            .join("items")
            .<Item,Product>join("product"));

It can be seen that there is only a limited amount of typesafety in the above query construction:

  • Columns are accessed by string literals, such as "shippingAddress".
  • Generic entity types are not really checked. The <Item,Product> generic parameters might as well be wrong.

Of course, there are more typesafe API parts in JPA’s CriteriaQuery API. Using those API parts quickly lead to the aforementioned verbosity, though, as can be seen in this Stack Overflow question, or in the Java EE 6 Tutorials.

LINQ: Much typesafety (in .NET)

LINQ goes very far in offering typesafety in both dimensions:

  1. Much typesafety when accessing records or tuples.
  2. Much typesafety when producing LINQ-to-SQL statements (not SQL statements).

As LINQ is formally integrated into various .NET languages, it has the advantage of being able to produce formally defined record types, directly into the target language (e.g. C#). Not only can typesafe records be produced, the LINQ-to-SQL statement is formally verified by the compiler as well. An example

// Typesafe renaming (aliasing with "AS" in SQL)
From p In db.Products
// Typesafe (named!) variable binding
Where p.UnitsInStock <= ReorderLevel AndAlso Not p.Discontinued
// The typesafe projection will produce a Products record
Select p

Another example from Stack Overflow can be seen here:

// Producing a C# tuple
var r = from u in db.Users
        join s in db.Staffs on u.Id equals s.UserId
        select new Tuple<User, Staff>(u, s);

// Producing an anonymous record type
var r = from u in db.Users
    select new { u.Name, 
                 u.Address,
                 ...,
                 (from s in db.Staffs 
                  select s.Password where u.Id == s.UserId) 
               };

LINQ has many obvious advantages when it comes to typesafety. In the case of LINQ, this comes at the price of losing actual SQL expressivity and syntax, as LINQ-to-SQL is not really SQL (just as JPQL is not really SQL either). The SQL querying API is partially shared with other, heterogeneous querying targets, such as LINQ-to-Entities, LINQ-to-Collections, LINQ-to-XML. This will reduce LINQ’s feature scope (see also a previous blog post, and I will soon blog about this again).

But C# offers all typesafety aspects that a SQL record offers as well: degree, column name (anonymous types), column index (tuples), column types (both types and tuples).

SLICK: Much typesafety (in Scala)

SLICK has been inspired by LINQ, and can thus offer a lot of typesafety as well. It offers:

  1. Much typesafety when accessing tuples (not records).
  2. Much typesafety when producing SLICK statements (not SQL statements).

SLICK takes advantage of Scala’s integrated tuple expressions. This is best shown by example:

// "for" is the "entry-point" to the DSL
val q = for {

    // FROM clause   WHERE clause
    c <- Coffees     if c.supID === 101

// SELECT clause and projection to a tuple
} yield (c.name, c.price)

The above example shows that the projection onto a (String, Int) tuple is done typesafely by the yield method. At the same time, the whole query expression is formally validated by the compiler, as SLICK makes heavy use of Scala’s language features in order to introduce an internal DSL for querying. Much more than LINQ, SLICK has a unique syntax that doesn’t remind of SQL any more. It is not obvious how subqueries, complex joins, grouping and aggregation can be expressed.

jOOQ: Much typesafety

jOOQ is mainly inspired by SQL itself and embraces all the features that SQL offers. It has thus:

  1. Much typesafety when accessing records or tuples.
  2. Much typesafety when producing SQL statements.

jOOQ offers similar capabilities as JPA when it comes to mapping SQL result sets onto records, although JPA’s mapping type hierarchies are not supported by jOOQ. But jOOQ also allows for typesafe tuple access, the way SLICK has implemented it. Ad-hoc records produced by arbitrary query projections will maintain their various column types through generic Record1<T1>, Record2<T1, T2>, Record3<T1, T2, T3>, … record types. Unlike in Java, this can be leveraged extensively in Scala, where these typesafe Record[N] types can be used just like Scala’s tuples.

On the other hand, just like LINQ-to-SQL, which has formally integrated querying as a first-class citizen into .NET languages, jOOQ allows for heavy type-checking and syntax-checking, when writing SQL statements in Java.

In SQL, you can typesafely write things like:

SELECT * FROM t WHERE (t.a, t.b) = (1, 2)
SELECT * FROM t WHERE (t.a, t.b) OVERLAPS (date1, date2)
SELECT * FROM t WHERE (t.a, t.b) IN (SELECT x, y FROM t2)
UPDATE t SET (a, b) = (SELECT x, y FROM t2 WHERE ...)
INSERT INTO t (a, b) VALUES (1, 2)

In jOOQ 3.0, you can (also typesafely!) write

select().from(t).where(row(t.a, t.b).eq(1, 2));
// Type-check here: ----------------->  ^^^^
 
select().from(t).where(row(t.a, t.b).overlaps(date1, date2));
// Type-check here: ------------------------> ^^^^^^^^^^^^
 
select().from(t).where(row(t.a, t.b).in(select(t2.x, t2.y).from(t2)));
// Type-check here: -------------------------> ^^^^^^^^^^
 
update(t).set(row(t.a, t.b), select(t2.x, t2.y).where(...));
// Type-check here: --------------> ^^^^^^^^^^

insertInto(t, t.a, t.b).values(1, 2);
// Type-check here: ---------> ^^^^

This also applies for existing API, which doesn’t involve row value expressions:

select().from(t).where(t.a.eq(select(t2.x).from(t2));
// Type-check here: ---------------> ^^^^
 
select().from(t).where(t.a.eq(any(select(t2.x).from(t2)));
// Type-check here: -------------------> ^^^^
 
select().from(t).where(t.a.in(select(t2.x).from(t2));
// Type-check here: ---------------> ^^^^

select(t1.a, t1.b).from(t1).union(select(t2.a, t2.b).from(t2));
// Type-check here: -------------------> ^^^^^^^^^^

jOOQ is not SQL, but unlike other attempts of introducing SQL as an internal domain-specific language into host languages like Java, Scala, C#, jOOQ looks very much like SQL thanks to its unique fluent API technique, which informally follows an underlying BNF notation.

Even if Java offers less expressiveness than other languages like C# or Scala, jOOQ probably comes closest to both result record typesafety and SQL syntax safety in the Java world.

Duck Typing in Scala: Structural Typing


Scala goes very far with its loads of features improving the JVM world. Sometimes, a bit too far, maybe. But this feature here is just plain awesome! Typesafe duck typing! An example:

def quacker(duck: {def quack(value: String): String}) {
  println (duck.quack("Quack"))
}

The above quacker method accepts any “duck” that can quack.

Read the full article here:
http://java.dzone.com/articles/duck-typing-scala-structural

Easy Mocking of Your Database


Test-driven development is something wonderful! Once you’ve established it in your organisation, you will start to:

  • Greatly improve your quality (things break less often)
  • Greatly improve your processes (things can be changed more easily)
  • Greatly improve your developer atmosphere (things are more fun to do)

The importance of doing the right test-driven development is to find a good ratio of what kind of code is to be covered…

  • by automated unit tests
  • by automated integration tests
  • by manual “smoke tests”
  • by manual “acceptance tests”
  • not at all

Finding that ratio can be grounds for heated, religious discussions. I will soon blog about my own opinion on that subject. In this post, however, we will focus on the first kind of test: unit tests.

Unit testing your data access

When databases are involved, people will probably quickly jump to writing integration tests, because all they have to do is create a little Derby, H2 or HSQLDB (or other) test database, and run a couple of data-setup queries prior to the actual test. Their code module will then hopefully not notice the difference to a productive environment, and the whole system can be tested as a blackbox. The advantage of this is that your tests can be written in a way to verify your business requirements, your user stories, or whatever you call them. So far, the theory.

When these database integration tests pile up, it starts to become increasingly difficult to shield them off one another. Avoiding inter-dependencies and at the same time, avoiding costly database setups is hard. You won’t be able to run the whole test-suite immediately after building / committing. You need nightly builds, weekly builds. But unit testing the data access layer isn’t that much easier! Because JDBC is an awful API to mock. There are so many different ways of configuring and executing queries through this highly stateful API, your unit tests quickly become unmanageable.

There are a few libraries that help you with database testing. Just to name a few:

  • MockRunner: This one has some JDBC-specific extensions that allow for simulating JDBC ResultSets, as well as for checking whether actual queries are executed
  • jMock: An “ordinary” Java mocking library
  • mockito: An “ordinary” Java mocking library
  • DBUnit: This one doesn’t mock your database, it’s good for testing your database. Another use-case, but still worth mentioning here

Some of the above libraries will not get you around the fact that JDBC is an awkward API to mock, specifically if you need to support several (incompatible!) versions of JDBC at the same time. Some examples can be seen here:

Mocking the database with jOOQ

When you’re using jOOQ in your application, mocking your database just became really easy in jOOQ 3.0. jOOQ now also ships with a Mock JDBC Connection. Unlike with other frameworks, however, you only have to implement a single functional interface with jOOQ, and provide that implementation to your MockConnection: The MockDataProvider. Here’s a simple implementation example:

MockDataProvider provider = new MockDataProvider() {

    // Your contract is to return execution results, given a context
    // object, which contains SQL statement(s), bind values, and some
    // other context values
    @Override
    public MockResult[] execute(MockExecuteContext context) 
    throws SQLException {

        // Use ordinary jOOQ API to create an org.jooq.Result object.
        // You can also use ordinary jOOQ API to load CSV files or
        // other formats, here!
        DSLContext create = DSL.using(configuration);
        Result<MyTableRecord> result = create.newResult(MY_TABLE);
        result.add(create.newRecord(MY_TABLE));

        // Now, return 1-many results, depending on whether this is
        // a batch/multi-result context
        return new MockResult[] {
            new MockResult(1, result)
        };
    }
};

// Put your provider into a MockConnection and use that connection
// in your application. In this case, with a jOOQ DSLContext:
Connection connection = new MockConnection(provider);
DSLContext create = DSL.using(connection, dialect);

// Done! just use regular jOOQ API. It will return the values
// that you've specified in your MockDataProvider
assertEquals(1, create.selectOne().fetch().size());

The above implementation acts as a callback for JDBC’s various executeXXX() methods. Through a very simple MockExecuteContext API, you can thus:

  • Get access to the executed SQL and bind values (Use general jOOQ API to inline bind values into the SQL statement)
  • Distinguish between regular SQL statements and both single-statement/multi-bind-value and multi-statement/no-bind-value batch executions
  • Return one or several results using jOOQ’s org.jooq.Result objects (which you can easily import from CSV, XML, JSON, TEXT formats)
  • Return “generated keys” results through the same API
  • Let jOOQ’s MockStatement take care of the serialisation of your mock data through the JDBC API

There is also an experimental implementation of a MockFileDatabase, a text-based mock database that uses the following format:

# This is a sample test database for MockFileDatabase
# Its syntax is inspired from H2's test script files

# When this query is executed...
select 'A' from dual;
# ... then, return the following result
> A
> -
> A
@ rows: 1

# Just list all possible query / result combinations
select 'A', 'B' from dual;
> A B
> - -
> A B
@ rows: 1

select "TABLE1"."ID1", "TABLE1"."NAME1" from "TABLE1";
> ID1 NAME1
> --- -----
> 1   X
> 2   Y
@ rows: 2

MockFileDatabase implements MockDataProvider, so it’s dead-simple to provide your unit tests with sample data. Future versions of jOOQ will allow for:

  • Regex pattern-matching SQL statements to provide mock results
  • Load these results from other formats, such as jOOQ’s supported export formats
  • Specify the behaviour of batch statements, multi-result statements, etc.

Using jOOQ’s MockConnection in other contexts

Things don’t stop here. As jOOQ’s MockConnection is the entry point for this mocking sub-API of jOOQ, you can also use it in other environments, such as when running JPA queries, Hibernate queries, iBatis or just your plain old legacy JDBC queries.

jOOQ has just become your preferred JDBC mock framework! 😉

jOOQ’s Reason for Being


The below paragraphs were taken from the jOOQ preface from the manual. It is worth thinking about why you should (or should not) use jOOQ in a given project. Specifically, you might be choosing between jOOQ and JPA, jOOQ and Hibernate, or jOOQ and SLICK (in a Scala context). here’s some guidance (slightly biased towards jOOQ, of course…):

jOOQ’s reason for being – compared to JPA

Java and SQL have come a long way. SQL is an “ancient”, yet established and well-understood technology. Java is a legacy too, although its platform JVM allows for many new and contemporary languages built on top of it. Yet, after all these years, libraries dealing with the interface between SQL and Java have come and gone, leaving JPA to be a standard that is accepted only with doubts, short of any surviving options.

So far, there had been only few database abstraction frameworks or libraries, that truly respected SQL as a first class citizen among languages. Most frameworks, including the industry standards JPA, EJB, Hibernate, JDO, Criteria Query, and many others try to hide SQL itself, minimising its scope to things called JPQL, HQL, JDOQL and various other inferior query languages

jOOQ has come to fill this gap.

jOOQ’s reason for being – compared to LINQ

Other platforms incorporate ideas such as LINQ (with LINQ-to-SQL), or Scala’s SLICK to better integrate querying as a concept into their respective language. By querying, they understand querying of arbitrary targets, such as SQL, XML, Collections and other heterogeneous data stores. jOOQ claims that this is going the wrong way too.

In more advanced querying use-cases (more than simple CRUD and the occasional JOIN), people will want to profit from the expressivity of SQL. Due to the relational nature of SQL, this is quite different from what object-oriented and partially functional languages such as C#, Scala, or Java can offer.

It is very hard to formally express and validate joins and the ad-hoc table expression types they create. It gets even harder when you want support for more advanced table expressions, such as pivot tables, unnested cursors, or just arbitrary projections from derived tables. With a very strong object-oriented typing model, these features will probably stay out of scope.

In essence, the decision of creating an API that looks like SQL or one that looks like C#, Scala, Java is a definite decision in favour of one or the other platform. While it will be easier to evolve SLICK in similar ways as LINQ, SQL feature scope that clearly communicates its underlying intent will be very hard to add, later on (e.g. how would you model Oracle’s partitioned outer join syntax? How would you model ANSI/ISO SQL:1999 grouping sets? How can you support scalar subquery caching? etc…).

jOOQ has come to fill this gap.

jOOQ is different

SQL was never meant to be abstracted. To be confined in the narrow boundaries of heavy mappers, hiding the beauty and simplicity of relational data. SQL was never meant to be object-oriented. SQL was never meant to be anything other than… SQL!

Get hidden feature requests from your users


In general, I’m not a marketing guy, I prefer to develop code. But when I look at modern marketing tools that we developers have created for our marketing friends, I’m getting a bit jealous. Take this blog, for instance. It’s the perfect jOOQ marketing tool. Check out my 2013 visitor statistics:

jOOQ blog 2013 statistics

jOOQ blog 2013 statistics

Quite obviously, my February posts were given a lot more attention than my January posts even if I’m taking into account the average visitor that visits old posts, generating “background noise”. The reason is simple:

  • In February, I blogged mostly about controversial Java topics. Before, the topics were more technical and SQL-related (and thus objective and boring). Java blogs get more traction than SQL blogs.
  • My two blogging partners DZone and JCG tend to reblog Java posts more than SQL posts

Aha. So I should write more Java blog posts in order to get even more traffic. But I don’t just want traffic, I want “relevant” traffic.

How to generate “relevant” traffic

“Relevant” traffic for jOOQ is traffic that will generate “conversions”, e.g. people that may not know jOOQ before hitting this blog, checking out jOOQ and downloading it, because they read my articles. That’s an obvious case for “relevant” traffic.

But I also want to get people on my blog / website, because they are already (or not yet) using jOOQ and because they’re looking for something specific, like a jOOQ feature that they’re missing. Only few people will actually take the time to write up a nice, understandable e-mail with a well-explained use-case to issue a feature request on the jOOQ user group. They’re more likely to give up after a couple of google searches. Hence, it is important to take notice of those searches and to be sure that they will end up on my blog / website, not on some arbitrary google search result or on Stack Overflow (which is good, but which I cannot analyse). Here are some interesting search strings that have lead to “hidden” feature requests or blog posts in the past:

Search strings leading to blog posts

These search strings indicated that there are grounds for generating more relevant traffic on the blog:

  • jooq vs linq: jOOQ is a good answer to Java’s missing LINQ (Language INtegrated Query).
  • jooq vs hibernate: jOOQ is a viable alternative to Hibernate. This is a good opportunity to read about the pros / cons of each approach.
  • scala slick: SLICK (Scala Language-Integrated Connection Kit is another “LINQ-esque” API.
  • jooq vs mybatis: MyBatis is a different approach at abstracting the data layer in Java.
  • jooq vs jpa: See jOOQ vs Hibernate
  • java fluent sql: All users that are looking for fluent APIs should come here. There is probably no better fluent implementation of SQL in Java, other than jOOQ.
  • scala sql: Yes, Scala users should use jOOQ to write SQL statements.
  • where are there good java sql open source projects: Here! 😉
  • jooq migration: Some people may expect jOOQ to add a database migration module. There are good tools for that already, e.g.: Liquibase or Flyway.
  • jooq caching: Some day, I will get this bad idea out of people’s head. The data access layer shouldn’t implement caching!
  • jooq google app engine: Yes, that should work, too. Although I don’t have a running instance anymore.
  • bad software: I don’t know how they got here 🙂
  • jooq clojure: I wonder what that would look like. Anything like SQLKorma?
  • jooq alternative: Why?
  • jooq enterprise sql: You can donate here, to feel a bit more enterprisey!
  • how to create a java internal dsl from a bnf: I should sell this idea, or create a non-free tool from it!
  • martin fowler jooq: Yes, I’ve asked him too. Neither he himself, nor his company ThoughtWorks responded to my inquiries so far. I should really get on their tech radar…

The above examples show that I should probably write a post that shows the main disadvantages of LINQ (heavily reduced expressivity) compared to using SQL directly (very expressive, feature-rich language). In a way, LINQ and JPQL both attempt to cripple / standardise SQL by removing most of SQL’s features. Unsurprisingly, one of the most popular articles on this blog is this one here:

https://blog.jooq.org/2012/07/30/when-will-we-have-linq-in-java

Search strings leading to “hidden” feature requests

These search strings indicated that users may be interested in a particular feature:

  • jooq ddl: It’s about time that jOOQ also supports DDL statements.
  • jooq cte: Common Table Expressions (CTE’s) are an important missing feature today, in jOOQ. jOOQ’s current fluent API technique doesn’t fully take care of taking advantage of CTE’s expressivity.
  • postgres insert returning: Postgres’ INSERT/UPDATE .. RETURNING syntax is one of the nice features in jOOQ.
  • jooq transaction: jOOQ currently doesn’t handle transactions. jOOQ can run, but jOOQ can’t hide from this feature (delegating it to other APIs).
  • relational division: Beautiful feature. Implemented in jOOQ!
  • bnf sql: Yes, jOOQ’s API has an underlying (informal) BNF notation. Time to formalise it!
  • jooq cursor: jOOQ can read cursors from stored procedures, which is a pain to do with JDBC.
  • derby trunc: jOOQ simulates functions unavailable in some dialects.
  • jooq logging: jOOQ allows for easy logging of executed SQL statements.
  • jooq meta: jOOQ has an API to query database meta data, which is more intuitive than JDBC’s
  • java multiple cursors: Yes, fetching several result sets is much easier with jOOQ than with JDBC.
  • hibernate olap: No, unfortunately, Hibernate can only handle simple OLTP. For OLAP, you’ve come to the right place.
  • simulate skip locked h2: Beautiful! Challenge accepted.
  • connect by: Oracle and CUBRID support this nice and concise way of expressing recursive SQL.
  • group by rollup: A nice OLAP feature.
  • load mysql script java .sql: jOOQ’s Loader API gets you started quickly, when batch loading data into your database.
  • derby bitand: Ah, yes. Bitwise operations are poorly supported in most SQL dialects. jOOQ simulates them for all databases.
  • jooq blob: Most JDBC drivers don’t really care about the distinction between Blob / byte[], or Clob / String. But things might get nasty for very large Blobs
  • production schema: Yes, jOOQ’s distinction between development schema and production schema has proven very useful to many users.
  • jooq batch insert: Batch queries. A pain to do with JDBC. So simple, with jOOQ
  • jooq customtable: I get a feeling that this “dynamic SQL” thing is still not 100% well implemented in jOOQ.
  • jooq informix support: Yes, one day!
  • for update sql server: jOOQ can simulate pessimistic locking through the JDBC API!

The above examples show that a library that is “true to SQL” in a way that it does not try to hide SQL’s syntax complexity hits the spot with many users.

Conclusion

These search strings help adding even more value to jOOQ. They are the “hidden feature requests” of jOOQ’s users. Tool vendors, use this knowledge and blog about your experience, products, thoughts, ideas. Give away free information that is interesting to a broad audience. It will pay back when you analyse your incoming traffic and the google search strings that people used to find your relevant blog (granted that your blog is somewhat relevant)!