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();
}