5649

Mapping relational DB to a List each containing a List using JdbcTemplate

Question:

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

Say I have the following 2 tables :

<strong>table_school</strong>

ID NAME

<strong>table_students</strong>

ID NAME ADDRESS SCHOOL_ID

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

int id, String name, List<Student> students

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

Many thanks !

Answer1:

Yes, you can fetch all data in 1 query.

<strong>Simple example:</strong>

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<Student> 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<Integer, School> 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<Integer, School> 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 <strong>performance</strong>:

If you expect many records to fetch it is nearly always a good idea to increase <strong>jdbc fetch size</strong> 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); } }

Recommend

  • Simple Injector: Different DbContext for selected controllers
  • Auto Grouping/Merging in Excel
  • else if statement in javascript not able to display validation message
  • How do I write to registers in hardware using Python?
  • CodeIgniter - Autoload
  • C++ usrsctp callback parameters null
  • Cakephp Form Helper
  • Linked tables and Slicer in excel
  • wordpress query - next two events by metadata date
  • Find first match in table range
  • Memory dump much smaller than available memory
  • d3js: time scaling and “1901”
  • How to work with multiple CSS properties?
  • Are there algorithms for putting a digest into the file being digested?
  • How to filter entities that are deleted using linq to entities
  • How to apply async task into this
  • Play Framework nested form errors missing
  • Installing PAR::Packer on Windows, dmake error 255
  • Adding new column to DataFrame with values dependent on index ref
  • Aggregate all dataframe row pair combinations using pandas
  • Is C++ compilable with OpenMP and boost on MacOS?
  • firebase, how to update data at a key
  • NHibernate proxyexception
  • Jquery UI Sortable, move item automatically
  • Sybase Error Implicit Conversion from datatype 'VARCHAR' to 'INT' not allowed
  • Creating a Multidimensional, Associative Array in VBScript
  • apply a javascript function to draggable copy
  • drawing random circles, storing their coorindates in an array
  • Aptana 3 remove bundle (jquery)
  • netsh acl setting (need alternative method - registry settings?)
  • Primefaces ManyCheckbox inside ui:repeat calls setter method only for last loop
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • EntityFramework adding new object to nested object collection
  • Checking variable from a different class in C#
  • failed to connect to specific WiFi in android programmatically
  • UserPrincipal.Current returns apppool on IIS
  • How can I use threading to 'tick' a timer to be accessed by other threads?
  • How do I use LINQ to get all the Items that have a particular SubItem?