• Post Reply Bookmark Topic Watch Topic
  • New Topic

JSP + JSTL + SQL  RSS feed

 
Kevin Weiss
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

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


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:


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 cut 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
Ranch Hand
Posts: 536
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
<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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I tried that, and unfortunately it didn't work. Any other suggestions? Anything else I should check?
 
Stefan Evans
Bartender
Posts: 1834
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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>%<cut 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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That suggestion worked. I finally got the search page working with:



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
Author and ninkuma
Marshal
Posts: 65833
134
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!