SQLCommand - ultra-light JDBC wrapper class





-3
Date Submitted Thu. Feb. 23rd, 2006 9:57 AM
Revision 3 of 3
Helper jpinkham
Tags Java | JDBC | lightweight | orm | SQL
Comments 1 comments
I actually like SQL. So, things like Hibernate with it's own query language don't quite fit my style. But, I don't want to code the same catch SQLException conn.close over and over either. So, I came up with this.

The idea is you extend SQLCommand (usually anonymously) override getSql(), and call execute() to get a list of whatever objects you are selecting - all the cleanup stuff is taken care of. It also handles nullable attributes more intuitively using ResultSetWrapper and PreparedStatementWrapper so that in your overridden getRow(ResultSetWrapper rs) method, you can call getInt on a nullable column and have it return null - what a concept! Also I like java.util.Date for my dates, so the wrappers convert to/from java.sql.Timestamp.

Right now I've only bothered with some basic types - it should be pretty clear how to add more if you need em.

See what ya'll think.

Oh, it uses JDK 1.5 Generics, but stripping that away would be pretty easy if you wanted 1.2 compliance.

import java.lang.reflect.UndeclaredThrowableException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Jim's super-light ORM
 *
 * Usage:  Extend SQLCommand and override getSql(). 
 *
 * If you want a query, override getRow and call executeQuery. 
 * For an update, override setParameters and call executeUpdate.
 *
 */

public abstract class SQLCommand<T> {

        /**
         * Pares down PreparedStatement to just the basics: Int, Double, String, Boolean, Date
         * Translates java.sql.Timestamp to java.util.Date, and boolean to char Y/N
         */

        protected static class PreparedStatementWrapper {
                private PreparedStatement stmt;
                PreparedStatementWrapper(PreparedStatement stmt) {
                        this.stmt = stmt;
                }
                public void setInt(int i, Integer val) throws SQLException {
                        if (val == null)
                                this.stmt.setNull(i, Types.INTEGER);
                        else
                                this.stmt.setInt(i, val);
                }
                public void setDouble(int i, Double val) throws SQLException {
                        if (val == null)
                                this.stmt.setNull(i, Types.DOUBLE);
                        else
                                this.stmt.setDouble(i, val);
                }
                public void setString(int i, String val) throws SQLException {
                        if (val == null)
                                this.stmt.setNull(i, Types.CHAR);
                        else
                                this.stmt.setString(i, val);
                }
                public void setBoolean(int i, Boolean val) throws SQLException {
                        if (val == null)
                                this.stmt.setNull(i, Types.CHAR);
                        else
                                setString(i, val.booleanValue() ? "Y" : "N");
                }
                public void setDate(int i, Date val) throws SQLException {
                        if (val == null)
                                this.stmt.setNull(i, Types.TIMESTAMP);
                        else
                                this.stmt.setTimestamp(i, new Timestamp(val.getTime()));
                }
                public ResultSet executeQuery() throws SQLException {
                        return this.stmt.executeQuery();
                }
                public int executeUpdate() throws SQLException {
                        return this.stmt.executeUpdate();
                }
                public void close() throws SQLException {
                        this.stmt.close();
                }
        }

        /**
         * Pares down ResultSet to just the basics: Int, Double, String, Boolean, Date
         * Translates java.sql.Timestamp to java.util.Date, and boolean to char Y/N
         */

        protected static class ResultSetWrapper {
                private ResultSet rs;
                ResultSetWrapper(ResultSet rs) {
                        this.rs = rs;
                }
                public Integer getInt(String columnName) throws SQLException {
                        return getInt(this.rs.findColumn(columnName));
                }
                public Integer getInt(int col) throws SQLException {
                        Integer ret = new Integer(this.rs.getInt(col));
                        return this.rs.wasNull() ? null : ret;
                }
                public Double getDouble(String columnName) throws SQLException {
                        Double ret = new Double(this.rs.getDouble(columnName));
                        return this.rs.wasNull() ? null : ret;
                }
                public String getString(int col) throws SQLException {
                        return this.rs.getString(col);
                }
                public String getString(String columnName) throws SQLException {
                        return this.rs.getString(columnName);
                }
                public Boolean getBoolean(String columnName) throws SQLException {
                        Boolean ret = Boolean.valueOf(this.rs.getBoolean(columnName));
                        return this.rs.wasNull() ? null : ret;
                }
                public Date getDate(String columnName) throws SQLException {
                        Timestamp ts = this.rs.getTimestamp(columnName);
                        return ts == null ? null : new Date(ts.getTime());
                }
                public boolean next() throws SQLException {
                        return this.rs.next();
                }
                //public void close() throws SQLException {
                //      this.rs.close();
                //}
        }
       
