欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-18 14:08:58
©Copyright 蕃薯耀 2020-01-09 https://www.cnblogs.com/fanshuyao/ 使用Ocbc连接是区分电脑是32位还是64位的,需要安装相应的驱动文件,不方便,所以采用第三方的Jar包(UCanAccess) UCanAccess-4.0.4-bin.zip ......

 

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

©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("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));
        */
    }
    
    
}

 

 

(如果你觉得文章对你有帮助,欢迎捐赠,^_^,谢谢!) 

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

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

©copyright 蕃薯耀 2020-01-09