Jaws and select distinct

[dlaidlaw]

Is there any way to get the jaws queries to do a SELECT DISTINCT? It is possible to tune the where clause on a custom finder, bu that is not where you specify DISTINCT.

For instance, a table called MESSAGEBOX with 3 columns: BOXID, USER, MESSAGEID where BOXID is the PK.

Would like to know all the distinct user fields as in:
SELECT DISTINCT USER FROM MESSAGEBOX

Any hints? Or do I have to write that query into a session bean?

[danch1]

You'll need to do that with a query from a session bean.

Finders are used to get instances of entities: what would it mean to return an instance of your messagebox entity, constraining non-pk field to be distinct? What would happen to the rest of the data for the entities involved?

[erik777]

Actually, I have the same problem. However, the problem is not uniqueness. Rather, the select DISTINCT is necessary to return uniqueness after doing an inner join.

If the table you are doing a findBy on is the parent of another table (1 to many), then joining it can result in many of the original in the collection. I need to do a DISTINCT to restore it to uniqueness.

For example:

Find all cities that have customers who compain too much.

In this case, I am doing a findBy on cities. Yet, I do not need 5 Miami's just because Miami has more than one customer that matched the criteria.

[davidjencks]

good point.

You could do a correlated subquery

select *
from t1
where t1.c1 in (select t2.c1 from t2 where somejoincondition on t1 and t2)

[danch1]

Ouch! subqueries can get real painful, performance wise. Maybe add a <requires-distinct> option in ?

[erik777]

Is <requires-distinct> a valid tag, or are you recommending it be added to the spec? If it is valid, will it work in JBoss 2.4.3?

In the meantime, I'll try the subquery. It is certainly better performance-wise than later deleting duplicate beans from the collection, and won't require the extra coding.

[erik777]

Well, unfortunately, MySQL won't support subselects until version 4.x:

"Sub-selects are scheduled for implementation in Version 4.x."

Ironically, they recommend using joins in the meantime:

http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html

So, I am still stuck.

[danch1]

It's not there at this point

[dlaidlaw]

SELECT DISTINCT has some real power in certain situations. Especially when selecting on a non-PK field. The example in the first post is a little contrived, but still valid. What if the USER column is just an email address, the messages got routed to a box based on some rules engine (this is actually happening in my code). A valid query would be to see all the BOXIDs from a particular email address. But since the email address is not unique within a BOXID you want a distinct QUERY. The finder method would still be returning PK's in a collection/enumeration and the calling process would be getting entities for the MESSAGEBOX rows. Is there anything inherently wrong with that? I didn't think all finders had to include the PK.
I am all for adding a tag to the finder, or an attribute to the finder tag itself, to specify a distinct query.

[markd]

I agree with danch1, you are going to lose some data with your current design.

However, you can code a finder where you do the JDBC calls yourself. Look at the bottom of http://www.jboss.org/online-manual/HTML/ch05s07.html.

Here is an example of some code that follows the documentation. Note: the distinct is not needed in this example, but it illustrates how to use the ejbFinder construct so that you can write a "select distinct" query.

public Collection ejbFindByUser(String user)throws FinderException{
Vector retVal = new Vector();
Connection con = null;
PreparedStatement ps = null;
ResultSet result = null;
Exception failed = null;
Integer key;
try {
con = ds.getConnection();
ps = con.prepareStatement("select distinct boxid where user = ?");
ps.setString(1, user);
result = ps.executeQuery();

while (result.next()){
key = new Integer(result.getBigDecimal(1).intValue());
retVal.add(key);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
if (result != null) {
result.close();
}
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}

} catch (SQLException se) {
se.printStackTrace();
}
}
return retVal;
}

[erik777]

I wasn't sure if you can write your own finder on a CMP bean. I have been thinking about doing this, but didn't know if it was possible or what was involved. I'm not sure what all "container managed" includes/excludes.

[erik777]

How do you get the data source for a CMP? Do I have to specify the data source again, even though it is defined in JAWS.XML?

[danch1]

> How do you get the data source for a CMP? Do I have
> to specify the data source again, even though it is
> defined in JAWS.XML?


Yes. jaws.xml only configures the CMP plugin itself, there's really no tie-in between it and the JNDI services.

[erik777]

I couldn't get it to work. It always returns an empty collection, without error.

It does not even appear to call the code. I have System.out.println calls, but nothing shows up in the log.

I even tried to create a "dummy" finder that returned two valid hard coded primary keys, and still get nothing but an empty collection. Has anyone got the coded custom finders to work in CMP?

Here's the dummy finder:
[pre]
public Collection findByTest() throws FinderException {
java.util.ArrayList list = new java.util.ArrayList();
list.add(new Integer(6));
list.add(new Integer(8));
return list;
}
[/pre]
Here's the Home entry:
[pre]
public Collection findByTest()
throws RemoteException, FinderException;
[/pre]
I had finder entries in JAWS.XML, but that just seemed to create errors. Do we need an entry there? If so, can someone please supply an example so I can get it to work without errors?