Data Source Connection with MySQL
lovetowork Jul 10, 2012 10:31 AMI have been working my way through the book "Beginning Java EE 5" using eclipse and jboss 7.0 with mysql. In Chap 7 they have an example that is using a DataSource and connection in a JSP. I want to have this JSP connect to MySQL database. I created a database called DataSourceExample and added a table called CUSTOMER_TBL to it with three fields and two records. I can access the data using a simple jave app but when I run the jsp project it aborts with the error seen below the code. I am missing something somewhere.
web.xml file
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
version="2.5" >
<display-name>DataSourceExample</display-name>
<servlet>
<display-name>DataSourceExample</display-name>
<servlet-name>DataSourceExample</servlet-name>
<jsp-file>/DataSourceExample.jsp</jsp-file>
</servlet>
<resource-ref>
<res-ref-name>jdbc/DataSourceExample</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
<welcome-file-list>
<welcome-file>DataSourceExample.jsp</welcome-file>
</welcome-file-list>
</web-app>
my-ds.xml file
<datasources>
<local-tx-datasource>
<jndi-name>jdbc/DataSourceExample</jndi-name>
<connection-url>
jdbc:mysql://localhost:3306/DataSourceExample
</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password>1234</password>
</local-tx-datasource>
</datasources>
DataSourceExample.jsp file
<%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*" %>
<html>
<head>
<title>Data Source Example</title>
</head>
<body>
<h1>Data Source Example</h1>
<% InitialContext context = new InitialContext();
DataSource dataSource =
(DataSource) context.lookup("java:comp/env/jdbc/DataSourceExample");
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
conn = dataSource.getConnection();
stmt = conn.createStatement();
rset = stmt.executeQuery("select * from CUSTOMER_TBL");
if (rset.next()) {
%>
<table withd="100%" border="1">
<tr align="left">
<th>Customer Num</th><th>Name></th><th>email</th>
</tr>
<%
do {
%>
<tr>
<td><%= rset.getString("CUSTOMER_NUM") %></td>
<td><%= rset.getString("NAME") %></td>
<td><%= rset.getString("EMAIL") %></td>
</tr>
<%
} while (rset.next());
%>
</table>
<%
} else {
%>
No results from query
<%
}
} catch (SQLException e) {
%>
<%= e.getMessage() %>
<%
e.printStackTrace();
} finally {
if (rset != null) { rset.close(); }
if (stmt != null) { stmt.close(); }
if (conn != null) { conn.close(); }
if (context != null) { context.close(); }
}
%>
</body>
</html>
09:23:53,801 INFO [org.jboss.as.deployment] (DeploymentScanner-threads - 1) Found 07-exp1.war in deployment directory. To trigger deployment create a file called 07-exp1.war.dodeploy
09:23:53,835 INFO [org.jboss.as.server.deployment] (MSC service thread 1-2) Starting deployment of "07-exp1.war"
09:23:54,224 INFO [org.jboss.as.server.controller] (DeploymentScanner-threads - 2) Deployment of "07-exp1.war" was rolled back with failure message {"Services with missing/unavailable dependencies" => ["jboss.naming.context.java.module.07-exp1.07-exp1.env/jdbc/DataSourceExample.jboss.deployment.unit.\"07-exp1.war\".module.07-exp1.07-exp1.0 missing [ jboss.naming.context.java.module.07-exp1.07-exp1.env/jdbc/DataSourceExample ]","jboss.deployment.unit.\"07-exp1.war\".jndiDependencyService missing [ jboss.naming.context.java.module.07-exp1.07-exp1.env/jdbc/DataSourceExample ]"]}
09:23:54,237 INFO [org.jboss.as.server.deployment] (MSC service thread 1-1) Stopped deployment 07-exp1.war in 12ms
09:23:54,238 ERROR [org.jboss.as.deployment] (DeploymentScanner-threads - 1) {"Composite operation failed and was rolled back. Steps that failed:" => {"Operation step-2" => {"Services with missing/unavailable dependencies" => ["jboss.naming.context.java.module.07-exp1.07-exp1.env/jdbc/DataSourceExample.jboss.deployment.unit.\"07-exp1.war\".module.07-exp1.07-exp1.0 missing [ jboss.naming.context.java.module.07-exp1.07-exp1.env/jdbc/DataSourceExample ]","jboss.deployment.unit.\"07-exp1.war\".jndiDependencyService missing [ jboss.naming.context.java.module.07-exp1.07-exp1.env/jdbc/DataSourceExample ]"]}}}