aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/org/traccar/database/QueryBuilder.java
diff options
context:
space:
mode:
Diffstat (limited to 'src/main/java/org/traccar/database/QueryBuilder.java')
-rw-r--r--src/main/java/org/traccar/database/QueryBuilder.java519
1 files changed, 519 insertions, 0 deletions
diff --git a/src/main/java/org/traccar/database/QueryBuilder.java b/src/main/java/org/traccar/database/QueryBuilder.java
new file mode 100644
index 000000000..5528b2320
--- /dev/null
+++ b/src/main/java/org/traccar/database/QueryBuilder.java
@@ -0,0 +1,519 @@
+/*
+ * Copyright 2015 Anton Tananaev (anton@traccar.org)
+ *
+ * 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 com.fasterxml.jackson.core.JsonProcessingException;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.traccar.Context;
+import org.traccar.model.MiscFormatter;
+import org.traccar.model.Permission;
+
+import javax.sql.DataSource;
+import java.io.IOException;
+import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Method;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+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.LinkedHashMap;
+import java.util.LinkedList;
+import java.util.List;
+import java.util.Map;
+
+public final class QueryBuilder {
+
+ private static final Logger LOGGER = LoggerFactory.getLogger(QueryBuilder.class);
+
+ private final Map<String, List<Integer>> indexMap = new HashMap<>();
+ private Connection connection;
+ private PreparedStatement statement;
+ private final String query;
+ private final boolean returnGeneratedKeys;
+
+ private QueryBuilder(DataSource dataSource, String query, boolean returnGeneratedKeys) throws SQLException {
+ this.query = query;
+ this.returnGeneratedKeys = returnGeneratedKeys;
+ if (query != null) {
+ connection = dataSource.getConnection();
+ String parsedQuery = parse(query.trim(), indexMap);
+ try {
+ if (returnGeneratedKeys) {
+ statement = connection.prepareStatement(parsedQuery, Statement.RETURN_GENERATED_KEYS);
+ } else {
+ statement = connection.prepareStatement(parsedQuery);
+ }
+ } catch (SQLException error) {
+ connection.close();
+ throw error;
+ }
+ }
+ }
+
+ 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<>();
+ 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, false);
+ }
+
+ public static QueryBuilder create(
+ DataSource dataSource, String query, boolean returnGeneratedKeys) throws SQLException {
+ return new QueryBuilder(dataSource, query, returnGeneratedKeys);
+ }
+
+ private List<Integer> indexes(String name) {
+ name = name.toLowerCase();
+ List<Integer> result = indexMap.get(name);
+ if (result == null) {
+ result = new LinkedList<>();
+ }
+ return result;
+ }
+
+ public QueryBuilder setBoolean(String name, boolean value) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ statement.setBoolean(i, value);
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ return this;
+ }
+
+ public QueryBuilder setInteger(String name, int value) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ statement.setInt(i, value);
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ return this;
+ }
+
+ public QueryBuilder setLong(String name, long value) throws SQLException {
+ return setLong(name, value, false);
+ }
+
+ public QueryBuilder setLong(String name, long value, boolean nullIfZero) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ if (value == 0 && nullIfZero) {
+ statement.setNull(i, Types.INTEGER);
+ } else {
+ statement.setLong(i, value);
+ }
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ return this;
+ }
+
+ public QueryBuilder setDouble(String name, double value) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ statement.setDouble(i, value);
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ return this;
+ }
+
+ public QueryBuilder setString(String name, String value) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ if (value == null) {
+ statement.setNull(i, Types.VARCHAR);
+ } else {
+ statement.setString(i, value);
+ }
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ return this;
+ }
+
+ public QueryBuilder setDate(String name, Date value) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ if (value == null) {
+ statement.setNull(i, Types.TIMESTAMP);
+ } else {
+ statement.setTimestamp(i, new Timestamp(value.getTime()));
+ }
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ return this;
+ }
+
+ public QueryBuilder setBlob(String name, byte[] value) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ if (value == null) {
+ statement.setNull(i, Types.BLOB);
+ } else {
+ statement.setBytes(i, value);
+ }
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ 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
+ && !method.isAnnotationPresent(QueryIgnore.class)) {
+ String name = method.getName().substring(3);
+ try {
+ if (method.getReturnType().equals(boolean.class)) {
+ setBoolean(name, (Boolean) method.invoke(object));
+ } else if (method.getReturnType().equals(int.class)) {
+ setInteger(name, (Integer) method.invoke(object));
+ } else if (method.getReturnType().equals(long.class)) {
+ setLong(name, (Long) method.invoke(object), name.endsWith("Id"));
+ } else if (method.getReturnType().equals(double.class)) {
+ setDouble(name, (Double) method.invoke(object));
+ } else if (method.getReturnType().equals(String.class)) {
+ setString(name, (String) method.invoke(object));
+ } else if (method.getReturnType().equals(Date.class)) {
+ setDate(name, (Date) method.invoke(object));
+ } else if (method.getReturnType().equals(byte[].class)) {
+ setBlob(name, (byte[]) method.invoke(object));
+ } else {
+ if (method.getReturnType().equals(Map.class)
+ && Context.getConfig().getBoolean("database.xml")) {
+ setString(name, MiscFormatter.toXmlString((Map) method.invoke(object)));
+ } else {
+ setString(name, Context.getObjectMapper().writeValueAsString(method.invoke(object)));
+ }
+ }
+ } catch (IllegalAccessException | InvocationTargetException | JsonProcessingException error) {
+ LOGGER.warn("Get property error", error);
+ }
+ }
+ }
+
+ return this;
+ }
+
+ private interface ResultSetProcessor<T> {
+ void process(T object, ResultSet resultSet) throws SQLException;
+ }
+
+ public <T> T executeQuerySingle(Class<T> clazz) throws SQLException {
+ Collection<T> result = executeQuery(clazz);
+ if (!result.isEmpty()) {
+ return result.iterator().next();
+ } else {
+ return null;
+ }
+ }
+
+ private <T> void addProcessors(
+ List<ResultSetProcessor<T>> processors,
+ final Class<?> parameterType, final Method method, final String name) {
+
+ 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 | InvocationTargetException error) {
+ LOGGER.warn("Set property error", 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 | InvocationTargetException error) {
+ LOGGER.warn("Set property error", 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 | InvocationTargetException error) {
+ LOGGER.warn("Set property error", 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 | InvocationTargetException error) {
+ LOGGER.warn("Set property error", 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 | InvocationTargetException error) {
+ LOGGER.warn("Set property error", error);
+ }
+ }
+ });
+ } else if (parameterType.equals(Date.class)) {
+ processors.add(new ResultSetProcessor<T>() {
+ @Override
+ public void process(T object, ResultSet resultSet) throws SQLException {
+ try {
+ Timestamp timestamp = resultSet.getTimestamp(name);
+ if (timestamp != null) {
+ method.invoke(object, new Date(timestamp.getTime()));
+ }
+ } catch (IllegalAccessException | InvocationTargetException error) {
+ LOGGER.warn("Set property error", error);
+ }
+ }
+ });
+ } else if (parameterType.equals(byte[].class)) {
+ processors.add(new ResultSetProcessor<T>() {
+ @Override
+ public void process(T object, ResultSet resultSet) throws SQLException {
+ try {
+ method.invoke(object, resultSet.getBytes(name));
+ } catch (IllegalAccessException | InvocationTargetException error) {
+ LOGGER.warn("Set property error", error);
+ }
+ }
+ });
+ } else {
+ processors.add(new ResultSetProcessor<T>() {
+ @Override
+ public void process(T object, ResultSet resultSet) throws SQLException {
+ String value = resultSet.getString(name);
+ if (value != null && !value.isEmpty()) {
+ try {
+ method.invoke(object, Context.getObjectMapper().readValue(value, parameterType));
+ } catch (InvocationTargetException | IllegalAccessException | IOException error) {
+ LOGGER.warn("Set property error", error);
+ }
+ }
+ }
+ });
+ }
+ }
+
+ public <T> Collection<T> executeQuery(Class<T> clazz) throws SQLException {
+ List<T> result = new LinkedList<>();
+
+ if (query != null) {
+
+ try {
+
+ try (ResultSet resultSet = statement.executeQuery()) {
+
+ ResultSetMetaData resultMetaData = resultSet.getMetaData();
+
+ List<ResultSetProcessor<T>> processors = new LinkedList<>();
+
+ Method[] methods = clazz.getMethods();
+
+ for (final Method method : methods) {
+ if (method.getName().startsWith("set") && method.getParameterTypes().length == 1
+ && !method.isAnnotationPresent(QueryIgnore.class)) {
+
+ final String name = method.getName().substring(3);
+
+ // Check if column exists
+ boolean column = false;
+ for (int i = 1; i <= resultMetaData.getColumnCount(); i++) {
+ if (name.equalsIgnoreCase(resultMetaData.getColumnLabel(i))) {
+ column = true;
+ break;
+ }
+ }
+ if (!column) {
+ continue;
+ }
+
+ addProcessors(processors, method.getParameterTypes()[0], method, name);
+ }
+ }
+
+ while (resultSet.next()) {
+ try {
+ T object = clazz.newInstance();
+ for (ResultSetProcessor<T> processor : processors) {
+ processor.process(object, resultSet);
+ }
+ result.add(object);
+ } catch (InstantiationException | IllegalAccessException e) {
+ throw new IllegalArgumentException();
+ }
+ }
+ }
+
+ } finally {
+ statement.close();
+ connection.close();
+ }
+ }
+
+ return result;
+ }
+
+ public long executeUpdate() throws SQLException {
+
+ if (query != null) {
+ try {
+ statement.execute();
+ if (returnGeneratedKeys) {
+ ResultSet resultSet = statement.getGeneratedKeys();
+ if (resultSet.next()) {
+ return resultSet.getLong(1);
+ }
+ }
+ } finally {
+ statement.close();
+ connection.close();
+ }
+ }
+ return 0;
+ }
+
+ public Collection<Permission> executePermissionsQuery() throws SQLException, ClassNotFoundException {
+ List<Permission> result = new LinkedList<>();
+ if (query != null) {
+ try {
+ try (ResultSet resultSet = statement.executeQuery()) {
+ ResultSetMetaData resultMetaData = resultSet.getMetaData();
+ while (resultSet.next()) {
+ LinkedHashMap<String, Long> map = new LinkedHashMap<>();
+ for (int i = 1; i <= resultMetaData.getColumnCount(); i++) {
+ String label = resultMetaData.getColumnLabel(i);
+ map.put(label, resultSet.getLong(label));
+ }
+ result.add(new Permission(map));
+ }
+ }
+ } finally {
+ statement.close();
+ connection.close();
+ }
+ }
+
+ return result;
+ }
+
+}