Refactor data manager code

5 files changed, 437 insertions, 319 deletions

diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java
index 33de88876..5473c6c77 100644
--- a/src/org/traccar/database/DataManager.java
+++ b/src/org/traccar/database/DataManager.java
@@ -17,21 +17,16 @@ package org.traccar.database;
 
 import com.mchange.v2.c3p0.ComboPooledDataSource;
 import java.io.File;
-import java.io.StringReader;
 import java.net.URL;
 import java.net.URLClassLoader;
 import java.sql.*;
 import java.util.*;
 import javax.json.JsonArray;
 import javax.sql.DataSource;
-import javax.xml.xpath.XPath;
-import javax.xml.xpath.XPathExpressionException;
-import javax.xml.xpath.XPathFactory;
 import org.traccar.helper.DriverDelegate;
-import org.traccar.helper.Log;
 import org.traccar.model.Device;
+import org.traccar.model.Permission;
 import org.traccar.model.Position;
-import org.xml.sax.InputSource;
 
 public class DataManager {
 
@@ -198,109 +193,98 @@ public class DataManager {
     private void createDatabaseSchema() throws SQLException {
 
         Connection connection = dataSource.getConnection();
-        try {
-            Statement statement = connection.createStatement();
-            try {
-                
-                ResultSet result = connection.getMetaData().getTables(
-                        connection.getCatalog(), null, null, null);
-                
-                boolean exist = false;
-                while (result.next()) {
-                    if (result.getString("TABLE_NAME").equalsIgnoreCase("traccar1")) {
-                        exist = true;
-                        break;
-                    }
-                }
-                
-                if (!exist) {
-
-                    statement.executeUpdate(
-                            "CREATE TABLE user (" +
-                            "id INT PRIMARY KEY AUTO_INCREMENT," +
-                            "email VARCHAR(1024) NOT NULL UNIQUE," +
-                            "password VARCHAR(1024) NOT NULL," +
-                            "salt VARCHAR(1024) NOT NULL," +
-                            "readonly BOOLEAN DEFAULT false NOT NULL," +
-                            "admin BOOLEAN DEFAULT false NOT NULL," +
-                            "map VARCHAR(1024) DEFAULT 'osm' NOT NULL," +
-                            "language VARCHAR(1024) DEFAULT 'en' NOT NULL," +
-                            "distance_unit VARCHAR(1024) DEFAULT 'km' NOT NULL," +
-                            "speed_unit VARCHAR(1024) DEFAULT 'kmh' NOT NULL," +
-                            "latitude DOUBLE DEFAULT 0 NOT NULL," +
-                            "longitude DOUBLE DEFAULT 0 NOT NULL," +
-                            "zoom INT DEFAULT 0 NOT NULL);" +
-                                    
-                            "CREATE TABLE device (" +
-                            "id INT PRIMARY KEY AUTO_INCREMENT," +
-                            "name VARCHAR(1024) NOT NULL," +
-                            "unique_id VARCHAR(1024) NOT NULL UNIQUE," +
-                            "status VARCHAR(1024)," +
-                            "last_update TIMESTAMP," +
-                            "position_id INT," +
-                            "data_id INT);" +
-
-                            "CREATE TABLE user_device (" +
-                            "user_id INT NOT NULL," +
-                            "device_id INT NOT NULL," +
-                            "read BOOLEAN DEFAULT true NOT NULL," +
-                            "write BOOLEAN DEFAULT true NOT NULL," +
-                            "FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE," +
-                            "FOREIGN KEY (device_id) REFERENCES device(id) ON DELETE CASCADE);" +
-
-                            "CREATE INDEX user_device_user_id ON user_device(user_id);" +
-
-                            "CREATE TABLE position (" +
-                            "id INT PRIMARY KEY AUTO_INCREMENT," +
-                            "protocol VARCHAR(1024)," +
-                            "device_id INT NOT NULL," +
-                            "server_time TIMESTAMP NOT NULL," +
-                            "device_time TIMESTAMP NOT NULL," +
-                            "fix_time TIMESTAMP NOT NULL," +
-                            "valid BOOLEAN NOT NULL," +
-                            "latitude DOUBLE NOT NULL," +
-                            "longitude DOUBLE NOT NULL," +
-                            "altitude DOUBLE NOT NULL," +
-                            "speed DOUBLE NOT NULL," +
-                            "course DOUBLE NOT NULL," +
-                            "address VARCHAR(1024)," +
-                            "other VARCHAR(8192) NOT NULL," +
-                            "FOREIGN KEY (device_id) REFERENCES device(id) ON DELETE CASCADE);" +
-
-                            "CREATE TABLE data (" +
-                            "id INT PRIMARY KEY AUTO_INCREMENT," +
-                            "protocol VARCHAR(1024)," +
-                            "device_id INT NOT NULL," +
-                            "server_time TIMESTAMP NOT NULL," +
-                            "device_time TIMESTAMP NOT NULL," +
-                            "other VARCHAR(8192) NOT NULL," +
-                            "FOREIGN KEY (device_id) REFERENCES device(id));" +
-
-                            "ALTER TABLE device ADD " +
-                            "FOREIGN KEY (position_id) REFERENCES position(id);" +
-
-                            "ALTER TABLE device ADD " +
-                            "FOREIGN KEY (data_id) REFERENCES data(id);" +
-
-                            "CREATE TABLE server (" +
-                            "id INT PRIMARY KEY AUTO_INCREMENT," +
-                            "registration BOOLEAN NOT NULL," +
-                            "latitude DOUBLE NOT NULL," +
-                            "longitude DOUBLE NOT NULL," +
-                            "zoom INT NOT NULL);" +
-
-                            "CREATE TABLE traccar1 (" +
-                            "id INT PRIMARY KEY AUTO_INCREMENT);");
-                    
-                    addUser("admin", "admin", true);
-                }
-
-            } finally {
-                statement.close();
+        ResultSet result = connection.getMetaData().getTables(
+                connection.getCatalog(), null, null, null);
+        
+        boolean exist = false;
+        while (result.next()) {
+            if (result.getString("TABLE_NAME").equalsIgnoreCase("traccar1")) {
+                exist = true;
+                break;
             }
-        } finally {
-            connection.close();
         }
+        if (!exist) {
+            return;
+        }
+        
+        QueryBuilder.create(dataSource,
+                "CREATE TABLE user (" +
+                "id INT PRIMARY KEY AUTO_INCREMENT," +
+                "email VARCHAR(1024) NOT NULL UNIQUE," +
+                "password VARCHAR(1024) NOT NULL," +
+                "salt VARCHAR(1024) NOT NULL," +
+                "readonly BOOLEAN DEFAULT false NOT NULL," +
+                "admin BOOLEAN DEFAULT false NOT NULL," +
+                "map VARCHAR(1024) DEFAULT 'osm' NOT NULL," +
+                "language VARCHAR(1024) DEFAULT 'en' NOT NULL," +
+                "distanceUnit VARCHAR(1024) DEFAULT 'km' NOT NULL," +
+                "speedUnit VARCHAR(1024) DEFAULT 'kmh' NOT NULL," +
+                "latitude DOUBLE DEFAULT 0 NOT NULL," +
+                "longitude DOUBLE DEFAULT 0 NOT NULL," +
+                "zoom INT DEFAULT 0 NOT NULL);" +
+
+                "CREATE TABLE device (" +
+                "id INT PRIMARY KEY AUTO_INCREMENT," +
+                "name VARCHAR(1024) NOT NULL," +
+                "uniqueId VARCHAR(1024) NOT NULL UNIQUE," +
+                "status VARCHAR(1024)," +
+                "lastUpdate TIMESTAMP," +
+                "positionId INT," +
+                "dataId INT);" +
+
+                "CREATE TABLE user_device (" +
+                "userId INT NOT NULL," +
+                "deviceId INT NOT NULL," +
+                "read BOOLEAN DEFAULT true NOT NULL," +
+                "write BOOLEAN DEFAULT true NOT NULL," +
+                "FOREIGN KEY (userId) REFERENCES user(id) ON DELETE CASCADE," +
+                "FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);" +
+
+                "CREATE INDEX user_device_user_id ON user_device(userId);" +
+
+                "CREATE TABLE position (" +
+                "id INT PRIMARY KEY AUTO_INCREMENT," +
+                "protocol VARCHAR(1024)," +
+                "deviceId INT NOT NULL," +
+                "serverTime TIMESTAMP NOT NULL," +
+                "deviceTime TIMESTAMP NOT NULL," +
+                "fixTime TIMESTAMP NOT NULL," +
+                "valid BOOLEAN NOT NULL," +
+                "latitude DOUBLE NOT NULL," +
+                "longitude DOUBLE NOT NULL," +
+                "altitude DOUBLE NOT NULL," +
+                "speed DOUBLE NOT NULL," +
+                "course DOUBLE NOT NULL," +
+                "address VARCHAR(1024)," +
+                "other VARCHAR(8192) NOT NULL," +
+                "FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);" +
+
+                "CREATE TABLE data (" +
+                "id INT PRIMARY KEY AUTO_INCREMENT," +
+                "protocol VARCHAR(1024)," +
+                "deviceId INT NOT NULL," +
+                "serverTime TIMESTAMP NOT NULL," +
+                "deviceTime TIMESTAMP NOT NULL," +
+                "other VARCHAR(8192) NOT NULL," +
+                "FOREIGN KEY (deviceId) REFERENCES device(id));" +
+
+                "ALTER TABLE device ADD " +
+                "FOREIGN KEY (positionId) REFERENCES position(id);" +
+
+                "ALTER TABLE device ADD " +
+                "FOREIGN KEY (dataId) REFERENCES data(id);" +
+
+                "CREATE TABLE server (" +
+                "id INT PRIMARY KEY AUTO_INCREMENT," +
+                "registration BOOLEAN NOT NULL," +
+                "latitude DOUBLE NOT NULL," +
+                "longitude DOUBLE NOT NULL," +
+                "zoom INT NOT NULL);" +
+
+                "CREATE TABLE traccar1 (" +
+                "id INT PRIMARY KEY AUTO_INCREMENT);").executeUpdate();
+        
+        addUser("admin", "admin", true);
     }
 
     public long login(String email, String password) throws SQLException {
@@ -346,137 +330,47 @@ public class DataManager {
         }
     }
 
-    public Collection<Map.Entry<Long, Long>> getPermissions() throws SQLException {
-
-        Connection connection = dataSource.getConnection();
-        try {
-            PreparedStatement statement = connection.prepareStatement(
-                    "SELECT user_id, device_id FROM user_device;");
-            try {
-                ResultSet resultSet = statement.executeQuery();
-
-                List<Map.Entry<Long, Long>> result = new LinkedList<Map.Entry<Long, Long>>();
-                while (resultSet.next()) {
-                    result.add(new AbstractMap.SimpleEntry<Long, Long>(
-                            resultSet.getLong(1), resultSet.getLong(2)));
-                }
-                
-                return result;
-            } finally {
-                statement.close();
-            }
-        } finally {
-            connection.close();
-        }
+    public Collection<Permission> getPermissions() throws SQLException {
+        return QueryBuilder.create(dataSource, 
+                "SELECT userId, deviceId FROM user_device;")
+                .executeQuery(new Permission());
     }
 
-    public JsonArray getDevices(long userId) throws SQLException {
-        Connection connection = dataSource.getConnection();
-        try {
-            PreparedStatement statement = connection.prepareStatement(
-                    "SELECT * FROM device WHERE id IN (" +
-                    "SELECT device_id FROM user_device WHERE user_id = ?);");
-            try {
-                statement.setLong(1, userId);
-                
-                ResultSet result = statement.executeQuery();
-                
-                List<Device> list = new LinkedList<Device>();
-                while (result.next()) {
-                    Device device = new Device();
-                    device.fromRecord(result);
-                    list.add(device);
-                }
-                
-                return ObjectConverter.arrayToJson(list);
-            } finally {
-                statement.close();
-            }
-        } finally {
-            connection.close();
-        }
+    public Collection<Device> getDevices(long userId) throws SQLException {
+        return QueryBuilder.create(dataSource, 
+                "SELECT * FROM device WHERE id IN (" +
+                "SELECT deviceId FROM user_device WHERE userId = :userId);")
+                .setLong("userId", userId)
+                .executeQuery(new Device());
     }
     
     public void addDevice(Device device) throws SQLException {
-
-        Connection connection = dataSource.getConnection();
-        try {
-            PreparedStatement statement = connection.prepareStatement(
-                    "INSERT INTO device (name, unique_id) VALUES (?, ?);",
-                    Statement.RETURN_GENERATED_KEYS);
-            try {
-                statement.setString(1, device.getName());
-                statement.setString(2, device.getUniqueId());
-                
-                statement.executeUpdate();
-                
-                ResultSet result = statement.getGeneratedKeys();
-                if (result.next()) {
-                    device.setId(result.getLong(1));
-                }
-            } finally {
-                statement.close();
-            }
-        } finally {
-            connection.close();
-        }
+        device.setId(QueryBuilder.create(dataSource,
+                "INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);")
+                .setObject(device)
+                .executeUpdate());
     }
     
     public void updateDevice(Device device) throws SQLException {
-
-        Connection connection = dataSource.getConnection();
-        try {
-            PreparedStatement statement = connection.prepareStatement(
-                    "UPDATE device SET name = ?, unique_id = ? WHERE id = ?;");
-            try {
-                statement.setString(1, device.getName());
-                statement.setString(2, device.getUniqueId());
-                statement.setLong(3, device.getId());
-                
-                statement.executeUpdate();
-            } finally {
-                statement.close();
-            }
-        } finally {
-            connection.close();
-        }
+        QueryBuilder.create(dataSource,
+                "UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;")
+                .setObject(device)
+                .executeUpdate();
     }
     
     public void removeDevice(Device device) throws SQLException {
-
-        Connection connection = dataSource.getConnection();
-        try {
-            PreparedStatement statement = connection.prepareStatement(
-                    "DELETE FROM device WHERE id = ?;");
-            try {
-                statement.setLong(1, device.getId());
-                
-                statement.executeUpdate();
-            } finally {
-                statement.close();
-            }
-        } finally {
-            connection.close();
-        }
+        QueryBuilder.create(dataSource,
+                "DELETE FROM device WHERE id = :id;")
+                .setObject(device)
+                .executeUpdate();
     }
     
     public void linkDevice(long userId, long deviceId) throws SQLException {
-
-        Connection connection = dataSource.getConnection();
-        try {
-            PreparedStatement statement = connection.prepareStatement(
-                    "INSERT INTO user_device (user_id, device_id) VALUES (?, ?);");
-            try {
-                statement.setLong(1, userId);
-                statement.setLong(2, deviceId);
-                
-                statement.executeUpdate();
-            } finally {
-                statement.close();
-            }
-        } finally {
-            connection.close();
-        }
+        QueryBuilder.create(dataSource,
+                "INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);")
+                .setLong("userId", userId)
+                .setLong("deviceId", deviceId)
+                .executeUpdate();
     }
 
 }
diff --git a/src/org/traccar/database/ObjectConverter.java b/src/org/traccar/database/ObjectConverter.java
index 8d6e73123..7852c88f4 100644
--- a/src/org/traccar/database/ObjectConverter.java
+++ b/src/org/traccar/database/ObjectConverter.java
@@ -45,10 +45,6 @@ public class ObjectConverter {
         return array.build();
     }
     
-    private static String getColumnName(String key) {
-        return key.replaceAll("([A-Z])", "_$1").toLowerCase();
-    }
-    
     private static boolean hasColumn(ResultSet resultSet, String columnName) throws SQLException {
         ResultSetMetaData metaData = resultSet.getMetaData();
         for (int i = 1; i <= metaData.getColumnCount(); i++) {
@@ -67,9 +63,8 @@ public class ObjectConverter {
     }
     
     public static String getString(ResultSet record, String key) throws SQLException {
-        String column = getColumnName(key);
-        if (hasColumn(record, column)) {
-            return record.getString(column);
+        if (hasColumn(record, key)) {
+            return record.getString(key);
         }
         return null;
     }
@@ -88,9 +83,8 @@ public class ObjectConverter {
     }
     
     public static long getLong(ResultSet record, String key) throws SQLException {
-        String column = getColumnName(key);
-        if (hasColumn(record, column)) {
-            return record.getLong(column);
+        if (hasColumn(record, key)) {
+            return record.getLong(key);
         }
         return 0;
     }
@@ -111,9 +105,8 @@ public class ObjectConverter {
     }
     
     public static Date getDate(ResultSet record, String key) throws SQLException {
-        String column = getColumnName(key);
-        if (hasColumn(record, column)) {
-            return record.getDate(column);
+        if (hasColumn(record, key)) {
+            return record.getDate(key);
         }
         return null;
     }
diff --git a/src/org/traccar/database/PermissionsManager.java b/src/org/traccar/database/PermissionsManager.java
index 16ddd336d..e889afb06 100644
--- a/src/org/traccar/database/PermissionsManager.java
+++ b/src/org/traccar/database/PermissionsManager.java
@@ -23,6 +23,7 @@ import java.util.Map;
 import java.util.Set;
 import org.traccar.Context;
 import org.traccar.helper.Log;
+import org.traccar.model.Permission;
 
 public class PermissionsManager {
     
@@ -42,8 +43,8 @@ public class PermissionsManager {
     public final void refresh() {
         permissions.clear();
         try {
-            for (Map.Entry<Long, Long> entry : Context.getDataManager().getPermissions()) {
-                getNotNull(entry.getKey()).add(entry.getValue());
+            for (Permission permission : Context.getDataManager().getPermissions()) {
+                getNotNull(permission.getUserId()).add(permission.getDeviceId());
             }
         } catch (SQLException error) {
             Log.warning(error);
diff --git a/src/org/traccar/database/QueryBuilder.java b/src/org/traccar/database/QueryBuilder.java
new file mode 100644
index 000000000..29a737f20
--- /dev/null
+++ b/src/org/traccar/database/QueryBuilder.java
@@ -0,0 +1,310 @@
+/*
+ * Copyright 2015 Anton Tananaev (anton.tananaev@gmail.com)
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.traccar.database;
+
+import java.beans.Introspector;
+import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Method;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Timestamp;
+import java.sql.Types;
+import java.util.Collection;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.LinkedList;
+import java.util.List;
+import java.util.Map;
+import javax.sql.DataSource;
+import org.traccar.model.Factory;
+
+public class QueryBuilder {
+    
+    private final Map<String, List<Integer>> indexMap;
+    private final PreparedStatement statement;
+    
+    private QueryBuilder(DataSource dataSource, String query) throws SQLException {
+        indexMap = new HashMap<String, List<Integer>>();
+        statement = dataSource.getConnection().prepareStatement(
+                parse(query, indexMap), Statement.RETURN_GENERATED_KEYS);
+    }
+    
+    private static String parse(String query, Map<String, List<Integer>> paramMap) {
+
+        int length = query.length();
+        StringBuilder parsedQuery = new StringBuilder(length);
+        boolean inSingleQuote = false;
+        boolean inDoubleQuote = false;
+        int index = 1;
+
+        for (int i = 0; i < length; i++) {
+
+            char c = query.charAt(i);
+
+            // String end
+            if (inSingleQuote) {
+                if (c == '\'') {
+                    inSingleQuote = false;
+                }
+            } else if (inDoubleQuote) {
+                if (c == '"') {
+                    inDoubleQuote = false;
+                }
+            } else {
+
+                // String begin
+                if (c == '\'') {
+                    inSingleQuote = true;
+                } else if (c == '"') {
+                    inDoubleQuote = true;
+                } else if (c == ':' && i + 1 < length
+                        && Character.isJavaIdentifierStart(query.charAt(i + 1))) {
+
+                    // Identifier name
+                    int j = i + 2;
+                    while (j < length && Character.isJavaIdentifierPart(query.charAt(j))) {
+                        j++;
+                    }
+
+                    String name = query.substring(i + 1, j);
+                    c = '?';
+                    i += name.length();
+                    name = name.toLowerCase();
+
+                    // Add to list
+                    List<Integer> indexList = paramMap.get(name);
+                    if (indexList == null) {
+                        indexList = new LinkedList<Integer>();
+                        paramMap.put(name, indexList);
+                    }
+                    indexList.add(index);
+
+                    index++;
+                }
+            }
+
+            parsedQuery.append(c);
+        }
+
+        return parsedQuery.toString();
+    }
+    
+    public static QueryBuilder create(DataSource dataSource, String query) throws SQLException {
+        return new QueryBuilder(dataSource, query);
+    }
+    
+    private List<Integer> indexes(String name) {
+        name = name.toLowerCase();
+        List<Integer> result = indexMap.get(name);
+        if (result == null) {
+            result = new LinkedList<Integer>();
+        }
+        return result;
+    }
+    
+    public QueryBuilder setBoolean(String name, boolean value) throws SQLException {
+        for (int i : indexes(name)) {
+            statement.setBoolean(i, value);
+        }
+        return this;
+    }
+    
+    public QueryBuilder setInteger(String name, int value) throws SQLException {
+        for (int i : indexes(name)) {
+            statement.setInt(i, value);
+        }
+        return this;
+    }
+    
+    public QueryBuilder setLong(String name, long value) throws SQLException {
+        for (int i : indexes(name)) {
+            statement.setLong(i, value);
+        }
+        return this;
+    }
+    
+    public QueryBuilder setDouble(String name, double value) throws SQLException {
+        for (int i : indexes(name)) {
+            statement.setDouble(i, value);
+        }
+        return this;
+    }
+    
+    public QueryBuilder setString(String name, String value) throws SQLException {
+        for (int i : indexes(name)) {
+            if (value == null) {
+                statement.setNull(i, Types.VARCHAR);
+            } else {
+                statement.setString(i, value);
+            }
+        }
+        return this;
+    }
+    
+    public QueryBuilder setDate(String name, Date value) throws SQLException {
+        for (int i : indexes(name)) {
+            if (value == null) {
+                statement.setNull(i, Types.TIMESTAMP);
+            } else {
+                statement.setTimestamp(i, new Timestamp(value.getTime()));
+            }
+        }
+        return this;
+    }
+    
+    public QueryBuilder setObject(Object object) throws SQLException {
+        
+        Method[] methods = object.getClass().getMethods();
+        
+        for (Method method : methods) {
+            if (method.getName().startsWith("get") && method.getParameterTypes().length == 0) {
+                String name = method.getName().substring(3);
+                try {
+                    if (method.getReturnType().equals(boolean.class)) {
+                        return setBoolean(name, (Boolean) method.invoke(object));
+                    } else if (method.getReturnType().equals(int.class)) {
+                        return setInteger(name, (Integer) method.invoke(object));
+                    } else if (method.getReturnType().equals(long.class)) {
+                        return setLong(name, (Long) method.invoke(object));
+                    } else if (method.getReturnType().equals(double.class)) {
+                        return setDouble(name, (Double) method.invoke(object));
+                    } else if (method.getReturnType().equals(String.class)) {
+                        return setString(name, (String) method.invoke(object));
+                    } else if (method.getReturnType().equals(Date.class)) {
+                        return setDate(name, (Date) method.invoke(object));
+                    }
+                } catch (IllegalAccessException error) {
+                } catch (InvocationTargetException error) {
+                }
+            }
+        }
+        
+        return this;
+    }
+    
+    private interface ResultSetProcessor<T> {
+        public void process(T object, ResultSet resultSet) throws SQLException;
+    }
+    
+    public <T extends Factory> Collection<T> executeQuery(T prototype) throws SQLException {
+        List<T> result = new LinkedList<T>();
+        
+        List<ResultSetProcessor<T>> processors = new LinkedList<ResultSetProcessor<T>>();
+        
+        Method[] methods = prototype.getClass().getMethods();
+        
+        for (final Method method : methods) {
+            if (method.getName().startsWith("set") && method.getParameterTypes().length == 1) {
+
+                final String name = method.getName().substring(3);
+                Class<?> parameterType = method.getParameterTypes()[0];
+
+                if (parameterType.equals(boolean.class)) {
+                    processors.add(new ResultSetProcessor<T>() {
+                        @Override
+                        public void process(T object, ResultSet resultSet) throws SQLException {
+                            try {
+                                method.invoke(object, resultSet.getBoolean(name));
+                            } catch (IllegalAccessException error) {
+                            } catch (InvocationTargetException error) {
+                            }
+                        }
+                    });
+                } else if (parameterType.equals(int.class)) {
+                    processors.add(new ResultSetProcessor<T>() {
+                        @Override
+                        public void process(T object, ResultSet resultSet) throws SQLException {
+                            try {
+                                method.invoke(object, resultSet.getInt(name));
+                            } catch (IllegalAccessException error) {
+                            } catch (InvocationTargetException error) {
+                            }
+                        }
+                    });
+                } else if (parameterType.equals(long.class)) {
+                    processors.add(new ResultSetProcessor<T>() {
+                        @Override
+                        public void process(T object, ResultSet resultSet) throws SQLException {
+                            try {
+                                method.invoke(object, resultSet.getLong(name));
+                            } catch (IllegalAccessException error) {
+                            } catch (InvocationTargetException error) {
+                            }
+                        }
+                    });
+                } else if (parameterType.equals(double.class)) {
+                    processors.add(new ResultSetProcessor<T>() {
+                        @Override
+                        public void process(T object, ResultSet resultSet) throws SQLException {
+                            try {
+                                method.invoke(object, resultSet.getDouble(name));
+                            } catch (IllegalAccessException error) {
+                            } catch (InvocationTargetException error) {
+                            }
+                        }
+                    });
+                } else if (parameterType.equals(String.class)) {
+                    processors.add(new ResultSetProcessor<T>() {
+                        @Override
+                        public void process(T object, ResultSet resultSet) throws SQLException {
+                            try {
+                                method.invoke(object, resultSet.getString(name));
+                            } catch (IllegalAccessException error) {
+                            } catch (InvocationTargetException error) {
+                            }
+                        }
+                    });
+                } else if (parameterType.equals(Date.class)) {
+                    processors.add(new ResultSetProcessor<T>() {
+                        @Override
+                        public void process(T object, ResultSet resultSet) throws SQLException {
+                            try {
+                                method.invoke(object, new Date(resultSet.getTimestamp(name).getTime()));
+                            } catch (IllegalAccessException error) {
+                            } catch (InvocationTargetException error) {
+                            }
+                        }
+                    });
+                }
+            }
+        }
+        
+        ResultSet resultSet = statement.executeQuery();
+
+        while (resultSet.next()) {
+            T object = (T) prototype.create();
+            for (ResultSetProcessor<T> processor : processors) {
+                processor.process(object, resultSet);
+            }
+            result.add(object);
+        }
+
+        return result;
+    }
+
+    public long executeUpdate() throws SQLException {
+        
+        statement.executeUpdate();
+        ResultSet resultSet = statement.getGeneratedKeys();
+        if (resultSet.next()) {
+            return resultSet.getLong(1);
+        }
+        return 0;
+    }
+    
+}
diff --git a/src/org/traccar/database/ResultSetConverter.java b/src/org/traccar/database/ResultSetConverter.java
deleted file mode 100644
index 2bac01393..000000000
--- a/src/org/traccar/database/ResultSetConverter.java
+++ /dev/null
@@ -1,80 +0,0 @@
-/*
- * Copyright 2015 Anton Tananaev (anton.tananaev@gmail.com)
- *
- * Licensed under the Apache License, Version 2.0 (the "License");
- * you may not use this file except in compliance with the License.
- * You may obtain a copy of the License at
- *
- *     http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-package org.traccar.database;
-
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
-import javax.json.Json;
-import javax.json.JsonArray;
-import javax.json.JsonArrayBuilder;
-import javax.json.JsonObjectBuilder;
-
-public class ResultSetConverter {
-
-    public static JsonArray convert(ResultSet rs) throws SQLException {
-
-        JsonArrayBuilder json = Json.createArrayBuilder();
-        ResultSetMetaData rsmd = rs.getMetaData();
-
-        while (rs.next()) {
-
-            int numColumns = rsmd.getColumnCount();
-            JsonObjectBuilder obj = Json.createObjectBuilder();
-
-            for (int i = 1; i <= numColumns; i++) {
-
-                String columnName = rsmd.getColumnName(i).toLowerCase();
-
-                switch (rsmd.getColumnType(i)) {
-                    case java.sql.Types.BIGINT:
-                        obj.add(columnName, rs.getInt(columnName));
-                        break;
-                    case java.sql.Types.BOOLEAN:
-                        obj.add(columnName, rs.getBoolean(columnName));
-                        break;
-                    case java.sql.Types.DOUBLE:
-                        obj.add(columnName, rs.getDouble(columnName));
-                        break;
-                    case java.sql.Types.FLOAT:
-                        obj.add(columnName, rs.getFloat(columnName));
-                        break;
-                    case java.sql.Types.INTEGER:
-                        obj.add(columnName, rs.getInt(columnName));
-                        break;
-                    case java.sql.Types.NVARCHAR:
-                        obj.add(columnName, rs.getNString(columnName));
-                        break;
-                    case java.sql.Types.VARCHAR:
-                        obj.add(columnName, rs.getString(columnName));
-                        break;
-                    case java.sql.Types.DATE:
-                        obj.add(columnName, ObjectConverter.dateFormat.format(rs.getDate(columnName)));
-                        break;
-                    case java.sql.Types.TIMESTAMP:
-                        obj.add(columnName, ObjectConverter.dateFormat.format(rs.getTimestamp(columnName)));
-                        break;
-                    default:
-                        break;
-                }
-            }
-
-            json.add(obj.build());
-        }
-
-        return json.build();
-    }
-}