How to connect to Microsoft Access or Microsoft Excel from Java.
Introduction
JDBC is a Java application programming interface (API) that connects Java to relational databases (and other tabular data, such as spreadsheets and flat files). To interact with a database, a Java application uses a JDBC driver. A JDBC driver implements the JDBC API for a particular database.
Microsoft do not produce a JDBC driver for Jet, the underlying database engine for the Microsoft Office product suite. However, Microsoft do produce a Jet-based ODBC driver. (ODBC is another data access technology, the Microsoft implementation of which is included with Windows.) To provide a JDBC interface to Office applications via this native Microsoft interface, a JDBC driver must be able to convert JDBC calls to ODBC calls. As far as the Java application is concerned, it is using a normal JDBC driver. As far as the Office application is concerned, it is being accessed via the normal ODBC driver.
The Easysoft JDBC-Access Gateway is a JDBC driver for Access that uses the Java Native Interface (JNI) to communicate with the Access ODBC driver library. Because this ODBC driver library also supports Excel, the Easysoft JDBC-Access Gateway also provides JDBC access to Excel.
You can use the Easysoft JDBC-Access Gateway to connect Java applications, servlet/JavaServer Pages (JSP) engines, integrated development environments (IDE) and application servers with Access and Excel.
The Easysoft JDBC-Access Gateway has both a Java component and a native Windows component, which is used to communicate with the Microsoft ODBC driver. By default, Java applets cannot load native code libraries; the Java security mechanism prevents this because it cannot police what happens at native code level. To use the Easysoft JDBC-Access Gateway with a Java applet, your Java security policy needs to allow the applet to load native libraries. For example:
/* Sample user .java.policy file */
grant codeBase "http://my_webserver/my_jdbc_applet_dir/-" {
permission java.lang.RuntimePermission "loadLibrary.*";
};
Note that using applets with the Easysoft JDBC-Access Gateway is not the only way to publish your Access or Excel data on the Web or manipulate the data from within a Web browser. As mentioned, the Easysoft JDBC-Access Gateway is compatible with servlet/JSP engines (such as Apache Tomcat) and application servers (such as Adobe ColdFusion), and these products allow Web-based applications with database backends to be created.
Supported Microsoft File Formats
The native library for the Easysoft JDBC-Access Gateway is odbcjt32.dll, which is Microsoft’s ODBC driver for Access and Excel (also known as the Microsoft ODBC Desktop Database Drivers).
odbcjt32.dll supports the following Access database file formats:
Version 4.0 .mdb files, which is the default format for databases created in Access 2000, 2002 and 2003.
Version 3.0 .mdb files, which is the default format for databases created in Access 95 and Access 97.
Version 2.0 .mdb files, which is the default format for databases created in Access 2.0.
Although Office Access 2007 introduced a new database file format (.accdb), version 4.0 .mdb files continue to be supported by Access 2007 and later.
In addition, odbcjt32.dll supports .xls format workbooks created in:
Excel 97–2003
Excel 5.0/95
Excel 4.0
Excel 3.0
Although Office Excel 2007 introduced new spreadsheet file formats (.xlsb, .xlsm, .xlsx), .xls files continue to be supported by Excel 2007 and later.
Before You Begin
What You Need To Know
Obtain this information from your system administrator:
The path to the target Access database (.mdb) or Excel workbook (.xls) on the machine you intend to install the Easysoft JDBC-Access Gateway on.
The target file must be visible through the local file system on the Easysoft JDBC-Access Gateway machine. For example, in a folder on this machine or a mapped network drive or in a shared folder.
Prerequisite Software
Java Runtime Environment Requirements
You need a Java Runtime Environment (JRE) installed on the Windows machine you want to install the Easysoft JDBC-Access Gateway on. The JRE contains the Java virtual machine (JVM), runtime class libraries, and Java application launcher that are necessary to run programs written in the Java programming language. The Easysoft JDBC-Access Gateway is compatible with JRE 1.6.0 and later.
To check whether you have the JRE installed on your machine and that your JRE version is one the Easysoft JDBC-Access Gateway supports, open a Command Prompt window, and type java -version. For example:
c:\>java -version
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03)
Java HotSpot(TM) Client VM (build 20.1-b02, mixed mode, sharing)
If the reported JRE version is 1.5.n or earlier (or you get the error "'java' is not recognized as an internal or external command, operable program or batch file."), you need to obtain a JRE for your machine.
If the output produced by running java -version contains 64-Bit Server VM, you have a 64-bit JRE. The Easysoft JDBC-Access Gateway requires a 32-bit JRE; a 32-bit JRE (x86) will install and function correctly on a 64-bit machine. (The Easysoft JDBC-Access Gateway uses the ODBC Desktop Database Drivers, which is a 32-bit library. There is no 64-bit version of this library. The native component of the Easysoft JDBC-Access Gateway and the JRE must also be 32-bit as you cannot mix 32-bit and 64-bit libraries.)
Windows versions of the JRE are available to download from:
http://www.oracle.com/technetwork/java/javase/downloads/index.html
Note The Java Development Kit (JDK), which includes the JRE, is also available to download from this web page. However, unless you are going to develop a Java application to run against the Easysoft JDBC-Access Gateway, you only need to download the JRE package.
ODBC Desktop Database Drivers
The ODBC Desktop Database Drivers must be installed on the machine on which you install the Easysoft JDBC-Access Gateway. Note that you do not need to install Access or Excel on the Easysoft JDBC-Access Gateway machine.
Since Windows 2000, the ODBC Desktop Database Drivers (one of the Jet Database Engine Components) have shipped with Windows and so should be already present on your machine as part of the Windows installation.
To check whether the ODBC Desktop Database Drivers are installed, use ODBC Data Source Administrator:
Do one of the following:
On 32-bit Windows, in the Windows Run dialog box, type:
%windir%\system32\odbcad32.exe
On 64-bit Windows, in the Windows Run dialog box, type:
%windir%\syswow64\odbcad32.exe
Note On 64-bit Windows, the ODBC Data Source Administrator that is accessible from Control Panel is a 64-bit application. The 64-bit ODBC Data Source Administrator only lists 64-bit ODBC drivers, and so cannot be used to check whether the ODBC Desktop Database Drivers are installed.
In ODBC Data Source Administrator, click the Drivers tab.
In the File column, look for the file name ODBCJT32.DLL.
If ODBCJT32.DLL is listed, the ODBC Desktop Database Drivers are installed, and you have the prerequisite software for the Easysoft JDBC-Access Gateway. Otherwise, you need to ask your system administrator to install the Microsoft driver before you can use the Easysoft JDBC-Access Gateway.
Installing the Easysoft JDBC-Access Gateway
Log into the Easysoft website.
If you have not yet done so, you need to register first. On the registration form, an asterisk (*) indicates that a field is mandatory.
Download the Easysoft JDBC-Access Gateway distribution.
Save the distribution file to a temporary directory on the machine where you intend to install the Easysoft JDBC-Access Gateway.
Execute the file distribution that you downloaded in the previous step.
Follow the on screen instructions.
Obtain a free trial licence:
In Easysoft Data Access License Manager, complete the contact information and click Request License.
The email contact address you supply must be the email address you registered with on the Easysoft web site.
Choose Time Limited Trial and click Next.
Choose Easysoft JDBC-Access Gateway from the drop-down list of products and click Next.
Click On-Line Request. You will get a message telling you that your license has been added. Click OK.
Click Finish.
Using the Easysoft JDBC-Access Gateway
Setting the Class Path
The Java class library file for the Easysoft JDBC-Access Gateway is named esmdb.jar. As esmdb.jar is not part of the Java platform, the class path must include the esmdb.jar file. Otherwise, your application will throw a ClassNotFoundException exception when trying to use the Easysoft JDBC-Access Gateway. The class path is a parameter that tells the JVM and Java programs where to find to find third-party and user-defined classes.
esmdb.jar is installed in the following location:
easysoft_installation_folder\Libs
The default location for easysoft_installation_folder is drive:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway.
What class path configuration is required depends on your application:
Applications that are Run at the Command Prompt
The CLASSPATH environment variable is used, which can be set on a system, user or session basis. For example, this command sets the class path for Java applications run in a particular command prompt session:
C:\MyJavaApps>set CLASSPATH="%CLASSPATH%;C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\esmdb.jar"
Alternatively, you can specify the class path on the Java command line that runs an application by using the java -classpath option.
C:\MyJavaApps> java -cp ".;C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\esmdb.jar" MyJavaApp
The following steps show how to set the class path when compiling and running the sample Easysoft JDBC-Access Gateway Java application. (Note that the procedure assumes that you have the JDK installed.)
Save the sample Java code to a file named ConnectToAccess.java.
In a Command Prompt, cd to the directory where you saved ConnectToAccess.java.
Set the class path for the Java compiler and application launcher:
C:\MyJavaApps>set CLASSPATH="%CLASSPATH%;C:\Program Files\Easysoft Limited\Easysoft JDBC-Access Gateway\Libs\esmdb.jar"
Compile and run the sample application. For example:
C:\MyJavaApps> "C:\Program Files\Java\jdk1.6.0_26\bin\javac" ConnectToAccess.java
C:\MyJavaApps> java ConnectToAccess
JDBC Driver Information
Driver name: easysoft.sql.esMdbDriver
Driver Major Version: 1
Driver Minor Version: 0
.
.
.
Applications that Run in an IDE
Each IDE has a different method for setting its class path. Setting the CLASSPATH environment variable will not work. You need to add the esmdb.jar folder to the IDE class path.
See Also
Using the Easysoft JDBC-Access Gateway with Eclipse IDE for Java Developers
Using the Easysoft JDBC-Access Gateway with NetBeans IDE
Using the Easysoft JDBC-Access Gateway with Embarcadero JBuilder
Servlets and JSPs
Servlets and JSPs are run in a servlet/JSP engine such as Apache Tomcat. The class path must be set according to the servlet/JSP engine documentation. Setting the CLASSPATH environment variable will not work. You may have to set the engine class path through a GUI or configuration file. Alternatively, you may have to copy esmdb.jar to a particular folder such as lib.
See Also
Using the Easysoft JDBC-Access Gateway with Apache Tomcat
Connecting to your Access Database or Excel Workbook
The Easysoft JDBC-Access Gateway provides two JDBC drivers: a driver for Access databases and a driver for Excel workbooks.
The Java class for the Access JDBC driver is easysoft.sql.esMdbDriver. The Java class for the Excel JDBC driver is easysoft.sql.esXlsDriver.
To register a Easysoft JDBC-Access Gateway JDBC driver, your Java application must specify the appropriate class. For example:
// Register the Access JDBC driver.
Class.forName("easysoft.sql.esMdbDriver");
–Or–
// Register the Excel JDBC driver.
Class.forName("easysoft.sql.esXlsDriver");
When the relevant JDBC driver is registered, you can establish a connection by using a connection URL and the getConnection method of the DriverManager class. For example:
String connectionUrl = "jdbc:easysoft:mdb" +
"?DBQ=C:/Users/Public/Northwind.mdb";
Connection con = DriverManager.getConnection(connectionUrl);
To establish a connection with the Easysoft JDBC-Access Gateway, use a connection URL of the form:
jdbc:easysoft:
where:
Example Connection URLs
Opens an Access database for read-only access:
jdbc:easysoft:mdb?DBQ=C:/Users/Public/Northwind.mdb;ReadOnly=True
Opens an an Access database that is stored in a shared folder:
jdbc:easysoft:mdb?DBQ=//mymachine/myshare/Sales.mdb;
Opens a password-protected Excel workbook from a mapped network drive:
jdbc:easysoft:xls?DBQ=Z:/Orgdata.xls;PWD=p455w0rd
Further Support
To contact the Easysoft support team, send an email message to support@easysoft.com.
Appendices
Easysoft JDBC-Access Gateway Demonstrators
This sample Java application connects to an Access database and returns a list of tables in the database. The application also prints out information about the database and the Easysoft JDBC-Access Gateway.
import java.sql.*;
import java.util.Properties;
public class ConnectToAccess {
public static void main(String[] args) {
// Replace the DBQ value with the path to your Access database.
String connectionUrl = "jdbc:easysoft:mdb?" +
"DBQ=C:/Users/Public/Northwind.mdb";
Driver driver = null;
DriverPropertyInfo props[] = null;
Connection con = null;
DatabaseMetaData dm = null;
ResultSet rs = null;
try {
// Register the Easysoft JDBC-Access Gateway.
Class.forName("easysoft.sql.esMdbDriver");
driver = DriverManager.getDriver(connectionUrl);
System.out.println("JDBC Driver Information");
System.out.println ("\tDriver name: " +
driver.getClass().getName());
System.out.println("\tDriver Major Version: " +
driver.getMajorVersion());
System.out.println("\tDriver Minor Version: " +
driver.getMinorVersion());
con = DriverManager.getConnection(connectionUrl);
dm = con.getMetaData();
System.out.println("\tJDBC Major Version: " +
dm.getJDBCMajorVersion());
System.out.println("\tJDBC Minor Version: " +
dm.getJDBCMinorVersion());
props = driver.getPropertyInfo (connectionUrl, new Properties());
/* These attributes are Microsoft Access ODBC driver attributes,
which the Easysoft JDBC-Access Gateway passes to the
Microsoft driver. Only the DBQ attribute is mandatory. */
System.out.println ("JDBC URL Attributes");
for (int i = 0; i < props.length; i++) {
System.out.print ("\t" + props[i].name);
System.out.print (" = ");
System.out.print (props[i].value);
System.out.print (" : ");
System.out.println (props[i].description +".");
}
System.out.println("Database Information");
System.out.println("\tDatabase Name: " +
dm.getDatabaseProductName());
System.out.println("\tDatabase Version: " +
dm.getDatabaseProductVersion());
rs = dm.getTables(null, null, "%", null);
System.out.println("Database Tables");
// Retrieve type and name for each table in the database.
while (rs.next()) {
System.out.println("\t" + rs.getString(3) +
" : " + rs.getString(4));
}
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
This sample JSP connects to and retrieves data from a JDBC data source that has been configured as a Java Naming and Directory Interface (JNDI) resource named Northwind.
<%@ page import="javax.naming.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="java.io.PrintWriter" %>
<%@page contentType="text/html;charset=UTF-8"%>
Sample Easysoft JDBC-Access Gateway JSP
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
// Look up our data source
DataSource ds = (DataSource) envCtx.lookup("jdbc/Northwind");
// Allocate and use a connection from the pool
con = ds.getConnection();
// Fetch and display data
stmt = con.createStatement();
// You need to edit this query
rs = stmt.executeQuery("SELECT CompanyName FROM suppliers");
while (rs.next()) {
// You need to edit this column name
String s = rs.getString("CompanyName");
out.print(s + "
");
}
rs.close();
rs = null;
stmt.close();
stmt = null;
con.close(); // Return to connection pool
con = null; // Make sure we do not close it twice
} catch (SQLException e) {
out.print("Throw e" + e);
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rs != null) {
try { rs.close(); } catch (SQLException e) { ; }
rs = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { ; }
stmt = null;
}
if (con != null) {
try { con.close(); } catch (SQLException e) { ; }
con = null;
}
}
%>
No comments:
Post a Comment