KISS-DBC
Simplifying database operations in Java This article shows how to apply 80/20 principle to your development practices. It covers simplification of database operations in Java code.
I will refer to classes from biz.hammurapi.sql and subpackages as a Hammurapi Group Data Access library. The central class of the library is SQLProcessor. All the classes are part of the Common library, which can be downloaded here
Several examples
This section shows an example of how much typing you can save. It contains two code snippets ? one for traditional JDBC programming, and the other using classes from biz.hammurapi.sql package. Both the examples connect to a database, create a table, populate it and the retrieve data.
This is a sample code using JDBC API
private static void doDull() throws ClassNotFoundException, SQLException {
Class.forName("org.hsqldb.jdbcDriver");
Connection con=DriverManager.getConnection("jdbc:hsqldb:.", "sa", "");
try {
Statement st=con.createStatement();
try {
//st.executeUpdate("create table people (first_name varchar(200), last_name varchar(200))");
} finally {
st.close();
}
PreparedStatement ps=con.prepareStatement("insert into people (first_name, last_name) values (?, ?)");
try {
for (int i=0; i<20; i++) {
ps.setString(1,"Pavel-" + i);
ps.setString(2, "Vlasov");
ps.execute();
}
} finally {
ps.close();
}
Statement st1=con.createStatement();
try {
st1.executeUpdate("insert into people (first_name, last_name) values ('Olga', 'Vlasov')");
} finally {
st1.close();
}
Statement st2=con.createStatement();
try {
ResultSet rs=st2.executeQuery("select * from people");
try {
while (rs.next()) {
System.out.println(rs.getString("FIRST_NAME")+" "+rs.getString("LAST_NAME"));
}
} finally {
rs.close();
}
} finally {
st2.close();
}
} finally {
con.close();
}
}
This is a sample code using classes from
biz.hammurapi.sql package
private static void doCool() throws ClassNotFoundException, IOException, SQLException {
HypersonicInMemoryDataSource ds=new HypersonicInMemoryDataSource(null);
try {
SQLProcessor processor=new SQLProcessor(ds, null);
processor.processUpdate("create table people (first_name varchar(200), last_name varchar(200))", null);
final int[] counter={0};
for (int i=0; i<20; i++) {
processor.processUpdate("insert into people (first_name, last_name) values (?, ?)",
new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setString(1,"Pavel "+ ++counter[0]);
ps.setString(2, "Vlasov");
}
});
}
processor.processUpdate("insert into people (first_name, last_name) values ('Pavel', 'Vlasov')", null);
processor.processSelect("select * from people", null,
new RowProcessor() {
public boolean process(ResultSet rs) throws SQLException {
System.out.println(rs.getString("FIRST_NAME")+" "+rs.getString("LAST_NAME"));
return true;
}
});
} finally {
ds.shutdown();
}
}
The first thing to notice is 33% reduction in size - from 45 lines to 30 lines. But the most important benefit is that you don't need to put all these try-finally blocks which makes your code more robust especially in case of nested selects. There are other advantages, which you will be described in the next sections.
Connecting to DB
Establising a connection to the database is the first thing you need to do. Typical code looks like:
Class.forName("org.hsqldb.jdbcDriver");
Connection con=DriverManager.getConnection("jdbc:hsqldb:.", "sa","");
What is bad with this code is that you need to remember driver class name and connection url name. It is also a good idea to use javax.sql.DataSource instead of connection because in this case you don't need to trace whether your connection is open or closed - you just follow get-use-close pattern in all the methods which work with database:
Connection con=dataSource.getConnection();
try {
... use connection ...
} finally {
con.close();
}
Application servers offer datasources out of the box. In a standalone application you can use datasources offered by
Hammurapi Group Data Access:
- biz.hammurapi.sql.ConnectionPerThreadDataSource - This datasource creates one connection per thread. All calls from the same thread share a connection. Connection is closed either on timeout or when datasource is shutdown. Datasource is shutdown either explicitly by calling shutdown() method or in finalize().
There are several subclasses of ConnectionPerThreadDataSource, which simplify working with Hypersonic database. They all reside in
biz.hammurapi.sql.hypersonic package:
- HypersonicInMemoryDataSource - Creates in-memory database. Can be initialized on creation if you provide script name to constructor.
- HypersonicServerDataSource - Connects to Hypersonic server.
- HypersonicStandaloneDataSource - Opens standalone Hypersonic database. There is an option to transparently initialize database from a script on creation of a database.
- HypersonicTmpDataSource - Same as standalone, but creates files in tmp folder. Hammurapi uses this datasource by default, so users don't even know that there is a database involved..
This is a sample of using a standalone datasource to store information about people:
private static final String CREATE_TABLE_SQL=
"CREATE CACHED TABLE PERSON " +
"(ID INTEGER IDENTITY NOT NULL PRIMARY KEY, " +
"FIRST_NAME VARCHAR(30), LAST_NAME VARCHAR(30))";
interface Person {
String getFirstName();
String getLastName();
}
private static void sample1() throws Exception {
DataSource dataSource=new HypersonicStandaloneDataSource("People") {
protected void initDB() throws SQLException {
new SQLProcessor(this, null).processUpdate(CREATE_TABLE_SQL, null);
}
};
SQLProcessor processor=new SQLProcessor(dataSource, null);
processor.processUpdate("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) VALUES ('Pavel', 'Vlasov')", null);
Iterator it= processor.project("SELECT * FROM PERSON", null, Person.class).iterator();
while (it.hasNext()) {
System.out.println(it.next());
}
}
This code created files People.properties, People.data and other in the current directory if they do not already exist.
This is the output of second run:
biz.hammurapi.sql.Samples$Person [ FirstName = Pavel; Id = 0; LastName = Vlasov ]
biz.hammurapi.sql.Samples$Person [ FirstName = Pavel; Id = 1; LastName = Vlasov ]
Processing selects
This section will show how to process selects in situations when you don't need to store select results in objects, but just process them.
Projection section will describe how to instantiate classes and interfaces from result set values.
biz.hammurapi.sql.SQLProcessor class has the following methods to perform selects:
- processSelect(java.lang.String sql, Parameterizer parameterizer, RowProcessor rowProcessor)
Executes sql string parameterized by parameterizer, if any. Each row is passed to rowProcessor
- processResourceSelect(java.lang.String resourceName, Parameterizer parameterizer, RowProcessor rowProcessor)
Same as above but SQL is read from resourceName. I have a friend who writes 100-200 lines SQL statements. This method is for him.
Interfaces involved in select processing:
Let's take a look at examples:
1. Unparameterized request
processor.processSelect("SELECT * FROM PERSON", null, new RowProcessor() {
public boolean process(ResultSet rs) throws SQLException {
System.out.println(rs.getInt("ID")+" "+rs.getString("LAST_NAME"));
return true;
}
});
This is the simplest form of select processing. This sample uses anonymous implementation of RowProcessor, but you can, for example, reuse one processor in multiple selects. One more thing to note is that if RowProcessor.process() returns false then the rest of the rows will not be processed.
2. Parameterized request
processor.processSelect(
"SELECT * FROM PERSON WHERE LAST_NAME=?",
new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setString(1, "VLASOV");
}
},
new RowProcessor() {
public boolean process(ResultSet rs) throws SQLException {
System.out.println(rs.getInt("ID")+" "+rs.getString("LAST_NAME"));
return true;
}
});
As in the first example, you don't have to use anonymous implementations, but have RowProcessor and Parameterized defined somwhere else and reuse them in several places.
3. Returning values from process() method
There is a small trick in returning values from process() method. Anonymous classes have access only to final variables, so to return something you will need to declare a final variable of type of some mutable object (array in this case) as shown in the following example:
private static String sample3(SQLProcessor processor) throws SQLException {
final String[] ret={null};
processor.processSelect(
"SELECT * FROM PERSON WHERE LAST_NAME=?",
new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setString(1, "VLASOV");
}
},
new RowProcessor() {
public boolean process(ResultSet rs) throws SQLException {
ret[0]=rs.getString("LAST_NAME");
return false;
}
});
return ret[0];
}
4. Insert/Update dualism
It is a commons case when you need to update some row if it exists and insert if it is not. The following sample shows how to achieve it with RowProcessorEx:
private static void updateOrInsert(
final SQLProcessor processor,
final String firstName,
final String lastName)
throws SQLException {
final Parameterizer parameterizer=new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setString(1, firstName);
ps.setString(2, lastName);
}
};
processor.processSelect(
"SELECT * FROM PERSON WHERE LAST_NAME=?",
new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setString(1, lastName);
}
},
new RowProcessorEx() {
public boolean process(ResultSet rs) throws SQLException {
processor.processUpdate(
"UPDATE PERSON SET FIRST_NAME=? WHERE LAST_NAME=?",
parameterizer);
return false;
}
public void onEmptyResultSet() throws SQLException {
processor.processUpdate(
"INSERT INTO PERSON (FIRST_NAME, LAST_NAME) VALUES (?, ?)",
parameterizer);
}
});
}
Note reuse of the same paremeterizer in insert and select.
Processing updates
Executing upades with
Hammurapi Group Data Access is very straightforward:
- Unparameterized update:
processor.processUpdate("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) VALUES ('Pavel', 'Vlasov')", null);
- Parameterized update:
Parameterizer parameterizer=new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setString(1, firstName);
ps.setString(2, lastName);
}
};
processor.processUpdate("UPDATE PERSON SET FIRST_NAME=? WHERE LAST_NAME=?",parameterizer);
Injection & Projection
In
Hammurapi Group Data Access there are two ways to put database values to Java object:
- Injection - setting values of existing object.
- Projection - creating a new object from database values.
Projection can be done in the following ways:
- Default - Row is projected to a collection of field values
- Constructor - row field values are passed to constructor
- Property - row field values are injected through setters or public obect fields
- Interface - An implementation of interface is created at runtime and backed up with row's field values. This feature uses dynamic proxies and thus can run only on Java 1.3 and above. Proxies implement equals(Object), hashCode() and toDom(org.w3c.dom.Element) methods, so you can use them as Map keys and easily serialize to XML.
Injection, Property and Inteface projections use very simple naming convention for mapping column names to setters and fields. Underscore is considered as word separator and gets removed. Words are conveerted to lower case and then first letter is capitalized.
FIRST_NAME -> setFirstName().
Injection and Property projection leverage
DomConfigFactory class to inject data.
There are also three ways to obtain projection results:
- Database backed collection - Collection which doesn't keep any database information and every method, e.g. size() trips to the database for results.
- Torn-off collection - Projection results are placed to a regular collection, e.g java.util.ArrayList
- Single object projection - Projects and returns first row from result set, null if result set is empty.
SQLProcessor supports paginated projection, which means you can execute a query and get not the whole result set, but only, say, objects projected from 25 rows starting from row 100.
Examples below will show all the above listed approaches.
- Injection
Person3 person=new Person3();
processor.inject("SELECT * FROM PERSON WHERE ID=2", null, null, person);
processor.inject(
"SELECT * FROM PERSON WHERE ID=?",
new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setInt(1, personId);
}
},
null,
person);
Person3 class is defined as follows:
public class Person3 {
private String firstName;
private String lastName;
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;
}
}
- Default, database backed.
Creates a database backed collection with members of type collection having one member per column.
IMPORTANT NOTE: Iterator returned by database backed collections holds open resultset. Resultsets is closed when end of collection is reached. If you do not iterate through the whole collection and you want to explicitly close resource then you'll need to cast Iterator to biz.hammurapi.sql.DataIterator and invoke its close() method. If you don't do it then it will be closed in finalize().
Collection c=processor.project("SELECT * FROM PERSON");
- Constructor, torn off.
Creates objects using Person2(String, String) constructor, places them to new ArrayList and returns this ArrayList.
Projector projector=new ConstructorProjector(Person2.class.getConstructor(new Class[] {String.class, String.class}), null);
c=processor.project("SELECT FIRST_NAME, LAST_NAME FROM PERSON", null, projector, new ArrayList());
Person2 class is defined as follows:
public class Person2 {
private String firstName;
private String lastName;
public Person2(String firstName, String lastName) {
this.firstName=firstName;
this.lastName=lastName;
}
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;
}
}
- Property, paged, database backed.
Creates instances of Person2 using default constructor and then invokes setFirstName() setLastName() to set values. Resulting collection will contain 3rd 15-rows page, that is projected rows from 31 to 45.
projector=new PropertyProjector(Person2.class, null, null);
c=processor.project("SELECT FIRST_NAME, LAST_NAME FROM PERSON", null, projector, 3, 15);
- Interface, paged, torn off.
Creates implmenetation of Person interface with getter and setter operations routed to values read from the database. Implementation is a value object, which means that changing values through setters does not change values in the database.
c=processor.project("SELECT * FROM PERSON", null, Person.class, new ArrayList(), 3, 15);
Interface Person is defined as follows:
interface Person {
String getFirstName();
String getLastName();
void setFirstName(String firstName);
void setLastName(String lastName);
}
- Single object projection.
projector=new PropertyProjector(Person2.class, null, null);
Person2 person2=(Person2) processor.projectSingleObject("SELECT * FROM PERSON WHERE ID=2", null, projector);
- Single interface projection
Person person=(Person) processor.projectSingleObject(
"SELECT * FROM PERSON WHERE ID=?",
new Parameterizer() {
public void parameterize(PreparedStatement ps) throws SQLException {
ps.setInt(1, personId);
}
},
Person.class);
Executing scripts
There are two methods to execute scripts in SQLProcessor: executeScript(java.io.Reader, char), which executes SQL statements read from reader separated by specified characted, and executeScript(java.io.Reader) which delegates to the first method with semicolon as separator.
Metrics
SQLProcessor can gather statistics about SQL statements execution if you set metric consumer.
processor.setMetricConsumer(new SimpleMetricConsumer());
...
System.out.println("SQL metrics: \n"+processor.getMetricConsumer());
There are two readily available metric consumers:
- SimpleMetricConsumer - It will cost you two calls to System.currentTimeMillis() per select/update. Sample output is below:
SQL metrics:
1/2774.0 <= INSERT INTO PERSON (FIRST_NAME, LAST_NAME) VALUES ('Pavel', 'Vlasov')
2774.0 <- biz.hammurapi.sql.SQLProcessor@c832d2
2/140.0 <= SELECT * FROM PERSON
120.0 <- biz.hammurapi.sql.ResultSetCollection$8@1808199
20.0 <- biz.hammurapi.sql.SQLProcessor@c832d2
- StackCountingMetricConsumer - It is more expensive, as it constructs stack trace per each select/update in addition to two calls of System.currentTimeMillis(), but it gives more insight on where SQL statements are fired from. Here is a sample output:
[hammurapi] 5/10.0 <= UPDATE RESULT SET COMPILATION_UNIT=?, IS_NEW=true WHERE ID=?
[hammurapi] 0.0 <- org.hammurapi.results.persistent.jdbc.ResultsFactory.newReviewResults(ResultsFactory.java:147)
[hammurapi] 10.0 <- org.hammurapi.results.persistent.jdbc.ResultsFactory.newReviewResults(ResultsFactory.java:147)
[hammurapi] 0.0 <- org.hammurapi.results.persistent.jdbc.ResultsFactory.newReviewResults(ResultsFactory.java:147)
[hammurapi] 0.0 <- org.hammurapi.results.persistent.jdbc.ResultsFactory.newReviewResults(ResultsFactory.java:147)
[hammurapi] 0.0 <- org.hammurapi.results.persistent.jdbc.ResultsFactory.newReviewResults(ResultsFactory.java:147)
You can create your own implementations of MetricConsumer if you need.
Summary of classes
This is a summary of most the interesting
Hammurapi Group Data Access classes and interfaces.
Conclusion
This article described classes and interfaces which can help you make you database code smaller and more robust. SQL Compiler (SQLC) goes further - it generates data access and data transfer classes.
