How to use jdbcTemplate and rowmapper, if my dto class contains list in it?

Question

I want to use rowMapper or jdbctemplate for one-to-many relation. My dto class contains List of questions in course dto.

public class CourseQuestionsDto {

        @NotNull(message="Course Id cannot be null.")
        private Integer courseId;
        
        @NotBlank(message="Course name cannot be blank.")
        private String courseName;
        
        private List<QuestionDto> questionsDto;
    }

And my question dto has few required fields, and also I have course dto class to get course details

I want to display all the questions under course in the format of "COURSEQUESTIONSDTO class" using jdbcTemplate.

I have tried something like this.

public List<CourseQuestionsDto> getActiveQuestion(){
        String courseSql="select course_id,course_name from mst_question where is_active=true";
        String questionSql="select * from mst_question where is_active=true and course_id=:courseId";
        String optionSql="select * from mst_question_option where question_id=:questionId";
        return getQuestion(courseSql,questionSql,optionSql);
    }

and getQuestion() method.

public List<CourseQuestionsDto> getQuestion(String courseSql,String questionSql,String optionSql){
        
        List<CourseDto> courseList= namedJdbcTemplate.query(courseSql, new CourseRowMapper());
        List<CourseQuestionsDto> courseQues =new ArrayList<CourseQuestionsDto>();
        Map<String,Object> courseMap=new HashMap<String, Object>();
        
        for(CourseDto course:courseList) {
            CourseQuestionsDto setCourse=new CourseQuestionsDto();
            courseMap.put("courseId",course.getCourseId());
            List<QuestionDto> questionList=namedJdbcTemplate.query(questionSql,courseMap, new QuestionRowMapper());
            List<QuestionDto> questionListReturn = new ArrayList<QuestionDto>();
            
            for(QuestionDto question:questionList) {
                SqlParameterSource questionMap=new MapSqlParameterSource("questionId",question.getQuestionId());
                List<QuestionOptionsDto> optionList=namedJdbcTemplate.query(optionSql,questionMap, new QuestionOptionRowMapper());
                
                question.setQuestionOptionsDto(optionList);
                questionListReturn.add(question);
                
            }
            setCourse.setCourseId(course.getCourseId());
            setCourse.setCourseName(course.getCourseName());
            setCourse.setQuestionsDto(questionListReturn);
            courseQues.add(setCourse);
        }
        
        return courseQues;
    }

Please help me with it, I have tried so many ways to do it but I couldn't. The above code is working fine, but it displays the courses that are not present in questions table.

I have tried above code. It displaying the courses that are not present in my question table. something like this.

"response": [
    {
      "courseId": 1,
      "courseName": "Induction",
      "questionsDto": [
        {
          "questionId": 2,
          "question": "How are you"
          "questionOptionsDto": [
            {
              "optionId": 1,
              "optionName": "string 1",
              "isActive": true,
              "isDeleted": false
            }
          ]
},
{
      "courseId": 2,
      "courseName": "string",
      "questionsDto": []
    }
  ]

score 0 · Answer 1 · answered May 14 '23 at 11:36

It looks like that you have the problem with your SQL query for fetching the courses. You are querying the mst_question table to get the list of courses, which will return all the courses even if there are no questions associated with them.

You need to change your SQL query to fetch only those courses that have at least one question associated with them. You can do this by using a subquery to count the number of questions associated with each course and filter out courses that have no questions.

SELECT c.course_id, c.course_name
FROM mst_course c
WHERE c.is_active = true
AND EXISTS (
  SELECT 1 FROM mst_question q WHERE q.is_active = true AND q.course_id = c.course_id
)

I hope this query will help you!

How to use jdbcTemplate and rowmapper, if my dto class contains list in it?

1 Answers1