How to query PostgreSQL data using the SELECT statement in Java

January 19, 2023

SUMMARY: This article provides instructions for querying data using the PostgreSQL SELECT statement in Java.

1. Getting started

2. Querying with the SELECT statement in Java

3. Common mistakes and errors

 

Getting started

To query data from a PostgreSQL database server using Java, you need to satisfy below prerequisites:

1. Make sure you have Java and Java Compiler (javac) installed on your server.

You can install java and javac by using the command:

yum install java 

 

You can verify the list of Java versions installed on your server by using the command:

rpm -qa | grep java

Example

[root@localhost data]# rpm -qa | grep java

javacc-javadoc-5.0-10.el7.noarch

java-1.7.0-openjdk-1.7.0.231-2.6.19.1.el7_6.x86_64

javassist-3.16.1-10.el7.noarch

javacc-maven-plugin-2.6-17.el7.noarch

postgresql94-jdbc-javadoc-9.4.1207-2.rhel7.noarch

javapackages-tools-3.4.1-11.el7.noarch

java-1.7.0-openjdk-headless-1.7.0.231-2.6.19.1.el7_6.x86_64

java-1.8.0-openjdk-headless-1.8.0.232.b09-0.el7_7.x86_64

java-1.8.0-openjdk-devel-1.8.0.232.b09-0.el7_7.x86_64

javacc-maven-plugin-javadoc-2.6-17.el7.noarch

javacc-demo-5.0-10.el7.noarch

javacc-manual-5.0-10.el7.noarch

javamail-1.4.6-8.el7.noarch

java-1.8.0-openjdk-1.8.0.232.b09-0.el7_7.x86_64

 

2. Download the latest version of the PostgreSQL JDBC driver:  https://jdbc.postgresql.org/

3. Set the correct CLASSPATH for the PostgreSQL JDBC driver and classpath files as follows:

export set CLASSPATH=/home/edb/Desktop/postgresql-42.2.8.jar:.

 

You can check the CLASSPATH setting by using the command:

echo $CLASSPATH

 

Example

[root@localhost data]# echo $CLASSPATH

/home/edb/Desktop/postgresql-42.2.8.jar:.

[root@localhost data]#

 

Querying with the SELECT statement in Java

Here is sample code written for executing a PostgreSQL SELECT statement in Java:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;





public class DataSelection

{

   public static void main( String args[] ) 

{

   Connection c = null;

   Statement stmt = null;

   try {

      Class.forName("org.postgresql.Driver");

      c = DriverManager.getConnection("jdbc:postgresql://localhost:5433/procedure_demo","postgres", "adminedb");

//     c.setAutoCommit(false);

      System.out.println("Successfully Connected.");



      stmt = c.createStatement();

      ResultSet rs = stmt.executeQuery( "select * from public.\"Album\" ;" );

      while ( rs.next() ) {

         int albumid = rs.getInt("AlbumId");

         String  title = rs.getString("Title");

         int artistid  = rs.getInt("ArtistId");

         System.out.printf( "AlbumId = %s , Title = %s, ArtistId = %s ", albumid,title, artistid );

         System.out.println();

      }

      rs.close();

      stmt.close();

      c.close();

   } catch ( Exception e ) {

      System.err.println( e.getClass().getName()+": "+ e.getMessage() );

      System.exit(0);

   }

   System.out.println(" Data Retrieved Successfully ..");

   }

}

 

Save the above code as “DataSelection.java” and then compile and execute it:

[root@localhost data]# export set CLASSPATH=/home/edb/Desktop/postgresql-42.2.8.jar:.

[root@localhost data]# javac DataSelection.java

[root@localhost data]# java DataSelection

Successfully Connected.

AlbumId = 1 , Title = For Those About To Rock We Salute You, ArtistId = 1

AlbumId = 2 , Title = Balls to the Wall, ArtistId = 2

.

.

.

AlbumId = 346 , Title = Mozart: Chamber Music, ArtistId = 274

AlbumId = 347 , Title = Koyaanisqatsi (Soundtrack from the Motion Picture), ArtistId = 275

 Data Retrieved Successfully ..

[root@localhost data]#

 

Common mistakes and errors

1. Error: java.lang.ClassNotFoundException: org.postgresql.Driver

The error is self-explanatory: the Postgres JDBC driver that we have added is not accessible or not available in the correct CLASSPATH.

2. Error: Could not find or load main class DataSelection

One common reason for this error is when the class file we created is not accessible or its path is not available in the correct CLASSPATH.

3. Make sure that the server from which you are connecting has been whitelisted/allowed in the pg_hba.conf file for the connection.

 

Share this

Relevant Blogs

More Blogs

An Overview of PostgreSQL Indexes

h2 { text-align: left !important; } .summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article describes indexes in PostgreSQL and how they can help retrieve data faster. It covers the types of indexes...
January 24, 2023