How to get started with JDBC using PostgresSQL on MacOS

Introduction

I had a chance to touch JDBC, but I wanted to practice it in my personal environment, so this is the procedure when I introduced PostgreSQL. It's basically a memorandum.

Various versions, etc.

- macOS : 10.15.4
- JDK : 13.0.1
- PostgreSQL : 12.2
- JDBC Driver : 42.4.12
 --Terminal: bash

Install PostgreSQL using Homebrew

-How to install PostgreSQL on Mac & make initial settings

Install PostgreSQL by referring to the above article. In the following, user and password are described as postgresql.

Download PostgreSQL JDBC Driver

-PostgreSQL Official

Download the JDBC Driver from the above page.

JDBC Driver (jar file) installation and CLASSPATH setting

If you have a MacOS and the JDK version is 8 or less, you can install a Java external library (jar file, etc.) in / Library / Java / Extensions and the class loader will search for the JDBC class file at compile time. However, since we are using JDK13 this time, we need to set the CLASSPATH.

1. Installation of JDBC Driver

Place the downloaded postgresql-42.2.12.jar in / Library / Java / Extensions.

2. CLASSPATH setting

Set the environment variable CLASSPATH to make the class loader react at compile time. Start the terminal and execute the following command.

CLASSPATH setting and reloading

echo 'export CLASSPATH=.:/Library/Java/Extensions/postgresql-42.2.12.jar:$CLASSPATH' >> .bash_profile
source ~/.bash_profile

The first line is writing the CLASSPATH to .bash_profile The second line reloads .bash_profile We are doing each.

Connection test to PostgreSQL using JDBC

Finally, we will demonstrate creating a test table, connecting to a database, and retrieving data.

1. Create a test table

Creating SQL for test table creation

Create the following files in any location. In the following, it is assumed that it is created in ~ / Desktop.

createTestTable.sql

create table test(
    id serial primary key,
    name varchar(255),
    point real
);
insert into test(name,point) values
('foo', 41.2),
('bar',55.1),
('baz', 10.7),
('qux', 98.0),
('quux', 22.2),
('foobar', 35.6);

Start PostgreSQL and create database

Start the terminal and execute the following command.

Start PostgreSQL and create database

brew services start postgresql
createdb test
psql test

Creating a test table

When the PostgreSQL console starts, execute the following command.

Creating a test table

\i ~/Desktop/createTestTable.sql
\q

2. Connection from JDBC to DB and acquisition of data

Creating a Java file for the call

Create the following files in any location. In the following, it is assumed that it is created in ~ / Desktop.

Main.java

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

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost/test";
        String user = "postgresql";
        String password = "postgresql";
        String sql = "select * from test";

        try(Connection con = DriverManager.getConnection(url, user, password)){
            try(PreparedStatement ps = con.prepareStatement(sql)){
                ResultSet rs = ps.executeQuery();
                while (rs.next()){
                    System.out.print(rs.getString("name"));
                    System.out.print(" gets ");
                    System.out.println(rs.getInt("point"));
                }
            } catch(SQLException e){
                e.printStackTrace();
            }
        } catch(SQLException e){
            e.printStackTrace();
        }
    }
}

Compile and run Java files

Compile and run Java files

cd ~/Desktop
javac Main.java
java Main

Output result

foo gets 41.2
bar gets 55.1
baz gets 10.7
qux gets 98.0
quux gets 22.2
foobar gets 35.6

in conclusion

I hope it will help those who access the database using JDBC for the first time. I would appreciate it if you could point out any mistakes.

References

-How to install PostgreSQL on Mac & make initial settings -Dot Install-Introduction to PostgreSQL