While Add Devices then facing some exception
in my traccar project when i add any device then that insert into my device table but showing an exception like
org.traccar.storage.StorageException: org.traccar.storage.StorageException: Failed to insert object, affected rows: 0
at org.traccar.storage.DatabaseStorage.addObject(DatabaseStorage.java:170)
at org.traccar.api.BaseObjectResource.add(BaseObjectResource.java:81)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:146)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:189)
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:176)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:93)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:478)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:400)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:81)
at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:274)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:248)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:244)
at org.glassfish.jersey.internal.Errors.process(Errors.java:292)
at org.glassfish.jersey.internal.Errors.process(Errors.java:274)
at org.glassfish.jersey.internal.Errors.process(Errors.java:244)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:266)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:253)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:696)
at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:397)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:349)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:358)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:312)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:205)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:89)
at org.eclipse.jetty.servlets.DoSFilter.doFilterChain(DoSFilter.java:462)
at org.eclipse.jetty.servlets.DoSFilter.doFilter(DoSFilter.java:317)
at org.eclipse.jetty.servlets.DoSFilter.doFilter(DoSFilter.java:282)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:82)
at org.traccar.web.OverrideFilter.doFilter(OverrideFilter.java:50)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:82)
at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:121)
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:133)
at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202)
at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1580)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1381)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1553)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1303)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:287)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)
at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: org.traccar.storage.StorageException: Failed to insert object, affected rows: 0
at org.traccar.storage.DatabaseStorage.addObject(DatabaseStorage.java:143)
... 70 more" and this is my script "CREATE TABLE TRACCAR_TEST.TC_POSITIONS
(
ID INTEGER DEFAULT "TRACCAR_TEST"."ISEQ$$_10278584".nextval NOT NULL,
PROTOCOL VARCHAR2(128 BYTE),
DEVICEID INTEGER NOT NULL,
SERVERTIME TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
DEVICETIME TIMESTAMP(6) NOT NULL,
FIXTIME TIMESTAMP(6) NOT NULL,
VALID NUMBER(1) NOT NULL,
LATITUDE FLOAT(126) NOT NULL,
LONGITUDE FLOAT(126) NOT NULL,
ALTITUDE FLOAT(126) NOT NULL,
SPEED FLOAT(126) NOT NULL,
COURSE FLOAT(126) NOT NULL,
ADDRESS VARCHAR2(512 BYTE),
ATTRIBUTES VARCHAR2(4000 BYTE),
ACCURACY FLOAT(126) DEFAULT 0 NOT NULL,
NETWORK VARCHAR2(4000 BYTE),
GEOFENCEIDS VARCHAR2(128 BYTE)
)" and please guide how to fix
result.append(" WHERE ");
result.append(conditionKey);
result.append(" = :");
result.append(conditionKey);
}
System.out.println("Permission query: " + result.toString()); // Debug log
return result.toString();
}
}
please see and fix thiss and "you see my DatabaseStorage.java full code
/*
* Copyright 2022 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.databind.ObjectMapper;
import org.traccar.config.Config;
import org.traccar.model.BaseModel;
import org.traccar.model.Device;
import org.traccar.model.Group;
import org.traccar.model.GroupedModel;
import org.traccar.model.Permission;
import org.traccar.storage.query.Columns;
import org.traccar.storage.query.Condition;
import org.traccar.storage.query.Order;
import org.traccar.storage.query.Request;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
public class DatabaseStorage extends Storage {
private final Config config;
private final DataSource dataSource;
private final ObjectMapper objectMapper;
private final String databaseType;
@Inject
public DatabaseStorage(Config config, DataSource dataSource, ObjectMapper objectMapper) {
this.config = config;
this.dataSource = dataSource;
this.objectMapper = objectMapper;
try {
databaseType = dataSource.getConnection().getMetaData().getDatabaseProductName();
System.out.println("Database type: Oracle"); // Debug log
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public <T> List<T> getObjects(Class<T> clazz, Request request) throws StorageException {
StringBuilder query = new StringBuilder("SELECT ");
if (request.getColumns() instanceof Columns.All) {
query.append('*');
} else {
query.append(formatColumns(request.getColumns().getColumns(clazz, "set"), c -> c));
}
query.append(" FROM ").append(getStorageName(clazz));
query.append(formatCondition(request.getCondition()));
query.append(formatOrder(request.getOrder()));
try {
System.out.println("Generated query: " + query.toString()); // Debug log
QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString());
for (Map.Entry<String, Object> variable : getConditionVariables(request.getCondition()).entrySet()) {
builder.setValue(variable.getKey(), variable.getValue());
}
return builder.executeQuery(clazz);
} catch (SQLException e) {
throw new StorageException("Error executing query: " + e.getMessage(), e);
}
}
@Override
public <T> long addObject(T entity, Request request) throws StorageException {
List<String> columns = request.getColumns().getColumns(entity.getClass(), "get");
StringBuilder query = new StringBuilder("INSERT INTO ");
query.append(getStorageName(entity.getClass()));
query.append("(");
query.append(formatColumns(columns, c -> c));
query.append(") VALUES (");
query.append(formatColumns(columns, c -> ':' + c));
query.append(")");
// Check for duplicate uniqueId if entity is a Device
if (entity instanceof Device device) {
String uniqueId = device.getUniqueId();
System.out.println("Checking uniqueId: [" + uniqueId + "]"); // Debug log
if (uniqueId == null || uniqueId.trim().isEmpty()) {
throw new StorageException("Device uniqueId cannot be null or empty");
}
String checkQuery = "SELECT COUNT(*) FROM tc_devices WHERE uniqueId = ?";
try {
System.out.println("Executing check query: " + checkQuery + " with uniqueId = [" + uniqueId + "]"); // Debug log
try (var connection = dataSource.getConnection();
var stmt = connection.prepareStatement(checkQuery)) {
stmt.setString(1, uniqueId);
var rs = stmt.executeQuery();
if (rs.next()) {
int count = rs.getInt(1);
System.out.println("Check query result: count = " + count); // Debug log
if (count > 0) {
throw new StorageException("Device with uniqueId [" + uniqueId + "] already exists");
}
} else {
System.out.println("Check query returned no results"); // Debug log
}
}
} catch (SQLException e) {
System.err.println("Error executing check query for uniqueId [" + uniqueId + "]: " + e.getMessage()); // Error log
throw new StorageException("Error checking for duplicate uniqueId", e);
}
}
try {
System.out.println("Executing insert query: " + query.toString()); // Debug log
QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString(), false);
// Log parameters
for (String column : columns) {
Object value = null;
try {
value = entity.getClass().getMethod("get" + column.substring(0, 1).toUpperCase() + column.substring(1))
.invoke(entity);
} catch (NoSuchMethodException | IllegalAccessException | java.lang.reflect.InvocationTargetException e) {
System.err.println("Error accessing getter for column " + column + ": " + e.getMessage()); // Error log
}
System.out.println("Column " + column + ": " + (value != null ? value.toString() : "NULL")); // Debug log
}
builder.setObject(entity, columns);
int rowsAffected = (int) builder.executeUpdate();
System.out.println("Insert query affected " + rowsAffected + " rows"); // Debug log
if (rowsAffected != 1) {
throw new StorageException("Failed to insert object, affected rows: " + rowsAffected);
}
// Retrieve ID for Device
if (entity instanceof Device device) {
String idQuery = "SELECT id FROM tc_devices WHERE uniqueId = ?";
try (var connection = dataSource.getConnection();
var stmt = connection.prepareStatement(idQuery)) {
stmt.setString(1, device.getUniqueId());
System.out.println("Executing ID query: " + idQuery + " with uniqueId = [" + device.getUniqueId() + "]"); // Debug log
var rs = stmt.executeQuery();
if (rs.next()) {
long id = rs.getLong("id");
System.out.println("Retrieved new device ID: " + id); // Debug log
return id;
} else {
throw new StorageException("Failed to retrieve ID for device with uniqueId [" + device.getUniqueId() + "]");
}
}
}
// For other entities, return a default value or fetch ID if needed
return rowsAffected > 0 ? 1 : 0; // Default for non-Device entities
} catch (SQLException e) {
System.err.println("Error executing insert query: " + e.getMessage()); // Error log
throw new StorageException(e);
} catch (Exception e) {
System.err.println("Error accessing entity fields: " + e.getMessage()); // Error log
throw new StorageException(e);
}
}
@Override
public <T> void updateObject(T entity, Request request) throws StorageException {
List<String> columns = request.getColumns().getColumns(entity.getClass(), "get");
StringBuilder query = new StringBuilder("UPDATE ");
query.append(getStorageName(entity.getClass()));
query.append(" SET ");
query.append(formatColumns(columns, c -> c + " = :" + c));
query.append(formatCondition(request.getCondition()));
try {
System.out.println("Executing update query: " + query.toString()); // Debug log
QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString());
builder.setObject(entity, columns);
for (Map.Entry<String, Object> variable : getConditionVariables(request.getCondition()).entrySet()) {
System.out.println("Setting update parameter " + variable.getKey() + ": " + variable.getValue()); // Debug log
builder.setValue(variable.getKey(), variable.getValue());
}
// Debug: Check number of rows in the table
if (entity.getClass().equals(Device.class)) {
try (var connection = dataSource.getConnection();
var stmt = connection.prepareStatement("SELECT COUNT(*) FROM tc_devices")) {
var rs = stmt.executeQuery();
if (rs.next()) {
System.out.println("Total rows in tc_devices: " + rs.getInt(1)); // Debug log
}
}
}
int rowsAffected =(int) builder.executeUpdate();
System.out.println("Update query affected " + rowsAffected + " rows"); // Debug log
if (rowsAffected == 0) {
System.out.println("Warning: No rows updated for query: " + query.toString()); // Warning log
// Optionally, you can return here if this is acceptable
}
} catch (SQLException e) {
System.err.println("Error executing update query: " + e.getMessage()); // Error log
throw new StorageException(e);
}
}
@Override
public void removeObject(Class<?> clazz, Request request) throws StorageException {
StringBuilder query = new StringBuilder("DELETE FROM ");
query.append(getStorageName(clazz));
query.append(formatCondition(request.getCondition()));
try {
System.out.println("Executing delete query: " + query.toString()); // Debug log
QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString());
for (Map.Entry<String, Object> variable : getConditionVariables(request.getCondition()).entrySet()) {
System.out.println("Setting delete parameter " + variable.getKey() + ": " + variable.getValue()); // Debug log
builder.setValue(variable.getKey(), variable.getValue());
}
int rowsAffected = (int) builder.executeUpdate();
System.out.println("Delete query affected " + rowsAffected + " rows"); // Debug log
} catch (SQLException e) {
System.err.println("Error executing delete query: " + e.getMessage()); // Error log
throw new StorageException(e);
}
}
@Override
public List<Permission> getPermissions(
Class<? extends BaseModel> ownerClass, long ownerId,
Class<? extends BaseModel> propertyClass, long propertyId) throws StorageException {
StringBuilder query = new StringBuilder("SELECT * FROM ");
query.append(Permission.getStorageName(ownerClass, propertyClass));
var conditions = new LinkedList<Condition>();
if (ownerId > 0) {
conditions.add(new Condition.Equals(Permission.getKey(ownerClass), ownerId));
}
if (propertyId > 0) {
conditions.add(new Condition.Equals(Permission.getKey(propertyClass), propertyId));
}
Condition combinedCondition = Condition.merge(conditions);
query.append(formatCondition(combinedCondition));
try {
System.out.println("Executing permissions query: " + query.toString()); // Debug log
QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString());
for (Map.Entry<String, Object> variable : getConditionVariables(combinedCondition).entrySet()) {
System.out.println("Setting permissions parameter " + variable.getKey() + ": " + variable.getValue()); // Debug log
builder.setValue(variable.getKey(), variable.getValue());
}
return builder.executePermissionsQuery();
} catch (SQLException e) {
System.err.println("Error executing permissions query: " + e.getMessage()); // Error log
throw new StorageException(e);
}
}
@Override
public void addPermission(Permission permission) throws StorageException {
StringBuilder query = new StringBuilder("INSERT INTO ");
query.append(permission.getStorageName());
query.append(" VALUES (");
query.append(permission.get().keySet().stream().map(key -> ':' + key).collect(Collectors.joining(", ")));
query.append(")");
// Extract userId and deviceId (or other key-value pairs)
Long userId = null;
Long deviceId = null;
for (var entry : permission.get().entrySet()) {
if (entry.getKey().equals("userId")) {
userId = entry.getValue();
} else if (entry.getKey().equals("deviceId")) {
deviceId = entry.getValue();
}
}
// Validate userId and deviceId exist
if (userId != null && deviceId != null) {
try (var connection = dataSource.getConnection()) {
// Check if user exists
String userCheckQuery = "SELECT COUNT(*) FROM tc_users WHERE id = ?";
try (var stmt = connection.prepareStatement(userCheckQuery)) {
stmt.setLong(1, userId);
var rs = stmt.executeQuery();
if (rs.next() && rs.getInt(1) == 0) {
throw new StorageException("User with ID " + userId + " does not exist");
}
}
// Check if device exists
String deviceCheckQuery = "SELECT COUNT(*) FROM tc_devices WHERE id = ?";
try (var stmt = connection.prepareStatement(deviceCheckQuery)) {
stmt.setLong(1, deviceId);
var rs = stmt.executeQuery();
if (rs.next() && rs.getInt(1) == 0) {
throw new StorageException("Device with ID " + deviceId + " does not exist");
}
}
// Check for duplicate permission
String permissionCheckQuery = "SELECT COUNT(*) FROM " + permission.getStorageName() + " WHERE userId = ? AND deviceId = ?";
try (var stmt = connection.prepareStatement(permissionCheckQuery)) {
stmt.setLong(1, userId);
stmt.setLong(2, deviceId);
var rs = stmt.executeQuery();
if (rs.next() && rs.getInt(1) > 0) {
System.out.println("Permission already exists for userId: " + userId + ", deviceId: " + deviceId);
return; // Skip insert if duplicate exists
}
}
} catch (SQLException e) {
System.err.println("Error checking user/device/permission existence: " + e.getMessage());
throw new StorageException("Error validating permission parameters", e);
}
} else {
throw new StorageException("Invalid permission parameters: userId or deviceId missing");
}
try {
System.out.println("Executing permission insert query: " + query.toString());
QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString(), false);
for (var entry : permission.get().entrySet()) {
System.out.println("Setting permission parameter " + entry.getKey() + ": " + entry.getValue());
builder.setLong(entry.getKey(), entry.getValue());
}
int rowsAffected =(int) builder.executeUpdate();
System.out.println("Permission insert affected " + rowsAffected + " rows");
if (rowsAffected != 1) {
System.err.println("Failed to insert permission for userId: " + userId + ", deviceId: " + deviceId + ", affected rows: " + rowsAffected);
throw new StorageException("Failed to insert permission, affected rows: " + rowsAffected);
}
} catch (SQLException e) {
System.err.println("Error executing permission insert query: " + e.getMessage());
throw new StorageException(e);
}
}
@Override
public void removePermission(Permission permission) throws StorageException {
StringBuilder query = new StringBuilder("DELETE FROM ");
query.append(permission.getStorageName());
query.append(" WHERE ");
query.append(permission
.get().keySet().stream().map(key -> key + " = :" + key).collect(Collectors.joining(" AND ")));
try {
System.out.println("Executing permission delete query: " + query.toString()); // Debug log
QueryBuilder builder = QueryBuilder.create(config, dataSource, objectMapper, query.toString(), false);
for (var entry : permission.get().entrySet()) {
System.out.println("Setting permission delete parameter " + entry.getKey() + ": " + entry.getValue()); // Debug log
builder.setLong(entry.getKey(), entry.getValue());
}
int rowsAffected =(int) builder.executeUpdate();
System.out.println("Permission delete affected " + rowsAffected + " rows"); // Debug log
} catch (SQLException e) {
System.err.println("Error executing permission delete query: " + e.getMessage()); // Error log
throw new StorageException(e);
}
}
private String getStorageName(Class<?> clazz) throws StorageException {
StorageName storageName = clazz.getAnnotation(StorageName.class);
if (storageName == null) {
throw new StorageException("StorageName annotation is missing");
}
return storageName.value();
}
private Map<String, Object> getConditionVariables(Condition genericCondition) {
Map<String, Object> results = new HashMap<>();
if (genericCondition instanceof Condition.Compare condition) {
if (condition.getValue() != null) {
results.put(condition.getVariable(), condition.getValue());
}
} else if (genericCondition instanceof Condition.Between condition) {
results.put(condition.getFromVariable(), condition.getFromValue());
results.put(condition.getToVariable(), condition.getToValue());
} else if (genericCondition instanceof Condition.Binary condition) {
results.putAll(getConditionVariables(condition.getFirst()));
results.putAll(getConditionVariables(condition.getSecond()));
} else if (genericCondition instanceof Condition.Permission condition) {
if (condition.getOwnerId() > 0) {
results.put(Permission.getKey(condition.getOwnerClass()), condition.getOwnerId());
} else {
results.put(Permission.getKey(condition.getPropertyClass()), condition.getPropertyId());
}
} else if (genericCondition instanceof Condition.LatestPositions condition) {
if (condition.getDeviceId() > 0) {
results.put("deviceId", condition.getDeviceId());
}
}
return results;
}
private String formatColumns(List<String> columns, Function<String, String> mapper) {
return columns.stream().map(mapper).collect(Collectors.joining(", "));
}
private String formatCondition(Condition genericCondition) throws StorageException {
return formatCondition(genericCondition, true);
}
private String formatCondition(Condition genericCondition, boolean appendWhere) throws StorageException {
StringBuilder result = new StringBuilder();
if (genericCondition != null) {
if (appendWhere) {
result.append(" WHERE ");
}
if (genericCondition instanceof Condition.Compare condition) {
result.append(condition.getColumn());
result.append(" ");
result.append(condition.getOperator());
result.append(" :");
result.append(condition.getVariable());
} else if (genericCondition instanceof Condition.Between condition) {
result.append(condition.getColumn());
result.append(" BETWEEN :");
result.append(condition.getFromVariable());
result.append(" AND :");
result.append(condition.getToVariable());
} else if (genericCondition instanceof Condition.Binary condition) {
result.append(formatCondition(condition.getFirst(), false));
result.append(" ");
result.append(condition.getOperator());
result.append(" ");
result.append(formatCondition(condition.getSecond(), false));
} else if (genericCondition instanceof Condition.Permission condition) {
result.append("id IN (");
result.append(formatPermissionQuery(condition));
result.append(")");
} else if (genericCondition instanceof Condition.LatestPositions condition) {
result.append("id IN (");
result.append("SELECT positionId FROM ");
result.append(getStorageName(Device.class));
if (condition.getDeviceId() > 0) {
result.append(" WHERE id = :deviceId");
}
result.append(")");
}
}
return result.toString();
}
private String formatOrder(Order order) {
StringBuilder result = new StringBuilder();
if (order != null) {
result.append(" ORDER BY ");
result.append(order.getColumn());
if (order.getDescending()) {
result.append(" DESC");
}
if (order.getLimit() > 0) {
if (databaseType.equals("Microsoft SQL Server")) {
result.append(" OFFSET 0 ROWS FETCH FIRST ");
result.append(order.getLimit());
result.append(" ROWS ONLY");
} else if (databaseType.toLowerCase().contains("oracle")) {
result.append(" FETCH FIRST ");
result.append(order.getLimit());
result.append(" ROWS ONLY");
} else {
result.append(" LIMIT ");
result.append(order.getLimit());
}
}
}
return result.toString();
}
private String formatPermissionQuery(Condition.Permission condition) throws StorageException {
StringBuilder result = new StringBuilder();
String outputKey;
String conditionKey;
if (condition.getOwnerId() > 0) {
outputKey = Permission.getKey(condition.getPropertyClass());
conditionKey = Permission.getKey(condition.getOwnerClass());
} else {
outputKey = Permission.getKey(condition.getOwnerClass());
conditionKey = Permission.getKey(condition.getPropertyClass());
}
String storageName = Permission.getStorageName(condition.getOwnerClass(), condition.getPropertyClass());
result.append("SELECT ");
result.append(storageName).append('.').append(outputKey);
result.append(" FROM ");
result.append(storageName);
result.append(" WHERE ");
result.append(conditionKey);
result.append(" = :");
result.append(conditionKey);
if (condition.getIncludeGroups()) {
boolean expandDevices;
String groupStorageName;
if (GroupedModel.class.isAssignableFrom(condition.getOwnerClass())) {
expandDevices = Device.class.isAssignableFrom(condition.getOwnerClass());
groupStorageName = Permission.getStorageName(Group.class, condition.getPropertyClass());
} else {
expandDevices = Device.class.isAssignableFrom(condition.getPropertyClass());
groupStorageName = Permission.getStorageName(condition.getOwnerClass(), Group.class);
}
result.append(" UNION ");
result.append("SELECT DISTINCT ");
if (!expandDevices) {
if (outputKey.equals("groupId")) {
result.append("all_groups.");
} else {
result.append(groupStorageName).append('.');
}
}
result.append(outputKey);
result.append(" FROM ");
result.append(groupStorageName);
result.append(" INNER JOIN (");
result.append("SELECT id AS parentId, id AS groupId FROM ");
result.append(getStorageName(Group.class));
result.append(" UNION ");
result.append("SELECT groupId AS parentId, id AS groupId FROM ");
result.append(getStorageName(Group.class));
result.append(" WHERE groupId IS NOT NULL");
result.append(" UNION ");
result.append("SELECT g2.groupId AS parentId, g1.id AS groupId FROM ");
result.append(getStorageName(Group.class));
result.append(" g2");
result.append(" INNER JOIN ");
result.append(getStorageName(Group.class));
result.append(" g1 ON g2.id = g1.groupId");
result.append(" WHERE g2.groupId IS NOT NULL");
result.append(") all_groups ON ");
result.append(groupStorageName);
result.append(".groupId = all_groups.parentId");
if (expandDevices) {
result.append(" INNER JOIN (");
result.append("SELECT groupId AS parentId, id AS deviceId FROM ");
result.append(getStorageName(Device.class));
result.append(" WHERE groupId IS NOT NULL");
result.append(") devices ON all_groups.groupId = devices.parentId");
}
result.append(" WHERE ");
result.append(conditionKey);
result.append(" = :");
result.append(conditionKey);
}
System.out.println("Permission query: " + result.toString()); // Debug log
return result.toString();
}
}