7p2cvw9Q

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(",");
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(5000), domainType varchar(5000));");
137                //st.execute("INSERT INTO dotcom VALUES (fileReader.nextLine(), fileReader.nextLine(), fileReader.nextLine());");
138                //st.execute("COPY dotcom FROM 'TopURLs' WITH DELIMITER as 'delimiter'");
139
140            }
141
142        } catch (IOException e) {
143            e.printStackTrace();
144        } finally {
145        }
146
147        // connection is of type Connection (in JDBC)
148        DatabaseMetaData dbm = connection.getMetaData();
149
150        // check if table is there
151        ResultSet tables = dbm.getTables(null, null, "table name", null);
152        if (tables.next()) {
153            System.out.println("Table exists");
154        } else {
155            System.out.println("Table does not exist");
156        }
157
158        // check if view is there?
159        //"create view foo as select * from table;"
160        //"select * from foo;"
161        ResultSet views = dbm.getTables("catalog name", null, null, null);
162        if (views.next()) {
163            System.out.println("View exists");
164        } else {
165            System.out.println("View does not exist");
166        }
167
168        String query = "SELECT * FROM branch";
169        ResultSet rs = executeSelect(connection, query);
170        try {
171            while (rs.next()) {
172                System.out.print("Column 1 returned ");
173                System.out.println(rs.getString(1));
174            }
175        } catch (SQLException e) {
176            e.printStackTrace();
177        }
178        rs.close();
179
180        dropTable(connection, "dotcom");
181        createTable(connection,
182                "dotcom(id int primary key, name varchar(15), street varchar(15), city varchar(15));");
183        int rows = insertIntoTableFromFile(connection, "customer", "src/Table.txt");
184        System.out.println(rows + " rows inserted.");
185    }
186}