Simple MyBatis without POJOS
I have a project that I needed to convert from JDBC to MyBatis. I did not want to change the way the program worked so I needed a way to implement MyBatis with a minimum of fuss.
The existing program creates an ArrayList filled with rows of HashMaps. I wanted to produce the same thing. The existing system deals with 200 tables and 150 main programs. It was converted to Java from mainframe COBOL and NATURAL. I did not want hundreds of POJOs around so I did not want to write classes to pass parameters or create the ArrayList.
It turns out that MyBatis is meant to work this way.
Here is my code:
The MyBatis config xml looks like this:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql:// localhost:3306/pilot" /> <property name="username" value="root" /> <property name="password" value="" /> </dataSource> </environment> <!-- change the production environment --> <environment id="production"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/pilot" /> <property name="username" value="root" /> <property name="password" value="" /> </dataSource> </environment> </environments> <mappers> <!—I put all the sql for a program in one xml file to help manage them <mapper resource="com/kpg/data/program1.xml" /> <mapper resource="com/kpg/data/program2.xml" /> <mapper resource="com/kpg/data/program3.xml" /> <mapper resource="com/kpg/data/program4.xml" /> <mapper resource="com/kpg/data/program5.xml" /> </mappers> </configuration>
The config has the JDBC connection, which will change quite a bit when I put the production connection in place. I will be using DB2 and the client supplied connection classes for that.
The config has the mappers. I am creating a mapper xml file for each program. This is so I can manage each program and the program’s SQL without being cluttered with SQL from other files. I started out having all the SQL for one table in a file, but then I had joins that confused the issue. I also became difficult to find which SQL belonged to which program.
A program’s Mapper xml looks like this:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="program1"> <resultMap id="result" type="HashMap"> </resultMap> <select id="query1" parameterType="map" resultMap="result"> SELECT * FROM TABLE1 WHERE KEY1= #{arg0} AND KEY2 = #{arg1} AND KEY_CODE = 'Q'; </select> <select id="query2" parameterType="map" resultMap="result"> SELECT COUNT(*) as RECORD_COUNT FROM TABLE2 WHERE KEY1 = #{arg0}; </select> </mapper>
The resultmap id is result and the type is HashMap, which means that a row will return in a hashmap.
The selects have a parameterType=”map” which means that I will be sending the query a Map with the variables in it. The variables are named arg0, arg1, arg2, etc. I don’t give them meaningful names because I want to be able to call the query using an array of variables.
The resultMap=”result” refers to the resultMap above that I typed as HashMap.
Now I don’t need a POJO, just one java class that I called ExecuteSql:
package com.kpg.model; import com.kpg.database.MyBatisConnectionFactory; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; public class ExecuteQuery { public ArrayList<HashMap<String,Object>> query(String program,int queryNo,Object... args) { String q=program+".query"+queryNo; SqlSession session=MyBatisConnectionFactory.getSqlSessionFactory().openSession(); HashMap<String,Object> m=array2map(args); try { List<Object> list= session.selectList(q,m); ArrayList<HashMap<String,Object>> rows=new ArrayList(list); return rows; } finally { session.close(); } } private HashMap<String,Object> array2map(Object[] args) { HashMap<String,Object> m=new HashMap<String,Object>(); int i=0; for (Object val:args) { m.put("arg"+i,val); i++; } return m; } }
I have a method called query that returns the ArrayList of HashMaps that the old JDBC programs returned. I need to pass it the name of the program and the query number and some arguments.
The program and the query number are concatenated so that it becomes:
Program1.query1
This corresponds to the Program1 mapper file and the .query1 refers to the query in that file.
There is a clunky bit of code that converts the array of Objects (Object… args) to a HashMap with the key built as arg0, arg1, arg2, etc. that the SQL in the mapper refers to. MyBatis will pull these out of the HashMap.
The line with session.selectList runs the sql and puts the results back in a List that I use to create the ArrayList that is returned.
I made a connection factory with cut and paste from some example I found on the intenet.
package comkpg.database; import java.io.FileNotFoundException; import java.io.IOException; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MyBatisConnectionFactory { private static SqlSessionFactory sqlSessionFactory; static { try { String resource = "SqlMapConfig.xml"; Reader reader = Resources.getResourceAsReader(resource); if (sqlSessionFactory == null) { sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader,"development"); //sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader,"production"); } } catch (FileNotFoundException fileNotFoundException) { fileNotFoundException.printStackTrace(); } catch (IOException iOException) { iOException.printStackTrace(); } } public static SqlSessionFactory getSqlSessionFactory() { return sqlSessionFactory; } }
It is quite easy.
To make it work in a program, I need two lines:
ExecuteQuery tt= new ExecuteQuery(); ArrayList<HashMap<String,Object>> rows=tt.query("Program1",1,"111111111","222222222");
The ArrayList Rows will have the results. In practice I wrap the ArrayList with an Abstract Query manager that provides all kinds of functionality to get and set fields, move through the array and check the results.
This is a nice Write-Once solution where you just need to add new SQL statements to the xml files in order to extend. It does not use any strange methods or complicated POJO classes. The client required MyBatis, but I think they expected a huge complicated set of classes to handle the queries. I wrote just one class and some easily managed xml files to do everything. The updates, inserts and deletes were just minor extensions of the code.