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
53 Comment threads
25 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
60 Comment authors
prachiAmandeep GandhileoParth PatibandhaMarc Puerto Marquès 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)

Rui Teixeira
Guest
Rui Teixeira

Hi!
Your code return this line to me:
“If you reach this line, please email me by telling how you do it?”

I just chage the address to:
jdbc:postgresql://192.168.0.1:6000/comecont”,”xgest”, “qwerty”

and that return the error…
Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Please reply to my email. Thanks!

Bobs Your Uncle
Guest
Bobs Your Uncle

Postgres is telling you that you are trying to connect to database that is not there or is connecting to a database you are not allowed to. Check these things…

Posgres database is running, i.e. you have started the service and
Does the database comecont exist?
Does 192.168.0.1 exist?
Does 192.168.0.1 accept requests via TCP/IP (the net) on port 6000?
Has the firewall has accepted you as a trusted zone? (e.g. the portmaster)

duh
Guest
duh

Why don’t people “READ” error messages these days… ?

software development company
Guest
software development company

} catch (SQLException e) {
System.out.println(“Connection Failed! Check output console”);
e.printStackTrace();
return;
}

I canvot get what this part of the code does ,do you mind explain it in more detail for me to understand

vasantha kumar
Guest
vasantha kumar

hi i got the code in your side… how to run the code……and any other class path is there please send it my mail id…..pvasanth05@gmail.com….class path means any rar file

Ubaid Raja
Guest
Ubaid Raja

Nice work, thanx for the article.

Ezra
Guest
Ezra

Is there a way to connect to Postgres using javascript ?

tundra
Guest
tundra

Thank’s for the article

bullz
Guest
bullz

Newbie here,

where do i put the postgresql.jre file?

Gregg Dunkle
Guest
Gregg Dunkle

Cannot concur a lot more with this, incredibly attractive article. Thanks A Lot.

Gaurav
Guest
Gaurav

Which jre version is compatible with these code? jre1.4/1.5/1.6 which one???
I found bad version number in .class file error.
Please reply soon.
Thank in advance.

trackback
JDBC Tutorials

[…] Connect to PostgreSQL with JDBC driver Here is an example to show you how to connect to PostgreSQL database with JDBC driver. […]

haha
Guest
haha

does the codes above work on eclipse?

Alex
Guest
Alex

Hi, I got a warning:

SQL exception: java.sql.SQLException: No suitable driver found for jdbc:postgresql://127.0.0.1:5432/fundb

Please replay, my boss gonna kill me 😀

Surendra
Guest
Surendra

set your class path variable as follow
goto my computer-> properties->Advanced->Environment Variable

Click New
Variable Name=CLASSPATH
Variable Value=C:\tomcat4\common\lib\servlet-api.jar;C:\tomcat4\common\lib\commons-lang-2.4.jar; c:\jdk1.6\bin;C:\Test\postgresql-8.3-603.jdbc4.jar;

click Ok
and same as do for follow window.
click ok

click ok.

open cmd and type
java JDBCExample

Alex
Guest
Alex

It’s works.
Thanks!

Joshua
Guest
Joshua

i still have the error class not found..where can i find the tomcat folder in the above code?
PLEASE HELP

Ambarish
Guest
Ambarish

I can connect to the database when I use the code java -cp c:\test\postgresql-9.1-901.jdbc3.jar;c:\test Example1 I modidified the classpath as .; c:\test\postgresql-9.1-901.jdbc3.jar;c:\test and then I use the command. java Example1 I get the following error. java.lang.ClassNotFoundException: org.postgresql.Driver at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(N at java.net.URLClassLoader.findClass(Unknown Sou at java.lang.ClassLoader.loadClass(Unknown Sourc at sun.misc.Launcher$AppClassLoader.loadClass(Un at java.lang.ClassLoader.loadClass(Unknown Sourc at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Unknown Source) at Example1.main(Example1.java:10)

jiameng
Guest
jiameng

Hi, I have a same problem as yours, and finally I solved it. It does help if you only change the classpath ot the environment variable. Try this:
1.go to your project fold in the eclipse, right click, and then choose “run as”, click “run configurations”.
2. go to class path tab, choose “add external jar”, then choose the “postgresql-9.1-901.jdbc3.jar” you have download.
3. run it again!

This works for me, and I hope it will help you.

PS: reference

http://stackoverflow.com/questions/1052978/eclipse-java-lang-classnotfoundexception

Finally, thanks mkyong, this is a great start tutorial for gostgis on java!

THX
Guest
THX

THX A LOT

sanjeev
Guest
sanjeev

many many thanks to mkyong…its working fine…

Djurdjura
Guest
Djurdjura

Thanks for this post. Really useful.

One item I couldn’t find yet anywhere is how would you go about detecting database connection afterwards? I mean, true database connection issues, not SQL errors.

Thanks for replying,

D.

Marcia
Guest
Marcia

Thank’s!

Murad
Guest
Murad

Thank you. it works

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

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…

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 »

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

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”

Roberta
Guest
Roberta

It was really helpful! Thank you a lot!

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
Matthias
Guest
Matthias

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

Ahmedou
Guest
Ahmedou

Thank a lot

Kamran
Guest
Kamran

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

Luke
Guest
Luke

Nice and simple explaination, thanks!

Featured Posts