JDBC: An example to connect MS Access database in Java 8

In this article, we will learn and list down the steps to connect MS Access database in Java 8 and finally executing a simple query to test whether connected database works as expected

 

We will divide this article into 2 parts

  • MS Access database
  • JDBC application for MS Access database using Java 8

 

Check MS Access database interaction through Java JDBC API for Java 1.7 or lower versions

 

Part 1: MS Access Database

Before working with JDBC API to interact with database (to be specific MS Access database for this example), we need to set up MS Access database and create required things like,

  • Create database
  • Create table (inside newly created database)
  • Insert few sample records (inside newly created table)

 

It’s very easy,

  • Open Microsoft Office Access 2007 Database
  • Create new table called “Player
  • Add 4 fields like Player_ID, Name, Age & Matches
  • And finally insert couple of records
  • As shown in the below screen-capture

 

Part 2: JDBC application using Java 8

As we are completed set up & ready with MS Access database, next step is to figure out essential things required to query the database from Java application using JDBC API in Java 8

Pre-requisite:

  • Loading MS Access driver class for Java 8 (ucanaccess.jdbc.UcanaccessDriver)
  • Database URL formation (database file location)
  • Required JAR files as listed below in the screen-capture

To download required JARS:

 

Once we are ready with above listed things, then we can go ahead and code an example to connect MS Access database and query it

Let us see an example on MS Access database

MsAccessDatabaseConnectionInJava8.java

package in.bench.resources.msaccess.db.example;

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();
			}
		}
	}
}

Output:

ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	43	200
2	Shane Warne		45	145
3	Kevin Pietersen	36	104
4	Shahid Afridi		36	27
5	Brian Lara		46	131
6	Graeme Smith		36	117
7	Mahela Jayawardene	38	145

Download: MS Access database connection example

 

Difference between Java version until 1.7 and Java 1.8 version

Until Java 1.7 version, we are using Jdbc-Odbc bridge to connect MS Access database using the JDBC driver class sun.jdbc.odbc.JdbcOdbcDriver

Whereas in Java 1.8 version, ucanaccess driver should be used to connect to MS Access database using driver class net.ucanaccess.jdbc.UcanaccessDriver

 

References:

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.html
http://ucanaccess.sourceforge.net/site.html
http://www.benchresources.net/spring-jdbc-introduction-and-jdbc-example-without-spring/
https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
https://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28025
http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
https://en.wikipedia.org/wiki/JDBC_driver
http://www.devx.com/tips/Tip/28818
https://docs.oracle.com/javase/7/docs/api/java/lang/Class.html#forName(java.lang.String)
http://stackoverflow.com/questions/21955256/manipulating-an-access-database-from-java-without-odbc

 

Read Also:

 

Happy Coding !!
Happy Learning !!

Sorting ArrayList using sort() method of List in Java 8
Various ways to iterate over HashMap of ArrayList in Java
  • Troy Wu

    I have spent two hours searching about how to connect java with access database, and 90% of result I found are useless. When I was about to give up, I found your website which is the best solution I found so far. I finally connect my java program to ms access. thank you so much, sir. Thank you!

    • Glad to know that, it helped you to connect MS-Access database from Java

  • Daroga Jee

    Where to paste these downloaded jar.

  • Daroga Jee

    Will this work with MS-Excel

    • This article explains the steps to insert new records into MS-Access database. Let us know, whether it works for you.

      • Daroga Jee

        then tell us.. Where to go and connect to MS-Excel..

        • Daroga,

          Above article, explains to insert records into MS-Excel. Go step-by-step, you will understand.

          • Daroga Jee

            where is the article…Tell us i cant see it..

          • Daroga Jee

            I think you don’t seem to get it… Difference between Excel And Access program… Where is Excel jar.. Where is full fledge Excel program of JAVA 8 JDBC…

          • MS-Access is the database to insert/select/delete records using JDBC APIs. I don’t think, there are JDBC APIs available to connect to MS-Excel spreadsheet. May be, you can use Apache POI APIs to insert items in MS-Excel spreadsheet

          • Ped Pen

            JDBC stands for Java Database Connectivity. Excel is not a database. The article is about using the Access database in Java. There are other APIs that can manipulate Excel files but these are not JDBC APIs.

          • This article works for MS-Access database only. By mistake it has been typed as MS-Excel in the above comment.

  • Sherif Abbas

    keep it up

  • Talal

    ” Error: Could not find or load main class MsAccessDatabaseConnectionInJava8 ”
    I am having this error. Please Help