Announcement Announcement Module
Collapse
No announcement yet.
Using jdbcTemplate on Join table Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using jdbcTemplate on Join table

    Hello,

    I'm not sure which method to use to retrieve the following record into a Student Object with a List of Classes.

    sql = "SELECT * FROM students as s, courses as c WHERE s.student_id=5 and s.student_id=c.student_id";

    I have a Student Object that contains a Set of courses, and I would like to return the Student with the Set of courses pre-populated. So, after execution I would like to return 1 Student object with 10 Courses. Previously I was using:

    Code:
    List rows = jdbcTemplate.query(sql, ids, idTypes, new RowMapper() {
         public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
               .......
         }
    }
    Any ideas how I can approach this problem?

    Thanks.

  • #2
    Your current query will yield something like this:
    Code:
    s.id            s.name        c.course
    5               'John Doe'    'Biology'
    5               'John Doe'    'Chemistry'
    5               'John Doe'    'Calculus'
    That is repetitious data.

    RowHandler will process one row at a time, getting in the way of your attempt to collect this as a list. Instead, you need to just query for courses, and stick it into your student object.

    Code:
    List<Student> studentResults = new Vector<Student>();
    for (String name : new String[]{"Joey", "Chandler", "Ross"}) {
    
        // Lookup student id based on name
        Long id = jdbcTemplate.queryForLong(
            "select id from student where name = ?",
            new Object[]{name});
    
        // Using student id, look up courses.
        List courses= jdbcTemplate.query(
           "SELECT * FROM courses as c WHERE c.student_id = ?",
            new Object[]{id},
            new RowMapper() {
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                   .......// This converts each COURSE row
             }
    
        Student s = new Student(id, courses);
        studentResults.add(s);
    }
    You can fetch the courses in one query, but it would require writing a sub-select like:
    Code:
    select * from courses
    where c.student_id in (
        select id
        from student
        where name = ?
    )
    Now we are getting into SQL-specifics.

    Comment


    • #3
      thank you

      Thanks. that did the trick

      Comment


      • #4
        Well that makes you do 2 queries, instead of using a RowMapper use a ResultSetExtractor. That way you have full control over what you do with the resultset (you will also do the iteration yourself.

        //Psuedo code
        Code:
        public class StudentExtractor implements ResultSetExtractor {
        
          public Object extractData(ResultSet rs) throws DataAccessException, SQLException {
            Student student = null;
            while (rs.next()) {
              if (student == null) {
                //create student based on first row
              }
        
              Course course = //create course based on row
              student.addCourse(course);
            }
            return student;
          }
        
        }
        That saves you an extra roundtrip to the database.

        Comment


        • #5
          Join query works with a custom ResultSetExtractor class

          Thanks a lot Marten Deinum, tried the Join query with a custom ResultSetExtractor class and it works. Thanks a lot again Marten

          Comment

          Working...
          X