How to set up PostgreSQL to work with Java

Postgres supports the Java language but working with Java in Postgres is easier with the Java Database Connectivity (JDBC) interface. Below is an introduction to the Java Database Connectivity interface and provide you with a few code tips to get you started. 

What Is PostgreSQL and Why It’s Cool Again

PostgreSQL is a highly popular object-oriented relational database management system (RDBMS). It is open-source and free, which makes it very popular with developers. The platform supports relational and non-relational queries by supporting JSON. However, one of the characteristics of Postgres is its emphasis on compliance with SQL standards. The database is designed to handle a range of workloads from single machines to data warehouses. 

Postgres main features include:

• Compatibility with major OS—supports Windows, Linux, Mac OS, Solaris. 
• Main programming languages support—such as C/C++, Phyton, Go and Java.
• Multiversion Concurrency Control—this is the main Postgres feature that allows several readers and writers to access and work in the same database at the same time. 
• High compliance—complies with 169 features of the SQL standard. 
• Dynamic community—develop and release extensions regularly. 

Despite being one of the oldest database systems on the market, PostgreSQL ranks as one of the most popular Database Management Systems amongst developers and database administrators. Some of the reasons for its popularity are: 

• Free to use—you can store unlimited data without functionality constraints and no distribution restrictions. 
• Avoids vendor lock-in—since allows the distribution, there are an array of companies providing services for Postgres, thus eliminating vendor lock-in. 
• Extensible—the Postgres community takes advantage of the easy extensions to add features to the database almost constantly. This flexibility allows users that need a particular feature to just write an extension for it. 

Postgres extensions make it possible for users to adapt the database for their needs without changing the core database. In addition, some cool PostgreSQL features include the ability to create your own data types and XML data queries. The streaming replication makes postgres backup solutions very attractive. 

PostgreSQL allows to run NoSQL queries by supporting JavaScript Object Notation (JSON) which is a lightweight format for storing and transporting data.  Postgres supports Java through a Java Database Connectivity (JDBC) which allows Java programs to connect to a PostgreSQL database using standard Java code. Read on to learn how to set up Postgres to work with Java. 

Setting Up PostgreSQL to Work with Java

Before we can start using PostgreSQL in Java programs we need to set up JDBC and Java on the machine. 

1. Download and install the latest version of Postgres JDBC from the Postgres repository.  
2. You should add the downloaded .jar file in your classpath or use it with the -classpath option.
3. The PostgreSQL server must be configured to allow TCP/IP connections, then verify users are allowed to connect to the server by setting up client authentication. 
4. Creating a database that will be accessed through JDBC needs a correct encoding of the data, meaning you should use the UNICODE encoding as a default to avoid discrepancies. 

Initializing the Driver implies:

• Importing the JDBC—using import java.sql.*;
• Loading the Driver—with the new versions the drivers will be loaded by the JVM when the application connects to PostgreSQL. 

The Postgresql Java Jdbc Interface—What It Is and Basic Commands

As mentioned above, JDBC is the core API of Java that provides a standard interface to SQL-compliant databases, especially PostgreSQL. We covered how to set up the JDBC driver and some considerations when creating the database. But how do we connect to an existing database? The following example of Java code, from the out code library, demonstrates how to connect to a postrgresql database instance using Amazon Web Services:

 public void donothing() throws SQLException, ClassNotFoundException
{
  Class.forName("org.postgresql.Driver");
  String url = "jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";
  Connection conn = DriverManager.getConnection(url);
  // Do stuff here
  conn.close();
}

Querying data from a table in the PostgreSQL database using JDBC API
Once you establish a database connection to the PostgreSQL server you need to create a statement object, which represents a SQL statement. There are three kinds of Statement objects:

• Statement—you implement a simple Statement without parameters
• PreparedStatement—a subclass, you can use it to add the parameters to SQL statements. 
• CallableStatement—you can use it to extend a PreparedStatement used to execute a stored procedure. 

After you create the Statement object, you can execute the query using one of these three methods:

• Execute—this command will return true if the first object of the query is a ResultSet object. 
• executeQuery—will return only one ResultSet object.
• executeUpdate—you can use this method for statements such as INSERT or DELETE, as it returns the number of rows affected by the statement. 

Finally, you can use a Java cursor to call the methods of the ResultSet object, then use a try-with-resources statement. This allows you to close all three objects (ResultSet, Statement, and Connection) automatically. 

Create a Table
You can use the following Java program to create a table in an open database.
import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         System.out.println("Opened database successfully");
         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
            "(ID INT PRIMARY KEY     NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

Once the program is compiled and executed, it will display the following two lines: 

Opened database successfully
Table created successfully

Discover similar code snippet examples using java.sql.DriverManager.getConnection

Insert Operation

How we can create records on our table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");
         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);
         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);
         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);
         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);
         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

Discover Similar examples with prepared statement

When the above program is compiled and executed, it will create given records in COMPANY table and will display the following two lines.

Opened database successfully
Records created successfully

Using the JDBC PostgreSQL transaction using JDBC transaction API.  

This is useful when you don’t want one SQL statement to execute unless another one completes. For example, inserting a new actor and the film that the actor leads.  Using a transaction you ensure that both statements take effect or neither does. 

The first step is to disable auto-commit mode, since Postgres treats each statement as a transaction, automatically committing it. Therefore, if you want to isolate one or more statements in a transaction, the first step is disabling auto-commit mode. You call the setAutoCommit() method of Connection by this command:

conn.setAutoCommit(false);

If you want to commit a transaction, you should call the commit method of the Connection object:

conn.commit();

Next, when you call the commit() method, you will find all the previous statements committed together as a single unit.

Rollback a transaction

Sometimes you don’t get the result of one statement that your expected. In this case, you can use the  rollback() method of the Connection object, to interrupt the current transaction, restoring values to original. 

conn.rollback();

You can learn more in this YouTube video tutorial 

The Bottom Line

Setting up the PostgreSQL JDBC interface may seem tricky. However, the latest versions of PostgreSQL have made the installation pretty straightforward by loading the driver automatically in the JVM.

Working on PostgreSQL using Java code is easier using the proprietary JDBC interface. In this article, we provided you with a brief introduction to how to install the interface and basic commands to start playing around.