trunk/tools/utils4DB.java

package tools; 

import java.sql.*;

public class utils4DB {

    private utils4DB() {};
    
    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 float[] getRange(Connection C, String table, String column) {

                String query = "";
                Statement S;
                ResultSet R;
        
                float[] range = new float[2];
        
                try { 
                    query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";";
                    S = C.createStatement();
                    R = S.executeQuery(query);
                
                    if (R.next()) {
                                range[0] = R.getFloat(1);
                                range[1] = R.getFloat(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 float[] getRange(Connection C, String table, String column, String selection) {

                String query = "";
                Statement S;
                ResultSet R;
        
                float[] range = new float[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.getFloat(1);
                                range[1] = R.getFloat(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:	41
Committed:	Tue Mar 21 18:41:48 2006 UTC (18 years, 6 months ago) by filippis
File size:	4549 byte(s)
Log Message:	getRange methods now return double array instead of Float array. Previous code is into comments.
Line	File contents
1	package tools;
2
3	import java.sql.*;
4
5	public class utils4DB {
6
7	private utils4DB() {};
8
9	public static double[] getRange(Connection C, String table, String column) {
10
11	String query = "";
12	Statement S;
13	ResultSet R;
14
15	double[] range = new double[2];
16
17	try {
18	query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";";
19	S = C.createStatement();
20	R = S.executeQuery(query);
21
22	if (R.next()) {
23	range[0] = R.getDouble(1);
24	range[1] = R.getDouble(2);
25	}
26
27	R.close();
28	S.close();
29
30	} // end try
31	catch (SQLException E) {
32	System.out.println("SQLException: " + E.getMessage());
33	System.out.println("SQLState: " + E.getSQLState());
34	System.out.println("VendorError: " + E.getErrorCode());
35	} // end catch
36
37	return range;
38
39	}
40
41	public static double[] getRange(Connection C, String table, String column, String selection) {
42
43	String query = "";
44	Statement S;
45	ResultSet R;
46
47	double[] range = new double[2];
48
49	try {
50	query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+selection+");";
51	S = C.createStatement();
52	R = S.executeQuery(query);
53
54	if (R.next()) {
55	range[0] = R.getDouble(1);
56	range[1] = R.getDouble(2);
57	}
58
59	R.close();
60	S.close();
61
62	} // end try
63	catch (SQLException E) {
64	System.out.println("SQLException: " + E.getMessage());
65	System.out.println("SQLState: " + E.getSQLState());
66	System.out.println("VendorError: " + E.getErrorCode());
67	} // end catch
68
69	return range;
70
71	}
72
73	/*
74	public static float[] getRange(Connection C, String table, String column) {
75
76	String query = "";
77	Statement S;
78	ResultSet R;
79
80	float[] range = new float[2];
81
82	try {
83	query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";";
84	S = C.createStatement();
85	R = S.executeQuery(query);
86
87	if (R.next()) {
88	range[0] = R.getFloat(1);
89	range[1] = R.getFloat(2);
90	}
91
92	R.close();
93	S.close();
94
95	} // end try
96	catch (SQLException E) {
97	System.out.println("SQLException: " + E.getMessage());
98	System.out.println("SQLState: " + E.getSQLState());
99	System.out.println("VendorError: " + E.getErrorCode());
100	} // end catch
101
102	return range;
103
104	}
105
106	public static float[] getRange(Connection C, String table, String column, String selection) {
107
108	String query = "";
109	Statement S;
110	ResultSet R;
111
112	float[] range = new float[2];
113
114	try {
115	query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+selection+");";
116	S = C.createStatement();
117	R = S.executeQuery(query);
118
119	if (R.next()) {
120	range[0] = R.getFloat(1);
121	range[1] = R.getFloat(2);
122	}
123
124	R.close();
125	S.close();
126
127	} // end try
128	catch (SQLException E) {
129	System.out.println("SQLException: " + E.getMessage());
130	System.out.println("SQLState: " + E.getSQLState());
131	System.out.println("VendorError: " + E.getErrorCode());
132	} // end catch
133
134	return range;
135
136	}
137	*/
138
139	public static void getDbSizeInfo(String connFile, String DB) {
140
141	Connection myConnection;
142	mySQLConnect SQLC;
143
144	double data = 0, index = 0, table_data = 0, table_index = 0, GB = Math.pow(2, 30);
145	String Query = null, table = null;
146	Statement Stmt = null;
147	ResultSet RS = null;
148
149	SQLC = new mySQLConnect();
150	SQLC.readConnectionFile(connFile);
151	myConnection = SQLC.openConnection();
152
153	try {
154	Query = "SHOW TABLE STATUS FROM "+DB;
155	Stmt = myConnection.createStatement();
156	RS = Stmt.executeQuery(Query);
157	while (RS.next()) {
158	table = RS.getString("Name");
159	table_data = RS.getDouble("Data_length");
160	table_index = RS.getDouble("Index_length");
161	data += RS.getDouble("Data_length");
162	index += RS.getDouble("Index_length");
163
164	System.out.println("Table "+table+"##data:"+table_data+", index:"+table_index);
165	}
166	RS.close();
167	Stmt.close();
168
169	System.out.println("Database "+DB+" needs "+((data+index)/GB)+ " GB (data:"+(data/GB)+", index:"+(index/GB)+").");
170	}
171	catch (SQLException E) {
172	System.out.println("SQLException: " + E.getMessage());
173	System.out.println("SQLState: " + E.getSQLState());
174	System.out.println("VendorError: " + E.getErrorCode());
175	} // end try/catch connection
176
177	SQLC.closeConnection(myConnection);
178
179	}
180
181
182	}