Connecting to a Tomcat Server with MySQL in JSP

So I'm trying to get some tutorial code working, and I'm stuck. I have a web application in Eclipse which consists of a DBConnector.java file, a Tomcat 6 server (local), and a short JSP script:

package com.atj.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


public class DBConnector {
private static DBConnector connector_ = null;

public static DBConnector getInstance() throws Exception {
    if (connector_ == null) {
        connector_ = new DBConnector();
    }
    return connector_;
}

public Connection getConnection() throws Exception {
    // Get DataSource
    Context ctx = new InitialContext();
    DataSource ds = (DataSource) ctx
            .lookup("java:comp/env/jdbc/mydatabase");
    Connection c = ds.getConnection();
    return c;
}

public String getFirstName() throws Exception {
    String first = "";
    try {
        Connection con = getConnection();
        Statement st = con.createStatement();
        ResultSet res = st.executeQuery("SELECT * FROM employees");
        while (res.next()) {
            first = res.getString("first");
            System.out.println(first);
        }
        con.close();
    } catch (SQLException s) {
        System.out.println("SQL code does not execute.");
    }
    return first;
}
}

I'm trying to post the JSP, but StackOverflow is parsing my HTML...argh..sorry..how do I escape this? Here is the tutorial code:

http://jspjdbc.blogspot.com/

It's a very short JSP script with the 2 main lines:

DBConnector dbConn = DBConnector.getInstance();
String name = dbConn.getFirstName(); <---error line

Anyway, when I open this in a browser, it prints "SQL Code does not execute" to the console. I can see this happens when an SQLException gets thrown. I debugged this down to this line in getConnection:

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

After executing this line, ctx is filled with mostly null values (except for id)

And then this line:

开发者_高级运维Connection c = ds.getConnection();

throws the SQLException, and control is immediately handed to the catch block.

Also, Eclipse is giving me a warning on this import, saying "The type DataSource is not accessible due to a restriction on the required library /System/Library/Frameworks/JavaVM.framework/Versions/1.4.2/Classes/classes.jar

import javax.sql.DataSource; //<---Eclipse is complaining about this line, but still compiling

Here is the project tree:

project
  Java Resources: src
    com.atj.project
      DBConnector.java
    Libraries
      EAR Libraries
    JRE System Library
    Web App Libraries
      mysql-connector-java-5.1.14-bin.jar
  Web Content
    META-INF
      content.xml
      MANIFEST.MF
    Web-INF
      lib
        mysql-connector-java-5.1.14-bin.jar
      web.xml
    db.jsp
Servers
  Tomcat v6.0 server at localhost-config
    catalina.policy
    catalina.properties
    content.xml
    server.xml
    tomcat-users.xml
    web.xml

Any help is greatly appreciated.

Edit-Here are my .xml files

content.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context>
<!-- Specify a JDBC datasource -->
<Resource name="jdbc/mydatabase"
auth="Container"
type="javax.sql.DataSource"
username="demo"
password="demo"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://www.coincan.net:3306/demo?autoReconnect=true"
validationQuery="select 1"
maxActive="2"
maxIdle="1"/>
</Context>

(new)web.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"     "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app id="WebApp_ID">
    <display-name>db</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>
    <resource-ref>
      <description>mydatabase</description>
      <res-ref-name>jdbc/mydatabase</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
  <res-sharing-scope>Shareable</res-sharing-scope>

I think you're still pretty far off.

You don't say if you have a META-INF/context.xml file to set up the data source pool in Tomcat; you need one.

You don't say if you declare the data source in your web.xml; you should.

See if those sort you out.

You might also want to read this. I think the JNDI name ought to be "jdbc/mydatabase".

You certainly aren't closing your resources properly in your method; you don't close the ResultSet at all, and you should close everything in a finally block.

Add this at the end of your web.xml:

   <resource-ref>
      <description>mydatabase</description>
      <res-ref-name>jdbc/mydatabase</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      <res-sharing-scope>Shareable</res-sharing-scope>
   </resource-ref>