Jfinaluib Code Note (4)---high imitation ibatis (MyBatis) for centralized management of SQL

To realize the centralized management of SQL, simple to transfer some fixed-length SQL into XML is very simple, there is no much to say, the key problem is that we usually deal with SQL, there are a lot of dynamic length, for example, the most common is the multi-conditional paging query, often we will write a large number of if else in the code, It is more difficult to transplant these into XML, completely imitation ibatis to do XML tags too much work, the most convenient way is to be able to direct the Java code to the logical processing of the XML, and then parse the logic code, bypassing that a lot of XML tag definition, Here's how it's handled in Jfinaluib:

1.0 temporarily or with the splicing, no preprocessing, the difficulty is not to parse the SQL table field of the type corresponding to the Java data type

<?xml version= "1.0"  encoding= "UTF-8"? ><sql-statement namespace= "Pingtai.user" >  <!--  Dynamic SQL processing  --><sql id= "SplitPage" >  <! [cdata[          from pt_user u     left join pt_userinfo ui on u.userinfoids = ui.ids     left join pt_department d on u.departmentids = d.ids     where 1=1    <% if (!isempty (userclass)) { %>      and u.userClass =  ' #userClass # '    <% } %>       <% if (!isempty (userName)) { %>      and u.userName like  '% #userName #% '    <% } %>       <% if (!isempty (names)) { %>     and ui.names like  '% #names #% '    <% }  %>     ]]></sql></sql-statement>

The following is the method of parsing SQL in the call XML, parsing is done with the Beetl template, and of course you can change to freemark or velocity, and the syntax is the same as they do

    /**     *  get sql, dynamic sql     *   @param  sqlId     *  @param  param     *   @return      */    public static String  GetSQL (String sqlid, map<string, object> param)  {      String sqltemplete = sqlmap.get (Sqlid);      if (null ==  sqltemplete | |  sqltemplete.isempty ()) {   log.error ("SQL statement does not exist: Sql id is"  + sqlid);      }     string sql = beetlkit.render (SqlTemplete ,  param);     set<string> keyset = param.keyset ();   for   (String key : keyset)  {   String value =  (String)  param.get (key);      sql = sql.replace ("#"  + key +  "#",  value );   }          return sql.trim ();     }

Here's how it's called

map<string, object> param = new hashmap<string, object> ();  Param.put ("UserClass", "0");  Param.put ("UserName", "Huajian"); String sql = Toolsqlxml.getsql ("Pingtai.user.splitPage", param);

Output sql:

From Pt_user u
Left join Pt_userinfo UI on u.userinfoids = Ui.ids
Left join Pt_department D on u.departmentids = D.ids
where 1=1 and U.userclass = ' 0 ' and u.username like '%huajian% '

There is a problem with SQL injection: it can only be handled in an unfriendly way, the validation and filtering of strings, and the Stringvali method in Toolsqlxml.

2.0 the method under test, because it is difficult to get the data type of the dictionary in SQL, so it is difficult to do preprocessing, but the following method is almost verified to be feasible, can achieve semi- preprocessing method,

This method exists only insufficient for the processing of the shaping, directly with the stitching, and then string using preprocessing, do not worry about the existence of SQL injection, because the middle of the data value of shaping data type conversion test,

import java.util.hashmap;import java.util.linkedlist;import java.util.list;import  java.util.map;import java.util.regex.matcher;import java.util.regex.pattern;import  Org.beetl.core.beetlkit;public class sql { public static void main (String[]  args)  {    String sqlTemplete =  "from pt_user u  Left join pt_userinfo ui on u.userinfoids = ui.ids left join  pt_department d on u.departmentids = d.ids  " +  "   where 1=1  " +  " <% if (!isempty (userclass)) { %> "+  "    and u.userclass = # ' $userClass $ ' #  "+  " <% } %> "+      "<% if (!isempty (userName)) { %>" +   "  and u.username  like # '% $userName $% ' #  "+   "<% } %>" +     "<% if (!isempty (names)) { %>" +    "  and ui.names like # '% $names $% ' # " +   "<% } %>" +      "<% if (!isempty (status)) { %>" +   "  and  ui.status = # $status $#  "+  " <% } %> ";   map<string,  Object> paramMap = new HashMap<String, Object> ();   Parammap.put ("userclass",  "0"),   parammap.put ("UserName",  "AA");   parammap.put (" Names ", " BB ")   parammap.put (" status ", " 1 ");     string sql =  beetlkit.render (Sqltemplete, parammap);    pattern pattern =  Pattern.compile ("#[\\w\\d\\$\\ ' \\%\\_]+#");  //#[\\w\\d]+#    \\$  pattern  pattern2 = patteRn.compile ("\\$[\\w\\d\\_]+\\$");  //#[\\w\\d]+#    \\$    matcher  matcher = pattern.matcher (SQL);     list<string> listpram =  new LinkedList<> ();    while  (Matcher.find ())  {    string clounm = matcher.group (0);  // # '% $names $% ' #       matcher matcher2 = pattern2.matcher (CLOUNM);    matcher2.find ();    string clounm2 = matcher2.group (0); //  $names $       if (Clounm.equals ("#" +clounm2+ "#")) {//Shaping     string clounm3 =  clounm2.replace ("$",  "");     try {     string  val =  (String)  parammap.get (CLOUNM3);      integer.parseint (Val);      sql&Nbsp;= sql.replace (Clounm, val);      //listpram.add (val);     } catch  (numberformatexception e)  {     throw new  runtimeexception ("Query parameter Value exception");     }   }else{//string      string clounm3 = clounm2.replace ("$",  "");     string val  =  (String)  parammap.get (CLOUNM3);        string  Clounm4 = clounm.replace ("#",  ""). Replace ("'" ", "). Replace (Clounm2, val);     listpram.add (CLOUNM4);         sql = sql.replace ( clounm,  "?"); &NBSP;&NBSP;&NBSP;}&NBSP;&NBSP;}&NBSP;&NBSP;&NBSP;&NBSP;SYSTEM.OUT.PRINTLN ("Preprocessing sql:"  + sql);     for  (String param : listpram)  {   system.out.println ("Preprocessing sqL parameter: " + param);   } }} 

The result of the final output is:

preprocessing Sql:from pt_user u left join Pt_userinfo ui on u.userinfoids = Ui.ids left joins pt_department d on u.departmentids = d.   IDS where 1=1 and U.userclass =?   and u.username like?   and ui.names like? and ui.status = 1
Preprocessing SQL parameters: 0
Preprocessing SQL parameters:%aa%
Preprocessing SQL parameters:%bb%

The deficiency of this method is that the data of the shaping is not preprocessed, that is Ui.status = 1, but through the # $status $ #能确定他是整形, but do not know whether it is int, long, BigDecimal

UserClass is Char

Username is varchar.

Names is varchar.