Java баз данных access

Java JDBC Example Connect to Microsoft Access Database

This JDBC tutorial guides you how to develop a Java program that connects to a Microsoft Access Database. In the early days of JDBC, you can connect to an Access database via JDBC ODBC driver provided by JDK. However JDBC ODBC driver is no longer supported so you need to use a third-party JDBC driver for Microsoft Access. And your Java code still uses JDBC API as normal.

1. Java JDBC Driver for Microsoft Access Database

There are several third-party JDBC drivers out there for Microsoft Access database, and we recommend UCanAccess — a pure Java JDBC Driver for Access that allows Java developers and JDBC client programs to read/write Microsoft Access databases. UCanAccess supports various Access formats: 2000, 2002/2003, 2007, 2010/2013/2016 (Access 97 is supported for read-only).

UCanAccess is open-source and implemented entirely in Java so it can be used across platforms (Windows, Mac, Linux…). It also provides Maven dependencies so you can integrate it in your existing projects quickly.

To use UCanAccess JDBC Driver for Access, add the following dependency information in your project’s pom.xml file:

 net.sf.ucanaccess ucanaccess 4.0.4 

In case you don’t use Maven, you have to download UCanAccess distribution and add the following JAR files to the classpath:

• ucanaccess-4.0.4.jar
• hsqldb-2.3.1.jar
• jackcess-2.1.11.jar
• commons-lang-2.6.jar
• commons-logging-1.1.3.jar

2. Java JDBC Example with Access Database

Suppose that we have an Access Database 2007 contains a table Contacts with the following fields:

The database file is located at e:\Java\JavaSE\MsAccess\Contacts.accdb . — This path will be used in database URL. We will write a Java program that uses the UCanAccess JDBC driver to connect to this database, insert a row and select all rows from the table Contacts.

You can use JDBC API as normal (see Connect to a database with JDBC). The differences lie in the database URL and Access-specific SQL syntax you can use. For example, you need to construct the database URL to include path of the Access database file like this:

String databaseURL = "jdbc:ucanaccess://e://Java//JavaSE//MsAccess//Contacts.accdb";

package net.codejava.jdbc; import java.sql.*; /** * This program demonstrates how to use UCanAccess JDBC driver to read/write * a Microsoft Access database. * @author www.codejava.net * */ public class JdbcAccessTest < public static void main(String[] args) < String databaseURL = "jdbc:ucanaccess://e://Java//JavaSE//MsAccess//Contacts.accdb"; try (Connection connection = DriverManager.getConnection(databaseURL)) < String sql = "INSERT INTO Contacts (Full_Name, Email, Phone) VALUES (?, ?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "Jim Rohn"); preparedStatement.setString(2, "rohnj@herbalife.com"); preparedStatement.setString(3, "0919989998"); int row = preparedStatement.executeUpdate(); if (row >0) < System.out.println("A row has been inserted successfully."); >sql = "SELECT * FROM Contacts"; Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery(sql); while (result.next()) < int fullname = result.getString("Full_Name"); String email = result.getString("Email"); String phone = result.getString("Phone"); System.out.println(id + ", " + fullname + ", " + email + ", " + phone); >> catch (SQLException ex) < ex.printStackTrace(); >> >

As you can see, this example looks like trivial JDBC code, the only difference lies in the database URL that needs to include path to the Access database file.

If you want to see the coding in action, I recommend to watch the video version of this article below:

References:

JDBC API References:

Related JDBC Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Add comment

Comments

No suitable driver found for jdbc:ucanaccess://C://users//gopal//Documents//gopal.accdb
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:706)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:252)
at prj.gopal.OracleResult1.main(OracleResult1.java:14 )
at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:251)
at JdbcAccessTest.main(JdbcAccessTest.java:18)

java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://ssdc.accdb
at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:702)
at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:251)
at JdbcAccessTest.main(JdbcAccessTest.java:18)

Источник

Java баз данных access

Jackcess

• OpenHMS
• Welcome
• Jackcess
• About
• Downloads
• SourceForge Project
• Cookbook
• Upgrading to Jackcess 4.x
• Upgrading to Older Versions
• FAQ
• Support
• Jackcess Encrypt
• GitHub Mirror
• Project Documentation
• Project Information
• Project Reports

Jackcess

Jackcess is a pure Java library for reading from and writing to MS Access databases (currently supporting versions 2000-2019). It is not an application. There is no GUI. It’s a library, intended for other developers to use to build Java applications. Jackcess is licensed under the Apache License (as of version 2.1.0) and currently requires Java 8+ (as of the 3.0.0 release) Take a look at our Frequently Asked Questions for more info.

Java 9+ Compatibility (2021-01-20)

While Jackcess still only requires Java 8+, as of the 4.0.0 release it now includes an Automatic-Module-Name of com.healthmarketscience.jackcess in its manifest. This allows it to be used safely in the module path for Java 9+ projects.

This release is binary compatible with the 3.x release series.

Java 8+ Support (2019-02-08)

Jackcess now requires Java 8+ as of the 3.0.0 release. All third party dependencies have been updated to the latest versions. Jackcess now supports Java 8+ data types like LocalDateTime and Path. Databases can now optionally return Date values (legacy, backwards compatible) or LocalDateTime values. See DateTimeType and the Upgrade Guide for more details

