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.

 

JDBC Lab -1 Test Database Connection & Create Table using derby db

http://db.apache.org/derby/derby_downloads.html:  db-derby-10.13.1.1-bin

http://db.apache.org/derby/releases/release-10.13.1.1.html :  db-derby-10.13.1.1-bin.zip

Download:    db-derby-10.13.1.1-bin.zip

Extra folder -> goto lib folder -> check "derby.jar" & " derbyclient.jar" files.

Steps: Configure derby db 

1. Open Eclipse IDE "Java EE IDE"

2. Create new Java Project  eg: "JDBC Project"

3. Right Click "Build Path" 

4. Select "Configure Build Path"

5. goto "Libraries"  tab in "Build path"

6. Add External JAR

7. Choose to folder "derby.jar"

8. Apply

// make file src/ TestConnection .java

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

public class TestConnection {

    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn = DriverManager.getConnection("jdbc:derby:D:\\Notes\\TU\\bca 6 th sem\\Java Advance Programming\\JDBCinteliJ\\db\\testdb;create=true");
        System.out.println("Connected to Derby Database!");


        Statement stmt = conn.createStatement();
        //Creating a table in Derby database


      String query;
         query = "CREATE TABLE Greetings (Message CHAR(20))";
         stmt.execute(query);

          query = "INSERT INTO Greetings VALUES('Hello, World')";
          stmt.execute(query);
        // stmt.close();
         System.out.println("Table created");


        ResultSet rs = stmt.executeQuery("Select * from Greetings");
        System.out.println("Contents of the table Greetings table:");
        while(rs.next()) {
            System.out.print(rs.getString("Message"));
            System.out.println();
        }



        conn.close();
    }

}

URL

 ImageDisplay:

https://www.javatpoint.com/Displaying-image-in-swing

http://zetcode.com/java/displayimage/

https://www3.ntu.edu.sg/home/ehchua/programming/java/J4a_GUI_2.html