Connecting JSP to Microsoft Access
Contents
- Introduction
- Installing the Easysoft JDBC Driver
- JSP Microsoft Access Example
- Connecting JSP to Access through Apache Tomcat
Introduction
This JSP tutorial shows how to connect Microsoft Access databases with JSP pages.
Java Server Pages (JSP) are text-based documents, typically HTML pages, that contain Java code. The embedded Java allows the page to contain dynamically generated content. JSP makes it possible to integrate content from a variety of data sources, such as databases, files, and JavaBean components. To use JSP, you need a JSP capable web server or application server. For example, Apache Tomcat, Oracle WebLogic, or IBM WebSphere.
Easysoft provide two ways to connect Microsoft Access databases with JSP pages.
- If your JSP capable web server or application server runs on Windows, use the JDBC-Access Gateway.
- If your JSP capable web server or application server runs on Linux or UNIX, use the JDBC-ODBC Bridge.
Installing the Easysoft JDBC Driver
Installing the JDBC-Access Gateway
Download, install, and license the JDBC-Access Gateway on the Windows machine where your JSP capable web server or application server is installed.
For more information about installing the JDBC-Access Gateway, refer to the JDBC-Access Gateway Getting Started Guide.
Installing the JDBC-ODBC Bridge
Download, install, and license the Windows JDBC-ODBC Bridge server on a machine where the Microsoft Access ODBC driver is installed. On this machine, configure a system ODBC data source that connects to your Access database. Test the data source with an ODBC application.
For more information about installing JDBC-ODBC Bridge and testing it with Microsoft Access, refer to Accessing Microsoft Access Data from any Java Platform. Refer also to the Installation and Configuration chapters of the JDBC-ODBC Bridge user guide.
JSP Microsoft Access Example
The example JSP page in this section shows how to connect to an Access database.
- Install the Easysoft Java Archive file (
.jar
) into the application you run JSP pages under. For example, if you are using Apache Tomcat on Windows, copyesmdb.jar
to$CATALINA_HOME\lib
; if you are using Apache Tomcat on Linux or UNIX, copyEJOB.jar
to$CATALINA_HOME/common/lib
.esmdb.jar
is the Java component of the JDBC-Access Gateway. The file is located indrive:\Program Files\Easysoft Limited\JDBC-Access Gateway
on the JDBC-Access Gateway machine, wheredrive
is the relevant drive letter, for example C.EJOB.jar
is the Java component of JDBC-ODBC Bridge. The file is located indrive:\Program Files\Easysoft\Easysoft JDBC-ODBC Bridge\jars
on the JDBC-ODBC Bridge server machine, wheredrive
is the relevant drive letter, for example C. - In your application, create a JDBC data source that connects to the target Microsoft Access data source.
The JDBC data source needs to specify the appropriate Easysoft JDBC driver class:
JDBC Driver Class JDBC-Access Gateway easysoft.sql.esMdbDriver JDBC-ODBC Bridge easysoft.sql.jobDriver The JDBC data source needs to specify the appropriate Easysoft JDBC driver connection URL:
JDBC Driver Connection URL JDBC-Access Gateway jdbc:easysoft:mdb?DBQ=path[;odbc-driver-attribute=value]
where:
- path is the path to the Access database (.mdb).
- odbc-driver-attribute is an Access ODBC driver attribute.
JDBC-ODBC Bridge jdbc:easysoft://hostname:port/access_system_data_source:logonuser=username:logonpassword=password
where:
- machinename is the name or IP address of the machine where the JDBC-ODBC Bridge server is running.
- port is the port on which the JDBC-ODBC Bridge server is listening, by default 8831.
- access_system_data_source is the Microsoft Access ODBC data source on the JDBC-ODBC Bridge server machine.
- username and password are a valid user name and password for the machine where the JDBC-ODBC Bridge server is running.
If you are required to supply a database user name and password when configuring the JDBC data source, use the same user name and password as the ones in the connection URL.
- Use
jdbc/MyDB
as the JNDI name for the JDBC data source. - Create a JSP page named jsp-ms-access-example.jsp. Add these lines to the file:
<html> <head> <title>JSP MicrosoftAccess Example</title> </head> <body> <%@ page import="javax.naming.*" %> <%@ page import="java.sql.*" %> <%@ page import="javax.sql.*" %> <h1>JSP MicrosoftAccess Example</h1> <% Connection conn = 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/MyDB"); // Allocate and use a connection from the pool conn = ds.getConnection(); // Fetch and display data stmt = conn.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 + "<br>"); } rs.close(); rs = null; stmt.close(); stmt = null; conn.close(); // Return to connection pool conn = 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 (conn != null) { try { conn.close(); } catch (SQLException e) { ; } conn = null; } } %> </body> </html>
You need to edit the
SELECT
statement in theexecuteQuery
method and the column name in thegetString
method.
Connecting JSP to Access through Apache Tomcat
You can run jsp-ms-access-example.jsp
under Apache Tomcat to connect to Access and retrieve data. For more information, refer to the JDBC-Access Gateway Getting Started Guide and Accessing ODBC Databases from Apache Tomcat.