You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@struts.apache.org by Paul Dlug <pa...@aps.org> on 2001/12/12 16:04:09 UTC
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: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Dynamic SQL query library
Posted by "John M. Corro" <jo...@cornerstone.net>.
Toplink is a popular commercial-level package for transparent object
persistence. Something else a coworker pointed out to me recently was the
CachedRowSet object from Sun. I believe it's still in beta, but take a
look...some very neat features implemented in it that might address what
you're looking for.
While I'm posting, for those that reply...can you make mention on
performance of the object persistance packages you've worked with? I'm
curious to see how performance varies across the various packages.
----- Original Message -----
From: "Robert Taylor" <rt...@mulework.com>
To: "Struts Users Mailing List" <st...@jakarta.apache.org>
Sent: Wednesday, December 12, 2001 7:21 AM
Subject: RE: Dynamic SQL query library
> 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: paul@ridge.aps.org [mailto:paul@ridge.aps.org]On Behalf Of Paul
> > Dlug
> > Sent: Wednesday, December 12, 2001 10:04 AM
> > To: struts-user@jakarta.apache.org
> > 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:
> > <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >
> >
> >
>
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: Dynamic SQL query library
Posted by Nathan Anderson <na...@sum-ware.com>.
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: paul@ridge.aps.org [mailto:paul@ridge.aps.org]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: paul@ridge.aps.org [mailto:paul@ridge.aps.org]On Behalf Of Paul
> > Dlug
> > Sent: Wednesday, December 12, 2001 10:04 AM
> > To: struts-user@jakarta.apache.org
> > 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:
> > <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >
> >
> >
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
--
To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
For additional commands, e-mail:
<ma...@jakarta.apache.org>
Re: Dynamic SQL query library
Posted by Paul Dlug <pa...@aps.org>.
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: paul@ridge.aps.org [mailto:paul@ridge.aps.org]On Behalf Of Paul
> > Dlug
> > Sent: Wednesday, December 12, 2001 10:04 AM
> > To: struts-user@jakarta.apache.org
> > 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:
> > <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >
> >
> >
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
RE: Dynamic SQL query library
Posted by Robert Taylor <rt...@mulework.com>.
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: paul@ridge.aps.org [mailto:paul@ridge.aps.org]On Behalf Of Paul
> Dlug
> Sent: Wednesday, December 12, 2001 10:04 AM
> To: struts-user@jakarta.apache.org
> 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:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>