13 Replies Latest reply on Nov 23, 2001 2:54 PM by Erik Sliman

    Jaws and select distinct

    Don Laidlaw Newbie

      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?

        • 1. Re: Jaws and select distinct
          Dan Christopherson Newbie

          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?

          • 2. Re: Jaws and select distinct
            Erik Sliman Apprentice

            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.


            • 3. Re: Jaws and select distinct
              David Jencks Master

              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)

              • 4. Re: Jaws and select distinct
                Dan Christopherson Newbie

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

                • 5. Re: Jaws and select distinct
                  Erik Sliman Apprentice

                  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.

                  • 6. Re: Jaws and select distinct
                    Erik Sliman Apprentice

                    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.

                    • 7. Re: Jaws and select distinct
                      Dan Christopherson Newbie

                      It's not there at this point

                      • 8. Re: Jaws and select distinct
                        Don Laidlaw Newbie

                        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.

                        • 9. Re: Jaws and select distinct
                          Mark Desrosier Newbie

                          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;
                          }

                          • 10. Re: Jaws and select distinct
                            Erik Sliman Apprentice

                            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.

                            • 11. Re: Jaws and select distinct
                              Erik Sliman Apprentice

                              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?

                              • 12. Re: Jaws and select distinct
                                Dan Christopherson Newbie

                                > 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.

                                • 13. Re: Jaws and select distinct
                                  Erik Sliman Apprentice

                                  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?