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 the column type for a certain column and table
     * @param table
     * @param column
     * @return
     */
    public String getColumnType(String table,String column){
        String query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS " +
                                        "WHERE TABLE_SCHEMA='"+this.dbname+"' AND TABLE_NAME='"+table+"' AND COLUMN_NAME='"+column+"';";
        String colType = this.getStringFromDb(query);
        return colType;
    }
    
    /**
     * To findout whether a key (i.e. column) is numeric-based or text-based
     * @param table
     * @param key
     * @return true if is numeric-based, false if is text-based
     */
    public boolean isKeyNumeric(String table, String key){
        boolean isNumeric = false;
        String colType = getColumnType(table,key);
        if (colType.contains("int") || colType.contains("INT")){
                isNumeric = true;
        }
        else if (colType.contains("char") || colType.contains("CHAR")){
                isNumeric = false;
        }
        else {
                System.err.println("The key '"+key+"' from table '"+table+"' is neither numeric-based (int) nor text-based (char/varchar). Check what's wrong!");
        }
        return isNumeric;
    }
    
    /**
     * 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();
                        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 get all distinct ordered ids from a certain key and table from this MySQLConnection
         * @param key the key name
         * @param table the table name
         * @return int array containing all ids 
         */
        public Integer[] getAllNumIds4KeyAndTable(String key, String table){
                Integer[] allIds=null;          
                try {
                        Statement S=conn.createStatement();
                        String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";";
                        ResultSet R=S.executeQuery(query);
                        ArrayList<Integer> idsAL=new ArrayList<Integer>();
                        while (R.next()){
                                idsAL.add(R.getInt(1));
                        }
                        allIds=new Integer[idsAL.size()];
                        for (int i=0;i<idsAL.size();i++) {
                                allIds[i]=idsAL.get(i);
                        }
                        R.close();
                        S.close();
                }
                catch (SQLException e){
                        e.printStackTrace();
                }
                return allIds;
        }

        /** 
         * To get all distinct ordered text (i.e. char/varchar column) ids from a certain key and table from this MySQLConnection
         * @param key the key name
         * @param table the table name
         * @return int array containing all ids 
         */
        public String[] getAllTxtIds4KeyAndTable(String key, String table){
                String[] allIds=null;           
                try {
                        Statement S=conn.createStatement();
                        String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";";
                        ResultSet R=S.executeQuery(query);
                        ArrayList<String> idsAL=new ArrayList<String>();
                        while (R.next()){
                                idsAL.add(R.getString(1));
                        }
                        allIds=new String[idsAL.size()];
                        for (int i=0;i<idsAL.size();i++) {
                                allIds[i]=idsAL.get(i);
                        }
                        R.close();
                        S.close();
                }
                catch (SQLException e){
                        e.printStackTrace();
                }
                return allIds;
        }

    /**
     * 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:	93
Committed:	Wed May 24 15:44:25 2006 UTC (18 years, 9 months ago) by duarte
File size:	20127 byte(s)
Log Message:	Now split of data also working with text-based keys as well as numerical MySQLConnection: - method getAllIds4KeyAndTable now splitted into two methods one for numerical ids and another for text ids - new methods getColumnType and isKeyNumerical DataDistribution: - method getIdSetsFromNodes splitted into two one for numerical ids one for text ids DataDistributer: - new methods: splitIdsIntoSets now splitted into two methods one numerical, one text - change methods: splitTableToCluster, splitTable, insertIdsToKeyMaster, removePK, addPK, createNewKeyMasterTbl, removeZeros, loadSplitData, dumpSplitData to make them work for both text and numeric keys. Introduced generic type T in some of them - some bugs corrected: -- an important one in createNewKeyMasterTbl, was introducing record in dbs_keys with srcDb instead of destDb as it should have been -- some bugs in loadSplitData and dumpSplitData to account for cases in which there are less ids than number of nodes and thus some nodes don't get any data. Wasn't counting with this before.
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	93	* To get the column type for a certain column and table
487			* @param table
488			* @param column
489			* @return
490			*/
491			public String getColumnType(String table,String column){
492			String query = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS " +
493			"WHERE TABLE_SCHEMA='"+this.dbname+"' AND TABLE_NAME='"+table+"' AND COLUMN_NAME='"+column+"';";
494			String colType = this.getStringFromDb(query);
495			return colType;
496			}
497
498			/**
499			* To findout whether a key (i.e. column) is numeric-based or text-based
500			* @param table
501			* @param key
502			* @return true if is numeric-based, false if is text-based
503			*/
504			public boolean isKeyNumeric(String table, String key){
505			boolean isNumeric = false;
506			String colType = getColumnType(table,key);
507			if (colType.contains("int") \|\| colType.contains("INT")){
508			isNumeric = true;
509			}
510			else if (colType.contains("char") \|\| colType.contains("CHAR")){
511			isNumeric = false;
512			}
513			else {
514			System.err.println("The key '"+key+"' from table '"+table+"' is neither numeric-based (int) nor text-based (char/varchar). Check what's wrong!");
515			}
516			return isNumeric;
517			}
518
519			/**
520	duarte	83	* To get all tables for this MySQLConnection's database.
521			* @return an array of String with all table names
522			*/
523			public String[] getTables4Db(){
524			String[] tables=null;
525			ArrayList<String> tablesAL=new ArrayList<String>();
526			String query="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='"+dbname+"' ORDER BY TABLE_NAME DESC;";
527			try {
528			Statement S = this.conn.createStatement();
529			ResultSet R=S.executeQuery(query);
530			while (R.next()){
531			tablesAL.add(R.getString(1));
532			}
533			S.close();
534			R.close();
535			tables=new String[tablesAL.size()];
536			for (int i=0;i<tablesAL.size();i++) {
537			tables[i]=tablesAL.get(i);
538			}
539			}
540			catch(SQLException e){
541			System.err.println("Couldn't get table names from "+host+" for db="+dbname);
542			System.err.println("SQLException: " + e.getMessage());
543			System.err.println("SQLState: " + e.getSQLState());
544			System.err.println("VendorError: " + e.getErrorCode());
545			e.printStackTrace();
546			}
547			return tables;
548			}
549	duarte	84
550			/**
551			* To get all distinct ordered ids from a certain key and table from this MySQLConnection
552			* @param key the key name
553			* @param table the table name
554			* @return int array containing all ids
555			*/
556	duarte	93	public Integer[] getAllNumIds4KeyAndTable(String key, String table){
557			Integer[] allIds=null;
558	duarte	84	try {
559			Statement S=conn.createStatement();
560			String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";";
561			ResultSet R=S.executeQuery(query);
562			ArrayList<Integer> idsAL=new ArrayList<Integer>();
563			while (R.next()){
564			idsAL.add(R.getInt(1));
565			}
566	duarte	93	allIds=new Integer[idsAL.size()];
567	duarte	84	for (int i=0;i<idsAL.size();i++) {
568			allIds[i]=idsAL.get(i);
569			}
570			R.close();
571			S.close();
572			}
573			catch (SQLException e){
574			e.printStackTrace();
575			}
576			return allIds;
577			}
578	duarte	83
579	duarte	93	/**
580			* To get all distinct ordered text (i.e. char/varchar column) ids from a certain key and table from this MySQLConnection
581			* @param key the key name
582			* @param table the table name
583			* @return int array containing all ids
584			*/
585			public String[] getAllTxtIds4KeyAndTable(String key, String table){
586			String[] allIds=null;
587			try {
588			Statement S=conn.createStatement();
589			String query="SELECT DISTINCT "+key+" FROM "+table+" ORDER BY "+key+";";
590			ResultSet R=S.executeQuery(query);
591			ArrayList<String> idsAL=new ArrayList<String>();
592			while (R.next()){
593			idsAL.add(R.getString(1));
594			}
595			allIds=new String[idsAL.size()];
596			for (int i=0;i<idsAL.size();i++) {
597			allIds[i]=idsAL.get(i);
598			}
599			R.close();
600			S.close();
601			}
602			catch (SQLException e){
603			e.printStackTrace();
604			}
605			return allIds;
606			}
607	duarte	84
608	duarte	83	/**
609	duarte	61	* To set the sql_mode of this connection.
610			* @param sqlmode either NO_UNSIGNED_SUBTRACTION or blank
611			*/
612			public void setSqlMode(String sqlmode) {
613			String query="SET SESSION sql_mode='"+sqlmode+"';";
614			try {
615			this.executeSql(query);
616			}
617			catch (SQLException e){
618			System.err.println("Couldn't change the sql mode to "+sqlmode);
619			System.err.println("SQLException: " + e.getMessage());
620			System.err.println("SQLState: " + e.getSQLState());
621			System.err.println("VendorError: " + e.getErrorCode());
622			e.printStackTrace();
623			}
624			}
625
626	duarte	55	}