February 3, 2011

Part 7: Configuring MyBatis

If you have followed the first parts of this tutorial Part 1 to Part 6 you should now have a very simple MySQL database setup and the java layer ready for the ModelingAgency web application.

Now I'm going to show you how to integrate your database and java model layer with MyBatis 3

  • Setting up the MyBatis configuration file
  • Configuring the mapper file

The MyBatis configuration file

First it's time to add the main MyBatis configuration file to your web application. You can call this file anything you want, but let's use mybatis.config.xml for now.

This file will hold all the MyBatis configuration info and references to all the other sql mapping files we will define.

Create the following file /src/mybatis.config.xml:


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 
 "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- Created by 2011: http://blog.idleworx.com http://www.idleworx.com -->
<configuration>
 
 <properties resource="dbcp.properties"/>
  
 <settings>
  <setting name="useGeneratedKeys" value="true"/>
 </settings>
    
 <typeAliases>  
  <typeAlias alias="Client" type="com.modelingagency.objects.Client"/>
  <typeAlias alias="Model" type="com.modelingagency.objects.Model"/>
  <typeAlias alias="Booking" type="com.modelingagency.objects.Booking"/>  
 </typeAliases>
  
 <environments default="development">
  
  <environment id="development">
   <transactionManager type="JDBC"/>
   <dataSource type="JNDI">
    <property name="initial_context" value="java:comp/env"/>
    <property name="data_source" value="/jdbc/mydb"/> 
   </dataSource>
  </environment>
  
  <environment id="testing">
   <transactionManager type="JDBC"/>
   <dataSource type="POOLED">
    <property name="driver" value="${db.driver}"/>
    <property name="url" value="${db.url}"/>
    <property name="username" value="${db.user}"/>
    <property name="password" value="${db.pass}"/>
   </dataSource>
  </environment>
    
 </environments>

 <mappers>
  <mapper resource="com/modelingagency/objects/SimpleMappers.xml"/>
 </mappers>

</configuration>

Also, create the following file /src/dbcp.properties:

db.url=jdbc:mysql://localhost/modeling_agency
db.driver=com.mysql.jdbc.Driver
db.user=test
db.pass=test

Notes

  • You'll notice we have a 'testing' environment setup. We'll use that for testing our app with jUnit.
  • Using the useGeneratedKeys property will allow MyBatis to make use of the autogenerated row id values from the database, as we defined in the SQL in Part 2: client_id INTEGER NOT NULL AUTO_INCREMENT
  • typeAliases will simplify things for you, by helping MyBatis identify fully qualified java class names. This way you won't have to type the fully qualified names of your objects in MyBatis mapper files
  • In a typical application there are usually many SQL mapper files, however for our simple application we only really need one, the SimpleMappers.xml file
  • The mybatis.config.xml file should go under /src/mybatis.config.xml (this is because the file should be on the application's classpath)

Tip:If you're wondering how to setup multiple environments in MyBatis see my other blog posting: Initializing MyBatis Using a ServletContextListener

Configuring the Mapper file

MyBatis SQL mappers allow us to map our java data model to our database model.

The mapper file holds all the sql statments you need to interact with your database, so it's pretty important, and it also happens to be a critical element of the MyBatis framework, which will simplify your life a lot, once you pass the learning curve :)

The mapper file also stores definitions of objects that will be used to retrieve data from the database. By doing this MyBatis abstracts most of the hard work of writing tons of sql statements, and most importantly jdbc code.

Since we've configured this file's location (com/modelingagency/objects/SimpleMappers.xml) in the mybatis.config.xml file above, MyBatis now knows to load this resource whenever it is initialized.


The main functionality we're going to need now in the SimpleMapper.xml file is this:

  • 1) Getting a list of clients
  • 2) Getting a list of models
  • 3) Getting a list of all bookings

Create the following file: /src/com/modelingagency/objects/SimpleMappers.xml:


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- Created by: http://blog.idleworx.com, http://www.idleworx.com -->
<mapper namespace="com.modelingagency.objects.SimpleMappers">
 
 <resultMap id="clientResultMap" type="Client">
  <id property="id" column="client_id" />  
  <result property="name" column="client_name" />  
 </resultMap>
 
 <resultMap id="modelResultMap" type="Model">
  <id property="id" column="model_id" />  
  <result property="name" column="model_name" /> 
 </resultMap>
  
 <resultMap id="bookingResultMap" type="Booking">
  <id property="id" column="booking_id" />  
  <result property="time" column="booking_time" />    
  <result property="location" column="booking_location" />
  <association property="client" column="booking_client_id" javaType="Client" resultMap="clientResultMap"/>
  <association property="model" column="booking_model_id" javaType="Model" resultMap="modelResultMap"/>
 </resultMap> 
  
 <select id="allClients" parameterType="string" resultMap="clientResultMap">
  select  * from client order by 1  
 </select>
 
 <select id="allModels" parameterType="string" resultMap="modelResultMap">
  select  * from model order by 1
 </select>
 
 <select id="allBookings" parameterType="string" resultMap="bookingResultMap">
  select * from client, model, booking
  where booking_client_id = client_id and booking_model_id = model_id
  order by 1
 </select>
 
</mapper>

Notes

  • The name of each query is identified by the 'id' parameter
  • parameterType indicates what type of parameter you will pass into the sql statement (for simple queries there is only one parameter or none), for queries which require multiple parameters you can use the 'hashmap' parameterType to pass in name value pairs. In our case the parameter is not actually used.
  • By defining result maps for each of our objects, MyBatis knows how to automatically map a column from the database table directly to the javabean property name (in our case the Client,Model and Booking classes)
  • If you don't use Result Maps in your mapper files, you can use the resultType attribute instead which specifies the Java Bean class name we want to map, and which is defined in mybatis.config.xml. Normally you have to put a fully qualified class name here like com.modelingagency.objects.Model, but because if you defined the typeAliases element in the mybatis.config.xml you can just use the short name
  • The allBookings query makes use of MyBatis associations. Associations are what MyBatis uses to retrieve one-to-many relationships from your database into java objects. To learn more about this read: Understanding MyBatis' Multiple Table Joins

To make sure the MyBatis configuration and mapper files are all setup correctly, before we create our DAO layer and use it from our Servlet, we need to do a quick test.

Part 8: Testing MyBatis with jUnit

Tags: , , , ,