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>
