MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PostgreSQL Java

without comments

The majority of information to write this post comes form knowing how Java works and where to find the PostgreSQL JDBC Java archive (.jar) file and the standard documentation. Here are the URLs:

The rest of the example is simply demonstrating how to create a fully working program to return one or more rows from a static query. After you download the latest PostgreSQL JDBC archive, with a command like:

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

Assuming you put it in test directory, like /home/student/java, you would add it to your Java $CLASSPATH environment variable, like this:

export set CLASSPATH="/home/student/Code/java/postgresql-42.3.7.jar:."

If you’re new to Java and Linux, the . (dot) represents the present working directory and is required in the Java $CLASSPATH to avoid raising a java.lang.ClassNotFoundException when you test your code. For example, the sample program name is PostgreSQLDriver.java and if you failed to include the present working directory in the $CLASSPATH it would raise the following error message when you try to run the compiled class file:

Error: Could not find or load main class PSQL
Caused by: java.lang.ClassNotFoundException: PSQL

Now that you’ve set your Java $CLASSPATH correctly, you can copy or type this PostgreSQLDriver.java Java program into a file.

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

// 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.Properties;
 
/* You can't include the following on Linux without raising an exception. */
// import com.mysql.jdbc.Driver;
 
public class PostgreSQLDriver {
  public PostgreSQLDriver() {
    /* Declare variables that require explicit assignments because
       they're addressed in the finally block. */
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
 
    /* Declare other variables. */
    String url;
    String username = "student";
    String password = "student";
    String database = "videodb";
    String hostname = "[::1]";
    String port = "5432";
    String sql;
 
    /* Attempt a connection. */
    try {
      // Set URL.
      url = "jdbc:postgresql://" + hostname + ":" + port + "/" + database;
 
      // Create instance of MySQLDriver.
      conn = DriverManager.getConnection (url, username, password);
 
      // Query the version of the database.
      sql = "SELECT version()";
      stmt = conn.createStatement();
      rset = stmt.executeQuery(sql);
 
      System.out.println ("Database connection established");
 
      // Read row returns for one column.
      while (rset.next()) {
        System.out.println("PostgreSQL Connected to the [" + rset.getString(1) + "] database."); }
 
    }
    catch (SQLException e) {
      System.err.println ("Cannot connect to database server:");
      System.out.println(e.getMessage());
    }
    finally {
      if (conn != null) {
        try {
          rset.close();
          stmt.close();
          conn.close();
          System.out.println ("Database connection terminated");
        }
        catch (Exception e) { /* ignore close errors */ }
      }
    }
  }
  /* Unit test. */
  public static void main(String args[]) {
    new PostgreSQLDriver();
  }
}

Now, you compile the program from the present working directory with this syntax:

javac PostgreSQLDriver.java

It creates a PostgreSQLDriver.class file, which you can run with this syntax:

java PostgreSQLDriver

It will return the following, which verifies you’ve connected to a valid database in the PostgreSQL instance. You should note that the IPV6 syntax is used in the example on line #25 but you could substitute localhost, an assigned host name, or an IP address.

Written by maclochlainn

August 8th, 2023 at 8:12 pm

Posted in Java,Java 17,Linux,Postgres,PostgreSQL,PostgreSQL 15

Tagged with ,

«
»