diff options
Diffstat (limited to 'src/org/traccar/database')
| -rw-r--r-- | src/org/traccar/database/DataManager.java | 33 | ||||
| -rw-r--r-- | src/org/traccar/database/ResultSetConverter.java | 80 |
2 files changed, 108 insertions, 5 deletions
diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index 1c6579bf4..172fbf763 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -26,6 +26,7 @@ import javax.sql.DataSource; import javax.xml.xpath.XPath; import javax.xml.xpath.XPathExpressionException; import javax.xml.xpath.XPathFactory; +import org.json.JSONArray; import org.traccar.helper.DriverDelegate; import org.traccar.helper.Log; import org.traccar.model.Device; @@ -254,17 +255,19 @@ public class DataManager { "id INT PRIMARY KEY AUTO_INCREMENT," + "name VARCHAR(1024) NOT NULL," + "unique_id VARCHAR(1024) NOT NULL UNIQUE," + - "position_id INT NOT NULL," + - "data_id INT NOT NULL);" + + "position_id INT," + + "data_id INT);" + "CREATE TABLE user_device (" + "user_id INT NOT NULL," + "device_id INT NOT NULL," + - "read BOOLEAN NOT NULL," + - "write BOOLEAN NOT NULL," + + "read BOOLEAN DEFAULT true NOT NULL," + + "write BOOLEAN DEFAULT true NOT NULL," + "FOREIGN KEY (user_id) REFERENCES user(id)," + "FOREIGN KEY (device_id) REFERENCES device(id));" + + "CREATE INDEX user_device_user_id ON user_device(user_id);" + + "CREATE TABLE position (" + "id INT PRIMARY KEY AUTO_INCREMENT," + "device_id INT NOT NULL," + @@ -321,7 +324,8 @@ public class DataManager { Connection connection = dataSource.getConnection(); try { PreparedStatement statement = connection.prepareStatement( - "SELECT id FROM user WHERE name = ? AND password = CAST(HASH('SHA256', STRINGTOUTF8(?), 1000) AS VARCHAR);"); + "SELECT id FROM user WHERE name = ? AND " + + "password = CAST(HASH('SHA256', STRINGTOUTF8(?), 1000) AS VARCHAR);"); try { statement.setString(1, name); statement.setString(2, password); @@ -357,5 +361,24 @@ public class DataManager { connection.close(); } } + + public JSONArray getDevices(long userId) throws SQLException { + + Connection connection = dataSource.getConnection(); + try { + PreparedStatement statement = connection.prepareStatement( + "SELECT * FROM device WHERE id IN (" + + "SELECT device_id FROM user_device WHERE user_id = ?);"); + try { + statement.setLong(1, userId); + + return ResultSetConverter.convert(statement.executeQuery()); + } finally { + statement.close(); + } + } finally { + connection.close(); + } + } } diff --git a/src/org/traccar/database/ResultSetConverter.java b/src/org/traccar/database/ResultSetConverter.java new file mode 100644 index 000000000..32289f756 --- /dev/null +++ b/src/org/traccar/database/ResultSetConverter.java @@ -0,0 +1,80 @@ +/* + * Copyright 2015 Anton Tananaev (anton.tananaev@gmail.com) + * + * 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 org.json.JSONArray; +import org.json.JSONObject; + +import java.sql.SQLException; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; + +public class ResultSetConverter { + + public static JSONArray convert(ResultSet rs) throws SQLException { + + JSONArray json = new JSONArray(); + ResultSetMetaData rsmd = rs.getMetaData(); + + while (rs.next()) { + + int numColumns = rsmd.getColumnCount(); + JSONObject obj = new JSONObject(); + + for (int i = 1; i <= numColumns; i++) { + + String columnName = rsmd.getColumnName(i).toLowerCase(); + + switch (rsmd.getColumnType(i)) { + case java.sql.Types.BIGINT: + obj.put(columnName, rs.getInt(columnName)); + break; + case java.sql.Types.BOOLEAN: + obj.put(columnName, rs.getBoolean(columnName)); + break; + case java.sql.Types.DOUBLE: + obj.put(columnName, rs.getDouble(columnName)); + break; + case java.sql.Types.FLOAT: + obj.put(columnName, rs.getFloat(columnName)); + break; + case java.sql.Types.INTEGER: + obj.put(columnName, rs.getInt(columnName)); + break; + case java.sql.Types.NVARCHAR: + obj.put(columnName, rs.getNString(columnName)); + break; + case java.sql.Types.VARCHAR: + obj.put(columnName, rs.getString(columnName)); + break; + case java.sql.Types.DATE: + obj.put(columnName, rs.getDate(columnName)); + break; + case java.sql.Types.TIMESTAMP: + obj.put(columnName, rs.getTimestamp(columnName)); + break; + default: + obj.put(columnName, rs.getObject(columnName)); + break; + } + } + + json.put(obj); + } + + return json; + } +} |