UCanAccess is a pure Java JDBC Driver implementation which allows Java developers and JDBC client programs to read and write Microsoft Access database files (.mdb
and .accdb
).
In this tutorial, we will demonstrate how to configure a project with UCanAccess and create a simple Java application to select data from an MS Access database.
Establishing a Connection
Before you can interact with a database, you need to establish a connection to it. The following is an example of how to establish a connection using UCanAccess:
String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
try (Connection connection = DriverManager.getConnection(url)) {
System.out.println("connection = " + connection);
// Rest of the code goes here...
} catch (SQLException e) {
e.printStackTrace();
}
In the code above, we create a connection URL using the absolute path to our Access database file. Then, we obtain a connection
object by calling DriverManager.getConnection(url)
.
Fetching Data from the Database
Once the connection
is established, we can execute SQL queries against the database. Here, let’s create a query
to select some data:
String query = "select id, title, release_date from album where id = ?";
PreparedStatement ps = connection.prepareStatement(query);
ps.setLong(1, 1L);
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"));
}
In this code, we create a PreparedStatement
object, which enables us to execute parameterized SQL queries in a secure and efficient manner. Here, our SQL query includes a parameter, represented by the ?
symbol, which we then set using setLong()
. This would replace the ?
with the value 1L
.
We then execute our query
by calling executeQuery()
on the PreparedStatement
object which returns a ResultSet
object. This object represents the result set of the query.
We then loop through the result set and print each record using rs.next()
, which is used to iterate through the ResultSet
.
Full Code
package org.kodejava.jdbc;
import java.sql.*;
public class MSAccessSelect {
public static void main(String[] args) {
String url = "jdbc:ucanaccess://C:/Users/wayan/Temp/musicdb.accdb;";
try (Connection connection = DriverManager.getConnection(url)) {
System.out.println("connection = " + connection);
String query = "select id, title, release_date from album where id = ?";
PreparedStatement ps = connection.prepareStatement(query);
ps.setLong(1, 1L);
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"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Remember to handle SQL exceptions that might be thrown during the interaction with the database. And there you have it! Now you know how to work with MS Access Database using UCanAccess in Java. Happy coding!
Maven Dependencies
<dependency>
<groupId>io.github.spannm</groupId>
<artifactId>ucanaccess</artifactId>
<version>5.1.1</version>
</dependency>