MyBatis SQL Data Mapping

Continuing from the previous post on Object Relational Mapping let us look at MyBatis SQL Data Mapper framework implementation.

The following are prerequisites if you plan to import the test project onto local machine and run code locally:
1. MySQL Database (Entity model and database schema is explained in the previous post)
2. Java 1.7
3. Eclipse (Java EE Editor Kepler)
4. Eclipse Plugins
        - Git
        - Maven
        - Gradle (Optional for gradle lovers)
        - MyBatis Generator

MyBatis is the successor to the open source Apache IBatis project started by Clinton Begin in 2002. MyBatis is a data mapper framework that abstracts boiler plate JDBC code with simplistic SQL Mapping to the persistence layer. 

The core components in MyBatis are:

1. XML Configuration files (yep xml still alive and kicking) but again xml configuration is optional , configuration can be done use java annotations as well.
2. XML Mapper files where the Object Relational Mapping or more strictly SQL Data Mapping occurs. These mapper files contain named mapped statements with SQL.
3. SqlSessionFactory, which opens up a database connection and provides the SqlSession core component which performs SQL Data Mapping, executing SQL on RDBMS and object mappings based on xml mapper configurations. Every MyBatis application centers on the SqlSessionFactory. 

That it, that’s what MyBatis application generally involve. More details on each of the components can be found on MyBatis Reference Documentation which is quite lean and neat.

MyBatis Overview (This Image comes from the Google Code MyBatis Site)

Image from Google Code MyBatis Site

The general steps in writing a MyBatis SQL Data Mapper Implementation involve:

1. Creating xml configurations file yep (mybatis-config.xml). This xml file will include settings for the dataSource connection details, any transaction manager, properties, mappers – which point to the SQL Mapper configuration files.
2. Creating the SQL Mapper Configuration files containing the SQL Data Mapping and mapped statements
3. Classes that use SQLSessionFactoryBuilder to retrieve instance of the SqlSessionFactory and acquire the SqlSession to execute mapped statements and queries.  

This looks pretty straightforward on paper but involves lots of typing to get the initial configuration correct, and may take a few iterations to get it right. Thankfully there are many side projects on the MyBatis ecosystem and MyBatis Generator is one such project that saves the day, making initial set up quick and simple.

Let start by first importing the project into eclipse workspace and then review each of the core modules that facilitates SQL Data Mapping using MyBatis.

Clone git repository and import test project:

The test project - com.malcolm.daotest is my attempt to look look at SQL Data Mapping and Hibernate ORM framework and validate ease of use and set up, strengths and weakness. The project uses Spring framework to bind together core components using annotations only, no xml configurations are used. Data access objects are implemented in MyBatis, Hibernate native (Session Factory and Criteria API), Hibernate JPA using Entity Manager and Spring Data JPA wrapper which uses the Hibernate JPA implementation.

The project is hosted on github so you may need to have git available on local machine to clone the project locally or if not using git you can still download the source code from github. The screen shots and code walk through uses Eclipse IDE (Kepler EE) with maven, git, gradle and mybatis generator plugins. All of these are not necessarily a requirement but eases lot of pain points during development.  

Using the eclipse git plugin import the project from github and use the following URI https://github.com/MalcolmPereira/com.malcolm.daotest.git leave rest as default and click next selecting the master branch.

Next specify a destination on your local machine where the repository will be cloned, and leave default setting and click on Next.

Next select import existing projects wizard option and import the project into the eclipse workspace by clicking on the finish button. 

If all goes well the project should be imported in the eclipse workspace with no issue.

If you are using gradle plugin and need to turn on gradle nature, right click on the project and activate gradle by selecting gradle - refresh all. 

MyBatis Generator:

Let us first examine core sections of the MyBatis generator configuration file (generatorConfig.xml), as mentioned earlier MyBatis generator aids in initial configurations to get started with sql mappers and generating the required java classes.

The following is the basic minimum setting required in the MyBatis generator configuration file.

Database Driver and Database Connection Settings:

MyBatis generator works by introspecting the database to generate required sql mappers and base model objects, so we need a database driver to connect to the database, mysql in this case. Please update the classpath entry to valid location of the mysql connector jar file. Specify required jdbc connection attributes with connection URL and database credentials.

Java Model Generator:
Java Model Generator generates the object model including primary keys. Generation of the model objects will depend on the table setting definitions described next. The target package instructs the model generator where to generate the model java objects.

