trunk/tools/utils4DB.java

package tools; 

import java.sql.*;
import java.io.*;
import java.util.*;
import java.lang.Math.*;

public class utils4DB {

    private utils4DB() {};

    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:	7
Committed:	Mon Jan 23 09:19:47 2006 UTC (18 years, 8 months ago) by duarte
File size:	2858 byte(s)
Log Message:	Moved everything to subdir tools, so that we keep the tools name for the package. Also changed the package name in the files to tools. To work with it please check out only file:///.../svn/tools/trunk/tools
Line	User	Rev	File contents
1	duarte	7	package tools;
2
3			import java.sql.*;
4			import java.io.*;
5			import java.util.*;
6			import java.lang.Math.*;
7
8			public class utils4DB {
9
10			private utils4DB() {};
11
12			public static float[] getRange(Connection C, String table, String column) {
13
14			String query = "";
15			Statement S;
16			ResultSet R;
17
18			float[] range = new float[2];
19
20			try {
21			query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+";";
22			S = C.createStatement();
23			R = S.executeQuery(query);
24
25			if (R.next()) {
26			range[0] = R.getFloat(1);
27			range[1] = R.getFloat(2);
28			}
29
30			R.close();
31			S.close();
32
33			} // end try
34			catch (SQLException E) {
35			System.out.println("SQLException: " + E.getMessage());
36			System.out.println("SQLState: " + E.getSQLState());
37			System.out.println("VendorError: " + E.getErrorCode());
38			} // end catch
39
40			return range;
41
42			}
43
44			public static float[] getRange(Connection C, String table, String column, String selection) {
45
46			String query = "";
47			Statement S;
48			ResultSet R;
49
50			float[] range = new float[2];
51
52			try {
53			query = "SELECT MIN("+column+"), MAX("+column+") FROM "+table+" WHERE ("+selection+");";
54			S = C.createStatement();
55			R = S.executeQuery(query);
56
57			if (R.next()) {
58			range[0] = R.getFloat(1);
59			range[1] = R.getFloat(2);
60			}
61
62			R.close();
63			S.close();
64
65			} // end try
66			catch (SQLException E) {
67			System.out.println("SQLException: " + E.getMessage());
68			System.out.println("SQLState: " + E.getSQLState());
69			System.out.println("VendorError: " + E.getErrorCode());
70			} // end catch
71
72			return range;
73
74			}
75
76			public static void getDbSizeInfo(String connFile, String DB) {
77
78			Connection myConnection;
79			mySQLConnect SQLC;
80
81			double data = 0, index = 0, table_data = 0, table_index = 0, GB = Math.pow(2, 30);
82			String Query = null, table = null;
83			Statement Stmt = null;
84			ResultSet RS = null;
85
86			SQLC = new mySQLConnect();
87			SQLC.readConnectionFile(connFile);
88			myConnection = SQLC.openConnection();
89
90			try {
91			Query = "SHOW TABLE STATUS FROM "+DB;
92			Stmt = myConnection.createStatement();
93			RS = Stmt.executeQuery(Query);
94			while (RS.next()) {
95			table = RS.getString("Name");
96			table_data = RS.getDouble("Data_length");
97			table_index = RS.getDouble("Index_length");
98			data += RS.getDouble("Data_length");
99			index += RS.getDouble("Index_length");
100
101			System.out.println("Table "+table+"##data:"+table_data+", index:"+table_index);
102			}
103			RS.close();
104			Stmt.close();
105
106			System.out.println("Database "+DB+" needs "+((data+index)/GB)+ " GB (data:"+(data/GB)+", index:"+(index/GB)+").");
107			}
108			catch (SQLException E) {
109			System.out.println("SQLException: " + E.getMessage());
110			System.out.println("SQLState: " + E.getSQLState());
111			System.out.println("VendorError: " + E.getErrorCode());
112			} // end try/catch connection
113
114			SQLC.closeConnection(myConnection);
115
116			}
117
118
119			}