0 Replies Latest reply on Jul 9, 2012 12:36 PM by prashid

    Error using JNDI datasouce on JBoss 7 for MySQL

    prashid

      I have spent many hrs try to fix the problem but no success. Googled and raed many related articles. followed this https://community.jboss.org/wiki/DataSourceConfigurationInAS7 also.

       

      I want to connect MySQL database using JNDI dataSource on JBoss to reterive some data.

       

      Someone please help.

       

      Error is generated as soon as I run test.jsp:

       

          javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver found for java:/jdbc/bookstore"

              org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:862)

              org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)

              org.apache.jsp.test_jsp._jspService(test_jsp.java:90)

              org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)

              javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

              org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:369)

              org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:326)

              org.apache.jasper.servlet.JspServlet.service(JspServlet.java:253)

              javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

       

      This is what I got

       

      MySQL Server 5.5:

          database -> bookstore

          table -> book

         

          connector -> mysql-connector-java-5.1.21-bin.jar

          container -> jboss-as-7.1.1.Final

          IDE -> Eclipse juno

       

      STEP 1 - JBoss:

       

      [JBoss_Home]/modules/com/mysql/main -> Got files below

       

          module.xml

          mysql-connector-java-5.1.21-bin.jar

          mysql-connector-java-5.1.21-bin.jar -> this auto created

       

       

      module.xml contain:

       

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

          <module xmlns="urn:jboss:module:1.0" name="com.mysql">

            <resources>

              <resource-root path="mysql-connector-java-5.1.21-bin.jar"/>

            </resources>

            <dependencies>

              <module name="javax.api"/>

            </dependencies>

          </module>

       

      STEP 2 JBoss:

       

      [JBoss_Home]/standalone/configuration/standalone.xml

       

      standalone.xml added dataSource as below:

       

          <subsystem xmlns="urn:jboss:domain:datasources:1.0">

                      <datasources>

                          <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">

                              <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>

                              <driver>h2</driver>

                              <security>

                                  <user-name>sa</user-name>

                                  <password>sa</password>

                              </security>

                          </datasource>

                          <datasource jta="true" jndi-name="java:/jdbc/bookstore" pool-name="bookstore_pool" enabled="true" use-java-context="true" use-ccm="true">

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

                              <driver>mysql</driver>

                              <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                              <pool>

                                  <min-pool-size>10</min-pool-size>

                                  <max-pool-size>100</max-pool-size>

                                  <prefill>true</prefill>

                              </pool>

                              <security>

                                  <user-name>root</user-name>

                                  <password>786</password>

                              </security>

                              <statement>

                                  <prepared-statement-cache-size>32</prepared-statement-cache-size>

                                  <share-prepared-statements>true</share-prepared-statements>

                              </statement>

                          </datasource>

                          <drivers>

                              <driver name="mysql" module="com.mysql">

                                  <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                              </driver>

                              <driver name="h2" module="com.h2database.h2">

                                  <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                              </driver>

                          </drivers>

                      </datasources>

                  </subsystem>

       

      STEP 3 - Eclipse Project:

       

      test.jsp page:

       

          <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

             

         

          <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

          <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

         

          <sql:query var="rs" dataSource="java:/jdbc/bookstore">

          select title,description from book

          </sql:query>

         

          <html>

            <head>

              <title>DB Test</title>

            </head>

            <body>

         

            <h2>Results</h2>

           

          <c:forEach var="row" items="${rs.rows}">

              Foo ${row.title}<br/>

              Bar ${row.description}<br/>

          </c:forEach>

         

            </body>

          </html>

       

      Add in web.xml:

       

          <!-- JDBC DataSources (java:comp/env/jdbc) -->

              <resource-ref>

                  <description>The used datasource</description>

                  <res-ref-name>jdbc/bookstore</res-ref-name>

                  <res-type>javax.sql.DataSource</res-type>

                  <res-auth>Container</res-auth>

              </resource-ref>

       

      jboss-web.xml contains:

       

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

          <jboss-web>

              <resource-ref>

                  <res-ref-name>jdbc/bookstore</res-ref-name>

                  <jndi-name>java:/jdbc/bookstore</jndi-name>

                  <res-type>javax.sql.DataSource</res-type>

                  <res-auth>Container</res-auth>

              </resource-ref>

          </jboss-web>

       

      Below is the project structure:

       

      pro.gif