Writing Custom find method

mkk · **Joined:** Wed Mar 21, 2007 3:02 pm **Posts:** 4

Hi All

Hibernate 3.1
SQL Server 2005

I have the following two tables..

TableA [int colA, int colB, String colC, int data] : primary key [colA+colB+colC]
TableB [int colX, int colY, String colZ, int data] : primary Key [colX+colY+colZ]

TableA to TableB has 1->Many mapping.
Foriegn Key for TableB is Primary Key of TableA.

So based on an entry in TableA I should be able to find an entry in TableB.

How do I generate finder methods like this..

findBycolA : If I have just colA value in tableA -> get all matching rows from TableB which has same value in colX.
Similarly findByColB, findByColC.

findBycolA&colB: If I have values of colA and colB of TableA -> get all matching rows from TableB which has same values in colX and colY.
Similary findBycolB&colC and findByColA&colC

findBycolA&colB&colC

Do I have to manually edit generated files. If so how do I handle when I regenerate.

Please let me know.

batmat · **Posted:** Thu Mar 22, 2007 3:29 am

You're using composite keys? As primary keys, they can't be nullable.

mkk · **Joined:** Wed Mar 21, 2007 3:02 pm **Posts:** 4

I did not understand what you said.
Yes, I am using composite primary key.

I can search on the complete primary key, i.e, with all fields in composite key.

I want to know if I can search with just 2/3 fields of my composite key.

Can you please point me to a some documentation where I can get some idea on this.

Thanks

mkk · **Joined:** Wed Mar 21, 2007 3:02 pm **Posts:** 4

Example:

TableA( colA, colB, colC, data)
TableB (colX, colY, colZ, data)

1->Many relation from TableA to TableB

Composite Primary key on TableA (colA+colB+colC)
Composite Primary Key on TableB (colX+colY+colZ)

Foriengn Key on TableB references TableA(colX=colA+coly=colB+colZ=colC)

If TableA has data like:

ColA colB colC data
===============
1 1 1 d1
2 2 2 d2
1 1 3 d3

If TableB has data like:

colX colY colZ data
================
1 1 9 x1
2 2 2 x2

Now I know I can get data = x1 from TableB if I know the key for TableA (1,1,1)

However if I know just part of the primary key in TableA(1,1)
I still want to be able to get (data=x1 from TableB) as there is partial match of composite Key.

If this is not possible, can you please point me why I cant do so.

Thanks

batmat · **Posted:** Thu Mar 22, 2007 11:32 am

Did you already map it? Try using "id.propA" (when propA is the property mapped to colA), "id.propB" and so on.

mkk · **Joined:** Wed Mar 21, 2007 3:02 pm **Posts:** 4

I am looking for something like this: (Please see that it uses all elements of composite key)

Code:

         TableADAO dao = new TableADAO();         
         // Finding by all the values in primary key
         TableAId id = new TableAId("1","1","1");
         TableA tabA = dao.findById(id);
         Set tabBs = tabA.getTableBs();
         for (Iterator iter=tabBs.iterator(); iter.hasNext();){
            TableB tabB = (TableB)iter.next();
            System.out.println(tabB.getData());
         }

Now I want to do the same with just two columns of the 3 column composite key.
All I can comeup with is

Code:

List bList = session.createCriteria("com.ccmc.beans.TableB")
         .add( Restrictions.eq("id.colX", "1") ) 
         .add( Restrictions.eq("id.colY", "1") ) 
         .list(); 
         for (Iterator iter=bList.iterator(); iter.hasNext();){
            TableB tabB = (TableB)iter.next();
            System.out.println(tabB.getData());
         }

But I think this is as good as working on just tableB.

......

batmat · **Posted:** Sat Mar 24, 2007 2:56 pm

1) What is your mapping?
2) What does exactly not suit you in the query you gave? If it's the use of id.colA, then just be aware that you don't have to use column name, but property name.

batmat wrote:

Did you already map it? Try using "id.propA" (when propA is the property mapped to colA), "id.propB" and so on.