DefaultSqlOperation
- By Frank Sauer
- April 24, 2001
COMPONENT JAVA
Applying Design Patterns to JDBC: Building a Lightweight Object-Relational Mapping Framework
Frank Sauer
DefaultSqlOperation
package com.trcinc.infrastructureservices.jdbc;
import java.sql.*;
import java.util.Map;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
/**
* This can be used to automatically set all the parameters of a
* PreparedStatement based on the properties obtained from a
* Persistable. Most useful for update and insert statements.
*
* @author Frank Sauer
* @version 1.0
*/
public class DefaultSQLOperation extends AbstractSqlOperation {
/**
* Contains all the implementations of the Mapper interface, keyed on
* class (for setValue) and on the JDBC type (for getValue) from
* the ResultSetMetaData (an Integer)
*/
private static HashMap mappers = null;
private String queryTemplate = null;
private String[] columns = null;
private Persistable p = null;
/**
* Creates new DefaultSQLOperation.
* @param queryTemplate SQL string for the prepared statement containing N ?s
* @param columns array of length N determining what properties go where
*/
public DefaultSQLOperation(String queryTemplate, String[] columns) {
this.queryTemplate = queryTemplate;
this.columns = columns;
}
public void setPersistable(Persistable p) {
this.p = p;
}
public Persistable getPersistable() {
return p;
}
/**
* The default is to ask my Persistable for the properties,
* but subclasses can override, for example to inject additional
* properties to deal with relationships
*/
protected Map getProperties() {
if (debugSQL()) System.err.println("Getting props from Persistable...");
return getPersistable().getProperties();
}
public Object execute(Connection c) throws SQLException {
if (getPersistable()==null) return null;
ps = c.prepareStatement(queryTemplate);
if (debugSQL()) System.err.println("Executing\n" + queryTemplate +
"\nwith parameters: ");
Map props = getProperties();
if (debugSQL()) System.err.println("Nr of columns = " + columns.length);
for (int i = 0; i < columns.length;i++) {
Object val = props.get(columns[i]);
if (debugSQL()) System.err.println(" "+columns[i] + " = " + val);
setValue(ps, i+1, val); // params start at 1, not 0
}
if (debugSQL()) System.err.println("Executing query!");
boolean b = ps.execute();
if (b) return constructResultFromResultSet(ps.getResultSet());
else return null;
}
/**
* Uses the ResultSet's MetaData to populate a HashMap for use with
* setProperties on a new Persistable. The types of the column data
* are translated with the same Mapper objects that are used to set
* the parameters on a PreparedStatement. The correct Mapper type is
* determined from the result of ResultSetMetaData.getColumnType(index)
*/
protected List constructResultFromResultSet(ResultSet rs) throws SQLException {
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
List results = new ArrayList();
while ((rs != null) && (rs.next())) {
HashMap props = new HashMap();
for (int i = 0; i < columnCount; i++) {
String columnName = md.getColumnName(i+1);
int type = md.getColumnType(i+1);
Object o = getValue(rs, type, i+1);
if (debugSQL()) System.err.println(columnName + " = " + o);
if (o != null) props.put(columnName, o);
}
Persistable p = getNewPersistable();
if (debugSQL()) System.err.println("Setting properties with: " + props);
p.setProperties(props);
results.add(p);
}
return results;
}
/**
* Override this if you want to set the properties on your own Persistable
* implementation. This is a factory method, return a new instance every time!
* DefaultSQLOperation has a default implementation called DefaultPersistable,
* which simply wraps a HashMap.
*/
protected Persistable getNewPersistable() {
return new DefaultPersistable();
}
protected void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
if (anObject==null) ps.setNull(index, Types.NULL);
else {
Mapper m = getMapper(anObject.getClass());
if (m != null)
m.setValue(ps,index,anObject);
}
}
/**
* Get the properly typed value from column with index index
*/
protected Object getValue(ResultSet rs, int type, int index) {
if (debugSQL()) System.err.println("COLUMN TYPE = " +type);
try {
Mapper m = getMapper(new Integer(type));
if (m != null) {
return m.getValue(rs, index);
}
} catch (Exception x) {
// NULL values in the database cause most of the Mapper classes to fail
// a NULL value will result in the absence of this property from the result.
if (debugSQL()) System.err.println("Warning: Unknown column type or NULL value for column " + index);
}
return null;
}
public interface Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException;
public Object getValue(ResultSet rs, int index) throws SQLException;
}
public static class ByteMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setByte(index, ((Byte)anObject).byteValue());
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return new Byte(rs.getByte(index));
}
}
public static class DateMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setDate(index, new java.sql.Date(((java.util.Date)anObject).getTime()));
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return rs.getDate(index);
}
}
public static class DoubleMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setDouble(index, ((Double)anObject).doubleValue());
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return new Double(rs.getDouble(index));
}
}
public static class FloatMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setFloat(index, ((Float)anObject).floatValue());
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return new Float(rs.getFloat(index));
}
}
public static class IntMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setInt(index, ((Integer)anObject).intValue());
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return new Integer(rs.getInt(index));
}
}
public static class NumericMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setBigDecimal(index, ((java.math.BigDecimal)anObject));
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return rs.getBigDecimal(index);
}
}
public static class LongMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setLong(index, ((Long)anObject).longValue());
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return new Long(rs.getLong(index));
}
}
public static class ShortMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setShort(index, ((Short)anObject).shortValue());
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return new Short(rs.getShort(index));
}
}
public static class StringMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setString(index, (String)anObject);
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return rs.getString(index);
}
}
public static class TimeMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setTime(index, (Time)anObject);
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return rs.getTime(index);
}
}
public static class TimestampMapper implements Mapper {
public void setValue(PreparedStatement ps, int index, Object anObject)
throws SQLException {
ps.setTimestamp(index, (Timestamp)anObject);
}
public Object getValue(ResultSet rs, int index) throws SQLException {
return rs.getTimestamp(index);
}
}
/**
* Register a new Mapper class so values can be mapped properly to their SQL type
* and vice versa.
*/
public static void registerMapper(Class valueType, int sqlType, Mapper aMapper) {
if (valueType != null) mappers.put(valueType, aMapper);
if (sqlType != 0) mappers.put(new Integer(sqlType), aMapper);
}
protected Mapper getMapper(Object key) {
if (mappers == null) {
mappers = new HashMap();
registerMapper(Byte.class, Types.TINYINT, new ByteMapper());
DateMapper dm = new DateMapper();
registerMapper(java.util.Date.class, Types.DATE, dm);
// always convert sql Dates to util Dates from ResultSets
registerMapper(java.sql.Date.class, 0, dm);
registerMapper(Double.class, Types.DOUBLE, new DoubleMapper());
registerMapper(Float.class, Types.FLOAT, new FloatMapper());
registerMapper(Integer.class, Types.INTEGER, new IntMapper());
registerMapper(java.math.BigDecimal.class, Types.NUMERIC,
new NumericMapper());
registerMapper(Long.class, Types.BIGINT, new LongMapper());
registerMapper(Short.class, Types.SMALLINT, new ShortMapper());
StringMapper strm = new StringMapper();
registerMapper(String.class, Types.VARCHAR, strm);
registerMapper(null, Types.LONGVARCHAR, strm);
registerMapper(Time.class, Types.TIME, new TimeMapper());
registerMapper(Timestamp.class, Types.TIMESTAMP, new TimestampMapper());
}
return (Mapper)mappers.get(key);
}
public static class DefaultPersistable implements Persistable {
private Map props = new HashMap();
public Map getProperties() {
return props;
}
public void setProperties(Map map) {
props = map;
}
}
}