Lab 2: JDBC using Postgres SQL

 

import java.nio.file.*;

import java.sql.*;

import java.io.*;

import java.util.*;



/**

 * This program tests that the database and the JDBC driver are correctly configured.

 *

 */

public class TestConnection

{

    public static void main(String args[]) throws IOException

    {

        try

        {

            runTest();

        }

        catch (SQLException ex)

        {

            for (Throwable t : ex)

                t.printStackTrace();

        }

    }

    /**

     * Runs a test by creating a table, adding a value, showing the table contents, and

     removing

     * the table.

     */

    public static void runTest() throws SQLException, IOException

    {

        try (Connection conn = getConnection())

        {

            Statement stat = conn.createStatement();



            stat.executeUpdate("CREATE TABLE Greetings (Message CHAR(20))");

            stat.executeUpdate("INSERT INTO Greetings VALUES ('Hello, World!')");





stat.executeUpdate("CREATE TABLE Publishers (Publisher_Id CHAR(6), Name CHAR(30), URL CHAR(80))");

stat.executeUpdate("INSERT INTO Publishers VALUES ('0201', 'Addison-Wesley', 'www.aw-bc.com')");

stat.executeUpdate("INSERT INTO Publishers VALUES ('0471', 'John Wiley & Sons', 'www.wiley.com')");



String query;



  query = "CREATE TABLE Books( "

         + "Title CHAR(60), "

         + "ISBN CHAR(13), "

         + "Publisher_Id CHAR(6), "

         + "Price DECIMAL(10,2))";



            stat.executeUpdate(query);

  query = "CREATE TABLE BooksAuthors( "

       + "ISBN CHAR(13), "

       + "Author_Id CHAR(6), "

         + "Seq_No CHAR(6))";



            stat.executeUpdate(query);



  query = "CREATE TABLE Authors( "

       + "Author_Id CHAR(6), "

       + "Name CHAR(30), "

         + "Fname CHAR(30))";

 

            stat.executeUpdate(query);

            System.out.println("table created");



 

            stat.executeUpdate("INSERT INTO Authors VALUES ('ALEX', 'Alexander', 'Christopher')");

            stat.executeUpdate("INSERT INTO Authors VALUES ('BROO', 'Brooks', 'Frederick P.')");

            stat.executeUpdate("INSERT INTO Authors VALUES ('ADDI', 'Wesley', 'Addison')");





            stat.executeUpdate("INSERT INTO Books VALUES ('A Guide to the SQL Standard', '0-201-96426-0', '0201', '47.95')");

            stat.executeUpdate("INSERT INTO Books VALUES ('A Pattern Language: Towns, Buildings', '0-19-501919-0', '019', '65.00')");





            stat.executeUpdate("INSERT INTO BooksAuthors VALUES ('0-201-96426-0', 'DATE', '1')");

            stat.executeUpdate("INSERT INTO BooksAuthors VALUES ('0-201-96426-0', 'DARW', '2')");

            stat.executeUpdate("INSERT INTO BooksAuthors VALUES ('0-19-501919-0', 'ALEX', '1')");



            System.out.println("inserted");



 try (ResultSet result = stat.executeQuery("SELECT * FROM Publishers"))

 {

 if (result.next())

 System.out.println(result.getString("Publisher_Id"));

 }

 



 stat.executeUpdate("DROP TABLE Greetings");

        }

    }



    /**

     * Gets a connection from the properties specified in the file database.properties.

     * @return the database connection

     */

    public static Connection getConnection() throws SQLException, IOException

    {

        Properties props = new Properties();

        try (InputStream in = Files.newInputStream(Paths.get("lib/database.properties")))

        {

            props.load(in);

        }

        String drivers = props.getProperty("jdbc.drivers");

        if (drivers != null) System.setProperty("jdbc.drivers", drivers);

        String url = props.getProperty("jdbc.url");

        String username = props.getProperty("jdbc.username");

        String password = props.getProperty("jdbc.password");



        return DriverManager.getConnection(url, username, password);

    }

}

 

Create folder “lib” in Project

// lib/“database.properties” (File)

jdbc.drivers=org.postgresql.Driver

jdbc.url=jdbc:postgresql:test

jdbc.username=postgres

jdbc.password=postgres

 

Add jar file in “lib” folder

“postgresql-42.2.8.jar”

Configure of Database

Goto “File”  Menu

Goto “ Project Structure”

Goto “ Project Settings” -> Select “ Modules”

Then Select “Dependencies” Tab

Then Click on ( + ) on right side to add “ Jar or directories” Option

Apply -| Click on Ok.