CODEWITHSUNDEEP
hibernatespring
Chandu
Chandu

Reputation: 75

Removing the duplicate rows from the result set

I am using Hibernate Template. From the controller I am calling DAO method like below:

List<SearchQuestionnaire> lst = 
           questionnaireDAO.listQuestionnaire(searchQuestionnaire);

In the DAO I am getting the result set like below:

String queryString1 = "select 
  new com.apple.ist.cm.ruleadmin.model.SearchQuestionnaire
      (qc.questionCategoryID,
       qc.questionCategoryName,
       qn.questionReferenceString,
       qn.question,
       qao.answerOptionGroupID,
       aog.answerOptionGroup,
       ans.answerStringValue) "
      + "from Questionnaire qn,
              QuestionCategory qc,
              QuestionAnswerOption qao,
              AnswerOptionGroup aog,
              AnswerString ans " 
      + "where 
             qn.questionCategoryID = qc.questionCategoryID 
         and qn.questionID=qao.questionID 
         and qao.answerOptionGroupID=aog.answerOptionGroupID 
         and qao.answerStringID=ans.answerStringID";

query1 = (List<SearchQuestionnaire>) hibernateTemplate.find(queryString1);

Now from the query1, I am getting results set like below:

  A    | B  | C  |    D
  -----|----|----|-----
    a1 | b1 | c1 | d1
    a1 | b1 | c1 | d2
    a1 | b1 | c1 | d3
    a2 | b2 | c2 | d4
    a2 | b2 | c2 | d5

Now in JSP, I want to display all the values of A, B, C, D. But A, B and C values in one row and when expand the row D values should be displayed like below:

   a1 | b1  | c1      
   -------------
     d1
     d2
     d3 

   a2 | b2  | c2      
   -------------
     d4
     d5

Now I am facing an issue in removing the duplicate values in the columns A, B and C.

Please suggest on the above. Here please consider A, B, C and D are the values of "queryString1" select clause.

Upvotes: 0

Views: 1036

Answers (1)

JB Nizet
JB Nizet

Reputation: 692171

Create a class ABC, containing A, B and C. Make it immutable and implement hashCode and equals, so that two instances of ABC having the same values for A, B and C have the same hashCode and are equal to each other.

Then, build a Map<ABC, List<D>> storing all the Ds indexed by their ABC tuple. Iterate over the entries of the map. Each entry will lead to a new ABC section. For each entry, iterate over its list of Ds. Each element will lead to a new line in the section.

Note that your HQL query shows that you didn't map association between entities as they should be. You should almost never create a join between two entities like this: rather than holding the ID of its question, a QuestionAnswerOption should have a direct association (a ManyToOne association) with its Question.

Upvotes: 1

Related Questions