How do I update records in MS Access database?

In this post, we will learn how to update records in a database using the Java Database Connectivity (JDBC) API. JDBC is a Java API which is used to connect and execute query in the database.

We will be working with an MS Access database file named musicdb.accdb for this example. Our goal is to update an album’s title and release date in our album table.

The following code snippet show you how to do it. The selectAlbum() method is just a helper method to show the album data before and after the data was updated.

package org.kodejava.jdbc;

import java.sql.*;
import java.time.LocalDate;
import java.time.Month;

public class MSAccessUpdate {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection conn = DriverManager.getConnection(url)) {
            conn.setAutoCommit(false);
            selectAlbum(conn, 2L);

            String sql = """
                    update album
                        set title = ?,
                        release_date = ?
                    where id = ?
                    """;

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "A Hard Day's Night");
            ps.setDate(2, Date.valueOf(LocalDate.of(1964, Month.JULY, 10)));
            ps.setLong(3, 2);

            int rowCount = ps.executeUpdate();
            System.out.printf("%s (rows) updated.%n", rowCount);

            selectAlbum(conn, 2L);

            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Executes a SQL query to select an album from the database based on the
     * provided ID.
     *
     * @param conn the Connection object representing the database connection
     * @param id   the ID of the album to be selected
     * @throws SQLException if a database access error occurs or the SQL query
     * is invalid
     */
    private static void selectAlbum(Connection conn, Long id) throws SQLException {
        String sql = "select * from album where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, id);

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.printf("id=%s, title=%s, released_date=%s%n",
                    rs.getLong("id"), rs.getString("title"),
                    rs.getDate("release_date"));
        }
    }
}

The following is an output produced by the code snippet above:

id=2, title=With the Beatles, released_date=1963-11-22
1 (rows) updated.
id=2, title=A Hard Day's Night, released_date=1964-07-10

Maven Dependencies

<dependency>
    <groupId>io.github.spannm</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.1.1</version>
</dependency>

Author
Recent Posts

Wayan

Founder at Kode Java

A programmer, runner and recreational diver, lives on the island of Bali, Indonesia. Programming in Java, Spring Framework, Hibernate / JPA, MySQL. To support me working on this project, buy me a cup of coffee, every little bit helps. Thank you.

Leave a ReplyCancel reply