Merge pull request #875 from vitalidze/master

Database code refactoring

2 files changed, 238 insertions, 155 deletions

diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java
index 27f4cb5c2..bb44c6389 100644
--- a/src/org/traccar/database/DataManager.java
+++ b/src/org/traccar/database/DataManager.java
@@ -91,45 +91,36 @@ public class DataManager {
 
         query = properties.getProperty("database.selectDevice");
         if (query != null) {
-            queryGetDevices = new NamedParameterStatement(query);
+            queryGetDevices = new NamedParameterStatement(query, dataSource);
         }
 
         query = properties.getProperty("database.insertPosition");
         if (query != null) {
-            queryAddPosition = new NamedParameterStatement(query);
+            queryAddPosition = new NamedParameterStatement(query, dataSource, Statement.RETURN_GENERATED_KEYS);
         }
 
         query = properties.getProperty("database.updateLatestPosition");
         if (query != null) {
-            queryUpdateLatestPosition = new NamedParameterStatement(query);
+            queryUpdateLatestPosition = new NamedParameterStatement(query, dataSource);
         }
     }
 
-    public synchronized List<Device> getDevices() throws SQLException {
-
-        List<Device> deviceList = new LinkedList<Device>();
+    private final NamedParameterStatement.ResultSetProcessor<Device> deviceResultSetProcessor = new NamedParameterStatement.ResultSetProcessor<Device>() {
+        @Override
+        public Device processNextRow(ResultSet rs) throws SQLException {
+            Device device = new Device();
+            device.setId(rs.getLong("id"));
+            device.setImei(rs.getString("imei"));
+            return device;
+        }
+    };
 
+    public List<Device> getDevices() throws SQLException {
         if (queryGetDevices != null) {
-            Connection connection = dataSource.getConnection();
-            try {
-                PreparedStatement statement = queryGetDevices.prepare(connection);
-                try {
-                    ResultSet result = statement.executeQuery();
-                    while (result.next()) {
-                        Device device = new Device();
-                        device.setId(result.getLong("id"));
-                        device.setImei(result.getString("imei"));
-                        deviceList.add(device);
-                    }
-                } finally {
-                    statement.close();
-                }
-            } finally {
-                connection.close();
-            }
+            return queryGetDevices.prepare().executeQuery(deviceResultSetProcessor);
+        } else {
+            return new LinkedList<Device>();
         }
-
-        return deviceList;
     }
 
     /**
@@ -149,61 +140,41 @@ public class DataManager {
         return devices.get(imei);
     }
 
-    public synchronized Long addPosition(Position position) throws SQLException {
+    private NamedParameterStatement.ResultSetProcessor<Long> generatedKeysResultSetProcessor = new NamedParameterStatement.ResultSetProcessor<Long>() {
+        @Override
+        public Long processNextRow(ResultSet rs) throws SQLException {
+            return rs.getLong(1);
+        }
+    };
 
+    public synchronized Long addPosition(Position position) throws SQLException {
         if (queryAddPosition != null) {
-            Connection connection = dataSource.getConnection();
-            try {
-                PreparedStatement statement = queryAddPosition.prepare(connection, Statement.RETURN_GENERATED_KEYS);
-                try {
-                    assignVariables(queryAddPosition, statement, position);
-                    statement.executeUpdate();
-
-                    ResultSet result = statement.getGeneratedKeys();
-                    if (result != null && result.next()) {
-                        return result.getLong(1);
-                    }
-                } finally {
-                    statement.close();
-                }
-            } finally {
-                connection.close();
+            List<Long> result = assignVariables(queryAddPosition.prepare(), position).executeUpdate(generatedKeysResultSetProcessor);
+            if (result != null && !result.isEmpty()) {
+                return result.iterator().next();
             }
         }
-
         return null;
     }
 
     public void updateLatestPosition(Position position, Long positionId) throws SQLException {
         if (queryUpdateLatestPosition != null) {
-            Connection connection = dataSource.getConnection();
-            try {
-                PreparedStatement statement = queryUpdateLatestPosition.prepare(connection);
-                try {
-                    assignVariables(queryUpdateLatestPosition, statement, position);
-                    queryUpdateLatestPosition.setLong(statement, "id", positionId);
-                    statement.executeUpdate();
-                } finally {
-                    statement.close();
-                }
-            } finally {
-                connection.close();
-            }
+            assignVariables(queryUpdateLatestPosition.prepare(), position).setLong("id", positionId).executeUpdate();
         }
     }
 
-    private void assignVariables(NamedParameterStatement nps, PreparedStatement ps, Position position) throws SQLException {
+    private NamedParameterStatement.Params assignVariables(NamedParameterStatement.Params params, Position position) throws SQLException {
 
-        nps.setLong(ps, "device_id", position.getDeviceId());
-        nps.setTimestamp(ps, "time", position.getTime());
-        nps.setBoolean(ps, "valid", position.getValid());
-        nps.setDouble(ps, "altitude", position.getAltitude());
-        nps.setDouble(ps, "latitude", position.getLatitude());
-        nps.setDouble(ps, "longitude", position.getLongitude());
-        nps.setDouble(ps, "speed", position.getSpeed());
-        nps.setDouble(ps, "course", position.getCourse());
-        nps.setString(ps, "address", position.getAddress());
-        nps.setString(ps, "extended_info", position.getExtendedInfo());
+        params.setLong("device_id", position.getDeviceId());
+        params.setTimestamp("time", position.getTime());
+        params.setBoolean("valid", position.getValid());
+        params.setDouble("altitude", position.getAltitude());
+        params.setDouble("latitude", position.getLatitude());
+        params.setDouble("longitude", position.getLongitude());
+        params.setDouble("speed", position.getSpeed());
+        params.setDouble("course", position.getCourse());
+        params.setString("address", position.getAddress());
+        params.setString("extended_info", position.getExtendedInfo());
 
         // DELME: Temporary compatibility support
         XPath xpath = XPathFactory.newInstance().newXPath();
@@ -211,22 +182,24 @@ public class DataManager {
             InputSource source = new InputSource(new StringReader(position.getExtendedInfo()));
             String index = xpath.evaluate("/info/index", source);
             if (!index.isEmpty()) {
-                nps.setLong(ps, "id", Long.valueOf(index));
+                params.setLong("id", Long.valueOf(index));
             } else {
-                nps.setLong(ps, "id", null);
+                params.setLong("id", null);
             }
             source = new InputSource(new StringReader(position.getExtendedInfo()));
             String power = xpath.evaluate("/info/power", source);
             if (!power.isEmpty()) {
-                nps.setDouble(ps, "power", Double.valueOf(power));
+                params.setDouble("power", Double.valueOf(power));
             } else {
-                nps.setLong(ps, "power", null);
+                params.setLong("power", null);
             }
         } catch (XPathExpressionException e) {
             Log.warning("Error in XML: " + position.getExtendedInfo(), e);
-            nps.setLong(ps, "id", null);
-            nps.setLong(ps, "power", null);
+            params.setLong("id", null);
+            params.setLong("power", null);
         }
+
+        return params;
     }
 
 }
diff --git a/src/org/traccar/database/NamedParameterStatement.java b/src/org/traccar/database/NamedParameterStatement.java
index 850462b2c..dcf041809 100644
--- a/src/org/traccar/database/NamedParameterStatement.java
+++ b/src/org/traccar/database/NamedParameterStatement.java
@@ -15,6 +15,7 @@
  */
 package org.traccar.database;
 
+import javax.sql.DataSource;
 import java.sql.*;
 import java.util.*;
 import java.util.Date;
@@ -25,9 +26,195 @@ public class NamedParameterStatement {
 
     private final String parsedQuery;
 
-    public NamedParameterStatement(String query) {
-        indexMap = new HashMap<String, List<Integer>>();
-        parsedQuery = parse(query, indexMap);
+    private final DataSource dataSource;
+
+    private final int autoGeneratedKeys;
+
+    public interface ResultSetProcessor<T> {
+        T processNextRow(ResultSet rs) throws SQLException;
+    }
+
+    public class Params {
+        Map<String, Object> values;
+        Map<String, Integer> types;
+
+        public Params setInt(String name, Integer value) {
+            setType(name, Types.INTEGER);
+            return setValue(name, value);
+        }
+
+        public Params setLong(String name, Long value) {
+            setType(name, Types.INTEGER);
+            return setValue(name, value);
+        }
+
+        public Params setString(String name, String value) {
+            setType(name, Types.VARCHAR);
+            return setValue(name, value);
+        }
+
+        public Params setDouble(String name, Double value) {
+            setType(name, Types.DOUBLE);
+            return setValue(name, value);
+        }
+
+        public Params setTimestamp(String name, Date value) {
+            setType(name, Types.TIMESTAMP);
+            return setValue(name, value);
+        }
+
+        public Params setBoolean(String name, Boolean value) {
+            setType(name, Types.BOOLEAN);
+            return setValue(name, value);
+        }
+
+        private void setType(String name, Integer type) {
+            if (types == null) {
+                types = new HashMap<String, Integer>();
+            }
+            types.put(name, type);
+        }
+
+        private Params setValue(String name, Object value) {
+            if (values == null) {
+                values = new HashMap<String, Object>();
+            }
+            values.put(name, value);
+            return this;
+        }
+
+        public <T> List<T> executeQuery(ResultSetProcessor<T> processor) throws SQLException {
+            Connection conn = null;
+            PreparedStatement stmt = null;
+            ResultSet rs = null;
+
+            List<T> result = new LinkedList<T>();
+
+            try {
+                conn = dataSource.getConnection();
+                stmt = conn.prepareStatement(parsedQuery);
+
+                setParams(stmt);
+
+                rs = stmt.executeQuery();
+                while (rs.next()) {
+                    result.add(processor.processNextRow(rs));
+                }
+            } finally {
+                closeQuietly(conn, stmt, rs);
+            }
+
+            return result;
+        }
+
+        public <T> List<T> executeUpdate(ResultSetProcessor<T> processor) throws SQLException {
+            Connection conn = null;
+            PreparedStatement stmt = null;
+            ResultSet rs = null;
+
+            try {
+                conn = dataSource.getConnection();
+                stmt = conn.prepareStatement(parsedQuery, autoGeneratedKeys);
+
+                setParams(stmt);
+
+                stmt.executeUpdate();
+
+                if (autoGeneratedKeys == Statement.RETURN_GENERATED_KEYS) {
+                    rs = stmt.getGeneratedKeys();
+                    List<T> result = new LinkedList<T>();
+                    while (rs.next()) {
+                        result.add(processor.processNextRow(rs));
+                    }
+                    return result;
+                }
+            } finally {
+                closeQuietly(conn, stmt, rs);
+            }
+
+            return null;
+        }
+
+        public void executeUpdate() throws SQLException {
+            executeUpdate(null);
+        }
+
+        private void setParams(PreparedStatement stmt) throws SQLException {
+            if (values == null) {
+                return;
+            }
+
+            for (Map.Entry<String, Object> entry : values.entrySet()) {
+                String name = entry.getKey();
+                Object value = entry.getValue();
+                int type = types.get(name);
+
+                List<Integer> indexList = indexMap.get(name);
+                if (indexList != null) for (Integer index: indexList) {
+                    if (value == null) {
+                        stmt.setNull(index, type);
+                    } else {
+                        switch (type) {
+                            case Types.INTEGER:
+                                if (value instanceof Long) {
+                                    stmt.setLong(index, (Long) value);
+                                } else if (value instanceof Integer) {
+                                    stmt.setInt(index, (Integer) value);
+                                } else {
+                                    throw new IllegalArgumentException("Unknown integer value: " + value.getClass());
+                                }
+                                break;
+                            case Types.VARCHAR:
+                                stmt.setString(index, (String) value);
+                                break;
+                            case Types.TIMESTAMP:
+                                stmt.setTimestamp(index, new Timestamp(((Date) value).getTime()));
+                                break;
+                            case Types.DOUBLE:
+                                stmt.setDouble(index, (Double) value);
+                                break;
+                            case Types.BOOLEAN:
+                                stmt.setBoolean(index, (Boolean) value);
+                                break;
+                        }
+                    }
+                }
+            }
+        }
+
+        private void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
+            if (rs != null) {
+                try {
+                    rs.close();
+                } catch (SQLException sqex) {
+                }
+            }
+
+            if (stmt != null) {
+                try {
+                    stmt.close();
+                } catch (SQLException sqex) {
+                }
+            }
+
+            if (conn != null) {
+                try {
+                    conn.close();
+                } catch (SQLException sqex) {
+                }
+            }
+        }
+    }
+
+    public NamedParameterStatement(String query, DataSource dataSource) {
+        this(query, dataSource, Statement.NO_GENERATED_KEYS);
+    }
+
+    public NamedParameterStatement(String query, DataSource dataSource, int autoGeneratedKeys) {
+        this.indexMap = new HashMap<String, List<Integer>>();
+        this.parsedQuery = parse(query, indexMap);
+        this.dataSource = dataSource;
+        this.autoGeneratedKeys = autoGeneratedKeys;
     }
 
     static String parse(String query, Map<String, List<Integer>> paramMap) {
@@ -83,84 +270,7 @@ public class NamedParameterStatement {
         return parsedQuery.toString();
     }
 
-    public PreparedStatement prepare(Connection connection, int autoGeneratedKeys) throws SQLException {
-        return connection.prepareStatement(parsedQuery, autoGeneratedKeys);
-    }
-
-    public PreparedStatement prepare(Connection connection) throws SQLException {
-        return prepare(connection, Statement.NO_GENERATED_KEYS);
-    }
-
-    public void setInt(PreparedStatement statement, String name, Integer value) throws SQLException {
-
-        List<Integer> indexList = indexMap.get(name);
-        if (indexList != null) for (Integer index: indexList) {
-            if (value != null) {
-                statement.setInt(index, value);
-            } else {
-                statement.setNull(index, Types.INTEGER);
-            }
-        }
-    }
-
-    public void setLong(PreparedStatement statement, String name, Long value) throws SQLException {
-
-        List<Integer> indexList = indexMap.get(name);
-        if (indexList != null) for (Integer index: indexList) {
-            if (value != null) {
-                statement.setLong(index, value);
-            } else {
-                statement.setNull(index, Types.INTEGER);
-            }
-        }
+    public Params prepare() {
+        return new Params();
     }
-
-    public void setBoolean(PreparedStatement statement, String name, Boolean value) throws SQLException {
-
-        List<Integer> indexList = indexMap.get(name);
-        if (indexList != null) for (Integer index: indexList) {
-            if (value != null) {
-                statement.setBoolean(index, value);
-            } else {
-                statement.setNull(index, Types.BOOLEAN);
-            }
-        }
-    }
-
-    public void setDouble(PreparedStatement statement, String name, Double value) throws SQLException {
-
-        List<Integer> indexList = indexMap.get(name);
-        if (indexList != null) for (Integer index: indexList) {
-            if (value != null) {
-                statement.setDouble(index, value);
-            } else {
-                statement.setNull(index, Types.DOUBLE);
-            }
-        }
-    }
-
-    public void setTimestamp(PreparedStatement statement, String name, Date value) throws SQLException {
-
-        List<Integer> indexList = indexMap.get(name);
-        if (indexList != null) for (Integer index: indexList) {
-            if (value != null) {
-                statement.setTimestamp(index, new Timestamp(value.getTime()));
-            } else {
-                statement.setNull(index, Types.TIMESTAMP);
-            }
-        }
-    }
-
-    public void setString(PreparedStatement statement, String name, String value) throws SQLException {
-
-        List<Integer> indexList = indexMap.get(name);
-        if (indexList != null) for (Integer index: indexList) {
-            if (value != null) {
-                statement.setString(index, value);
-            } else {
-                statement.setNull(index, Types.VARCHAR);
-            }
-        }
-    }
-
 }