· 10 years ago · Aug 01, 2016, 06:48 PM
1package eu.union.dev.engine.storage;
2
3import eu.union.dev.PvPMain;
4import eu.union.dev.engine.KPlayer;
5import eu.union.dev.engine.managers.PlayerManager;
6
7import java.sql.*;
8import java.util.ArrayList;
9import java.util.List;
10import java.util.UUID;
11import java.util.logging.Level;
12
13
14public class Database {
15
16 String user = "";
17 String database = "";
18 String password = "";
19 String port = "";
20 String hostname = "";
21 Connection c = null;
22
23 public Database(String user, String password, String database, String port, String hostname) {
24 this.user = user;
25 this.database = database;
26 this.password = password;
27 if (port == null) {
28 this.port = "3306";
29 } else {
30 this.port = port;
31 }
32 this.hostname = hostname;
33 }
34
35
36 /**
37 * Open mysql connection
38 *
39 * @return
40 */
41 public Connection open() {
42 try {
43 Class.forName("com.mysql.jdbc.Driver");
44 c = DriverManager.getConnection("jdbc:mysql://" + hostname + ":" + port + "/" + database + "?autoReconnect=true", user, password);
45 System.out.println("MySQL connection successful");
46 PvPMain.getInstance().getLogger().log(Level.INFO, "MySQL connection successful");
47 return c;
48 } catch (SQLException e) {
49 PvPMain.getInstance().getLogger().log(Level.SEVERE, "MySQL connection has been aborted");
50 PvPMain.getInstance().getLogger().log(Level.SEVERE, "Reason:" + e.getMessage());
51 e.printStackTrace();
52 } catch (ClassNotFoundException e) {
53 System.out.println("JDBC Driver not found!");
54 }
55 return c;
56 }
57
58 public Connection getConnection() {
59 return this.c;
60 }
61
62 /**
63 * Close mysql connection
64 */
65 public Connection close(Connection c) {
66 try {
67 c.close();
68 } catch (SQLException e) {
69 PvPMain.getInstance().getLogger().log(Level.SEVERE, "MySQL can't close connection");
70 e.printStackTrace();
71 }
72 return c;
73 }
74
75 /**
76 * Get all tables together and setup one by one
77 */
78 public synchronized void setupTables() {
79 setupProfiles();
80 }
81
82 /**
83 * Setup the hub player profiles
84 */
85 public synchronized void setupProfiles() {
86 try {
87 PreparedStatement profiles = c
88 .prepareStatement("CREATE TABLE IF NOT EXISTS `Profiles`(`id` int(11) NOT NULL auto_increment,"
89 + " `UUID` varchar(255) NOT NULL,"
90 + " `Kills` int(10) NOT NULL,"
91 + " `Deaths` int(10) NOT NULL,"
92 + "`Coins` bigint(255) NOT NULL,"
93 + "`Level` int(10) NOT NULL,"
94 + "`KDR` int(10) NOT NULL,"
95 + "`EXP` int(15) NOT NULL,"
96 + " PRIMARY KEY(`id`));");
97 profiles.execute();
98 profiles.close();
99 } catch (Exception e) {
100 e.printStackTrace();
101 }
102 }
103
104
105 /**
106 * Try to create a new player profile
107 *
108 * @param uuid
109 * @return
110 */
111 public synchronized boolean createPlayerProfile(UUID uuid) {
112 try {
113 String PvPMainStatement = "SELECT * FROM Profiles WHERE UUID = '" + uuid + "';";
114 ResultSet result = this.c.createStatement().executeQuery(PvPMainStatement);
115 if (result.next()) {
116 PlayerManager.addPlayerProfile(new KPlayer(uuid, result.getInt("Kills"),
117 result.getInt("Deaths"), result.getLong("Coins"),
118 result.getInt("Level"), result.getInt("KDR"), result.getInt("EXP")));
119
120 } else {
121 PlayerManager.addPlayerProfile(new KPlayer(uuid, 0, 0, 0, 0, 0,0));
122 }
123 } catch (SQLException e) {
124 System.out.println("Error while trying to create PlayerDat! Reason: " + e.getMessage());
125 return false;
126 }
127 return true;
128 }
129
130 /**
131 * Update all data from player profile to the sql database
132 *
133 * @param playerProfile
134 * @return
135 */
136 public synchronized boolean updatePlayerProfileSQL(KPlayer playerProfile) {
137 try {
138 String PvPMainStatement = "SELECT * FROM Profiles WHERE UUID = '" + playerProfile.getUuid() + "'";
139 ResultSet result = this.c.createStatement().executeQuery(PvPMainStatement);
140 if (result.next()) {
141 String PvPMainUpdate = "UPDATE Profiles SET Kills = " + playerProfile.getKills()
142 + ", Deaths = " + playerProfile.getDeaths()
143 + ", Coins = " + playerProfile.getCoins()
144 + ", Level = " + playerProfile.getLevel()
145 + ", KDR = " + playerProfile.getKDR()
146 + ", EXP = " + playerProfile.getCurrentEXP()
147 + " WHERE UUID = '"
148 + playerProfile.getUuid() + "';";
149 this.c.createStatement().executeUpdate(PvPMainUpdate);
150 } else {
151 String PvPMainUpdate = "INSERT INTO Profiles (UUID, Kills, Deaths, Coins, Level, KDR, EXP) VALUES ('"
152 + playerProfile.getUuid() + "', "
153 + playerProfile.getKills() + ", "
154 + playerProfile.getDeaths() + ", "
155 + playerProfile.getCoins() + ", "
156 + playerProfile.getLevel() + ", "
157 + playerProfile.getKDR() + ", "
158 + playerProfile.getCurrentEXP() + "); ";
159 this.c.createStatement().executeUpdate(PvPMainUpdate);
160 }
161 } catch (SQLException e) {
162 System.out.println("Error while trying to update the MySQL! Reason: " + e.getMessage());
163 return false;
164 }
165 return true;
166 }
167
168 public synchronized List<String> getTop(TopType type) throws SQLException {
169 List<String> result = new ArrayList<>();
170 Statement s = c.createStatement();
171
172 switch (type) {
173 case KILLS:
174 if (s != null) {
175 ResultSet rs = s.executeQuery("SELECT UUID FROM `Profiles` ORDER BY `Kills`");
176 rs.findColumn("PlayerName");
177 if (rs.last()) {
178 result.add(rs.getString("PlayerName"));
179 }
180 if (rs.previous()) {
181 result.add(rs.getString("PlayerName"));
182 }
183 if (rs.previous()) {
184 result.add(rs.getString("PlayerName"));
185 }
186 }
187 break;
188 case DEATHS:
189 if (s != null) {
190 ResultSet rs = s.executeQuery("SELECT UUID FROM `Profiles` ORDER BY `Deaths`");
191 rs.findColumn("PlayerName");
192 if (rs.last()) {
193 result.add(rs.getString("PlayerName"));
194 }
195 if (rs.previous()) {
196 result.add(rs.getString("PlayerName"));
197 }
198 if (rs.previous()) {
199 result.add(rs.getString("PlayerName"));
200 }
201 }
202 break;
203 case COINS:
204 if (s != null) {
205 ResultSet rs = s.executeQuery("SELECT UUID FROM `Profiles` ORDER BY `Coins`");
206 rs.findColumn("PlayerName");
207 if (rs.last()) {
208 result.add(rs.getString("PlayerName"));
209 }
210 if (rs.previous()) {
211 result.add(rs.getString("PlayerName"));
212 }
213 if (rs.previous()) {
214 result.add(rs.getString("PlayerName"));
215 }
216 }
217 break;
218 case LEVEL:
219 if (s != null) {
220 ResultSet rs = s.executeQuery("SELECT UUID FROM `Profiles` ORDER BY `Level`");
221 rs.findColumn("PlayerName");
222 if (rs.last()) {
223 result.add(rs.getString("PlayerName"));
224 }
225 if (rs.previous()) {
226 result.add(rs.getString("PlayerName"));
227 }
228 if (rs.previous()) {
229 result.add(rs.getString("PlayerName"));
230 }
231 }
232 break;
233 }
234 return result;
235 }
236
237}