html form that contains a list of options drawn from db

[Edmund Castermund]

Hi,

I'm creating an html form, and the user will select options from a pulldown list. The content of that list will need to be drawn from the data base.

Is it best to have the form contained in a servlet? And then the action attribute of the form will be another servlet? Since most of the form is static (other than the list contents) I thought it made sense to do this as a .jsp file.
Can I grab the list contents with a scriptlet?
bp

[Bear Bibeault]

View generation should done with a JSP. And only view generation. Any data gathering or processing should be in servlet controllers.

And JSPs should never contain scriptlets. Ever.

[Pete Nelson]

There are a number of ways to pull this off. While ideally you should separate your view to JSP and your control & data gathering to servlet, the Java Standard Tag Libraries provide a quick-and-dirty way to pull the data & display it all within the JSP.

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

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

<sql:query dataSource="jdbc/someDataSource" name="userQuery">
  SELECT username FROM users
</sql:query>

<select name="user">
 <c:forEach items="${userQuery.rows}" var="row">
    <option>${row.username}</option>
 </c:forEach>
</select>

What I find is a more important question with these lists is "how often does this data change?". If you use the quick-and-dirty JSTL sql tags, every page request results in another query of the database, which might be overkill. I have a number of apps that must display a drop-down list of departments, which change infrequently (maybe every six months). In these cases, I will frequently use an application scope List, and then use a Filter to make sure the List is set, and up-to-date. I am attaching source code from one of my filters for your benefit (or at least I would, if I knew how to use attachments on this forum).

[Bear Bibeault]

Pete Nelson wrote:Java Standard Tag Libraries provide a quick-and-dirty way to pull the data & display it all within the JSP.

The emphasis here is on "quick and dirty". Even the JSTL Specification says not to use these tags in anything but test code or where there are no alternatives.

[Pete Nelson]

Here's the code I wanted to attach to my previous reply:

package city.stpaul.amanda.web;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.LinkedHashMap;

import javax.naming.NamingException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.UnavailableException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import city.stpaul.util.StaticUtils;

/**
 * Servlet Filter implementation class SelectListInitFilter
 */
public class SelectListInitFilter implements Filter {
	
	private Logger logger;
	private long lastLoaded = 0L;
	private int updateMinutes = 1440;
	private ServletContext scontext;
	private DataSource dataSource;
	private String deptListQuery;
	private String userHashQuery;
	
	private static final int DEFAULT_UPDATE_MINS = 180;

    /**
     * Default constructor. 
     */
    public SelectListInitFilter() {
        this.logger = Logger.getLogger(this.getClass());
        logger.trace("Constructor called; logger initialized");
    }

	/**
	 * @see Filter#destroy()
	 */
	public void destroy() {
		;
	}

	/**
	 * @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain)
	 */
	public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
		// synchronized so only one thread attempts to update
		// all other threads should wait, and then check the age
		synchronized(this) {
			long now = Calendar.getInstance().getTimeInMillis();
			if((now - this.lastLoaded) > updateMinutes)
				updateSelectLists();
		}
		// pass the request along the filter chain
		chain.doFilter(request, response);
	}

	/**
	 * @see Filter#init(FilterConfig)
	 */
	public void init(FilterConfig fConfig) throws ServletException {
		this.scontext = fConfig.getServletContext();
		
		String scTimeoutStr = fConfig.getInitParameter("updateMinutes");
		if(scTimeoutStr != null) {
			try {
				this.updateMinutes = Integer.parseInt(scTimeoutStr);
			} catch (NumberFormatException nfe) {
				logger.warn("NumberFormatException thrown parsing updateMinutes", nfe);
				this.updateMinutes = DEFAULT_UPDATE_MINS;
			}
		}

		this.deptListQuery = fConfig.getInitParameter("deptListQuery");
		if(this.deptListQuery == null || "".equals(this.deptListQuery))
			throw new UnavailableException("deptListQuery is null or empty; cannot continue.");
		
		this.userHashQuery = fConfig.getInitParameter("userHashQuery");
		if(this.userHashQuery == null || "".equals(this.userHashQuery))
			throw new UnavailableException("userHashQuery is null or empty; cannot continue.");

		String dsn = fConfig.getInitParameter("dataSourceName");
		if(dsn == null || "".equals(dsn))
			dsn = "jdbc/Amanda";
		try {
			this.dataSource = StaticUtils.dataSourceFromJNDI(dsn);
		} catch(NamingException ne) {
			logger.fatal("NamingException thrown", ne);
			throw new UnavailableException("Could not resolve JNDI DataSource; cannot continue");
		}		
	}
	
	private synchronized void updateSelectLists() {
		ArrayList<String> newDeptList = new ArrayList<String>();
		// using a LinkedHashMap to preserve ordering
		LinkedHashMap<String,String> newUserHash = new LinkedHashMap<String,String>();
		
		Connection conn = null;
		try {			
			conn = this.dataSource.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(this.deptListQuery);
			while(rs.next()) {
				newDeptList.add(rs.getString(1));
			}
			rs.close();
			
			rs = stmt.executeQuery(this.userHashQuery);
			while(rs.next()) {
				newUserHash.put(rs.getString(1), rs.getString(2));
			}
			rs.close();

			scontext.removeAttribute("DeptList");
			scontext.setAttribute("DeptList", Collections.unmodifiableList(newDeptList));
			scontext.removeAttribute("UserMap");
			scontext.setAttribute("UserMap", Collections.unmodifiableMap(newUserHash));
			this.lastLoaded = Calendar.getInstance().getTimeInMillis();
			
			stmt.close();
			conn.close();
			conn = null;
		} catch(Exception e) {
			logger.error("Exception thrown updating select lists", e);
		} finally {
			if(conn != null) {
				try { conn.close(); conn = null; }
				catch(Exception e) { ; }
			}
		}
	}
	
}
/*
  Example web.xml entry for this filter:
  <filter>
  	<description>
  	</description>
  	<display-name>SelectListInitFilter</display-name>
  	<filter-name>SelectListInitFilter</filter-name>
  	<filter-class>city.stpaul.amanda.web.SelectListInitFilter</filter-class>
  	<init-param>
  		<param-name>dataSourceName</param-name>
  		<param-value>jdbc/Amanda</param-value>
  	</init-param>
  	<init-param>
  		<param-name>updateMinutes</param-name>
  		<param-value>180</param-value>
  	</init-param>
  	<init-param>
  		<param-name>deptListQuery</param-name>
  		<param-value><![CDATA[
  			select department 
    		  from departments 
   			 order by department
   		]]></param-value>
  	</init-param>
  	<init-param>
  		<param-name>userHashQuery</param-name>
  		<param-value><![CDATA[
		  Select distinct userid, username
		    From validuser
		  Order by username
   		]]></param-value>
  	</init-param>
  	
  </filter>
  

*/

Then, in the jsp, I simply reference the application scoped variables DeptList and UserMap:

<select name="department">  
  <c:forEach items="${applicationScope.DeptList}" var="dept">  
     <option>${dept}</option>  
  </c:forEach>  
</select>  

[Edmund Castermund]

Hi,
that last reply was exactly what I was looking for...the logic in an external class and have the jsp call it.
thanks,
bp