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 get all tables for this MySQLConnection's database.
     * @return an array of String with all table names
     */
        public String[] getTables4Db(){
                String[] tables=null;
                ArrayList<String> tablesAL=new ArrayList<String>();
                String query="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+dbname+"' ORDER BY TABLE_NAME DESC;";
                try {                   
                        Statement S = this.conn.createStatement();
                        ResultSet R=S.executeQuery(query);
                        while (R.next()){
                                tablesAL.add(R.getString(1));
                        }
                        S.close();
                        R.close();
                        conn.close();
                        tables=new String[tablesAL.size()];
                        for (int i=0;i<tablesAL.size();i++) {
                                tables[i]=tablesAL.get(i);
                        }
                }
                catch(SQLException e){                  
                        System.err.println("Couldn't get table names from "+host+" for db="+dbname);
            System.err.println("SQLException: " + e.getMessage());
            System.err.println("SQLState:     " + e.getSQLState());
            System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                }
                return tables;
        }

    /**
     * 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:	83
Committed:	Wed Apr 19 14:02:35 2006 UTC (18 years, 5 months ago) by duarte
File size:	17351 byte(s)
Log Message:	Got rid of getTables4Db in DataDistribution Add method getTables4Db to MySQLConnection. Makes a lot more sense. Changed accordingly the only call to the method in DataDistribution's method chkCounts
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	duarte	80	/**
36	duarte	55	* Connect to database using the given server, user and password
37	duarte	80	* @param dbServer
38			* @param dbUserName
39			* @param dbPassword
40	duarte	55	*/
41			public MySQLConnection(String dbServer, String dbUserName, String dbPassword) {
42			loadMySQLDriver();
43			host=dbServer;
44			user=dbUserName;
45			password=dbPassword;
46			port="";
47			dbname="";
48			String connStr="jdbc:mysql://"+host+port+"/"+dbname;
49			try {
50			conn = DriverManager.getConnection(connStr, user, password);
51			} catch (SQLException e) {
52			System.err.println("SQLException: " + e.getMessage());
53			System.err.println("SQLState: " + e.getSQLState());
54			System.err.println("VendorError: " + e.getErrorCode());
55			e.printStackTrace();
56			} // end try/catch connection
57			}
58
59			/**
60	duarte	59	* Connect to database using the given server, user, password and dbname.
61			* Please always use this constructor in preference rather than constructing without specifying a database
62	duarte	80	* @param dbServer
63			* @param dbUserName
64			* @param dbPassword
65			* @param dbName
66	duarte	55	*/
67			public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName) {
68	duarte	62	loadMySQLDriver();
69			host=dbServer;
70			user=dbUserName;
71			password=dbPassword;
72			port="";
73			dbname=dbName;
74			String connStr="jdbc:mysql://"+host+port+"/"+dbname;
75			try {
76			conn = DriverManager.getConnection(connStr, user, password);
77			} catch (SQLException e) {
78			System.err.println("SQLException: " + e.getMessage());
79			System.err.println("SQLState: " + e.getSQLState());
80			System.err.println("VendorError: " + e.getErrorCode());
81			e.printStackTrace();
82			} // end try/catch connection
83	duarte	55	}
84
85			/**
86			* Connect to database using the given server, user, password, dbname and port
87	duarte	59	* Only needed if mysql server uses a port different from the standard 3306
88	duarte	80	* @param dbServer
89			* @param dbUserName
90			* @param dbPassword
91			* @param dbName
92			* @param portNum
93	duarte	55	*/
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	duarte	80	* Connect to database giving a connection file
114			* @param connFile the connection file's name
115	duarte	55	*/
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	duarte	57	private void loadMySQLDriver() {
134	duarte	55	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	duarte	60	/**
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	duarte	55	public Statement createStatement() throws SQLException {
219			return this.conn.createStatement();
220			}
221
222	duarte	58	public void executeSql(String query) throws SQLException{
223			Statement stmt;
224			stmt = conn.createStatement();
225			stmt.execute(query);
226			stmt.close();
227	duarte	55	}
228	duarte	60
229	duarte	55	/**
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	duarte	57
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	duarte	55
311	duarte	57	/**
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	duarte	55
410	duarte	58	/**
411	duarte	60	* 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	duarte	58	* @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	duarte	63	query = "SELECT DISTINCT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='"+dbname+"' AND TABLE_NAME='"+table+"';";
423	duarte	58	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	duarte	60
446	duarte	61	/**
447	duarte	64	* 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	duarte	83	* To get all tables for this MySQLConnection's database.
487			* @return an array of String with all table names
488			*/
489			public String[] getTables4Db(){
490			String[] tables=null;
491			ArrayList<String> tablesAL=new ArrayList<String>();
492			String query="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+dbname+"' ORDER BY TABLE_NAME DESC;";
493			try {
494			Statement S = this.conn.createStatement();
495			ResultSet R=S.executeQuery(query);
496			while (R.next()){
497			tablesAL.add(R.getString(1));
498			}
499			S.close();
500			R.close();
501			conn.close();
502			tables=new String[tablesAL.size()];
503			for (int i=0;i<tablesAL.size();i++) {
504			tables[i]=tablesAL.get(i);
505			}
506			}
507			catch(SQLException e){
508			System.err.println("Couldn't get table names from "+host+" for db="+dbname);
509			System.err.println("SQLException: " + e.getMessage());
510			System.err.println("SQLState: " + e.getSQLState());
511			System.err.println("VendorError: " + e.getErrorCode());
512			e.printStackTrace();
513			}
514			return tables;
515			}
516
517			/**
518	duarte	61	* To set the sql_mode of this connection.
519			* @param sqlmode either NO_UNSIGNED_SUBTRACTION or blank
520			*/
521			public void setSqlMode(String sqlmode) {
522			String query="SET SESSION sql_mode='"+sqlmode+"';";
523			try {
524			this.executeSql(query);
525			}
526			catch (SQLException e){
527			System.err.println("Couldn't change the sql mode to "+sqlmode);
528			System.err.println("SQLException: " + e.getMessage());
529			System.err.println("SQLState: " + e.getSQLState());
530			System.err.println("VendorError: " + e.getErrorCode());
531			e.printStackTrace();
532			}
533			}
534
535	duarte	55	}