turning form parameters into queries

[Heather Buch]

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]>