Hi Ted,
I remember playing with cached rowsets sometime ago. It is probably useful
to standardize your tags/code etc. based on the cached rowset rather than
create your own data-structure - but I found that particularly the
update/delete/insert into the rowset doesnt work properly for all but the
simplest tables.
I am currently implementing my own web-app(struts-based) that could handle
more cases - one thing with this is that the user can specify the
update/insert/delete in an xml file to configure my rowset - since I think
it is not possible to anticipate in all cases what statements they would
want to use. This also allows them to fire off multiple statements (for
data-integrity reasons or something else) in a transaction for each of the
actions update/insert/delete ...
Pratima
-----Original Message-----
From: Ted Husted [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 07, 2001 4:39 AM
To: [EMAIL PROTECTED]
Subject: Re: Scratch RowSets
Ted Husted wrote:
> Now, the next step is to create a RowSet from scratch to insert a new
> record to a new table. Given this, there doesn't seem to be any reason
> to have a seperate value object bean for a data set that is coming from
> or going to a persistent store.
In case anyone is interested, all I did was select a record that wasn't
there (primary key=0), and, bingo-bango, CachedRowSet created an empty
but valid RowSet, ready to receive new rows.
So, now instead of duplicating the data in my own set of properties, I'm
using the CachedRowSet's storage locations directly through a thin
wrapper with conventional mutators and accessors. This reduces the
overhead of redundant storage, retains all the flexibility of a standard
value object, is compatible with existing code bases, and can also
leverage the type casting built into RowSets.
[ DBMS ] -> [ RowSet ] -> [ ActionForm -> RowSet ] ->
[ DBMS ] -> [ RowSet ] -> [ JSP or ActionForm ]
And, of course, a RowSet can be treated just like a ResultSet in a JSP,
but without the overhead of an open connection.
A full treatment will follow, but here are some snippets.
ResultValue.java
// a RowSet Iterator wrapper subclassed as a
// value object wrapper around a CachedRowSet (whew!)
/**
* Return the account
*/
public String getAccount() {
try {
return values.getString("account");
}
catch (SQLException sqle) {
return null;
}
}
// .. more property wrappers
/**
* Bulk mutator for data transfer from another object
*/
public void set(
String bid, String lot, String amount, String account,
String precedence, String bidType, String bidFrom, String
pickup
) throws SQLException {
values.updateString("bid_key",bid);
values.updateString("lot",lot);
values.updateString("amount",amount);
values.updateString("account",account);
values.updateString("bidder_key",precedence);
values.updateString("bidType",bidType);
values.updateString("bidFrom",bidFrom);
values.updateString("pickup",pickup);
}
/**
* Convenience constructor to set internal RowSet
*/
public ResultValue(RowSet values) {
super(values);
}
Result.java
// encapsulates instance of ResultValue with other
// helper properties and serves as a data access object
public int insert() throws SQLException {
ResultValue resultValue = (ResultValue) getRows();
// MySQL can't insert via a RowSet, so use a Statement instead
return Statements.bidInsert(
resultValue.getLot(),
resultValue.getAmount(),
resultValue.getAccount(),
resultValue.getPrecedence(),
resultValue.getBidType(),
resultValue.getBidFrom(),
resultValue.getPickup()
);
}
Access.java (an Action)
// selects appropriate classes and
// methods for given request task
// -- INSERT --
if (task.equals("insert")) {
// Instantiate blank RowSet
result = thisResult.select(); // key=0
resultValue =
(ResultValue) thisResult.getRows();
// Create new row in empty set
resultValue.moveToInsertRow();
// Transfer data
resultValue.set(
thisForm.getBid(),
thisForm.getLot(),
thisForm.getAmount(),
thisForm.getAccount(),
thisForm.getPrecedence(),
thisForm.getBidType(),
thisForm.getBidFrom(),
thisForm.getPickup()
);
// Execute insert command for this Result object
result = thisResult.insert();
// Analyze outcome
if (result==0) {
message = "error.database.error";
}
else {
message = "record.inserted";
}
}
When retrieving multiple rows, the Result object (a proper JavaBean) can
be inserted in the request, along with it's RowSet. I wrote a quick
Iterator wrapper for RowSets so it could be used by the iterate tag.
Front to back This gives you a pattern like:
// Search.perform
// Select command
if (task.equals("lot"))
command = Commands.BID_SEARCH_LOT;
if (task.equals("account"))
command = Commands.BID_SEARCH_ACCOUNT;
// Ready result
Result thisResult = new Result(key,task,command);
// Retrieve data set
thisResult.execute();
// Queue for view
request.setAttribute("result",thisResult);
// Result.execute
public void execute() throws SQLException {
rows = new ResultValue(
RowSets.getRows( getKey(),getCommand() )
);
}
// RowSets.getRows
public static final RowSet getRows(String key, String command)
throws SQLException {
RowSet crs = new CachedRowSet();
crs.setDataSourceName(Commands.JNDI_NAME_KEY);
crs.setCommand(command);
crs.setString(1,key);
crs.setMaxRows(Setup.MAX_ROWS);
crs.execute();
return crs;
}
// result.jsp
<logic:iterate name="result" property="rows" id="row">
<td align="left">
<html:link page="/bid/Access.do?task=select" paramName="row"
paramId="key" paramProperty="bid">
<bean:write name="row" property="bid" filter="true"/>
</html:link>
</td>
<td align="left">
<bean:write name="row" property="lot" filter="true"/>
</td>
<td align="left">
<bean:write name="row" property="amount" filter="true"/>
</td>
<td align="left">
<html:link page="/account/Access.do?task=select" paramName="row"
paramId="key" paramProperty="account">
<bean:write name="row" property="username" filter="true"/>
</html:link>
</td>
<td align="left">
<bean:write name="row" property="bidType" filter="true"/>
</td>
<td align="left">
<bean:write name="row" property="bidFrom" filter="true"/>
</td>
<td align="left">
<bean:write name="row" property="pickup" filter="true"/>
</td>
</tr>
</logic:iterate>
Again, I'll update Strut by Strut with a full treatment of this, with
working stub files.
-- Ted Husted, Husted dot Com, Fairport NY USA.
-- Custom Software ~ Technical Services.
-- Tel 716 737-3463.
-- http://www.husted.com/about/struts/
