Run PostgreSQL on Java

Try running PostgreSQL in Java.

environment

• macOS Mojave
• Eclipse 4.7.3
• PostgreSQL 11.4
• JDBC Driver 42.2.5（postgresql-42.2.5.jar）

Introducing JDBC Driver

A JDBC driver is required as a standard Java API to access PostgreSQL's RDB.

First of all, from the download.

https://jdbc.postgresql.org/download.html#current

Here we use ** postgresql-42.2.5.jar **.

Place it anywhere under this project (is it easy to understand inside lib /?)

However, as it is, it is not possible to know where it is referring, so it is necessary to set the path.

on mac

Click postgresql-42.2.5.jar ** Project tab **-> ** Properties **-> ** Java build path **-> ** Library **-> * * Add external JAR **-> Find postgresql-42.2.5.jar and open-> ** Apply and close **

OK if postgresql-42.2.5.jar is referenced in the reference library in

DB to use

This time, we will reuse all the previously used ones.

The users are as follows

terminal

customer=# \l
                                List of databases
   Name    |    Owner    | Encoding | Collate | Ctype |   Access privileges   
-----------+-------------+----------+---------+-------+-----------------------
 customer  | sf213471118 | UTF8     | C       | C     | 

The table uses the ** customer ** table

terminal

customer=# \d
          List of relations
 Schema |   Name   | Type  |    Owner   
--------+----------+-------+-------------
 public | customer | table | sf213471118

The contents of ** customer table ** are as follows

terminal

customer=# SELECT * FROM customer;
       id        |   name   | gender | age 
-----------------+----------+--------+-----
 10001           |Taro Tanaka| m       |  31
 10002           |Hanako Nakamura| w       |  44
 10003           |Ichiro Sato| m       |  20
 10004           |Aya Uchida| w       |  18
 10005           |Jiro Takahashi| m       |  56
 10006           |Satoru Nakazato| m       |  34
(6 rows)

An important part of writing Java

I will leave a place that I want to remember when writing.

Exception handling

.java

   public static void main(String[] args) throws Exception {
      try {
          System.out.println("DB access start");
          PostgreSQL_test.dbConnect();
      } catch (Exception e) {
    	  System.out.println("error main()");
    	  e.printStackTrace();
      } finally {
    	  System.out.println("DB access finished");
      }
   }

Exception handling is described by try-catch (-finally).

