3 Replies Latest reply on Jul 19, 2012 4:26 PM by Violet Harris

    Data Source Connection with MySQL

    Violet Harris Newbie

      I 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 ]"]}}}

        • 1. Re: Data Source Connection with MySQL
          Ray DeCampo Newbie

          Although you used the same name (jdbc/DataSourceExample) in both the web.xml and the my-ds.xml, they are actually not the same.  The resource name in the web.xml will be prefixed with java:comp/env and will not be a globally-scoped name.  So you need a jboss-web.xml to connect the logical name from the web.xml to a real name of a resource in the application server.

           

          Also, have you actually deployed your data source successfully?  Can you see it in the JNDI browser in the JBoss console?  I think JBoss will want you to prefix the name with java: (although I have been using 7.1.1 not 7.0).  Then the jboss-web.xml would be:

           

           

          <?xml version="1.0" encoding="UTF-8"?>
          <jboss-web xmlns="http://www.jboss.com/xml/ns/javaee"
                     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                     xsi:schemaLocation="http://www.jboss.com/xml/ns/javaee http://www.jboss.org/j2ee/schema/jboss-web_6_0.xsd"
                     version="6.0">
              <resource-ref>
                  <res-ref-name>jdbc/DataSourceExample</res-ref-name>
                  <jndi-name>java:/jdbc/DataSourceExample</jndi-name>
              </resource-ref>
          </jboss-web>
          
          • 3. Re: Data Source Connection with MySQL
            Violet Harris Newbie

            It is working now.

            1) I had a standalone.xml file in the jboss\standalone\configuration where I had a datasource described. I had the jndi-name set to java:jboss/datasources/MySqlDS2. I changed it to just MySqlDS2.

             

            2) My mysql-ds.xml file in the WEB-INF directory :

             

            <?xml version="1.0" encoding="UTF-8"?>

            <datasources>

                    <local-tx-datasource>

                    <jndi-name>MySqlDS2</jndi-name>

             

                    <connection-url>jdbc:mysql://localhost:3306/DataSourceExample</connection-url>

                    <driver-class>com.mysql.jdbc.Driver</driver-class>

                    <user-name>****</user-name>

                    <password>****</password>

             

                    <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>

                    <metadata>

                    <type-mapping>mySQL</type-mapping>

                    </metadata>

                    </local-tx-datasource>

             

            </datasources>

             

            3) my web.xml file in the WEB-INF directory

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

            <welcome-file-list>

            <welcome-file>DataSourceExample.jsp</welcome-file>

            </welcome-file-list>

            </web-app>