Introduction
This article explains how to use the JDBC ODBC bridge to access an MS-Access database from Java applications. Instead of elaborating on the basics of the database, let's get down to the subject.
ODBC driver
In Java, we require a driver to be loaded at runtime to connect to any data source. The same is the case for ODBC data sources too. The driver is implemented as a class that is located and loaded at runtime. The ODBC driver for JDBC connections is named sun.java.odbc.JdbcOdbcDriver
.
ODBC connection string
As in Visual C++, we require an ODBC connection string to connect to the data source. Consider for example, that we are writing a VC++ program that connects to an Access database file named myDB.mdb present in the application directory. We would use an ODBC connection string as follows:
"Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb;"
In Java, we would write a similar connection string, but there would be an additional specification that points to the driver that will be required for the connection, that is, jdbc:odbc:
. Then, follow it up with the connection string. So the connection string in Java becomes:
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=myDB.mdb;"
Thus, to generalize the above, to be able to connect with an ODBC DSN, we require a connection string of the form:
"jdbc:odbc:ODBC DSN String"
Import the classes to connect to the database
The package containing the database related classes is contained in java.sql
. So, we do the import as follows:
import java.sql.*;
Load the JDBC:ODBC driver
Dynamically load the class sun.java.odbc.JdbcOdbcDriver
as follows:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Open the MS-Access database file in the application space
To do this, we use the ODBC DSN as specified above:
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb;";
Connection conn = DriverManager.getConnection(database, "", "");
Create a Statement object to execute the SQL query
The Statement
must be created to execute a SQL query on the opened database. It is done with the following code:
Statement s = conn.createStatement();
Cleanup after finishing the job
To clean up after we are done with the SQL query, we call s.close()
to dispose the object Statement
. Then, before we end our program or after the point where we decide that the database is not required any more, we close the database with a call to conn.close()
. The following code does the cleanup after we are done:
s.close();
conn.close();
Execute a SQL statement on a valid Statement object
Call s.execute("SQL statement")
when you require to execute a SQL query. It returns the number of rows effected by the query. If the last query holds a ResultSet
to be returned which generally occurs with SELECT ...
type queries, then call s.getResultSet()
which returns the ResultSet
object. The following code shows how to use a SELECT
query and display the value contained in the first two columns of the table.
String selTable = "SELECT * FROM SOMETABLE";
s.execute(selTable);
ResultSet rs = s.getResultSet();
while((rs!=null) && (rs.next()))
{
System.out.println(rs.getString(1) + " : " + rs.getString(2));
}
That's all. Let us now see a demo app that clarifies all that I have written above.
An example application
The following application does the following:
- Loads the JDBC ODBC driver.
- Opens a ODBC data source which opens the file myDB.mdb present in the application working directory.
- Gets the
Statement
object for SQL execution. - Generates the name of a table with a random number generator.
- Creates the table.
- Enters 25 random entries into it.
- Displays the content of the table.
- Deletes or drops the table created.
- Closes the
Statement
object and then closes the connection to the database.
Here is the required code:
import java.sql.*;
public class dbAccess
{
public static void main(String[] args)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=myDB.mdb;";
Connection conn = DriverManager.getConnection(database, "", "");
Statement s = conn.createStatement();
String tableName = "myTable" + String.valueOf((int)(Math.random() * 1000.0));
String createTable = "CREATE TABLE " + tableName +
" (id Integer, name Text(32))";
s.execute(createTable);
for(int i=0; i<25; i++)
{
String addRow = "INSERT INTO " + tableName + " VALUES ( " +
String.valueOf((int) (Math.random() * 32767)) + ", 'Text Value " +
String.valueOf(Math.random()) + "')";
s.execute(addRow);
}
String selTable = "SELECT * FROM " + tableName;
s.execute(selTable);
ResultSet rs = s.getResultSet();
while((rs!=null) && (rs.next()))
{
System.out.println(rs.getString(1) + " : " + rs.getString(2));
}
String dropTable = "DROP TABLE " + tableName;
s.execute(dropTable);
s.close();
conn.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
That's all I have!