欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mybatis中 SQL语句复用

程序员文章站 2024-03-05 12:50:24
mapper.xml *用 mapper.xml 间共用 项目中也许我们会遇到一段sql语句被多个查询、增加等语句用到的情况,如何去偷懒呢,复用sql无疑是较好的选择...

mapper.xml *用

mapper.xml 间共用

项目中也许我们会遇到一段sql语句被多个查询、增加等语句用到的情况,如何去偷懒呢,复用sql无疑是较好的选择

这里只提供简单的示范:

如果只是单表查询,并且希望共用的sql只会出现在同一个mapper.xml文件中,那么我们可以直接在

<mapper namespace="xxxxx"></mapper>

中写下面的业务代码

<sql id="unitsql">
    a.userid,a.legalcode,legalname,
    biddercodetype,legalrole,
    licenseno,date_format(licenseenddate,"%y-%m-%d") as licenseenddate,
    taxcertno,date_format(taxcertenddate,"%y-%m-%d") as taxcertenddate,
    localtaxcertno,date_format(localtaxcertenddate,"%y-%m-%d") as localtaxcertenddate,
    organno,date_format(organcertenddate,"%y-%m-%d") as organcertenddate,
    legalrepresent,legaltype,
    perresentphone,legalindustary,creditrate,
    countryregion,legalunitaddress,regioncode,registerprovince,
    a.regcapital,a.regcapcurrency,a.regunit,
    registercity,registercounty,basicbank,basicbranchbank,
    basicaccountno,basicaccountname,legalcontact,legalcontactphone,
    legalcontactaddress,legalweb,legalzipcode,legalemail,
    legalstatus
</sql>

然后引用

<select id="gettenderagentbyid" parametertype="string" resulttype="map">
    select <include refid="unitsql"/>, -- 就是这句话引用 ,refid为共有sql语句id
    b.auditopinion,b.createuser,
    b.createtime,
    b.lastupdateuser,
    b.lastupdatetime,
    b.disabled,b.infostatus
    from p_legalunit a, p_tenderagent b
    where b.userid=#{userid} and a.userid = b.userid and b.disabled='0'
</select>

如果是连表查询,那么我们极有可能会希望有一个sql语句被多个mapper.xml引用的的方法,很简单。
我们可以新建一个mapper.xml文件(当然,这个mapper.xml要在你的扫描路径下,本文不赘述)。取名为common.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="common">
  <sql id="common.unitsql">
    a.userid,a.legalcode,legalname,
    biddercodetype,legalrole,
    licenseno,date_format(licenseenddate,"%y-%m-%d") as licenseenddate,
    taxcertno,date_format(taxcertenddate,"%y-%m-%d") as taxcertenddate,
    localtaxcertno,date_format(localtaxcertenddate,"%y-%m-%d") as localtaxcertenddate,
    organno,date_format(organcertenddate,"%y-%m-%d") as organcertenddate,
    legalrepresent,legaltype,
    perresentphone,legalindustary,creditrate,
    countryregion,legalunitaddress,regioncode,registerprovince,
    a.regcapital,a.regcapcurrency,a.regunit,
    registercity,registercounty,basicbank,basicbranchbank,
    basicaccountno,basicaccountname,legalcontact,legalcontactphone,
    legalcontactaddress,legalweb,legalzipcode,legalemail,
    legalstatus
  </sql>
</mappper>

在另一个mapper.xml文件引用

<select id="gettenderagentbyid" parametertype="string" resulttype="map">
    select <include refid="common.unitsql"/>, -- 就是这句话引用 ,refid为共有sql语句id
    b.auditopinion,b.createuser,
    b.createtime,
    b.lastupdateuser,
    b.lastupdatetime,
    b.disabled,b.infostatus
    from p_legalunit a, p_tenderagent b
    where b.userid=#{userid} and a.userid = b.userid and b.disabled='0'
</select>

==注:为何我的文件名要取为common.xml并且id也为common.unitsql呢?这是为了后期维护方便,如此,可以更容易找到该共有的sql在哪一个文件==

以上所述是小编给大家介绍的mybatis中 sql语句复用,希望对大家有所帮助