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 { /** * 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 { void executeWithCleanup(PreparedStatementWrapper pstmt) throws SQLException; }; protected void execute(QBody 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 executeQuery() { this.row_cnt=0; final List ret = new ArrayList(); execute(new QBody() { 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() { public void executeWithCleanup(final PreparedStatementWrapper pstmt) throws SQLException { do { setParameters(pstmt); SQLCommand.this.row_cnt += pstmt.executeUpdate(); } while (moreRows()); } }); } } And, here's an example usage snippet from my DAO class: // see SortableDataProvider examples protected List 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(); }