DefaultSqlOperation

• By Frank Sauer
• April 24, 2001

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