· 8 years ago · Dec 08, 2017, 03:28 PM
1 private static Database database;
2 private Connection connection = plugin.getInstance().getDatabase().getConnection();
3
4 public DatabaseData(WNPlayBungeeCord plugin, Database database) {
5 this.plugin = plugin;
6 this.database = database;
7 }
8
9 public void createTables() {
10 String sql = "CREATE TABLE IF NOT EXISTS rank (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL , " +
11 "displayname VARCHAR(50) NOT NULL, color VARCHAR(50) NOT NULL , weight INT NOT NULL);";
12 String sql1 = "CREATE TABLE IF NOT EXISTS user (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name " +
13 "VARCHAR(50) NOT NULL, uuid VARCHAR(50) NOT NULL, rank_id INT NOT NULL, rank_expire LONG NOT NULL, first_join LONG NOT NULL, played_time LONG NOT NULL, last_version INT NOT NULL, ip VARCHAR(100) NOT NULL);";
14 plugin.getInstance().getDatabase().update(sql);
15 plugin.getInstance().getDatabase().update(sql1);
16 }
17
18 public Rank getRankById(int id) {
19 PreparedStatement preparedStatement = null;
20 try {
21 preparedStatement = connection.prepareStatement("SELECT * FROM rank WHERE id = '" + id + "';");
22 preparedStatement.executeQuery();
23 if (preparedStatement.getResultSet().next()) {
24 return getRankFromResultSet(preparedStatement.getResultSet());
25 }
26 } catch (SQLException e) {
27 e.printStackTrace();
28 }
29 return new Rank(0, "", "", "", 0);
30 }
31
32 public Rank getRankByName(String rank) {
33 PreparedStatement preparedStatement;
34 try {
35 preparedStatement = connection.prepareStatement("SELECT * FROM rank WHERE name = '" + rank + "';");
36 preparedStatement.executeQuery();
37 if (preparedStatement.getResultSet().next()) {
38 return getRankFromResultSet(preparedStatement.getResultSet());
39 }
40 } catch (SQLException e) {
41 e.printStackTrace();
42 }
43 return new Rank(0, "", "", "", 0);
44 }
45
46 public Rank getRankFromResultSet(ResultSet resultSet) throws SQLException {
47 int id = resultSet.getInt("id");
48 String name = resultSet.getString("name");
49 String displayName = resultSet.getString("displayname");
50 String color = resultSet.getString("color");
51 int weight = resultSet.getInt("weight");
52 return new Rank(id, name, displayName, color, weight);
53 }
54
55 public User getUserByName(String name) {
56 PreparedStatement preparedStatement;
57 try {
58 preparedStatement = connection.prepareStatement("SELECT * FROM user WHERE name = '" + name + "';");
59 preparedStatement.executeQuery();
60 if (preparedStatement.getResultSet().next()) {
61 return getUserFromResultSet(preparedStatement.getResultSet());
62 }
63 } catch (Exception e) {
64 e.printStackTrace();
65 }
66 return new User(0, "", "", 0, 0, 0, 0, 0, "");
67 }
68
69 public User getUserById(int id) {
70 PreparedStatement preparedStatement;
71 try {
72 preparedStatement = connection.prepareStatement("SELECT * FROM user WHERE id = '" + id + "';");
73 preparedStatement.executeQuery();
74 if (preparedStatement.getResultSet().next()) {
75 return getUserFromResultSet(preparedStatement.getResultSet());
76 }
77 } catch (Exception e) {
78 e.printStackTrace();
79 }
80 return new User(0, "", "", 0, 0, 0, 0, 0, "");
81 }
82
83 public void setUser(ProxiedPlayer player) {
84 ProxyServer.getInstance().getScheduler().runAsync(plugin, () -> {
85 PreparedStatement preparedStatement;
86 try {
87 preparedStatement = connection.prepareStatement("INSERT INTO user (name, uuid, rank_id, rank_expire, first_join, played_time, last_version, ip) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
88 preparedStatement.setString(1, player.getName());
89 preparedStatement.setString(2, player.getUUID());
90 preparedStatement.setInt(3, 0);
91 preparedStatement.setLong(4, -1);
92 preparedStatement.setLong(5, System.currentTimeMillis());
93 preparedStatement.setLong(6, 0);
94 preparedStatement.setInt(7, player.getPendingConnection().getVersion());
95 preparedStatement.setString(8, player.getAddress().getAddress().getHostAddress());
96 preparedStatement.executeUpdate();
97 } catch (SQLException e) {
98 e.printStackTrace();
99 }
100 });
101 }
102
103 public User getUserFromResultSet(ResultSet resultSet) throws SQLException {
104 int id = resultSet.getInt("id");
105 String name = resultSet.getString("name");
106 String uuid = resultSet.getString("uuid");
107 int rankId = resultSet.getInt("rank_id");
108 long rankExpire = resultSet.getLong("rank_expire");
109 long firstJoin = resultSet.getLong("first_join");
110 long playedTime = resultSet.getLong("played_time");
111 int lastVersion = resultSet.getInt("last_version");
112 String ip = resultSet.getString("ip");
113 return new User(id, name, uuid, rankId, rankExpire, firstJoin, playedTime, lastVersion, ip);
114 }
115
116 public boolean userExistsByName(String name) {
117 PreparedStatement preparedStatement;
118 try {
119 preparedStatement = connection.prepareStatement("SELECT * FROM user WHERE name = '" + name + "';");
120 preparedStatement.executeQuery();
121 return preparedStatement.getResultSet().next();
122 } catch (SQLException e) {
123 e.printStackTrace();
124 return false;
125 }
126 }
127
128 public boolean userExistsById(int id) {
129 PreparedStatement preparedStatement;
130 try {
131 preparedStatement = connection.prepareStatement("SELECT * FROM user WHERE id = '" + id + "';");
132 preparedStatement.executeQuery();
133 return preparedStatement.getResultSet().next();
134 } catch (SQLException e) {
135 e.printStackTrace();
136 return false;
137 }
138 }
139
140 public static Database getDatabase() {
141 return database;
142 }