· 9 years ago · Apr 12, 2017, 01:24 PM
1import java.sql.*;
2
3public class Driver {
4 static Connection mConnection;
5 static Statement mStatement;
6
7 public static void main(String[] args) {
8
9 try {
10 //Get a connection to database
11 mConnection = DriverManager.getConnection("jdbc:mysql://localhost/?user=root&password=12345");
12 //Create a statement
13 mStatement = mConnection.createStatement();
14
15 //Execure SQL query
16 /*String sql = "INSERT INTO employees "
17 + " (last_name, first_name, email)"
18 + " VALUES ('"data"', 'Data2', 'd@ua.fm')";
19 statement.executeUpdate(sql);*/
20
21 createDatabase();
22 //addAccount("Dmitry", "pass1");
23 //addAccount("User2", "password2");
24
25 //addUser(1, "UserName1");
26 //addUser(2, "Vvvvvvvvv");
27
28 //addDevice(15, "token id ", "json list apps", 40.85017679, -73.77937317);
29
30 getUsers("User2");
31
32 } catch(Exception exc) {
33 exc.printStackTrace();
34 }
35 }
36 public static void createDatabase() {
37 try {
38 int result = mStatement.executeUpdate("CREATE DATABASE IF NOT EXISTS process_monitoring_database DEFAULT CHARACTER SET utf8");
39 System.out.println("Create database " + result);
40 /*String sql = "CREATE TABLE IF NOT EXISTS process_monitoring_database.account ( " +
41 " account_id INT NOT NULL AUTO_INCREMENT, " +
42 " login VARCHAR(50) NOT NULL, " +
43 " password VARCHAR(50) NOT NULL, " +
44 " PRIMARY KEY ( account_id )) " +
45 " UNIQUE KEY login_UNIQUE (login ASC)) " +
46 " ENGINE = InnoDB " +
47 " DEFAULT CHARACTER SET = utf8;";*/
48 String sql = "CREATE TABLE IF NOT EXISTS process_monitoring_database.account ( " +
49 " account_id INT NOT NULL AUTO_INCREMENT, " +
50 " login VARCHAR(50) NOT NULL, " +
51 " password VARCHAR(50) NOT NULL, " +
52 " PRIMARY KEY (account_id), " +
53 " UNIQUE INDEX account_id_UNIQUE (account_id ASC), " +
54 " UNIQUE INDEX login_UNIQUE (login ASC)) " +
55 " ENGINE = InnoDB " +
56 " DEFAULT CHARACTER SET = utf8;";
57
58 mStatement.executeUpdate(sql);
59 sql = "CREATE TABLE IF NOT EXISTS process_monitoring_database.user ( " +
60 " user_id INT NOT NULL AUTO_INCREMENT, " +
61 " account_id INT NOT NULL, " +
62 " user_name VARCHAR(50) NOT NULL, " +
63 " PRIMARY KEY ( user_id ), " +
64 " INDEX account_id_idx (account_id ASC), " +
65 " CONSTRAINT account_id " +
66 " FOREIGN KEY (account_id) " +
67 " REFERENCES process_monitoring_database.account ( account_id ) " +
68 " ON DELETE CASCADE " +
69 " ON UPDATE CASCADE) " +
70 " ENGINE = InnoDB " +
71 " DEFAULT CHARACTER SET = utf8;";
72 mStatement.executeUpdate(sql);
73 sql = "CREATE TABLE IF NOT EXISTS process_monitoring_database.device ( " +
74 " device_id INT NOT NULL AUTO_INCREMENT, " +
75 " user_id INT NOT NULL, " +
76 " token VARCHAR(255) NOT NULL, " +
77 " apps LONGTEXT NULL, " +
78 " latitude FLOAT NULL, " +
79 " longtitude FLOAT NULL, " +
80 " PRIMARY KEY (device_id), " +
81 " INDEX user_id_idx (user_id ASC), " +
82 " CONSTRAINT user_id " +
83 " FOREIGN KEY ( user_id ) " +
84 " REFERENCES process_monitoring_database.user ( user_id ) " +
85 " ON DELETE NO ACTION " +
86 " ON UPDATE NO ACTION) " +
87 " ENGINE = InnoDB " +
88 " DEFAULT CHARACTER SET = utf8;";
89 mStatement.executeUpdate(sql);
90
91 } catch (SQLException e) {
92 e.printStackTrace();
93 }
94 }
95 public static void addAccount(String login, String password) {
96 String sql = "INSERT INTO process_monitoring_database.account " +
97 "(`login`, `password`) VALUES (?, ?)";
98
99 try {
100 PreparedStatement preparedStatement = mConnection.prepareStatement(sql);
101 preparedStatement.setString(1, login);
102 preparedStatement.setString(2, password);
103 preparedStatement.executeUpdate();
104 } catch (SQLException e) {
105 e.printStackTrace();
106 }
107 }
108 public static void addUser(int accountID, String userName) {
109 String sql = "INSERT INTO process_monitoring_database.user " +
110 " (account_id, user_name) VALUES (?, ?)";
111 try {
112 PreparedStatement preparedStatement = mConnection.prepareStatement(sql);
113 preparedStatement.setInt(1, accountID);
114 preparedStatement.setString(2, userName);
115 preparedStatement.executeUpdate();
116 } catch (SQLException e) {
117 e.printStackTrace();
118 }
119 }
120 public static void addDevice(int userID, String token, String apps, double latitude, double longtitude) {
121 String sql = "INSERT INTO process_monitoring_database.device " +
122 " (user_id, token, apps, latitude, longtitude) " +
123 " VALUES (?, ?, ?, ?, ?)";
124 try {
125 PreparedStatement preparedStatement = mConnection.prepareStatement(sql);
126 preparedStatement.setInt(1, userID);
127 preparedStatement.setString(2, token);
128 preparedStatement.setString(3, apps);
129 preparedStatement.setDouble(4, latitude);
130 preparedStatement.setDouble(5, longtitude);
131 preparedStatement.executeUpdate();
132 } catch (SQLException e) {
133 e.printStackTrace();
134 }
135 }
136 public static void getUsers(String login) {
137 try {
138 //ResultSet resultSet = mStatement.executeQuery();
139 String sql = "SELECT * FROM process_monitoring_database.account WHERE login=?";
140 PreparedStatement preparedStatement = mConnection.prepareStatement(sql);
141 preparedStatement.setString(1, login);
142
143 ResultSet rs = preparedStatement.executeQuery();
144 rs.next();
145 int accountID = rs.getInt("account_id");
146
147 System.out.println("========== " + accountID);
148
149 sql = "SELECT * FROM process_monitoring_database.user WHERE account_id=?";
150 preparedStatement = mConnection.prepareStatement(sql);
151 preparedStatement.setInt(1, accountID);
152
153 rs = preparedStatement.executeQuery();
154 while(rs.next()) {
155 System.out.println(rs.getInt("user_id") + " " + rs.getString("user_name"));
156 }
157 } catch (SQLException e) {
158 e.printStackTrace();
159 }
160 }
161}