Rémi Forax's Blog

Meta protocol mix in JDBC 4.0

While reading the JDBC 4.0 spec, i found a meta protocol mix in the way the spec specifies how to create an empty DataSet using the newly introduced query interface mecanism. But before let me introduce the context :

JDBC 4.0 introduces a new way to map a class to a result of a JDBC Query automagically. It's not a real O/R Mapper like hibernate or the one describes by the EJB3 spec because by example inheritance or relationship between tables are not managed. It's just a toy O/R Mapper that ease the repetitive task of manually copying data from a ResultSet to object fields.

By example, the class below describes a table of authors and the inner-interface Author.Query describes queries that can be applied on this table.
The queries return a DataSet, it's a new interface that inherits from List (thus is Iterable) and internally stores a ResultSet or a RowSet depending if the query remains or not connected to the database.

public class Author {
  private int id;
  private String firstname;
  private String lastname;
  
  public Author() {
    // used by JDBC mapper
  }

  public int getId() {
    return id;
  }
  
  public String getFirstname() {
    return firstname;
  }
  public void setFirstname(String firstname) {
    this.firstname = firstname;
  }
  
  public String getLastname() {
    return lastname;
  }
  public void setLastname(String lastname) {
    this.lastname = lastname;
  }

  public static final String DDL=
    "create table author(\n" +
    "  id        INTEGER NOT NULL\n"+
    "            PRIMARY KEY GENERATED ALWAYS AS IDENTITY\n"+
    "            (START WITH 1, INCREMENT BY 1),\n" +
    "  firstname VARCHAR(128) NOT NULL UNIQUE,\n"+
    "  lastname  VARCHAR(128) NOT NULL UNIQUE"+
    ")";

  public interface Query extends BaseQuery {
    @Select(sql="SELECT * FROM author")
    DataSet getAllAuthors();
    
    @Select(sql="SELECT * FROM author WHERE id = ?1")
    DataSet getAuthorById(int id);
    
    @Update("DELETE FROM author WHERE id = ?1")
    public int delete(int id);
  }
}

To specify a query, the spec introduces two annotations @Select (SELECT)or @Update (UPDATE, ALTER, DELETE etc) but no annotation for operations like CREATE or DROP.
Now, in order to execute a query, the developer must primarily call a new method of the database connection named createQueryObject to create an object that implements the queries interface.
This method prepares the corresponding statements and create reflective data-structures needed to inject data during the execution of a query.
With the query object, all method defined on the query interface are availables, so executing a query is easy as calling a method.

  Connection con=...
  Author.Query query=con.createQueryObject(Author.Query.class);

  DatatSet authors=query.getAllAuthors();
  for(Author author:authors)
    System.out.println(author.getFirstName()+" "+author.getLastname());

[I've updated the following section, Rémi]

Now, the spec introduces a way to create an empty DataSet automatically without using a query SELECT (section 19.4.2.6).
This is done by defining a special method in the query interface. This method need to be named "create" and takes a table name as parameter and returns a DataSet.
Here the spec defines a meta protocol mix !

What is a meta protocol ?
JUnit uses a meta protocol to defined its tests, a method is a test method if its name starts with "test". It's a way to segregate classical methods from the ones that play a special role.
Using annotations is another way to defined a meta protocol but in the case of the current JDBC 4 spec, the spec mix a meta protocol defined with annotations and a one defined with method name.

What if a method named "create" is tagged by an annotation @Select ? oups, there is a meta protocol mix.
I don't know if is's too late to modify the spec but i think the spec needs some clarification in that area.

Comments

• The Update annotation can be used for any DML or DDL statement which returns an update count so u can invoke create table foo...

  I am not sure i follow your concern about method names associated with annotations, it is up to the developer to decide the name of the method to align with the annotation

  Posted by: lancea on August 02, 2006 at 09:46 AM

• First, Java should have a multi-line string literal for multi-line SQL statements. Adding \n"+ into end of each line is ugly and unnecessarily difficult.

  Second, you should not have UNIQUE after firstname and lastname but instead have UNIQUE(lastname, firstname) ! :-)

  public static final String DDL=
  @"create table author(
  id INTEGER NOT NULL
  PRIMARY KEY GENERATED ALWAYS AS IDENTITY
  (START WITH 1, INCREMENT BY 1),
  firstname VARCHAR(128) NOT NULL,
  lastname VARCHAR(128) NOT NULL,
  UNIQUE(lastname,firstname)
  )"@;
  

  Posted by: fuerte on August 02, 2006 at 10:31 AM

• @lancea,
  i agree with you, the spec should only defined annotations.
  The spec includes a way to create a table without specifying a DDL
  by using the fields of the class to deduce the columns of the table.
  The problem is that instead of using an annotation, the spec
  says that the method shoulb be named "create".

  Rémi Forax
  

  Posted by: forax on August 02, 2006 at 01:50 PM

• There are no methods to just create a table within the EoD functionality. I believe where the point of confusion is that you can add a create() method to a Query interface inorder to create an empty disconnected DataSet. You need to specify the tableName so that when the sync() occurrs it knows which table it is associated with. Underneath the covers a Disconnected DataSet is really a CachedRowset which requires this info in order to persist the data back to the database

  Posted by: lancea on August 03, 2006 at 05:50 AM

• Is there an implementation somewhere?

  Posted by: spullara on August 05, 2006 at 06:01 PM

• @spullara,

  JDBC 4 is already available in the last build of mustang and
  because the JDK now embed derby (renamed to Java BD)
  it's easy to test.

  Rémi

  Posted by: forax on August 21, 2006 at 11:33 AM

This work is licensed under a Creative Commons License.