aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/org/traccar/database/DataManager.java119
-rw-r--r--src/org/traccar/database/NamedParameterStatement.java274
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);
- }
- }
- }
-
}