Jsp not connect to mysql
please can someone tell me why i am an able to connect to mysql.
this sample code below i have used to query mysql
<HTML>
<HEAD><TITLE>Employee List</TITLE></HEAD>
<BODY>
<%@ page import="java.sql.*" %>
<TABLE BORDER=1 width="75%">
<TR><TH>Last Name</TH><TH>First Name</TH></TR>
<%
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/cartapp","user","user");
st = conn.createStatement();
rs = st.executeQuery("select * from employees");
while(rs.next()) {
%>
<TR><TD><%= rs.getString("lname") %></TD>
<TD><%= rs.getString("fname") %></TD></TR>
<%
}
%>
</TABLE>
<%
} catch (Exception ex) {
ex.printStackTrace();
%>
</TABLE>
Error occur at runtime (Could not connect to mysql)!!!!!
<%
} finally {
if (rs != null) rs.close();
if (st != null) st.close();
if (conn != null) conn.close();
}
%>
</BODY>
</HTML>
but i keep getting this error
11:03:51,489 ERROR [STDERR] java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
11:03:51,489 ERROR [STDERR] at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1355)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1201)
11:03:51,489 ERROR [STDERR] at org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:127)
11:03:51,489 ERROR [STDERR] at org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:65)
11:03:51,489 ERROR [STDERR] at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
11:03:51,489 ERROR [STDERR] at java.lang.Class.forName0(Native Method)
11:03:51,489 ERROR [STDERR] at java.lang.Class.forName(Class.java:164)
11:03:51,489 ERROR [STDERR] at org.apache.jsp.example1_jsp._jspService(example1_jsp.java:55)
11:03:51,489 ERROR [STDERR] at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
11:03:51,489 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
11:03:51,489 ERROR [STDERR] at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
11:03:51,489 ERROR [STDERR] at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
11:03:51,489 ERROR [STDERR] at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
11:03:51,489 ERROR [STDERR] at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
11:03:51,489 ERROR [STDERR] at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
11:03:51,489 ERROR [STDERR] at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
11:03:51,489 ERROR [STDERR] at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
11:03:51,489 ERROR [STDERR] at org.jboss.web.tomcat.tc5.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:156)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
11:03:51,489 ERROR [STDERR] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
11:03:51,489 ERROR [STDERR] at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
11:03:51,489 ERROR [STDERR] at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
11:03:51,489 ERROR [STDERR] at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
11:03:51,489 ERROR [STDERR] at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
11:03:51,489 ERROR [STDERR] at java.lang.Thread.run(Thread.java:595)
please what do u think is wrong?
thanks
[5301 byte] By [
kengimela] at [2007-11-26 21:16:21]
# 1
Hi KengimelIn Connection con = DriverManager.getConnection( "jdbc:odbc:Datasource" );Use data source name or the connection string instead of the URL..
# 2
@ chellam thanks for that but i have already tried that before posting.the problem i still can't understand where it is coming from.
# 3
11:03:51,489 ERROR [STDERR] java.lang.ClassNotFoundException: com.mysql.jdbc.Driver you have to add the jar file containing the driver class in your lib folder
# 4
do you mean the lib folder on my application server ?or where exactlythanks
# 5
Hi,
I've exactly the same problem;
I'm developing a jsp/servlet application with database mysql in Eclipse.
In all my project in Eclipse with DB, I simply add JConnector jar file in buildpath of the project and all works fine.
But with this JSP application, I've added JConnector jar file in buildpath, but it doesn't work;
I obtain the same error as kengimel:
ClassDefNotFound: com.mysql.jdbc.Driver
I've solved in a more drastic way, that is I've added JConnector jar file, directly in my java\jre6.0\lib\ext\ folder.
Now all work, but why adding jar to buildpath doesn't work?
At the end, I want to add all my project components in a single jar file, including also JConnector.
But this jar file won't works if JConnector doesn't work when imported in buildpath.
please help us!
Thank you in advance
MargNat
# 6
For MySQL , you need an additional JAR file :
aspectjrt.jar (look for this JAR file in the MySQL JDBC Connector ZIP file)
mysql-connector-java-5.0.5-bin.jar
Download mysql-connector-java-5.0.5.zip and unzip the file and copy the above two JAR files in to your projects lib folder.
Then try a simple JDBC test from a Java Class like this:
package jdbcTest;
import java.sql.*;
public class TestJdbcConnection {
public static void main(String[] args) {
TestJdbcConnection tjc = new TestJdbcConnection();
try {
tjc.testConnection();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException cnfe) {
cnfe.printStackTrace();
}
}
private void testConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
//jdbc:mysql://host_name:port/dbname
/*
This requires MySQL Connector JAR file: mysql-connector-java-5.0.5-bin.jar and
aspectjrt.jar - Make sure that both JAR files are visible in the classpath
used by whatever tool (IDE, Command line etc) is used to run this main.
*/
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_schema_name", "username", "password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT 'testing 123' as a FROM dual");
while (rs.next()) {
String s = rs.getString("a");
System.out.println(" s is : " + s);
}
rs.close();
stmt.close();
con.close();
}
}
In the above code replace database_sechma_name, username and password with yours.
Message was edited by:
appy77
# 7
you have to place the jar file in the lib directory of your applicationWEB-INFsrcclassesslib - -here you have top place the jar file then try the code appy provided
# 8
No, you don't undersood;
the code posted by appy77 works fine.
It doesn't work when I use it in a JSP application on Tomcat apache server.
In this case, it works only if I put mysql-connector jar in my java\jre6.0\ext\lib\ folder
This problem looks like tomcat servlet classes (that are in jar files) doesn't see mysql-connector classeS (that are in other jar file).
I could have said a nonsense.
Thank you in advance
MargNat
# 9
Hi,
First you have to install jconnector jar file in Java\jre1.5.0_06\lib\ext\mysql-connector-java-3.1.14-bin.jar file. You can download this jar at mysql site. It is free of cost. After that You try this code.
import java.sql.*;
public class JdbcExample1 {
public static void main(String args[]) {
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql:///test", "root", "letus");
if(!con.isClosed())
System.out.println("Successfully connected to MySQL server...");
} catch(Exception e) {
System.err.println("Exception: " + e.getMessage());
} finally {
try {
if(con != null)
con.close();
} catch(SQLException e) {}
}
}
}
with Regards
sure...:)-
# 10
I think i know a large quantity of the problem there.
You need to know about the CLASS Loader Tree for
Web applications. Your using the "System classloader"
by default if it finds the driver jar in /ext/lib. You want
either the application loader (you get by calling
Class.forName() ignore this bit about it here) or the
"current thread context" loader and particularly if you
are trying to call it from a jsp page of i see some of the
required exceptions are not declared of one is in the error
output as the first line.
"current context class loader":
java.lang.Thread currentThread()getContextClassLoader()
Also interfaces ( java.sql.Driver ) are a data type so i found
if i cast it it operates.
You can find about class loader tree in the "Tomcat docs"
in their "Class loader HOW TO".
For most of the example code and the exceptions to throw
go to my site http://www.nicephotog-jvm.net/signup-signin.html
and get the code/information source from the zip
called QueryUserRsltstMysql_binaries_and_source.zip
# 11
Thank you nicephotog!
you've understood well what's my problem.
I've solved;
I try to explain for all.
Practically, I've read carefully tomcat classloader doc;
then I've tried to execute my project externally to Eclipse;
I've created a new dir named "myProject" in webapps dir of Tomcat installation directory
(that is ..\apache software foundation\Tomcat 5.5\webapps\ )
then, I've created in it, the following folder:
META-INF
WEB-INF
WEB-INF\lib
WEB-INF\classes
I've copied jsp files and other multimedial files of my eclipse project into "myProject" folder I've created initially (According to my web.xml file);
I've copied web.xml file into WEB-INF dir;
I've copied class files of my eclipse project into WEB-INF\classes
I've copied mysql-JConnector jar file into WEB-INF\lib
That'all folks!
Then
start tomcat
open firefox and go to url:
localhost:8080/myProject
All works fine!
It is possible to create a single jar file that can be used on every PC, with tomcat and jvm installed.
To create a jar file, in the prompt, go to tomcat webapps dir, then go in myProject dir and do:
jar cf ../myProject.war *
In Eclipse, if you import JConnector jar in buildpath as an inside jar or external jar, it doesn't work with a JSP application.
I think, but I'm not sure, this is due to the fact eclipse create a its own configuration for tomcat server;
in fact in the project list, it is possible to see the server folder that contains the files:
server.xml
tomcat-users.xml
web.xml
I hope this is useful for someone.
Thank you again;
MargNat
# 12
Hi,
Try this code.
<%@ page contentType="text/html;charset=windows-1252" import="java.sql.*,com.mysql.jdbc.Driver"%>
<%
try
{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/emp?user=root&password=letus");
System.out.println("success");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from emp");
while(rs.next())
{
out.println(rs.getInt(1)+"<br/>");
out.println(rs.getString(2)+"<br/>");
out.println(rs.getInt(3)+"<br/>");
out.println(rs.getString(4)+"<br />");
}
} catch (Exception ex)
{
ex.printStackTrace();
}
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>untitled</title>
</head>
<body>
hello
</body>
</html>
with Regards
sure...:)-
# 13
Hi,folks.
I have encountered the same problem.now I have copy both mysql-connector-java-5.0.5-bin.jar and aspectjrt.jar to the WEB-INF\lib of my project directory.but after I run the program ,it generated following exception:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: java.security.AccessControlException: access denied (java.net.SocketPermission 127.0.0.1:3306 connect,resolve)
......................................................................
This exception has been debug,it just dued to the statement :
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/test","user","password");
Can anybody help me to solve it ? Please reply as quickly as possible.
Thank you for kind attention and help.
# 14
@Devemor: why are you crossposting this problem in another thread?Please continue here: http://forum.java.sun.com/thread.jspa?threadID=5150877&tstart=0 =)
# 15
Hello.I think maybe your mysql connector's settings are wrong.
# 16
Dude .. If you have read the original thread I have linked here above, you'd concluded that the problem is already resolved ;)
# 17
Sorry!I'm going to fix it soon. Hehe
# 18
Hi all,
I think I have a similar problem. I have intalled next applications and versions:
- Tomcat 4.1
- MySQL Server 5.0
- J2SDK 1.4.2
- JDBC Controller -> mysql-connector-java-3.1.13-bin.jar (installed in Java\jre1.5.0_06\lib\ext\mysql-connector-java-3.1.13-bin.jar)
The code posted by appy77 works fine.
I will try with the example in Apache Website:
http://tomcat.apache.org/tomcat-4.1-doc/jndi-datasource-examples-howto.html
- I have configured the database as the example shown:
mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost
->IDENTIFIED BY 'javadude' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
->id int not null auto_increment primary key,
->foo varchar(25),
->bar int);
- I have included in the configuration file $CATALINA_HOME/conf/server.xml; as the example shown:
<Context path="/DBTest" docBase="DBTest"
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>100</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>username</name>
<value>javauser</value>
</parameter>
<parameter>
<name>password</name>
<value>javadude</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/javatest?autoReconnect=true</value>
</parameter>
</ResourceParams>
</Context>
- I have included in the configuration file WEB-INF/web.xml ; as the example shown:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
<description>MySQL Test App</description>
<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>
</web-app>
- Finally I have created next code in order to test my datasource:
1.- test.jsp
<html>
<head>
<title>DB Test</title>
</head>
<body>
<%
foo.DBTest tst = new foo.DBTest();
tst.init();
%>
<h2>Results</h2>
Foo <%= tst.getFoo() %>
Bar <%= tst.getBar() %>
</body>
</html>
2.- DBTest.java
package foo;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
public class DBTest {
String foo = "Not Connected";
int bar = -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) {
foo = "Got Connection "+conn.toString();
Statement stmt = conn.createStatement();
ResultSet rst =
stmt.executeQuery(
"select id, foo, bar from testdata");
if(rst.next()) {
foo=rst.getString(2);
bar=rst.getInt(3);
}
conn.close();
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
public String getFoo() { return foo; }
public int getBar() { return bar;}
}
*****************************************************************************
The result in the browser with http://localhost:8080/DBTest/test.jsp was:
Foo Not Connected
Bar -1
that means that (in DBTest.java) DataSource is null:
DataSource ds =
(DataSource)ctx.lookup(
"java:comp/env/jdbc/TestDB");
Next step, I have tried with the example of sure_2912, and it worked fine.
I dont know why tha application works when the functions: Class.forName("com.mysql.jdbc.Driver") and Connection con = DriverManager.getConnection("jdbc:mysql://localhost/emp?user=root&password=letus"); are inside JSP files and it doesnt when are in java files. I think the problem is in configuration files: web.xml; server.xml, etc.
Can anybody help me please?
Thanks in advanced.
egonztri
# 19
One quick check you could do is see if your server.xml and web.xml is *valid and well formed XML*.
> and it doesnt when are in java files. I think the
> problem is in configuration files: web.xml;
> server.xml, etc.
One more thing to check. Check if you have nested the Context node in server.xml under the Host node. Many people forget this step.
If you call Connection Pooling JDBC code from the command line it will not work because, all configuration is inside the web container.
So the Connection Pooling related Java code needs to be called from either a Servlet or a JSP , after the Web Container starts.
But since it is bad programming practice to write JDBC code in JSPs , it is better to create DataAccessObjects and then call DAOs from the Servlets.... but first get your basic example working according to the tutorial instructions.
> Can anybody help me please?
>
> Thanks in advanced.
>
> egonztri
# 20
Thanks for your answer.
>Check if you have nested the Context node in server.xml under the >Host node.
I have included the API definition (Context, allocation, DataSource) in the server.xml file that Tomcat had at the right location under:
<Engine className="org.apache.catalina.core.StandardEngine" debug="0" defaultHost="localhost" mapperClass="org.apache.catalina.core.StandardEngineMapper" name="Standalone">
<Host className="org.apache.catalina.core.StandardHost" appBase="webapps" autoDeploy="true"......
..........................................
></Host>
And Web.xml is defined as following:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
<description>MySQL Test App</description>
<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>
</web-app>
>server.xml and web.xml is *valid and well formed XML*.
I have seen something strange ? in the head of the file server.xml:
<?xml version='1.0' encoding='utf-8'?> when in the file Web.xml is defined as: <?xml version="1.0" encoding="ISO-8859-1"?>
Also, I have defined a new configuration file webapps folder: DBTest.xml where is defined the context, logger, Datasource as I ave shown in the topic before, but it doesnt work either. Maybe, Do I have to reference this file in anywhere else?
Thank you
# 21
I have found the trouble to this issue:
http://www.developer.com/java/data/print.php/10932_2172891_3
I have download both *.jar files:
commons-dbcp.jar
commons-pool.jar
inside <CATALINA_HOME>/common/lib/
and the example works!!!!
Thanks everydody.
Regards
egonztri
# 22
Glad you got it working.
A few things to note though...
The connection pooling setting is different for I think Tomcat 4.x and lower , and Tomcat 5.x and higher.
So follow the instructions carefully for your version of Tomcat.
The official instructions are here:
Tomcat 4.x JNDI :
http://tomcat.apache.org/tomcat-4.1-doc/jndi-datasource-examples-howto.html
Tomcat 5.x JNDI
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html
I tested the JNDI instructions for Tomcat 6.0.x
http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
It no longer requires commons-dbcp.jar or commons-pool.jar , because by default Tomcat 6.0.x comes with tomcat-dbcp.jar in the lib folder.
When in doubt, start with the fresh installation of your Tomcat version and then , follow the official instructions *for that version*.
# 23
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/test","user","password");
well u need tyo use port no also...
("jdbc:mysql:3306/localhost/test","user","password");
plz copy ur jconnector jar file in ur application lib folder or ur tomcate lib folder...if still u face same problem
then try
Class.forName(org.git.mm.mysql.Driver) in place of
Class.forName(com.j................)
Bye
# 24
> Connection
> conn=DriverManager.getConnection("jdbc:mysql://localho
> st/test","user","password");
>
> well u need tyo use port no also...
> ("jdbc:mysql:3306/localhost/test","user","password");
>
> plz copy ur jconnector jar file in ur application lib
> folder or ur tomcate lib folder...if still u face
> same problem
> then try
> Class.forName(org.git.mm.mysql.Driver) in place of
> Class.forName(com.j................)
>
> Bye
The above instruction won't work for a Connection Pooling set-up, they will only work if you don't use connection pooling.
