JSP + JSTL + SQL

[Kevin Weiss]

Hi all,

I'm trying to retrieve rows from a PostgreSQL database using a simple search form (search.jsp). Here's the relevant code:

.
.
.
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
.
.
.
    <form method="post" action="Search.jsp" name="Query Input Form">
        <input type="text" name="query" value="" />
        <input type="submit" value="Submit Query" name="Submit" />
    </form>
.
.
.
    <c:if test="${param.query}">
        <sql:query var="result" dataSource="jdbc/postgres">
            SELECT * FROM msds WHERE filename LIKE <sql:param value="${param.query}"/>
        </sql:query>
    </c:if>
.
.
.
<p> Query was <c:out value="${param.query}"/> </p>
.
.
.

The search.jsp page compiles and runs fine, but no data is retrieved. When input is provided, a SQL statement similar to the following should execute:

    SELECT * FROM msds WHERE filename LIKE '%Acetone%'

If I hardcode the line above, I get two rows returned (as expected). However, if use aforementioned param.query parameter, nothing is returned. Now, that parameter is picking up the input from the search form--I'm checking that with the c:out line above.

Anyway, onto the question. Does anyone know how to properly add the single quote (') and percentage (%) characters to my SQL expression above so that it returns rows from the database using my parameter?

[Richard Green]

<sql:query var="result" dataSource="jdbc/postgres">
SELECT * FROM msds WHERE filename LIKE '%<sql:param value="${param.query}"/>%'
</sql:query>
[ June 28, 2006: Message edited by: Lynette Dawson ]

[Kevin Weiss]

Well, I tried that, and unfortunately it didn't work. Any other suggestions? Anything else I should check?

[Stefan Evans]

<c:if test="${param.query}">
  <sql:query var="result" dataSource="jdbc/postgres">
    SELECT * FROM msds WHERE filename LIKE ?
    <sql:param value="${param.query}"/>        
  </sql:query>    
</c:if>

You may have to include wildcard characters in the query text if you want it to find the correct results.

If you want to put the wildcard characters in automatgically I think this would work
<sql:param>%${param.query}%</sql:param>
or in a JSP1.2 container
<sql:param>%<c:out value="${param.query}"/>%</sql:param>

Also, insert standard disclaimer about how doing SQL queries from a JSP page is bad form, and that code like this should be in a servlet.

Good luck,
evnafets

[Kevin Weiss]

That suggestion worked. I finally got the search page working with:

    <sql:query var="result" dataSource="jdbc/postgres">
    SELECT * FROM msds WHERE filename LIKE ?
    <sql:param>%${param.query}%</sql:param>        
    </sql:query>

Since I'm still fairly new to java based web programming, why is it not recommended to JSTL SQL statements in a JSP page? Is it just not recommended in general (SQL queries from a web page)?

Thanks to everyone who helped, and thanks for all the info.

[Bear Bibeault]

Originally posted by Kevin Weiss:
Since I'm still fairly new to java based web programming, why is it not recommended to JSTL SQL statements in a JSP page? Is it just not recommended in general (SQL queries from a web page)?

Because it's an egregious violation of the principle of Separation of Concerns. By mixing up model and view code in the JSPs, it makes it much more difficult to re-use code, extend code. maintain code and adds complexity.

For more detail, look up the pattern called MVC (Model-Controller-View), and "Model 2" when it comes to web applications.

[Ben Souther]

As far as I'm concerned JSTL SQL was a huge blunder.
Even the specs recommend against using them in anything but the most trivial of apps.

The first paragraph in Chapter 10 of the JSTL spec:

Many web applications need to access relational databases as the source of dynamic data for their presentation layer. While it is generally preferred to have database operations handled within the business logic of a web application designed with an MVC architecture, there are situations where page authors require this capability within their JSP pages (e.g. prototyping/testing, small scale/simple applications, lack of developer resources).