JAVA连接Access数据库

Springboot项目连接MC Access数据库

相关依赖

使用ucanaccess驱动连接access数据库，一下是相关依赖（由于网络原因，我是直接将jar下载至本地直接加载，也可直接从maven中心库拉取）

 		<dependency>
            <groupId>net.sf.sccloud</groupId>
            <artifactId>ucanaccess</artifactId>
            <version>5.0.1</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/ucanaccess-5.0.1.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>com.healthmarketscience.jackcess</groupId>
            <artifactId>jackcess</artifactId>
            <version>3.0.1</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/jackcess-3.0.1.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>com.healthmarketscience.jackcess</groupId>
            <artifactId>jackcess-encrypt</artifactId>
            <version>3.0.0</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/lib/jackcess-encrypt-3.0.0.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>org.bouncycastle</groupId>
            <artifactId>bcprov-jdk15on</artifactId>
            <version>1.68</version>
        </dependency>

配置

spring.datasource.driver-class-name=net.ucanaccess.jdbc.UcanaccessDriver
spring.datasource.url=jdbc:ucanaccess://D:/test.accdb;charset=utf-8
spring.datasource.password=xxxxxxxx
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.pool-size=30

遇到问题：

连接无密码的access数据库可以直接访问，但是一旦数据库设置了密码就报错

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 Decoding not supported. Please choose a CodecProvider which supports reading the current database encoding.

提示字符集问题

解决办法

1. 需要一个CryptCodecOpener（一个加密解密的扩展库，用于打开受加密保护的 Microsoft Access 数据库），项目示例地址

2. 自定义一个相关实现类

package com.example.demo;

import java.io.File;
import java.io.IOException;

import com.healthmarketscience.jackcess.CryptCodecProvider;  
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;

import net.ucanaccess.jdbc.JackcessOpenerInterface;

public class CryptCodecOpener implements JackcessOpenerInterface {
	public Database open(File fl, String pwd) throws IOException {
		DatabaseBuilder dbd = new DatabaseBuilder(fl);
		dbd.setAutoSync(false);
		dbd.setCodecProvider(new CryptCodecProvider(pwd));
		dbd.setReadOnly(false);
		return dbd.open();
	}
}

3. 修改配置

spring.datasource.driver-class-name = net.ucanaccess.jdbc.UcanaccessDriver
spring.datasource.url = jdbc:ucanaccess://D:/test.accdb;openExclusive=false;ignoreCase;jackcessOpener=com.example.demo.CryptCodecOpener
spring.datasource.password = xxx

4. 完美解决！

参考文献

https://stackoverflow.com/questions/59468649/ucaexc3-0-7-decoding-not-supported-please-choose-a-codecprovider-which-suppo