· 4 years ago · Jun 24, 2021, 09:44 PM
1public class SQLite {
2 @Getter
3 private static final SQLite Instance = new SQLite();
4 private final File chestStorage = ChestStorage.getData();// A reference to Main Class
5
6 private Connection connection = connect();
7
8 private boolean loadDataStartup = true;
9
10 private final String TableNameChest = "mytest", TableOneFirstRow = "Location", TableOnesecondRow = "Player_UUID",
11 TableOneThirdRow = "FilterWhiteBlack", TableOneFourthRow = "Chest_Type", TableOneFifthRow = "Suction_Range",
12 TableOneSixthRow = "Link_Range", TableOneSeventhRow = "Link_Amount", TableOneEighthRow = "Next_Upgrade",
13 TableOneNineRow = "Chest_contents", TableOneTenRow = "SettingsName", TableOneElevenRow = "AmountOfPages",
14 TableOneTwelveRow = "FilterItems", TableOneThirteenRow = "Link_location";
15 private final String TableTwoUsers = "Users", TableTwoFirstRow = "PLAYER_NAME", TableTwosecondRow = "UUID";
16
17 private boolean batchUpdateGoingOn = false;
18
19
20 public Connection connect() {
21
22
23 try {
24 File dbFile = new File(chestStorage, "database.db");
25 if (!dbFile.exists()) {
26 try {
27 dbFile.createNewFile();
28
29 //String url = "jdbc:sqlite:" + dbFile.getPath();
30 } catch (IOException ex) {
31 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "File write error: " + dbFile + ".db", ex);
32 }
33 }
34
35 if (this.connection != null && !this.connection.isClosed()) {
36 return this.connection;
37 }
38 Class.forName("org.sqlite.JDBC");
39 this.connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
40 return this.connection;
41
42
43 } catch (SQLException ex) {
44 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "SQLite exception on initialize", ex);
45 } catch (ClassNotFoundException ex) {
46 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "You need the SQLite JBDC library. Google it. Put it in /lib folder.");
47 }
48 return null;
49 }
50
51
52 public void load(String sql) {
53 this.connection = connect();
54 try {
55 Statement s = this.connection.createStatement();
56 s.executeUpdate(sql);
57 s.close();
58 } catch (SQLException e) {
59 e.printStackTrace();
60 }
61 initialize();
62 }
63/*
64 public Statement Statements() {
65 try {
66 connect().createStatement();
67 } catch (SQLException throwables) {
68 throwables.printStackTrace();
69 }
70 return statement;
71 }*/
72
73 public void disconnect() {
74 try {
75 if (connection != null) {
76 connection.close();
77 }
78 } catch (SQLException exception) {
79 exception.printStackTrace();
80 }
81 }
82
83
84 /*private void update(String sql) {
85 try {
86 connect().executeupdate(sql);
87 } catch (SQLException exception) {
88 exception.printStackTrace();
89 }
90 }
91
92 public ResultSet getResult(String sql) {
93 try {
94 return connect().executeQuery(sql);
95 } catch (SQLException exception) {
96 exception.printStackTrace();
97 }
98 return null;
99 }*/
100
101 public void createTables() {
102 String test;
103 String tableOne = "CREATE TABLE IF NOT EXISTS " + TableNameChest + " (" + TableOneFirstRow + " VARCHAR(64) PRIMARY KEY, " +
104 TableOnesecondRow + " VARCHAR(64) , " + TableOneThirdRow + " VARCHAR(64), " + TableOneFourthRow + " VARCHAR(64), " +
105 TableOneFifthRow + " VARCHAR(64), " + TableOneSixthRow + " VARCHAR(64), " + TableOneSeventhRow + " VARCHAR(64), " +
106 TableOneEighthRow + " VARCHAR(64), " + TableOneNineRow + " VARCHAR(64)," + TableOneTenRow + " VARCHAR(64)," +
107 TableOneElevenRow + " VARCHAR(64)," + TableOneTwelveRow + " VARCHAR(64)," + TableOneThirteenRow + " VARCHAR(64));";
108 String tableTwo = "CREATE TABLE IF NOT EXISTS " + TableTwoUsers + " (" + TableTwoFirstRow + " VARCHAR(64) , " + TableTwosecondRow + " VARCHAR(64) PRIMARY KEY);";
109 //Common.log(tableOne);
110
111
112 String[] tabels = {tableOne, tableTwo};
113
114 for (int i = 0; i < tabels.length; i++) {
115 test = tabels[i];
116 //System.out.println(test);
117 load(test);
118 }
119 }
120
121 //TODO Fix more of load code and cleanup/fixes?
122 public void LoadFromSQlite() {
123 ArrayList<Location> locationArrayList = new ArrayList<>();
124 PreparedStatement ps = null;
125 this.connection = connect();
126 ResultSet rs;
127 SerializedMap map = new SerializedMap();
128
129 try {
130 String sql = "SELECT * FROM " + TableNameChest;
131 ps = this.connection.prepareStatement(sql);
132 rs = ps.executeQuery();
133
134 while (rs.next()) {
135
136 Location location = SerializeUtil.deserializeLocation(rs.getObject(TableOneFirstRow));
137 UUID playerUUID = UUID.fromString(rs.getString(TableOnesecondRow));
138 boolean filterWhiteBlack = rs.getBoolean(TableOneThirdRow);
139 TypeOfContainer typeOfContainer = TypeOfContainer.valueOf(rs.getString(TableOneFourthRow));
140 int suctionRange = rs.getInt(TableOneFifthRow);
141 int linkRange = rs.getInt(TableOneSixthRow);
142 int linkAmount = rs.getInt(TableOneSeventhRow);
143 String nextUpgrade = rs.getString(TableOneEighthRow);
144 ItemStack[] chestItems = SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneNineRow));
145 String containerSettingsName = rs.getString(TableOneTenRow);
146 int numberOfPages = rs.getInt(TableOneElevenRow);
147 ItemStack[] filterItems = rs.getString(TableOneTwelveRow) != null ?
148 (SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneTwelveRow))) : (new ItemStack[]{(new ItemStack(Material.AIR))});
149 //Array LinkedLocations = rs.getArray(TableOneThirteenRow) != null ? rs.getArray(TableOneThirteenRow) : ((Array) new ArrayList<Location>());
150
151 //locationArrayList.addAll((Collection<? extends Location>) LinkedLocations.getArray());
152
153 ChestRegistry.getInstance().addChestToCacheFromSQL(location, new ChestData(playerUUID,
154 nextUpgrade, typeOfContainer, containerSettingsName, numberOfPages,
155 filterWhiteBlack, suctionRange, linkRange, linkAmount, filterItems, chestItems, null));
156
157 }
158 } catch (SQLException throwables) {
159 throwables.printStackTrace();
160 } finally {
161 try {
162 if (ps != null)
163 ps.close();
164 if (this.connection != null)
165 this.connection.close();
166 } catch (SQLException ex) {
167 ChestStorage.getInstance().getLogger().log(Level.SEVERE, Errors.sqlConnectionClose(), ex);
168 }
169 }
170 }
171
172 public void removeChestfromSQlites(Location location) {
173 List<String> sqls = new ArrayList<>();
174 String sql = "DELETE FROM " + TableNameChest + " WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
175
176 sqls.add(sql);
177 this.batchUpdate(sqls);
178 }
179
180 public void saveItemsToSQlites(Location location, ItemStack[] itemStack) {
181 List<String> sqls = new ArrayList<>();
182 String sql = "UPDATE " + TableNameChest + " SET " + TableOneNineRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
183
184 sqls.add(sql);
185 this.batchUpdate(sqls);
186 }
187
188 public void saveFilterListToSQlites(Location location, ItemStack[] itemStack) {
189 List<String> sqls = new ArrayList<>();
190 String sql = "UPDATE " + TableNameChest + " SET " + TableOneTwelveRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
191
192 sqls.add(sql);
193 this.batchUpdate(sqls);
194 }
195
196 @SneakyThrows
197 public void saveDataOnBlockPlace(ChestData chestData, Location location) {
198 List<String> sqls = new ArrayList<>();
199
200 String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
201 + "," + TableOnesecondRow + "," + TableOneThirdRow
202 + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
203 + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
204 + "," + TableOneTenRow + "," + TableOneElevenRow
205 + ") VALUES('" + SerializeUtil.serializeLoc(location)
206 + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
207 + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
208 + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
209 + "','" + chestData.getNextUpgrade()
210 + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
211 + "','" + chestData.getContainerSettingsName() + "','" + chestData.getNumberOfPages() + "')";
212 sqls.add(sql);
213
214
215 sqls.add(sql);
216
217 //System.out.println(sqls);
218 this.batchUpdate(sqls);
219 }
220
221 //TODO Make data values more cleaner.
222 @SneakyThrows
223 public void saveToSQlites(Map<Location, ChestData> chests) {
224 List<String> sqls = new ArrayList<>();
225 for (Map.Entry<Location, ChestData> entry : chests.entrySet()) {
226 Location location = entry.getKey();
227 ChestData chestData = entry.getValue();
228
229 String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
230 + "," + TableOnesecondRow + "," + TableOneThirdRow
231 + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
232 + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
233 + "," + TableOneTenRow + "," + TableOneElevenRow + "," + TableOneTwelveRow + "," + TableOneThirteenRow
234 + ") VALUES('" + SerializeUtil.serializeLoc(location)
235 + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
236 + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
237 + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
238 + "','" + chestData.getNextUpgrade()
239 + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
240 + "','" + chestData.getContainerSettingsName() + "','" + chestData.getNumberOfPages()
241 + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getFilterItems())
242 + "','" + chestData.getLocations() + "')";
243
244
245 sqls.add(sql);
246 }
247 //System.out.println(sqls);
248 this.batchUpdate(sqls);
249 }
250
251 protected final void batchUpdate(@NonNull List<String> sqls) {
252 this.connection = connect();
253
254 if (sqls.size() == 0)
255 return;
256 try {
257 final Statement batchStatement = this.connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
258 final int processedCount = sqls.size();
259
260 // Prevent automatically sending db instructions
261 this.connection.setAutoCommit(false);
262
263
264 for (final String sql : sqls)
265 batchStatement.addBatch(sql);
266 if (processedCount > 10_000)
267 Common.log("Updating your database (" + processedCount + " entries)... PLEASE BE PATIENT THIS WILL TAKE "
268 + (processedCount > 50_000 ? "10-20 MINUTES" : "5-10 MINUTES") + " - If server will print a crash report, ignore it, update will proceed.");
269
270 // Set the flag to start time notifications timer
271 batchUpdateGoingOn = true;
272
273 // Notify console that progress still is being made
274 new Timer().scheduleAtFixedRate(new TimerTask() {
275
276 @Override
277 public void run() {
278 if (batchUpdateGoingOn)
279 Common.log("Still executing, DO NOT SHUTDOWN YOUR SERVER.");
280 else
281 cancel();
282 }
283 }, 1000 * 30, 1000 * 30);
284
285 // Execute
286 batchStatement.executeBatch();
287
288 // This will block the thread
289 this.connection.commit();
290
291 //Common.log("Updated " + processedCount + " database entries.");
292
293 } catch (final Throwable t) {
294 t.printStackTrace();
295
296 } finally {
297 try {
298 this.connection.setAutoCommit(true);
299
300 } catch (final SQLException ex) {
301 ex.printStackTrace();
302 }
303
304 // Even in case of failure, cancel
305 batchUpdateGoingOn = false;
306 }
307 }
308
309
310 public void initialize() {
311 try {
312 PreparedStatement preparedStatement = this.connection.prepareStatement("SELECT * FROM " + TableNameChest + " WHERE " + TableOnesecondRow + " = ?");
313 ResultSet resultSet = preparedStatement.executeQuery();
314 close(preparedStatement, resultSet);
315
316 } catch (SQLException ex) {
317 ChestStorage.getInstance().getLogger().log(Level.SEVERE, "Unable to retreive connection", ex);
318 }
319 }
320
321 public void close(PreparedStatement preparedStatement, ResultSet resultSet) {
322 try {
323 if (preparedStatement != null)
324 preparedStatement.close();
325 if (resultSet != null)
326 resultSet.close();
327 } catch (SQLException ex) {
328 ex.printStackTrace();
329 /*Error.close(ChestStorage.getInstance(), ex);*/
330 }
331 }
332
333}
334