4.1 Apache Common Dbutils

Dbutils is a free open source O/R mapping library given by Apache. It can mapping the JDBC result set to Java bean without any configuration file.

The mapping is based on database column name and Java bean property name.

For example, database column named as NAME in table USER will mapping to the Java bean User getName()/setName(). This mapping is result set level, not table level. Which means, we can select from more than one table, and mapping the result data to a POJO.

In VelocityWeb, there is little improve. that is , FIRST_NAME can mapping to firstName.

Here is a sample of select single record:

    public boolean checkPassword(String userName, String encryptedPassword) {
        String sql = "select * from signon where username = ?";
        List paraList = new LinkedList();
        paraList.add(userName);
        Signon user = (Signon) this.queryBean(sql, paraList.toArray(), Signon.class);
        if (user == null) {
            // can't find the user with login name
            return false;
        }
        return encryptedPassword.equals(user.getPassword());
    }	  

and here is another sample for select more than one records:

    public java.util.List getAll(){
        String sql = "select * from product";
        return this.queryBeanList(sql, null, Product.class);//each element is a Product object
    }	

It's pity to say that, Dbutils is based on JDBC java.sql.PreparedStatement, which receive parameters by index, not by a name. For complex query, it may be easy to make mistake for parameters.