· 5 years ago · Jun 04, 2020, 09:06 PM
1package me.walter;
2
3import java.sql.Connection;
4import java.sql.PreparedStatement;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.sql.Statement;
8
9import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
10import com.zaxxer.hikari.HikariConfig;
11import com.zaxxer.hikari.HikariDataSource;
12
13public class MySQL<PreparedStatement>
14{
15 private Connection connection;
16 public HikariConfig mysqlDataSource;
17 public static HikariDataSource ds;
18
19 public void connect(String user, String password)
20 {
21 HikariConfig hikariConfig = new HikariConfig();
22 hikariConfig.setJdbcUrl("jdbc:mysql://localhost:3306/"+"faz"+"?useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC");
23 hikariConfig.setUsername(user);
24 hikariConfig.setPassword(password);
25 hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
26 hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
27 hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
28 hikariConfig.addDataSourceProperty("autoReconnect", true);
29 hikariConfig.addDataSourceProperty("cachePrepStmts", true);
30 hikariConfig.addDataSourceProperty("prepStmtCacheSize", 250);
31 hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
32 hikariConfig.addDataSourceProperty("useServerPrepStmts", true);
33 hikariConfig.addDataSourceProperty("cacheResultSetMetadata", true);
34 ds = new HikariDataSource(hikariConfig);
35 }
36
37 public static HikariDataSource getDs() {
38 return ds;
39 }
40
41
42// public void createDatabase()
43// {
44// try {
45// String table = "CREATE DATABASE pets IF NOT EXISTS;" ;
46// PreparedStatement statement = mysqlDataSource.getConnection().prepareStatement(table);
47// statement.executeQuery();
48// statement.close();
49// } catch (SQLException e) {
50// e.printStackTrace();
51// }
52// }
53 public void createTable()
54 {
55 try {
56 // String table = "CREATE TABLE pets(name varchar(20), petname varchar(20), primary key (name)); ";
57
58 String table = "CREATE TABLE IF NOT EXISTS pets(" +
59 "name VARCHAR(255)," +
60 "petname VARCHAR(255)," +
61 "pettype VARCHAR(255)," +
62 "petdisplayname VARCHAR(50)," +
63 "resistance TINYINT(1)," +
64 "fireresistance TINYINT(1)," +
65 "swiftness TINYINT(1)," +
66 "strenght TINYINT(1)," +
67 "PRIMARY KEY (name));";
68 Connection connection = getDs().getConnection();
69 java.sql.PreparedStatement statement = connection.prepareStatement(table);
70 statement.execute();
71 statement.close();
72 } catch (SQLException e) {
73 // TODO Auto-generated catch block
74 e.printStackTrace();
75 }
76 }
77
78
79 public void insertData(String name, String petname, String type, String displayname)
80 {
81 String table = "INSERT INTO pets VALUES('" + name + "', '"+ petname +"', '"+ type + "', '" + displayname + "' ,0 ,0 ,0 ,0);";
82
83 try {
84 Connection connection = getDs().getConnection();
85 java.sql.PreparedStatement statement1 = connection.prepareStatement(table);
86 statement1.execute();
87 statement1.close();
88
89 } catch (SQLException e) {
90 e.printStackTrace();
91 }
92
93 }
94 public String getDataName(String name){
95 String table = "SELECT name FROM pets WHERE petname = '"+ name + "';";
96 Statement preparedStatement;
97 String result = " ";
98
99 try {
100 Connection connection = getDs().getConnection();
101 preparedStatement = connection.createStatement();
102 ResultSet rs = preparedStatement.executeQuery(table);
103 if(rs.next()) {
104 result = rs.getString("name");
105 System.out.println(result);
106 preparedStatement.close();
107 return result;}
108 return result;
109
110
111 } catch (SQLException e) {
112 e.printStackTrace();
113 }
114 return result;
115
116 }
117
118 public String getDataDisplayname(String name){
119 String table = "SELECT petdisplayname FROM pets WHERE petname = 'Pet_di_"+ name + "';";
120 Statement preparedStatement;
121 String result = " ";
122
123 try {
124 Connection connection = getDs().getConnection();
125 preparedStatement = connection.createStatement();
126 ResultSet rs = preparedStatement.executeQuery(table);
127 if(rs.next()) {
128 result = rs.getString("petdisplayname");
129 preparedStatement.close();
130 return result;}
131 return result;
132
133
134 } catch (SQLException e) {
135 e.printStackTrace();
136 }
137 return result;
138
139 }
140
141 public String getDataPetName(String name){
142 String table = "SELECT petname FROM pets WHERE name = '"+ name + "';";
143 Statement preparedStatement;
144 String result ;
145
146 try {
147 Connection connection = getDs().getConnection();
148 preparedStatement = connection.createStatement();
149 ResultSet rs = preparedStatement.executeQuery(table);
150 if (rs.next()) {
151 result = rs.getString("petname");
152 return result;
153 }
154 } catch (SQLException e) {
155 e.printStackTrace();
156 }
157
158 return " ";
159 }
160
161 public String getDataPetDisplayName(String name){
162 String table = "SELECT petdisplayname FROM pets WHERE name = '"+ name + "';";
163 Statement preparedStatement;
164 String result ;
165
166 try {
167 Connection connection = getDs().getConnection();
168 preparedStatement = connection.createStatement();
169 ResultSet rs = preparedStatement.executeQuery(table);
170 if (rs.next()) {
171 result = rs.getString("petdisplayname");
172 return result;
173 }
174 } catch (SQLException e) {
175 e.printStackTrace();
176 }
177
178 return " ";
179 }
180
181
182 public void closeConnection()
183 {
184 try {
185 connection.close();;
186 } catch (SQLException e) {
187 e.printStackTrace();
188 }
189 }
190 public boolean checkHasPet(String name){
191 String has = "SELECT * FROM pets WHERE name = '"+name+"';";
192 PreparedStatement preparedStatement;
193
194 try {
195 Connection connection = getDs().getConnection();
196 preparedStatement = (PreparedStatement) connection.prepareStatement(has);
197 // preparedStatement.setString(1,name);
198 ResultSet rs = ((java.sql.PreparedStatement) preparedStatement).executeQuery();
199 return rs.next();
200 } catch (SQLException e) {
201 e.printStackTrace();
202 }
203
204 return false;
205 }
206
207
208 public boolean checkCompatibility(String name, String petname){
209 String table = "SELECT * FROM pets WHERE name ='"+name+"' and petname ='"+ petname+"';";
210 PreparedStatement st;
211
212 try {
213 Connection connection = getDs().getConnection();
214 st = (PreparedStatement) connection.prepareStatement(table);
215 ResultSet rs = ((java.sql.PreparedStatement) st).executeQuery();
216 return rs.next();
217 } catch (SQLException e) {
218 e.printStackTrace();
219 }
220
221 return false;
222 }
223
224 public void updateDataPetDisplayName(String newname, String pname){
225 String table = "UPDATE pets SET petdisplayname='"+ newname +"' WHERE petname = 'Pet_di_"+pname+"';";
226 PreparedStatement pst;
227 try {
228 Connection connection = getDs().getConnection();
229 pst=(PreparedStatement) connection.prepareStatement(table);
230 ((java.sql.PreparedStatement) pst).execute();
231 }
232 catch (SQLException e) {
233 e.printStackTrace();
234 }
235
236 }
237
238 public int getResistanceValue(String name)
239 {
240 String table = "SELECT resistance FROM pets WHERE name ='"+name+"';";
241 Statement pst;
242 try {
243 Connection connection = getDs().getConnection();
244
245 pst = connection.createStatement();
246 ResultSet rs = pst.executeQuery(table);
247 while(rs.next()) {;
248 return rs.getInt("resistance");}
249 pst.close();
250 rs.close();
251
252 } catch (SQLException e) {
253 // TODO Auto-generated catch block
254 e.printStackTrace();
255 }
256 return -1;
257
258 }
259
260 public int getStrenghtValue(String name)
261 {
262 String table = "SELECT strenght FROM pets WHERE name ='"+name+"';";
263 Statement pst;
264 try {
265 Connection connection = getDs().getConnection();
266
267 pst = connection.createStatement();
268 ResultSet rs = pst.executeQuery(table);
269 while(rs.next()) {
270 return rs.getInt("strenght");}
271 pst.close();
272 rs.close();
273
274 } catch (SQLException e) {
275 // TODO Auto-generated catch block
276 e.printStackTrace();
277 }
278 return -1;
279
280 }
281 public int getFireResistanceValue(String name)
282 {
283 String table = "SELECT fireresistance FROM pets WHERE name ='"+name+"';";
284 Statement pst;
285 try {
286 Connection connection = getDs().getConnection();
287
288 pst = connection.createStatement();
289 ResultSet rs = pst.executeQuery(table);
290 while(rs.next()) {
291 return rs.getInt("fireresistance");}
292 pst.close();
293 rs.close();
294
295 } catch (SQLException e) {
296 // TODO Auto-generated catch block
297 e.printStackTrace();
298 }
299 return -1;
300
301 }
302 public int getSwiftnessValue(String name)
303 {
304 String table = "SELECT swiftness FROM pets WHERE name ='"+name+"';";
305 Statement pst;
306 try {
307 Connection connection = getDs().getConnection();
308
309 pst = connection.createStatement();
310 ResultSet rs = pst.executeQuery(table);
311 while(rs.next()) {
312 return rs.getInt("swiftness");}
313 pst.close();
314 rs.close();
315
316 } catch (SQLException e) {
317 // TODO Auto-generated catch block
318 e.printStackTrace();
319 }
320 return -1;
321
322 }
323
324 public void updateEffects(String enchant, int value, String name){
325 String table = "UPDATE pets SET "+ enchant + "="+ value +" WHERE name = '"+name+"';";
326 PreparedStatement pst;
327 try {
328 Connection connection = getDs().getConnection();
329 pst=(PreparedStatement) connection.prepareStatement(table);
330 ((java.sql.PreparedStatement) pst).execute();
331
332 }
333 catch (SQLException e) {
334 e.printStackTrace();
335 }
336
337 }
338
339
340}