MySQL Datasource in jsp

[electron33]

Hi.
I have a little problem. i tries to connect to a datasource defined in Tomcat context.
But when i run the jsp page i got an error telling that the driver i not found.
I'm using the mysql jar 5.0.x whitch i have placed under Catalina common lib folder in Windows. I have done it like this in the Context

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
	maxActive="100" maxIdle="30" maxWait="10000"
	username="root" password="root" driverClassName="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/test?autoReconnect=true"/>

and have

added a reference in the web.xml like this:

<resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/TestDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

executes the query like this in jsp

<sql:query var="rs" dataSource="jdbc/TestDB" sql="SELECT * FROM EMPLOYEE"/>
    
    <c:forEach var="row" items="${rs.rows}">
        ${row.username}
        ${row.password}
    </c:forEach>

Is this correct? Can some one help me with this problem.

[Openlink]

Hello,

What is the full text of the error message that you are receiving?

I suspect this is a CLASSPATH issue. Add the full path to the driver file to both the USER and SYSTEM CLASSPATH. If there are multiple entries in those variables, add the driver to the beginning of the path. Then run "java com.mysql.jdbc.Driver" in a DOS box and see if the command returns driver version info.

[electron33]

I think i have solved this problem. You can add MySQL driver in the class path and then get the connection with parameters set as parameters in web.xml or you can create a datasource in web.xml and call this in code. I have tried both and it worked.

If some one else have trouble with connection they can try the following:

Solution 1:

<context-param>
        <param-name>driverClass</param-name>
        <param-value>com.mysql.jdbc.Driver</param-value>
    </context-param>
    <context-param>
        <param-name>databaseUrl</param-name>
        <param-value>jdbc:mysql://localhost:3306/test</param-value>
    </context-param>
    <context-param>
        <param-name>user</param-name>
        <param-value>root</param-value>
    </context-param>
    <context-param>
        <param-name>password</param-name>
        <param-value>root</param-value>
    </context-param>

Call it from Servlet as getServletContext().getInitParameter("databaseUrl") and put it into a string.
Here you must add MySQL Driver jar file in ClassPath

Solution 2:

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

Have only tried this from JSP page.

<sql:query dataSource="jdbc/TestDB" var="rs" >
            SELECT * FROM EMPLOYEE
</sql:query>

Here you can add the Jarfile in Tomcat Catalina/common/lib dir.
You must then create at reference in server.xml under conf folder
Put following Resource inside GlobalNamingResources

<Resource
      name="jdbc/TestDB"
      type="javax.sql.DataSource"
      maxActive="4"
      maxIdle="2"
      username="root"
      maxWait="5000"
      driverClassName="com.mysql.jdbc.Driver"
      password="root"
      url="jdbc:mysql://localhost:3306/test"/>

You should now have a reference to MySQL database.