MySQL Datasource in jsp

•

What is DaniWeb IT Discussion Community?

You're currently browsing the JSP section within the Web Development category of DaniWeb, a massive community of 428,635 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,078 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our JSP advertiser: Lunarpages JSP Web Hosting

Thread Tools Display Modes

Views: 2256 | Replies: 2 | Solved

•

Join Date: Jan 2008

Posts: 35

Reputation: electron33 is an unknown quantity at this point

Rep Power: 1

Solved Threads: 4

electron33 electron33 is offline

Offline

Light Poster

MySQL Datasource in jsp

Feb 3rd, 2008

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.

•

Join Date: Feb 2008

Posts: 2

Reputation: Openlink is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

Openlink

Offline

Newbie Poster

Re: MySQL Datasource in jsp

Feb 6th, 2008

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.

•

Join Date: Jan 2008

Posts: 35

Reputation: electron33 is an unknown quantity at this point

Rep Power: 1

Solved Threads: 4

electron33 electron33 is offline

Offline

Light Poster

Re: MySQL Datasource in jsp

Feb 6th, 2008

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:

Help with Code Tags

(Toggle Plain Text)

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

Help with Code Tags

(Toggle Plain Text)

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

Help with Code Tags

(Toggle Plain Text)

<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

Help with Code Tags

(Toggle Plain Text)

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