java - Multiple one to many in Resultsetextractor

Question

I have 2 one to many relations. How can I insert all 3 classes in resultsetextractor?

For an example one student can enrolls many courses. One course can have many subjects. how can I put this resultsetextractor?

My extractor is like this. I have 3 classes with attributes for Students, Courses and Subjects where I have all the getters and setters and 3 classes are properly linked.

public class DetailExtractor implements ResultSetExtractor<List<Students>> {
    @Override
        public List<Students> extractData(ResultSet rs) throws SQLException, DataAccessException {
         
         Map<String, Students> result = new LinkedHashMap<>();
         Map<String, Courses> resultCourses = new HashMap<>();
         Map<String, Subjects> resultSubjects = new HashMap<>();
            
            while (rs.next()) {
                
                String StudentID = rs.getString("studentid");
                Students oneRecord = result.get(StudentID);
                
                   if (oneRecord == null ) {
                oneRecord = new Students();
                result.put(StudentID , oneRecord);
                
                try {oneRecord.setStudentId(rs.getString("studentid"));} catch (Exception e) {} 
                try {oneRecord.setStudentName(rs.getString("studentname"));} catch (Exception e) {} 
                try {oneRecord.setAge(rs.getString("age));} catch (Exception e) {}  
                
                   }
                   
                   while (rs.next()) {
                       String CourseID = rs.getString("courseid");
                       Courses scRecord = resultCourses.get(CourseID);
                                            
                       if (scRecord == null ) {
                           scRecord = new Courses();
                           resultCourses.put(CourseID , scRecord);
                          
                           oneRecord.addCoursesItem(scRecord); 
                           
                 try {scRecord.setCourseId(rs.getString("courseid"));} catch (Exception e) {}   
                 try {scRecord.setCourseName(rs.getString("coursename"));} catch (Exception e) {} 
                 try {scRecord.setLevel(rs.getString("level"));} catch (Exception e) {} 
                
                       }
                 Subjects thRecord = new Subjects();
                 scRecord.addSubjectsItem(thRecord);
                 
                 try {thRecord.setSubjectId(rs.getString("subjectid"));  } catch (Exception e) {}   
                 try {thRecord.setSubjectName(rs.getString("subjectname"));  } catch (Exception e) {}   
                 try {thRecord.setTopics(rs.getString("topics"));  } catch (Exception e) {}     
                    
                
     }
            }
            return new ArrayList<>(result.values());
    }
}

I need to get an output like this:

{
 studentID:S001,
 studentName:Steve,
 Courses:[
  {
   courseId:C001,
   courseName:SoftwareEnginnering,
   level: Beginner,
   Subjects:[
     {
      subjectID:SUB1,
      subjectName:Java,
      topics:variables,if loop,while loop
     },
     {
      subjectID:SUB2,
      subjectName:Data Structure and Algorithms,
      topics: Topic1,topic2
     },
    ]
  }, 
  {
   courseId:C002,
   courseName:HR,
   level: Beginner,
   Subjects:[
     {
      subjectID:SUB01,
      subjectName:xxx,
      topics:x1,x2,x3
     },
     {
      subjectID:SUB02,
      subjectName:yyy,
      topics: y1,y2
     },
    ]
  }
 ]
}

Basically Courses and Subjects won't be repeated. Using this code, I can get an output if I give * and get all table columns. In my case user can give his required columns as an input. So they will only get the required columns in the output. * = all columns , I can get an output. But if I want to get studentId ,StudentName and CourseId , then it's giving an error.

SQL state [null]; error code [0]; Could not find courseid in [studentid]; nested exception is java.sql.SQLException: Could not find courseid in [studentid] with root cause java.sql.SQLException: Could not find subjectid in [studentid]

How can I fix this?

Answer 1

等待大神答复