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: