Rémi Forax's Blog

Create or Drop a table using 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 a table 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 hybernate 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());

Now, the spec introduces a way to create a table using the query interface but

1. There is no annotation like @Create so the query factory just relies on the name of the method "create"
2. There is no way to drop a table with the same mecanism.

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. Futhermore, it will be cool if a table can be dropped using the same mecanism.

Comments

• @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

• 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

• 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

This work is licensed under a Creative Commons License.