Sunday, August 4, 2013

SQL Data Management with MyBatis

Managing Persistence of Data

The majority of applications work against some sort of data store and in most cases that data store is a database. For many years now it has been standard practice to separate the Data Management (that which manages  persistence of data) from other business logic in our solutions. 
We do this because we do not want to have Business Logic with hard dependencies on the structure of our database, or with dependencies on how we query data from the database. 

Separating Data Management results in a lower impact on our application if we should we decide to make changes in the database or even migrate to another database.
One way to help with abstracting Data Management is to use an Entity Framework. There are a number of frameworks commonly in use such as Microsoft EF, NHibernate/Hibernate or Toplink.  Entity Frameworks hide much of the nitty gritty SQL from the developer and allowing us to work in an object oriented fashion with persistence of data. They often provide an SQL like query language for reading data. They generate SQL under the hood, provide advanced caching of data, can provide independence from the database vendor. Common statements such as INSERT, UPDATE and DELETE operations are managed by the framework. However there are some drawbacks with Entity Frameworks since they can add another layer of complexity. We also hand over control of the SQL to the framework and performance can suffer if we are running  complex select statements.
Another consideration is how we are designing our data persistence. If we are designing our data persistence from our object model (or Code First) this makes working with an Entity Framework easier. However if we are designing from the database first (using a relational model) there can be difficulties, particularly if there are a large amount of relations in the model. This is a likely indicator of complex SELECT statements.
A good alternative to an Entity Framework is the MyBatis framework which provides a different method of abstracting Data Management and addresses some of the drawbacks of an Entity Framework.

What is MyBatis?

MyBatis is a framework which allows us to bind objects to SQL statements and execute these statements. In this framework the developer defines the SQL statements which are to be executed and how they are mapped to objects. This gives the developer more control over the SQL which results in more flexibility than an Entity Framework. However MyBatis does not provide SQL generation so we have to define all SQL statements. Since we define the SQL we also have control over how statements are executed and therefore we can execute complex SQL select statements fairly simply and map the results to any given object. With control over the SQL we have more control over the execution and performance.

MyBatis is available in both .NET and Java flavors.


How does MyBatis Work?

MyBatis works with an SqlSession object which manages the SQL session. This object provides the context in which we execute the SQL statements. The SqlSession is created from an XML file containing the session properties. This XML file contains mainly information regarding how to connect to the database,  and contains references to all the "mapper" XML files (these are the files containing the SQL queries).
An example session file is shown below:

<?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">
<configuration>
  <environments default="Development">
    <environment id="Development">
      <transactionManager type="JDBC" />
      <dataSource type="POOLED">
        <property name="driver" value="driver.MyDatabaseDriver" />
        <property name="url" value="jdbc:xxx://localhost;database=mydb;"/>
        <property name="username" value="myuser" />
        <property name="password" value="mypass" />
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="myapp/CustomerQueryMapper.xml" />
  </mappers>         
</configuration>


An effective way to implement this is to create your own "session factory" which manages the reading of the Session Properties XML and provides configured SqlSessions.






The SQL statements are place in "mapper" XML files where we define the type of SQL statement (insert, update, delete, select) and write the actual statement. When writing the SQL statements we can inject parameters into the statement mapped from our Java/.NET objects. Then to make things even simpler we can define an Interface containing methods which execute the queries providing the correct parameters and return values. The methods in the interface have the same names as the SQL Query identities.
So for example, if we have some queries on a database for Customer Information we can place the queries in a CustomerQueries.xml. In the XML file we can define queries which take parameters from a Customer object or even return result sets of Customer objects. We can also use other objects for parameter values or return values, there are no restrictions here.





The queries are structured via mapper namespaces and a reference to the mapper file must exist in the SqlSession. Below is a sample code from a CustomerQueries.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">
<mapper namespace="example.CustomerQueryMapper">
  <select id="findCustomerNames"  parameterType="example.Customer"     
          resultType="string">
                        
    SELECT CUSTOMER_NAME FROM CUSTOMER_TABLE
    WHERE
      LAND = #{land} AND
      SALES_CODE = #{salesCode}

 </select>
</mapper>

So we can see in this example that there is a single SQL query for finding customer names ("findCustomerNames"). We bind a parameter to this query in the form of a Customer object and indicate that the query will return a set of  "string" objects. We then inject the parameters to the query using the "#{}" notation. This will insert the "land" and "salesCode" properties from the Customer class into the above query. This provides a great deal of flexibility in mapping our queries to objects.
The example above shows only a mapping on a "select" statement, but there is also support for "insert", "update" and "delete". For full details regarding how to use MyBatis please refer to the MyBatis documentation (http://mybatis.github.io/mybatis-3/).


The Advantages and Disadvantages

Some of the advantages with MyBatis:
  • Flexibility - we can form the SQL exactly as we want and there is little restriction
  • Simple Configuration - the XML configuration is simple and easy to deploy
  • Complex Select Statement Performance - since select queries are executed directly there is no overhead for loading lots of entity objects before obtaining the result

Some of the disadvantages with MyBatis:
  • No SQL Generation - does not generate SQL like an entity framework so "inserts", "updates" and "deletes" have to be defined manually.
  • No database independence - the SQL is written by the developer specifically for the given database so migrating to another type of database is perhaps not as easy as with an Entity Framework
  • Less advanced caching - MyBatis perhaps lacks the advanced caching techniques that other Entity Frameworks possess.

Conclusion

MyBatis is a very useful framework which aids the persistence of data. The technology is mature and appears broadly used in industry, quoting references such as MySpace (https://code.google.com/p/mybatis/wiki/FeedbackTestimonial). However it still depends on what is required from the Data Management layer. When selecting a framework I would consider the following questions:
  • Do we have complex select statements, or table relationships?
  • Are our developers comfortable with coding SQL?
  • Is the data design driven from the relational database?

If the answer to these questions are "Yes", it would be well worth considering MyBatis as a viable framework for  Data Management. 


1 comment: