Here is an idea I had that I am just starting to try out. I created a method that can take a bean [could be a FormBean, but mine is more of a value object] and make it into an SQL statement. If there are null values, they are not included in the "WHERE" clause.
First I needed a way of taking a SQL command, and a list of parameters and return the results. [There is probably a better way, but this is my first idea of how to do it...] Attached are the supporting classes that I used to make this work. - SQLParam class: This is an object that is used to hold an object and it's JDBC type [see java.sql.Types]. A collection of these [ArrayList is what I used] is sent to the DataAccess class along with an SQL command. - DataAccess class: This is closely copied from Ted Husted's StatementUtils class [found in Scaffold.jar]. It requires a ConnectionPool class be created [a ConnectionPool class for PoolMan is attached], but the ConnectionPool class can be replaced to use your container's connection pool or some other third party pool. Basically this class takes a SQL command, and a list of SQLParams and returns a RowList object - RowList class: This is an object that can hold a resultset after the connection has been closed [returned to the pool]. It is basically an ArrayList of ArrayLists of Objects. The other part you'll be most interested in is the Model class. Here is where I make SQL statements and the associated collection of SQLParams out of a bean, and pass them to the DataAccess class. Also in the Model is a method to take the results [RowList object], and make it into a collection of beans. I hope that made sense. This is by no means a proven way of doing things.. I am pretty comfortable with the parts that Ted Husted wrote/I adapted, but I'm not sure about the efficiency of the RowList object. It has worked so far without much of a hitch [as long as you are careful about creating your SQL commands] Take a look and let me know what you think.... Nathan Anderson -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Paul Dlug Sent: Wednesday, December 12, 2001 7:43 AM To: Struts Users Mailing List Subject: Re: Dynamic SQL query library I've looked at these actually, but at this time I don't really want to delve into an Object -> Database mapping or object persistance. I just want a utility class to generate raw SQL statements. My biggest problem is not mapping the object to and from the database but generating 'search' type queries (letting the user specified a number of parameters and dynamically generating the query from them). The mapping is the easy part. Robert Taylor wrote: > > Might want to have a look at JDO or the database package provided by > Expresso. > > JDO: > http://jcp.org/aboutJava/communityprocess/review/jsr012/index.html > http://castor.exolab.org/index.html > > Expresso: > http://www.jcorporate.com/econtent/Content.do?state=template&template=2&reso > urce=636&db=default > http://www.jcorporate.com/html/products/expresso/dbpooling.html > > HTH, > > robert > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Paul > > Dlug > > Sent: Wednesday, December 12, 2001 10:04 AM > > To: [EMAIL PROTECTED] > > Subject: Dynamic SQL query library > > > > > > This is somewhat offtopic but definitely related to web development and > > I'm sure someone on this list has run up against this issue before. > > > > I'm looking for a java library to generate dynamic SQL. Basically I want > > the ability to do something like: > > Query q = new Query(); > > q.type(SELECT); > > q.add("table1", "username", EQUALS, "john"); > > q.add("table1", "added_date", LESS_THAN, new java.util.Date()); > > Statement st = conn.createStatement(); > > st.executeQuery(q.toString()); > > > > Or something just as easy to use as this, basically generating queries > > without being locked into creating SQL statements by hand since they > > could change at any time. I came across the org.apache.turbine.util.db > > library from Turbine which seems to have what I'm looking for, but it's > > too tightly coupled with Turbine. Ideally I need a library that is > > general enough to use in any environment (why isn't there a Jakarta > > Commons SQL Utils??), or something that can work hand in hand with > > struts. Any advice greatly appreciated. > > > > -- > > 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]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
ConnectionPool.java
Description: Binary data
DataAccess.java
Description: Binary data
RowList.java
Description: Binary data
SQLParam.java
Description: Binary data
Bean.java
Description: Binary data
Model.java
Description: Binary data
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
