Summary: in this tutorial, you will learn how to update data in a PostgreSQL database using JDBC API.
To update data in a table in a PostgreSQL database, you use these steps:
- Create a database connection.
- Create a PreparedStatement object.
- Execute the UPDATE statement by calling the
executeUpdate()method of thePreparedStatementobject. - Close the database connection.
Creating a database connection
To create a PostgreSQL database connection from a Java program, you need to have the PostgreSQL JDBC driver. Check it out how to connect to a PostgreSQL database for the detailed information.
The following connect() method establishes a connection to the dvdrental sample database and returns a Connection object.
1 2 3 4 5 6 7 8 9 10 11 12 | private String url = "jdbc:postgresql://localhost/dvdrental"; private String user = "postgres"; private String password = "postgres"; /** * Connect to the PostgreSQL database * * @return a Connection object */ public Connection connect() throws SQLException { return DriverManager.getConnection(url, user, password); } |
The following updateLastName() method updates the last name of an actor in the actor table.
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 | /** * Update actor's last name based on actor's id * * @param id * @param lastName * @return the number of affected rows */ public int updateLastName(int id, String lastName) { String SQL = "UPDATE actor " + "SET last_name = ? " + "WHERE actor_id = ?"; int affectedrows = 0; try (Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL)) { pstmt.setString(1, lastName); pstmt.setInt(2, id); affectedrows = pstmt.executeUpdate(); } catch (SQLException ex) { System.out.println(ex.getMessage()); } return affectedrows; } |
How it works.
- First, prepare an
UPDATEstatement to update the last name of an actor in theactortable. - Next, establish a database connection and create a
PreparedStatementobject. - Then, pass the new last name of the actor and the id of the actor that is being updated by calling the
setString()andsetInt()methods of thePreparedStatementobject. - After that, call the
executeUpdate()method to execute theUPDATEstatement. The method returns the number of row affected. - Finally, because we use the try-with-resources statement, therefore the
PreparedStatementandConnectionobjects are automatically closed.
The following is the complete update 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 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 | package com.postgresqltutorial; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * * @author postgresqltutorial.com */ public class Main { private String url = "jdbc:postgresql://localhost/dvdrental"; private String user = "postgres"; private String password = "postgres"; /** * Connect to the PostgreSQL database * * @return a Connection object */ public Connection connect() throws SQLException { return DriverManager.getConnection(url, user, password); } /** * Update actor's last name based on actor's id * * @param id * @param lastName * @return the number of affected rows */ public int updateLastName(int id, String lastName) { String SQL = "UPDATE actor " + "SET last_name = ? " + "WHERE actor_id = ?"; int affectedrows = 0; try (Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL)) { pstmt.setString(1, lastName); pstmt.setInt(2, id); affectedrows = pstmt.executeUpdate(); } catch (SQLException ex) { System.out.println(ex.getMessage()); } return affectedrows; } /** * @param args the command line arguments */ public static void main(String[] args) { Main main = new Main(); main.updateLastName(200, "Climo"); } } |
Before running the program, we check the actor with id 200.
1 2 3 4 5 6 | SELECT * FROM actor WHERE actor_id = 200; |
Now, we run the Java program to update he last name of Thora from Temple to Climo.
And we check the actor with id again.
As you see the values in the last_name column changed to Climo as we expected.
Because the last_update column is automatically updated to the time when the row changes so that its value changes as well.
In this tutorial, we have shown you step by step how to update data in a table using JDBC API.