java连接Access数据库

 

 

package com.ziyun.cms.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class AccessUtil {

    public static final String MDB_FILE_PATH_BASE = "d:\\202104.mdb";
    public static final String SQL_BASE = "select 图号,流水号,检测结果,检测时间,检测人,检测错误信息,检测设置  from test";

    public static void main(String[] args) throws Exception {
        String sql = SQL_BASE + " where 检测时间>=' 2021-04-29 19:00:00' ";
        AccessUtil.query(MDB_FILE_PATH_BASE, sql);
//        testConnect(MDB_FILE_PATH_BASE);
    }

    public static void query(String mdbFilePath, String sql) throws Exception {
        // 定义需要的对象
        PreparedStatement ps = null;
        Connection ct = null;
        ResultSet rs = null;

        int count = 0;

        try {
            Class.forName("com.hxtt.sql.access.AccessDriver");

            Properties prop = new Properties();
            prop.put("charSet", "GBK");
            ct = DriverManager.getConnection("jdbc:Access:///" + mdbFilePath, prop);

            // 创建ps，创建数据库
            ps = ct.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(
                        rs.getString(1) + "   " + rs.getString(2) + "   " + rs.getString(3) + "   " + rs.getString(4)
                                + "   " + rs.getString(5) + "   " + rs.getString(6) + "   " + rs.getString(7));
                count++;
            }
            log.info("总行数：{}", count);

        } catch (Exception e) {
            e.printStackTrace();
            log.error("Access数据库连接失败：{}", e);
            throw new Exception(e);
        } finally {
            // 关闭资源！！！！！
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (ct != null) {
                try {
                    ct.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }
    }

    public static boolean testConnect(String mdbFilePath) {
        // 定义需要的对象
        PreparedStatement ps = null;
        Connection ct = null;
        try {
            Class.forName("com.hxtt.sql.access.AccessDriver");
            Properties prop = new Properties();
            prop.put("charSet", "GBK");
            ct = DriverManager.getConnection("jdbc:Access:///" + mdbFilePath, prop);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("Access数据库连接失败：{}", e);
            return false;
        } finally {
            // 关闭资源！！！！！
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (ct != null) {
                try {
                    ct.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

}