Java Client Generator:
The Java Client Generator generates Java interfaces and classes that allow easy use of the generated Java model and XML map files. Generation of xml map files is controlled by type attribute which can take values - XMLMAPPER, ANNOTATEDMAPPER and MIXEDMAPPER, we are using XMLMAPPER which will generate XML map files.




SQL Map Generator:
The SQL Map Generator generates the SQL Mapper files when the client generator type is XMLMAPPER.

Table Setting:
The Table element is used to select a table in the database for introspection. Selected tables will cause the following objects to be generated for each table - SQL Map File, Java Model reflecting the columns in the database table, DAO interface class.

Please refer to MyBatis XML configuration reference for more details. 

MyBatis generator can be run from the command line or easier way by using the MyBatis Generator Eclipse plugin and right clicking on the generator config file in eclipse.

This should generate the corresponding packages and java objects.












com.malcolm.daotest.mybatis.mapper  - Mapper Interfaces:

The mapper package contains the mapper interfaces. MyBatis generator will generate definitions for basic CRUD (Create Read Update and Delete) operations.

com.malcolm.daotest.mybatis.model - Model Objects:
The model package contains the entity model created by introspecting the database and settings specified in the table attribute definitions of the MyBatis generator configuration file.

com.malcolm.daotest.mybatis.sqlmap - SQL Mapper resources:

The sqlmap resource package contains the sql named mappers that will be used to map entity object to SQL operations for the CRUD definitions  in the mapper interfaces.

Extending MyBatis Generator Components:

MyBatis generator will generate code for a typical CRUD application which should suffice for most use cases, however one drawback of MyBatis generator is that it does not generate code for relationships and does not have any way of expressing for them, for example: Employee may belong to one Department, have a Designation, play one ore more Role and be associated to zero or more Project. To express relationships via composition or aggregation we may need to update the generated model manually to include additional relationships.

Employee.java entity model object:

If we need extended join queries joining multiple tables, example - Employee, Department, Designation, Roles and Project, MyBatis Generator generated code will not work, in such cases better to create new mapper interface and sql mapper files which will contain the extended join queries and mappers. 

EmployeeMapperExt.java mapper interface:

EmployeeMapperExt.xml sql map:

Complex associations and collections can be used in the sql map xml files to build object relationship trees. Please check out Mapper XML file for further details.

MyBatis DAO Components:

Now that we have the model, mappers and sql map how do we translate these to valid data access object components ?. We create DAO classes by implementing the mapper interfaces and use the SqlSession object to execute named queries in the sql map files.

I am using MyBatis-Spring which offers seamless integration between MyBatis and Spring. 

BaseDAO.java: Looks up sql mapper using the SQLSession,

EmployeeDAO.java: Extends the BaseDAO and implements the mapper interfaces.

SQLSession is used is obtained via org.mybatis.spring.SqlSessionTemplate which ensures that the SqlSession is associated with running Spring transactions. In addition, SqlSessionTemplate manages the session life-cycle, including closing, committing or rolling back the session as necessary.

MyBatis Spring Configuration:

Putting it all together we use a spring configuration object which helps in defining spring beans via annotations and autowire the SQLSession.

MyBatisDAOConfig.java:

MyBatis Implementation Design:

Strengths:

1. No JDBC boiler plate code. 
2. Low learning curve, simple and straight forward.
3. Works with most RDBMS and other legacy databases that support SQL.
4. Can be easily integrated using dependency injection frameworks.
5. Full control of SQL which can be fine-tuned to improve performance interacting with the underlying RDBMS.
6. Supports connection pooling and caching mechanisms to further aid overall performance. 

Weakness:

1. Pagination, no implicit support of pagination for large resultsets.
2. Full control over sql, which means sql is tied to a specific engine, porting to another database may need tweaks to the sql (not really a weakness per say not sure how many times that will happen :)) 
3. Can quickly get out of control as sql maps - result columns are randomly updated by developers to suite functional requirements, hard to maintain clean relational model both at object level and relational database level. 
4. If application has full control on database schema and object model, it would be better to use a pure Object Relational Mapping framework like Hibernate / JPA.

Please refer to MyBatis reference for further details.    

Back to previous post on Object Relational Mapping.

Until next time,
Malcolm

Back to Java Thoughts