# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1323712378 0
# Node ID a4e79e8e0aa0a13f7e4dd96eea8819883822b291
# Parent  21e49e0a2307057e41019254a350d2bbaa5c41ae
Added support for symbolic SQL statements.

flys-aft/trunk@3390 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 21e49e0a2307 -r a4e79e8e0aa0 flys-aft/src/main/java/de/intevation/db/Statements.java
--- a/flys-aft/src/main/java/de/intevation/db/Statements.java	Mon Dec 12 16:57:58 2011 +0000
+++ b/flys-aft/src/main/java/de/intevation/db/Statements.java	Mon Dec 12 17:52:58 2011 +0000
@@ -7,8 +7,154 @@
 
 import org.apache.log4j.Logger;
 
+import java.util.List;
+import java.util.ArrayList;
+import java.util.Map;
+import java.util.HashMap;
+import java.util.Enumeration;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.PreparedStatement;
+import java.sql.Timestamp;
+
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
 public class Statements
 {
+    public static final Pattern VAR = Pattern.compile(":([a-zA-Z0-9_]+)");
+
+    public static class SymbolicStatement {
+
+        protected String statement;
+        protected String compiled;
+        protected Map<String, List<Integer>> positions;
+
+        public class Instance {
+
+            /** TODO: Support more types. */
+
+            protected PreparedStatement stmnt;
+
+            public Instance(Connection connection) throws SQLException {
+                stmnt = connection.prepareStatement(compiled);
+            }
+
+            public void close() {
+                try {
+                    stmnt.close();
+                }
+                catch (SQLException sqle) {
+                    log.error("cannot close statement", sqle);
+                }
+            }
+
+            public void setInt(String key, int value)
+            throws SQLException 
+            {
+                List<Integer> pos = positions.get(key.toLowerCase());
+                if (pos != null) {
+                    for (Integer p: pos) {
+                        stmnt.setInt(p, value);
+                    }
+                }
+            }
+
+            public void setString(String key, String value)
+            throws SQLException 
+            {
+                List<Integer> pos = positions.get(key.toLowerCase());
+                if (pos != null) {
+                    for (Integer p: pos) {
+                        stmnt.setString(p, value);
+                    }
+                }
+            }
+
+            public void setObject(String key, Object value)
+            throws SQLException 
+            {
+                List<Integer> pos = positions.get(key.toLowerCase());
+                if (pos != null) {
+                    for (Integer p: pos) {
+                        stmnt.setObject(p, value);
+                    }
+                }
+            }
+
+            public void setTimestamp(String key, Timestamp value)
+            throws SQLException 
+            {
+                List<Integer> pos = positions.get(key.toLowerCase());
+                if (pos != null) {
+                    for (Integer p: pos) {
+                        stmnt.setTimestamp(p, value);
+                    }
+                }
+            }
+
+            public void setDouble(String key, int value)
+            throws SQLException 
+            {
+                List<Integer> pos = positions.get(key.toLowerCase());
+                if (pos != null) {
+                    for (Integer p: pos) {
+                        stmnt.setDouble(p, value);
+                    }
+                }
+            }
+
+            public void setNull(String key, int sqlType)
+            throws SQLException 
+            {
+                List<Integer> pos = positions.get(key.toLowerCase());
+                if (pos != null) {
+                    for (Integer p: pos) {
+                        stmnt.setNull(p, sqlType);
+                    }
+                }
+            }
+
+            public void set(Map<String, Object> map) throws SQLException {
+                for (Map.Entry<String, Object> entry: map.entrySet()) {
+                    setObject(entry.getKey(), entry.getValue());
+                }
+            }
+
+        } // class Instance
+
+        public SymbolicStatement(String statement) {
+            this.statement = statement;
+            compile();
+        }
+
+        public String getStatement() {
+            return statement;
+        }
+
+        protected void compile() {
+            positions = new HashMap<String, List<Integer>>();
+
+            StringBuffer sb = new StringBuffer();
+            Matcher m = VAR.matcher(statement);
+            int index = 1;
+            while (m.find()) {
+                String key = m.group(1).toLowerCase();
+                List<Integer> list = positions.get(key);
+                if (list == null) {
+                    list = new ArrayList<Integer>();
+                    positions.put(key, list);
+                }
+                list.add(index++);
+                m.appendReplacement(sb, "?");
+            }
+            m.appendTail(sb);
+            compiled = sb.toString();
+        }
+    } // class SymbolicStatement
+
+
     private static Logger log = Logger.getLogger(Statements.class);
 
     public static final String RESOURCE_PATH = "/sql/";
@@ -17,13 +163,36 @@
     protected String type;
     protected String driver;
 
-    protected Properties properties;
+    protected Map<String, SymbolicStatement> statements;
 
     public Statements(String type, String driver) {
         this.type   = type;
         this.driver = driver;
     }
 
+    public SymbolicStatement getStatement(String key) {
+        if (statements == null) {
+            statements = loadStatements();
+        }
+        return statements.get(key);
+    }
+
+    protected Map<String, SymbolicStatement> loadStatements() {
+        Map<String, SymbolicStatement> statements =
+            new HashMap<String, SymbolicStatement>();
+
+        Properties properties = loadProperties();
+
+        for (Enumeration e = properties.propertyNames(); e.hasMoreElements();) {
+            String key = (String)e.nextElement();
+            String value = properties.getProperty(key);
+            SymbolicStatement symbolic = new SymbolicStatement(value);
+            statements.put(key, symbolic);
+        }
+
+        return statements;
+    }
+
     protected String driverToProperties() {
         return
             type + "-" + 
@@ -59,15 +228,11 @@
         return common;
     }
 
-    protected Properties getProperties() {
-
-        if (properties != null) {
-            return properties;
-        }
+    protected Properties loadProperties() {
 
         Properties common = loadCommon();
 
-        properties = new Properties(common);
+        Properties properties = new Properties(common);
 
         String path = RESOURCE_PATH + driverToProperties();