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