· 5 years ago · Jun 16, 2020, 03:38 PM
1package com.foxxite.multicharacter.sql;
2
3import com.foxxite.multicharacter.MultiCharacter;
4
5import javax.sql.rowset.CachedRowSet;
6import java.io.File;
7import java.io.IOException;
8import java.sql.*;
9import java.util.ArrayList;
10import java.util.HashMap;
11import java.util.logging.Level;
12import java.util.logging.LogRecord;
13
14public class SQLHandler {
15
16 private final MultiCharacter plugin;
17 private final File sqlFile;
18 private Connection conn;
19
20 public SQLHandler(MultiCharacter plugin) {
21
22 this.plugin = plugin;
23
24 sqlFile = new File(this.plugin.getDataFolder(), "storage.db");
25
26 if (!sqlFile.exists()) {
27 sqlFile.getParentFile().mkdirs();
28 try {
29 sqlFile.createNewFile();
30 } catch (IOException e) {
31 plugin.getPluginLogger().log(new LogRecord(Level.SEVERE, e.getMessage() + " " + e.getCause()));
32 e.printStackTrace();
33 }
34 }
35
36 connect();
37 setupDatabase();
38 }
39
40 private void connect() {
41
42 try {
43 // db parameters
44 String url = "jdbc:sqlite:" + sqlFile.getAbsolutePath();
45 // create a connection to the database
46 conn = DriverManager.getConnection(url);
47
48 plugin.getPluginLogger().log(new LogRecord(Level.INFO, "Connection to SQLite has been established."));
49
50 } catch (SQLException e) {
51 System.out.println(e.getMessage());
52 }
53 }
54
55 public void closeConnection() {
56 try {
57 if (conn != null) {
58 conn.close();
59 }
60 } catch (SQLException e) {
61 plugin.getLogger().log(new LogRecord(Level.SEVERE, e.getMessage() + " " + e.getCause()));
62 e.printStackTrace();
63 }
64 }
65
66 private void setupDatabase() {
67
68 ArrayList<String> tables = new ArrayList<>();
69
70 tables.add(
71 "CREATE TABLE IF NOT EXISTS \"Vault\" (\n" +
72 "\t\"CharacterUUID\"\tTEXT UNIQUE,\n" +
73 "\t\"Balance\"\tREAL,\n" +
74 "\t\"Group\"\tTEXT,\n" +
75 "\tPRIMARY KEY(\"CharacterUUID\")\n" +
76 ");"
77 );
78
79 tables.add(
80 "CREATE TABLE IF NOT EXISTS \"Characters\" (\n" +
81 "\t\"UUID\"\tTEXT UNIQUE,\n" +
82 "\t\"OwnerUUID\"\tTEXT,\n" +
83 "\t\"Name\"\tTEXT,\n" +
84 "\t\"Birthday\"\tTEXT,\n" +
85 "\t\"Nationality\"\tREAL,\n" +
86 "\t\"Sex\"\tTEXT,\n" +
87 "\t\"Skin\"\tTEXT,\n" +
88 "\t\"Texture\"\tTEXT,\n" +
89 "\t\"Signature\"\tTEXT,\n" +
90 "\t\"Deleted\"\tINTEGER DEFAULT 0,\n" +
91 "\tPRIMARY KEY(\"UUID\")\n" +
92 ");"
93 );
94
95 tables.add(
96 "CREATE TABLE IF NOT EXISTS \"Inventories\" (\n" +
97 "\t\"CharacterUUID\"\tTEXT UNIQUE,\n" +
98 "\t\"Contents\"\tTEXT,\n" +
99 "\t\"Health\"\tREAL DEFAULT 20,\n" +
100 "\t\"Hunger\"\tINTEGER DEFAULT 20,\n" +
101 "\t\"EXP\"\tREAL DEFAULT 0,\n" +
102 "\t\"EXPLevel\"\tINTEGER DEFAULT 0,\n" +
103 "\tPRIMARY KEY(\"CharacterUUID\")\n" +
104 ");"
105 );
106
107 tables.add(
108 "CREATE TABLE IF NOT EXISTS \"LogoutLocations\" (\n" +
109 "\t\"ID\"\tINTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,\n" +
110 "\t\"CharacterUUID\"\tTEXT,\n" +
111 "\t\"World\"\tTEXT,\n" +
112 "\t\"X\"\tREAL,\n" +
113 "\t\"Y\"\tREAL,\n" +
114 "\t\"Z\"\tREAL,\n" +
115 "\t\"Yaw\"\tREAL,\n" +
116 "\t\"Pitch\"\tREAL\n" +
117 ");"
118 );
119
120 try {
121 Statement stmt = conn.createStatement();
122 // create a new table
123 for (String createQuery : tables) {
124 stmt.execute(createQuery);
125 }
126
127 } catch (Exception e) {
128 plugin.getLogger().log(new LogRecord(Level.SEVERE, e.getMessage() + " " + e.getCause()));
129 e.printStackTrace();
130 }
131 }
132
133 public void executeUpdateQuery(String query) {
134 try {
135 Statement stmt = conn.createStatement();
136 stmt.executeUpdate(query);
137 } catch (Exception e) {
138 plugin.getPluginLogger().severe(e.getMessage() + " " + e.getCause());
139 plugin.getPluginLogger().info("Query: " + query);
140
141 if (e instanceof SQLException) {
142 SQLException sqlException = (SQLException) e;
143 plugin.getPluginLogger().info(sqlException.getSQLState() + " " + sqlException.getErrorCode());
144 }
145
146 e.printStackTrace();
147 }
148 }
149
150 public HashMap<Integer, HashMap<String, Object>> executeQuery(String query, HashMap<String, String> columns) {
151 try {
152 Statement stmt = conn.createStatement();
153 ResultSet resultSet = stmt.executeQuery(query);
154
155 HashMap<Integer, HashMap<String, Object>> rows = new HashMap<>();
156
157 int rowCount = 0;
158 while (resultSet.next()) {
159
160 HashMap<String, Object> resultRows = new HashMap<>();
161
162 columns.forEach((columnName, columnType) -> {
163
164 try {
165 switch (columnName) {
166 case "int":
167 case "integer":
168 resultRows.put(columnName, resultSet.getInt(columnName));
169 break;
170 case "float":
171 resultRows.put(columnName, resultSet.getFloat(columnName));
172 break;
173 case "double":
174 resultRows.put(columnName, resultSet.getDouble(columnName));
175 break;
176 case "string":
177 resultRows.put(columnName, resultSet.getString(columnName));
178 break;
179 case "long":
180 resultRows.put(columnName, resultSet.getLong(columnName));
181 break;
182 default:
183 resultRows.put(columnName, resultSet.getObject(columnName));
184 break;
185 }
186 } catch (SQLException throwables) {
187 throwables.printStackTrace();
188 }
189
190
191 });
192
193 rows.put(rowCount, resultRows);
194
195 // Process the row.
196 rowCount++;
197 }
198 return rows;
199 } catch (Exception e) {
200 plugin.getLogger().log(new LogRecord(Level.SEVERE, e.getMessage() + " " + e.getCause()));
201 e.printStackTrace();
202 }
203
204 return null;
205 }
206
207 public int getRows(ResultSet rs) throws SQLException {
208
209 CachedRowSet localResult = (CachedRowSet) rs;
210
211 int rowCount = 0;
212 while (localResult.next()) {
213 // Process the row.
214 rowCount++;
215 }
216 return rowCount;
217 }
218}