xSruNK65

1import java.sql.*;
2import java.util.Scanner;
3import java.io.*;
4
5public class Database {
6
7    public static Connection connectToDatabase(String user, String port,
8            String database) {
9        System.out.println("-------- PostgreSQL JDBC Connection Testing ------------");
10        try {
11            Class.forName("org.postgresql.Driver");
12        } catch (ClassNotFoundException e) {
13
14            System.out.println("Where is your PostgreSQL JDBC Driver? " + "Include in your library path!");
15            e.printStackTrace();
16        }
17        System.out.println("PostgreSQL JDBC Driver Registered!");
18
19        Connection connection = null;
20        try {
21            connection = DriverManager.getConnection("jdbc:postgresql://localhost:" + port + "/" + database, user, "doesn't matter!");
22        } catch (SQLException e) {
23            System.out.println("Connection Failed! Check output console");
24            e.printStackTrace();
25        }
26        return connection;
27    }
28
29    public static ResultSet executeSelect(Connection connection, String query) {
30        Statement st = null;
31        try {
32            st = connection.createStatement();
33        } catch (SQLException e) {
34            e.printStackTrace();
35            return null;
36        }
37
38        ResultSet rs = null;
39        try {
40            rs = st.executeQuery(query);
41            //st.close();
42        } catch (SQLException e) {
43            e.printStackTrace();
44            return null;
45        }
46
47        return rs;
48    }
49
50    public static void dropTable(Connection connection, String table) {
51        Statement st = null;
52        try {
53            st = connection.createStatement();
54            st.execute("DROP TABLE " + table);
55            st.close();
56        } catch (SQLException e) {
57            e.printStackTrace();
58        }
59    }
60
61    public static void createTable(Connection connection,
62            String tableDescription) {
63        Statement st = null;
64        try {
65            st = connection.createStatement();
66            st.execute("CREATE TABLE " + tableDescription);
67            st.close();
68        } catch (SQLException e) {
69            e.printStackTrace();
70        }
71    }
72
73    public static int insertIntoTableFromFile(Connection connection,
74            String table, String file) {
75
76        BufferedReader br = null;
77        int numRows = 0;
78        try {
79            Statement st = connection.createStatement();
80            String sCurrentLine, brokenLine[], composedLine = "";
81            br = new BufferedReader(new FileReader("src/TopURLs"));
82
83            while ((sCurrentLine = br.readLine()) != null) {
84                // Insert each line to the DB
85                brokenLine = sCurrentLine.split("t");
86                composedLine = "INSERT INTO dotcom VALUES (";
87                int i;
88                for (i = 0; i < brokenLine.length - 1; i++) {
89                    composedLine += "'" + brokenLine[i] + "',";
90                }
91                composedLine += "'" + brokenLine[i] + "')";
92                numRows += st.executeUpdate(composedLine);
93                //System.out.println(composedLine);
94            }
95        } catch (IOException e) {
96            e.printStackTrace();
97        } catch (SQLException e) {
98            e.printStackTrace();
99        } finally {
100            try {
101                if (br != null)
102                    br.close();
103            } catch (IOException ex) {
104                ex.printStackTrace();
105            }
106        }
107        return numRows;
108    }
109
110    public static void print() {
111        System.out.println("########## 1st Query ##########");
112
113    }
114
115    public static void main(String[] argv) throws SQLException {
116        /*
117        Scanner input = new Scanner(System.in);
118        System.out.println("Please enter your Username:");
119        String user = input.next();
120        System.out.println("Please enter your Port ID:");
121        String port = input.next();
122        */
123
124        String user = "zbva777";
125        String port = "28046";
126        String database = "test";
127
128        Connection connection = connectToDatabase(user, port, database);
129
130        if (connection != null) {
131            System.out.println("You made it, take control your database now!");
132        } else {
133            System.out.println("Failed to make connection!");
134            return;
135        }
136        // Now we're ready to work on the DB
137
138        // connection is of type Connection (in JDBC)
139        DatabaseMetaData dbm = connection.getMetaData();
140
141        // check if table is there
142        ResultSet tables = dbm.getTables(null, null, "dotcom", null);
143        if (tables.next()) {
144            System.out.println("Table exists");
145        } else {
146            System.out.println("Table does not exist");
147        }
148
149        // check if view is there?
150        //"create view foo as select * from table;"
151        //"select * from foo;"
152        ResultSet views = dbm.getTables("catalog name", null, null, null);
153        if (views.next()) {
154            System.out.println("View exists");
155        } else {
156            System.out.println("View does not exist");
157        }
158
159        String query = "SELECT * FROM dotcom";
160        String view = "CREATE VIEW view as SELECT FROM dotcom";
161        ResultSet rs = executeSelect(connection, query);
162        try {
163            while (rs.next()) {
164                //System.out.print("Column 1 returned ");
165                //System.out.println(rs.getString(1));
166            }
167        } catch (SQLException e) {
168            e.printStackTrace();
169        }
170
171        dropTable(connection, "dotcom");
172        createTable(connection, "dotcom (rank int primary key, name varchar(128), type varchar(128), subtype varchar(128), subsubtype varchar(128));");
173        int rows = insertIntoTableFromFile(connection, "dotcom", "src/TopURLs");
174        System.out.println(rows + " rows inserted.");
175
176        //Print data from result set
177        print();
178        while(rs.next()) {
179        rs.getInt("rank");
180        String name = rs.getString("name");
181        System.out.println(name);
182        }
183
184        rs.close();
185    }
186}