trunk/tools/MySQLConnection.java

package tools;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;
import java.io.IOException;
import java.util.ArrayList;

public class MySQLConnection {

        /*--------------------- constants -----------------------*/
        
    // -- constants for database connection --
    static final String    HOST =                       "white";        
    static final String    USER =                   "";
    static final String    PASSWORD =           "nieve";
        
        /*------------------- member variables --------------------*/
        
        public Connection conn; 
        private String host;
        private String user;
        private String password=PASSWORD;
        private String port;
        private String dbname;
        
        /*-------------------- constructors -----------------------*/

        /**
         * Connect to database using the given server, user and password
         */
        public MySQLConnection(String dbServer, String dbUserName, String dbPassword) {
                loadMySQLDriver();
                host=dbServer;
                user=dbUserName;
                password=dbPassword;
                port="";
                dbname="";              
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                            
        }

        /**
         * Connect to database using the given server, user, password and dbname.
         * Please always use this constructor in preference rather than constructing without specifying a database
         */
        public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName) {
                loadMySQLDriver(); 
                host=dbServer;
                user=dbUserName;
                password=dbPassword;
                port="";
                dbname=dbName;          
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                                           
        }

        /**
         * Connect to database using the given server, user, password, dbname and port
         * Only needed if mysql server uses a port different from the standard 3306
         */
        public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName, int portNum) {
                loadMySQLDriver();
                host=dbServer;
                user=dbUserName;
                password=dbPassword;
                port=":"+portNum;
                dbname=dbName;          
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                            
        }
                
        /**
         * Connect to database giving a connection file 
         */
        public MySQLConnection(String connFile) {               
                loadMySQLDriver();
                readConnectionFile(connFile);
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                            
        }
        
        
        /*---------------------- methods -------------------------*/
        
        private void loadMySQLDriver() {
                try {
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                }
                catch(Exception e) {
                        e.printStackTrace();
                        System.err.println("An exception occurred while loading the mysql jdbc driver, exiting.");
                        System.exit(1);
                }
        }

        /**
         * Used in the constructor that gets a connFile as argument. To read the connection parameters from a connection file.
         * @param connFile
         */
        private void readConnectionFile(String connFile) {
                // reads the values of the connFile into the static variables; 
                String homedir = System.getProperty("user.home"); 
                if (connFile.length()==0) { // no file was specified
                        connFile=homedir+"/.my.cnf"; // assume default configuration file 
                }               
                // else the location of the connection file was given           
                // Open the configuration file          
                BufferedReader fileIn = null;
                StringTokenizer str;
                String item, oneLine;
                // to control if the minimum mandatory 4 parameters are given in file
                int paramCount=0; 
                // setting default blank values for port and dbname, they are set to blank unless fields specified in file
                port="";
                dbname="";
                // list the entries in the file and decompose them 
                try {
                        fileIn = new BufferedReader(new FileReader(new File(connFile))); // open BufferedReader to file connFile 
                        while ((oneLine = fileIn.readLine()) != null ) {
                                // Construct a stringTokenizer for the line that we read with : delimited
                                str = new StringTokenizer(oneLine, "="); // true sets returnDelimiters flag 
                                while ( str.hasMoreTokens()) {
                                        item = str.nextToken();
                                        if( item.equals("host")) { // mandatory parameter
                                                host=str.nextToken();
                                                paramCount++;
                                                break; 
                                        } // end if host 
                                        if( item.equals("port")) { // optional parameter
                                                port=":"+str.nextToken();
                                                break; 
                                        } // end if port
                                        if( item.equals("user")) { // mandatory parameter
                                                user=str.nextToken();
                                                paramCount++;
                                                break; 
                                        } // end if password 
                                        if( item.equals("password")) { // mandatory parameter
                                                password=str.nextToken();
                                                paramCount++;
                                                break; 
                                        } // end if password
                                        if( item.equals("database")) { // mandatory parameter
                                                dbname=str.nextToken();
                                                paramCount++;
                                                break; 
                                        } // end if password                                    
                                } // next token in this line 
                        } // next line in the file
                        if (paramCount<4){
                                System.err.println("Not all mandatory parameters are given in connection file "+connFile+". Can't connect to mysql server, exiting.");
                                System.exit(1);
                        }                       
                } 
                catch (IOException e) {
                        System.err.println("Couldn't open file "+connFile);                     
                        e.printStackTrace();
                        System.exit(1);
                }  
                
                try { // closing the file
                        if (fileIn != null) fileIn.close();                     
                } catch (IOException e) {
                        System.err.println("Couldn't close file "+connFile);
                        e.printStackTrace(); 
                }
                
        }

        public Statement createStatement() throws SQLException {
                return this.conn.createStatement();
        }
        
        public void executeSql(String query) throws SQLException{
                Statement stmt;         
                stmt = conn.createStatement();  
                stmt.execute(query);
                stmt.close();               
        }
                
        /** 
         * @param query
         * @return the first column of the first row of the result of the given query as a string
         * or null if no results were returned
         */     
        public String getStringFromDb(String query) {
                Statement    stmt;
                ResultSet    rs;
                String       result = null;
                
                try { 
                        
                    stmt = conn.createStatement();
                    rs = stmt.executeQuery(query);
                    if(rs.next()) {
                        result = rs.getString(1);
                    }
                    rs.close();
                    stmt.close(); 
                    
                } // end try
                catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                    System.err.println("SQLState:     " + e.getSQLState());
                    System.err.println("VendorError:  " + e.getErrorCode());
                    e.printStackTrace();
                } // end catch                          
                
                return result;
        }
        
        /** 
         * @param query
         * @return the first column of the first row of the result of the given query as an integer
         * or -1 if no results were returned
         */
        public int getIntFromDb(String query) {
                Statement    stmt;
                ResultSet    rs;
                int          result = -1;

                try { 
                        
                    stmt = conn.createStatement();
                    rs = stmt.executeQuery(query);
                    if(rs.next()) {
                        result = rs.getInt(1);
                    }
                    rs.close();
                    stmt.close(); 
                    
                } // end try
                catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                    System.err.println("SQLState:     " + e.getSQLState());
                    System.err.println("VendorError:  " + e.getErrorCode());
                    e.printStackTrace();
                } // end catch                  
                
                return result;
        }
        
        public void close() {
                
                try {
                        conn.close();
            } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
            } // end try/catch connection 
        }
        
        /**
         * Get the actual Connection object within this MySQLConnection. In case that an external library need a Connection object
         * @return
         */
        public Connection getConnectionObject() {
                return this.conn;
        }

    /**
     * To print the db size info for the given db of this MySQLConnection.
     * @param dbName
     */
        public void printDbSizeInfo (String dbName) {
                double data = 0, index = 0, table_data = 0, table_index = 0, GB = Math.pow(2, 30);
                String Query = null, table = null;
                Statement Stmt = null;
                ResultSet RS = null;            
                try {
                    Query = "SHOW TABLE STATUS FROM "+dbName;
                    Stmt = this.conn.createStatement();
                    RS = Stmt.executeQuery(Query);
                    while (RS.next()) {
                                table = RS.getString("Name");
                                table_data = RS.getDouble("Data_length");
                                table_index = RS.getDouble("Index_length");
                                data += RS.getDouble("Data_length");
                                index += RS.getDouble("Index_length");
                                System.out.println("Table "+table+"##data:"+table_data+", index:"+table_index);
                    }
                    RS.close();
                    Stmt.close();       
                    System.out.println("Database "+dbName+" needs "+((data+index)/GB)+ " GB (data:"+(data/GB)+", index:"+(index/GB)+").");
                } 
                catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                    System.err.println("SQLState:     " + e.getSQLState());
                    System.err.println("VendorError:  " + e.getErrorCode());
                    e.printStackTrace();
                } // end try/catch connection
    }

    /**
     * To get range of a column from a table in this MySQLConnection. Note that if the connection was created without pointing 
     * to a particular database then the argument table must be specified as dbname.tablename.
     * @param table
     * @param column
     * @return
     */
    public double[] getRange(String table, String column) {
        String query = "";
        Statement S;
        ResultSet R;
        double[] range = new double[2];
        try { 
            query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";";
            S = this.conn.createStatement();
            R = S.executeQuery(query);          
            if (R.next()) {
                        range[0] = R.getDouble(1);
                        range[1] = R.getDouble(2);
            } 
            R.close();
            S.close();
        } // end try
        catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
            System.err.println("VendorError:  " + e.getErrorCode());
            e.printStackTrace();
        } // end catch          
        return range;           
    }

    /**
     * To get range of a column from a table in this MySQLConnection using a given WHERE condition. Note that if 
     * the connection was created without pointing to a particular database then the argument table must 
     * be specified as dbname.tablename.
     * @param table
     * @param column
     * @param whereStr
     * @return
     */
    public double[] getRange(String table, String column, String whereStr) {
        String query = "";
        Statement S;
        ResultSet R;
        double[] range = new double[2];
        try { 
            query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+whereStr+");";
            S = this.conn.createStatement();
            R = S.executeQuery(query);          
            if (R.next()) {
                        range[0] = R.getDouble(1);
                        range[1] = R.getDouble(2);
            } 
            R.close();
            S.close();
        } // end try
        catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
            System.err.println("VendorError:  " + e.getErrorCode());
            e.printStackTrace();
        } // end catch          
        return range;           
    }
        
    /**
     * To get all indexes names for a certain table. Note the MySQLConnection object must be created with a non-blank database.
     * Using INFORMATION_SCHEMA db, only works from mysql 5.0
     * @param table
     * @return
     */
    public String[] getAllIndexes4Table(String table) {
        ArrayList<String> indexesAL=new ArrayList<String>();
        String query;
        Statement S;
        ResultSet R;
        try { 
            query = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='"+dbname+"' AND TABLE_NAME='"+table+"';";
            S = this.conn.createStatement();
            R = S.executeQuery(query);          
            while (R.next()) {
                        indexesAL.add(R.getString(1));                  
            } 
            R.close();
            S.close();
        } // end try                    
        catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
            System.err.println("VendorError:  " + e.getErrorCode());
            e.printStackTrace();
        } // end catch
        String[] indexes=new String[indexesAL.size()];
        int i=0;
        for (String index:indexesAL) {
                indexes[i]=index;
                i++;
        }
        return indexes;
    }
    
    /**
     * To set the sql_mode of this connection. 
     * @param sqlmode either NO_UNSIGNED_SUBTRACTION or blank
     */
    public void setSqlMode(String sqlmode) {
        String query="SET SESSION sql_mode='"+sqlmode+"';";
        try {
                this.executeSql(query);
        }
        catch (SQLException e){
                System.err.println("Couldn't change the sql mode to "+sqlmode);
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
            System.err.println("VendorError:  " + e.getErrorCode());
            e.printStackTrace();                
        }
    }
    
}
Revision:	62
Committed:	Thu Apr 6 12:15:37 2006 UTC (18 years, 5 months ago) by duarte
File size:	14391 byte(s)
Log Message:	Got rid of method dbExists. Was useless and not working.
Line	File contents
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 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	}