How to Exclude one record from Query?

[Samar Land]

Hello Ranchers,

I have a current search By Name for users form, It's working just fine.

What I would to do is to exclude one of my result to be showed up

Let say I have this table,

FName LName Address
Samar Land WI
Matt Lero WI
Dany Land WI

When I do search for land, the normal results it will be Samar & Dany
Or If I do Dany, result will be Dany Land

What I want is not displaying Dany at all in my result.

So, What it comes to my mind, that I can add as an if statement in JSP page for

<c:if test="${not empty users}">
<table >
<tr >
<th>Name</th>
<th>Address</th>
</tr>
<tr>
<c:forEach items="${users}" var="user" varStatus="loopStatus">
// do something here to not display it like
//<c:if test="${user.name} = Dany">
//<c:if test="${user.name} = Dany">
No Result
// </c:if>
<c:if test="${empty user.email}">
${user.name}
</c:if>
<td>${user.address}</td>
</tr>
</c:forEach>

it didn't work like this way, Any other suggestions to make it here?

[Bear Bibeault]

You're doing it backwards. If you don't want certain records to display, don't fetch them from the database in the first place. Use a where clause on your query to filter the results to only those that will be displayed.

The JSTL in the JSP is most certainly not the place to be doing this.

[Samar Land]

I'm looking for a way without modify my query.

[Bear Bibeault]

Why? It's the right way to do it.

[Stefan Evans]

I noticed that your commented out <c:if> statement was invalid.

Instead of

<c:if test="${user.name} = Dany">

Try

<c:if test="${user.name == 'Dany'}">

The EL expression should be the whole expression, including the static string value.

I would echo Bear in saying that it would be preferable to do the filtering in java code - either in your query, or whatever code is processing the results of the query to put it into your user list.

[Bear Bibeault]

Yes, failing doing it in the query (as is appropriate), then at minimum it should be handled in model or controller code. The view is no place to be doing this sort of thing.

[Samar Land]

I solved it by adding this to my query.

name != "Dany"

[Bear Bibeault]

Good to see that you are dealing with it at the query level. But that seems rather specific. Have you considered what would be needed if the excluded string where to change? Or if you needed to exclude on multiple strings? You might want to investigate the "in" clause.

[Samar Land]

What I figured out to do so far here is adding a properties file with the exclude list, then add it to my query

exempt.properties
students =Samar,Dan

Then I had to add the following to my query:

 " AND (first_name NOT IN (:students))"
return getSession()
.createSQLQuery(query)
.setParameterList("students", getExemptStudents())

Add the method:

private List<String> getExemptStudents() {
return Arrays.asList(ResourceUtils.getBundle("exempt").getString("students").split(","));
}

Hope that will help the others

I think the title of my thread should be changed.

[Bear Bibeault]

 

Samar Land wrote:I think the title of my thread should be changed.

What would you suggest?

[Samar Land]

 

Bear Bibeault wrote:

Samar Land wrote:I think the title of my thread should be changed.

What would you suggest?

Maybe something like:

How to Exclude one record from Query?