aglappe-0.6/tools/utils4DB.java

package tools; 

import java.sql.*;
/**
 * Package:             tools
 * Class:               utils4DB
 * Author:              Ioannis Filippis, filippis@molgen.mpg.de
 * Date:                23/01/2006
 * 
 * utils4DB contains static methods to facilitate information retrieval from database.
 * For example execute an update Query, get the range of a numeric field from a table,
 * get information for the size of a database
 * 
 * Changelog:
 * 04/04/06 modified by IF - execUpdateQuery method added and code in comments was removed
 * 21/03/06 modified by IF - getRange methods return double array instead of float 
 * 23/01/06 first created by IF
 */
public class utils4DB {

    private utils4DB() {};
    
    public static void execUpdateQuery(Connection C, String query) {

        Statement S;

        try {
            S = C.createStatement();
            S.executeUpdate(query);
            S.close();
        } // end try
        catch (SQLException E) {
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
        } // end catch
                
    }
    
    public static double[] getRange(Connection C, 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 = C.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.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
        } // end catch
        
        return range;
        
    }

    public static double[] getRange(Connection C, String table, String column, String selection) {

        String query = "";
        Statement S;
        ResultSet R;

        double[] range = new double[2];

        try { 
            query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+selection+");";
            S = C.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.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
        } // end catch
        
        return range;
        
    }
    
    public static void getDbSizeInfo(String connFile, String DB) {

                Connection myConnection;
                mySQLConnect SQLC;
        
                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;
        
                SQLC = new mySQLConnect();
                SQLC.readConnectionFile(connFile);
                myConnection = SQLC.openConnection();
        
                try {
                    Query = "SHOW TABLE STATUS FROM "+DB;
                    Stmt = myConnection.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 "+DB+" needs "+((data+index)/GB)+ " GB (data:"+(data/GB)+", index:"+(index/GB)+").");
                } 
                catch (SQLException E) {
                    System.out.println("SQLException: " + E.getMessage());
                    System.out.println("SQLState:     " + E.getSQLState());
                    System.out.println("VendorError:  " + E.getErrorCode());
                } // end try/catch connection
        
                SQLC.closeConnection(myConnection);

    }
    

}
Revision:	197
Committed:	Thu Jun 14 14:44:44 2007 UTC (17 years, 4 months ago) by duarte
File size:	4097 byte(s)
Log Message:	Tagging version 0.6 for release of cmview 0.6
Line	File contents
1	package tools;
2
3	import java.sql.*;
4	/**
5	* Package: tools
6	* Class: utils4DB
7	* Author: Ioannis Filippis, filippis@molgen.mpg.de
8	* Date: 23/01/2006
9	*
10	* utils4DB contains static methods to facilitate information retrieval from database.
11	* For example execute an update Query, get the range of a numeric field from a table,
12	* get information for the size of a database
13	*
14	* Changelog:
15	* 04/04/06 modified by IF - execUpdateQuery method added and code in comments was removed
16	* 21/03/06 modified by IF - getRange methods return double array instead of float
17	* 23/01/06 first created by IF
18	*/
19	public class utils4DB {
20
21	private utils4DB() {};
22
23	public static void execUpdateQuery(Connection C, String query) {
24
25	Statement S;
26
27	try {
28	S = C.createStatement();
29	S.executeUpdate(query);
30	S.close();
31	} // end try
32	catch (SQLException E) {
33	System.out.println("SQLException: " + E.getMessage());
34	System.out.println("SQLState: " + E.getSQLState());
35	System.out.println("VendorError: " + E.getErrorCode());
36	} // end catch
37
38	}
39
40	public static double[] getRange(Connection C, String table, String column) {
41
42	String query = "";
43	Statement S;
44	ResultSet R;
45
46	double[] range = new double[2];
47
48	try {
49	query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";";
50	S = C.createStatement();
51	R = S.executeQuery(query);
52
53	if (R.next()) {
54	range[0] = R.getDouble(1);
55	range[1] = R.getDouble(2);
56	}
57
58	R.close();
59	S.close();
60
61	} // end try
62	catch (SQLException E) {
63	System.out.println("SQLException: " + E.getMessage());
64	System.out.println("SQLState: " + E.getSQLState());
65	System.out.println("VendorError: " + E.getErrorCode());
66	} // end catch
67
68	return range;
69
70	}
71
72	public static double[] getRange(Connection C, String table, String column, String selection) {
73
74	String query = "";
75	Statement S;
76	ResultSet R;
77
78	double[] range = new double[2];
79
80	try {
81	query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+selection+");";
82	S = C.createStatement();
83	R = S.executeQuery(query);
84
85	if (R.next()) {
86	range[0] = R.getDouble(1);
87	range[1] = R.getDouble(2);
88	}
89
90	R.close();
91	S.close();
92
93	} // end try
94	catch (SQLException E) {
95	System.out.println("SQLException: " + E.getMessage());
96	System.out.println("SQLState: " + E.getSQLState());
97	System.out.println("VendorError: " + E.getErrorCode());
98	} // end catch
99
100	return range;
101
102	}
103
104	public static void getDbSizeInfo(String connFile, String DB) {
105
106	Connection myConnection;
107	mySQLConnect SQLC;
108
109	double data = 0, index = 0, table_data = 0, table_index = 0, GB = Math.pow(2, 30);
110	String Query = null, table = null;
111	Statement Stmt = null;
112	ResultSet RS = null;
113
114	SQLC = new mySQLConnect();
115	SQLC.readConnectionFile(connFile);
116	myConnection = SQLC.openConnection();
117
118	try {
119	Query = "SHOW TABLE STATUS FROM "+DB;
120	Stmt = myConnection.createStatement();
121	RS = Stmt.executeQuery(Query);
122	while (RS.next()) {
123	table = RS.getString("Name");
124	table_data = RS.getDouble("Data_length");
125	table_index = RS.getDouble("Index_length");
126	data += RS.getDouble("Data_length");
127	index += RS.getDouble("Index_length");
128
129	System.out.println("Table "+table+"##data:"+table_data+", index:"+table_index);
130	}
131	RS.close();
132	Stmt.close();
133
134	System.out.println("Database "+DB+" needs "+((data+index)/GB)+ " GB (data:"+(data/GB)+", index:"+(index/GB)+").");
135	}
136	catch (SQLException E) {
137	System.out.println("SQLException: " + E.getMessage());
138	System.out.println("SQLState: " + E.getSQLState());
139	System.out.println("VendorError: " + E.getErrorCode());
140	} // end try/catch connection
141
142	SQLC.closeConnection(myConnection);
143
144	}
145
146
147	}