How to use JSTL sql tag

Question

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

<sql:setDataSource var="dataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/cloud" user="root"  password="root"
scope="session" /> 

<sql:query var="qryProvider" >
    SELECT * FROM `provider`;
</sql:query>

<table>
    <c:forEach var="row" items="${qryProvider.rows}">
        <tr>
            <td>${row.display_name}</td>

        </tr>
    </c:forEach>
</table>

I got the following error:

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /testJSTL.jsp at line 8

5: url="jdbc:mysql://localhost:3306/cloud" user="root"  password="root"
6: scope="session" /> 
7:  
8: <sql:query var="qryProvider" >
9:     SELECT * FROM `provider`;
10: </sql:query>
11:  


Stacktrace:
    org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:567)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:456)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
root cause

javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.lang.NullPointerException"
    org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:911)
    org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:840)
    org.apache.jsp.testJSTL_jsp._jspService(testJSTL_jsp.java:94)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:433)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

I tried to follow this tut, but it gives me an error if I write

<sql:query var="qryProvider" dataSource="${dataSource}" >

I'm new to this, could someone point me in the right direction?

score 11 · Accepted Answer · edited May 25 '13 at 15:17

11

My advice would be to forget about the <sql> tags completely, and to make all your database operations in plain Java (in a servlet or action of your preferred MVC framework). This servlet would build a list of bean instances, ready to be displayed by your JSP. Use RequestDispatcher to dispatch the request to the JSP from the servlet.

Even the official Java EE tutorial says:

The JSTL SQL tags for accessing databases listed in Table 7-7 are designed for quick prototyping and simple applications. For production applications, database operations are normally encapsulated in JavaBeans components.

edited May 25 '13 at 15:17

Arjan Tijms

37,782
12
108
140

answered Oct 08 '11 at 14:05

JB Nizet

678,734
91
1,224
1,255

An example of such a servlet can be found here: [Show JDBC ResultSet in HTML in JSP page using MVC pattern](http://stackoverflow.com/questions/5003142/show-jdbc-resultset-in-html-table-in-jsp-page-using-mvc-pattern)
– BalusC Sep 11 '15 at 11:44

score -2 · Answer 2 · answered Sep 11 '15 at 11:33

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


<sql:setDataSource var="dataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/cloud" user="root"  password="root"
scope="session" /> 


<sql:query var="qryProvider" dataSource="${dataSource}">

    SELECT * FROM provider;
</sql:query>

<table>
    <c:forEach var="row" items="${qryProvider.rows}">
        <tr>
            <td>${row.display_name}</td>

        </tr>
    </c:forEach>
</table>

Can you also add some explanation? – Robert Sep 11 '15 at 11:49 — Robert, Sep 11 '15 at 11:49

Nilesh · Answer 3 · 2016-02-06T11:33:18.720

-3

SELECT * FROM provider;

Replace your code and import sql package

or import sql package and Remove ; from your select statement

edited Feb 06 '16 at 11:33

answered Feb 06 '16 at 11:18

Nilesh

1
1

How to use JSTL sql tag

3 Answers3

Linked