Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

problem in jdbc-tomcat-mysql connection

843854May 14 2005

hi everybody

i m in serious problem since last 15-20 days. i m trying hard to make the connection jdbc-mysql using tomcat with the help of a jsp test page but every time i am facing almost the similar probems listed below in detail:

to make sure that the jdrivers's .jar (mysql-connector-java-3.1.7-bin.jar) available for such connections working fine i have tried the above said connection without using tomcat....which is working fine...for this i have created a dummy database with a dummy table ..and written some queries in the .java file of my code which upon running inserts the specified values in the defined(dummy) database.

this is the confirmation that the classpath etc..are ok in regard to the mysql-connector-java-3.1.7-bin.jar

now i have placed a copy of this .jar file in to the commons/lib directory of the tomcat web server(which is suppossed to be the place for such external jars)
after doing so i have followed 2 methods :

method 1st:........

i have made aone more jar file from the following .java file....

import javax.naming.*;
import javax.sql.*;
import java.sql.*;

public class TestSQLLoad
{

String user = "Not Connected";
String pwd = "no pwd";
int id = -1;

public void init() {
try{
Context ctx = new InitialContext();
if(ctx == null ) {
throw new Exception("Boom - No Context");
}

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

if (ds != null) {
Connection conn = ds.getConnection();

if(conn != null) {
user = "Got Connection "+conn.toString();
Statement stmt = conn.createStatement();
String q = "select name, password, id from user";
ResultSet rst = stmt.executeQuery(q);
if(rst.next()) {
user=rst.getString(1);
pwd=rst.getString(2);
id = rst.getInt(3);
}
conn.close();
}
}
}catch(Exception e) {
e.printStackTrace();
}
}

public String getUser() {
return user;
}

public String getPassword() {
return pwd;
}

public int getID()
{
return id;
}
}

after compiling it at the cmd prompt i made a .jar file from the .class of it and named it MySQLLoad.jar...then i placed it also in to the commons/lib directory where the mysql-connector-java-3.1.7-bin.jar was already placed.

then i have changed the server.xml and web.xml files from the conf directory of the tomcat as given here:

server.xml






<Server port="8005" shutdown="SHUTDOWN">


<Listener className="org.apache.catalina.mbeans.ServerLifecycleListener" />
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />


<GlobalNamingResources>


<Environment name="simpleValue" type="java.lang.Integer" value="30"/>


<Resource name="UserDatabase" auth="Container"
type="org.apache.catalina.UserDatabase"
description="User database that can be updated and saved"
factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
pathname="conf/tomcat-users.xml" />

</GlobalNamingResources>




<Service name="Catalina">




<Connector
port="80" maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="100"
connectionTimeout="20000" disableUploadTimeout="true" />








<Connector port="8009"
enableLookups="false" redirectPort="8443" protocol="AJP/1.3" />










<Engine name="Catalina" defaultHost="localhost">







<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
resourceName="UserDatabase"/>













<Host name="localhost" appBase="webapps"
unpackWARs="true" autoDeploy="true"
xmlValidation="false" xmlNamespaceAware="false">















<Context path="/testdb" docBase="TOMCAT_HOME/webapps/tomcat-docs/testdb"
debug="5" reloadable="true" crossContext="true" >

<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_DBTest_log." suffix=".txt"
timestamp="true"/>

<Resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"/>

<ResourceParams name="jdbc/TestDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>


<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>


<parameter>
<name>maxIdle</name>
<value>5</value>
</parameter>


<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>


<parameter>
<name>username</name>
<value>root</value>
</parameter>
<parameter>
<name>password</name>
<value>mysql</value>
</parameter>


<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>


<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/testdb?autoReconnect=true</value>
</parameter>
</ResourceParams>
</Context>

</Host>

</Engine>

</Service>

</Server>

and the other xml file i.e
web.xml is here:

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">




















































<servlet>
<servlet-name>default</servlet-name>

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

<servlet-class>org.apache.catalina.servlets.DefaultServlet</servlet-class>
<init-param>
<param-name>debug</param-name>
<param-value>0</param-value>
</init-param>
<init-param>
<param-name>listings</param-name>
<param-value>true</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>














<servlet>
<servlet-name>invoker</servlet-name>
<servlet-class>
org.apache.catalina.servlets.InvokerServlet
</servlet-class>
<init-param>
<param-name>debug</param-name>
<param-value>0</param-value>
</init-param>
<load-on-startup>2</load-on-startup>
</servlet>











<!--

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 11 2005

Added on May 14 2005

#java-database-connectivity-jdbc

0 comments

362 views