================================

©Copyright 蕃薯耀 2020-01-09

https://www.cnblogs.com/fanshuyao/

使用Ocbc连接是区分电脑是32位还是64位的，需要安装相应的驱动文件，不方便，所以采用第三方的Jar包（UCanAccess）

UCanAccess-4.0.4-bin.zip（自行搜索）

需要的Jar包：

ucanaccess-4.0.4.jar

在Lib文件的jar包：

commons-lang-2.6.jar

commons-logging-1.1.3.jar

hsqldb.jar

jackcess-2.1.11.jar

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.plan.commons.Row;
import com.plan.commons.RowImpl;

public class MdbUtils {

private static Logger log = Logger.getLogger(MdbUtils.class);  
//odbc方式区分32位和64位系统  
/\*  
private final static String JDBC\_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";  
private final static String JDBC\_URL = "jdbc:odbc:driver={Microsoft Access Driver (\*.mdb, \*.accdb)};DBQ=";  
\*/

//使用UCanAccess  
private final static String JDBC\_DRIVER = "net.ucanaccess.jdbc.UcanaccessDriver";  
private final static String JDBC\_URL = "jdbc:ucanaccess://";

public static void close(ResultSet resultSet, Statement statement, Connection connection){  
    try {  
        if(resultSet != null){  
            resultSet.close();  
            //log.info("关闭mdb resultSet连接。");  
            //System.out.println("关闭mdb resultSet连接。");  
        }  
        if(statement != null){  
            statement.close();  
            //log.info("关闭mdb statement连接。");  
            //System.out.println("关闭mdb statement连接。");  
        }  
        if(connection != null){  
            connection.close();  
            //log.info("关闭mdb connection连接。");  
            //System.out.println("关闭mdb connection连接。");  
        }  
    } catch (SQLException e) {  
        e.printStackTrace();  
        log.error("关闭mdb连接出错。" + e);  
    }  
}

/\*\*  
 \* mdb文件获取连接  
 \* @param absoluteFilePath  
 \* @return  
 \*/  
public static Connection getConn(String absoluteFilePath){  
    log.info("mdb文件路径absoluteFilePath=" + absoluteFilePath);

    Properties prop = new Properties();  
    prop.put("charset", "utf-8");//解决中文乱码（GB2312/GBK）  
    //prop.put("user", "");  
    //prop.put("password", "");

    String url = JDBC\_URL + absoluteFilePath;  
    Connection connection = null;  
    try {  
        connection = DriverManager.getConnection(url, prop);  
    } catch (SQLException e) {  
        e.printStackTrace();  
        log.info("mdb文件获取连接出错。Exception=" + e);  
    }  
    return connection;  
}

/\*\*  
 \* 查询mdb文件的表数据  
 \* @param absoluteFilePath mdb文件绝对路径  
 \* @param sql 查询的sql语句  
 \* @return  
 \*/  
public static List<Row> read(String absoluteFilePath, String sql){  
    log.info("mdb文件路径absoluteFilePath=" + absoluteFilePath);  
    log.info("mdb查询sql=" + sql);

    List<Row> rowList = new ArrayList<Row>();  
    Properties prop = new Properties();  
    prop.put("charset", "utf-8");//解决中文乱码（GB2312/GBK）  
    //prop.put("user", "");  
    //prop.put("password", "");

    String url = JDBC\_URL + absoluteFilePath;  
    //PreparedStatement preparedStatement = null;  
    Statement statement = null;  
    ResultSet resultSet = null;  
    Connection connection = null;  
    try{  
        Class.forName(JDBC\_DRIVER);  
        connection = DriverManager.getConnection(url, prop);  
        statement = connection.createStatement();  
        resultSet = statement.executeQuery(sql);  
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

        while(resultSet.next()){  
            Row row = new RowImpl();  
            for(int i=1; i<= resultSetMetaData.getColumnCount(); i++){  
                String columnName = resultSetMetaData.getColumnName(i);//列名  
                Object columnValue = resultSet.getObject(i);  
                row.addColumn(columnName, columnValue);  
            }  
            rowList.add(row);  
        }  
    }catch (Exception e) {  
        e.printStackTrace();  
        log.info("mdb文件读取sql出错。Exception=" + e);  
        throw new RuntimeException(e);  
    }finally{  
        close(resultSet, statement, connection);  
    }  
    return rowList;  
}

/\*\*  
 \* 查询mdb文件的表数据  
 \* @param file File  
 \* @param sql 查询的sql语句  
 \* @return  
 \*/  
public static List<Row> read(File file, String sql){  
    return read(file.getAbsolutePath(), sql);  
}

/\*\*  
 \* 更新mdb文件的表数据，返回更新的记录数量，0表示没有更新（ID不允许更新）  
 \* @param absoluteFilePath mdb文件绝对路径  
 \* @param sql 查询的sql语句  
 \* @return  
 \*/  
public static int update(String absoluteFilePath, String sql){  
    log.info("mdb文件绝对路径，absoluteFilePath=" + absoluteFilePath);  
    log.info("mdb文件更新，sql=" + sql);

    Properties prop = new Properties();  
    prop.put("charset", "utf-8");//解决中文乱码（GB2312/GBK）

    String url = JDBC\_URL + absoluteFilePath;  
    Statement statement = null;  
    Connection connection = null;  
    int updateSize = 0;  
    try{  
        Class.forName(JDBC\_DRIVER);  
        connection = DriverManager.getConnection(url, prop);  
        statement = connection.createStatement();  
        updateSize = statement.executeUpdate(sql);

    }catch (Exception e) {  
        e.printStackTrace();  
        log.info("mdb文件更新sql出错。Exception=" + e);  
        throw new RuntimeException(e);  
    }finally{  
        close(null, statement, connection);  
    }  
    log.info("mdb更新数量，updateSize=" + updateSize + "。sql="+sql);  
    return updateSize;  
}

/\*\*  
 \* 更新mdb文件的表数据，返回更新的记录数量，0表示没有更新  
 \* @param absoluteFilePath mdb文件绝对路径  
 \* @param sql 查询的sql语句  
 \* @return  
 \*/  
public static int update(String absoluteFilePath, String sql, List<Object> params){  
    log.info("mdb文件路径absoluteFilePath=" + absoluteFilePath);  
    log.info("mdb更新sql=" + sql);  
    Properties prop = new Properties();  
    prop.put("charset", "utf-8");//解决中文乱码（GB2312/GBK）

    String url = JDBC\_URL + absoluteFilePath;  
    PreparedStatement preparedStatement = null;  
    Connection connection = null;  
    int updateSize = 0;  
    try{  
        Class.forName(JDBC\_DRIVER);  
        connection = DriverManager.getConnection(url, prop);  
        preparedStatement = connection.prepareStatement(sql);  
        if(params != null && params.size() > 0){  
            for(int i=0; i<params.size(); i++){  
                preparedStatement.setObject(i + 1, params.get(i));  
            }  
        }  
        updateSize = preparedStatement.executeUpdate();

    }catch (Exception e) {  
        e.printStackTrace();  
        log.info("mdb文件更新sql出错。Exception=" + e);  
        throw new RuntimeException(e);  
    }finally{  
        close(null, preparedStatement, connection);  
    }  
    log.info("mdb更新数量，updateSize=" + updateSize + "。sql="+sql);  
    return updateSize;  
}

/\*\*  
 \* mdb文件sql执行（如新增、删除字段），成功返回true  
 \* @param absoluteFilePath mdb文件绝对路径  
 \* @param sql 查询的sql语句  
 \* @return  
 \*/  
public static boolean execute(String absoluteFilePath, String sql){  
    log.info("mdb文件绝对路径，absoluteFilePath=" + absoluteFilePath);  
    log.info("mdb文件sql执行，sql=" + sql);

    Properties prop = new Properties();  
    prop.put("charset", "utf-8");//解决中文乱码（GB2312/GBK）

    String url = JDBC\_URL + absoluteFilePath;  
    Statement statement = null;  
    Connection connection = null;  
    boolean result = false;  
    try{  
        Class.forName(JDBC\_DRIVER);  
        connection = DriverManager.getConnection(url, prop);  
        statement = connection.createStatement();  
        statement.execute(sql);  
        result = true;  
        log.info("mdb文件执行sql成功。sql=" + sql);  
    }catch (Exception e) {  
        e.printStackTrace();  
        log.info("mdb文件执行sql出错。Exception=" + e);  
        throw new RuntimeException(e);  
    }finally{  
        close(null, statement, connection);  
    }  
    return result;  
}

public static void main(String\[\] args) {  
    /\*  
    String sql = "select \* from cu\_proj\_zxgh\_land";  
    //List<Map<String, Object>> listMap = read("C:/db/test.mdb", sql);  
    List<Row> rowList = read("C:/db/02-地块划分与指标控制图.mdb", sql);  
    if(rowList != null && rowList.size() > 0){  
        System.out.println("=====listMap.size()="+rowList.size());  
        for (Row row : rowList) {  
            System.out.println(row.toString());  
            System.out.println("");  
        }  
    }  
    \*/

    /\*  
    //更新数据  
    String sql = "update t\_user set age=199 where id=1";  
    System.out.println(update("C:/db/test.mdb", sql));  
    \*/

    //preparedStatement  
    /\*  
    String sql = "update t\_user set age=?,email=? where id=?";  
    List<Object> params = new ArrayList<Object>();  
    params.add(99);  
    params.add("bbb@qq.com");  
    params.add(1);  
    System.out.println(update("C:/db/test.mdb", sql, params));  
    \*/

    //增加列  
    /\*  
    String sql = "alter table t\_user add column gh\_id int";  
    //String sql = "alter table t\_user add column my\_id datetime not null default now()";  
    System.out.println(execute("C:/db/test.mdb", sql));  
    \*/  
}

}

（如果你觉得文章对你有帮助，欢迎捐赠，^_^，谢谢！）

================================

©Copyright 蕃薯耀 2020-01-09

https://www.cnblogs.com/fanshuyao/