• Post Reply Bookmark Topic Watch Topic
  • New Topic

Filtering with dropdown lists and SQL statements  RSS feed

 
A Farroll
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I am familiar with Java but new to JSP. I have a Java Servlet app where user actions are recorded in a SQL Server database amd I now need to quickly put together a JSP front end application to view user actions. I want two drop down boxes to filter the results that will be displayed in a list box. What I need is the first drop down list box to show unique user names that have logged in. I can interrogate the database with the following SQL;

"SELECT DISTINCT(USER_ID) FROM AUDIT_MESSAGE"

Then when a user is selected from the first drop down list box (perhaps some sort of on change event) a second drop down list box shows the logins times of the selected user. Again I can interrogate the database with the following SQL;

SELECT SESSION_ID, EventTIME FROM dbo.AUDIT_MESSAGE
WHERE OPERATION = 'loginResponse' AND RESULTS = 'OK'
AND USER_ID = 'firstdropdownlistselection'

Then finally when a login time is selected in the second drop down list box all events for the selected user while logged in with that login time are displayed in the list box.

I look forward to any feedback. Thanks in advance as I am afraid I haven't the time to learn JSP and need to put this together quickly. I am using Eclipse Helios.

Regards

AJFarroll
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A Farroll wrote:I look forward to any feedback. Thanks in advance as I am afraid I haven't the time to learn JSP and need to put this together quickly.

With all due respect, you are probably asking in the wrong place. We'll give you hints but we expect you to learn to write your own code. Try StackOverflow if you want quick answers and code handed to you.

You'll need JavaScript and Ajax to do what you are asking. The on change of the select control can fire an Ajax request to a servlet which will take the request parameter and use it to set the parameter in your second SQL PreparedStatement. Then you can use a JSON response to populate the second select control. So it's neither quick nor easy. There are several things you'll need to learn if you are truly interested in learning and not just throwing together code without understanding what it's doing.

If you want to learn, give it a shot and post your code and we'll help.
 
A Farroll
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi J Kevin Robbins,

Thank you for a prompt response. I absolutely agree that writing your own code is the best experience and best way to learn. Its just that, like many others, I have so much other things to do and this will be a one off JSP project just to fulfill one small objective. I also was not aware that this would be a substantial task.

However, since posting my request I have had a little go at this with the first part completed successfully with the first dropdown list showing the distinct user list. Maybe you could just give me some pointers on how to get the result of the first dropdown list to be used to poulate the second. Here is my code so far. My boss now wants this as a stand alone small project because the servlet app is now "stable" and running.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Check User Activity</title>
</head>
<body>
<sql:setDataSource driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://XXXXXXXXX\XXXXXX:XXXX;databaseName=XXXXXXXXXXXXX"
var="localSource"
user="XXXXXXXX"
password="XXXXXXXXXXXXX"/>

<sql:query dataSource="${localSource}"
sql="SELECT DISTINCT(USER_ID) FROM AUDIT_MESSAGE"
var="result" />

<br >
<Select name="UserID" id="UserIDs">
<c:forEach items="${result.rows}" var="st">
<option value="1"><c:out value="${st.USER_ID}"/></option>
</c:forEach>
</select>

</body>
</html>

Thanks again

Regards

AJFarroll
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Congratulations on diving in and trying. Too many people give up and want to be spoon-fed the answers.

The first thing that I feel obliged to point out is that the database actions (SQL tags) should not be in the jsp. That's okay for quick testing or prototyping, but not production. Put that in a DAO. In fact, I'd put both of your SQL statements in a DAO as PreparedStatements and invoke them from a servlet.

Now on to the list. If you aren't familiar with jQuery, you need to be. You'll want an Ajax call to populate your second list and life is too short to do Ajax without jQuery. A basic call would look something like this:

That's a rough example off the top of my head; don't expect that code to work as is. Use Firebug to look at the results and you'll need to read up on how to make your servlet or Ajax handler class create and return the JSON string, but "data" will contain the information you need to populate the second select/option control.

That should keep you busy for a while.
 
A Farroll
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi J. Kevin Robbins,

Thanks again for further feedback. I have managed to spend some time on this today and now changed it to use JavaBeans with a Prepared Statement and still successfully populated the first drop down list box. See code below. Now I have to work out how to get the selected value from the first list box to be part of the WHERE clause in the second prepared statement. Use JQuery and AJAX you say? I know what these are but never used either but I will look into it. I won't be today though as now have to do other things. Will try and keep you updated.

<?xml version = "1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<%-- page settings --%>
<%@ page import = "java.util.*" %>
<%@ page import = "org.checkusers.jsp.beans.*" %>

<jsp:useBean id = "loginData" scope = "request"
class = "org.checkusers.jsp.beans.FilterUsers" />

<html xmlns = "http://www.w3.org/1999/xhtml">

<head>
<title>Check User Activity</title>
</head>
<body>
<% // start scriptlet

List<Logins> loginList = loginData.getLoginList();
Iterator<Logins> loginListIterator = loginList.iterator();
Logins loginids;
%>
<Select name="showID" id="showIDs">

<%
while (loginListIterator.hasNext()){
loginids = (Logins) loginListIterator.next();
%>
<option value="1"><%= loginids.getUserID()%></option>
<% // Continue scriptlet
} //End while
%> <%-- end scriptlet completely --%>
</Select>
</body>
</html>
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You took a huge step backwards when you went from using <c:forEach> to using scriptlets. You were on the right track the first time. I strongly suggest you pick up a copy of "Head First Servlets and JSP". It will get you up to speed on current technology. Also check out the faq section of this site. There are many excellent article there that will help.

btw, "Kevin" is fine. My full name sounds too formal.
 
A Farroll
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Kevin,

Ah! I thought I was moving in the right direction going to scriptlets because I am under the impression I cannot reuse the same <SQL and <C tags. I will continue with this when I have any available time. I had to take yesterday off as annual leave because child minder was sick, hence the late reply.

Is the "Head First Servlets and JSP" worth having? Is it good for learning about Servlets? I ask because I may be involved more with a few servlet projects rather than JSP. That is what I mean't by the JSP probably being a one off, although I am willing to learn what I can on JSP in the time I have.

Regards

Alan
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's an excellent book and I consider it a "must have" for any Java developers library. You'll find other great suggestions on books here.

I'm going through this book for the second time as I study for the Web Component Developer exam. But whether you intend to take the exam or not, you should read it. Interestingly enough, the first few chapters will explain and teach scriptlets and then tell you to never use them. That's because you will encounter them in the real world and you need to know how to support them, but you shouldn't be using them for new development.

In one of my previous "jobs-from-hell", it was not unusual to find a jsp with 2,000 lines of scriptlet code. They were nearly impossible to support. I got out of that job as soon as I could find something else. Even at my current job we still have developers using them, but I'm doing my best to drag them into the 21st century.
 
A Farroll
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Kevin,

I had a look on amazon.co.uk and there is a kindle edition. But it is over 900 pages not sure I would like to read that on a Kindle and haven't that much time either. But might be very good as a reference book though. Its Friday 1.30 pm here in Scotland and probably won't get any time to work on this until Monday. So I will have a go at getting this further then.

Thanks again for all guidance so far. I will try and keep you informed.

Alan
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A FarrollBut wrote: it is over 900 pages not sure I would like to read that on a Kindle and haven't that much time either.


I didn't say it would be easy. But very much worth the effort. It just depends on how good a programmer you want to be.

I'm not a big fan of e-books. My paperback copy is sitting here and it's chock full of Post-It notes, paper clips, and highlighting. But that's what works for me.

Good luck!
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!