Monday, December 22, 2008

FETCH DISTICT ROWS USING HIBERNATE

Using Projections
------------------------------

DetachedCriteria criteria = DetachedCriteria.forClass(DATATABLE.class);
ProjectionList projList=Projections.projectionList();

// add the restrictions to the criteria
criteria.add(Restrictions.like("teamName",
CloConstants.PERCENTAGE + name.toUpperCase() + CloConstants.PERCENTAGE));

// distinct on teamId, also adding the two properties teamId and TeamName will return only two properties from the DB.
projList.add(Projections.distinct(Projections.property("teamId)));
projList.add(Projections.property("teamName"));

// hibernateTemplate for springs
List teamList = hibernateTemplate.findByCriteria(criteria);
System.out.println("teamsList :"+teamList.size());


// The List, teamList returned will be a list of Object. In order to retrieve a list of DATATABLE objects, use setResultTransformer. But this retrieves all the columns from the table DATATABLE. criteria.setProjection(projList).setResultTransformer(Transformers.aliasToBean(DATATABLE.class));


Using Named Query
------------------------------------------
var names = (from dr in dataTable.Rows
select (string)dr["Name"]).Distinct().OrderBy(name => name);

Using Query by Example
----------------------------------------

DataTable dataTable = new DataTable ();
//Search by the name
dataTable.setName("I%");
dataTable.setTeamName (CloConstants.PERCENTAGE + teamSearchName.toUpperCase() + CloConstants.PERCENTAGE);
Example example= Example.create(dataTable);
//exclude a particular column in the table
example.excludeProperty("teamIInactiveFLag");
example.excludeZeroes();
example.enableLike();
criteria.add(example);

No comments: