Connect to PostgreSQL with JDBC driver

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.

postgresql 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/test

To 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.

project layout

> 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.mkyong.jdbc;

import com.mkyong.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.mkyong.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

References

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
52 Comment threads
25 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
59 Comment authors
Amandeep GandhileoParth PatibandhaMarc Puerto Marquèsclaf Recent comment authors
newest oldest most voted
mutaz
Guest
mutaz

when i run it using the cmd it gives the following error:
could not find or load main class for JDBCExample
and when i use JCreator to run it, it gives the following error:
Usage: java [-options] class [args…]
(to execute a class)
or java [-options] -jar jarfile [args…]
(to execute a jar file)…
…………………..

claf
Guest
claf

You need to compile your java file to get a class file. Use javac from JDK to do so.

Sebastian Cheung
Guest
Sebastian Cheung

Have added postgreSQL JDBC driver, and also in PostGreSQL.app running in the background and createdb mkyong, but it is still reporting error of:

org.postgresql.util.PSQLException: FATAL: role “mkyong” does not exist

at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:691)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:207)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:65)

at org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:156)

at org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:35)

at org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnection.java:22)

at org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.java:47)

at org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:30)

at org.postgresql.Driver.makeConnection(Driver.java:414)

at org.postgresql.Driver.connect(Driver.java:282)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:247)

at JDBCExample.main(JDBCExample.java:31)

Mina
Guest
Mina

Thanks!

Sid
Guest
Sid

Please add PostgreSQL JDBC Driver in your Classpath

how to get solution for this??

Sid
Guest
Sid

Please add PostgreSQL JDBC Driver in your Classpath

how to solve this?/

Sid
Guest
Sid

Please add PostgreSQL JDBC Driver in your Classpath

Sid
Guest
Sid

how to give -cp in ubuntu….????

Bakasa Barato Kawaii#loliTim
Guest
Bakasa Barato Kawaii#loliTim

Thanks men, your info help me more than another pages.

Dinesh
Guest
Dinesh

Excellent Work .. thanks much.. Keep up..

Khan
Guest
Khan

Anyone help me! I cannot query some data from table.

I have a table named FOO in PostgrelSQL. In java I qurey: select * from FOO. Then it make error “ERROR: relation “FOO” does not exist”. Sorry for my English

Nophawit Karunlanchakorn
Guest
Nophawit Karunlanchakorn

thank to much for your article.

cuong le
Guest
cuong le

Hi Mkyong. I’m building web service java using server tomcat and apache axis 2. But when i connect to postgresql database, so it has an error: “java.lang.NoClassDefFoundError: org/postgresql/Driver” although i have built path jdbc postgresql library.
Could you tell me an advise to solve this error.
Thanks you so much!

Asiana
Guest
Asiana

Thank you so much. This is a very quick and easy tutorial. Thank you :)

Karl
Guest
Karl

This is possibly the best guide for connecting to Postgresql with JDBC. If I can suggest a few things before this tutorial begins, a few lines on the setup within postgresql itself that are needed first but not obvious how to do them. I will also include how I did each step, but I did it a crude way — sorry! -create user with password (I created the user, then altered it to add a password) -create database with user access (I created the database with owner = user) -configure postgresql to allow user access through the localhost (I edited… Read more »

Edison Quisiguiña
Guest
Edison Quisiguiña

Muchas Gracias, Bendiciones :)
Thanks a lot, Blessings on you :)

Alexander
Guest
Alexander

Thanks!

Joshua
Guest
Joshua

Mr mkyong, I have an android app that will connect to the Postgresql and when i try your code it says that my jdbc driver is registered but when i connect it to the postgre database, it can’t connect ..why?? Please help
im using eclipse. Is it about the connection?

Asem
Guest
Asem

thank you very much!

Luke
Guest
Luke

Nice and simple explaination, thanks!

Kamran
Guest
Kamran

Awesome. Thanks! Plain and simple, unlike all the other Spring tutorials out there! Please carry on the great work sir! :)

Ahmedou
Guest
Ahmedou

Thank a lot

Matthias
Guest
Matthias

Great description how to connect to a postgre-database using JDBC!

Mike
Guest
Mike

I found JdbcTemplate from Spring (e.g. here http://www.dzone.com/tutorials/java/spring/spring-jdbc-tutorial-1.html) less bulky

Mike
Guest
Mike
Roberta
Guest
Roberta

It was really helpful! Thank you a lot!

Saga Castro
Guest
Saga Castro

Hi. Thanks, your site has helped me quite a lot (Just stating with Java). Now I’m trying yo connect to a remote DB located in my school’s server that i usually connect through putty’s SSH/Ubuntu 12.04 and I have the task of connecting to that DB from a java program. I have this code import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; public class JDBCExample { public static void main(String[] argv) { System.out.println("-------- PostgreSQL " + "JDBC Connection Testing ------------"); try { Class.forName("org.postgresql.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); return; } System.out.println("PostgreSQL JDBC Driver Registered!"); Connection connection = null; try { connection… Read more »

Will
Guest
Will

Hope you have solved it but the problem is your JDBC isn’t located at the specified path or you have downloaded the JDBC driver.

Will
Guest
Will

Also, it says “.driver” it should be “.JDBC”

Innocent
Guest
Innocent

NB:
I use the default database provided by the software. it is called: DREBY. openbravo pos version is 2.30.2.
or knowing anything java or linux I prefer to entrust an expert like you.
I’ve entered data into the database. if you agree I will pass. thank you

Innocent
Guest
Innocent

A Mr. Kyong. First of all, congratulations for the work you do. I would like to request your assistance to modify a software point of sale pos openbarvo call. I’d like to make a few changes to adapt to my commercce. I am convinced that you know you should do it. The changes I want are: – Turn the A5 ticket (because I use a laser printer hp 1020) – Make a statement of my profits over – Change the state of my products (enlarge the column name products, drop the column taxes) and finally to another state with just… Read more »

ravi mandali
Guest
ravi mandali

hi..
i am use netbeans 7.2
and my database cannectin is pgadmin of postgresql..
i have ready classes for the database….
so how can i direct make table in pgadmin by using of persistance classes…??

plzz sir rpl me…

ARUMUGAM
Guest
ARUMUGAM

Dear Sir,

I am using openjdk 1.6, netbeans 6.5.1(having postgres8.3 jdbc driver), postgres8.4 on centOS6.0.

pg_hba.conf is having an entry
host all all 127.0.0.1/32 md5

postmaster (pid 1466) is running…

also added port 5432 protocol tcp to permit by SELinux.

I am getting error

“Unable to add connection. Cannot establish connection to jdbc:postgresql://127.0.0.1:5432/postgres using org.postgresql.Driver(connection refused.Check that the host name and port name are correct and that the postmaster is accepting TCP/IP connections”)

Please help me!

Thanks and Regards

abu
Guest
abu

Can you solve it?
What was the problem?

Vito
Guest
Vito

Check that the host name and port name are correct and that the postmaster is accepting TCP/IP connections

Murad
Guest
Murad

Thank you. it works