JAVA连接Access数据库
<div class="operating">
<a class="href-article-edit slide-toggle">版权</a>
</div>
</div>
</div>
</div>
<div id="blogColumnPayAdvert">
<div class="column-group">
<div class="column-group-item column-group0 column-group-item-one">
<div class="item-l">
<a class="item-target" href="https://blog.csdn.net/tubing1996/category_10723343.html" target="_blank" title="java" data-report-click="{"spm":"1001.2101.3001.6332"}">
<img class="item-target" src="https://img-blog.csdnimg.cn/20201014180756927.png?x-oss-process=image/resize,m_fixed,h_64,w_64" alt="">
<span class="title item-target">
<span>
<span class="tit">java</span>
<span class="dec">专栏收录该内容</span>
</span>
</span>
</a>
</div>
<div class="item-m">
<span>4 篇文章</span>
<span>0 订阅</span>
</div>
<div class="item-r">
<a class="item-target article-column-bt articleColumnFreeBt" data-id="10723343">订阅专栏</a>
</div>
</div>
</div>
</div>
<article class="baidu_pl">
<div id="article_content" class="article_content clearfix">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-044f2cf1dc.css">
<div id="content_views" class="markdown_views prism-atom-one-dark">
<svg xmlns="http://www.w3.org/2000/svg" style="display: none;">
<path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path>
</svg>
<h2><a name="t0"></a><a id="SpringbootMC_Access_0"></a>Springboot项目连接MC <a href="https://so.csdn.net/so/search?q=Access%E6%95%B0%E6%8D%AE%E5%BA%93&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=Access%E6%95%B0%E6%8D%AE%E5%BA%93&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"Access数据库\"}"}" data-tit="Access数据库" data-pretit="access数据库">Access数据库</a></h2>
相关依赖
使用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>
<span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>com.healthmarketscience.jackcess<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>jackcess<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>3.0.1<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>scope</span><span class="token punctuation">></span></span>system<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>scope</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>systemPath</span><span class="token punctuation">></span></span>${project.basedir}/lib/jackcess-3.0.1.jar<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>systemPath</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>com.healthmarketscience.jackcess<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>jackcess-encrypt<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>3.0.0<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>scope</span><span class="token punctuation">></span></span>system<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>scope</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>systemPath</span><span class="token punctuation">></span></span>${project.basedir}/lib/jackcess-encrypt-3.0.0.jar<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>systemPath</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>dependency</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>groupId</span><span class="token punctuation">></span></span>org.bouncycastle<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>groupId</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>artifactId</span><span class="token punctuation">></span></span>bcprov-jdk15on<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>artifactId</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"><</span>version</span><span class="token punctuation">></span></span>1.68<span class="token tag"><span class="token tag"><span class="token punctuation"></</span>version</span><span class="token punctuation">></span></span> <span class="token tag"><span class="token tag"><span class="token punctuation"></</span>dependency</span><span class="token punctuation">></span></span>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
配置
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
- 1
- 2
- 3
- 4
- 5
- 6
遇到问题:
连接无密码的access数据库可以直接访问,但是一旦数据库设置了密码就报错
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 Decoding not supported. Please choose a CodecProvider which supports reading the current database encoding.
提示字符集问题
解决办法
-
需要一个CryptCodecOpener(一个加密解密的扩展库,用于打开受加密保护的 Microsoft Access 数据库),项目示例地址
-
自定义一个相关实现类
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();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 修改配置
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
- 1
- 2
- 3
- 完美解决!
参考文献
https://stackoverflow.com/questions/59468649/ucaexc3-0-7-decoding-not-supported-please-choose-a-codecprovider-which-suppo