java访问mdb文件隐藏表报错解决办法:
UCAExc:::5.0.1 user lacks privilege or object not found: MSYSOBJECTS
在地址url中添加;sysSchema=true
在sql语句中添加sys.MSysObjects 写法
在地址url中添加;sysSchema=true
在sql语句中添加sys.MSysObjects 写法
数据库访问方式:
public static DruidContext getDruidContextByMdb(String mdbPath)
{
DbConfig mdbCfg=new DbConfig();
mdbCfg.setDbType("mdb");
//mdbCfg.setUrl("jdbc:odbc:Driver={MicroSoft..Access..Driver(*.mdb)};DBQ="+mdbPath);
mdbCfg.setUrl("jdbc:ucanaccess://"+mdbPath+";sysSchema=true");
mdbCfg.setUsername("");
mdbCfg.setPassword("");
return getDruidContext(mdbCfg);
}
private List<String> getTableNameList(DruidContext db)
{
String sql="select b.name from sys.MSysObjects a inner join sys.MSysObjects b ";
sql+=" on a.Id=b.ParentId ";
sql+=" where a.Name='Tables' and b.Flags=0";
//
return db.queryForList(sql);
}
public void readFileWriteData(String mdbPath)
{
DruidContext db=DruidFactory.getDruidContextByMdb(mdbPath);
List<String> tableList=this.getTableNameList(db);
String sql="select top 100 * from nh";
SqlRowSet row=db.queryForRowSet(sql);
row.first();
while(row.next())
{
System.out.println(row.getString("nhdm"));
}
//List<Map<String,Object>> mapList=db.getDataTable("select top 100 * from nh");
//
for(String table :tableList)
{
//
}
}
pom.xml引入包
<dependency>
<groupId>net.sf.ucanaccess</groupId>
<artifactId>ucanaccess</artifactId>
<version>5.0.1</version>
</dependency>
Druid初始化为JdbcTemplate模板的方法
public static final String driverClassName_mdb="net.ucanaccess.jdbc.UcanaccessDriver";
public DruidContext initDataStore2(DbConfig cds) {
if (this.dataSource == null) {
try {
lock.writeLock().lock();
if (this.dataSource == null) {
//数据源配置
Properties prop = new Properties();
switch (cds.getDbType()) {
case "postgis":
prop.put("driverClassName", driverClassName_postgresql);
break;
case "postgresql":
prop.put("driverClassName", driverClassName_postgresql);
break;
case "mysql":
prop.put("driverClassName", driverClassName_mysql);
break;
case "oracle":
prop.put("driverClassName", driverClassName_oracle);
break;
case "sqlserver":
prop.put("driverClassName", driverClassName_sqlserver);
break;
case "dm":
prop.put("driverClassName", driverClassName_dm);
break;
case "mdb":
prop.put("driverClassName", driverClassName_mdb);
prop.put("hsqldb.method_class_names","net.ucanaccess.*");
break;
default:
prop.put("driverClassName", driverClassName_postgresql);
break;
}
prop.put("url", cds.getUrl());
prop.put("username", cds.getUsername());
prop.put("password", cds.getPassword());
//
prop.put("initialSize", "2");
prop.put("maxActive", "20");
prop.put("maxWait", "10000");
//返回的是DataSource
this.dataSource = DruidDataSourceFactory.createDataSource(prop);
this.jdbcTemplate = new JdbcTemplate(this.dataSource);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
lock.writeLock().unlock();
}
}
return this;
}
本blog地址:http://blog.csdn.net/hsg77