Connecting JSP to Microsoft Access

This JSP tutorial shows how to use the Easysoft JDBC-ODBC Bridge (JOB) 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, BEA WebLogic or IBM WebSphere.

Installing the Easysoft JDBC-ODBC Bridge

Download, install and license the Windows JOB 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. Use one of the test applets included in the JOB distribution to check that you can access the ODBC data source through JOB.

For more information about installing JOB and testing it with Microsoft Access, see Accessing Microsoft Access Data from any Java Platform. Refer also to the Installation and Configuration chapters of the JOB User Guide.

JSP MS Access Example

The example JSP page in this section shows how to connect to an Access database.

Install EJOB.jar into the application you run JSP pages under. For example, if you are using Apache Tomcat on Linux or Unix, copy EJOB.jar to $CATALINA_HOME/common/lib.

EJOB.jar is the JOB JDBC driver. The file is located in drive:\Program Files\Easysoft\Easysoft JDBC-ODBC Bridge\jars on the JOB Server machine, where drive 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 JOB JDBC driver class, easysoft.sql.jobDriver, and a JOB connection URL:

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 JOB Server is running.
port is the port on which the JOB Server is listening, by default 8831.
access_system_data_source is the Microsoft Access ODBC data source on the JOB Server machine.
username and password are a valid user name and password for the machine where the JOB 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 MS Access Example</title>
 </head>
 <body>
   <%@ page import="javax.naming.*" %>
   <%@ page import="java.sql.*" %>
   <%@ page import="javax.sql.*" %>

   <h1>JSP MS Access 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 the executeQuery method and the column name in the getString method.

Connecting JSP to Microsoft Access

Contents

Installing the Easysoft JDBC-ODBC Bridge

JSP MS Access Example

Connecting JSP to Access through Apache Tomcat

Article Feedback