Connecting to a Tomcat Server with MySQL in JSP

Question

So I'm trying to get some tutorial code working, and I'm stuck. I have a web application in Eclipse which consists of a DBConnector.java file, a Tomcat 6 server (local), and a short JSP script:

package com.atj.db; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; public class DBConnector { private static DBConnector connector_ = null; public static DBConnector getInstance() throws Exception { if (connector_ == null) { connector_ = new DBConnector(); } return connector_; } public Connection getConnection() throws Exception { // Get DataSource Context ctx = new InitialContext(); DataSource ds = (DataSource) ctx .lookup("java:comp/env/jdbc/mydatabase"); Connection c = ds.getConnection(); return c; } public String getFirstName() throws Exception { String first = ""; try { Connection con = getConnection(); Statement st = con.createStatement(); ResultSet res = st.executeQuery("SELECT * FROM employees"); while (res.next()) { first = res.getString("first"); System.out.println(first); } con.close(); } catch (SQLException s) { System.out.println("SQL code does not execute."); } return first; } }

I'm trying to post the JSP, but StackOverflow is parsing my HTML...argh..sorry..how do I escape this? Here is the tutorial code:

http://jspjdbc.blogspot.com/

It's a very short JSP script with the 2 main lines:

DBConnector dbConn = DBConnector.getInstance(); String name = dbConn.getFirstName(); <---error line

Anyway, when I open this in a browser, it prints "SQL Code does not execute" to the console. I can see this happens when an SQLException gets thrown. I debugged this down to this line in getConnection:

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

After executing this line, ctx is filled with mostly null values (except for id)

And then this line:

Connection c = ds.getConnection();

throws the SQLException, and control is immediately handed to the catch block.

Also, Eclipse is giving me a warning on this import, saying "The type DataSource is not accessible due to a restriction on the required library /System/Library/Frameworks/JavaVM.framework/Versions/1.4.2/Classes/classes.jar

import javax.sql.DataSource; //<---Eclipse is complaining about this line, but still compiling

Here is the project tree:

project Java Resources: src com.atj.project DBConnector.java Libraries EAR Libraries JRE System Library Web App Libraries mysql-connector-java-5.1.14-bin.jar Web Content META-INF content.xml MANIFEST.MF Web-INF lib mysql-connector-java-5.1.14-bin.jar web.xml db.jsp Servers Tomcat v6.0 server at localhost-config catalina.policy catalina.properties content.xml server.xml tomcat-users.xml web.xml

Any help is greatly appreciated.

Edit-Here are my .xml files

content.xml

<?xml version="1.0" encoding="UTF-8"?> <Context> <!-- Specify a JDBC datasource --> <Resource name="jdbc/mydatabase" auth="Container" type="javax.sql.DataSource" username="demo" password="demo" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://www.coincan.net:3306/demo?autoReconnect=true" validationQuery="select 1" maxActive="2" maxIdle="1"/> </Context>

(new)web.xml

<?xml version="1.0" encoding="UTF-8"?> <!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 id="WebApp_ID"> <display-name>db</display-name> <welcome-file-list> <welcome-file>index.html</welcome-file> <welcome-file>index.htm</welcome-file> <welcome-file>index.jsp</welcome-file> <welcome-file>default.html</welcome-file> <welcome-file>default.htm</welcome-file> <welcome-file>default.jsp</welcome-file> </welcome-file-list> <resource-ref> <description>mydatabase</description> <res-ref-name>jdbc/mydatabase</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope>

I really don't know xml, so unfortunately, the web.xml and context.xml are only vaguely familiar to me, and I have no idea why the connection is failing. — prelic
– prelic, Commented Jan 14, 2011 at 2:10

duffymo · Accepted Answer · 2011-01-14 01:37:27Z

I think you're still pretty far off.

You don't say if you have a META-INF/context.xml file to set up the data source pool in Tomcat; you need one.

You don't say if you declare the data source in your web.xml; you should.

See if those sort you out.

You might also want to read this. I think the JNDI name ought to be "jdbc/mydatabase".

You certainly aren't closing your resources properly in your method; you don't close the ResultSet at all, and you should close everything in a finally block.

Add this at the end of your web.xml:

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

Yes, sorry, I have a content.xml. I'll edit my question to include it. I haven't modified the web.xml file. How does the data source declaration look in the web.xml file? I'm fairly certain it's not there.
That solved the http:500 error. Now the code is echoing "SQL Code does not execute" to the console, which I can see in the Java code is called when a SQLExcelption is thrown. I set a breakpoint and examined the exception, and the detailed message says: "Cannot create JDBC driver for class" for connect url 'null'"id=65. Any idea why the driver is being created?
Where is the context.xml located? And is the MySQL connector JAR in the Tomcat /lib directory? Tomcat requires that it be available to the server now.
I edited my question to add the file tree for the whole project

Collectives™ on Stack Overflow

Connecting to a Tomcat Server with MySQL in JSP

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related