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.