JDBC to PostgreSQL - How to Update a Record in PostgreSQL using the Java JDBC Driver
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:
1 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | INSERT INTO tblcars(carname,brand) VALUES ('fortuner', 'toyota'), ('wigo','toyota'), ('ertiga','suzuki'), ('city','honda'); |
The output from this operation will look like the following:
1 2 3 4 5 6 7 | 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’.
1 2 3 4 5 6 7 8 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | 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:
1 2 3 4 5 |
The result will look something like this:
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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 thecarname
of a particular car in thetblcars
table.Then, we set our connection using the
dbconnect()
function after we create aPreparedStatement
object.Next, we use our
prepareStatement
to pass the new value of thecarname
.We then execute our
UPDATE
statement by calling theexecuteUpdata()
method.Finally, the
Connection
andPreparedStatement
objects will be closed using thetry-catch
block.
Let’s test our application one more time by modifying our main()
function with the following code:
1 2 3 4 | 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:
1 2 3 4 5 6 7 8 9 | 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:
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 | 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; } } |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started