· 9 years ago · Apr 12, 2017, 12:20 PM
1import java.sql.*;
2
3public class Driver {
4 private static final String url = "jdbc:mysql://localhost:3306/process_monitoring_database";
5 private static final String user = "root";
6 private static final String password = "12345";
7
8 static Connection mConnection;
9 static Statement mStatement;
10
11 public static void main(String[] args) {
12
13 try {
14 //Get a connection to database
15 //mConnection = DriverManager.getConnection(url, user, password);
16 mConnection = DriverManager.getConnection("jdbc:mysql://localhost/?user=root&password=12345");
17 //Create a statement
18 mStatement = mConnection.createStatement();
19
20 //Execure SQL query
21 /*String sql = "INSERT INTO employees "
22 + " (last_name, first_name, email)"
23 + " VALUES ('"data"', 'Data2', 'd@ua.fm')";
24 statement.executeUpdate(sql);*/
25
26 //createDatabase();
27 addAccount("User2", "sssssss");
28 //addUser(1, "vvvvvvvvvvv");
29 //addUser(1, "aaaaaaaaa");
30 //ResultSet resultSet = myStmt.executeQuery("INSERT INTO `demo`.`employees` (`last_name`, `first_name`, `email`) VALUES ('Ttttt', 'Dm', 'dimqa123@ua.fm'");
31
32 /*while(resultSet.next()) {
33 System.out.println(resultSet.getString("last_name") + " " + resultSet.getString("first_name") + " " + resultSet.getString("email") + " ");
34 }*/
35
36 } catch(Exception exc) {
37 exc.printStackTrace();
38 }
39 }
40 public static void createDatabase() {
41 try {
42 int result = mStatement.executeUpdate("CREATE DATABASE IF NOT EXISTS process_monitoring_database DEFAULT CHARACTER SET utf8");
43 System.out.println("Create database " + result);
44 /*String sql = "CREATE TABLE IF NOT EXISTS process_monitoring_database.account ( " +
45 " account_id INT NOT NULL AUTO_INCREMENT, " +
46 " login VARCHAR(50) NOT NULL, " +
47 " password VARCHAR(50) NOT NULL, " +
48 " PRIMARY KEY ( account_id )) " +
49 " UNIQUE KEY login_UNIQUE (login ASC)) " +
50 " ENGINE = InnoDB " +
51 " DEFAULT CHARACTER SET = utf8;";*/
52 String sql = "CREATE TABLE process_monitoring_database.account ( " +
53 " account_id INT NOT NULL AUTO_INCREMENT, " +
54 " login VARCHAR(50) NOT NULL, " +
55 " password VARCHAR(50) NOT NULL, " +
56 " PRIMARY KEY (account_id), " +
57 " UNIQUE INDEX account_id_UNIQUE (account_id ASC), " +
58 " UNIQUE INDEX login_UNIQUE (login ASC)) " +
59 " ENGINE = InnoDB " +
60 " DEFAULT CHARACTER SET = utf8;";
61
62 mStatement.executeUpdate(sql);
63 sql = "CREATE TABLE IF NOT EXISTS process_monitoring_database.user ( " +
64 " user_id INT NOT NULL AUTO_INCREMENT, " +
65 " account_id INT NOT NULL, " +
66 " user_name VARCHAR(50) NOT NULL, " +
67 " PRIMARY KEY ( user_id ), " +
68 " INDEX account_id_idx (account_id ASC), " +
69 " CONSTRAINT account_id " +
70 " FOREIGN KEY (account_id) " +
71 " REFERENCES process_monitoring_database.account ( account_id ) " +
72 " ON DELETE CASCADE " +
73 " ON UPDATE CASCADE) " +
74 " ENGINE = InnoDB " +
75 " DEFAULT CHARACTER SET = utf8;";
76 mStatement.executeUpdate(sql);
77 sql = "CREATE TABLE IF NOT EXISTS process_monitoring_database.device ( " +
78 " device_id INT NOT NULL AUTO_INCREMENT, " +
79 " user_id INT NOT NULL, " +
80 " token VARCHAR(255) NOT NULL, " +
81 " apps LONGTEXT NULL, " +
82 " latitude FLOAT NULL, " +
83 " longtitude FLOAT NULL, " +
84 " PRIMARY KEY (device_id), " +
85 " INDEX user_id_idx (user_id ASC), " +
86 " CONSTRAINT user_id " +
87 " FOREIGN KEY ( user_id ) " +
88 " REFERENCES process_monitoring_database.user ( user_id ) " +
89 " ON DELETE NO ACTION " +
90 " ON UPDATE NO ACTION) " +
91 " ENGINE = InnoDB " +
92 " DEFAULT CHARACTER SET = utf8;";
93 mStatement.executeUpdate(sql);
94
95 } catch (SQLException e) {
96 e.printStackTrace();
97 }
98 }
99 public static void addAccount(String login, String password) {
100 String sql = "INSERT INTO process_monitoring_database.account " +
101 "(`login`, `password`) VALUES (?, ?)";
102
103 try {
104 PreparedStatement preparedStatement = mConnection.prepareStatement(sql);
105 preparedStatement.setString(1, login);
106 preparedStatement.setString(2, password);
107 preparedStatement.executeUpdate();
108 } catch (SQLException e) {
109 e.printStackTrace();
110 }
111 }
112 public static void addUser(int accountID, String userName) {
113 String sql = "INSERT INTO process_monitoring_database.user " +
114 " (account_id, user_name) VALUES (?, ?)";
115 try {
116 PreparedStatement preparedStatement = mConnection.prepareStatement(sql);
117 preparedStatement.setInt(1, accountID);
118 preparedStatement.setString(2, userName);
119 preparedStatement.executeUpdate();
120 } catch (SQLException e) {
121 e.printStackTrace();
122 }
123 }
124}