Expression Evaluation (2018-09-08)

Have you ever wished that Jackcess could handle field «default values» (or other expressions)? Wish no longer! Expression evaluation is now enabled by default as of the 3.5.0 release. See the expression package javadocs for more details.

Brand New License! (2015-04-16)

Due to the generosity of Health Market Science and the efforts of the Apache Tika project, the OpenHMS projects have been relicensed under the Apache License, Version 2.0 (Jackcess versions 2.1.0 and higher).

Sample code

Here are a few snippets of code to whet your appetite. For more extensive examples, checkout the cookbook. And, since Jackcess is heavily unit tested, you can find even more example code in the unit tests.

Iterating through the rows of a table:

Table table = DatabaseBuilder.open(new File(«my.mdb»)).getTable(«MyTable»); for(Row row : table)
Row row = CursorBuilder.findRow(table, Collections.singletonMap(«a», «foo»)); if(row != null) < System.out.println("Found row where 'a' == 'foo': " + row); >else

Database db = DatabaseBuilder.create(Database.FileFormat.V2000, new File("new.mdb")); Table newTable = new TableBuilder("NewTable") .addColumn(new ColumnBuilder("a", DataType.LONG)) .addColumn(new ColumnBuilder("b", DataType.TEXT)) .toTable(db); newTable.addRow(1, "foo");

Database db = DatabaseBuilder.open(new File("my.mdb")); new ImportUtil.Builder(db, "Imported").importResultSet(resultSet); db.close();

Database db = DatabaseBuilder.open(new File("my.mdb")); new ImportUtil.Builder(db, "Imported2").setDelimiter(",").importFile(new File("my.csv")); db.close();

Other Resources

Some other jackcess related projects:

• mdbtools — Open Source project for reading Access files, written in C.
• Jackcess Encrypt — Extension library for Jackcess which implements support for some forms of Microsoft Access and Microsoft Money encryption.
• UCanAccess — Open Source pure Java JDBC Driver implementation.

Copyright ©2005–2023 OpenHMS. All rights reserved.

Источник

How to Connect Java (JDBC) with MS Access Database

In this tutorial I will teach you how to connect java (jdbc) with ms access database.

MS Access is a part of Microsoft Office and used as database management system (dbms).

For making a new database go to MS Access > Blank Database. Give a name to database and click on Create button to create the database.

Below example shows jdbc ms access database connectivity.

How to Connect Java (JDBC) with MS Access Database

JDK 1.8

If you are using jdk 1.8 then you will get ClassNotFoundException. Just read below article to solve the error.

Above program is really easy to understand. You can ask your queries in comment section.

Related Posts

How to Connect Java (JDBC) with MySQL or Oracle Database

Solve java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Error

Save and Retrieve Image from MySQL Database Using Servlet and JSP

Servlet Registration Form with MySQL Database Example

3 thoughts on “How to Connect Java (JDBC) with MS Access Database”

getting following error pls fix that for me.

java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at com.JDBCMSAccess.main(JDBCMSAccess.java:13)

java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Class.java:340)
at GUI.(GUI.java:71)
at GUI.main(GUI.java:108)

I got this error. i am using JDK 1.14.0

Источник

How to connect java to Ms access Database [duplicate]

A bit late, but for the record, jdbc-odbc-bridge can be copied from jdk7 and into jdk8. Ucanaccess is great but not for concurrent access. I’d opt for Ucanaccess if only one process shall access the database-file

1 Answer 1

you can use ucanacess.jar for connect Ms Aceess database

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MsAccessDatabaseConnectionInJava8 < public static void main(String[] args) < // variables Connection connection = null; Statement statement = null; ResultSet resultSet = null; // Step 1: Loading or registering Oracle JDBC driver class try < Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); >catch(ClassNotFoundException cnfex) < System.out.println("Problem in loading or " + "registering MS Access JDBC driver"); cnfex.printStackTrace(); >// Step 2: Opening database connection try < String msAccDB = "D:/WORKSPACE/TEST_WORKSPACE/Java-JDBC/Player.accdb"; String dbURL = "jdbc:ucanaccess://" + msAccDB; // Step 2.A: Create and get connection using DriverManager class connection = DriverManager.getConnection(dbURL); // Step 2.B: Creating JDBC Statement statement = connection.createStatement(); // Step 2.C: Executing SQL & retrieve data into ResultSet resultSet = statement.executeQuery("SELECT * FROM PLAYER"); System.out.println("ID\tName\t\t\tAge\tMatches"); System.out.println("==\t================\t===\t======="); // processing returned data and printing into console while(resultSet.next()) < System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3) + "\t" + resultSet.getString(4)); >> catch(SQLException sqlex) < sqlex.printStackTrace(); >finally < // Step 3: Closing database connection try < if(null != connection) < // cleanup resources, once after processing resultSet.close(); statement.close(); // and then finally close connection connection.close(); >> catch (SQLException sqlex) < sqlex.printStackTrace(); >> > > 

Linked

Related

Hot Network Questions

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.7.27.43548

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник