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 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:	64
Committed:	Thu Apr 6 14:47:09 2006 UTC (18 years, 5 months ago) by duarte
File size:	16015 byte(s)
Log Message:	Added method getCreateIndex4Table
Line	User	Rev	File contents
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			/**
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	duarte	59	* 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	duarte	55	*/
60			public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName) {
61	duarte	62	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	duarte	55	}
77
78			/**
79			* Connect to database using the given server, user, password, dbname and port
80	duarte	59	* Only needed if mysql server uses a port different from the standard 3306
81	duarte	55	*/
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	duarte	57	private void loadMySQLDriver() {
121	duarte	55	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	duarte	60	/**
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	duarte	55	public Statement createStatement() throws SQLException {
206			return this.conn.createStatement();
207			}
208
209	duarte	58	public void executeSql(String query) throws SQLException{
210			Statement stmt;
211			stmt = conn.createStatement();
212			stmt.execute(query);
213			stmt.close();
214	duarte	55	}
215	duarte	60
216	duarte	55	/**
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	duarte	57
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	duarte	55
298	duarte	57	/**
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	duarte	55
397	duarte	58	/**
398	duarte	60	* 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	duarte	58	* @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	duarte	63	query = "SELECT DISTINCT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='"+dbname+"' AND TABLE_NAME='"+table+"';";
410	duarte	58	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	duarte	60
433	duarte	61	/**
434	duarte	64	* 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	duarte	61	* 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	duarte	55	}