From a46259ebcfeaeca70306886feb2bc424a1bdee52 Mon Sep 17 00:00:00 2001 From: Vitaly Litvak Date: Fri, 10 Oct 2014 00:00:10 +0400 Subject: Refactored NamedParameterStatement to manage connections and prepared statements all by itself --- src/org/traccar/database/DataManager.java | 119 ++++----- .../traccar/database/NamedParameterStatement.java | 274 +++++++++++++++------ 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 getDevices() throws SQLException { - - List deviceList = new LinkedList(); + private final NamedParameterStatement.ResultSetProcessor deviceResultSetProcessor = new NamedParameterStatement.ResultSetProcessor() { + @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 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(); } - - return deviceList; } /** @@ -149,61 +140,41 @@ public class DataManager { return devices.get(imei); } - public synchronized Long addPosition(Position position) throws SQLException { + private NamedParameterStatement.ResultSetProcessor generatedKeysResultSetProcessor = new NamedParameterStatement.ResultSetProcessor() { + @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 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>(); - parsedQuery = parse(query, indexMap); + private final DataSource dataSource; + + private final int autoGeneratedKeys; + + public interface ResultSetProcessor { + T processNextRow(ResultSet rs) throws SQLException; + } + + public class Params { + Map values; + Map 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(); + } + types.put(name, type); + } + + private Params setValue(String name, Object value) { + if (values == null) { + values = new HashMap(); + } + values.put(name, value); + return this; + } + + public List executeQuery(ResultSetProcessor processor) throws SQLException { + Connection conn = null; + PreparedStatement stmt = null; + ResultSet rs = null; + + List result = new LinkedList(); + + 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 List executeUpdate(ResultSetProcessor 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 result = new LinkedList(); + 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 entry : values.entrySet()) { + String name = entry.getKey(); + Object value = entry.getValue(); + int type = types.get(name); + + List 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>(); + this.parsedQuery = parse(query, indexMap); + this.dataSource = dataSource; + this.autoGeneratedKeys = autoGeneratedKeys; } static String parse(String query, Map> 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 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 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 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 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 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 indexList = indexMap.get(name); - if (indexList != null) for (Integer index: indexList) { - if (value != null) { - statement.setString(index, value); - } else { - statement.setNull(index, Types.VARCHAR); - } - } - } - } -- cgit v1.2.3