· 10 years ago · Apr 16, 2016, 09:48 PM
1
2import net.astrocoder.dev.Core;
3import net.astrocoder.dev.core.PlayerProfile;
4
5import java.sql.*;
6import java.util.*;
7import java.util.Date;
8import java.util.logging.Level;
9
10public class Database {
11
12 String user = "";
13 String database = "";
14 String password = "";
15 String port = "";
16 String hostname = "";
17 Connection c = null;
18
19 public Database(String user, String password, String database, String port, String hostname) {
20 this.user = user;
21 this.database = database;
22 this.password = password;
23 if(port == null){
24 this.port = "3306";
25 }else{
26 this.port = port;
27 }
28 this.hostname = hostname;
29 }
30
31 /**
32 * Open mysql connection
33 * @return
34 */
35 public Connection open() {
36 try {
37 Class.forName("com.mysql.jdbc.Driver");
38 c = DriverManager.getConnection("jdbc:mysql://" + hostname + ":" + port + "/" + database + "?autoReconnect=true", user, password);
39 System.out.println("MySQL connection successful");
40 Core.getInstance().getLogger().log(Level.INFO, "MySQL connection successful");
41 return c;
42 } catch (SQLException e) {
43 Core.getInstance().getLogger().log(Level.SEVERE, "MySQL connection has been aborted");
44 Core.getInstance().getLogger().log(Level.SEVERE, "Reason:" + e.getMessage());
45 e.printStackTrace();
46 } catch (ClassNotFoundException e) {
47 System.out.println("JDBC Driver not found!");
48 }
49 return c;
50 }
51
52 public Connection getConnection() {
53 return this.c;
54 }
55
56 /**
57 * Close mysql connection
58 */
59 public Connection close(Connection c){
60 try{
61 c.close();
62 } catch (SQLException e) {
63 Core.getInstance().getLogger().log(Level.SEVERE, "MySQL can't close connection");
64 e.printStackTrace();
65 }
66 return c;
67 }
68
69 /**
70 * Get all tables together and setup one by one
71 */
72 public void setupTables(){
73 setupProfiles();
74 }
75
76 /**
77 * Setup the hub player profiles
78 */
79 public void setupProfiles(){
80 try {
81 PreparedStatement profiles = c
82 .prepareStatement("CREATE TABLE IF NOT EXISTS `Core`(`id` int(11) NOT NULL auto_increment,"
83 + " `UUID` varchar(255) NOT NULL,"
84 + " `PlayTime` int(255) NOT NULL,"
85 + " `Coins` bigint(255) NOT NULL,"
86 + " `Spins` bigint(255) NOT NULL,"
87 + "`PlayersVisible` BOOLEAN NOT NULL,"
88 + "`Chat` BOOLEAN NOT NULL,"
89 + "`ParticlesEnable` BOOLEAN NOT NULL,"
90 + "`NumberJoins` int(10) NOT NULL,"
91 + "`NumberKicks` int(10) NOT NULL,"
92 + "`Level` bigint(155) NOT NULL,"
93 + " PRIMARY KEY(`id`));");
94 profiles.execute();
95 profiles.close();
96 } catch (Exception e) {
97 e.printStackTrace();
98 }
99 }
100
101 /**
102 * Try to create a new player profile
103 * @param uuid
104 * @return
105 */
106 public boolean createPlayerProfile(UUID uuid){
107 try
108 {
109 String coreStatement = "SELECT * FROM Core WHERE UUID = '" + uuid + "';";
110 ResultSet result = this.c.createStatement().executeQuery(coreStatement);
111 if (result.next()) {
112 //PlayerDat.addPlayerDat(new PlayerDat(uuid, new Date(), result.getLong("PlayTime"), result.getBoolean("PlayersVisible"), result.getBoolean("Chat"), result.getInt("Points"), result.getInt("NumberBans"), result.getInt("NumberJoins"), result.getInt("NumberKicks"), result.getInt("NumberReports")));
113 PlayerProfile.addPlayerProfile(new PlayerProfile(
114 uuid,
115 new Date(),
116 result.getLong("PlayTime"),
117 result.getLong("Coins"),
118 result.getLong("Spins"),
119 result.getBoolean("PlayersVisible"),
120 result.getBoolean("Chat"),
121 result.getBoolean("ParticlesEnable"),
122 result.getInt("NumberJoins"),
123 result.getInt("NumberKicks"),
124 result.getInt("Level")));
125
126 } else {
127 //PlayerDat.addPlayerDat(new PlayerDat(uuid, new Date(), 0L, true, true, 0, 0, 1, 0, 0));
128 PlayerProfile.addPlayerProfile(new PlayerProfile(uuid, new Date(), 0L,0L,0L,true,true,true,0,0,0));
129 }
130 }
131 catch (SQLException e)
132 {
133 System.out.println("Error while trying to create PlayerDat! Reason: " + e.getMessage());
134 return false;
135 }
136 return true;
137 }
138
139 /**
140 * Update all data from player profile to the sql database
141 * @param playerProfile
142 * @return
143 */
144 public boolean updatePlayerProfileSQL(PlayerProfile playerProfile)
145 {
146 try
147 {
148 /*
149 PlayerProfile.addPlayerProfile(new PlayerProfile(
150 uuid,
151 new Date(),
152 result.getLong("PlayTime"),
153 result.getLong("Coins"),
154 result.getLong("Spins"),
155 result.getBoolean("PlayersVisible"),
156 result.getBoolean("Chat"),
157 result.getBoolean("ParticlesEnable"),
158 result.getInt("NumberJoins"),
159 result.getInt("NumberKicks")));
160 */
161 String coreStatement = "SELECT * FROM Core WHERE UUID = '" + playerProfile.getUUID() + "'";
162 ResultSet result = this.c.createStatement().executeQuery(coreStatement);
163 if (result.next())
164 {
165 String coreUpdate = "UPDATE Core SET PlayTime = " + playerProfile.getPlayTime()
166 + ", Coins = " + playerProfile.getCoins()
167 + ", Spins = " + playerProfile.getSpins()
168 + ", PlayersVisible = " + playerProfile.isPlayersVisible()
169 + ", Chat = " + playerProfile.hasChat()
170 + ", ParticlesEnable = " + playerProfile.isParticles()
171 + ", NumberJoins = " + playerProfile.getNumberJoins()
172 + ", Level = " + playerProfile.getLevel()
173 + ", NumberKicks = " + playerProfile.getNumberKicks() + " " //Caso de erro ver aqui, falta virgula
174 + " WHERE UUID = '"
175 + playerProfile.getUUID() + "';";
176 this.c.createStatement().executeUpdate(coreUpdate);
177 }
178 else
179 {
180 String coreUpdate = "INSERT INTO Core (UUID, PlayTime, Coins, Spins, PlayersVisible, Chat, ParticlesEnable, NumberJoins, NumberKicks, Level) VALUES ('"
181 + playerProfile.getUUID() + "', "
182 + playerProfile.getPlayTime() + ", "
183 + playerProfile.getCoins() + ", "
184 + playerProfile.getSpins() + ", "
185 + playerProfile.isPlayersVisible() + ", "
186 + playerProfile.hasChat() + ", "
187 + playerProfile.isParticles() + ", "
188 + playerProfile.getNumberJoins() + ", "
189 + playerProfile.getNumberKicks() + ", "
190 + playerProfile.getLevel() + "); ";
191 this.c.createStatement().executeUpdate(coreUpdate);
192 }
193 }
194 catch (SQLException e)
195 {
196 System.out.println("Error while trying to update the MySQL! Reason: " + e.getMessage());
197 return false;
198 }
199 return true;
200 }
201
202 /**
203 * Create a sql query
204 * @param query
205 * @return
206 */
207 public ResultSet querySQL(String query) {
208 Connection c = null;
209
210 if (checkConnection()) {
211 c = getConnection();
212 } else {
213 c = open();
214 }
215
216 Statement s = null;
217
218 try {
219 s = c.createStatement();
220 } catch (SQLException e1) {
221 e1.printStackTrace();
222 }
223
224 ResultSet ret = null;
225
226 try {
227 ret = s.executeQuery(query);
228 } catch (SQLException e) {
229 e.printStackTrace();
230 }
231
232 close(c);
233
234 return ret;
235 }
236
237 /**
238 * Check is there are connection
239 * @return
240 */
241 private boolean checkConnection() {
242 return c != null;
243 }
244
245 /**
246 * Update sql query
247 * @param update
248 */
249 public void updateSQL(String update) {
250
251 Connection c = null;
252
253 if (checkConnection()) {
254 c = getConnection();
255 } else {
256 c = open();
257 }
258
259 Statement s = null;
260
261 try {
262 s = c.createStatement();
263 s.executeUpdate(update);
264 } catch (SQLException e1) {
265 e1.printStackTrace();
266 }
267
268 open();
269
270 }
271
272 /**
273 * Get Coins Top
274 * @return
275 * @throws SQLException
276 */
277 public List<String> getCoinsTopPlayers() throws SQLException{
278 List<String> result = new ArrayList<String>();
279 Statement s = c.createStatement();
280 if(s != null){
281 ResultSet rs = s.executeQuery("SELECT UUID FROM `Core` ORDER BY `Coins`");
282 rs.findColumn("UUID");
283 if(rs.last()){
284 result.add(rs.getString("UUID"));
285 }if(rs.previous()){
286 result.add(rs.getString("UUID"));
287 }if(rs.previous()){
288 result.add(rs.getString("UUID"));
289 }
290 }
291 return result;
292 }
293
294 /**
295 * Get Spins Top
296 * @return
297 * @throws SQLException
298 */
299 public List<String> getSpinsTopPlayers() throws SQLException{
300 List<String> result = new ArrayList<String>();
301 Statement s = c.createStatement();
302 if(s != null){
303 ResultSet rs = s.executeQuery("SELECT UUID FROM `Core` ORDER BY `Spins`");
304 rs.findColumn("UUID");
305 if(rs.last()){
306 result.add(rs.getString("UUID"));
307 }if(rs.previous()){
308 result.add(rs.getString("UUID"));
309 }if(rs.previous()){
310 result.add(rs.getString("UUID"));
311 }
312 }
313 return result;
314 }
315
316}