Returning a mapped statement as a ResultSet

[Nobody/Anonymous]

Is there any way to return the results for my query as a ResultSet and not a List of objects?
When I call the mapped-statement name from my Java class:

MappedStatement mappedStatement = sqlMap.getMappedStatement(mappedStatementName);
list = mappedStatement.executeQueryForList(con, parameterObject, skipResults, maxResults);

In XML:

<mapped-statement name="xyz"...>
   query
</mapped-statement>

instead of returning a list I want it to return the a result set.
How can do this?

Dylan

[Nobody/Anonymous]

My initial reaction was why?  But then again, it really annoys me when someone responds to  my questions that way.  :-)

The answer at this time is no.  50% of the purpose behind using SQL Maps is that it automatically populates your JavaBeans for you.  That said, I would be interested to know more about the "why" so that we can find an alternative solution, or perhaps we just need an executeQuereyForResultSet() method....for now, maybe you could use a Map.....

You can retrieve the results from a query in a Map (or even a List of Maps).  This basically gives you a quick-n-dirty way to retrieve raw data from your database.  For example:

<mapped-statement name="..." result-class="java.util.HashMap">
    SELECT * FROM some_table
</mapped-statement>

The returned Map will contain key/value pairs where the column name is the key and the values are the result of a call to ResultSet.getObject().  I recommend using 1.2.0 or higher for the above statement.

I have been investigating adding RowSet support to the framework as well.  I'm just in the initial planning stage of that addition though....

What would you think of RowSet support vs. ResultSet support?

Cheers,
Clinton

[Nobody/Anonymous]

I had a situation when I needed a direct access to the ResultSet. It happened when direct mapping is not possible. For example i have Event object with eventDate and eventTime fields, which are strings. Now i have 3 fields, those two that are not mapped and a field that contains a Date object. After I execute the query in a loop I populate in a loop the fields I need.

Like possible solution I see adding the ResultsSet as a parameter to the RowHandler.handleRow function.

public void handleRow (ResultSet rs, Object object) {
  
   Event event = (Event)object;
   Date d = rs.getString("event_time");

   SimpleDateFormat timeFormat = new SimpleDateFormat("hh:mm");
   SimpleDateFormat dateFormat = new SimpleDateFormat("mm/dd/yyyy");

   String time = timeFormat.format(d);
   String date = dateFromat.format(d);
   // excetpion handling
   event.setDate(date);
   event.setTime(time);
}

Evgeni

[Nobody/Anonymous]

Hi Evgeni,

This can be handled in one of two alternative approaches that do not involve the database or the ResultSet.

First, declare your single property of type Date. For example:

private Date eventDateTime;
public void setEventDateTime(Date dateTime) {
eventDateTime = dateTime;
}
public Date getEventDateTime() {
return eventDateTime;
}

The above property would be what you map using the SqlMap framework.

Now we need get methods for your String types (date and time).

public String getEventDate(){...}
public String getEventTime(){...}

What goes in these getters depends on where you want to handle the conversion. You can either do it in the getters, for example:

public String getEventDate() {
SimpleDateFormat dateFormat = new SimpleDateFormat("mm/dd/yyyy");
String date = dateFromat.format(d);
return date;
}

public String getEventTime() {
SimpleDateFormat timeFormat = new SimpleDateFormat("hh:mm");
String time = timeFormat.format(d);
return time;
}

OR you can declare private fields (for date and time strings) and populate them do it in the setter (better performance?). For example:

private String eventDate;
private String eventTime;

public void setEventDateTime(Date dateTime) {
eventDateTime = dateTime;
SimpleDateFormat dateFormat = new SimpleDateFormat("mm/dd/yyyy");
eventDate = dateFromat.format(d);
SimpleDateFormat timeFormat = new SimpleDateFormat("hh:mm");
eventTime = timeFormat.format(d);
}

And then the getters just return the private fields:

public String getEventDate() {
return eventDate;
}
public String getEventTime() {
return eventTime;
}

The advantages of this approach are:

1) You now have a more robust API for your dates that are independent of the database and available via the standard JavaBeans API. Other users of this class can benefit from this.

2) Your properties are all automatically populated by the SqlMap framework without any extra code on your part. This is not a compromise to support the framework, it's actually a more flexible design that is MORE independent from the database.

What do you think of this approach?

Cheers,
Clinton