From b70876b7a3b852d0f0b9aade30d6a9405c054ac5 Mon Sep 17 00:00:00 2001
From: Demian <demianalonso@gmail.com>
Date: Tue, 9 Jun 2015 13:10:50 -0300
Subject: Moved all the SQL queries to the configuration file

---
 src/org/traccar/database/DataManager.java | 50 ++++++++++---------------------
 1 file changed, 15 insertions(+), 35 deletions(-)

(limited to 'src/org/traccar/database')

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<User> 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<Permission> 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<Device> 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<Position> 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