Connect to mysql 5 with Tomcat 6

When I try to solve this problem I read two docs: help in Netbeans 6(http://www.netbeans.org/kb/60/web/mysql-webapp.html#settingUpConnPool) and jndi help from tomcat.apache.org(http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html). But when I lookup InitialContext, application throws NamingException. I spent over four hours to solve this problem. As I've understood, when I create resource in context.xml which placed in web application, Tomcat dont put it in global resources and when I connect to this resource, I should write prefix java:comp/env/ before actual resource name. I replace this line:

Datasource ds = (Datasource)ic.lookup("jdbs/sambledb");

with this:

Datasource ds = (Datasource)ic.lookup("java:comp/env/jdbs/sambledb");

YES! Lookup result was successfull.

I use oficial recommended jconnector version 5.1 from mysql.com. I copy jar with connector in TOMCAT_HOME/lib/, but I get ClassNotFoundException.... after long four or five hours I hate Window Vista, Tomcat, Netbeans, Java, Computers and...my life :) . I cannt direct solve this problem. I have no ideas how services in Windows works, but when I uninstall Tomcat which I downloaded from link with name Windows Service Installer(http://www.sai.msu.su/apache/tomcat/tomcat-6/v6.0.18/bin/apache-tomcat-6.0.18.exe) and install simple zip version (http://www.sai.msu.su/apache/tomcat/tomcat-6/v6.0.18/bin/apache-tomcat-6.0.18.zip) all works ok(YEAH!) :)

Some tips:

-Don't use empty password in mysql to connect.

-In different cases Tomcat throws exception sounds like this - driver "com.mysql.jdbc.Driver" not found. You can reduce number of meanings if insert in your code this line - Class.forName("com.mysql.jdbc.Driver").

-DONT USE SERVICE INSTALLER VERSION OF TOMCAT!!!(I like init.d:))

Finally I have something like this:

web.xml:

view plainprint?

1. <resource-ref>  
2. <description>jdbc:mysql://localhost:3306/mynewdatabase</description>  
3.   <res-ref-name>jdbc/mynewdatabase/<res-ref-name>  
4.   <res-type>javax.sql.DataSource</res-type>  
5.   <res-auth>Container</res-auth>  
6. </resource-ref>  

<resource-ref>
<description>jdbc:mysql://localhost:3306/mynewdatabase</description>
  <res-ref-name>jdbc/mynewdatabase/<res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

context.xml:

view plainprint?

1. <Resource name="jdbc/mynewdatabase" auth="Container" type="javax.sql.DataSource"  
2. maxActive="100" maxIdle="30" maxWait="10000"  
3. username="javauser" password="qwerty" driverClassName="com.mysql.jdbc.Driver"  
4. url="jdbc:mysql://127.0.0.1:3306/mynewdatabase"/></resource>  

<Resource name="jdbc/mynewdatabase" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="qwerty" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/mynewdatabase"/></resource>

sample method to test connection:

view plainprint?

1. ...  
2. public String getEmployers() {  
3.   try {  
4.     InitialContext ic = new InitialContext();  
5.     DataSource ds = (DataSource)   ic.lookup("java:comp/env/jdbc/mynewdatabase");  
6.     Connection con = ds.getConnection(); //NO User and Password, we set it in Resource  
7.     Statement st = con.createStatement();  
8.   
9.     ResultSet rs = st.executeQuery("SELECT * FROM employers");  
10.   
11.     String ret = "Employers ";  
12.     while (rs.next()) {  
13.       ret += rs.getString("name") + ",";  
14.     }  
15.     return ret;  
16.   } catch (SQLException ex) {  
17.     return ex.getMessage();  
18.   } catch (NamingException ex) {  
19.     return ex.getMessage();  
20.   }  
21. }  
22. ...  

...
public String getEmployers() {
  try {
    InitialContext ic = new InitialContext();
    DataSource ds = (DataSource)   ic.lookup("java:comp/env/jdbc/mynewdatabase");
    Connection con = ds.getConnection(); //NO User and Password, we set it in Resource
    Statement st = con.createStatement();

    ResultSet rs = st.executeQuery("SELECT * FROM employers");

    String ret = "Employers ";
    while (rs.next()) {
      ret += rs.getString("name") + ",";
    }
    return ret;
  } catch (SQLException ex) {
    return ex.getMessage();
  } catch (NamingException ex) {
    return ex.getMessage();
  }
}
...

If you have troubles, write its in comments and I try to help you.