From b70876b7a3b852d0f0b9aade30d6a9405c054ac5 Mon Sep 17 00:00:00 2001 From: Demian Date: Tue, 9 Jun 2015 13:10:50 -0300 Subject: Moved all the SQL queries to the configuration file --- .gitignore | 4 ++ debug.xml | 70 +++++++++++++++++++++++++++++++ src/org/traccar/database/DataManager.java | 50 +++++++--------------- 3 files changed, 89 insertions(+), 35 deletions(-) create mode 100644 .gitignore diff --git a/.gitignore b/.gitignore new file mode 100644 index 000000000..3fe98582b --- /dev/null +++ b/.gitignore @@ -0,0 +1,4 @@ +/target +.classpath +.project +.settings/ diff --git a/debug.xml b/debug.xml index 6ca0c224a..bd747c97f 100644 --- a/debug.xml +++ b/debug.xml @@ -117,9 +117,78 @@ id INT PRIMARY KEY AUTO_INCREMENT); + + INSERT INTO server (registration, latitude, longitude, zoom) + VALUES (:registration, :latitude, :longitude, :zoom); + + + + SELECT * FROM server; + + + + UPDATE server SET registration = :registration WHERE id = :id; + + + + SELECT * + FROM user + WHERE email = :email AND password = CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR); + + + + SELECT * FROM user; + + + + INSERT INTO user (name, email, password, admin) + VALUES (:name, :email, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR), :admin); + + + + UPDATE user SET + name = :name, + email = :email, + admin = :admin, + password = CASEWHEN((SELECT password FROM user WHERE id = :id) = :password, :password, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR)) + WHERE id = :id; + + + + DELETE FROM user WHERE id = :id; + + + + "SELECT userId, deviceId FROM user_device;" + + SELECT * FROM device; + + + SELECT * FROM device WHERE id IN (SELECT deviceId FROM user_device WHERE userId = :userId); + + + + INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId); + + + + UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id; + + + + DELETE FROM device WHERE id = :id; + + + + INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId); + + + + SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to; + INSERT INTO position (deviceId, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other) @@ -130,6 +199,7 @@ UPDATE device SET positionId = :id WHERE id = :deviceId; + 5000 diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index db863e8f4..7c2505f77 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -167,9 +167,7 @@ public class DataManager { Server server = new Server(); server.setRegistration(true); - QueryBuilder.create(dataSource, - "INSERT INTO server (registration, latitude, longitude, zoom)" + - "VALUES (:registration, :latitude, :longitude, :zoom);") + QueryBuilder.create(dataSource, properties.getProperty("database.insertServer")) .setObject(server) .executeUpdate(); @@ -213,46 +211,37 @@ public class DataManager { } public User login(String email, String password) throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM user WHERE email = :email AND " + - "password = CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR);") + return QueryBuilder.create(dataSource, properties.getProperty("database.loginUser")) .setString("email", email) .setString("password", password) .executeQuerySingle(new User()); } public Collection getUsers() throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM user;") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectUsersAll")) .executeQuery(new User()); } public void addUser(User user) throws SQLException { - user.setId(QueryBuilder.create(dataSource, - "INSERT INTO user (name, email, password, admin) " + - "VALUES (:name, :email, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR), :admin);") + user.setId(QueryBuilder.create(dataSource, properties.getProperty("database.insertUser")) .setObject(user) .executeUpdate()); } public void updateUser(User user) throws SQLException { - QueryBuilder.create(dataSource, - "UPDATE user SET name = :name, email = :email, admin = :admin," + - "password = CASEWHEN((SELECT password FROM user WHERE id = :id) = :password, :password, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR)) WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.updateUser")) .setObject(user) .executeUpdate(); } public void removeUser(User user) throws SQLException { - QueryBuilder.create(dataSource, - "DELETE FROM user WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.deleteUser")) .setObject(user) .executeUpdate(); } public Collection getPermissions() throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT userId, deviceId FROM user_device;") + return QueryBuilder.create(dataSource, properties.getProperty("database.getPermissions")) .executeQuery(new Permission()); } @@ -262,45 +251,38 @@ public class DataManager { } public Collection getDevices(long userId) throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM device WHERE id IN (" + - "SELECT deviceId FROM user_device WHERE userId = :userId);") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectDevices")) .setLong("userId", userId) .executeQuery(new Device()); } public void addDevice(Device device) throws SQLException { - device.setId(QueryBuilder.create(dataSource, - "INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);") + device.setId(QueryBuilder.create(dataSource, properties.getProperty("database.insertDevice")) .setObject(device) .executeUpdate()); } public void updateDevice(Device device) throws SQLException { - QueryBuilder.create(dataSource, - "UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.updateDevice")) .setObject(device) .executeUpdate(); } public void removeDevice(Device device) throws SQLException { - QueryBuilder.create(dataSource, - "DELETE FROM device WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.removeDevice")) .setObject(device) .executeUpdate(); } public void linkDevice(long userId, long deviceId) throws SQLException { - QueryBuilder.create(dataSource, - "INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);") + QueryBuilder.create(dataSource, properties.getProperty("database.linkDevice")) .setLong("userId", userId) .setLong("deviceId", deviceId) .executeUpdate(); } public Collection getPositions(long userId, long deviceId, Date from, Date to) throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to;") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectPositions")) .setLong("deviceId", deviceId) .setDate("from", from) .setDate("to", to) @@ -318,14 +300,12 @@ public class DataManager { } public Server getServer() throws SQLException { - return QueryBuilder.create(dataSource, - "SELECT * FROM server;") + return QueryBuilder.create(dataSource, properties.getProperty("database.selectServer")) .executeQuerySingle(new Server()); } public void updateServer(Server server) throws SQLException { - QueryBuilder.create(dataSource, - "UPDATE server SET registration = :registration WHERE id = :id;") + QueryBuilder.create(dataSource, properties.getProperty("database.updateServer")) .setObject(server) .executeUpdate(); } -- cgit v1.2.3