воскресенье, 28 сентября 2008 г.

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:

<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:

<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:
...
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.

2 комментария:

Анонимный комментирует...

Hi...
I have a problem with mysql SQL syntax: simple SELECTs work good, but something like "SELECT count(*) from my_table where a=1 and b=2" doesn't work...

I'm using:
Netbeans 6.5 MySQL 5.1 connector/J 5.1.6 Servlets

didn't configure anything - just using netbeans with tomcat and mysql library in project properties.

dimka комментирует...

I think that you must using HAVING instead of WHERE. See: http://dev.mysql.com/doc/refman/5.0/en/select.html Can you write error in comment?

Most popular

Authors