How do I insert a record into Microsoft Access database and get the generated key?

By Wayan in JDBC, Microsoft Access 0 Comment

In this tutorial, we’ll guide you through the process of inserting records into an MS Access Database. In the code snippet below we are using the UCanAccess JDBC driver, you can find the Maven dependencies at the end of this tutorial.

Setting Up The Connection

Firstly, we need to set up a connection to our MS Access database. This is done by specifying the path of your database file (.mdb or .accdb) in the JDBC URL and then getting a connection from DriverManager.

String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
try (Connection connection = DriverManager.getConnection(url)) {
    ...
} catch (SQLException e) {
    e.printStackTrace();
}

Preparing the SQL Statement

Next step is to prepare our SQL insert statement. We use a PreparedStatement – it handles the tedious task of substituting the respective parameters into the placeholders ("?") in our SQL statement.

String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Setting Parameters and Executing the SQL Statement

After defining our SQL statement with placeholders, we replace these placeholders with actual values using the setter methods (setInt(), setString(), setDate(), etc.) provided by the PreparedStatement class. Once, the placeholders have been replaced with actual values, we call executeUpdate() method on the PreparedStatement object to execute the SQL statement.

ps.setString(1, "With the Beatles");
ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
int rowCount = ps.executeUpdate();
System.out.println("rowCount = " + rowCount);

In the code snippet above we set all placeholders with values we want to insert into the database, and then the statement is executed. The new record gets added to the database. We also keep the rowCount returned by the executeUpdate() method.

Get the Generated Key Value

When creating the PreparedStatement object we also passes the Statement.RETURN_GENERATED_KEYS parameter, this will allow us to get the generated key from the statement object after executing the query.

ResultSet generatedKeys = ps.getGeneratedKeys();
long generatedKey = 1L;
while (generatedKeys.next()) {
    generatedKey = generatedKeys.getLong(1);
}
System.out.println("Generated Key = " + generatedKey);

The Entire Code Snippet

Here is the entire Java class.

package org.kodejava.jdbc;

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

public class MSAccessInsert {
    public static void main(String[] args) {
        String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb";
        try (Connection connection = DriverManager.getConnection(url)) {
            String sql = "insert into album (title, release_date, created) values (?, ?, ?)";

            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, "With the Beatles");
            ps.setDate(2, Date.valueOf(LocalDate.of(1963, Month.NOVEMBER, 22)));
            ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));

            int rowCount = ps.executeUpdate();
            System.out.println("rowCount = " + rowCount);

            ResultSet generatedKeys = ps.getGeneratedKeys();
            long generatedKey = 1L;
            while (generatedKeys.next()) {
                generatedKey = generatedKeys.getLong(1);
            }
            System.out.println("Generated Key = " + generatedKey);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The code above inserts a new record into the album table in the Access database. Remember to handle any SQLException that may be thrown while interacting with the database.

And that’s it! You have successfully inserted a record into an MS Access database using UCanAccess in Java. Whether you’re creating a small application or dealing with large-scale data management, UCanAccess is an easy, efficient way to work with MS Access databases in Java applications.

Maven Dependencies

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

Maven Central

How do I retrieve auto-generated keys?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

import java.sql.*;

public class GetGeneratedKeyExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // The orders table have an ID column which value will be auto
            // generated by database. When inserting a new record a new id
            // for the primary key will be generated, and we will get the
            // generated key, so we can use it in another process. For
            // instance if we have a master detail tables where the details
            // table required an id from the master table.
            String sql = """
                    INSERT INTO purchase_order (username, order_date)
                    VALUES ('foobar', '2021-09-25')
                    """;

            Statement stmt = connection.createStatement();

            // When executing the statement we can pass the
            // Statement.RETURN_GENERATED_KEYS so that we can later extract
            // the generated key from the statement object after executing
            // the query.
            stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

            ResultSet keys = stmt.getGeneratedKeys();
            long lastKey = 1L;
            while (keys.next()) {
                lastKey = keys.getLong(1);
            }

            System.out.println("Last Key: " + lastKey);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The structure of purchase_order table.

CREATE TABLE `purchase_order`
(
    `id`         bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `username`   varchar(50)         NOT NULL,
    `order_date` date                NOT NULL,
    PRIMARY KEY (`id`)
);

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central