Error using JNDI datasouce on JBoss 7 for MySQL

Question

I followed these instructions.

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

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 contains:

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

Answer 1

This worked for me on 7.1.1.Final without any problems. Here is what my setup looked like.

${JBOSS_HOME}/modules/com/mysql/main/modules.xml:

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="com.mysql">
  <resources>
      <resource-root path="mysql-connector-java-5.1.21-bin.jar"/>
  </resources>
  <dependencies>
      <module name="javax.api" />
  </dependencies>
</module>

In the standalone.xml:

<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/test" pool-name="test_pool" enabled="true" use-java-context="true" use-ccm="true">
            <connection-url>jdbc:mysql://localhost:3306/as7test</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>user</user-name>
                <password>mypass</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>

That worked for me with no errors. I didn't try to make a connection through any code, but I did test the connection on the web admin console.

Edit: Try adding <module name="javax.transaction"/> to your dependencies. There was a bug at one point where that was not added by default and I see you are using an XA data source.