cjEeKGbx

1import java.sql.*;
2import java.util.Scanner;
3import java.io.*;
4
5public class Database {
6
7    public static Connection connectToDatabase(String user, String port, String database) {
8        System.out.println("-------- PostgreSQL " + "JDBC Connection Testing ------------");
9        try {
10            Class.forName("org.postgresql.Driver");
11        } catch (ClassNotFoundException e) {
12
13            System.out.println("Where is your PostgreSQL JDBC Driver? " + "Include in your library path!");
14            e.printStackTrace();
15        }
16        System.out.println("PostgreSQL JDBC Driver Registered!");
17
18        Connection connection = null;
19        try {
20            connection = DriverManager.getConnection("jdbc:postgresql://localhost:" + port + "/" + database, user,
21                    "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, String tableDescription) {
62        Statement st = null;
63        try {
64            st = connection.createStatement();
65            st.execute("CREATE TABLE " + tableDescription);
66            st.close();
67        } catch (SQLException e) {
68            e.printStackTrace();
69        }
70    }
71
72    public static int insertIntoTableFromFile(Connection connection, String table, String file) {
73
74        BufferedReader br = null;
75        int numRows = 0;
76        try {
77            Statement st = connection.createStatement();
78            String sCurrentLine, brokenLine[], composedLine = "";
79            br = new BufferedReader(new FileReader(file));
80
81            while ((sCurrentLine = br.readLine()) != null) {
82                // Insert each line to the DB
83                brokenLine = sCurrentLine.split("t");
84                composedLine = "INSERT INTO dotcom VALUES (";
85                int i;
86                for (i = 0; i < brokenLine.length - 1; i++) {
87                    composedLine += "'" + brokenLine[i] + "',";
88                }
89                composedLine += "'" + brokenLine[i] + "')";
90                numRows = st.executeUpdate(composedLine);
91            }
92        } catch (IOException e) {
93            e.printStackTrace();
94        } catch (SQLException e) {
95            e.printStackTrace();
96        } finally {
97            try {
98                if (br != null)
99                    br.close();
100            } catch (IOException ex) {
101                ex.printStackTrace();
102            }
103        }
104        return numRows;
105    }
106
107    public static void main(String[] argv) throws SQLException, FileNotFoundException {
108
109        @SuppressWarnings("resource")
110        Scanner input = new Scanner(System.in);
111        System.out.println("Please enter your Username:");
112        String user = input.next();
113        System.out.println("Please enter your Port ID:");
114        String port = input.next();
115        String database = "test";
116
117        Connection connection = connectToDatabase(user, port, database);
118
119        Statement st = connection.createStatement();
120
121        if (connection != null) {
122            System.out.println("You made it, take control your database now!");
123        } else {
124            System.out.println("Failed to make connection!");
125            return;
126        }
127        // Now we're ready to work on the DB
128
129        // read TopURLs file
130        try {
131            BufferedReader fileReader = new BufferedReader(new FileReader("TopURLs"));
132
133            while (fileReader.readLine() != null) {
134
135                st.execute("DROP TABLE IF EXISTS dotcom;");
136                st.execute("CREATE TABLE dotcom (rank integer PRIMARY KEY, domainName varchar(128), domainType varchar(128));");
137                st.execute("INSERT INTO dotcom VALUES(1, 'google', 'com');");
138                //st.execute("INSERT INTO dotcom VALUES(2, 'facebook', 'com');");
139            }
140        } catch (IOException e) {
141            e.printStackTrace();
142        } finally {
143        }
144
145        /*
146        try {
147            BufferedReader fileReader = new BufferedReader(new FileReader("TopURLs"));
148
149            while (fileReader.readLine() != null) {
150
151                st.execute("DROP TABLE IF EXISTS dotcom;");
152                st.execute("CREATE TABLE dotcom (rank integer PRIMARY KEY, domainName varchar(128), domainType varchar(128));");
153                st.execute("INSERT INTO dotcom SELECT com, domainType FROM dotcom WHERE domainType = 'com'");
154            }
155
156        } catch (IOException e) {
157            e.printStackTrace();
158        } finally {
159        }
160        */
161
162        /*
163        Statement createStatement = null;
164        PreparedStatement insertStatement = null;
165        try {
166            BufferedReader fileReader = new BufferedReader(new FileReader("TopURLs"));
167            String line = null;
168            createStatement = connection.createStatement();
169            createStatement.executeUpdate("DROP TABLE IF EXISTS dotcom");
170            createStatement.executeUpdate("CREATE TABLE dotcom (rank integer PRIMARY KEY, domainName varchar(128), domainType varchar(128))");
171
172            connection.setAutoCommit(false);//commit whole batch at the end
173            insertStatement = connection.prepareStatement("INSERT INTO dotcom VALUES (?, ?, ?)");
174
175            while ( (line = fileReader.readLine()) != null) {
176                line = fileReader.readLine();
177                String[] urls = line.split("t");//space or any other delimiter that you're using
178                insertStatement.setInt(1, Integer.parseInt(urls[0]));
179                insertStatement.setString(2, urls[1]);
180                insertStatement.setString(3, urls[2]);
181                //insertStatement.setString(4, urls[3]);
182
183                insertStatement.addBatch();
184            }
185            insertStatement.executeBatch();
186            connection.commit();
187        } catch (IOException e) {
188            e.printStackTrace();
189        } finally {
190            if(connection != null) {
191                connection.setAutoCommit(true);
192            }
193            if(createStatement != null) {
194                createStatement.close();
195            }
196            if(insertStatement != null) {
197                insertStatement.close();
198            }
199        }
200        */
201
202        // connection is of type Connection (in JDBC)
203        DatabaseMetaData dbm = connection.getMetaData();
204
205        // check if table is there
206        ResultSet tables = dbm.getTables(null, null, "table name", null);
207        if (tables.next()) {
208            System.out.println("Table exists");
209        } else {
210            System.out.println("Table does not exist");
211        }
212
213        // check if view is there?
214        //"create view foo as select * from table;"
215        //"select * from foo;"
216        ResultSet views = dbm.getTables("catalog name", null, null, null);
217        if (views.next()) {
218            System.out.println("View exists");
219        } else {
220            System.out.println("View does not exist");
221        }
222
223        String query = "SELECT * FROM internet";
224        ResultSet rs = executeSelect(connection, query);
225        try {
226            while (rs.next()) {
227                System.out.print("Column 1 returned ");
228                System.out.println(rs.getString(1));
229            }
230        } catch (SQLException e) {
231            e.printStackTrace();
232        }
233        rs.close();
234
235        dropTable(connection, "dotcom");
236        createTable(connection,
237                "dotcom (id int primary key, name varchar(15), type varchar(15));");
238        int rows = insertIntoTableFromFile(connection, "dotcom", "TopURLs");
239        System.out.println(rows + " rows inserted.");
240    }
241}