From 6ac6520eb0c96dcd690a5bd777a275673c7cd6e3 Mon Sep 17 00:00:00 2001 From: Anton Tananaev Date: Sun, 13 Feb 2022 08:54:22 -0800 Subject: Create storage package --- .../java/org/traccar/database/DataManager.java | 3 + .../java/org/traccar/database/QueryBuilder.java | 485 --------------------- .../java/org/traccar/database/QueryExtended.java | 27 -- .../java/org/traccar/database/QueryIgnore.java | 26 -- src/main/java/org/traccar/model/Calendar.java | 2 +- src/main/java/org/traccar/model/Command.java | 2 +- src/main/java/org/traccar/model/Device.java | 4 +- src/main/java/org/traccar/model/Geofence.java | 2 +- src/main/java/org/traccar/model/Notification.java | 2 +- src/main/java/org/traccar/model/Position.java | 2 +- src/main/java/org/traccar/model/Server.java | 2 +- src/main/java/org/traccar/model/User.java | 4 +- .../java/org/traccar/storage/QueryBuilder.java | 485 +++++++++++++++++++++ .../java/org/traccar/storage/QueryExtended.java | 27 ++ src/main/java/org/traccar/storage/QueryIgnore.java | 26 ++ 15 files changed, 551 insertions(+), 548 deletions(-) delete mode 100644 src/main/java/org/traccar/database/QueryBuilder.java delete mode 100644 src/main/java/org/traccar/database/QueryExtended.java delete mode 100644 src/main/java/org/traccar/database/QueryIgnore.java create mode 100644 src/main/java/org/traccar/storage/QueryBuilder.java create mode 100644 src/main/java/org/traccar/storage/QueryExtended.java create mode 100644 src/main/java/org/traccar/storage/QueryIgnore.java (limited to 'src/main/java') diff --git a/src/main/java/org/traccar/database/DataManager.java b/src/main/java/org/traccar/database/DataManager.java index ebd0dcade..199167419 100644 --- a/src/main/java/org/traccar/database/DataManager.java +++ b/src/main/java/org/traccar/database/DataManager.java @@ -47,6 +47,9 @@ import org.traccar.model.Position; import org.traccar.model.Server; import org.traccar.model.Statistics; import org.traccar.model.User; +import org.traccar.storage.QueryBuilder; +import org.traccar.storage.QueryExtended; +import org.traccar.storage.QueryIgnore; import javax.sql.DataSource; import java.beans.Introspector; diff --git a/src/main/java/org/traccar/database/QueryBuilder.java b/src/main/java/org/traccar/database/QueryBuilder.java deleted file mode 100644 index 396cbf562..000000000 --- a/src/main/java/org/traccar/database/QueryBuilder.java +++ /dev/null @@ -1,485 +0,0 @@ -/* - * Copyright 2015 - 2020 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.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> 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> 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 indexList = paramMap.computeIfAbsent(name, k -> new LinkedList<>()); - 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 indexes(String name) { - name = name.toLowerCase(); - List 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 { - setString(name, Context.getObjectMapper().writeValueAsString(method.invoke(object))); - } - } catch (IllegalAccessException | InvocationTargetException | JsonProcessingException error) { - LOGGER.warn("Get property error", error); - } - } - } - - return this; - } - - private interface ResultSetProcessor { - void process(T object, ResultSet resultSet) throws SQLException; - } - - public T executeQuerySingle(Class clazz) throws SQLException { - Collection result = executeQuery(clazz); - if (!result.isEmpty()) { - return result.iterator().next(); - } else { - return null; - } - } - - private void addProcessors( - List> processors, - final Class parameterType, final Method method, final String name) { - - if (parameterType.equals(boolean.class)) { - processors.add((object, resultSet) -> { - 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((object, resultSet) -> { - 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((object, resultSet) -> { - 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((object, resultSet) -> { - 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((object, resultSet) -> { - 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((object, resultSet) -> { - 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((object, resultSet) -> { - try { - method.invoke(object, resultSet.getBytes(name)); - } catch (IllegalAccessException | InvocationTargetException error) { - LOGGER.warn("Set property error", error); - } - }); - } else { - processors.add((object, resultSet) -> { - 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 Collection executeQuery(Class clazz) throws SQLException { - List result = new LinkedList<>(); - - if (query != null) { - - try { - - try (ResultSet resultSet = statement.executeQuery()) { - - ResultSetMetaData resultMetaData = resultSet.getMetaData(); - - List> 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.getDeclaredConstructor().newInstance(); - for (ResultSetProcessor processor : processors) { - processor.process(object, resultSet); - } - result.add(object); - } catch (ReflectiveOperationException 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 executePermissionsQuery() throws SQLException, ClassNotFoundException { - List result = new LinkedList<>(); - if (query != null) { - try { - try (ResultSet resultSet = statement.executeQuery()) { - ResultSetMetaData resultMetaData = resultSet.getMetaData(); - while (resultSet.next()) { - LinkedHashMap 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; - } - -} diff --git a/src/main/java/org/traccar/database/QueryExtended.java b/src/main/java/org/traccar/database/QueryExtended.java deleted file mode 100644 index 07bc2c211..000000000 --- a/src/main/java/org/traccar/database/QueryExtended.java +++ /dev/null @@ -1,27 +0,0 @@ -/* - * Copyright 2017 Anton Tananaev (anton@traccar.org) - * Copyright 2017 Andrey Kunitsyn (andrey@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 java.lang.annotation.ElementType; -import java.lang.annotation.Retention; -import java.lang.annotation.RetentionPolicy; -import java.lang.annotation.Target; - -@Target(ElementType.METHOD) -@Retention(RetentionPolicy.RUNTIME) -public @interface QueryExtended { -} diff --git a/src/main/java/org/traccar/database/QueryIgnore.java b/src/main/java/org/traccar/database/QueryIgnore.java deleted file mode 100644 index ac835cf2f..000000000 --- a/src/main/java/org/traccar/database/QueryIgnore.java +++ /dev/null @@ -1,26 +0,0 @@ -/* - * Copyright 2017 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 java.lang.annotation.ElementType; -import java.lang.annotation.Retention; -import java.lang.annotation.RetentionPolicy; -import java.lang.annotation.Target; - -@Target(ElementType.METHOD) -@Retention(RetentionPolicy.RUNTIME) -public @interface QueryIgnore { -} diff --git a/src/main/java/org/traccar/model/Calendar.java b/src/main/java/org/traccar/model/Calendar.java index 0b45ca6c8..af6364626 100644 --- a/src/main/java/org/traccar/model/Calendar.java +++ b/src/main/java/org/traccar/model/Calendar.java @@ -24,7 +24,7 @@ import net.fortuna.ical4j.filter.predicate.PeriodRule; import net.fortuna.ical4j.model.DateTime; import net.fortuna.ical4j.model.Period; import net.fortuna.ical4j.model.component.CalendarComponent; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryIgnore; import java.io.ByteArrayInputStream; import java.io.IOException; diff --git a/src/main/java/org/traccar/model/Command.java b/src/main/java/org/traccar/model/Command.java index 99930d1e6..71bc232e9 100644 --- a/src/main/java/org/traccar/model/Command.java +++ b/src/main/java/org/traccar/model/Command.java @@ -15,7 +15,7 @@ */ package org.traccar.model; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryIgnore; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; diff --git a/src/main/java/org/traccar/model/Device.java b/src/main/java/org/traccar/model/Device.java index 0c9be932d..46de8003d 100644 --- a/src/main/java/org/traccar/model/Device.java +++ b/src/main/java/org/traccar/model/Device.java @@ -18,8 +18,8 @@ package org.traccar.model; import java.util.Date; import java.util.List; -import org.traccar.database.QueryExtended; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryExtended; +import org.traccar.storage.QueryIgnore; public class Device extends GroupedModel { diff --git a/src/main/java/org/traccar/model/Geofence.java b/src/main/java/org/traccar/model/Geofence.java index 85f392f99..a451da9f5 100644 --- a/src/main/java/org/traccar/model/Geofence.java +++ b/src/main/java/org/traccar/model/Geofence.java @@ -19,7 +19,7 @@ import java.text.ParseException; import org.traccar.Context; import org.traccar.config.Keys; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryIgnore; import org.traccar.geofence.GeofenceCircle; import org.traccar.geofence.GeofenceGeometry; import org.traccar.geofence.GeofencePolygon; diff --git a/src/main/java/org/traccar/model/Notification.java b/src/main/java/org/traccar/model/Notification.java index f1983a03a..01ca2711c 100644 --- a/src/main/java/org/traccar/model/Notification.java +++ b/src/main/java/org/traccar/model/Notification.java @@ -18,7 +18,7 @@ package org.traccar.model; import java.util.HashSet; import java.util.Set; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryIgnore; import com.fasterxml.jackson.annotation.JsonIgnore; diff --git a/src/main/java/org/traccar/model/Position.java b/src/main/java/org/traccar/model/Position.java index 09d25e832..bbea58901 100644 --- a/src/main/java/org/traccar/model/Position.java +++ b/src/main/java/org/traccar/model/Position.java @@ -17,7 +17,7 @@ package org.traccar.model; import java.util.Date; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryIgnore; public class Position extends Message { diff --git a/src/main/java/org/traccar/model/Server.java b/src/main/java/org/traccar/model/Server.java index 03d087cac..fdb071a18 100644 --- a/src/main/java/org/traccar/model/Server.java +++ b/src/main/java/org/traccar/model/Server.java @@ -17,7 +17,7 @@ package org.traccar.model; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import org.traccar.Context; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryIgnore; @JsonIgnoreProperties(ignoreUnknown = true) public class Server extends ExtendedModel { diff --git a/src/main/java/org/traccar/model/User.java b/src/main/java/org/traccar/model/User.java index 359bdc2c2..447cf691c 100644 --- a/src/main/java/org/traccar/model/User.java +++ b/src/main/java/org/traccar/model/User.java @@ -17,8 +17,8 @@ package org.traccar.model; import com.fasterxml.jackson.annotation.JsonIgnore; -import org.traccar.database.QueryExtended; -import org.traccar.database.QueryIgnore; +import org.traccar.storage.QueryExtended; +import org.traccar.storage.QueryIgnore; import org.traccar.helper.Hashing; import java.util.Date; diff --git a/src/main/java/org/traccar/storage/QueryBuilder.java b/src/main/java/org/traccar/storage/QueryBuilder.java new file mode 100644 index 000000000..c7800f19b --- /dev/null +++ b/src/main/java/org/traccar/storage/QueryBuilder.java @@ -0,0 +1,485 @@ +/* + * Copyright 2015 - 2020 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.storage; + +import com.fasterxml.jackson.core.JsonProcessingException; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; +import org.traccar.Context; +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> 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> 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 indexList = paramMap.computeIfAbsent(name, k -> new LinkedList<>()); + 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 indexes(String name) { + name = name.toLowerCase(); + List 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 { + setString(name, Context.getObjectMapper().writeValueAsString(method.invoke(object))); + } + } catch (IllegalAccessException | InvocationTargetException | JsonProcessingException error) { + LOGGER.warn("Get property error", error); + } + } + } + + return this; + } + + private interface ResultSetProcessor { + void process(T object, ResultSet resultSet) throws SQLException; + } + + public T executeQuerySingle(Class clazz) throws SQLException { + Collection result = executeQuery(clazz); + if (!result.isEmpty()) { + return result.iterator().next(); + } else { + return null; + } + } + + private void addProcessors( + List> processors, + final Class parameterType, final Method method, final String name) { + + if (parameterType.equals(boolean.class)) { + processors.add((object, resultSet) -> { + 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((object, resultSet) -> { + 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((object, resultSet) -> { + 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((object, resultSet) -> { + 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((object, resultSet) -> { + 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((object, resultSet) -> { + 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((object, resultSet) -> { + try { + method.invoke(object, resultSet.getBytes(name)); + } catch (IllegalAccessException | InvocationTargetException error) { + LOGGER.warn("Set property error", error); + } + }); + } else { + processors.add((object, resultSet) -> { + 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 Collection executeQuery(Class clazz) throws SQLException { + List result = new LinkedList<>(); + + if (query != null) { + + try { + + try (ResultSet resultSet = statement.executeQuery()) { + + ResultSetMetaData resultMetaData = resultSet.getMetaData(); + + List> 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.getDeclaredConstructor().newInstance(); + for (ResultSetProcessor processor : processors) { + processor.process(object, resultSet); + } + result.add(object); + } catch (ReflectiveOperationException 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 executePermissionsQuery() throws SQLException, ClassNotFoundException { + List result = new LinkedList<>(); + if (query != null) { + try { + try (ResultSet resultSet = statement.executeQuery()) { + ResultSetMetaData resultMetaData = resultSet.getMetaData(); + while (resultSet.next()) { + LinkedHashMap 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; + } + +} diff --git a/src/main/java/org/traccar/storage/QueryExtended.java b/src/main/java/org/traccar/storage/QueryExtended.java new file mode 100644 index 000000000..3796f1f40 --- /dev/null +++ b/src/main/java/org/traccar/storage/QueryExtended.java @@ -0,0 +1,27 @@ +/* + * Copyright 2017 Anton Tananaev (anton@traccar.org) + * Copyright 2017 Andrey Kunitsyn (andrey@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.storage; + +import java.lang.annotation.ElementType; +import java.lang.annotation.Retention; +import java.lang.annotation.RetentionPolicy; +import java.lang.annotation.Target; + +@Target(ElementType.METHOD) +@Retention(RetentionPolicy.RUNTIME) +public @interface QueryExtended { +} diff --git a/src/main/java/org/traccar/storage/QueryIgnore.java b/src/main/java/org/traccar/storage/QueryIgnore.java new file mode 100644 index 000000000..553d4b9fc --- /dev/null +++ b/src/main/java/org/traccar/storage/QueryIgnore.java @@ -0,0 +1,26 @@ +/* + * Copyright 2017 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.storage; + +import java.lang.annotation.ElementType; +import java.lang.annotation.Retention; +import java.lang.annotation.RetentionPolicy; +import java.lang.annotation.Target; + +@Target(ElementType.METHOD) +@Retention(RetentionPolicy.RUNTIME) +public @interface QueryIgnore { +} -- cgit v1.2.3