Below is a class of mine that is a bit primitive but does the job. There's
probably plenty wrong (like using integer constants instead of type safe enum)
with it as I'm a bit of an amateur. With queries I reckon the usual rules of
MVC go out the window & you design for
1 - performance
2 - minimum lines of code.
3 - simpicity - You can put junior programmers to work on this sort of code.
..DL = Data List object. It has no GUI code but needs a method for each
combination of parameters. It has no SQL but uses the data objects created by
classes that have the SQL.
=========
I think that once a system has many queries the database design must be stable
so it's not so bad if the GUI is bound to the DB design. (OO heresy!)
If u have a web only interface an alternative is to build the SQL in your
ActionForm & have a generalised system that runs the SQL & returns a
List/Collection of type you specify. Some such thing must exist. You just put
the collection in the request for the JSP to display.
thanks,
Keith.
package com.biff.biffapp1.db;
import java.util.Iterator;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Collections;
import java.io.Serializable;
import org.apache.log4j.Logger;
import com.biff.utils.Biff1AppException;
import com.biff.utils.ZUtils;
/**
* Data List Object
*
**/
public class UserDL extends Object implements Serializable {
private static final
Logger logger = Logger.getLogger(UserDL.class.getName());
public static final int SELECT_ALL = 60;
public static final int SELECT_INACTIVE = 61;
public static final int SELECT_ACTIVE = 62;
public static final int SORT_USER_NAME = 50;
public static final int SORT_ACTIVE_USER_NAME = 51;
public static final int SORT_USER_ID = 52;
public static final int SORT_ACTIVE_USER_ID = 53;
public static final int SORT_SIGNON_COUNT = 54;
public static final int SORT_LAST_SIGNON = 55;
// holds data for table.
private ArrayList userList = new ArrayList(50); // what init size?
private int selectionType = 0;
private int sortType = 0;
/*
* Constructor - build date from default query.
*/
public UserDL() {
this.selectionType = UserDL.SELECT_ALL;
this.sortType = UserDL.SORT_USER_NAME;
constructorCode();
}
/*
* Constructor -
*/
public UserDL(int selectionType, int sortType) {
this.selectionType = selectionType;
this.sortType = sortType;
constructorCode();
}
/*
*/
public void constructorCode(){
//logger.debug("constructorCode: start. selectionType="+ selectionType
// +" sortType="+
sortType);
Iterator iii = UserDB.getInstance().getArrayList().iterator();
while (iii.hasNext()) {
UserDO ddd = (UserDO)iii.next();
boolean includeIt = false;
switch (selectionType) {
case UserDL.SELECT_ALL :
includeIt = true;
break;
case UserDL.SELECT_INACTIVE :
if (ddd.getRowStatus().equals("D")) includeIt = true;
break;
case UserDL.SELECT_ACTIVE :
if (ddd.getRowStatus().equals("A")) includeIt = true;
break;
default :
throw new Biff1AppException(
"constructorCode: unknown selectionType: "
+ selectionType);
}
if (includeIt) userList.add(ddd);
}
if (userList.size() == 0) {
logger.warn("constructorCode: no data loaded");
}
Collections.sort(userList, new UserDLComparator(sortType));
//logger.debug("constructorCode: data has been loaded from
DB");
}
/*
*/
public ArrayList getData() {
return userList;
}
/*
* Make an iterator available for the data.
*/
public Iterator getIterator() {
return userList.iterator();
}
class UserDLComparator implements Comparator {
private int sortType;
private String key1;
private String key2;
private UserDO k1;
private UserDO k2;
UserDLComparator(int sortType) {
this.sortType = sortType;
}
/**
* interface Comparator
*/
public int compare(Object o1, Object o2) {
k1 = (UserDO)o1;
k2 = (UserDO)o2;
switch (sortType) {
case UserDL.SORT_USER_NAME :
key1 = k1.getSurname() + k1.getFirstName();
key2 = k2.getSurname() + k2.getFirstName();
break;
case UserDL.SORT_ACTIVE_USER_NAME :
key1 = k1.getRowStatus() + k1.getSurname() +
k1.getFirstName();
key2 = k2.getRowStatus() + k2.getSurname() +
k2.getFirstName();
break;
case UserDL.SORT_USER_ID :
key1 = k1.getUserID();
key2 = k2.getUserID();
break;
case UserDL.SORT_ACTIVE_USER_ID :
key1 = k1.getRowStatus() + k1.getUserID();
key2 = k2.getRowStatus() + k2.getUserID();
break;
case UserDL.SORT_SIGNON_COUNT :
key1 = ZUtils.intToString(k1.getSignonCount(),
8);
key2 = ZUtils.intToString(k2.getSignonCount(),
8);
break;
case UserDL.SORT_LAST_SIGNON :
key1 = k1.getLastSignonDatetime();
key2 = k2.getLastSignonDatetime();
break;
default :
logger.warn("warning unknown sortType=" +
sortType);
//key1 = key2 = "";
key1 = k1.getUserID();
key2 = k2.getUserID();
}
return key1.compareTo(key2);
}
/**
* interface Comparator. true only if the specified object is also a
* comparator and it imposes the same ordering as this comparator.
*/
public boolean equals(Object o1) {
//logger.debug("equals called!");
return (o1 instanceof UserDLComparator
&& this.compare(this, o1) == 0);
}
} //end class UserDLComparator
} // end
--- "Siggelkow, Bill" <[EMAIL PROTECTED]> wrote:
> Heather,
> In my opinion, the approach you are taking is not separating out your logic
> appropriately. In fact, it sounds like your are binding your persistence
> layer (database columns) all the way up to the presentation layer (your
> action forms). A better approach is to separate the model (data) from the
> view (presentation). Let your action classes (controller) marshall data from
> the database to the action forms. The BeanUtils classes can ease this
> activity.
>
> Typically what I have are the following:
>
> ActionForms (or DynaActionForms) that represent the form fields
> Actions that get the forms and then create business objects from these forms.
> At this point I may pass that business object to a Service layer (like a
> Manager object)
> The manager object interacts with a DAO (data access object) that performs
> the actual JDBC stuff.
>
> It really is a question of appropriate assigment of responsibilities.
>
> I suggest you read throught the Struts User Guide as well as check out some
> of the new Struts books that are available.
>
> -----Original Message-----
> From: Heather Buch [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 10, 2003 4:24 AM
> To: [EMAIL PROTECTED]
> Subject: turning form parameters into queries
>
>
> Hi all,
>
> Finding a good method to create SQL queries out of html form parameters
> is something I've struggled with for some time now. Is there a good or
> recommended way of doing this? For that matter, is there a "best
> practice" for making SQL queries in struts?
>
> I use a mysql database backend for my struts application. In the
> browser, the user selects some choices from an html form. My Action
> classes collect the information that the user has chosen out of my form
> bean, pretty standard.
>
> The Action class then sends that info back to a corresponding
> BusinessLogic class (BusinessLogic classes reside in a package that does
> not know about struts or servlets, but correspond roughly one
> BusinessLogic class to one Action class).
>
> The BusinessLogic class needs to take those form parameters and create a
> list of "QueryParam" objects, which are then sent to a "SQLMaker" class
> where they are used to build constraints (the stuff after "WHERE" in the
> query) for SQL queries.
>
> In my BusinessLogic classes, I have this method which gets called by my
> Action class:
>
> public void setQueryParams(String querytype, String fieldname, List
> parameters)
>
>
> The "QueryParam" class is basically just a bean that I iterate through
> when I build the constraint part of the query. It contains these fields:
>
> protected List _values = null;
> protected int _datatype = -1;
> protected String _colname = null;
> protected String _tablename = null;
> protected String _singlevalue = null;
>
> xThere are a couple of problems with this.
>
> First, every BusinessLogic class (and there are many because they
> correspond to Action classes) has the information about the form fields,
> and the database struture info that maps to it, hard-coded. Instead of
> being able to create this method once and for all in a superclass of my
> BusinessLogic classes, I have to rewrite this method for every
> BusinessLogic class, because the subclassed BusinessLogic class knows
> about the Action class that calls its "setQueryParams", and therefore
> what html form fields that Action class will use and how they will be
> mapped. That also means that every time I change a form field name, I
> have to make the change in my BusinessLogic class as well.
>
> Which classes should know what? It seems that either my form bean needs
> to know enough to be able to name form fields after database columns, or
> my business logic classes need to know the name of the html form fields,
> and how they map to database column information, to make the constraints.
>
> Would it make sense to cache a copy of the database struture in the
> struts application, and add all the form field mappings to that? Or is
> there a rule for writing html form fields to go into a database (like,
> "html form fields must be named after database columns").
>
> Somewhere the mapping from form field to database column name, and then
> the addition of other information needed to make the QueryParam (and
> therefore the query), needs to be done, (and it would be nice if the
> method could only appear once in the application), but I'm not sure I
> know where.
>
>
> Thanks,
>
> Heather M. Buch
>
>
>
>
> --
> To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>
> --
> To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>
=====
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Search the archive:-
http://www.mail-archive.com/struts-user%40jakarta.apache.org/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Keith Bacon - Looking for struts work - South-East UK.
phone UK 07960 011275
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
