· 5 years ago · Feb 23, 2020, 07:14 PM
1package dk.rasmusbendix.swalbertpunishment;
2
3import com.zaxxer.hikari.HikariConfig;
4import com.zaxxer.hikari.HikariDataSource;
5import dk.rasmusbendix.swalbertpunishment.player.PlayerInformation;
6import org.bukkit.OfflinePlayer;
7import org.bukkit.entity.Player;
8
9import java.sql.*;
10import java.util.HashMap;
11import java.util.UUID;
12
13public final class Database {
14
15 private HikariConfig config;
16 private HikariDataSource dataSource;
17 private SwalbertPunishments plugin;
18
19 private static String TABLE_PREFIX;
20
21 public Database(SwalbertPunishments plugin) {
22
23 this.plugin = plugin;
24 CustomConfig cc = plugin.getCustomConfig();
25
26 config = new HikariConfig();
27 TABLE_PREFIX = cc.getString("database.table-prefix", "punishmentgui_");
28 config.setJdbcUrl(cc.getString("database.url"));
29 config.setUsername(cc.getString("database.username"));
30 config.setPassword(cc.getString("database.password"));
31 config.addDataSourceProperty("cachePrepStmts", true);
32 config.addDataSourceProperty("prepStmtCacheSize", "250");
33 config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
34
35 dataSource = new HikariDataSource(config);
36
37
38 // Source for checking tables https://stackoverflow.com/a/2943166
39 try(Connection connection = getConnection()) {
40 DatabaseMetaData dbm = connection.getMetaData();
41 // check if our table is there
42 ResultSet tables = dbm.getTables(null, null, TABLE_PREFIX + "player", null);
43 if (tables.next()) {
44 // Table exists
45 } else {
46 // Table doesn't exist, create it.
47 String createQuery = "CREATE TABLE `" + TABLE_PREFIX + "player` (" +
48 " `uuid` varchar(36) NOT NULL," +
49 " `offence` varchar(50) NOT NULL," +
50 " `severity` int(11) NOT NULL DEFAULT '0'," +
51 " `last_committed` bigint(20) NOT NULL," +
52 " PRIMARY KEY (`uuid`,`offence`)" +
53 ") ENGINE=InnoDB DEFAULT CHARSET=latin1";
54 PreparedStatement stmt = connection.prepareStatement(createQuery);
55 stmt.executeUpdate();
56 stmt.close();
57 }
58 tables.close();
59 } catch (SQLException e) {
60 e.printStackTrace();
61 }
62
63 }
64
65 void shutdown() {
66 dataSource.close();
67 }
68
69 private Connection getConnection() throws SQLException {
70 return dataSource.getConnection();
71 }
72 /*
73 *
74 CREATE TABLE `player` (
75 `uuid` varchar(36) NOT NULL,
76 `offence` varchar(50) NOT NULL,
77 `severity` int(11) NOT NULL DEFAULT '0',
78 `last_committed` bigint(20) NOT NULL,
79 PRIMARY KEY (`uuid`,`offence`)
80) ENGINE=InnoDB DEFAULT CHARSET=latin1;
81 *
82 * */
83
84
85 public PlayerInformation getPlayer(OfflinePlayer player) {
86
87 if(!hasPlayer(player.getUniqueId()))
88 return new PlayerInformation(player.getName(), player.getUniqueId());
89
90 try (Connection conn = getConnection();
91 PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + TABLE_PREFIX + "player WHERE uuid = ?")) {
92
93 stmt.setString(1, player.getUniqueId().toString());
94 ResultSet rs = stmt.executeQuery();
95 HashMap<String, Integer> map = new HashMap<>();
96
97 while(rs.next()) {
98 map.put(rs.getString("offence"), rs.getInt("severity"));
99// System.out.println("O:S > " + rs.getString("offence") + ":" + rs.getInt("severity"));
100 }
101
102 rs.close();
103
104 return new PlayerInformation(player.getName(), player.getUniqueId(), map);
105
106 }catch (SQLException e) {
107 e.printStackTrace();
108 }
109
110 return null;
111 }
112
113 public boolean hasPlayerPunishment(UUID uuid, String punishment) {
114
115 ResultSet rs = null;
116 try (Connection conn = getConnection();
117 PreparedStatement stmt = conn.prepareStatement("SELECT uuid,offence FROM " + TABLE_PREFIX + "player WHERE uuid = ? AND offence = ?")) {
118
119 stmt.setString(1, uuid.toString());
120 stmt.setString(2, punishment);
121 rs = stmt.executeQuery();
122 if(rs.next())
123 return true;
124
125 } catch (SQLException e) {
126 e.printStackTrace();
127 } finally {
128 try {
129 if (rs != null && !rs.isClosed())
130 rs.close();
131 } catch (SQLException ex) {
132 ex.printStackTrace();
133 }
134 }
135
136 return false;
137
138 }
139
140 public boolean hasPlayer(UUID uuid) {
141
142 ResultSet rs = null;
143 try (Connection conn = getConnection();
144 PreparedStatement stmt = conn.prepareStatement("SELECT uuid FROM " + TABLE_PREFIX + "player WHERE uuid = ?")) {
145
146 stmt.setString(1, uuid.toString());
147 rs = stmt.executeQuery();
148 if(rs.next())
149 return true;
150
151 } catch (SQLException e) {
152 e.printStackTrace();
153 } finally {
154 try {
155 if (rs != null && !rs.isClosed())
156 rs.close();
157 } catch (SQLException ex) {
158 ex.printStackTrace();
159 }
160 }
161
162 return false;
163
164 }
165
166 // UPDATE player SET severity = ?, last_committed = ? WHERE uuid = ? AND offence = ?;
167 public void savePlayer(PlayerInformation pi) {
168
169 if(!hasPlayer(pi.getUuid())) {
170 addPlayer(pi);
171 return;
172 }
173
174 try (Connection conn = getConnection()) {
175
176 HashMap<String, Integer> map = pi.getPunishmentMap();
177 for(String offence : map.keySet()) {
178 if(hasPlayerPunishment(pi.getUuid(), offence)) {
179 PreparedStatement stmt = conn.prepareStatement("UPDATE " + TABLE_PREFIX + "player SET severity = ?, last_committed = ? WHERE uuid = ? AND offence = ?");
180
181 stmt.setInt(1, map.get(offence));
182 stmt.setLong(2, System.currentTimeMillis());
183 stmt.setString(3, pi.getUuid().toString());
184 stmt.setString(4, offence);
185
186 stmt.executeUpdate();
187 stmt.close();
188 } else {
189 addPlayerPunishment(pi, offence);
190 }
191 }
192
193 } catch (SQLException e) {
194 e.printStackTrace();
195 }
196
197 }
198
199 private boolean addPlayerPunishment(PlayerInformation pi, String offence) {
200 try (Connection conn = getConnection()) {
201
202 // Check if map is empty, if it is, return, since theres nothing to save
203 HashMap<String, Integer> map = pi.getPunishmentMap();
204 if(map.keySet().size() == 0) {
205 return false;
206 }
207
208
209 PreparedStatement stmt = conn.prepareStatement("INSERT INTO " + TABLE_PREFIX + "player(uuid, offence, severity, last_committed) VALUES(?, ?, ?, ?)");
210
211 stmt.setString(1, pi.getUuid().toString());
212 stmt.setString(2, offence);
213 stmt.setInt(3, map.get(offence));
214 stmt.setLong(4, System.currentTimeMillis());
215
216 stmt.executeUpdate();
217
218 stmt.close();
219
220
221 return true;
222
223 } catch (SQLException e) {
224 e.printStackTrace();
225 return false;
226 }
227 }
228
229 // INSERT INTO player(uuid, offence, severity, last_committed) VALUES(?, ?, ?, ?);
230 private boolean addPlayer(PlayerInformation pi) {
231
232 try (Connection conn = getConnection()) {
233
234 // Check if map is empty, if it is, return, since theres nothing to save
235 HashMap<String, Integer> map = pi.getPunishmentMap();
236 if(map.keySet().size() == 0) {
237 return false;
238 }
239
240
241 for(String offence : map.keySet()) {
242 PreparedStatement stmt = conn.prepareStatement("INSERT INTO " + TABLE_PREFIX + "player(uuid, offence, severity, last_committed) VALUES(?, ?, ?, ?)");
243
244 stmt.setString(1, pi.getUuid().toString());
245 stmt.setString(2, offence);
246 stmt.setInt(3, map.get(offence));
247 stmt.setLong(4, System.currentTimeMillis());
248
249 stmt.executeUpdate();
250
251 stmt.close();
252 }
253
254
255
256 return true;
257
258 } catch (SQLException e) {
259 e.printStackTrace();
260 return false;
261 }
262
263 }
264
265}