In this example, since ʻException e` is set, only the location of the error can be specified, but it is also possible to instantly grasp the error content by changing the catch condition and writing in parallel.

For example

.java

try {
    file_reader = new FileReader(customer);
    ~
  } catch (FileNotFoundException f) {          //File search error
    System.out.println("cannot find file");
    f.printStackTrace();
  } catch (IOException i) {                    //File I / O error
    System.out.println("in/out error");
    i.printStackTrace();
  } finally {
    try {
      if(file_reader != null) {
        file_reader.close();
      }
    } catch (IOException i) {                  //Error closing file
      System.out.println(i);
      i.printStackTrace();
    }
  }

If there is a flow

① try --catch --catch

• When you do new FileReader (customer) but cannot find the file name
• ** customer ** When there is a file but it cannot be output

It is branched in consideration of the two.

② finally --try --catch

• Error during close

Only detected.

There are many exception handling other than FileNotFoundException and ʻIOException, but you can catch all the errors by using ʻException (though I can only think of specifying the location as shown in the program two above. )

For DB connection

JDBC initialization

You need to initialize the JDBC driver before connecting to the DB.

In case of PostgreSQL, write as follows

.java

Class.forName("org.postgresql.Driver").newInstance();

DB connection establishment / DB disconnection

In addition, it is necessary to use the ** getConnection ** method that the ** DriverManager ** class has as standard to establish the DB connection.

.java

connection = DriverManager.getConnection( <URL> , <USER>, <PASSWORD>);

The URL is written as jdbc: postgresql: // <location>: <port number> / <DB name>.

This time, I set it below.

Regarding disconnection, make sure to use ** finally ** just in case.

.java

rs.close();    //rs is a variable in the ResultSet interface

SQL statement description

When sending SQL to DB, you must first create a statement using the ** createStatement ** method defined in the ** Connection ** interface (must be written as a set when sending SQL).

.java

statement = con.createStatement();

When the above is over, write a query for the SQL statement

.java

resultset = stm.executeQuery(" <SQL statement> ");

Data output

You can process line by line with the ** next () ** method of the ** ResultSet ** interface.

.java

while (rs.next()) {
　　　System.out.println(rs.getInt("<column>"));
     System.out.println(rs.getString("<column>"));
}

Java description this time

PostgreSQL_test.java

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

public class PostgreSQL_test {

   public static void main(String[] args) throws Exception {
      try {
          System.out.println("DB access start");
          PostgreSQL_test.dbConnect();
      } catch (Exception e) {
    	  System.out.println("error main()");
    	  e.printStackTrace();
      } finally {
    	  System.out.println("DB access finished");
      }
   }

   private static void dbConnect() throws Exception {
      Connection con = null;
      Statement stm = null;
      ResultSet rs = null;
      int num = 1;

      String url		= "jdbc:postgresql://localhost:5432/customer";
      String user		= "sf213471118";
      String password	= "";          //No password has been set this time

      try {
         Class.forName("org.postgresql.Driver").newInstance();
         con = DriverManager.getConnection(url, user, password);
         stm = con.createStatement();
         rs = stm.executeQuery("SELECT * FROM customer");
         while (rs.next()) {
        	 System.out.println("[" + num + "Item]");
        	 num++;
        	 System.out.println("id     : " + rs.getString("id"));
        	 System.out.println("name   : " + rs.getString("name"));
        	 System.out.println("gender : " + rs.getString("gender"));
        	 System.out.println("age    : " + rs.getInt("age"));
         }
      } catch(SQLException e) {
    	  System.out.println("error dbConnect()");
    	  e.printStackTrace();
      	}finally {
      		try {
      			if ((rs != null)||(stm != null)||(con != null)) {
      				rs.close();
      			}
      		} catch (Exception e) {
      	         e.printStackTrace();
            }
      	}
      }
}

If you move this, the following will be returned.

JavaConsole

DB access start
[1st case]
id     : 10001         
name   :Taro Tanaka
gender : m  
age    : 31
[2nd]
id     : 10002          
name   :Hanako Nakamura
gender : w  
age    : 44
[3rd case]
id     : 10003          
name   :Ichiro Sato
gender : m  
age    : 20
[4th]
id     : 10004          
name   :Aya Uchida
gender : w  
age    : 18
[5th]
id     : 10005          
name   :Jiro Takahashi
gender : m  
age    : 56
[6th]
id     : 10006          
name   :Satoru Nakazato
gender : m  
age    : 34
DB access finished

If it was true, I wanted to output the current number of names with rs.next (), but I didn't understand well, so I output it with a sigh. .. ..

~~ I would be very happy if anyone could teach me how to do it ~~

[Addition] 2019/7/29

Thank you for your comment. I fixed it as follows.

(1) Receive the number of trials of the loop part with rs.getRow ()

PostgreSQL_test.java

    while (rs.next()) {
      	 System.out.println("[" + rs.getRow() + "Item]");        /*Correction part*/
       	 System.out.println("id     : " + rs.getString("id"));
       	 System.out.println("name   : " + rs.getString("name"));
       	 System.out.println("gender : " + rs.getString("gender"));
       	 System.out.println("age    : " + rs.getInt("age"));
    }

②Fixed close omission

PostgreSQL_test.java

     try {
         if ((rs != null)||(stm != null)||(con != null)) {
             rs.close();
             stm.close();      /*Correction part*/
             con.close();      /*Correction part*/
          }
      } catch (Exception e) {
                 e.printStackTrace();
        }

Thank you again.