JDBC to PostgreSQL - How to Update a Record in PostgreSQL using the Java JDBC Driver

Written by orkb
October 07, 2019
  • Java
  • JDBC
  • PostgreSQL

Introduction

When you have data stored in a PostgreSQL database, there will be times when you want to modify certain data in a table. The UPDATE command is used to make those modifications in PostgreSQL; with the help of the PostgreSQL JDBC driver, you can easily perform UPDATE operations from a Java application. In this article, we’ll show you how to create a Java application that connects JDBC to PostgreSQL and then performs an UPDATE operation against a database table.

Prerequisites

Before we turn our attention to Java, let’s take a moment to go over the prerequisites that are necessary for this tutorial:

  • First, you need to make sure that PostgreSQL server has been installed and configured on your machine. The service needs to be running in the background. If you’re using Linux or Windows, you can download PostgreSQL here.

  • You also need to make sure that Eclipse IDE has been installed and configured on your system. Windows and Linux users can download Eclipse IDE here.

  • You’ll need to have basic knowledge of Java syntax in order to follow along with the examples in this article.

Creating a Sample Database

In this section, we’ll create the sample database that will be used throughout this article.

First, we’ll login to our PostgreSQL server and create a database using the following command:

CREATE DATABASE cars;

We can then connect to the new database using the PostgreSQL meta-command \c followed by the database name, as seen here: \c cars;.

Then we’ll create a table named tblcars using the following command:

CREATE TABLE tblcars (
id SERIAL PRIMARY KEY,
carname TEXT NOT NULL,
brand TEXT NOT NULL
);

Now that we have a table, we can insert some records into it:

INSERT INTO tblcars(carname,brand) VALUES
('fortuner', 'toyota'),
('wigo','toyota'),
('ertiga','suzuki'),
('city','honda');

The output from this operation will look like the following:

id | carname | brand
----+----------+--------
1 | fortuner | toyota
2 | wigo | toyota
3 | ertiga | suzuki
4 | city | honda
(4 ROWS)

Updating a Record in PostgreSQL Using the PostgreSQL JDBC Driver

Now that we created our sample database, we’ll proceed with updating the records that we inserted into the table.

Connecting to PostgreSQL server via a Java Application

The first thing we need to do is to connect to the PostgreSQL database. To do this, we’ll need to create a function, and our function will require the following:

  • Connection string
  • User name
  • Password

For the purposes of this tutorial, we’ll be using a PostgreSQL superuser named ‘postgres’ with a password of ‘1234’.

// connection string
private final String conectionUrl = "jdbc:postgresql://localhost/testdatabase";

// the name of the user
private final String user_name = "postgres";

// the password of the user
private final String pwd = "1234";

NOTE: These connection details are for example only; your connection and login details may vary based on your PostgreSQL setup.

We’ll be passing these details to the getConnection() method of the DriverManager class as we connect to our PostgreSQL database server.

The code shown below defines a dbconnect() function that will use both the aforementioned connection details and the getConnection() method:

public Connection dbconnect() {
Connection dbconnect = null;

try {
dbconnect = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}

return dbconnect;
}

We can test our code by modifying the main() function, which acts as the entry point of our application. The following code details exactly what to place in the main() function. After making our changes, we can run the program by pressing CTRL + F11 on the keyboard:

public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
app.dbconnect();
}

The result will look something like this:

image shows the result of the java application

Java UPDATE Operation to a PostgreSQL Database using the JDBC Driver

The following code performs an UPDATE operation against the tblcars table in our PostgreSQL database:

public long updateCarname(Car car) {
String SQLupdate = "UPDATE tblcars "
+ "SET carname = ? "
+ "WHERE id = ?";

int rowsAffected = 0;

try (Connection conn = dbconnect();

PreparedStatement prepareStatement = conn.prepareStatement(SQLupdate)){
prepareStatement.setString(1, carname);
prepareStatement.setInt(2, id);


rowsAffected = prepareStatement.executeUpdate();
} catch (SQLException ex) {
System.err.println(ex.getMessage());
}
return rowsAffected;
}

Let’s take a closer look at what’s going on in each part of this code:

  • First, we create a SQL statement called SQLupdate that will update the carname of a particular car in the tblcars table.

  • Then, we set our connection using the dbconnect() function after we create a PreparedStatement object.

  • Next, we use our prepareStatement to pass the new value of the carname.

  • We then execute our UPDATE statement by calling the executeUpdata() method.

  • Finally, the Connection and PreparedStatement objects will be closed using the try-catch block.

Let’s test our application one more time by modifying our main() function with the following code:

JdbcDemo app = new JdbcDemo();
// the below code will update 1 record within the table tblcars
// having an id of 1 and will change the carname field with new value "expander"
app.updateCarname(1, "expander");

We can run a quick SELECT in our PostgreSQL shell to see if we successfully updated the target record:

cars=# SELECT * FROM tblcars;
id | carname | brand
----+-----------+------------
2 | wigo | toyota
3 | ertiga | suzuki
4 | city | honda
5 | Adventure | Mitsubishi
1 | expander | toyota
(5 ROWS)

We can see that the record with an id of “1” was updated with the new name “expander”.

Conclusion

When you’re working with PostgreSQL, it’s important to know how to modify existing data in tables. Fortunately, it’s easy to make these changes to data using the UPDATE statement. In this tutorial, we showed you how to build a Java application that connects JDBC to PostgreSQL and then updates a record in a PostgreSQL database. With this sample application as a guide, you’ll be able to write code that connects JDBC to PostgreSQL and performs operations against your own database tables.

The Code

Throughout this tutorial, we examined our Java code one section at a time. Shown below is the Java application in its entirety:

package com.pgjdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo {


private final String conUrl = "jdbc:postgresql://localhost/cars";
private final String username = "postgres";
private final String password = "1234";

public Connection dbconnect() {
Connection dbconnect = null;

try {
dbconnect = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}

return dbconnect;
}


public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
app.updateCarname(1, "expander");
}


public int updateCarname(int id, String carname) {
String SQLupdate = "UPDATE tblcars "
+ "SET carname = ? "
+ "WHERE id = ?";


int rowsAffected = 0;

try (Connection conn = dbconnect();

PreparedStatement prepareStatement = conn.prepareStatement(SQLupdate)){
prepareStatement.setString(1, carname);
prepareStatement.setInt(2, id);


rowsAffected = prepareStatement.executeUpdate();
} catch (SQLException ex) {
System.err.println(ex.getMessage());
}
return rowsAffected;
}
}
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.