· 8 years ago · Oct 08, 2017, 08:46 AM
1package com.gmail.nossr50;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.util.HashMap;
8import java.util.ArrayList;
9import java.sql.PreparedStatement;
10
11
12import org.bukkit.entity.Player;
13
14import com.avaje.ebeaninternal.server.lib.sql.DataSourceException;
15import com.gmail.nossr50.config.LoadProperties;
16
17
18public class Database {
19
20 private Connection conn;
21 private mcMMO plugin;
22
23 public Database(mcMMO instance)
24 {
25 plugin = instance;
26 // Load the driver instance
27 try {
28 Class.forName("com.mysql.jdbc.Driver").newInstance();
29 } catch (Exception ex) {
30 throw new DataSourceException("Failed to initialize JDBC driver");
31 }
32
33 // make the connection
34 try {
35 conn = DriverManager.getConnection("jdbc:mysql://" + LoadProperties.MySQLserverName + ":" + LoadProperties.MySQLport + "/" + LoadProperties.MySQLdbName + "?user=" + LoadProperties.MySQLuserName + "&password=" + LoadProperties.MySQLdbPass);
36 } catch (SQLException ex) {
37 // handle any errors
38 System.out.println("SQLException: " + ex.getMessage());
39 System.out.println("SQLState: " + ex.getSQLState());
40 System.out.println("VendorError: " + ex.getErrorCode());
41 }
42 }
43 //Create the DB structure
44 public void createStructure(){
45 Write("CREATE TABLE IF NOT EXISTS `"+LoadProperties.MySQLtablePrefix+"users` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT," +
46 "`user` varchar(40) NOT NULL," +
47 "`lastlogin` int(32) unsigned NOT NULL," +
48 "`party` varchar(100) NOT NULL DEFAULT ''," +
49 "PRIMARY KEY (`id`)," +
50 "UNIQUE KEY `user` (`user`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;");
51 Write("CREATE TABLE IF NOT EXISTS `"+LoadProperties.MySQLtablePrefix+"cooldowns` (`user_id` int(10) unsigned NOT NULL," +
52 "`taming` int(32) unsigned NOT NULL DEFAULT '0'," +
53 "`mining` int(32) unsigned NOT NULL DEFAULT '0'," +
54 "`woodcutting` int(32) unsigned NOT NULL DEFAULT '0'," +
55 "`repair` int(32) unsigned NOT NULL DEFAULT '0'," +
56 "`unarmed` int(32) unsigned NOT NULL DEFAULT '0'," +
57 "`herbalism` int(32) unsigned NOT NULL DEFAULT '0'," +
58 "`excavation` int(32) unsigned NOT NULL DEFAULT '0'," +
59 "`archery` int(32) unsigned NOT NULL DEFAULT '0'," +
60 "`swords` int(32) unsigned NOT NULL DEFAULT '0'," +
61 "`axes` int(32) unsigned NOT NULL DEFAULT '0'," +
62 "`acrobatics` int(32) unsigned NOT NULL DEFAULT '0'," +
63 "`prayer` int(32) unsigned NOT NULL DEFAULT '0'," +
64 "PRIMARY KEY (`user_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;");
65 Write("CREATE TABLE IF NOT EXISTS `"+LoadProperties.MySQLtablePrefix+"skills` (`user_id` int(10) unsigned NOT NULL," +
66 "`taming` int(10) unsigned NOT NULL DEFAULT '0'," +
67 "`mining` int(10) unsigned NOT NULL DEFAULT '0'," +
68 "`woodcutting` int(10) unsigned NOT NULL DEFAULT '0'," +
69 "`repair` int(10) unsigned NOT NULL DEFAULT '0'," +
70 "`unarmed` int(10) unsigned NOT NULL DEFAULT '0'," +
71 "`herbalism` int(10) unsigned NOT NULL DEFAULT '0'," +
72 "`excavation` int(10) unsigned NOT NULL DEFAULT '0'," +
73 "`archery` int(10) unsigned NOT NULL DEFAULT '0'," +
74 "`swords` int(10) unsigned NOT NULL DEFAULT '0'," +
75 "`axes` int(10) unsigned NOT NULL DEFAULT '0'," +
76 "`acrobatics` int(10) unsigned NOT NULL DEFAULT '0'," +
77 "`prayer` int(10) unsigned NOT NULL DEFAULT '0'," +
78 "PRIMARY KEY (`user_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;");
79 Write("CREATE TABLE IF NOT EXISTS `"+LoadProperties.MySQLtablePrefix+"experience` (`user_id` int(10) unsigned NOT NULL," +
80 "`taming` int(10) unsigned NOT NULL DEFAULT '0'," +
81 "`mining` int(10) unsigned NOT NULL DEFAULT '0'," +
82 "`woodcutting` int(10) unsigned NOT NULL DEFAULT '0'," +
83 "`repair` int(10) unsigned NOT NULL DEFAULT '0'," +
84 "`unarmed` int(10) unsigned NOT NULL DEFAULT '0'," +
85 "`herbalism` int(10) unsigned NOT NULL DEFAULT '0'," +
86 "`excavation` int(10) unsigned NOT NULL DEFAULT '0'," +
87 "`archery` int(10) unsigned NOT NULL DEFAULT '0'," +
88 "`swords` int(10) unsigned NOT NULL DEFAULT '0'," +
89 "`axes` int(10) unsigned NOT NULL DEFAULT '0'," +
90 "`acrobatics` int(10) unsigned NOT NULL DEFAULT '0'," +
91 "`prayer` int(10) unsigned NOT NULL DEFAULT '0'," +
92 "PRIMARY KEY (`user_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;");
93 Write("CREATE TABLE IF NOT EXISTS `"+LoadProperties.MySQLtablePrefix+"spawn` (`user_id` int(10) NOT NULL," +
94 "`x` int(64) NOT NULL DEFAULT '0'," +
95 "`y` int(64) NOT NULL DEFAULT '0'," +
96 "`z` int(64) NOT NULL DEFAULT '0'," +
97 "`world` varchar(50) NOT NULL DEFAULT ''," +
98 "PRIMARY KEY (`user_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;");
99 try{Write("ALTER TABLE '" + LoadProperties.MySQLtablePrefix + "cooldowns` ADD COLUMN `prayer` int(32) unsigned NOT NULL DEFAULT '0';");}catch(Exception e){}
100 try{Write("ALTER TABLE '" + LoadProperties.MySQLtablePrefix + "skills` ADD COLUMN `prayer` int(10) unsigned NOT NULL DEFAULT '0';");}catch(Exception e){}
101 try{Write("ALTER TABLE '" + LoadProperties.MySQLtablePrefix + "experience` ADD COLUMN `prayer` int(10) unsigned NOT NULL DEFAULT '0';");}catch(Exception e){}
102 }
103 // check if its closed
104 private void reconnect()
105 {
106 System.out.println("[mcMMO] Reconnecting to MySQL...");
107 try
108 {
109 conn = DriverManager.getConnection("jdbc:mysql://" + LoadProperties.MySQLserverName + ":" + LoadProperties.MySQLport + "/" + LoadProperties.MySQLdbName + "?user=" + LoadProperties.MySQLuserName + "&password=" + LoadProperties.MySQLdbPass);
110
111 System.out.println("[mcMMO] Connection success!");
112 } catch (SQLException ex)
113 {
114 System.out.println("[mcMMO] Connection to MySQL failed! Check status of MySQL server!");
115 System.out.println("SQLException: " + ex.getMessage());
116 System.out.println("SQLState: " + ex.getSQLState());
117 System.out.println("VendorError: " + ex.getErrorCode());
118 }
119
120 try {
121 if(conn.isValid(5)){
122 Users.clearUsers();
123
124 for(Player x : plugin.getServer().getOnlinePlayers())
125 {
126 Users.addUser(x);
127 }
128 }
129 } catch (SQLException e) {
130 //Herp
131 }
132 }
133 // write query
134 public boolean Write(String sql) {
135 /*
136 * Double check connection to MySQL
137 */
138 try {
139 if(!conn.isValid(5)){
140 reconnect();
141 }
142 } catch (SQLException e) {
143 e.printStackTrace();
144 }
145
146 try {
147 PreparedStatement stmt = null;
148 stmt = this.conn.prepareStatement(sql);
149 stmt.executeUpdate();
150 return true;
151 } catch(SQLException ex) {
152 System.out.println("SQLException: " + ex.getMessage());
153 System.out.println("SQLState: " + ex.getSQLState());
154 System.out.println("VendorError: " + ex.getErrorCode());
155 return false;
156 }
157 }
158
159 // Get Int
160 // only return first row / first field
161 public Integer GetInt(String sql) {
162 PreparedStatement stmt = null;
163 ResultSet rs = null;
164 Integer result = 0;
165
166 /*
167 * Double check connection to MySQL
168 */
169 try
170 {
171 if(!conn.isValid(5))
172 {
173 reconnect();
174 }
175 } catch (SQLException e)
176 {
177 e.printStackTrace();
178 }
179
180 try {
181 stmt = this.conn.prepareStatement(sql);
182 if (stmt.executeQuery() != null) {
183 stmt.executeQuery();
184 rs = stmt.getResultSet();
185 if(rs.next()){
186 result = rs.getInt(1);
187 }
188 else { result = 0; }
189 }
190 }
191 catch (SQLException ex) {
192 System.out.println("SQLException: " + ex.getMessage());
193 System.out.println("SQLState: " + ex.getSQLState());
194 System.out.println("VendorError: " + ex.getErrorCode());
195 }
196
197 return result;
198 }
199
200 // read query
201 public HashMap<Integer, ArrayList<String>> Read(String sql) {
202 /*
203 * Double check connection to MySQL
204 */
205 try
206 {
207 if(!conn.isValid(5))
208 {
209 reconnect();
210 }
211 } catch (SQLException e)
212 {
213 e.printStackTrace();
214 }
215
216 PreparedStatement stmt = null;
217 ResultSet rs = null;
218 HashMap<Integer, ArrayList<String>> Rows = new HashMap<Integer, ArrayList<String>>();
219
220 try {
221 stmt = this.conn.prepareStatement(sql);
222 if (stmt.executeQuery() != null) {
223 stmt.executeQuery();
224 rs = stmt.getResultSet();
225 while (rs.next()) {
226 ArrayList<String> Col = new ArrayList<String>();
227 for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
228 Col.add(rs.getString(i));
229 }
230 Rows.put(rs.getRow(),Col);
231 }
232 }
233 }
234 catch (SQLException ex) {
235 System.out.println("SQLException: " + ex.getMessage());
236 System.out.println("SQLState: " + ex.getSQLState());
237 System.out.println("VendorError: " + ex.getErrorCode());
238 }
239
240 // release dataset
241 if (rs != null) {
242 try {
243 rs.close();
244 } catch (SQLException sqlEx) { } // ignore
245 rs = null;
246 }
247 if (stmt != null) {
248 try {
249 stmt.close();
250 } catch (SQLException sqlEx) { } // ignore
251 stmt = null;
252 }
253
254 return Rows;
255 }
256
257}