sascha@4073: package de.intevation.db;
sascha@4073: 
teichmann@4772: import java.sql.Connection;
teichmann@4772: import java.sql.PreparedStatement;
teichmann@4772: import java.sql.ResultSet;
teichmann@4772: import java.sql.SQLException;
teichmann@4772: import java.sql.Timestamp;
teichmann@4772: 
sascha@4073: import java.util.ArrayList;
teichmann@4772: import java.util.HashMap;
teichmann@4772: import java.util.List;
sascha@4073: import java.util.Map;
sascha@4073: 
sascha@4073: import java.util.regex.Matcher;
sascha@4073: import java.util.regex.Pattern;
sascha@4073: 
sascha@4073: import org.apache.log4j.Logger;
sascha@4073: 
sascha@4073: public class SymbolicStatement {
sascha@4073: 
sascha@4073:     private static Logger log = Logger.getLogger(SymbolicStatement.class);
sascha@4073: 
sascha@4073:     public static final Pattern VAR = Pattern.compile(":([a-zA-Z0-9_]+)");
sascha@4073: 
sascha@4073:     protected String statement;
sascha@4073:     protected String compiled;
sascha@4073:     protected Map<String, List<Integer>> positions;
sascha@4073: 
sascha@4073:     public class Instance {
sascha@4073: 
sascha@4073:         /** TODO: Support more types. */
sascha@4073: 
sascha@4073:         protected PreparedStatement stmnt;
sascha@4073: 
sascha@4073:         public Instance(Connection connection) throws SQLException {
sascha@4073:             stmnt = connection.prepareStatement(compiled);
sascha@4073:         }
sascha@4073: 
sascha@4073:         public void close() {
sascha@4073:             try {
sascha@4073:                 stmnt.close();
sascha@4073:             }
sascha@4073:             catch (SQLException sqle) {
sascha@4073:                 log.error("cannot close statement", sqle);
sascha@4073:             }
sascha@4073:         }
sascha@4073: 
sascha@4076:         public Instance setInt(String key, int value)
teichmann@4736:         throws SQLException
sascha@4073:         {
sascha@4073:             List<Integer> pos = positions.get(key.toLowerCase());
sascha@4073:             if (pos != null) {
sascha@4073:                 for (Integer p: pos) {
sascha@4073:                     stmnt.setInt(p, value);
sascha@4073:                 }
sascha@4073:             }
sascha@4076: 
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4076:         public Instance setString(String key, String value)
teichmann@4736:         throws SQLException
sascha@4073:         {
sascha@4073:             List<Integer> pos = positions.get(key.toLowerCase());
sascha@4073:             if (pos != null) {
sascha@4073:                 for (Integer p: pos) {
sascha@4073:                     stmnt.setString(p, value);
sascha@4073:                 }
sascha@4073:             }
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4076:         public Instance setObject(String key, Object value)
teichmann@4736:         throws SQLException
sascha@4073:         {
sascha@4073:             List<Integer> pos = positions.get(key.toLowerCase());
sascha@4073:             if (pos != null) {
sascha@4073:                 for (Integer p: pos) {
sascha@4073:                     stmnt.setObject(p, value);
sascha@4073:                 }
sascha@4073:             }
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4076:         public Instance setTimestamp(String key, Timestamp value)
teichmann@4736:         throws SQLException
sascha@4073:         {
sascha@4073:             List<Integer> pos = positions.get(key.toLowerCase());
sascha@4073:             if (pos != null) {
sascha@4073:                 for (Integer p: pos) {
sascha@4073:                     stmnt.setTimestamp(p, value);
sascha@4073:                 }
sascha@4073:             }
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4087:         public Instance setDouble(String key, double value)
teichmann@4736:         throws SQLException
sascha@4073:         {
sascha@4073:             List<Integer> pos = positions.get(key.toLowerCase());
sascha@4073:             if (pos != null) {
sascha@4073:                 for (Integer p: pos) {
sascha@4073:                     stmnt.setDouble(p, value);
sascha@4073:                 }
sascha@4073:             }
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4100:         public Instance setLong(String key, long value)
teichmann@4736:         throws SQLException
sascha@4100:         {
sascha@4100:             List<Integer> pos = positions.get(key.toLowerCase());
sascha@4100:             if (pos != null) {
sascha@4100:                 for (Integer p: pos) {
sascha@4100:                     stmnt.setLong(p, value);
sascha@4100:                 }
sascha@4100:             }
sascha@4100:             return this;
sascha@4100:         }
sascha@4100: 
sascha@4076:         public Instance setNull(String key, int sqlType)
teichmann@4736:         throws SQLException
sascha@4073:         {
sascha@4073:             List<Integer> pos = positions.get(key.toLowerCase());
sascha@4073:             if (pos != null) {
sascha@4073:                 for (Integer p: pos) {
sascha@4073:                     stmnt.setNull(p, sqlType);
sascha@4073:                 }
sascha@4073:             }
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4076:         public Instance set(Map<String, Object> map) throws SQLException {
sascha@4073:             for (Map.Entry<String, Object> entry: map.entrySet()) {
sascha@4073:                 setObject(entry.getKey(), entry.getValue());
sascha@4073:             }
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4076:         public Instance clearParameters() throws SQLException {
sascha@4073:             stmnt.clearParameters();
sascha@4076:             return this;
sascha@4073:         }
sascha@4073: 
sascha@4075:         public boolean execute() throws SQLException {
teichmann@5480:             if (log.isDebugEnabled()) {
teichmann@5480:                 log.debug("execute: " + compiled);
teichmann@5480:             }
sascha@4075:             return stmnt.execute();
sascha@4075:         }
sascha@4075: 
sascha@4075:         public ResultSet executeQuery() throws SQLException {
teichmann@5480:             if (log.isDebugEnabled()) {
teichmann@5480:                 log.debug("query: " + compiled);
teichmann@5480:             }
sascha@4075:             return stmnt.executeQuery();
sascha@4075:         }
sascha@4075: 
sascha@4075:         public int executeUpdate() throws SQLException {
teichmann@5480:             if (log.isDebugEnabled()) {
teichmann@5480:                 log.debug("update: " + compiled);
teichmann@5480:             }
sascha@4075:             return stmnt.executeUpdate();
sascha@4075:         }
sascha@4075: 
sascha@4073:     } // class Instance
sascha@4073: 
sascha@4073:     public SymbolicStatement(String statement) {
sascha@4073:         this.statement = statement;
sascha@4073:         compile();
sascha@4073:     }
sascha@4073: 
sascha@4073:     public String getStatement() {
sascha@4073:         return statement;
sascha@4073:     }
sascha@4073: 
sascha@4073:     protected void compile() {
sascha@4073:         positions = new HashMap<String, List<Integer>>();
sascha@4073: 
sascha@4073:         StringBuffer sb = new StringBuffer();
sascha@4073:         Matcher m = VAR.matcher(statement);
sascha@4073:         int index = 1;
sascha@4073:         while (m.find()) {
sascha@4073:             String key = m.group(1).toLowerCase();
sascha@4073:             List<Integer> list = positions.get(key);
sascha@4073:             if (list == null) {
sascha@4073:                 list = new ArrayList<Integer>();
sascha@4073:                 positions.put(key, list);
sascha@4073:             }
sascha@4073:             list.add(index++);
sascha@4073:             m.appendReplacement(sb, "?");
sascha@4073:         }
sascha@4073:         m.appendTail(sb);
sascha@4073:         compiled = sb.toString();
sascha@4073:     }
sascha@4073: } // class SymbolicStatement