Easy Database Helper for JAVA

EasyDb4J is a simple yet functional database helper which helps dealing with queries and object-relational mappings, with the single interface IConnection. It has features like:

• [statement-free SQL query] (#1-sql-query)
• [entity query with ORMs] (#2-entity-query)
• [OO styled criterion query] (#3-criterion-query)

0. Prelude

---

In the very begining, build a IConnectionFactory of your database to open connections. You may use a shortcut method in ConnectionFactoryBuilder to build a database-specific factory, i.e.:

IConnectionFactory factory = ConnectionFactoryBuilder.buildSQLite("test.db", "user", "pass");

Of course a corresponding JDBC driver should be included in the classpath, otherwise you would get a ClassNotFoundException.

By default, a C3P0 ComboPooledDataSource will be used as the inner connection pool.

Once a connection factory is setted up, you can get a connection by a single call:

IConnection conn = factory.openConnection();

1. SQL query

---

SQL queries, with or without parameters, can be exeuted directly in various statement-free approaches.

executeQuery

Executes the SQL query and returns the ResultSet object generated by the query.

ResultSet executeQuery(String sql, Object[] params = null);
ResultSet executeQuery(String sql, String[] paramNames = null, Object item = null);

Example usage:

class User {
  private int id;
  private String name;
  /* getters and setters are ommitted */
}

/* create table */
conn.executeUpdate("create table user (id int, un varchar(32))");

/* query all users without parameters */
ResultSet rs1 = conn.executeQuery("select * from user");

/* query user with id equals to 3 */
ResultSet rs2 = conn.executeQuery("select * from user where id = ?",
    new Object[] { new Integer(3) });

/* query user by name from a strong-typed object */
ResultSet rs3 = conn.executeQuery("select * from user where un = ?",
    new String[] { "name" }, new User("Jack"));

NOTE: in a strong-typed query, the parameter item is taken as the source of parameters in the query. The arary of paramNames are names of fields in the source item to read. The item could also be a instance of a Map, while paramNames are its keys. The order of paramNames, as well as the params in a none strong-typed query, should be same as the order of their occurrence in the query.

executeScalar

Executes an SQL statement and returns the first column of the first row in the result set generated by the query.

Object executeScalar(String sql, Object[] params = null);
Object executeScalar(String sql, String[] paramNames = null, Object item = null);

Example usage:

Integer count = (Integer) conn.executeScalar("select count(*) from user");

executeUpdate

Executes an SQL statement and returns the number of rows affected.

int executeUpdate(String sql, Object[] params = null);
int executeUpdate(String sql, String[] paramNames = null, Object item = null);

Example usage:

class User {
  private String name;
  private String password;
  /* getters and setters are ommitted */
}

/* create table */
conn.executeUpdate("create table user (un varchar(32), up varchar(32))");

/* params from an array of strings */
conn.executeUpdate("insert into user (un, up) values (?, ?)",
    new Object[] { "user1", "pass1" });

/* params from an object of User */
conn.executeUpdate("insert into user (un, up) values (?, ?)",
    new String[] { "name", "password" },
    new User("user2", "pass2"));

TIPS: you may conveniently and efficiently execute a query multiple times by passing the parameter item as an array or a instance of a Collection like this:

List list = new ArrayList();
list.add(new User("user1", "pass1"));
list.add(new User("user2", "pass2"));

/* params from a list of users */
conn.executeUpdate("insert into user (un, up) values (?, ?)",
    new String[] { "name", "password" },
    list);

query

Executes a query and returns a list of Maps, containing pairs of fields and their values. By default, the map item in the result is a case-insensitive linked hash map.

List query(String sql, Object[] params = null);
List query(String sql, String[] paramNames = null, Object item = null);

Example usage:

List list = conn.query("select * from user");
Map userMap = (Map) list.get(0);

Fall back

Since the IConnection extends the java.sql.Connection interface, you may still use original methods to do whatever you want.

2. Entity query

---

The connection factory has an object-relational mapping　mechanism inside so that it is quite simple to query with strong-typed entities.

CRUD

Basic operations of CRUD.

Example usage:

class User {
  private int id;
  private String name;
  private String password;
  /* getters and setters are ommitted */
}

/* create a table named 'user' */
conn.createTable(User.class);

/* insert a user */
long id = conn.insert(User.class, new User("user1", "pass1"));

/* find a user */
User user1 = conn.find(User.class, new Long(id));

/* and update the user's info */
user1.setPassword("passModified");
conn.update(User.class, user1);

/* finally delete the user */
conn.delete(User.class, user1);

Overloads are provided to query by a entitiy's name instead of its class.

Example usage:

/* register an entity named "user_entity" of the User class */
factory.getMapping()
  .registerTable("user_entity",
    factory.getMapping().findTable(User.class));

/* evertything else goes the same */
conn.createTable("user_entity");
conn.insert("user_entity", new User("user1", "pass1"));
User user1 = conn.find("user_entity", new Long(id));
conn.update("user_entity", user1);
conn.delete("user_entity", user1);

query

Executes a query and returns list of strong-typed objects.

List query(Class clazz, String sql, String[] paramNames = null, Object item = null);
/* or */
List query(String entity, String sql, String[] paramNames = null, Object item = null);

Example usage:

List list1 = conn.query(User.class, "select * from user");
/* or */
List list2 = conn.query("user_entity", "select * from user");

3. Criterion query

---

Queries can be built in an object-oriented way, like Hibernate.

Creating a ICriteria instance

ICriteria represents a query of a specific entity.

ICriteria cri = conn.createCriteria(User.class);

Narrowing the result set

The class Clauses defines factory methods for obtaining built-in criterion types.

List list = conn.createCriteria(User.class)
    .add(Clauses.between("id", new Long(1), new Long(3)))
    .add(Clauses.like("name", "Ja%"))
    .list();

Ordering the results

List list = conn.createCriteria(User.class)
    .add(Clauses.like("name", "Ja%"))
    .addOrder(Order.asc("age"))
    .list();

4. Advanced

---

Custom ORMs

Object-relational mappings help map an object class to a relational table and vice versa. EasyDb4J uses Tables to define ORMs. Besides, two interfaces, ValueBinder and ValueExtractor, tell the library how to process mappings. The former one reads values of fields from an object and sets them to a query Statement, while the latter one extracts values from a ResultSet of a query and then injects them into an object.

Table

A Table is a description of a relational table, consisted of several Columns representing the mapping between a field in an object class and a column in the relational table, and constraints on the table, including PrimaryKey and UniqueKey.

Normally, it is not necessary to initialize a Table manually. Before any [Entity query] (#2-entity-query), the factory will detect the corresponding Table instance, and will automatically create one if none is found, with default naming strategy.

For example, we have a class like:

class EntityClass {
  private int id;
  private String fieldOfString;
  /* getters and setters are ommitted */
}

When we call conn.insert(EntityClass.class, new EntityClass()), a mapped Table will be generated with names in a underscode-seperated, lowercase style as follow:

  class EntityClass  =>  table entity_class
    id                     id
    fieldOfString          field_of_string

If you want a different naming style, you should define custom tables. Example usage:

Table table = new Table();

/* set table name and class */
table.setName("entityClass");
table.setEntityClass(EntityClass.class);

/* add columns */
Column idCol = new Column("id", "id", Types.IDENTITY);
table.addColumn(column);

Column stringCol = new Column("fieldOfString", "fieldOfString", Types.VARCHAR);
table.addColumn(column);

/* add primary key */
PrimaryKey pk = new PrimaryKey();
pk.addColumn(idCol);
table.setPrimaryKey(pk);

/* finally register the table in the connection factory */
factory.getMapping().registerTable(EntityClass.class, table);

ValueBinder & ValueExtractor

// TODO

Dialecting

// TODO