A JDBC example to show you how to connect to a PostgreSQL database with a JDBC driver.
Tested with:
- Java 8
- PostgreSQL 11
- PostgreSQL JDBC driver 42.2.5
1. Download PostgreSQL JDBC Driver
Visit http://jdbc.postgresql.org/download.html to download the latest PostgreSQL JDBC Driver.
2. JDBC Connection
2.1 Make a connection to the PostgreSQL database.
JDBCExample.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCExample {
public static void main(String[] args) {
// https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html#package.description
// auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName
// register JDBC driver, optional, since java 1.6
/*try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}*/
// auto close connection
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password")) {
if (conn != null) {
System.out.println("Connected to the database!");
} else {
System.out.println("Failed to make connection!");
}
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
Output, No driver?
> javac JDBCExample.java
> java JDBCExample
SQL State: 08001
No suitable driver found for jdbc:postgresql://127.0.0.1:5432/testTo run it with java command, we need to load the PostgreSQL JDBC driver manually. Assume everything is stored in the c:\db folder, run it again with -cp option.
> java -cp "c:\db\postgresql-42.2.5.jar;c:\db" JDBCExample
Connected to the database!3. Maven
The PostgreSQL JDBC driver is available in the Maven central repository.
pom.xml
<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.5</version> </dependency>
4. JDBC Select
4.1 Another JDBC example to get all rows from a table.
JDBCExample2.java
package com.favtuts.jdbc;
import com.favtuts.jdbc.model.Employee;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCExample2 {
public static void main(String[] args) {
List<Employee> result = new ArrayList<>();
String SQL_SELECT = "Select * from EMPLOYEE";
// auto close connection and preparedStatement
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/test", "postgres", "password");
PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) {
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
long id = resultSet.getLong("ID");
String name = resultSet.getString("NAME");
BigDecimal salary = resultSet.getBigDecimal("SALARY");
Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");
Employee obj = new Employee();
obj.setId(id);
obj.setName(name);
obj.setSalary(salary);
// Timestamp -> LocalDateTime
obj.setCreatedDate(createdDate.toLocalDateTime());
result.add(obj);
}
result.forEach(x -> System.out.println(x));
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
Employee.java
package com.favtuts.jdbc.model;
import java.math.BigDecimal;
import java.time.LocalDateTime;
public class Employee {
private Long id;
private String name;
private BigDecimal salary;
private LocalDateTime createdDate;
//...
}
Table definition.
CREATE TABLE EMPLOYEE
(
ID serial,
NAME varchar(100) NOT NULL,
SALARY numeric(15, 2) NOT NULL,
CREATED_DATE timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (ID)
);
Download Source Code
$ git clone https://github.com/favtuts/java-core-tutorials-examples.git
$ cd java-jdbc/postgresql