Java JDBC: An example to connect MS Access database

In this article, we will learn and list down the steps to connect MS Access database 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
  • Java JDBC application

 

Check MS Access database interaction through Java JDBC API for Java 1.8 version

 

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 and create new table called “Player” and add 4 fields like

  • Player_ID
  • Name
  • Age
  • Matches

And finally insert couple of records

1_MS_Access_database_table_screen_capture

 

Part 2: Java JDBC application

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

 

Pre-requisite:

  • Loading MS Access driver class (com.jdbc.odbc.JdbcOdbcDriver)
  • Database URL formation (database file location)

 

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

MsAccessDatabaseConnectionExample.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 MsAccessDatabaseConnectionExample {

	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("sun.jdbc.odbc.JdbcOdbcDriver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MS Access JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			String msAccessDBName = 
                                   "D:\\WORKSPACE\\TEST_WORKSPACE\\Java-JDBC\\Player.accdb";
			String dbURL = 
                                "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};                                      DBQ=" + msAccessDBName + ";DriverID=22;READONLY=true";

			// 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

 

References:

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.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)

 

Read Also:

 

Happy Coding !!
Happy Learning !!

Java JDBC: Creating database using JDBC Statement Interface
Java JDBC: An example to connect Oracle database