1 |
package tools; |
2 |
|
3 |
import java.io.BufferedReader; |
4 |
import java.io.File; |
5 |
import java.io.FileReader; |
6 |
import java.sql.Connection; |
7 |
import java.sql.DriverManager; |
8 |
import java.sql.ResultSet; |
9 |
import java.sql.SQLException; |
10 |
import java.sql.Statement; |
11 |
import java.util.StringTokenizer; |
12 |
import java.io.IOException; |
13 |
import java.util.ArrayList; |
14 |
|
15 |
public class MySQLConnection { |
16 |
|
17 |
/*--------------------- constants -----------------------*/ |
18 |
|
19 |
// -- constants for database connection -- |
20 |
static final String HOST = "white"; |
21 |
static final String USER = ""; |
22 |
static final String PASSWORD = "nieve"; |
23 |
|
24 |
/*------------------- member variables --------------------*/ |
25 |
|
26 |
public Connection conn; |
27 |
private String host; |
28 |
private String user; |
29 |
private String password=PASSWORD; |
30 |
private String port; |
31 |
private String dbname; |
32 |
|
33 |
/*-------------------- constructors -----------------------*/ |
34 |
|
35 |
/** |
36 |
* Connect to database using the given server, user and password |
37 |
*/ |
38 |
public MySQLConnection(String dbServer, String dbUserName, String dbPassword) { |
39 |
loadMySQLDriver(); |
40 |
host=dbServer; |
41 |
user=dbUserName; |
42 |
password=dbPassword; |
43 |
port=""; |
44 |
dbname=""; |
45 |
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
46 |
try { |
47 |
conn = DriverManager.getConnection(connStr, user, password); |
48 |
} catch (SQLException e) { |
49 |
System.err.println("SQLException: " + e.getMessage()); |
50 |
System.err.println("SQLState: " + e.getSQLState()); |
51 |
System.err.println("VendorError: " + e.getErrorCode()); |
52 |
e.printStackTrace(); |
53 |
} // end try/catch connection |
54 |
} |
55 |
|
56 |
/** |
57 |
* Connect to database using the given server, user, password and dbname |
58 |
*/ |
59 |
public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName) { |
60 |
loadMySQLDriver(); |
61 |
host=dbServer; |
62 |
user=dbUserName; |
63 |
password=dbPassword; |
64 |
port=""; |
65 |
dbname=dbName; |
66 |
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
67 |
try { |
68 |
conn = DriverManager.getConnection(connStr, user, password); |
69 |
} catch (SQLException e) { |
70 |
System.err.println("SQLException: " + e.getMessage()); |
71 |
System.err.println("SQLState: " + e.getSQLState()); |
72 |
System.err.println("VendorError: " + e.getErrorCode()); |
73 |
e.printStackTrace(); |
74 |
} // end try/catch connection |
75 |
} |
76 |
|
77 |
/** |
78 |
* Connect to database using the given server, user, password, dbname and port |
79 |
*/ |
80 |
public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName, int portNum) { |
81 |
loadMySQLDriver(); |
82 |
host=dbServer; |
83 |
user=dbUserName; |
84 |
password=dbPassword; |
85 |
port=":"+portNum; |
86 |
dbname=dbName; |
87 |
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
88 |
try { |
89 |
conn = DriverManager.getConnection(connStr, user, password); |
90 |
} catch (SQLException e) { |
91 |
System.err.println("SQLException: " + e.getMessage()); |
92 |
System.err.println("SQLState: " + e.getSQLState()); |
93 |
System.err.println("VendorError: " + e.getErrorCode()); |
94 |
e.printStackTrace(); |
95 |
} // end try/catch connection |
96 |
} |
97 |
|
98 |
/** |
99 |
* Connect to database giving a connection file |
100 |
*/ |
101 |
public MySQLConnection(String connFile) { |
102 |
loadMySQLDriver(); |
103 |
readConnectionFile(connFile); |
104 |
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
105 |
try { |
106 |
conn = DriverManager.getConnection(connStr, user, password); |
107 |
} catch (SQLException e) { |
108 |
System.err.println("SQLException: " + e.getMessage()); |
109 |
System.err.println("SQLState: " + e.getSQLState()); |
110 |
System.err.println("VendorError: " + e.getErrorCode()); |
111 |
e.printStackTrace(); |
112 |
} // end try/catch connection |
113 |
} |
114 |
|
115 |
|
116 |
/*---------------------- methods -------------------------*/ |
117 |
|
118 |
private void loadMySQLDriver() { |
119 |
try { |
120 |
Class.forName("com.mysql.jdbc.Driver").newInstance(); |
121 |
} |
122 |
catch(Exception e) { |
123 |
e.printStackTrace(); |
124 |
System.err.println("An exception occurred while loading the mysql jdbc driver, exiting."); |
125 |
System.exit(1); |
126 |
} |
127 |
} |
128 |
|
129 |
public Statement createStatement() throws SQLException { |
130 |
return this.conn.createStatement(); |
131 |
} |
132 |
|
133 |
public void executeSql(String query) throws SQLException{ |
134 |
Statement stmt; |
135 |
stmt = conn.createStatement(); |
136 |
stmt.execute(query); |
137 |
stmt.close(); |
138 |
} |
139 |
|
140 |
/** |
141 |
* @param query |
142 |
* @return the first column of the first row of the result of the given query as a string |
143 |
* or null if no results were returned |
144 |
*/ |
145 |
public String getStringFromDb(String query) { |
146 |
Statement stmt; |
147 |
ResultSet rs; |
148 |
String result = null; |
149 |
|
150 |
try { |
151 |
|
152 |
stmt = conn.createStatement(); |
153 |
rs = stmt.executeQuery(query); |
154 |
if(rs.next()) { |
155 |
result = rs.getString(1); |
156 |
} |
157 |
rs.close(); |
158 |
stmt.close(); |
159 |
|
160 |
} // end try |
161 |
catch (SQLException e) { |
162 |
System.err.println("SQLException: " + e.getMessage()); |
163 |
System.err.println("SQLState: " + e.getSQLState()); |
164 |
System.err.println("VendorError: " + e.getErrorCode()); |
165 |
e.printStackTrace(); |
166 |
} // end catch |
167 |
|
168 |
return result; |
169 |
} |
170 |
|
171 |
/** |
172 |
* @param query |
173 |
* @return the first column of the first row of the result of the given query as an integer |
174 |
* or -1 if no results were returned |
175 |
*/ |
176 |
public int getIntFromDb(String query) { |
177 |
Statement stmt; |
178 |
ResultSet rs; |
179 |
int result = -1; |
180 |
|
181 |
try { |
182 |
|
183 |
stmt = conn.createStatement(); |
184 |
rs = stmt.executeQuery(query); |
185 |
if(rs.next()) { |
186 |
result = rs.getInt(1); |
187 |
} |
188 |
rs.close(); |
189 |
stmt.close(); |
190 |
|
191 |
} // end try |
192 |
catch (SQLException e) { |
193 |
System.err.println("SQLException: " + e.getMessage()); |
194 |
System.err.println("SQLState: " + e.getSQLState()); |
195 |
System.err.println("VendorError: " + e.getErrorCode()); |
196 |
e.printStackTrace(); |
197 |
} // end catch |
198 |
|
199 |
return result; |
200 |
} |
201 |
|
202 |
public void close() { |
203 |
|
204 |
try { |
205 |
conn.close(); |
206 |
} catch (SQLException e) { |
207 |
System.err.println("SQLException: " + e.getMessage()); |
208 |
System.err.println("SQLState: " + e.getSQLState()); |
209 |
System.err.println("VendorError: " + e.getErrorCode()); |
210 |
e.printStackTrace(); |
211 |
} // end try/catch connection |
212 |
} |
213 |
|
214 |
/** |
215 |
* Get the actual Connection object within this MySQLConnection. In case that an external library need a Connection object |
216 |
* @return |
217 |
*/ |
218 |
public Connection getConnectionObject() { |
219 |
return this.conn; |
220 |
} |
221 |
|
222 |
/** |
223 |
* Used in the constructor that gets a connFile as argument. To read the connection parameters from a connection file. |
224 |
* @param connFile |
225 |
*/ |
226 |
private void readConnectionFile(String connFile) { |
227 |
// reads the values of the connFile into the static variables; |
228 |
String homedir = System.getProperty("user.home"); |
229 |
if (connFile.length()==0) { // no file was specified |
230 |
connFile=homedir+"/.my.cnf"; // assume default configuration file |
231 |
} |
232 |
// else the location of the connection file was given |
233 |
// Open the configuration file |
234 |
BufferedReader fileIn = null; |
235 |
StringTokenizer str; |
236 |
String item, oneLine; |
237 |
// to control if the minimum necessary 3 parameters are given in file |
238 |
int cfgParsPresent=0; |
239 |
// setting default blank values for port and dbname, they are set to blank unless fields specified in file |
240 |
port=""; |
241 |
dbname=""; |
242 |
// list the entries in the file and decompose them |
243 |
try { |
244 |
fileIn = new BufferedReader(new FileReader(new File(connFile))); // open BufferedReader to file connFile |
245 |
while ((oneLine = fileIn.readLine()) != null ) { |
246 |
// Construct a stringTokenizer for the line that we read with : delimited |
247 |
str = new StringTokenizer(oneLine, "="); // true sets returnDelimiters flag |
248 |
while ( str.hasMoreTokens()) { |
249 |
item = str.nextToken(); |
250 |
if( item.equals("host")) { // mandatory parameter |
251 |
host=str.nextToken(); |
252 |
cfgParsPresent++; |
253 |
break; |
254 |
} // end if host |
255 |
if( item.equals("port")) { // optional parameter |
256 |
port=":"+str.nextToken(); |
257 |
break; |
258 |
} // end if port |
259 |
if( item.equals("user")) { // mandatory parameter |
260 |
user=str.nextToken(); |
261 |
cfgParsPresent++; |
262 |
break; |
263 |
} // end if password |
264 |
if( item.equals("password")) { // mandatory parameter |
265 |
password=str.nextToken(); |
266 |
cfgParsPresent++; |
267 |
break; |
268 |
} // end if password |
269 |
if( item.equals("database")) { // optional parameter |
270 |
dbname=str.nextToken(); |
271 |
break; |
272 |
} // end if password |
273 |
} // next token in this line |
274 |
} // next line in the file |
275 |
if (cfgParsPresent<3){ |
276 |
System.err.println("Not all mandatory parameters are given in connection file "+connFile+". Can't connect to mysql server, exiting."); |
277 |
System.exit(1); |
278 |
} |
279 |
} |
280 |
catch (IOException e) { |
281 |
System.err.println("Couldn't open file "+connFile); |
282 |
e.printStackTrace(); |
283 |
System.exit(1); |
284 |
} |
285 |
|
286 |
try { // closing the file |
287 |
if (fileIn != null) fileIn.close(); |
288 |
} catch (IOException e) { |
289 |
System.err.println("Couldn't close file "+connFile); |
290 |
e.printStackTrace(); |
291 |
} |
292 |
|
293 |
} |
294 |
|
295 |
/** |
296 |
* To print the db size info for the given db of this MySQLConnection. |
297 |
* @param dbName |
298 |
*/ |
299 |
public void printDbSizeInfo (String dbName) { |
300 |
double data = 0, index = 0, table_data = 0, table_index = 0, GB = Math.pow(2, 30); |
301 |
String Query = null, table = null; |
302 |
Statement Stmt = null; |
303 |
ResultSet RS = null; |
304 |
try { |
305 |
Query = "SHOW TABLE STATUS FROM "+dbName; |
306 |
Stmt = this.conn.createStatement(); |
307 |
RS = Stmt.executeQuery(Query); |
308 |
while (RS.next()) { |
309 |
table = RS.getString("Name"); |
310 |
table_data = RS.getDouble("Data_length"); |
311 |
table_index = RS.getDouble("Index_length"); |
312 |
data += RS.getDouble("Data_length"); |
313 |
index += RS.getDouble("Index_length"); |
314 |
System.out.println("Table "+table+"##data:"+table_data+", index:"+table_index); |
315 |
} |
316 |
RS.close(); |
317 |
Stmt.close(); |
318 |
System.out.println("Database "+dbName+" needs "+((data+index)/GB)+ " GB (data:"+(data/GB)+", index:"+(index/GB)+")."); |
319 |
} |
320 |
catch (SQLException e) { |
321 |
System.err.println("SQLException: " + e.getMessage()); |
322 |
System.err.println("SQLState: " + e.getSQLState()); |
323 |
System.err.println("VendorError: " + e.getErrorCode()); |
324 |
e.printStackTrace(); |
325 |
} // end try/catch connection |
326 |
} |
327 |
|
328 |
/** |
329 |
* To get range of a column from a table in this MySQLConnection. Note that if the connection was created without pointing |
330 |
* to a particular database then the argument table must be specified as dbname.tablename. |
331 |
* @param table |
332 |
* @param column |
333 |
* @return |
334 |
*/ |
335 |
public double[] getRange(String table, String column) { |
336 |
String query = ""; |
337 |
Statement S; |
338 |
ResultSet R; |
339 |
double[] range = new double[2]; |
340 |
try { |
341 |
query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";"; |
342 |
S = this.conn.createStatement(); |
343 |
R = S.executeQuery(query); |
344 |
if (R.next()) { |
345 |
range[0] = R.getDouble(1); |
346 |
range[1] = R.getDouble(2); |
347 |
} |
348 |
R.close(); |
349 |
S.close(); |
350 |
} // end try |
351 |
catch (SQLException e) { |
352 |
System.err.println("SQLException: " + e.getMessage()); |
353 |
System.err.println("SQLState: " + e.getSQLState()); |
354 |
System.err.println("VendorError: " + e.getErrorCode()); |
355 |
e.printStackTrace(); |
356 |
} // end catch |
357 |
return range; |
358 |
} |
359 |
|
360 |
/** |
361 |
* To get range of a column from a table in this MySQLConnection using a given WHERE condition. Note that if |
362 |
* the connection was created without pointing to a particular database then the argument table must |
363 |
* be specified as dbname.tablename. |
364 |
* @param table |
365 |
* @param column |
366 |
* @param whereStr |
367 |
* @return |
368 |
*/ |
369 |
public double[] getRange(String table, String column, String whereStr) { |
370 |
String query = ""; |
371 |
Statement S; |
372 |
ResultSet R; |
373 |
double[] range = new double[2]; |
374 |
try { |
375 |
query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+whereStr+");"; |
376 |
S = this.conn.createStatement(); |
377 |
R = S.executeQuery(query); |
378 |
if (R.next()) { |
379 |
range[0] = R.getDouble(1); |
380 |
range[1] = R.getDouble(2); |
381 |
} |
382 |
R.close(); |
383 |
S.close(); |
384 |
} // end try |
385 |
catch (SQLException e) { |
386 |
System.err.println("SQLException: " + e.getMessage()); |
387 |
System.err.println("SQLState: " + e.getSQLState()); |
388 |
System.err.println("VendorError: " + e.getErrorCode()); |
389 |
e.printStackTrace(); |
390 |
} // end catch |
391 |
return range; |
392 |
} |
393 |
|
394 |
/** |
395 |
* To get all indexes for a certain table. Note the MySQLConnection object must be created with a non-blank database. |
396 |
* @param table |
397 |
* @return |
398 |
*/ |
399 |
public String[] getAllIndexes4Table(String table) { |
400 |
ArrayList<String> indexesAL=new ArrayList<String>(); |
401 |
String query; |
402 |
Statement S; |
403 |
ResultSet R; |
404 |
try { |
405 |
query = "SELECT index_name from INFORMATION_SCHEMA.statistics WHERE table_schema='"+dbname+"' AND table_name='"+table+"';"; |
406 |
S = this.conn.createStatement(); |
407 |
R = S.executeQuery(query); |
408 |
while (R.next()) { |
409 |
indexesAL.add(R.getString(1)); |
410 |
} |
411 |
R.close(); |
412 |
S.close(); |
413 |
} // end try |
414 |
catch (SQLException e) { |
415 |
System.err.println("SQLException: " + e.getMessage()); |
416 |
System.err.println("SQLState: " + e.getSQLState()); |
417 |
System.err.println("VendorError: " + e.getErrorCode()); |
418 |
e.printStackTrace(); |
419 |
} // end catch |
420 |
String[] indexes=new String[indexesAL.size()]; |
421 |
int i=0; |
422 |
for (String index:indexesAL) { |
423 |
indexes[i]=index; |
424 |
i++; |
425 |
} |
426 |
return indexes; |
427 |
} |
428 |
} |