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