Java postgresql execute query

How to query PostgreSQL data using the SELECT statement in Java

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

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:

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

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:

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.

Источник

The Ultimate Guide to Query Data from The PostgreSQL Using JDBC

Summary: in this tutorial, you will learn how to query data from a table in the PostgreSQL database using JDBC API.

To query data from a table using JDBC, you use the following steps:

1. Establish a database connection to the PostgreSQL server.
2. Create an instance of the Statement object
3. Execute a statement to get a ResultSet object
4. Process the ResultSet object.
5. Close the database connection.

Establishing a database connection

To connect to the PostgreSQL database, you need to provide account information such as username, password, and the connection string. See the connecting to the PostgreSQL database server for more information.

For example, the following method connects to a PostgreSQL database and returns a Connection object:

 /** * Connect to the PostgreSQL database * * @return a Connection object * @throws java.sql.SQLException */ public Connection connect() throws SQLException < return DriverManager.getConnection(url, user, password); > Code language: Java (java)

The URL, user, and password are as follows:

 private final String url = "jdbc:postgresql://localhost/dvdrental"; private final String user = "postgres"; private final String password = "postgres";Code language: SQL (Structured Query Language) (sql)

Creating a Statement Object

A Statement object represents an SQL statement. First, you create a Statement object from the Connection object. Then, you execute the Statement object to get a ResultSet object that represents a database result set.

JDBC provides you with three kinds of Statement objects:

• Statement: you use the Statement to implement a simple SQL statement that has no parameters.
• PreparedStatement: is the subclass of the Statement class. It gives you the ability to add the parameters to the SQL statements.
• CallableStatement: extends the PreparedStatement class used to execute a stored procedure that may have parameters.

Executing the query

To execute a query, you use one of the following methods of the Statement object:

• execute: returns true if the first object of the query is a ResultSet object. You can get the ResultSet by calling the method getResultSet.
• executeQuery: returns only one ResultSet object.
• executeUpdate: returns the number of rows affected by the statement. You use this method for the INSERT, DELETE, or UPDATE statement.

Processing the ResultSet Object

After having a ResultSet object, you use a cursor to loop through the result set by calling the methods of the ResultSet object.

Note that this cursor is a Java cursor, not the database cursor.

Closing a database connection

In JDBC 4.1, you use a try-with-resources statement to close ResultSet, Statement, and Connection objects automatically.

Querying data examples

In the following section, we will show you various example of querying data from simple to complex.

Querying data with a statement that returns one row

The following method returns the number of actors in the actor table using the COUNT function.

 /** * Get actors count * @return */ public int getActorCount() < String SQL = "SELECT count(*) FROM actor"; int count = 0; try (Connection conn = connect(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SQL)) < rs.next(); count = rs.getInt(1); > catch (SQLException ex) < System.out.println(ex.getMessage()); >return count; >Code language: Java (java)

We have done the following in the getActorCount method:

1. First, prepared an SQL statement that counts the number of rows in the actor table.
2. Second, established a connection to the database, created a Statement object, and executed the query.
3. Third, processed the result set by moving the cursor the first row and get its value using the getInt() method.

The following demonstrates the output of the method.

Querying data using a statement that returns multiple rows

The following getActors method queries data from the actor table and displays the actor information.

 /** * Get all rows in the actor table */ public void getActors() < String SQL = "SELECT actor_id,first_name, last_name FROM actor"; try (Connection conn = connect(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SQL)) < // display actor information displayActor(rs); > catch (SQLException ex) < System.out.println(ex.getMessage()); >>Code language: Java (java)

In the displayActor method, we loop through the result set and print out the information for each row.

/** * Display actor * * @param rs * @throws SQLException */ private void displayActor(ResultSet rs) throws SQLException < while (rs.next()) < System.out.println(rs.getString("actor_id") + "\t" + rs.getString("first_name") + "\t" + rs.getString("last_name")); > >Code language: Java (java)

Querying data using a statement that has parameters

To query the database with parameters, you use the PreparedStatement object.

First, you use the question mark (?) as the placeholder in the SQL statement. Then, you use methods of the PreparedStatement object such as setInt, setString,… to pass the value to the placeholders.

The following method allows you to find an actor by his/her id.

 /** * Find actor by his/her ID * * @param actorID */ public void findActorByID(int actorID) < String SQL = "SELECT actor_id,first_name,last_name " + "FROM actor " + "WHERE actor_id = ?"; try (Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL)) < pstmt.setInt(1, actorID); ResultSet rs = pstmt.executeQuery(); displayActor(rs); > catch (SQLException ex) < System.out.println(ex.getMessage()); >>Code language: Java (java)

You can download the full source code of the tutorial below:

package com.postgresqltutorial; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * * @author postgresqltutorial.com */ public class Main < private final String url = "jdbc:postgresql://localhost/dvdrental"; private final String user = "postgres"; private final String password = "postgres"; /** * Connect to the PostgreSQL database * * @return a Connection object * @throws java.sql.SQLException */ public Connection connect() throws SQLException < return DriverManager.getConnection(url, user, password); > /** * Get all rows in the actor table */ public void getActors() < String SQL = "SELECT actor_id,first_name, last_name FROM actor"; try (Connection conn = connect(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SQL)) < // display actor information displayActor(rs); > catch (SQLException ex) < System.out.println(ex.getMessage()); >> /** * Get actors count * @return */ public int getActorCount() < String SQL = "SELECT count(*) FROM actor"; int count = 0; try (Connection conn = connect(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SQL)) < rs.next(); count = rs.getInt(1); > catch (SQLException ex) < System.out.println(ex.getMessage()); >return count; > /** * Display actor * * @param rs * @throws SQLException */ private void displayActor(ResultSet rs) throws SQLException < while (rs.next()) < System.out.println(rs.getString("actor_id") + "\t" + rs.getString("first_name") + "\t" + rs.getString("last_name")); > > /** * Find actor by his/her ID * * @param actorID */ public void findActorByID(int actorID) < String SQL = "SELECT actor_id,first_name,last_name " + "FROM actor " + "WHERE actor_id = ?"; try (Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL)) < pstmt.setInt(1, actorID); ResultSet rs = pstmt.executeQuery(); displayActor(rs); > catch (SQLException ex) < System.out.println(ex.getMessage()); >> /** * @param args the command line arguments */ public static void main(String[] args) < Main main = new Main(); main.findActorByID(200); > >Code language: Java (java)

In this tutorial, we have shown you how to query data from the PostgreSQL database using JDBC API.

Источник