ViewVC Help

View File | Revision Log | Show Annotations | View Changeset | Root Listing

root/owl/tags/aglappe-0.6/tools/MySQLConnection.java

#	Line 10 | Line 10
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
#	Line 53 | Line 54
54		}
55
56		/**
57	<	* Connect to database using the given server, user, password and dbname
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();
61	>	loadMySQLDriver();
62		host=dbServer;
63		user=dbUserName;
64		password=dbPassword;
#	Line 70 | Line 72
72		System.err.println("SQLState:     " + e.getSQLState());
73		System.err.println("VendorError:  " + e.getErrorCode());
74		e.printStackTrace();
75	<	} // end try/catch connection
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();
#	Line 125 | Line 128
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) {
210	<	Statement stmt;
211	<	try {
212	<	stmt = conn.createStatement();
213	<	stmt.execute(query);
137	<	stmt.close();
138	<	} catch (SQLException e) {
139	<	System.err.println("SQLException: " + e.getMessage());
140	<	System.err.println("SQLState:     " + e.getSQLState());
141	<	System.err.println("VendorError:  " + e.getErrorCode());
142	<	e.printStackTrace();
143	<	} // end catch
209	>	public void executeSql(String query) throws SQLException{
210	>	Statement stmt;
211	>	stmt = conn.createStatement();
212	>	stmt.execute(query);
213	>	stmt.close();
214		}
215	<
215	>
216		/**
217		* @param query
218		* @return the first column of the first row of the result of the given query as a string
#	Line 225 | Line 295
295		return this.conn;
296		}
297
228	–	/**
229	–	* Used in the constructor that gets a connFile as argument. To read the connection parameters from a connection file.
230	–	* @param connFile
231	–	*/
232	–	private void readConnectionFile(String connFile) {
233	–	// reads the values of the connFile into the static variables;
234	–	String homedir = System.getProperty("user.home");
235	–	if (connFile.length()==0) { // no file was specified
236	–	connFile=homedir+"/.my.cnf"; // assume default configuration file
237	–	}
238	–	// else the location of the connection file was given
239	–	// Open the configuration file
240	–	BufferedReader fileIn = null;
241	–	StringTokenizer str;
242	–	String item, oneLine;
243	–	// to control if the minimum necessary 3 parameters are given in file
244	–	int cfgParsPresent=0;
245	–	// setting default blank values for port and dbname, they are set to blank unless fields specified in file
246	–	port="";
247	–	dbname="";
248	–	// list the entries in the file and decompose them
249	–	try {
250	–	fileIn = new BufferedReader(new FileReader(new File(connFile))); // open BufferedReader to file connFile
251	–	while ((oneLine = fileIn.readLine()) != null ) {
252	–	// Construct a stringTokenizer for the line that we read with : delimited
253	–	str = new StringTokenizer(oneLine, "="); // true sets returnDelimiters flag
254	–	while ( str.hasMoreTokens()) {
255	–	item = str.nextToken();
256	–	if( item.equals("host")) { // mandatory parameter
257	–	host=str.nextToken();
258	–	cfgParsPresent++;
259	–	break;
260	–	} // end if host
261	–	if( item.equals("port")) { // optional parameter
262	–	port=":"+str.nextToken();
263	–	break;
264	–	} // end if port
265	–	if( item.equals("user")) { // mandatory parameter
266	–	user=str.nextToken();
267	–	cfgParsPresent++;
268	–	break;
269	–	} // end if password
270	–	if( item.equals("password")) { // mandatory parameter
271	–	password=str.nextToken();
272	–	cfgParsPresent++;
273	–	break;
274	–	} // end if password
275	–	if( item.equals("database")) { // optional parameter
276	–	dbname=str.nextToken();
277	–	break;
278	–	} // end if password
279	–	} // next token in this line
280	–	} // next line in the file
281	–	if (cfgParsPresent<3){
282	–	System.err.println("Not all mandatory parameters are given in connection file "+connFile+". Can't connect to mysql server, exiting.");
283	–	System.exit(1);
284	–	}
285	–	}
286	–	catch (IOException e) {
287	–	System.err.println("Couldn't open file "+connFile);
288	–	e.printStackTrace();
289	–	System.exit(1);
290	–	}
291	–
292	–	try { // closing the file
293	–	if (fileIn != null) fileIn.close();
294	–	} catch (IOException e) {
295	–	System.err.println("Couldn't close file "+connFile);
296	–	e.printStackTrace();
297	–	}
298	–
299	–	}
300	–
298		/**
299		* To print the db size info for the given db of this MySQLConnection.
300		* @param dbName
#	Line 397 | Line 394
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	+	* Gets an array of Strings with all queries necessary to create all the indexes for a certain table
435	+	* @param table
436	+	* @return
437	+	*/
438	+	public String[] getCreateIndex4Table(String table){
439	+	String[] indexes=this.getAllIndexes4Table(table);
440	+	String[] createIndexQueries=new String[indexes.length];
441	+	for (int i=0;i<indexes.length;i++){
442	+	String index=indexes[i];
443	+	try {
444	+	Statement S;
445	+	ResultSet R;
446	+	String query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS " +
447	+	"WHERE TABLE_SCHEMA='"+dbname+"' AND TABLE_NAME='"+table+"' AND INDEX_NAME='"+index+"' " +
448	+	"ORDER BY SEQ_IN_INDEX;";
449	+	S = this.conn.createStatement();
450	+	R = S.executeQuery(query);
451	+	String createIndexStr="CREATE INDEX "+index+" ON "+table+" (";
452	+	while (R.next()) {
453	+	String colName = R.getString(1);
454	+	createIndexStr+=colName+",";
455	+	}
456	+	createIndexStr=createIndexStr.substring(0,createIndexStr.lastIndexOf(","));
457	+	createIndexStr+=");";
458	+	createIndexQueries[i]=createIndexStr;
459	+	R.close();
460	+	S.close();
461	+	} // end try
462	+	catch (SQLException e) {
463	+	System.err.println("SQLException: " + e.getMessage());
464	+	System.err.println("SQLState:     " + e.getSQLState());
465	+	System.err.println("VendorError:  " + e.getErrorCode());
466	+	e.printStackTrace();
467	+	} // end catch
468	+	}
469	+	return createIndexQueries;
470	+	}
471	+
472	+	/**
473	+	* To set the sql_mode of this connection.
474	+	* @param sqlmode either NO_UNSIGNED_SUBTRACTION or blank
475	+	*/
476	+	public void setSqlMode(String sqlmode) {
477	+	String query="SET SESSION sql_mode='"+sqlmode+"';";
478	+	try {
479	+	this.executeSql(query);
480	+	}
481	+	catch (SQLException e){
482	+	System.err.println("Couldn't change the sql mode to "+sqlmode);
483	+	System.err.println("SQLException: " + e.getMessage());
484	+	System.err.println("SQLState:     " + e.getSQLState());
485	+	System.err.println("VendorError:  " + e.getErrorCode());
486	+	e.printStackTrace();
487	+	}
488	+	}
489	+
490		}

Diff Legend

–	Removed lines
+	Added lines
<	Changed lines
>	Changed lines