Executing database queries using EDB JDBC

The purpose of this article is to demonstrate a basic approach on how to write a basic Java program on Linux to connect and execute queries in EPAS. This article can be useful for the engineers who are working with the cases involving Java applications.

The first step is to install Java and EDB JDBC:

yum -y install java 
yum -y install edb-jdbc
yum -y install java-11-openjdk
yum  -y install java-devel

sudo update-alternatives --config java

# select 2), java-11-openjdk.x86_64

The examples below were performed using the following following OS, Java and EDB JDBC packages:

$ cat /etc/redhat-release 
Rocky Linux release 8.7 (Green Obsidian)

$ yum list installed | grep "java\|jdbc"
edb-jdbc.noarch                       42.5.0.1-1.rhel8                       @enterprisedb-enterprise-noarch
java-1.8.0-openjdk.x86_64             1:1.8.0.362.b09-2.el8_7                @appstream                     
java-1.8.0-openjdk-headless.x86_64    1:1.8.0.362.b09-2.el8_7                @appstream                     
java-11-openjdk.x86_64                1:11.0.18.0.10-2.el8_7                 @appstream                     
java-11-openjdk-devel.x86_64          1:11.0.18.0.10-2.el8_7                 @appstream                     
java-11-openjdk-headless.x86_64       1:11.0.18.0.10-2.el8_7                 @appstream                     
javapackages-filesystem.noarch        5.3.0-2.module+el8.3.0+125+5da1ae29    @appstream                     
tzdata-java.noarch                    2022g-1.el8                            @appstream       

Please see an example Java EDB JDBC file, adjusted from the edb-jdbc docs.

This file runs SELECT 1 on the database.

$ vi jdbc_select.java

 import java.sql.*;
 public class JDBC_select
 {
   public static void main(String[] args)
   {
     try
     {
       Class.forName("com.edb.Driver");
       String url      = "jdbc:edb://localhost:9999/test_db";
       String user     = "enterprisedb";
       String password = "pass";
       Connection con  = DriverManager.getConnection(url, user, password);
       Statement stmt  = con.createStatement();
       ResultSet rs    = stmt.executeQuery("SELECT 1");
       while(rs.next())
       {
         System.out.println(rs.getString(1));
       }

       rs.close();
       stmt.close();
       con.close();
       System.out.println("Command successfully executed");
     }
     catch(ClassNotFoundException e)
     {
       System.out.println("Class Not Found : " + e.getMessage());
     }
     catch(SQLException exp)
     {
       System.out.println("SQL Exception: " + exp.getMessage());
       System.out.println("SQL State:     " + exp.getSQLState());
       System.out.println("Vendor Error:  " + exp.getErrorCode());
     }
   }
 }

• Edit String url, replacing port 9999 and database test_db with desired values;
• Edit ResultSet rs, replacing SQL query SELECT 1 with desired query.

The location of the edb-jdbc.jar file which is used to run the program may vary, find it using:

$ find /usr /opt -type f -name 'edb-jdbc*.jar'

/usr/edb/jdbc/edb-jdbc18.jar

Run the following, inserting the .jar path:

$ java -cp /usr/edb/jdbc/edb-jdbc18.jar JDBC_select.java

Example expected output:

1
Command successfully executed

This command specifies the CLASSPATH, compiles and executes in one command.

This file alters data in the database, changing the password of a user. This file is written in a slightly different way to the previous.

Additionally, this example demonstrates creating a CLASSPATH, compiling and running the function in separate commands.

vi jdbc_alter.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBC_alter
{
  public static void main( String args[] )
	{
		 Connection c = null;
		 Statement stmt = null;
		 try
			{
				Class.forName("com.edb.Driver");
				c = DriverManager.getConnection("jdbc:edb://localhost:5444/edb","enterprisedb", "adminedb");
				c.setAutoCommit(false);
				System.out.println("Successfully Connected.");
				stmt = c.createStatement();
				ResultSet rs = stmt.executeQuery( "alter role test password \'test124\' replace \'test1241212\';");
        rs.close();
				stmt.close();
				c.close();
			}
		catch ( Exception e )
			{
				System.err.println( e.getClass().getName()+": "+ e.getMessage() );
				System.exit(0);
      }
		System.out.println("Alter Successful.");
	 }
}

Here java.sql, being imported at the beginning of the file, provides the API for accessing and processing data stored in a data source, i.e. provides methods for querying and updating data in a database, and is oriented towards relational databases.

DriverManager.getConnection needs to be updated with the database connection string. We need to run System.out.println with an appropriate message which will be printed after successful completion of the required step.

The query or job we need to execute can be specified in stmt.executeQuery. Here we have executed a command to alter a user password as an example. To get notified for the job failure or success, we can use System.err.println function.

We need to set the CLASSPATH environment variable in the machine as below for using EDB JDBC.

export CLASSPATH=/usr/edb/jdbc/edb-jdbc18.jar:.

We can verify the CLASSPATH setting by using the command:

echo $CLASSPATH

Now the code we save in point 2 needs to be compiled before use to check for any issues with the same. Below command can be used for the same.

javac jdbc_alter.java

Once it is compiled, it will create a new file with a similar name (jdbc_alter in this case).

# ls
jdbc_alter.java jdbc_alter.class jdbc_alter

We can execute the program using below command:

java jdbc_alter

We can see the below output after executing the file. Here, as we run an ALTER command, it doesn't shows a result like the previous SELECT operation.

Successfully Connected.
Alter Successful.