Gauss DB（DWS）实践系列 mybatis改造

        MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。

        如果没有MyBatis框架时，要使用已有的MyBatis来直接访问DWS数据库时，需要对MyBatis做一下改造，以下介绍几种常见的情况：

--mybatis改造样例

-----------------------------------------------------------------------------------
------------------------------mybatis语句---------------------------------------
----------------------------------------------------------------------------------

SELECT
	uuid,
	org_seq,
	auth_code,
	store_name,
	store_type,
	open_status,
	open_date,
	create_time
FROM
	store_info
	
<!--注释 where语句-->	
<where>
----------------------------------------------------------------
	<!--注释 if语句1-->
	<if test='open_date_value!=null'>open_date &gt;= #{open_date_value}</if>
	<!--注释 if语句2-->
	<if test='store_type==2'>AND create_time &lt;= #{open_date_value}</if>
----------------------------------------------------------------	
    <!--注释 choose语句-->
    <choose>
        <when test='wordValue!=null and wordKey==1'>mobile=#{wordValue}</when>
        <when test='wordValue!=null and wordKey==4'>tanzk_account=#{wordValue}</when>
        <when test='wordValue!=null and wordKey==2'>phone_last_four=#{wordValue}</when>
        <when test='wordValue!=null and wordKey==3'>wx_nick like ('%'||#{wordValue}||'%')</when>
        <otherwise>1=1</otherwise>
    </choose>	
	
</where>
----------------------------------------------------------------
<!--join语句-->
<choose>
    <when test='lvFlag==7'>LEFT JOIN (SELECT uid,nick as name FROM organization.t_employee where is_delete = 0) b ON a.dept_id::bigint = b.uid</when>
        <otherwise>LEFT JOIN (SELECT id,name FROM organization.t_department WHERE is_delete=0) b ON a.dept_id::bigint = b.id</otherwise>
</choose>
----------------------------------------------------------------
<!--排序字段-->
<choose>
    <when test='sortFlag==1'> order by create_time desc</when>
    <when test='sortFlag==2'> order by uuid asc</when>
    <when test='sortFlag==3'> order by open_date desc</when>
    <when test='sortFlag==4'> order by org_seq asc</when>
    <otherwise>order by auth_code desc</otherwise>
</choose>



---------------------------------------------------------------------------------
------------------------------改造后dws语句----------------------------------
---------------------------------------------------------------------------------

SELECT
	uuid,
	org_seq,
	auth_code,
	store_name,
	store_type,
	open_status,
	open_date,
	create_time
FROM
	store_info
----------------------------------------------------------------
WHERE
----------------------------------------------------------------
-----对应If判断,  &gt; 对应大于号 > ， &lt; 对应小于号 <    -------
	--注释 if语句1--
	case 
		when ${open_date_value} is not null 
			then open_date >= ${open_date_value} 
		else true 
	end
	--注释 if语句2--
	AND 
	case 
		when ${store_type}=2 
			then create_time <= ${open_date_value} 
		else true 
	end
--为防sql注入，以上可改为如下语句
	--注释 if语句1--
	case ${open_date_value}
		when '' 
			then true 
		when ${open_date_value}  
			then open_date >= ${open_date_value} 
		else true 
	end
	--注释 if语句2--
	AND 
	case ${store_type} 
		when 2 
			then create_time <= ${open_date_value} 
		else true 
	end	
----------------------------------------------------------------
    and
	--注释 choose语句--
    case ${wordValue}
	    when '' 
			then 1=1
		when ${wordValue} 
			then case ${wordKey}
					when 1 then mobile=${wordValue}
					when 4 then tanzk_account=${wordValue}
					when 2 then phone_last_four=${wordValue}
					when 3 then wx_nick like ('%'||${wordValue}||'%') 
					else true 
				 end 
        else 1=1
	end 

------------------------------------------------------------------
	
/* join语句（修改完后，别名也许做对应修改，原语句都为b，改后变为b、c；
例如 原：b.name，改后：case ${lvFlag} when 7 then b.name else c.name end）
 */
 
LEFT JOIN (SELECT uid,nick as name FROM organization.t_employee where is_delete = 0) b ON a.dept_id::bigint = b.uid and case ${lvFlag} when 7 then true else false end  
LEFT JOIN (SELECT id,name FROM organization.t_department WHERE is_delete=0) c ON a.dept_id::bigint = c.id and  case ${lvFlag} when 7 then  false else true end
  

------------------------------------------------------------------  


--Order By排序，需要把升序和降序分开---
order by
  case
    ${sortflag}
    when '2' Then uuid
    when '4' Then org_seq
  end asc,
  case
    ${sortflag}
    when '1' Then create_time
    when '3' Then open_date
    when '2' Then null
    when '4' Then null	
    else auth_code
  end desc

想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料~