On Tue, 4 Feb 2003, Jacob Hookom wrote:
> Date: Tue, 4 Feb 2003 23:56:39 -0600
> From: Jacob Hookom <[EMAIL PROTECTED]>
> Reply-To: Struts Users Mailing List <[EMAIL PROTECTED]>
> To: 'Struts Users Mailing List' <[EMAIL PROTECTED]>
> Subject: RE: [OT] Which Object Relational mapping tool?
>
> In my opinion, this topic is just fine-- it comes up every week :-)
>
> I personally dislike O/R mappers because in most cases, I completely agree
> that beans and db should be separated, but it's at the point of merger that
> things get overly complex at the expense of an additional layer that needs
> just as much configuring as writing your sql code for each use case.
>
I've had to write some read-only database apps lately -- you know, the
kind where you have 100 or so different SQL SELECT commands to manage,
that often do really complicated joins, and where your business folks keep
tweaking the set of columns to be returned ... and it's just not worth all
the effort to create an O/R mapping for every possible SELECT that you
might ever want to run.
If you've got tasks like that, the recent nightly builds of Struts 1.1
(which include commons-beanutils 1.6 or later) contain a secret weapon --
the org.apache.commons.beanutils.RowSetDynaClass class, which copies the
contents of a java.sql.ResultSet into a set of DynaBeans whose properties
exactly match the columns in your query. This means you can use all the
Struts tags that you know and love to display the results, without ever
having to figure out how to run an O/R mapping tool for every possible
SELECT statement.
Example code in an Action (assuming you're using JNDI-based data sources
for connection pooling):
InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/MyDatabase");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
RowSetDynaClass rsdc = null;
try {
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery
("select custid as id, custname as name from customers" +
" where credit_limit > 1000 order by credit_limit descending");
rsdc = new RowSetDynaClass(rs);
} catch (SQLException e) {
... deal with exception ...
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
;
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
;
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
;
}
conn = null;
}
}
if (rsdc != null) {
request.setAttribute("customers", rsdc.getRows());
... forward to the "list customers" page
}
Example code from the "list customers" page:
<table>
<tr>
<th>Customer Id</th>
<th>Customer Name</th>
</tr>
<logic:iterate id="customer" name="custoemrs">
<tr>
<td><bean:write name="customer" property="id"/></td>
<td><bean:write name="customer" property="name"/></td>
</tr>
</logic:iterate>
</table>
You will still want to use O-R tools for use cases where you are updating
the contents of a database -- but, for all the selects, you don't really
need to go to all that effort if you don't want to.
Craig
PS: There is a performance tradeoff with the approach described above,
because the data is copied into an in-memory array. However, one key
advantage is that you can immediately release the database resources for
the query, so that the ultimate page that renders the output does not have
to worry about it. Most of the no-copy solutions I have seen require you
to deal with closing the result set, and/or releasing the connection back
to the connection pool, in the JSP page itself. To me, that does not seem
like something you really want to make the page author worry about.
PPS: If you're using JSTL in your pages, the
javax.servlet.jsp.jstl.sql.ResultSupport class can be used in a manner
very similar to what is described above, but makes the query results easy
to access via JSTL tags. That way, you won't be tempted to code the SQL
queries directly in your page, which doesn't really fit into an MVC
paradigm :-).
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
