The RIFE framework has functionality which abstracts the Sql Create Table statement into Java classes. The fundamental reason to use an abstraction instead of writing the Sql directly is that the underlying framework generates Sql specific to the targeted database. Why is this important? Frequently I use an open-source database like Hypersonic for development but a commercial product like Oracle for production.

The following code demonstrates the technique:

  String driverClassname = "org.hsqldb.jdbcDriver";
  String url = "jdbc:hsqldb:hsql://localhost:9101/test";
  String username = "sa";
  String password = "";
  String poolSize = 5;

  Datasource ds = new Datasource(driverClassname, url, username, password, poolSize);

  CreateTable create = new CreateTable(ds);

  create.table("beer")
    .columns(Beer.class)
    .primaryKey("id")
    .precision("brand", 50)
    .nullable("brand", CreateTable.NOTNULL);

  String createSql = create.getSql();

When executed, the generated SQL looks like this:

CREATE TABLE beer (brand VARCHAR(50) NOT NULL, id INTEGER NOT NULL, price NUMERIC, PRIMARY KEY (id))

For completeness, here are the relevant parts of Beer.java

public class Beer {

    private String brand = null;

    private BigDecimal price = null;

    private int id = 0;

    // ... snipped out getters and setters.
}