Download JDBC Driver for Microsoft Access. Free Trial.

Connecting JSP to Microsoft Access

Contents

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.

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.

  1. 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, copy esmdb.jar to $CATALINA_HOME\lib; if you are using Apache Tomcat on Linux or UNIX, copy EJOB.jar to $CATALINA_HOME/common/lib.

    esmdb.jar is the Java component of the JDBC-Access Gateway. The file is located in drive:\Program Files\Easysoft Limited\JDBC-Access Gateway on the JDBC-Access Gateway machine, where drive is the relevant drive letter, for example C.

    EJOB.jar is the Java component of JDBC-ODBC Bridge. The file is located in drive:\Program Files\Easysoft\Easysoft JDBC-ODBC Bridge\jars on the JDBC-ODBC Bridge server machine, where drive is the relevant drive letter, for example C.

  2. 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=user_name: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.
    • user_name 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.
  3. Use jdbc/MyDB as the JNDI name for the JDBC data source.
  4. 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 the executeQuery method and the column name in the getString 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.

Download JDBC Driver for Microsoft Access. Free Trial.