MyBatis Exploration

We are exploring MyBatis as a faster, more flexible data access framework than Hibernate.

What is MyBatis?

MyBatis maps Java objects to SQL result sets. This is different from Hibernate, which maps Java objects to RDBMS entities (tables or views) and generates the SQL for us. With MyBatis, you code SQL statements and map the result sets to Java POJOs. The quirks of the legacy MITSIS database are more readily handled by SQL than by Hibernate's object-relational model.

How Does MyBatis Work?

  1. You have Java POJOs designed to hold the data.
  2. You have a SQL statement that will return rows of data; each row will contain one or more columns. 
  3. You have a mapping configuration that specifies how to move the SQL result set data into the Java POJOs.
  4. You have a mapper Java interface that provides the ability to actually run the query and populate the POJOs.

The Pieces

A sample project is available at <svn+ssh://svn.mit.edu/es-common-services-framework/sturner/mybatis-deptaudit-test>

This project uses MyBatis to retrieve the data involved in the Departmental Audit API. This involves getting a list of students who are in a particular department for a given term. For each student, various sets of data are retrieved. For this overview, we'll concentrate on the students and their subjects.

1. The Java POJOs

The simplified object model is:

Gliffy Macro Error

You do not have permission to view this diagram.

2. The SQL Statement

The SQL query to get the relevant students (for a given department and term) and the student's subjects is:

select stu.*, subj.* from stv_dept_audit_ug_student_view stu
   join stv_dept_audit_subject_view subj on stu.pidm = subj.pidm
   where stu.crse_dept = #{deptId} and stu.term_code = #{termCode}

(#{deptId} and #{termCode} are MyBatis placeholders for the query parameters)

In the MyBatis confguration, we assign a name to the SQL statement - this will also be the name of the method used to execute the query. In the sample project, this name is "getStudentsByDeptAndTermWithSubjects". The full XML configuration element for the SQL statement can be found in the project file src/main/resources/mappers/DeptAuditUgStudent-mapper.xml

3. The SQL-POJO Mapping

Also in src/main/resources/mappers/DeptAuditUgStudent-mapper.xml we find the XML that links the fields in the SQL results set to the properties in the POJOS. Here's the full configuration:

	<resultMap id="studentResult" type="edu.mit.csf.deptaudit.domain.DeptAuditUgStudent">
		<result property="termCode" column="term_code" />
		<result property="courseDept" column="crse_dept" />
		<result property="mitId" column="mit_id" />
		<result property="pidm" column="pidm" />
		<result property="lastName" column="last_name" />
		<result property="firstName" column="first_name" />
		<result property="middleInitial" column="middle_initial" />
		<result property="unitsCompletedBeyondGir" column="units_ubs_completed" />
		<collection property="subjects" ofType="edu.mit.csf.deptaudit.domain.DeptAuditSubject">
			<result property="pidm" column="pidm" />
			<result property="termCode" column="term_code" />
			<result property="subjectCode" column="subj_code" />
			<result property="subjectNumber" column="subj_numb" />
			<result property="units" column="total_units" />
			<result property="hgn" column="hgn_code" />
			<result property="grade" column="grade" />
			<result property="studentSubjRegId" column="student_subj_reg_id" />
			<result property="votes" column="vote" />
		</collection>
	</resultMap>

This mapping is an XML element called "resultMap" and has a name to identify it (studentResult) and the name of a POJO that will hold the data from one row of the SQL result set.

The <result> elements specify how individual fields in the SQL result are mapped to POJO properties. For example, the mit_id column in the SQL result is mapped to the mitId property in the Java  DeptAuditUgStudent class.

The <collection> element represents the one-to-many nature of the relationship between Student and Subject. The DeptAuditSubject class name is given as the type of object held in the collection. The result set columns drawn from the stv_dept_audit_subject_view in the SQL query are mapped to their POJO equivalents by using the <result> elements.

Using this configuration, MyBatis will automatically aggregate the data so that each Student object will include the relevant collection of Subject objects.

4. The Mapper Interface

Lastly we need a way to actually execute the query and get a hold of the Java objects containing the data. This is done via an interface - the name of the interface is referred to by the mapping XML, and in the example project the interface is edu.mit.csf.deptaudit.mappers.DeptAuditUgStudentMapper

To provide a way to run the SQL statement, we put a method in the interface - the method name matches the name of the SQL <select> element we defined in section 2 above: getStudentsByDeptAndTermWithSubjects. The method signature looks like this:

 List<DeptAuditUgStudent> getStudentsByDeptAndTermWithSubjects(
             @Param("deptId") String deptId, 
             @Param("termCode") String termCode);

Making It All Work

Now that all the pieces are in place, how do we actually retrieve real data from the database?

The sample project uses a Spring-MyBatis module that enables the app to configure the MyBatis system at startup via bean definitions, and provides for dependency injection of the DeptAuditUgStudentMapper interface into classes where it's needed. The sample app's config is in src/main/resources/applicationContext

  1. Similar to Hibernate, we configure a data source and a MyBatis "SqlSessionFactory".
  2. We configure a MapperScannerConfigurer bean so that MyBatis knows where to find the mapping xmls.

To exercise all of this setup, there are several JUnit tests in csf.DeptAuditTest - the test that runs the query we've described above is testGetStudentsByDeptAndTermWithSubjects(). The test class is set up to run using Spring's JUnit runner: it loads the applicationContext and injects the DeptAuditUgStudentMapper interface. The test itself simple calls the relevant interface method (getStudentsByDeptAndTermWithSubjects) passing in department ID and term code as parameters. It receives a list of DeptAuditUgStudent objects; each of these object (representing a single student) has its subjects property fully populated with DeptAuditSubject objects.


 

 

  • No labels