Saturday, 02 November 2019 05:46

Java and Postgres

Written by http://blog.mclaughlinsoftware.com/2019/11/02/java-and-postgres/
Rate this item
(0 votes)

I wanted to get Java working with PostgreSQL to test some GUI interfaces on Linux. Figuring out the necessary JAR file for the JDBC was my first hurdle. I found it

was postgreSQL-42-2.5.jar file.

You can download it with the following command line:

wget https://jdbc.postgresql.org/download/postgresql-42.2.5.jar

wget https://jdbc.postgresql.org/download/postgresql-42.2.5.jar

I downloaded it to a Java directory off the home/student directory. Then, I added the following CLASSPATH to local java.env environment file.

#!/usr/bin/bash# Set the Java CLASSPATH environment variable to include the JDBC jar file.exportsetCLASSPATH=/home/student/Java/postgresql-42.2.5.jar:.

#!/usr/bin/bash # Set the Java CLASSPATH environment variable to include the JDBC jar file. export set CLASSPATH=/home/student/Java/postgresql-42.2.5.jar:.

I sourced the postgresql-42.2.5.jar file and I wrote the following JavaTest.java program:

123456789101112131415161718192021222324252627282930313233343536
/* Import classes. */importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.logging.Level;importjava.util.logging.Logger;publicclass JavaTest {publicstaticvoid main(String[] args){/* Set default strings for the connection. */String url ="jdbc:postgresql://localhost:5432/videodb";String user ="student";String password ="student";/* Try the connection and statement. */try{Connection conn =DriverManager.getConnection(url, user, password);Statement st = conn.createStatement();ResultSet rs = st.executeQuery("SELECT VERSION()");if(rs.next()){System.out.println(rs.getString(1));}/* Close the result set and statement. */ rs.close(); st.close();}catch(SQLException e){ Logger logger = Logger.getLogger(JavaTest.class.getName()); logger.log(Level.SEVERE, e.getMessage(), e);}}}

/* Import classes. */ import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; public class JavaTest { public static void main(String[] args) { /* Set default strings for the connection. */ String url = "jdbc:postgresql://localhost:5432/videodb"; String user = "student"; String password = "student"; /* Try the connection and statement. */ try { Connection conn = DriverManager.getConnection(url, user, password); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT VERSION()"); if (rs.next()) { System.out.println(rs.getString(1)); } /* Close the result set and statement. */ rs.close(); st.close(); } catch (SQLException e) { Logger logger = Logger.getLogger(JavaTest.class.getName()); logger.log(Level.SEVERE, e.getMessage(), e); } } }

I compiled the JavaTest.java program and tested it. It failed with the following error:

FATAL: Ident authentication failed for user - Unable to connect to PostgreSQL

FATAL: Ident authentication failed for user - Unable to connect to PostgreSQL

The failure occurred because I hadn’t allowed the connection in PostgreSQL’s pg_hba.conf file. I changed the following line in my pg_hba.conf file:

host all all 127.0.0.1/32 ident

host all all 127.0.0.1/32 ident

to

host all all 127.0.0.1/32 trust

host all all 127.0.0.1/32 trust

Then, I restarted the postgresql-11 service, like this:

systemctl restart postgresql-11.service

systemctl restart postgresql-11.service

The JavaTest program ran successfully and returned:

PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64-bit

PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64-bit

You can extend the logic to output a comma-separated value file by leveraging the JDBC help page, like this:

/* Import classes. */importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.logging.Level;importjava.util.logging.Logger;/* Create class withonly a static method for runtime testing. */publicclass JavaReturnSet {publicstaticvoid main(String[] args){/* Set default strings for the connection. */String url ="jdbc:postgresql://localhost:5432/videodb";String user ="student";String password ="student";/* Try the connection and statement. */try{/* Set connection, statement, and result set. */Connection conn =DriverManager.getConnection(url, user, password);Statement st = conn.createStatement();/* Use the + to concatenate lines for query clarity. */ResultSet rs = st.executeQuery("SELECT m.account_number\n"+", CONCAT(c.last_name,', ',c.first_name)\n"+"FROM member m JOIN contact c\n"+"ON m.member_id = c.member_id");/* Get query metadata for subsequent management of results. */ResultSetMetaData rsmd = rs.getMetaData();String line;while(rs.next()){/* Initialize the line output for each row. */ line ="";/* Process the columns. */for(int i =0; i < rsmd.getColumnCount(); i++){if(rsmd.getColumnType(i +1)==12){ line = line +"\""+ rs.getString(i +1)+"\"";}else{ line = line + rs.getInt(i +1);}/* Put a comma between output columns. */if(i < rsmd.getColumnCount()-1){ line = line +",";}}System.out.println(line);}/* Close the result set and statement. */ rs.close(); st.close();}catch(SQLException e){ Logger logger = Logger.getLogger(JavaReturnSet.class.getName()); logger.log(Level.SEVERE, e.getMessage(), e);}}}

/* Import classes. */ import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; /* Create class withonly a static method for runtime testing. */ public class JavaReturnSet { public static void main(String[] args) { /* Set default strings for the connection. */ String url = "jdbc:postgresql://localhost:5432/videodb"; String user = "student"; String password = "student"; /* Try the connection and statement. */ try { /* Set connection, statement, and result set. */ Connection conn = DriverManager.getConnection(url, user, password); Statement st = conn.createStatement(); /* Use the + to concatenate lines for query clarity. */ ResultSet rs = st.executeQuery("SELECT m.account_number\n" + ", CONCAT(c.last_name,', ',c.first_name)\n" + "FROM member m JOIN contact c\n" + "ON m.member_id = c.member_id"); /* Get query metadata for subsequent management of results. */ ResultSetMetaData rsmd = rs.getMetaData(); String line; while (rs.next()) { /* Initialize the line output for each row. */ line = ""; /* Process the columns. */ for (int i = 0; i < rsmd.getColumnCount(); i++) { if (rsmd.getColumnType(i + 1) == 12) { line = line + "\"" + rs.getString(i + 1) + "\""; } else { line = line + rs.getInt(i + 1); } /* Put a comma between output columns. */ if (i < rsmd.getColumnCount() - 1) { line = line + ","; } } System.out.println(line); } /* Close the result set and statement. */ rs.close(); st.close(); } catch (SQLException e) { Logger logger = Logger.getLogger(JavaReturnSet.class.getName()); logger.log(Level.SEVERE, e.getMessage(), e); } } }

As always, I hope this helps those looking for a clean solution.

Read 37 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.