· 7 years ago · Feb 24, 2019, 09:56 AM
1package me.mrcookies.helper.database;
2
3import com.zaxxer.hikari.HikariConfig;
4import com.zaxxer.hikari.HikariDataSource;
5import me.mrcookies.helper.utils.References;
6
7import java.sql.Connection;
8import java.sql.PreparedStatement;
9import java.sql.ResultSet;
10import java.sql.SQLException;
11import java.util.Objects;
12
13public class MySQL {
14
15 private static final HikariConfig hcConfig = new HikariConfig();
16 private static final HikariDataSource hds;
17
18 static {
19 hcConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
20 hcConfig.setJdbcUrl("jdbc:mysql://" + References.host + ":" + References.port + "/" + References.database); // + "?autoReconnect=true&useLegacyDatetimeCode=false&serverTimezone=UTC"
21 hcConfig.setUsername(References.username);
22 hcConfig.setPassword(References.password);
23 hcConfig.addDataSourceProperty("cachePrepStmts", "true");
24 hcConfig.addDataSourceProperty("prepStmtCacheSize", "250");
25 hcConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
26 hcConfig.setConnectionTimeout(30000);
27 hcConfig.setMaximumPoolSize(50);
28 hcConfig.setLeakDetectionThreshold(60 * 1000);
29 hcConfig.setAutoCommit(true);
30 hds = new HikariDataSource(hcConfig);
31 }
32
33 private static Connection getConnection() {
34 try {
35 return hds.getConnection();
36 } catch (SQLException e) {
37 e.printStackTrace();
38 }
39 return null;
40 }
41
42 public void initialize() {
43
44 Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");
45
46 try {
47 connection.prepareStatement("SELECT 1 FROM members LIMIT 1").executeQuery().close();
48 connection.close();
49 } catch (final SQLException e) {
50 try {
51 PreparedStatement ps = connection.prepareStatement(
52 "CREATE TABLE IF NOT EXISTS `members` (\n" +
53 " `id` int auto_increment primary key,\n" +
54 " `username` text,\n" +
55 " `id_long` bigint\n" +
56 ") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
57 );
58
59 ps.executeUpdate();
60 ps.close();
61 connection.close();
62 System.out.println("Helper > Database structure created...");
63 } catch (final SQLException e1) {
64 e1.printStackTrace();
65
66 try {
67 connection.close();
68 } catch (SQLException e2) {
69 e2.printStackTrace();
70 }
71 }
72 }
73 }
74
75
76 public String getString(String table, String key, String where, String value) {
77 Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");
78
79 try {
80 final PreparedStatement ps = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
81 ps.setString(1, value);
82 final ResultSet rs = ps.executeQuery();
83 if (rs.next()) {
84 String result = rs.getString(key);
85 rs.close();
86 ps.close();
87 connection.close();
88 return result;
89 }
90 connection.close();
91 } catch (final SQLException e) {
92 e.printStackTrace();
93
94 try {
95 connection.close();
96 } catch (SQLException e1) {
97 e1.printStackTrace();
98 }
99 }
100 return null;
101 }
102
103 public boolean getBool(String table, String key, String where, String value) {
104 Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");
105
106 try {
107 final PreparedStatement ps = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
108 ps.setString(1, value);
109 final ResultSet rs = ps.executeQuery();
110 if (rs.next()) {
111 boolean result = rs.getBoolean(key);
112 rs.close();
113 ps.close();
114 connection.close();
115 return result;
116 }
117 connection.close();
118 } catch (final SQLException e) {
119 e.printStackTrace();
120
121 try {
122 connection.close();
123 } catch (SQLException e1) {
124 e1.printStackTrace();
125 }
126 }
127 return false;
128 }
129
130 public MySQL setString(String table, String key, String value, String where, String wherevalue) {
131 Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");
132 try {
133 PreparedStatement ps;
134 final PreparedStatement check = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
135 check.setString(1, wherevalue);
136
137 if (check.executeQuery().next()) {
138 ps = connection.prepareStatement(String.format("UPDATE %s SET %s = ? WHERE %s = ?", table, key, where));
139 ps.setString(1, value);
140 ps.setString(2, wherevalue);
141 } else {
142 ps = connection.prepareStatement(String.format("INSERT INTO %s (%s, %s) VALUES (?, ?)", table, where, key));
143 ps.setString(1, wherevalue);
144 ps.setString(2, value);
145 check.close();
146 }
147
148 ps.execute();
149 ps.close();
150 connection.close();
151 } catch (final SQLException e) {
152 e.printStackTrace();
153
154 try {
155 connection.close();
156 } catch (SQLException e1) {
157 e1.printStackTrace();
158 }
159 }
160
161 return this;
162 }
163
164 public MySQL setBool(String table, String key, boolean value, String where, String wherevalue) {
165 Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");
166
167 try {
168 PreparedStatement ps;
169 final PreparedStatement check = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
170 check.setString(1, wherevalue);
171
172 if (check.executeQuery().next()) {
173 ps = connection.prepareStatement(String.format("UPDATE %s SET %s = ? WHERE %s = ?", table, key, where));
174 ps.setInt(1, value ? 1 : 0);
175 ps.setString(2, wherevalue);
176 } else {
177 ps = connection.prepareStatement(String.format("INSERT INTO %s (%s, %s) VALUES (?, ?)", table, where, key));
178 ps.setString(1, wherevalue);
179 ps.setInt(2, value ? 1 : 0);
180 }
181
182 check.close();
183
184 ps.execute();
185 ps.close();
186 connection.close();
187 } catch (final SQLException e) {
188 e.printStackTrace();
189
190 try {
191 connection.close();
192 } catch (SQLException e1) {
193 e1.printStackTrace();
194 }
195 }
196
197 return this;
198 }
199
200 public MySQL dropEntry(String table, String where, String wherevalue) {
201 Connection connection = Objects.requireNonNull(getConnection(), "SQL Connection is null");
202
203 try {
204 PreparedStatement ps;
205 final PreparedStatement check = connection.prepareStatement(String.format("SELECT * FROM %s WHERE %s = ?", table, where));
206 check.setString(1, wherevalue);
207
208 if (check.executeQuery().next()) {
209 ps = connection.prepareStatement(String.format("DELETE FROM %s WHERE %s = ?", table, where));
210 ps.setString(1, wherevalue);
211 ps.execute();
212 ps.close();
213 }
214
215 check.close();
216 connection.close();
217 } catch (final SQLException e) {
218 e.printStackTrace();
219
220 try {
221 connection.close();
222 } catch (SQLException e1) {
223 e1.printStackTrace();
224 }
225 }
226
227 return this;
228 }
229
230}