none
Error when using Tomcat to connect datasource.

    Question

  • At my university, I have a lab, the target is connecting the datasource which using Tomcat. I code by Eclipse, use SQL Server 2005 and Tomcat 6.0.16.

    Here my structure package:

    The content of DisplayBooksServlet.java:

    package lab.ds.serv;

     

    import java.io.IOException;

    import java.sql.Connection;

    import java.sql.ResultSet;

    import java.sql.ResultSetMetaData;

    import java.sql.SQLException;

    import java.sql.Statement;

    import java.util.ArrayList;

    import java.util.List;

     

    import javax.naming.InitialContext;

    import javax.naming.NamingException;

    import javax.servlet.Servlet;

    import javax.servlet.ServletException;

    import javax.servlet.http.HttpServlet;

    import javax.servlet.http.HttpServletRequest;

    import javax.servlet.http.HttpServletResponse;

    import javax.sql.DataSource;

     

    /**

    Servlet implementation class for Servlet: DisplayBooksServlet

    *

    */

    public class DisplayBooksServlet extends HttpServlet {

     

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    doPost(request, response);

    }    

     

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    try {

    String query = "SELECT TITLE, AUTHOR, PRICE FROM BOOKS";

    // Create initial context

    InitialContext ctx = new InitialContext();

     

    // Lookup database

    DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/BookStoreDB");

     

    // Get connection from the datasource

    conn = ds.getConnection();

     

    // Execute the query

    stmt = conn.createStatement();

    rs = stmt.executeQuery(query);

     

    // Prepare for displaying

    List<String[]> lstResult = new ArrayList<String[]>();

    ResultSetMetaData rsmt = rs.getMetaData();

    int numCols = rsmt.getColumnCount();

    String[] rows;

     

    // Get column names

     

    rows = new String[numCols];

    for (int i = 0; i < numCols; i++) {

    rows[i] = rsmt.getColumnName(i + 1);

    }

    lstResult.add(rows);

     

    while (rs.next()) {

    rows = new String[numCols];

    rows[0] = rs.getString("TITLE");

    rows[1] = rs.getString("AUTHOR");

    rows[2] = String.valueOf(rs.getFloat("PRICE"));

     

    lstResult.add(rows);

    }

     

    System.out.println("Number of records=" + lstResult.size());

    request.setAttribute("BOOKS", lstResult);

    request.getRequestDispatcher("/DisplayBooks.jsp").forward(request, response);

    } catch (NamingException e) {

    e.printStackTrace();

    } catch (SQLException e) {

    e.printStackTrace();

    } finally {

    if (rs != null) { try { rs.close(); } catch (SQLException e) { } }

     

    if (stmt != null) { try { stmt.close(); } catch (SQLException e) {} }

     

    if (conn != null) { try { conn.close(); } catch (SQLException e) { } }

    } } }

    The content of Context.xml:
    <Context path="LabDSBookStore" docBase="LabDSBookStore" crossContext="true">
      <Resource name="jdbc/BookStoreDB" auth="Container" type="javax.sql.DataSource"
        driverClassName=" com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=BookStore"
        username="sa"
        password="sa123"
        maxActive="100" maxIdle="30" maxWait="10000"
        poolPreparedStatements="true"
        
        removeAbandoned="true"
        removeAbandonedTimeout="300"
        logAbandoned="true"
      />
    </Context>
    The content of web.xml:
    <?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
      <display-name>LabDSBookStore</display-name>
      <welcome-file-list>
        <welcome-file>index.html</welcome-file>
        <welcome-file>index.htm</welcome-file>
        <welcome-file>index.jsp</welcome-file>
        <welcome-file>default.html</welcome-file>
        <welcome-file>default.htm</welcome-file>
        <welcome-file>default.jsp</welcome-file>
      </welcome-file-list>
      <servlet>
        <description></description>
        <display-name>DisplayBooksServlet</display-name>
        <servlet-name>DisplayBooksServlet</servlet-name>
        <servlet-class>lab.ds.serv.DisplayBooksServlet</servlet-class>
      </servlet>
      <servlet-mapping>
        <servlet-name>DisplayBooksServlet</servlet-name>
        <url-pattern>/DisplayBooksServlet</url-pattern>
      </servlet-mapping>
      <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/BookStoreDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
      </resource-ref>
    </web-app>
    The content of DisplayBooks.jsp:
    <%@ page import="java.util.List" %>
    <HTML>
    <% List<String[]> lstBooks = (List<String[]>)request.getAttribute("BOOKS"); %>
    <BODY>
    <TABLE border="1">
    <% for (String[] row: lstBooks) { %>
    <TR>
    <% for (String col: row) { %>
    <TD> <%= col %></TD>
    <% } %>
    </TR>
    <% } %>
    </TABLE>
    </BODY>
    </HTML>
    The content of index.jsp:
    <%@page import="javax.naming.Context"%>
    <%
    response.sendRedirect(request.getContextPath() + "/DisplayBooksServlet");
    %>
    About SQL Server 2005, I created a database, name BookStore, with content: 
    CREATE TABLE BOOKS(ID VARCHAR(10),TITLE VARCHAR(256),AUTHOR VARCHAR(128),PUBLISHER VARCHAR(128),PRICE FLOAT)
    then:
    INSERT INTO BOOKS VALUES('AAA001','Develop Web Application with JSP, Servlet','John Han','Maxim',59)
    INSERT INTO BOOKS VALUES('AAA002','Implement Core Web Application with JSP, Servlet','Rocky','Home',9)
    I export to file .war, start Tomcat, run Google Chrome, connect "localhost:9090/LabDSBookStore" (I use port 9090 for Tomcat) and have an error: cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
    I have already placed sqljdbc.jar in apache-tomcat-6.0.16\lib, also in LabDSBookStore\WebContent\WEB-INF\lib; also placed the file LabDSBookStore.war in apache-tomcat-6.0.16\work\Catalina\localhost and apache-tomcat-6.0.16\webapps
    I don't know how to fix this error. Thank for all ideas from everyone.

    Saturday, August 27, 2011 3:42 AM

Answers

All replies