Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Zero-based vs one-based APIs
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Any API that bridges two languages / mind sets, such as Java / SQL will inevitably face the difficulty of finding a consistent strategy to solving the "based-ness" problem. Should arrays be one-based or zero-based?
Clearly, Java is zero-based and SQL is one-based, and the best strategy for jOOQ is to keep things this way. The following are a set of rules that you should remember if this ever confuses you:
All SQL API is one-based
When using SQL API, such as the index-based ORDER BY clause, or window functions such as in the example below, jOOQ will not interpret indexes but send them directly as-is to the SQL engine. For instance:
SELECT nth_value(title, 3) OVER (ORDER BY id) FROM book ORDER BY 1
create.select(nthValue(BOOK.TITLE, 3).over(orderBy(BOOK.ID)))
.from(BOOK)
.orderBy(1).fetch();
In the above example, we're looking for the 3rd value of X in T ordered by Y. Clearly, this window function uses one-based indexing. The same is true for the ORDER BY clause, which orders the result by the 1st column - again one-based counting. There is no column zero in SQL.
All jOOQ API is zero-based
jOOQ is a Java API and as such, one-basedness would be quite surprising despite the fact that JDBC is one-based (see below). For instance, when you access a record by index in a jOOQ org.jooq.Result, given that the result extends java.util.List, you will use zero-based index access:
Result<?> result = create.select(BOOK.ID, BOOK.TITLE)
.from(BOOK)
.orderBy(1)
.fetch();
for (int i = 0; i < result.size(); i++)
System.out.println(result.get(i));
Unlike in JDBC, where java.sql.ResultSet#absolute(int) positions the underlying cursor at the one-based index, we Java developers really don't like that way of thinking. As can be seen in the above loop, we iterate over this result as we do over any other Java collection.
All JDBC API is one-based
An exception to the above rule is, obviously, all jOOQ API that is JDBC-interfacing. E.g. when you implement a custom data type binding, you will work with JDBC API directly from within jOOQ, which is one-based.
Feedback
Do you have any feedback about this page? We'd love to hear it!