        private Connection conn;
        public SQLCommand(Connection conn) {
                this.conn = conn;
        }

        protected abstract String getSql();
        @SuppressWarnings("unused")     protected void setParameters(PreparedStatementWrapper p) throws SQLException {};
        @SuppressWarnings("unused")     protected T getRow(ResultSetWrapper rs) throws SQLException {
                throw new UnsupportedOperationException("you forgot to override getRow");
        }
        protected boolean moreRows() { return false; }  // default update just once
        protected int row_cnt = 0;
        protected int getRowCnt() { return this.row_cnt; }
        protected Connection getConnection() { return this.conn; }
       
        private interface QBody<T> {
                void executeWithCleanup(PreparedStatementWrapper pstmt) throws SQLException;
        };
       
        protected void execute(QBody<T> qbody) {
                String sql = getSql();
                PreparedStatementWrapper pstmt = null;
                try {
                        pstmt = new PreparedStatementWrapper(this.conn.prepareStatement(sql));
                        qbody.executeWithCleanup(pstmt);
                } catch (Exception e) {
                        throw new UndeclaredThrowableException(e, "Problem with SQL:"+sql);
                } finally {
                        if (pstmt != null)
                                try {
                                        pstmt.close();
                                } catch (Exception e2) {
                                        throw new UndeclaredThrowableException(e2, "Problem closing stmt");                         
                                }
                        if (this.conn != null)
                                try {
                                        this.conn.close();
                                } catch (Exception e2) {
                                        throw new UndeclaredThrowableException(e2, "Problem closing connection");                                   
                                }
                }
        }

        public List<T> executeQuery() {
                this.row_cnt=0;
                final List<T> ret = new ArrayList<T>();
                execute(new QBody<T>() {
                        public void executeWithCleanup(final PreparedStatementWrapper pstmt) throws SQLException {      
                                setParameters(pstmt);
                                ResultSetWrapper rs = new ResultSetWrapper(pstmt.executeQuery());
                                while (rs.next()) {
                                        ret.add(getRow(rs));
                                        SQLCommand.this.row_cnt++;
                                }
                        }
                });
                return ret;
        }
       
        public void executeUpdate() {
                this.row_cnt=0;
                execute(new QBody<T>() {
                        public void executeWithCleanup(final PreparedStatementWrapper pstmt) throws SQLException {
                                do {
                                        setParameters(pstmt);
                                    SQLCommand.this.row_cnt += pstmt.executeUpdate();
                                } while (moreRows());
                        }
                });
        }
}
 

        // Light-weight items for catalog only - shallow members
        protected static class GetCatalogItemsCommand extends SQLCommand<Item> {
                public GetCatalogItemsCommand(Connection conn) {
                        super(conn);
                }       
                @Override
                protected String getSql() {                    
                        return "select i.id,i.name,m.name as donor,description,category_id,c.value as category_name,event_on "+
                        "from members m, items i "+
                        "left join codes c on (i.category_id = c.id and c.code = 'CATEGORY') "+
                        "where m.id = i.member_id";
                }
                @Override
                protected Item getRow(ResultSetWrapper rs) throws SQLException {
                        Member m = new Member();
                        m.setName(rs.getString("donor"));
                        Item item = new Item(m);
                        item.setItemNumber(rs.getInt("id"));
                        item.setMember(m);
                        item.setName(rs.getString("name"));
                        item.setDescription(rs.getString("description"));
                        item.setCategory(DAO.getInstance().getCategory(rs.getInt("category_id")));
                        item.setDate(rs.getDate("event_on"));
                        return item;
                }
        }       

        // see SortableDataProvider examples
        protected List<Item> getItems(final List sortList, final int first, final int count) {
                return new GetCatalogItemsCommand(getConnection()) {
                        @Override
                        protected String getSql() {
                                String orderBy = "";
                                if (!sortList.isEmpty()) {
                                        orderBy = "order by ";
                                        boolean first1 = true;
                                        Iterator i = sortList.iterator();
                                        while (i.hasNext()) {
                                                SortParam p = (SortParam)i.next();
                                                if (!first1)
                                                        orderBy += ",";
                                                first1 = false;
                                                orderBy += p.getProperty();
                                                if (!p.isAscending())
                                                        orderBy += " DESC";
                                        }
                                }
                                return super.getSql()+" " + orderBy+ " limit "+count+" offset "+first;
                        }
                }.executeQuery();
        }
 

Jim Pinkham

Comments

Comments Spring Framework
Thu. Sep. 21st, 2006 9:36 PM    Beginner java_junkie

Voting