Connecting JSP to Microsoft Access

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.

Contents

Installing the Easysoft JDBC Driver

Installing the JDBC-Access Driver

If you have not already done so, please register with us to download a fully functional trial version of the JDBC-Access Driver.

Download, install and license the JDBC-Access Driver on the Windows machine where your JSP capable web server or application server is installed.

For more information about installing the JDBC-Access Driver, see the JDBC-Access Driver Getting Started Guide.

Installing the JDBC-ODBC Bridge

If you have not already done so, please register with us to download a fully functional trial version of JOB.

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.

  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 Driver. The file is located in drive:\Program Files\Easysoft Limited\JDBC-Access Driver on the JDBC-Access Driver machine, where drive is the relevant drive letter, for example C.

    EJOB.jar is the Java component of JOB. 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.

  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 DriverClass
    JDBC-Access Drivereasysoft.sql.esMdbDriver
    JOBeasysoft.sql.jobDriver

    The JDBC data source needs to specify the appropriate Easysoft JDBC driver connection URL:

    JDBC DriverConnection URL
    JDBC-Access Driver 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.
    JOB 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.

  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 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 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, see the JDBC-Access Driver Getting Started Guide and Accessing ODBC Databases from Apache Tomcat.

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)