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
JDBC program to connect MS Access Database
- 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
2.1 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
2.2 To download required JARS:
2.3 JDBC program to connect and query MS Access database/table
- Once we are ready with above listed things
- Then we can go ahead and code an example to connect MS Access database
- Finally querying 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 and // 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
2.4 Download:
Difference between Java 1.7 and Java 1.8 versions ?
- 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
Read Also:
- Introduction to JDBC
- Different types of JDBC drivers
- JDBC Driver list for all leading databases
- Basic components of JDBC
- JDBC connection steps
- Oracle database connection steps
- MySQL database connection steps
- MS Access database connection steps until Java 7 version
References:
- https://www.databasestar.com/jdbc-in-java/
- 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
Happy Coding !!
Happy Learning !!