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

Recommended Posts

How to get started with JDBC using PostgresSQL on MacOS
How to get started with slim
[Note] How to get started with Rspec
How to get started with Eclipse Micro Profile
How to get started with creating a Rails app
How to get started with Gatsby (TypeScript) x Netlify x Docker
How to get along with Rails
I tried to get started with Swagger using Spring Boot
How to install Adopt OpenJDK on Debian, Ubuntu with apt (-get)
I tried to get started with WebAssembly
Rails beginners tried to get started with RSpec
How to get resource files out with spring-boot
Using JDBC on Linux
Get started with Gradle
How to run javafx with Raspberry Pi Posted on 2020/07/12
Whether to enable SSL when using JDBC with MySQL.
How to unit test with JVM with source using RxAndroid
As of April 2018 How to get Java 8 on Mac
Get started with "Introduction to Practical Rust Programming" (Day 3)
How to get jdk etc from oracle with cli
[Docker] How to update using a container on Heroku and how to deal with Migrate Error
How to set environment variables when using Payjp with Rails
Get started with Spring boot
How to deploy on heroku
How to get inside a container running on AWS Fargate
First year Java developers on udemy get started with PHP
How to get values in real time with TextWatcher (Android)
How to number (number) with html.erb
Get started with DynamoDB with docker
How to update with activerecord-import
How to make an app using Tensorflow with Android Studio
How to install JDK 8 on Windows without using the installer
How to register as a customer with Square using Tomcat
How to authorize using graphql-ruby
How to use PlantUML with Visual Studio Code (created on October 30, 2020)
How to execute Postgresql copy command with column information on Java
Easy code review to get started with Jenkins / SonarQube: Static analysis
How to run a mock server on Swagger-ui using stoplight/prism (using AWS/EC2/Docker)
I tried connecting to MySQL using JDBC Template with Spring MVC
How to switch Java version with direnv in terminal on Mac
How to get the contents of Map using for statement Memorandum
How to get boolean value with jQuery in rails simple form
How to write when installing Amazon Corretto 8 on CentOS 8 with Ansible.
(Spring-data-jpa) How to get POJO (other than entity class) using JPA2.1
How to call with One Touch (without confirmation) on Android (Java)
How to get JDK 11 on your mac in a comfortable way
How to scroll horizontally with ScrollView
Let's get started with parallel programming
How to use Java HttpClient (Get)
How to deploy jQuery on Rails
How to deploy Laravel on CentOS 7
How to "hollow" View on Android
How to use Spring Data JDBC
[Java] How to update Java on Windows
How to install ImageMagick on Windows 10
How to enclose any character with "~"
How to use Ruby on Rails
How to deploy Bootstrap on Rails
How to run JavaFX on Docker
How to get parameters in Spark
How to use Bio-Formats on Ubuntu 20.04