Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.*;
- import java.util.Scanner;
- import java.io.*;
- public class Database {
- public static Connection connectToDatabase(String user, String port,
- String database) {
- System.out.println("-------- PostgreSQL JDBC Connection Testing ------------");
- try {
- Class.forName("org.postgresql.Driver");
- } catch (ClassNotFoundException e) {
- System.out.println("Where is your PostgreSQL JDBC Driver? " + "Include in your library path!");
- e.printStackTrace();
- }
- System.out.println("PostgreSQL JDBC Driver Registered!");
- Connection connection = null;
- try {
- connection = DriverManager.getConnection("jdbc:postgresql://localhost:" + port + "/" + database, user, "doesn't matter!");
- } catch (SQLException e) {
- System.out.println("Connection Failed! Check output console");
- e.printStackTrace();
- }
- return connection;
- }
- public static ResultSet executeSelect(Connection connection, String query) {
- Statement st = null;
- try {
- st = connection.createStatement();
- } catch (SQLException e) {
- e.printStackTrace();
- return null;
- }
- ResultSet rs = null;
- try {
- rs = st.executeQuery(query);
- //st.close();
- } catch (SQLException e) {
- e.printStackTrace();
- return null;
- }
- return rs;
- }
- public static void dropTable(Connection connection, String table) {
- Statement st = null;
- try {
- st = connection.createStatement();
- st.execute("DROP TABLE " + table);
- st.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public static void createTable(Connection connection,
- String tableDescription) {
- Statement st = null;
- try {
- st = connection.createStatement();
- st.execute("CREATE TABLE " + tableDescription);
- st.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public static int insertIntoTableFromFile(Connection connection,
- String table, String file) {
- BufferedReader br = null;
- int numRows = 0;
- try {
- Statement st = connection.createStatement();
- String sCurrentLine, brokenLine[], composedLine = "";
- br = new BufferedReader(new FileReader("src/TopURLs"));
- while ((sCurrentLine = br.readLine()) != null) {
- // Insert each line to the DB
- brokenLine = sCurrentLine.split("t");
- composedLine = "INSERT INTO dotcom VALUES (";
- int i;
- for (i = 0; i < brokenLine.length - 1; i++) {
- composedLine += "'" + brokenLine[i] + "',";
- }
- composedLine += "'" + brokenLine[i] + "')";
- numRows += st.executeUpdate(composedLine);
- //System.out.println(composedLine);
- }
- } catch (IOException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- try {
- if (br != null)
- br.close();
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- }
- return numRows;
- }
- public static void print() {
- System.out.println("########## 1st Query ##########");
- }
- public static void main(String[] argv) throws SQLException {
- /*
- Scanner input = new Scanner(System.in);
- System.out.println("Please enter your Username:");
- String user = input.next();
- System.out.println("Please enter your Port ID:");
- String port = input.next();
- */
- String user = "zbva777";
- String port = "28046";
- String database = "test";
- Connection connection = connectToDatabase(user, port, database);
- if (connection != null) {
- System.out.println("You made it, take control your database now!");
- } else {
- System.out.println("Failed to make connection!");
- return;
- }
- // Now we're ready to work on the DB
- // connection is of type Connection (in JDBC)
- DatabaseMetaData dbm = connection.getMetaData();
- // check if table is there
- ResultSet tables = dbm.getTables(null, null, "dotcom", null);
- if (tables.next()) {
- System.out.println("Table exists");
- } else {
- System.out.println("Table does not exist");
- }
- // check if view is there?
- //"create view foo as select * from table;"
- //"select * from foo;"
- ResultSet views = dbm.getTables("catalog name", null, null, null);
- if (views.next()) {
- System.out.println("View exists");
- } else {
- System.out.println("View does not exist");
- }
- String query = "SELECT * FROM dotcom";
- String view = "CREATE VIEW view as SELECT FROM dotcom";
- ResultSet rs = executeSelect(connection, query);
- try {
- while (rs.next()) {
- //System.out.print("Column 1 returned ");
- //System.out.println(rs.getString(1));
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
- dropTable(connection, "dotcom");
- createTable(connection, "dotcom (rank int primary key, name varchar(128), type varchar(128), subtype varchar(128), subsubtype varchar(128));");
- int rows = insertIntoTableFromFile(connection, "dotcom", "src/TopURLs");
- System.out.println(rows + " rows inserted.");
- //Print data from result set
- print();
- while(rs.next()) {
- rs.getInt("rank");
- String name = rs.getString("name");
- System.out.println(name);
- }
- rs.close();
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement