java操作access数据库

最新推荐文章于 2025-05-14 00:15:00 发布

码农-文若书生

最新推荐文章于 2025-05-14 00:15:00 发布

阅读量817

点赞数

CC 4.0 BY-SA版权

文章标签： java ucanaccess access

本文链接：https://blog.csdn.net/u011943534/article/details/130676527

文章介绍了如何在Java项目中通过UCanAccess库来操作Access数据库，包括引入Gradle或Maven依赖，创建表，插入数据以及使用JDBC进行连接和查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、引入依赖

gradle:

implementation 'net.sf.ucanaccess:ucanaccess:5.0.1'

maven:

<dependency>
    <groupId>net.sf.ucanaccess</groupId>
    <artifactId>ucanaccess</artifactId>
    <version>5.0.1</version>
</dependency>

二、测试

1.创建表

    /**
     * 测试创建表
     * */
    @Test
    public void testCreateTable() throws SQLException, IOException, ClassNotFoundException {
        //这里同样支持mdb和accdb
        Database db = DatabaseBuilder.create(Database.FileFormat.V2000, new File("d:/tmp/new.mdb"));
        Table newTable;

        //刚才是创建文件，这里是在文件里创建表，字段名，字段类型
        newTable = new TableBuilder("test").addColumn(new ColumnBuilder("c1").setSQLType(Types.VARCHAR))
                .addColumn(new ColumnBuilder("c2").setSQLType(Types.VARCHAR))
                .addColumn(new ColumnBuilder("c3").setSQLType(Types.VARCHAR))
                .addColumn(new ColumnBuilder("c4").setSQLType(Types.INTEGER))
                .addColumn(new ColumnBuilder("c5").setSQLType(Types.VARCHAR))
                .addColumn(new ColumnBuilder("c6").setSQLType(Types.INTEGER))
                .toTable(db);
        //插入一条数据测试
        newTable.addRow("12", "foo", "212", 44, "323", 56);
    }

2.插入数据

    /**
     * 测试向表中插入值
     * */
    @Test
    public void testInsert() {
        try {
            Database dbin = DatabaseBuilder.open(new File("d:/tmp/new.mdb"));
            Table table = dbin.getTable("test");
            table.addRow("1", 1, "2", "3");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

3.使用jdbc

jdbc工具类：

public class AccessJdbcUtils {
    private static final String DRIVER_CLASS_NAME = "net.ucanaccess.jdbc.UcanaccessDriver";
    private AccessJdbcUtils() {}

    public static Connection getConnection(String path, String username, String password) throws ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.setProperty("user", username);
        properties.setProperty("password", password);
        return getConnection(path, properties);
    }

    public static Connection getConnection(String path, Properties properties) throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER_CLASS_NAME);
        return DriverManager.getConnection(getUrl(path), properties);
    }

    public static Statement createStatement(Connection conn) throws SQLException {
        return conn.createStatement();
    }

    public static PreparedStatement createPrepareStatement(Connection conn, String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    public static ResultSet query(Connection conn, String sql) throws SQLException {
        Statement statement = createStatement(conn);
        return statement.executeQuery(sql);
    }

    public static ResultSet query(Connection conn, String sql, List<Object> params) throws SQLException {
        PreparedStatement statement = createPrepareStatement(conn, sql);
        if (params != null) {
            for (int i = 0; i < params.size(); i++) {
                statement.setObject(i + 1, params.get(i));
            }
        }
        return statement.executeQuery();
    }

    public static void closeConn(Connection conn) throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    public static void closeRs(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }

    public static void closeStatement(Statement statement) throws SQLException {
        if (statement != null) {
            statement.close();
        }
    }

    public static void closeRsAndStatement(ResultSet rs) throws SQLException {
        if (rs != null) {
            Statement statement = rs.getStatement();
            rs.close();
            statement.close();
        }
    }

    private static String getUrl(String path) {
        return "jdbc:ucanaccess://" + path;
    }
}