Connect to Access database with Java [UCan Access] * Set until it can be executed with VS Code

Purpose

Access the MS Access database in Java. At that time, it seems better to use a JDBC driver called UCanAccess, so I will introduce it.

Also, since I am using VS Code in Java development, I will set it until it can be executed here.

Prerequisites

The following environment is assumed.

• JDK 15 (at least 8)
• MS Access 2016
• VSCode

If you don't develop with VS Code, VS Code is not required. When developing with VS Code, it is necessary to make settings that allow Java development with VS Code. Java development is explained separately in VS Code, so please refer to here. Introduction of Java 15 and VS Code environment settings

Introduction of UCanAccess

UCanAccess is a type of JDBC for connecting to Access.

** What is JDBC ** A library for Java to connect to a database and is included with the JDK. By adding an external library such as UCanAccess this time, you can connect to database products such as Access and MySQL. External libraries exist for each product.

Download UCanAccess

Download from here. https://sourceforge.net/projects/ucanaccess/files/

Select "Download Latest Version" to download UCanAccess-5.0.0-bin.zip.

UCanAccess folder layout

Unzip the downloaded zip. Then move the entire folder to an easy-to-understand location. This time, I placed it in C: \ Program Files \ UCanAccess-5.0.0-bin.

Access settings

Next, enter the Access settings. Open Access and select "Options". Open the option General. Make sure that the order of the new database is "Japanese-Legacy" like this. If not, please change it.

Access settings are now complete.

Create sample database

Create a database to connect from Java. I created it like this.

Also, remember the path to the files in this database. This time, I saved it in the following path. C:\Users\kazus\Documents\sample.accdb

Test with the included console tool

Now, UCanAccess comes with a console tool. This is a tool that allows you to connect to Access via the CLI.

To run it, run console.bat in the UCanAccess folder. Then a command prompt will be launched, and you can throw SQL after that by entering the full path of the Access database here. let's do it. You can operate the database like this!

• Press Ctrl + C to exit.

Used in VS Code Java projects

Now that I know that UCanAccess can be used with console tools, it's time to get into VS Code settings.

Create a Java project

First, create a Java project on VS Code. You can create it with Java: Create Java Project ... from Ctrl + Shift + P.

coding

Let's write the code to connect to the Access database.

App.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class App {
    public static void main(String[] args) throws Exception {
        try {
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://C:/Users/kazus/Documents/sample.accdb");
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from users");
            while (rs.next()) {
                System.out.println(rs.getString("user_name"));
            }
            rs.close();
            st.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println("error");
        }
    }
}

Added UCanAccess library to Java project

Add the UCanAccess .jar to your project's Referenced Libraries. You can add from + like this. The .jar to be added is as follows.

• UCanaccess-5.0.0.jar
• The following 4 under lib /
  • commons-lang3-3.8.1.jar
  • commons-logging-1.2.jar
  • hsqldb-2.5.0.jar
  • jackcess-3.0.1.jar

Please restart VS Code just in case you add it.

Execute

Let's execute with ▶. I was able to do it like this!