Saturday, January 25, 2014

PostgresSQL Connection With Java

package exercise.qa.selenium;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet; 
import java.sql.Statement;

public class PostgreSQLJDBC {
    static Connection con = null;
    static Statement stmt = null;
    static ResultSet rs = null;

    /*
     * Connecting To Database
     */
    public static void main(String[] arg) {

        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();
            return;

        }

        System.out.println("PostgreSQL JDBC Driver Registered!");

        try {

            con = DriverManager.getConnection(
                    "jdbc:postgresql://127.0.0.1:5432/testdb", "postgres",
                    "password");

        } catch (SQLException e) {
             e.printStackTrace();
             System.err.println(e.getClass().getName()+": "+e.getMessage());
             System.exit(0);

        }

        if (con != null) {
            System.out.println("You made it, take control your database now!");
        } else {
            System.out.println("Failed to make connection!");
        }
    }
   
    /*
     * Create a Table
     */
    public static void createTable(){
        try {
         stmt = con.createStatement();
         String sql = "CREATE TABLE COMPANY " +
                      "(ID INT PRIMARY KEY     NOT NULL," +
                      " NAME           TEXT    NOT NULL, " +
                      " AGE            INT     NOT NULL, " +
                      " ADDRESS        CHAR(50), " +
                      " SALARY         REAL)";
         stmt.executeUpdate(sql);
        } catch (SQLException e ) {
             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
             System.exit(0);
           }finally {
        try{
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }       
        }catch (SQLException e ) {
             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
             System.exit(0);
           }
           }
    }

   
    /*
     * INSERT Operation
     */
   
    public static void insertRecords(){
        try {
        con.setAutoCommit(false);
        stmt = con.createStatement();
        String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
              + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
        stmt.executeUpdate(sql);
        con.commit();
        }catch (Exception e) {
             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
             System.exit(0);
          }finally {
              try{
                if (stmt != null) {
                    stmt.close();
                }
                if (con != null) {
                    con.close();
                }       
            }catch (SQLException e ) {
                 System.err.println( e.getClass().getName()+": "+ e.getMessage() );
                 System.exit(0);
               }
          }
    }
   
    /*
     * SELECT Operation
     */
   
    public static void selectRecords(){
        try {
            con.setAutoCommit(false);
             stmt = con.createStatement();
             rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
             while ( rs.next() ) {
                int id = rs.getInt("id");
                String  name = rs.getString("name");
                int age  = rs.getInt("age");
                String  address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println( "ID = " + id );
                System.out.println( "NAME = " + name );
                System.out.println( "AGE = " + age );
                System.out.println( "ADDRESS = " + address );
                System.out.println( "SALARY = " + salary );
                System.out.println();
             }
           } catch ( Exception e ) {
             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
             System.exit(0);
           }finally {
                  try{
                       if (rs != null) {
                          rs.close();
                        }
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (con != null) {
                        con.close();
                    }       
                }catch (SQLException e ) {
                     System.err.println( e.getClass().getName()+": "+ e.getMessage() );
                     System.exit(0);
                   }
              }
    }
   
    /*
     * UPDATE Operation
     */
   
    public static void updateRecords(){
        try {
            con.setAutoCommit(false);
             stmt = con.createStatement();
             String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
             stmt.executeUpdate(sql);
             con.commit();
             rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
             while ( rs.next() ) {
                int id = rs.getInt("id");
                String  name = rs.getString("name");
                int age  = rs.getInt("age");
                String  address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println( "ID = " + id );
                System.out.println( "NAME = " + name );
                System.out.println( "AGE = " + age );
                System.out.println( "ADDRESS = " + address );
                System.out.println( "SALARY = " + salary );
                System.out.println();
             }
           } catch ( Exception e ) {
             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
             System.exit(0);
           }finally {
                  try{
                       if (rs != null) {
                          rs.close();
                        }
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (con != null) {
                        con.close();
                    }       
                }catch (SQLException e ) {
                     System.err.println( e.getClass().getName()+": "+ e.getMessage() );
                     System.exit(0);
                   }
              }
    }
   
    /*
     * DELETE Operation
     */
   
    public static void deleteRecords(){
        try {
            con.setAutoCommit(false);
             stmt = con.createStatement();
             String sql = "DELETE from COMPANY where ID=2;";
             stmt.executeUpdate(sql);
             con.commit();
             rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
             while ( rs.next() ) {
                int id = rs.getInt("id");
                String  name = rs.getString("name");
                int age  = rs.getInt("age");
                String  address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println( "ID = " + id );
                System.out.println( "NAME = " + name );
                System.out.println( "AGE = " + age );
                System.out.println( "ADDRESS = " + address );
                System.out.println( "SALARY = " + salary );
                System.out.println();
             }
           } catch ( Exception e ) {
             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
             System.exit(0);
           }finally {
                  try{
                       if (rs != null) {
                          rs.close();
                        }
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (con != null) {
                        con.close();
                    }       
                }catch (SQLException e ) {
                     System.err.println( e.getClass().getName()+": "+ e.getMessage() );
                     System.exit(0);
                   }
              }
    }
   
}//End of the Class

   

No comments:

Post a Comment