Mdb文件工具类，UCanAccess使用，Access数据库操作

 

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

©copyright 蕃薯耀 2020-01-09

 

使用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("[e-mail]");
        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