Java and Postgres
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 |
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. 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | /* 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 |
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 |
to
host all all 127.0.0.1/32 trust |
Then, I restarted the postgresql-11
service, like this:
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 |
You can extend the logic to output a comma-separated value file by leveraging the JDBC help page, like this:
/* 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.