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