Simple MyBatis without POJOS
\nI 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.
\nThe 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.
\nIt turns out that MyBatis is meant to work this way.
\nHere is my code:
\nThe MyBatis config xml looks like this:<\/p>\n
\n\r\n<?xml version=\"1.0\" encoding=\"UTF-8\"?> <!DOCTYPE configuration\r\nPUBLIC \"-\/\/mybatis.org\/\/DTD Config 3.0\/\/EN\"\r\n\"http:\/\/mybatis.org\/dtd\/mybatis-3-config.dtd\">\r\n<configuration>\r\n<environments default=\"development\">\r\n<environment id=\"development\">\r\n<transactionManager type=\"JDBC\" \/>\r\n<dataSource type=\"POOLED\">\r\n<property name=\"driver\" value=\"com.mysql.jdbc.Driver\" \/>\r\n<property name=\"url\" value=\"jdbc:mysql:\/\/ localhost:3306\/pilot\" \/>\r\n<property name=\"username\" value=\"root\" \/>\r\n<property name=\"password\" value=\"\" \/>\r\n<\/dataSource>\r\n<\/environment>\r\n<!--\u00a0 change the production environment -->\r\n<environment id=\"production\">\r\n<transactionManager type=\"JDBC\" \/>\r\n<dataSource type=\"POOLED\">\r\n<property name=\"driver\" value=\"com.mysql.jdbc.Driver\" \/>\r\n<property name=\"url\" value=\"jdbc:mysql:\/\/localhost:3306\/pilot\" \/>\r\n<property name=\"username\" value=\"root\" \/>\r\n<property name=\"password\" value=\"\" \/>\r\n<\/dataSource>\r\n<\/environment>\r\n<\/environments>\r\n<mappers>\r\n<!\u2014I put all the sql for a program in one xml file to help manage them \uf0e0\r\n<mapper resource=\"com\/kpg\/data\/program1.xml\" \/>\r\n<mapper resource=\"com\/kpg\/data\/program2.xml\" \/>\r\n<mapper resource=\"com\/kpg\/data\/program3.xml\" \/>\r\n<mapper resource=\"com\/kpg\/data\/program4.xml\" \/>\r\n<mapper resource=\"com\/kpg\/data\/program5.xml\" \/>\r\n<\/mappers>\r\n<\/configuration><\/code><\/pre>\n<\/blockquote>\nThe 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.<\/p>\n
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\u2019s 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.<\/p>\n
A program\u2019s Mapper xml looks like this:<\/p>\n
\n\r\n<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<!DOCTYPE mapper\r\nPUBLIC \"-\/\/mybatis.org\/\/DTD Mapper 3.0\/\/EN\"\r\n\"http:\/\/mybatis.org\/dtd\/mybatis-3-mapper.dtd\">\r\n<mapper namespace=\"program1\">\r\n<resultMap id=\"result\" type=\"HashMap\">\r\n<\/resultMap>\r\n<select id=\"query1\" parameterType=\"map\" resultMap=\"result\">\r\nSELECT * FROM TABLE1 WHERE KEY1= #{arg0} AND KEY2 = #{arg1} AND\r\nKEY_CODE = 'Q';\r\n<\/select>\r\n<select id=\"query2\" parameterType=\"map\" resultMap=\"result\">\r\nSELECT COUNT(*) as RECORD_COUNT FROM TABLE2 WHERE KEY1 = #{arg0};\r\n<\/select>\r\n<\/mapper><\/code><\/pre>\n<\/blockquote>\nThe resultmap id is result and the type is HashMap, which means that a row will return in a hashmap.<\/p>\n
The selects have a parameterType=\u201dmap\u201d 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\u2019t give them meaningful names because I want to be able to call the query using an array of variables.<\/p>\n
The resultMap=\u201dresult\u201d refers to the resultMap above that I typed as HashMap.<\/p>\n
Now I don\u2019t need a POJO, just one java class that I called ExecuteSql:<\/p>\n
\n\r\npackage com.kpg.model;\r\nimport com.kpg.database.MyBatisConnectionFactory;\r\nimport java.util.ArrayList;\r\nimport java.util.HashMap;\r\nimport java.util.Iterator;\r\nimport java.util.List;\r\nimport org.apache.ibatis.session.SqlSession;\r\nimport org.apache.ibatis.session.SqlSessionFactory;\r\n\r\npublic class ExecuteQuery {\r\npublic ArrayList<HashMap<String,Object>> query(String program,int queryNo,Object... args) {\r\nString q=program+\".query\"+queryNo;\r\nSqlSession session=MyBatisConnectionFactory.getSqlSessionFactory().openSession();\r\nHashMap<String,Object> m=array2map(args);\r\ntry {\r\nList<Object> list= session.selectList(q,m);\r\nArrayList<HashMap<String,Object>> rows=new ArrayList(list);\r\nreturn rows;\r\n} finally {\r\nsession.close();\r\n}\r\n}\r\n\r\nprivate HashMap<String,Object> array2map(Object[] args) {\r\nHashMap<String,Object> m=new HashMap<String,Object>();\r\nint i=0;\r\nfor (Object val:args) {\r\nm.put(\"arg\"+i,val);\r\ni++;\r\n}\r\nreturn m;\r\n}\r\n}<\/code><\/pre>\n<\/blockquote>\nI 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.<\/p>\n
The program and the query number are concatenated so that it becomes:<\/p>\n
Program1.query1<\/p>\n
This corresponds to the Program1 mapper file and the .query1 refers to the query in that file.<\/p>\n
There is a clunky bit of code that converts the array of Objects (Object\u2026 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.<\/p>\n
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.<\/p>\n
I made a connection factory with cut and paste from some example I found on the intenet.<\/p>\n
\npackage comkpg.database;\r\nimport java.io.FileNotFoundException;\r\nimport java.io.IOException;\r\nimport java.io.Reader;\r\nimport org.apache.ibatis.io.Resources;\r\nimport org.apache.ibatis.session.SqlSessionFactory;\r\nimport org.apache.ibatis.session.SqlSessionFactoryBuilder;\r\npublic class MyBatisConnectionFactory {\r\nprivate static SqlSessionFactory sqlSessionFactory;\r\nstatic {\r\ntry {\r\nString resource = \"SqlMapConfig.xml\";\r\nReader reader = Resources.getResourceAsReader(resource);\r\nif (sqlSessionFactory == null) {\r\nsqlSessionFactory = new SqlSessionFactoryBuilder().build(reader,\"development\");\r\n\/\/sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader,\"production\");\r\n}\r\n}\r\ncatch (FileNotFoundException fileNotFoundException) {\r\nfileNotFoundException.printStackTrace();\r\n}\r\ncatch (IOException iOException) {\r\niOException.printStackTrace();\r\n}\r\n}\r\npublic static SqlSessionFactory getSqlSessionFactory() {\r\nreturn sqlSessionFactory;\r\n}\r\n}<\/code><\/pre>\n<\/blockquote>\nIt is quite easy.<\/p>\n
To make it work in a program, I need two lines:<\/p>\n
\n\r\nExecuteQuery tt= new ExecuteQuery();\r\nArrayList<HashMap<String,Object>> rows=tt.query(\"Program1\",1,\"111111111\",\"222222222\");\r\n<\/code><\/pre>\n<\/blockquote>\nThe 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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/posts\/920"}],"collection":[{"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/comments?post=920"}],"version-history":[{"count":1,"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/posts\/920\/revisions"}],"predecessor-version":[{"id":921,"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/posts\/920\/revisions\/921"}],"wp:attachment":[{"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/media?parent=920"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/categories?post=920"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blogseye\/wp-json\/wp\/v2\/tags?post=920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}