I have a search form that needs to include results from two different tables. The tables have no relationship to each other and our separate. In my example scenario, we have gas stations and grocery stores. The grocery store table might have attributes like freezerSize, produceStorage, numberOfCarts. The gas stations table might have gasTankSizeInGallons, windowCleanerInGallons, etc.... There are some shared fields between the two tables (i.e. - numberOfEmployees, squareFeetOfStoreSpace, numberOfShelves, etc...).
My search query needs to sort and display the gas stations and grocery stores together. I was thinking of using a SQL union and setting the non-applicable fields to 0 or null. However, I'm really stumped about how to do this with ibatis (since both objects are of a different type):
<select id="searchQuery" parameterClass="java.util.Map" resultClass="????????????????">
SELECT
storeName, storeCity, storeState, numberOfCarts, freezerSize, 0 gasTankSizeInGallons, 0 windowCleanerInGallons
FROM
grocery_stores
UNION
SELECT
storeName, storeCity, storeState, 0 numberOfCarts, 0 freezerSize, gasTankSizeInGallons, windowCleanerInGallons
FROM
gas_stations
ORDER BY storeState, storeCity, storeName
</select>
Note - the actual query has many more things in the order by, it is paginated, and there are many more fields in the select (plus a where clause for each applicable field in the select field).
What should be the resultClass for the above query? I have a GroceryStore and GasStation class both of which extend from Store. However, Store does not have many of the GroceryStore and GasStation specific fields. I could do two separate queries, but the ordering of the results would have to be done in java and it would be inefficient since it would need to load large amounts of data first.
Thanks