jdbctemplate mysql 分页查询 返回list对象,将关系数据库映射到List< Object>.每个包含一个List< Object>.使用JdbcTempla...

I am using Spring MVC with JdbcTemplate and a MySQL database.

Say I have the following 2 tables :

table_school

ID NAME

table_students

ID NAME ADDRESS SCHOOL_ID

I have a School POJO that has the following class variables :

int id, String name, List students

Is there a way of retrieving a List with each School object containing the appropriate List of Student objects using JdbcTemplate in one query? I know this is easily achievable using Hibernate but I would like to use JdbcTemplate ..

Many thanks !

解决方案

Yes, you can fetch all data in 1 query.

Simple example:

class Student {

int id;

String name;

String addr;

Student(int id, String name, String addr) {

this.addr = addr;

this.id = id;

this.name = name;

}

}

class School {

int id;

String name;

List students = new ArrayList<>();

School(int id, String name) {

this.id = id;

this.name = name;

}

void addStudent(Student s) {

students.add(s);

}

}

/*

* helper method that gets school from map or create if not present

*/

private School getSchool(Map schoolMap, int id, String name) {

School school = schoolMap.get(id);

if (school == null) {

school = new School(id, name);

schoolMap.put(id, school);

}

return school;

}

// RUN QUERY

String sql =

" select st.ID, st.NAME, st.ADDRESS. s.id, s.name" +

" from table_students st" +

" inner join table_school s on st.school_id = s.id";

final Map schoolMap = new HashMap<>();

jdbcTemplate.query(sql, new RowCallbackHandler() {

@Override

public void processRow(ResultSet rs) throws SQLException {

int studentId = rs.getInt(1);

String studentName = rs.getString(2);

String studentAddr = rs.getString(3);

int schoolId = rs.getInt(4);

String schoolName = rs.getString(5);

Student student = new Student(studentId, studentName, studentAddr);

getSchool(schoolMap, schoolId, schoolName).addStudent(student);

}

});

One final point regarding fetching performance:

If you expect many records to fetch it is nearly always a good idea to increase jdbc fetch size parameter. So before run query set it on your jdbcTemplate:

jdbcTemplate.setFetchSize(200); // you can experiment with this value

or if you are using spring's JdbcDaoSupport you can use such pattern:

public class MyDao extends JdbcDaoSupport {

....

@Override

protected void initTemplateConfig() {

getJdbcTemplate().setFetchSize(200);

}

}