· 6 years ago · May 30, 2019, 11:26 PM
1package me.perotin.privatetalkpremium.utils;
2
3import com.zaxxer.hikari.HikariConfig;
4import com.zaxxer.hikari.HikariDataSource;
5import me.perotin.privatetalkpremium.objects.Chatroom;
6import me.perotin.privatetalkpremium.PrivateTalk;
7import org.bukkit.Bukkit;
8import org.bukkit.scheduler.BukkitRunnable;
9
10import java.sql.Connection;
11import java.sql.PreparedStatement;
12import java.sql.ResultSet;
13import java.sql.SQLException;
14import java.util.*;
15
16public class SQLWrapper {
17
18 private String user;
19 private String database;
20 private String password;
21 private String host;
22 public HikariDataSource hikariDataSource;
23
24
25 public SQLWrapper(PrivateTalk plugin) {
26 this.user = plugin.getConfig().getString("user");
27 this.database = plugin.getConfig().getString("database");
28 this.password = plugin.getConfig().getString("password");
29 this.host = plugin.getConfig().getString("host");
30
31 }
32
33 public SQLWrapper(String user, String database, String password, String host) {
34 this.user = user;
35 this.database = database;
36 this.password = password;
37 this.host = host;
38 }
39
40 public void init() {
41 // configuring HikariConfig object
42 HikariConfig config;
43 config = new HikariConfig();
44 config.setJdbcUrl("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
45 config.setUsername(user);
46 config.setPassword(password);
47 config.setMaximumPoolSize(15);
48 config.addDataSourceProperty("databaseName", database);
49 config.addDataSourceProperty("serverName", host);
50 config.addDataSourceProperty("cachePrepStmts", "true");
51 config.addDataSourceProperty("prepStmtCacheSize", "250");
52 config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
53 config.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
54
55 this.hikariDataSource = new HikariDataSource(config);
56
57 }
58
59
60 public void insertNewData(Chatroom chatroom) {
61
62
63 try (Connection connection = hikariDataSource.getConnection()) {
64
65
66 String chatSql = "INSERT INTO ChatroomTable VALUES (?, ?, ?, ?, ?)";
67 PreparedStatement inserter = connection.prepareStatement(chatSql);
68 inserter.setString(1, chatroom.getName());
69 inserter.setString(2, chatroom.getDescription());
70 inserter.setBoolean(3, chatroom.isPrivate());
71 if (chatroom.getOwner() != null) {
72 inserter.setString(4, chatroom.getOwner().toString());
73 } else {
74 inserter.setString(4, "0");
75 }
76 inserter.setBoolean(5, chatroom.isConsoleOwned());
77 inserter.executeUpdate();
78
79
80 for (UUID member : chatroom.getMembers()) {
81 String sql = "INSERT INTO MembersTable VALUES (?,?)";
82
83 inserter = connection.prepareStatement(sql);
84 inserter.setString(1, chatroom.getName());
85 inserter.setString(2, member.toString());
86 inserter.executeUpdate();
87 }
88
89 for (UUID banned : chatroom.getBanned()) {
90
91 String sql = "INSERT INTO BannedTable VALUES (?, ?)";
92 inserter = connection.prepareStatement(sql);
93 inserter.setString(1, chatroom.getName());
94 inserter.setString(2, banned.toString());
95 inserter.executeUpdate();
96 }
97
98 for (UUID nicker : chatroom.getNicknames().keySet()) {
99 String sql = "INSERT INTO NicknamesTable VALUES (?, ?, ?)";
100 inserter = connection.prepareStatement(sql);
101 inserter.setString(1, chatroom.getName());
102 inserter.setString(2, nicker.toString());
103 inserter.setString(3, chatroom.getNick(nicker));
104 inserter.executeUpdate();
105 }
106
107 for (UUID mod : chatroom.getModerators()) {
108 String sql = "INSERT INTO ModeratorsTable VALUES (?, ?)";
109 inserter = connection.prepareStatement(sql);
110 inserter.setString(1, chatroom.getName());
111 inserter.setString(2, mod.toString());
112 inserter.executeUpdate();
113 }
114 for (UUID muted : chatroom.getMuted()) {
115 String sql = "INSERT INTO MutedTable VALUES (?, ?)";
116 inserter = connection.prepareStatement(sql);
117 inserter.setString(1, chatroom.getName());
118 inserter.setString(2, muted.toString());
119 inserter.executeUpdate();
120 }
121 for (UUID chatter : chatroom.getTalkers()) {
122 String sql = "INSERT INTO InChatTable VALUES (?, ?)";
123 inserter = connection.prepareStatement(sql);
124 inserter.setString(1, chatroom.getName());
125 inserter.setString(2, chatter.toString());
126 inserter.executeUpdate();
127 }
128
129 inserter.close();
130
131
132 } catch (SQLException ex) {
133 ex.printStackTrace();
134
135 }
136
137 }
138
139 public ArrayList<Chatroom> loadChatrooms() {
140
141 ArrayList<Chatroom> chatroomsToLoad = new ArrayList<>();
142 PreparedStatement preparedStatement;
143 ResultSet resultSet;
144 try (Connection connection = hikariDataSource.getConnection()) {
145 preparedStatement = connection.prepareStatement("SELECT * FROM ChatroomTable");
146 resultSet = preparedStatement.executeQuery();
147
148 while (resultSet.next()) {
149 String name = resultSet.getString("name");
150 String description = resultSet.getString("description");
151 String ownerString = resultSet.getString("owner");
152 UUID owner = null;
153 if (!ownerString.equals("0")) {
154 owner = UUID.fromString(resultSet.getString("owner"));
155 }
156 boolean status = resultSet.getBoolean("status");
157 boolean consoleOwned = resultSet.getBoolean("server_owned");
158 String modSql = "SELECT * FROM ModeratorsTable WHERE chatroom = ?;";
159 preparedStatement = connection.prepareStatement(modSql);
160 preparedStatement.setString(1, name);
161 ResultSet mods = preparedStatement.executeQuery();
162 ArrayList<UUID> moderators = new ArrayList<>();
163 HashSet<UUID> members = new HashSet<>();
164 HashSet<UUID> muted = new HashSet<>();
165 HashSet<UUID> banned = new HashSet<>();
166 HashMap<UUID, String> nicknames = new HashMap<>();
167 HashSet<UUID> inChat = new HashSet<>();
168
169 while (mods.next()) {
170
171 UUID modUuid = UUID.fromString(mods.getString("uuid"));
172 moderators.add(modUuid);
173 }
174 mods.close();
175 String memberSql = "SELECT * FROM MembersTable WHERE chatroom = ?;";
176 preparedStatement = connection.prepareStatement(memberSql);
177 preparedStatement.setString(1, name);
178
179 // do the rest
180 ResultSet memberRes = preparedStatement.executeQuery();
181 while (memberRes.next()) {
182 members.add(UUID.fromString(memberRes.getString("uuid")));
183
184 }
185
186 memberRes.close();
187 String mutedSql = "SELECT * FROM MutedTable WHERE chatroom = ?;";
188 preparedStatement = connection.prepareStatement(mutedSql);
189 preparedStatement.setString(1, name);
190 ResultSet mutedResult = preparedStatement.executeQuery();
191 while (mutedResult.next()) {
192 muted.add(UUID.fromString(mutedResult.getString("uuid")));
193 }
194 mutedResult.close();
195 String bannedSql = "SELECT * FROM BannedTable WHERE chatroom = ?;";
196 preparedStatement = connection.prepareStatement(bannedSql);
197 preparedStatement.setString(1, name);
198 ResultSet bannedRes = preparedStatement.executeQuery();
199
200 while (bannedRes.next()) {
201 banned.add(UUID.fromString(bannedRes.getString("uuid")));
202 }
203 bannedRes.close();
204 String nickSql = "SELECT * FROM NicknamesTable WHERE chatroom = ?;";
205 preparedStatement = connection.prepareStatement(nickSql);
206 preparedStatement.setString(1, name);
207 ResultSet nickRes = preparedStatement.executeQuery();
208 while (nickRes.next()) {
209 nicknames.put(UUID.fromString(nickRes.getString("uuid")), nickRes.getString("nickname"));
210 }
211 nickRes.close();
212
213 String chatSql = "SELECT * FROM InChatTable WHERE chatroom = ?;";
214 preparedStatement = connection.prepareStatement(chatSql);
215 preparedStatement.setString(1, name);
216 ResultSet chatRes = preparedStatement.executeQuery();
217 while (chatRes.next()) {
218 inChat.add(UUID.fromString(chatRes.getString("uuid")));
219 }
220 chatRes.close();
221
222
223 chatroomsToLoad.add(new Chatroom(name, description, members, owner, status, banned, moderators, true, nicknames, inChat, muted, true, consoleOwned));
224
225
226 }
227 resultSet.close();
228 preparedStatement.close();
229
230
231 } catch (SQLException ex) {
232 ex.printStackTrace();
233 }
234
235 return chatroomsToLoad;
236 }
237
238
239 public void cleanup() {
240 hikariDataSource.close();
241
242 }
243
244 public void delete(Chatroom chatroom) {
245 String chatSql = "DELETE FROM ChatroomTable WHERE name = ?";
246 String modSql = "DELETE FROM ModeratorsTable WHERE chatroom = ?";
247 String nickSql = "DELETE FROM NicknamesTable WHERE chatroom = ?";
248 String mutedSql = "DELETE FROM MutedTable WHERE chatroom = ?";
249 String membersSql = "DELETE FROM MembersTable WHERE chatroom = ?";
250 String bannedSql = "DELETE FROM BannedTable WHERE chatroom = ?";
251 String inChatSql = "DELETE FROM InChatTable WHERE chatroom = ?";
252
253
254 new BukkitRunnable() {
255
256 @Override
257 public void run() {
258 try (Connection connection = hikariDataSource.getConnection()) {
259 PreparedStatement statement = connection.prepareStatement(chatSql);
260 statement.setString(1, chatroom.getName());
261 statement.executeUpdate();
262
263 statement = connection.prepareStatement(modSql);
264 statement.setString(1, chatroom.getName());
265 statement.executeUpdate();
266
267 statement = connection.prepareStatement(nickSql);
268 statement.setString(1, chatroom.getName());
269 statement.executeUpdate();
270
271 statement = connection.prepareStatement(mutedSql);
272 statement.setString(1, chatroom.getName());
273 statement.executeUpdate();
274
275 statement = connection.prepareStatement(membersSql);
276 statement.setString(1, chatroom.getName());
277 statement.executeUpdate();
278
279 statement = connection.prepareStatement(bannedSql);
280 statement.setString(1, chatroom.getName());
281 statement.executeUpdate();
282
283 statement = connection.prepareStatement(inChatSql);
284 statement.setString(1, chatroom.getName());
285 statement.executeUpdate();
286
287 statement.close();
288 connection.close();
289
290
291 } catch (SQLException ex) {
292 ex.printStackTrace();
293 }
294 }
295 }.runTaskAsynchronously(PrivateTalk.getInstance());
296 }
297
298 public void createTables() {
299
300 try (Connection connection = hikariDataSource.getConnection()) {
301
302 PreparedStatement create = connection.prepareStatement("CREATE TABLE IF NOT EXISTS ChatroomTable (name VARCHAR(50) NOT NULL PRIMARY KEY, description VARCHAR(60) NOT NULL, status BOOLEAN NOT NULL, owner VARCHAR(36), server_owned BOOLEAN)");
303 create.executeUpdate();
304
305 create = connection.prepareStatement("CREATE TABLE IF NOT EXISTS ModeratorsTable" +
306 "(chatroom VARCHAR(50) NOT NULL, uuid VARCHAR(36) NOT NULL, PRIMARY KEY (chatroom, uuid))");
307 create.executeUpdate();
308
309 create = connection.prepareStatement("CREATE TABLE IF NOT EXISTS NicknamesTable" +
310 "(chatroom VARCHAR(50) NOT NULL, uuid VARCHAR(36) NOT NULL, nickname VARCHAR(20) NOT NULL, PRIMARY KEY(chatroom, uuid, nickname))");
311 create.executeUpdate();
312
313 create = connection.prepareStatement("CREATE TABLE IF NOT EXISTS MutedTable (chatroom VARCHAR(50) NOT NULL," +
314 "uuid VARCHAR(36) NOT NULL, PRIMARY KEY(chatroom, uuid))");
315 create.executeUpdate();
316
317 create = connection.prepareStatement("CREATE TABLE IF NOT EXISTS MembersTable" +
318 "(chatroom VARCHAR(50), uuid VARCHAR(36), PRIMARY KEY(chatroom, uuid))");
319 create.executeUpdate();
320
321 create = connection.prepareStatement("CREATE TABLE IF NOT EXISTS BannedTable" +
322 "(chatroom VARCHAR(50), uuid VARCHAR(36), PRIMARY KEY(chatroom, uuid))");
323 create.executeUpdate();
324
325 create = connection.prepareStatement("CREATE TABLE IF NOT EXISTS InChatTable" +
326 "(chatroom VARCHAR(50), uuid VARCHAR(36), PRIMARY KEY(chatroom, uuid))");
327 create.executeUpdate();
328
329
330 create.close();
331 connection.close();
332 } catch (SQLException ex) {
333 ex.printStackTrace();
334
335 }
336 }
337
338
339 public String getDatabase() {
340 return this.database;
341 }
342
343 public enum SQLTable {
344 Chatroom, Moderators, Nicknames, Muted, Members, Banned, InChat
345 }
346}