Java 连接Access数据库

Java 连接Access数据库

maven中央仓库下载UCanAccess驱动包

<!-- https://mvnrepository.com/artifact/net.sf.ucanaccess/ucanaccess -->
<dependency>
    <groupId>net.sf.ucanaccess</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.0.1</version>
</dependency>

如果引用不成功，则下载对应的jar包，手动安装到maven

mvn install:install-file -Dfile=jar包的位置(参数一) -DgroupId=groupId(参数二) -DartifactId=artifactId(参数三) -Dversion=version(参数四) -Dpackaging=jar

UCanAccess连接

//dbURL是mdb文件路径
public static Connection getConn(String dbURL) {
        try {
            //加载驱动
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            //建立连接
			return DriverManager.getConnection("jdbc:ucanaccess://" + dbURL);
        } catch (Exception e) {
            System.out.println("AccessDB connection fail");
            e.printStackTrace();
        }
        return null;
    }

查询

/**
     * 通过反射机制查询多条记录
     *
     * @param sql    sql
     * @param params 参数
     * @param clazz  类
     * @return 查询结果
     */
    public static <T> List<T> select(String sql, List<Object> params,
                                     Class<T> clazz, Connection conn) throws Exception {
        List<T> list = new ArrayList<T>();
        int index = 1;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            assert conn != null;
            ps = conn.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (Object param : params) {
                    ps.setObject(index++, param);
                }
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int cols_len = metaData.getColumnCount();
            T t;
            boolean isNull = false;
            while (rs.next()) {
                //通过反射机制创建一个实例
                t = clazz.newInstance();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnName(i + 1);
                    Object cols_value = rs.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    if (Arrays.stream(clazz.getDeclaredFields()).anyMatch(a -> a.getName().equals(cols_name))) {
                        Field field = clazz.getDeclaredField(cols_name);//获取对象属性
                        field.setAccessible(true); //打开javabean的访问权限
                        String type = field.getGenericType().toString();
                        if(cols_value != null && cols_value != ""){
                            isNull = true;
                            if (type.equals("class java.lang.String")) {
                                field.set(t, String.valueOf(cols_value));
                            }
                            if (type.equals("class java.lang.Double")) {
                                field.set(t, Double.parseDouble(String.valueOf(cols_value)));
                            }
                            if (type.equals("class java.lang.Integer")) {
                                field.set(t, Integer.parseInt(String.valueOf(cols_value)));
                            }
                            if (type.equals("class java.sql.Timestamp")) {
                                field.set(t, Timestamp.valueOf(String.valueOf(cols_value)));
                            }
                        }
                    }
                }
                if(isNull) list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            close(conn, ps, rs);
        }
        return list;
    }