1 |
duarte |
55 |
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 |
duarte |
58 |
import java.util.ArrayList; |
14 |
duarte |
55 |
|
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 |
duarte |
80 |
/** |
36 |
duarte |
55 |
* Connect to database using the given server, user and password |
37 |
duarte |
80 |
* @param dbServer |
38 |
|
|
* @param dbUserName |
39 |
|
|
* @param dbPassword |
40 |
duarte |
55 |
*/ |
41 |
|
|
public MySQLConnection(String dbServer, String dbUserName, String dbPassword) { |
42 |
|
|
loadMySQLDriver(); |
43 |
|
|
host=dbServer; |
44 |
|
|
user=dbUserName; |
45 |
|
|
password=dbPassword; |
46 |
|
|
port=""; |
47 |
|
|
dbname=""; |
48 |
|
|
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
49 |
|
|
try { |
50 |
|
|
conn = DriverManager.getConnection(connStr, user, password); |
51 |
|
|
} catch (SQLException e) { |
52 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
53 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
54 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
55 |
|
|
e.printStackTrace(); |
56 |
|
|
} // end try/catch connection |
57 |
|
|
} |
58 |
|
|
|
59 |
|
|
/** |
60 |
duarte |
59 |
* Connect to database using the given server, user, password and dbname. |
61 |
|
|
* Please always use this constructor in preference rather than constructing without specifying a database |
62 |
duarte |
80 |
* @param dbServer |
63 |
|
|
* @param dbUserName |
64 |
|
|
* @param dbPassword |
65 |
|
|
* @param dbName |
66 |
duarte |
55 |
*/ |
67 |
|
|
public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName) { |
68 |
duarte |
62 |
loadMySQLDriver(); |
69 |
|
|
host=dbServer; |
70 |
|
|
user=dbUserName; |
71 |
|
|
password=dbPassword; |
72 |
|
|
port=""; |
73 |
|
|
dbname=dbName; |
74 |
|
|
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
75 |
|
|
try { |
76 |
|
|
conn = DriverManager.getConnection(connStr, user, password); |
77 |
|
|
} catch (SQLException e) { |
78 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
79 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
80 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
81 |
|
|
e.printStackTrace(); |
82 |
|
|
} // end try/catch connection |
83 |
duarte |
55 |
} |
84 |
|
|
|
85 |
|
|
/** |
86 |
duarte |
176 |
* Connect to database using the given server and dbName |
87 |
|
|
* Password taken from default, user name from unix user name |
88 |
|
|
* @param dbServer |
89 |
|
|
* @param dbName |
90 |
|
|
*/ |
91 |
|
|
public MySQLConnection(String dbServer, String dbName) { |
92 |
|
|
loadMySQLDriver(); |
93 |
|
|
host=dbServer; |
94 |
|
|
user=getUserName(); |
95 |
|
|
port=""; |
96 |
|
|
dbname=dbName; |
97 |
|
|
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
98 |
|
|
try { |
99 |
|
|
conn = DriverManager.getConnection(connStr, user, password); |
100 |
|
|
} catch (SQLException e) { |
101 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
102 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
103 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
104 |
|
|
e.printStackTrace(); |
105 |
|
|
} // end try/catch connection |
106 |
|
|
} |
107 |
|
|
|
108 |
|
|
/** |
109 |
duarte |
55 |
* Connect to database using the given server, user, password, dbname and port |
110 |
duarte |
59 |
* Only needed if mysql server uses a port different from the standard 3306 |
111 |
duarte |
80 |
* @param dbServer |
112 |
|
|
* @param dbUserName |
113 |
|
|
* @param dbPassword |
114 |
|
|
* @param dbName |
115 |
|
|
* @param portNum |
116 |
duarte |
55 |
*/ |
117 |
|
|
public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName, int portNum) { |
118 |
|
|
loadMySQLDriver(); |
119 |
|
|
host=dbServer; |
120 |
|
|
user=dbUserName; |
121 |
|
|
password=dbPassword; |
122 |
|
|
port=":"+portNum; |
123 |
|
|
dbname=dbName; |
124 |
|
|
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
125 |
|
|
try { |
126 |
|
|
conn = DriverManager.getConnection(connStr, user, password); |
127 |
|
|
} catch (SQLException e) { |
128 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
129 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
130 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
131 |
|
|
e.printStackTrace(); |
132 |
|
|
} // end try/catch connection |
133 |
|
|
} |
134 |
|
|
|
135 |
|
|
/** |
136 |
duarte |
80 |
* Connect to database giving a connection file |
137 |
|
|
* @param connFile the connection file's name |
138 |
duarte |
55 |
*/ |
139 |
|
|
public MySQLConnection(String connFile) { |
140 |
|
|
loadMySQLDriver(); |
141 |
|
|
readConnectionFile(connFile); |
142 |
|
|
String connStr="jdbc:mysql://"+host+port+"/"+dbname; |
143 |
|
|
try { |
144 |
|
|
conn = DriverManager.getConnection(connStr, user, password); |
145 |
|
|
} catch (SQLException e) { |
146 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
147 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
148 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
149 |
|
|
e.printStackTrace(); |
150 |
|
|
} // end try/catch connection |
151 |
|
|
} |
152 |
|
|
|
153 |
|
|
|
154 |
|
|
/*---------------------- methods -------------------------*/ |
155 |
|
|
|
156 |
duarte |
176 |
/** get user name from operating system (for use as database username) */ |
157 |
duarte |
177 |
public static String getUserName() { |
158 |
duarte |
176 |
String user = null; |
159 |
|
|
user = System.getProperty("user.name"); |
160 |
|
|
if(user == null) { |
161 |
|
|
System.err.println("Could not get user name from operating system. Exiting"); |
162 |
|
|
System.exit(1); |
163 |
|
|
} |
164 |
|
|
return user; |
165 |
|
|
} |
166 |
|
|
|
167 |
duarte |
109 |
public static void loadMySQLDriver() { |
168 |
duarte |
55 |
try { |
169 |
|
|
Class.forName("com.mysql.jdbc.Driver").newInstance(); |
170 |
|
|
} |
171 |
|
|
catch(Exception e) { |
172 |
|
|
e.printStackTrace(); |
173 |
|
|
System.err.println("An exception occurred while loading the mysql jdbc driver, exiting."); |
174 |
|
|
System.exit(1); |
175 |
|
|
} |
176 |
|
|
} |
177 |
|
|
|
178 |
duarte |
60 |
/** |
179 |
|
|
* Used in the constructor that gets a connFile as argument. To read the connection parameters from a connection file. |
180 |
|
|
* @param connFile |
181 |
|
|
*/ |
182 |
|
|
private void readConnectionFile(String connFile) { |
183 |
|
|
// reads the values of the connFile into the static variables; |
184 |
|
|
String homedir = System.getProperty("user.home"); |
185 |
|
|
if (connFile.length()==0) { // no file was specified |
186 |
|
|
connFile=homedir+"/.my.cnf"; // assume default configuration file |
187 |
|
|
} |
188 |
|
|
// else the location of the connection file was given |
189 |
|
|
// Open the configuration file |
190 |
|
|
BufferedReader fileIn = null; |
191 |
|
|
StringTokenizer str; |
192 |
|
|
String item, oneLine; |
193 |
|
|
// to control if the minimum mandatory 4 parameters are given in file |
194 |
|
|
int paramCount=0; |
195 |
|
|
// setting default blank values for port and dbname, they are set to blank unless fields specified in file |
196 |
|
|
port=""; |
197 |
|
|
dbname=""; |
198 |
|
|
// list the entries in the file and decompose them |
199 |
|
|
try { |
200 |
|
|
fileIn = new BufferedReader(new FileReader(new File(connFile))); // open BufferedReader to file connFile |
201 |
|
|
while ((oneLine = fileIn.readLine()) != null ) { |
202 |
|
|
// Construct a stringTokenizer for the line that we read with : delimited |
203 |
|
|
str = new StringTokenizer(oneLine, "="); // true sets returnDelimiters flag |
204 |
|
|
while ( str.hasMoreTokens()) { |
205 |
|
|
item = str.nextToken(); |
206 |
|
|
if( item.equals("host")) { // mandatory parameter |
207 |
|
|
host=str.nextToken(); |
208 |
|
|
paramCount++; |
209 |
|
|
break; |
210 |
|
|
} // end if host |
211 |
|
|
if( item.equals("port")) { // optional parameter |
212 |
|
|
port=":"+str.nextToken(); |
213 |
|
|
break; |
214 |
|
|
} // end if port |
215 |
|
|
if( item.equals("user")) { // mandatory parameter |
216 |
|
|
user=str.nextToken(); |
217 |
|
|
paramCount++; |
218 |
|
|
break; |
219 |
|
|
} // end if password |
220 |
|
|
if( item.equals("password")) { // mandatory parameter |
221 |
|
|
password=str.nextToken(); |
222 |
|
|
paramCount++; |
223 |
|
|
break; |
224 |
|
|
} // end if password |
225 |
|
|
if( item.equals("database")) { // mandatory parameter |
226 |
|
|
dbname=str.nextToken(); |
227 |
|
|
paramCount++; |
228 |
|
|
break; |
229 |
|
|
} // end if password |
230 |
|
|
} // next token in this line |
231 |
|
|
} // next line in the file |
232 |
|
|
if (paramCount<4){ |
233 |
|
|
System.err.println("Not all mandatory parameters are given in connection file "+connFile+". Can't connect to mysql server, exiting."); |
234 |
|
|
System.exit(1); |
235 |
|
|
} |
236 |
|
|
} |
237 |
|
|
catch (IOException e) { |
238 |
|
|
System.err.println("Couldn't open file "+connFile); |
239 |
|
|
e.printStackTrace(); |
240 |
|
|
System.exit(1); |
241 |
|
|
} |
242 |
|
|
|
243 |
|
|
try { // closing the file |
244 |
|
|
if (fileIn != null) fileIn.close(); |
245 |
|
|
} catch (IOException e) { |
246 |
|
|
System.err.println("Couldn't close file "+connFile); |
247 |
|
|
e.printStackTrace(); |
248 |
|
|
} |
249 |
|
|
|
250 |
|
|
} |
251 |
|
|
|
252 |
duarte |
97 |
public String getDbname() { |
253 |
|
|
return dbname; |
254 |
|
|
} |
255 |
|
|
|
256 |
|
|
public void setDbname(String dbname) { |
257 |
|
|
this.dbname = dbname; |
258 |
|
|
} |
259 |
|
|
|
260 |
|
|
public String getHost() { |
261 |
|
|
return host; |
262 |
|
|
} |
263 |
|
|
|
264 |
|
|
public void setHost(String host) { |
265 |
|
|
this.host = host; |
266 |
|
|
} |
267 |
|
|
|
268 |
|
|
public String getPassword() { |
269 |
|
|
return password; |
270 |
|
|
} |
271 |
|
|
|
272 |
|
|
public void setPassword(String password) { |
273 |
|
|
this.password = password; |
274 |
|
|
} |
275 |
|
|
|
276 |
|
|
public String getUser() { |
277 |
|
|
return user; |
278 |
|
|
} |
279 |
|
|
|
280 |
|
|
public void setUser(String user) { |
281 |
|
|
this.user = user; |
282 |
|
|
} |
283 |
|
|
|
284 |
duarte |
55 |
public Statement createStatement() throws SQLException { |
285 |
|
|
return this.conn.createStatement(); |
286 |
|
|
} |
287 |
|
|
|
288 |
duarte |
58 |
public void executeSql(String query) throws SQLException{ |
289 |
|
|
Statement stmt; |
290 |
|
|
stmt = conn.createStatement(); |
291 |
|
|
stmt.execute(query); |
292 |
|
|
stmt.close(); |
293 |
duarte |
55 |
} |
294 |
duarte |
60 |
|
295 |
duarte |
55 |
/** |
296 |
|
|
* @param query |
297 |
|
|
* @return the first column of the first row of the result of the given query as a string |
298 |
|
|
* or null if no results were returned |
299 |
stehr |
121 |
* TODO: How to figure out whether a database error occured? |
300 |
duarte |
55 |
*/ |
301 |
|
|
public String getStringFromDb(String query) { |
302 |
|
|
Statement stmt; |
303 |
|
|
ResultSet rs; |
304 |
|
|
String result = null; |
305 |
|
|
|
306 |
|
|
try { |
307 |
|
|
|
308 |
|
|
stmt = conn.createStatement(); |
309 |
|
|
rs = stmt.executeQuery(query); |
310 |
|
|
if(rs.next()) { |
311 |
|
|
result = rs.getString(1); |
312 |
|
|
} |
313 |
|
|
rs.close(); |
314 |
|
|
stmt.close(); |
315 |
|
|
|
316 |
|
|
} // end try |
317 |
|
|
catch (SQLException e) { |
318 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
319 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
320 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
321 |
|
|
e.printStackTrace(); |
322 |
|
|
} // end catch |
323 |
|
|
|
324 |
|
|
return result; |
325 |
|
|
} |
326 |
|
|
|
327 |
|
|
/** |
328 |
|
|
* @param query |
329 |
|
|
* @return the first column of the first row of the result of the given query as an integer |
330 |
|
|
* or -1 if no results were returned |
331 |
|
|
*/ |
332 |
|
|
public int getIntFromDb(String query) { |
333 |
|
|
Statement stmt; |
334 |
|
|
ResultSet rs; |
335 |
|
|
int result = -1; |
336 |
|
|
|
337 |
|
|
try { |
338 |
|
|
|
339 |
|
|
stmt = conn.createStatement(); |
340 |
|
|
rs = stmt.executeQuery(query); |
341 |
|
|
if(rs.next()) { |
342 |
|
|
result = rs.getInt(1); |
343 |
|
|
} |
344 |
|
|
rs.close(); |
345 |
|
|
stmt.close(); |
346 |
|
|
|
347 |
|
|
} // end try |
348 |
|
|
catch (SQLException e) { |
349 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
350 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
351 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
352 |
|
|
e.printStackTrace(); |
353 |
|
|
} // end catch |
354 |
|
|
|
355 |
|
|
return result; |
356 |
|
|
} |
357 |
|
|
|
358 |
|
|
public void close() { |
359 |
|
|
|
360 |
|
|
try { |
361 |
|
|
conn.close(); |
362 |
|
|
} catch (SQLException e) { |
363 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
364 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
365 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
366 |
|
|
e.printStackTrace(); |
367 |
|
|
} // end try/catch connection |
368 |
|
|
} |
369 |
duarte |
57 |
|
370 |
|
|
/** |
371 |
|
|
* Get the actual Connection object within this MySQLConnection. In case that an external library need a Connection object |
372 |
|
|
* @return |
373 |
|
|
*/ |
374 |
|
|
public Connection getConnectionObject() { |
375 |
|
|
return this.conn; |
376 |
|
|
} |
377 |
duarte |
55 |
|
378 |
duarte |
57 |
/** |
379 |
|
|
* To print the db size info for the given db of this MySQLConnection. |
380 |
|
|
* @param dbName |
381 |
|
|
*/ |
382 |
|
|
public void printDbSizeInfo (String dbName) { |
383 |
|
|
double data = 0, index = 0, table_data = 0, table_index = 0, GB = Math.pow(2, 30); |
384 |
|
|
String Query = null, table = null; |
385 |
|
|
Statement Stmt = null; |
386 |
|
|
ResultSet RS = null; |
387 |
|
|
try { |
388 |
|
|
Query = "SHOW TABLE STATUS FROM "+dbName; |
389 |
|
|
Stmt = this.conn.createStatement(); |
390 |
|
|
RS = Stmt.executeQuery(Query); |
391 |
|
|
while (RS.next()) { |
392 |
|
|
table = RS.getString("Name"); |
393 |
|
|
table_data = RS.getDouble("Data_length"); |
394 |
|
|
table_index = RS.getDouble("Index_length"); |
395 |
|
|
data += RS.getDouble("Data_length"); |
396 |
|
|
index += RS.getDouble("Index_length"); |
397 |
|
|
System.out.println("Table "+table+"##data:"+table_data+", index:"+table_index); |
398 |
|
|
} |
399 |
|
|
RS.close(); |
400 |
|
|
Stmt.close(); |
401 |
|
|
System.out.println("Database "+dbName+" needs "+((data+index)/GB)+ " GB (data:"+(data/GB)+", index:"+(index/GB)+")."); |
402 |
|
|
} |
403 |
|
|
catch (SQLException e) { |
404 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
405 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
406 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
407 |
|
|
e.printStackTrace(); |
408 |
|
|
} // end try/catch connection |
409 |
|
|
} |
410 |
|
|
|
411 |
|
|
/** |
412 |
|
|
* To get range of a column from a table in this MySQLConnection. Note that if the connection was created without pointing |
413 |
|
|
* to a particular database then the argument table must be specified as dbname.tablename. |
414 |
|
|
* @param table |
415 |
|
|
* @param column |
416 |
|
|
* @return |
417 |
|
|
*/ |
418 |
|
|
public double[] getRange(String table, String column) { |
419 |
|
|
String query = ""; |
420 |
|
|
Statement S; |
421 |
|
|
ResultSet R; |
422 |
|
|
double[] range = new double[2]; |
423 |
|
|
try { |
424 |
|
|
query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";"; |
425 |
|
|
S = this.conn.createStatement(); |
426 |
|
|
R = S.executeQuery(query); |
427 |
|
|
if (R.next()) { |
428 |
|
|
range[0] = R.getDouble(1); |
429 |
|
|
range[1] = R.getDouble(2); |
430 |
|
|
} |
431 |
|
|
R.close(); |
432 |
|
|
S.close(); |
433 |
|
|
} // end try |
434 |
|
|
catch (SQLException e) { |
435 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
436 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
437 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
438 |
|
|
e.printStackTrace(); |
439 |
|
|
} // end catch |
440 |
|
|
return range; |
441 |
|
|
} |
442 |
|
|
|
443 |
|
|
/** |
444 |
|
|
* To get range of a column from a table in this MySQLConnection using a given WHERE condition. Note that if |
445 |
|
|
* the connection was created without pointing to a particular database then the argument table must |
446 |
|
|
* be specified as dbname.tablename. |
447 |
|
|
* @param table |
448 |
|
|
* @param column |
449 |
|
|
* @param whereStr |
450 |
|
|
* @return |
451 |
|
|
*/ |
452 |
|
|
public double[] getRange(String table, String column, String whereStr) { |
453 |
|
|
String query = ""; |
454 |
|
|
Statement S; |
455 |
|
|
ResultSet R; |
456 |
|
|
double[] range = new double[2]; |
457 |
|
|
try { |
458 |
|
|
query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+whereStr+");"; |
459 |
|
|
S = this.conn.createStatement(); |
460 |
|
|
R = S.executeQuery(query); |
461 |
|
|
if (R.next()) { |
462 |
|
|
range[0] = R.getDouble(1); |
463 |
|
|
range[1] = R.getDouble(2); |
464 |
|
|
} |
465 |
|
|
R.close(); |
466 |
|
|
S.close(); |
467 |
|
|
} // end try |
468 |
|
|
catch (SQLException e) { |
469 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
470 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
471 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
472 |
|
|
e.printStackTrace(); |
473 |
|
|
} // end catch |
474 |
|
|
return range; |
475 |
|
|
} |
476 |
duarte |
55 |
|
477 |
duarte |
58 |
/** |
478 |
duarte |
60 |
* To get all indexes names for a certain table. Note the MySQLConnection object must be created with a non-blank database. |
479 |
|
|
* Using INFORMATION_SCHEMA db, only works from mysql 5.0 |
480 |
duarte |
58 |
* @param table |
481 |
|
|
* @return |
482 |
|
|
*/ |
483 |
|
|
public String[] getAllIndexes4Table(String table) { |
484 |
|
|
ArrayList<String> indexesAL=new ArrayList<String>(); |
485 |
|
|
String query; |
486 |
|
|
Statement S; |
487 |
|
|
ResultSet R; |
488 |
|
|
try { |
489 |
duarte |
63 |
query = "SELECT DISTINCT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='"+dbname+"' AND TABLE_NAME='"+table+"';"; |
490 |
duarte |
58 |
S = this.conn.createStatement(); |
491 |
|
|
R = S.executeQuery(query); |
492 |
|
|
while (R.next()) { |
493 |
|
|
indexesAL.add(R.getString(1)); |
494 |
|
|
} |
495 |
|
|
R.close(); |
496 |
|
|
S.close(); |
497 |
|
|
} // end try |
498 |
|
|
catch (SQLException e) { |
499 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
500 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
501 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
502 |
|
|
e.printStackTrace(); |
503 |
|
|
} // end catch |
504 |
|
|
String[] indexes=new String[indexesAL.size()]; |
505 |
|
|
int i=0; |
506 |
|
|
for (String index:indexesAL) { |
507 |
|
|
indexes[i]=index; |
508 |
|
|
i++; |
509 |
|
|
} |
510 |
|
|
return indexes; |
511 |
|
|
} |
512 |
duarte |
60 |
|
513 |
duarte |
61 |
/** |
514 |
duarte |
64 |
* Gets an array of Strings with all queries necessary to create all the indexes for a certain table |
515 |
|
|
* @param table |
516 |
|
|
* @return |
517 |
|
|
*/ |
518 |
|
|
public String[] getCreateIndex4Table(String table){ |
519 |
|
|
String[] indexes=this.getAllIndexes4Table(table); |
520 |
|
|
String[] createIndexQueries=new String[indexes.length]; |
521 |
|
|
for (int i=0;i<indexes.length;i++){ |
522 |
|
|
String index=indexes[i]; |
523 |
|
|
try { |
524 |
|
|
Statement S; |
525 |
|
|
ResultSet R; |
526 |
|
|
String query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS " + |
527 |
|
|
"WHERE TABLE_SCHEMA='"+dbname+"' AND TABLE_NAME='"+table+"' AND INDEX_NAME='"+index+"' " + |
528 |
|
|
"ORDER BY SEQ_IN_INDEX;"; |
529 |
|
|
S = this.conn.createStatement(); |
530 |
|
|
R = S.executeQuery(query); |
531 |
|
|
String createIndexStr="CREATE INDEX "+index+" ON "+table+" ("; |
532 |
|
|
while (R.next()) { |
533 |
|
|
String colName = R.getString(1); |
534 |
|
|
createIndexStr+=colName+","; |
535 |
|
|
} |
536 |
|
|
createIndexStr=createIndexStr.substring(0,createIndexStr.lastIndexOf(",")); |
537 |
|
|
createIndexStr+=");"; |
538 |
|
|
createIndexQueries[i]=createIndexStr; |
539 |
|
|
R.close(); |
540 |
|
|
S.close(); |
541 |
|
|
} // end try |
542 |
|
|
catch (SQLException e) { |
543 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
544 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
545 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
546 |
|
|
e.printStackTrace(); |
547 |
|
|
} // end catch |
548 |
|
|
} |
549 |
|
|
return createIndexQueries; |
550 |
|
|
} |
551 |
|
|
|
552 |
|
|
/** |
553 |
duarte |
93 |
* To get the column type for a certain column and table |
554 |
|
|
* @param table |
555 |
|
|
* @param column |
556 |
|
|
* @return |
557 |
|
|
*/ |
558 |
|
|
public String getColumnType(String table,String column){ |
559 |
|
|
String query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS " + |
560 |
|
|
"WHERE TABLE_SCHEMA='"+this.dbname+"' AND TABLE_NAME='"+table+"' AND COLUMN_NAME='"+column+"';"; |
561 |
|
|
String colType = this.getStringFromDb(query); |
562 |
|
|
return colType; |
563 |
|
|
} |
564 |
|
|
|
565 |
|
|
/** |
566 |
|
|
* To findout whether a key (i.e. column) is numeric-based or text-based |
567 |
|
|
* @param table |
568 |
|
|
* @param key |
569 |
|
|
* @return true if is numeric-based, false if is text-based |
570 |
|
|
*/ |
571 |
|
|
public boolean isKeyNumeric(String table, String key){ |
572 |
|
|
boolean isNumeric = false; |
573 |
|
|
String colType = getColumnType(table,key); |
574 |
|
|
if (colType.contains("int") || colType.contains("INT")){ |
575 |
|
|
isNumeric = true; |
576 |
|
|
} |
577 |
|
|
else if (colType.contains("char") || colType.contains("CHAR")){ |
578 |
|
|
isNumeric = false; |
579 |
|
|
} |
580 |
|
|
else { |
581 |
|
|
System.err.println("The key '"+key+"' from table '"+table+"' is neither numeric-based (int) nor text-based (char/varchar). Check what's wrong!"); |
582 |
|
|
} |
583 |
|
|
return isNumeric; |
584 |
|
|
} |
585 |
|
|
|
586 |
|
|
/** |
587 |
duarte |
83 |
* To get all tables for this MySQLConnection's database. |
588 |
|
|
* @return an array of String with all table names |
589 |
|
|
*/ |
590 |
|
|
public String[] getTables4Db(){ |
591 |
|
|
String[] tables=null; |
592 |
|
|
ArrayList<String> tablesAL=new ArrayList<String>(); |
593 |
|
|
String query="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+dbname+"' ORDER BY TABLE_NAME DESC;"; |
594 |
|
|
try { |
595 |
|
|
Statement S = this.conn.createStatement(); |
596 |
|
|
ResultSet R=S.executeQuery(query); |
597 |
|
|
while (R.next()){ |
598 |
|
|
tablesAL.add(R.getString(1)); |
599 |
|
|
} |
600 |
|
|
S.close(); |
601 |
|
|
R.close(); |
602 |
|
|
tables=new String[tablesAL.size()]; |
603 |
|
|
for (int i=0;i<tablesAL.size();i++) { |
604 |
|
|
tables[i]=tablesAL.get(i); |
605 |
|
|
} |
606 |
|
|
} |
607 |
|
|
catch(SQLException e){ |
608 |
|
|
System.err.println("Couldn't get table names from "+host+" for db="+dbname); |
609 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
610 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
611 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
612 |
|
|
e.printStackTrace(); |
613 |
|
|
} |
614 |
|
|
return tables; |
615 |
|
|
} |
616 |
duarte |
84 |
|
617 |
|
|
/** |
618 |
|
|
* To get all distinct ordered ids from a certain key and table from this MySQLConnection |
619 |
|
|
* @param key the key name |
620 |
|
|
* @param table the table name |
621 |
|
|
* @return int array containing all ids |
622 |
|
|
*/ |
623 |
duarte |
93 |
public Integer[] getAllNumIds4KeyAndTable(String key, String table){ |
624 |
|
|
Integer[] allIds=null; |
625 |
duarte |
84 |
try { |
626 |
|
|
Statement S=conn.createStatement(); |
627 |
|
|
String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";"; |
628 |
|
|
ResultSet R=S.executeQuery(query); |
629 |
|
|
ArrayList<Integer> idsAL=new ArrayList<Integer>(); |
630 |
|
|
while (R.next()){ |
631 |
|
|
idsAL.add(R.getInt(1)); |
632 |
|
|
} |
633 |
duarte |
93 |
allIds=new Integer[idsAL.size()]; |
634 |
duarte |
84 |
for (int i=0;i<idsAL.size();i++) { |
635 |
|
|
allIds[i]=idsAL.get(i); |
636 |
|
|
} |
637 |
|
|
R.close(); |
638 |
|
|
S.close(); |
639 |
|
|
} |
640 |
|
|
catch (SQLException e){ |
641 |
|
|
e.printStackTrace(); |
642 |
|
|
} |
643 |
|
|
return allIds; |
644 |
|
|
} |
645 |
duarte |
83 |
|
646 |
duarte |
93 |
/** |
647 |
|
|
* To get all distinct ordered text (i.e. char/varchar column) ids from a certain key and table from this MySQLConnection |
648 |
|
|
* @param key the key name |
649 |
|
|
* @param table the table name |
650 |
|
|
* @return int array containing all ids |
651 |
|
|
*/ |
652 |
|
|
public String[] getAllTxtIds4KeyAndTable(String key, String table){ |
653 |
|
|
String[] allIds=null; |
654 |
|
|
try { |
655 |
|
|
Statement S=conn.createStatement(); |
656 |
|
|
String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";"; |
657 |
|
|
ResultSet R=S.executeQuery(query); |
658 |
|
|
ArrayList<String> idsAL=new ArrayList<String>(); |
659 |
|
|
while (R.next()){ |
660 |
|
|
idsAL.add(R.getString(1)); |
661 |
|
|
} |
662 |
|
|
allIds=new String[idsAL.size()]; |
663 |
|
|
for (int i=0;i<idsAL.size();i++) { |
664 |
|
|
allIds[i]=idsAL.get(i); |
665 |
|
|
} |
666 |
|
|
R.close(); |
667 |
|
|
S.close(); |
668 |
|
|
} |
669 |
|
|
catch (SQLException e){ |
670 |
|
|
e.printStackTrace(); |
671 |
|
|
} |
672 |
|
|
return allIds; |
673 |
|
|
} |
674 |
duarte |
84 |
|
675 |
duarte |
94 |
/** |
676 |
|
|
* To get all distinct ordered ids from a certain key and table from this MySQLConnection |
677 |
|
|
* @param key the key name |
678 |
|
|
* @param table the table name |
679 |
|
|
* @return int array containing all ids |
680 |
|
|
*/ |
681 |
|
|
public Object[] getAllIds4KeyAndTable(String key, String table){ |
682 |
|
|
Object[] allIds=null; |
683 |
|
|
try { |
684 |
|
|
Statement S=conn.createStatement(); |
685 |
|
|
String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";"; |
686 |
|
|
ResultSet R=S.executeQuery(query); |
687 |
|
|
ArrayList<String> idsAL=new ArrayList<String>(); |
688 |
|
|
while (R.next()){ |
689 |
|
|
idsAL.add(R.getString(1)); |
690 |
|
|
} |
691 |
|
|
if (isKeyNumeric(table,key)){ |
692 |
|
|
allIds=new Integer[idsAL.size()]; |
693 |
|
|
for (int i=0;i<idsAL.size();i++) { |
694 |
|
|
allIds[i]=Integer.parseInt(idsAL.get(i)); |
695 |
|
|
} |
696 |
|
|
} else { |
697 |
|
|
allIds=new String[idsAL.size()]; |
698 |
|
|
for (int i=0;i<idsAL.size();i++){ |
699 |
|
|
allIds[i]=idsAL.get(i); |
700 |
|
|
} |
701 |
|
|
} |
702 |
|
|
R.close(); |
703 |
|
|
S.close(); |
704 |
|
|
} |
705 |
|
|
catch (SQLException e){ |
706 |
|
|
e.printStackTrace(); |
707 |
|
|
} |
708 |
|
|
return allIds; |
709 |
|
|
} |
710 |
|
|
|
711 |
duarte |
83 |
/** |
712 |
duarte |
61 |
* To set the sql_mode of this connection. |
713 |
|
|
* @param sqlmode either NO_UNSIGNED_SUBTRACTION or blank |
714 |
|
|
*/ |
715 |
|
|
public void setSqlMode(String sqlmode) { |
716 |
|
|
String query="SET SESSION sql_mode='"+sqlmode+"';"; |
717 |
|
|
try { |
718 |
|
|
this.executeSql(query); |
719 |
|
|
} |
720 |
|
|
catch (SQLException e){ |
721 |
|
|
System.err.println("Couldn't change the sql mode to "+sqlmode); |
722 |
|
|
System.err.println("SQLException: " + e.getMessage()); |
723 |
|
|
System.err.println("SQLState: " + e.getSQLState()); |
724 |
|
|
System.err.println("VendorError: " + e.getErrorCode()); |
725 |
|
|
e.printStackTrace(); |
726 |
|
|
} |
727 |
|
|
} |
728 |
|
|
|
729 |
duarte |
55 |
} |