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

mybatis generator 的复合查询

程序员文章站 2022-07-12 10:50:05
...

  mybatis generator可以自动生成sqlmap文件和查询语句,但是只能完成单表的查询,自己改了下,可以实现复合查询,感觉能比自己写查询语句能方便一些吧。
下面就讲讲怎么改的,和主要区别。
先说下表结构,主表stockclothestbl,副表有很多,那其中一个colortbl表举例。
正常通过generator可以自动生成StockclothestblExample.java,ColortblExample.java和StockclothestblMapper.xml,ColortblMapper.xml,4个文件。StockclothestblExample.java和ColortblExample.java文件用于查询。新建StockclothesExample.java。用于包含2个查询对象。
public class StockclothesExample {

    public StockclothestblExample stockclothestblExample;

    public ColortblExample colortblExample;

    public StockclothestblExample getStockclothestblExample() {
        return stockclothestblExample;
    }

    public void setStockclothestblExample(StockclothestblExample stockclothestblExample) {
        this.stockclothestblExample = stockclothestblExample;
    }

    public ColortblExample getColortblExample() {
        return colortblExample;
    }

    public void setColortblExample(ColortblExample colortblExample) {
        this.colortblExample = colortblExample;
    }
}

然后开始修改StockclothestblMapper.xml和ColortblMapper.xml文件

StockclothestblMapper.xml 文件里,添加sql,仿照自动生成的单表查询。
  <!-- 联合查询 -->
  <select id="selectStockclothes" resultMap="selectStockclothesResult"  parameterType="cn.com.shopdepot.javaModel.custom.StockclothesExample">
    select
    <if test="stockclothestblExample.distinct" >
      distinct
    </if>
    <include refid="cn.com.shopdepot.javaClient.StockclothestblMapper.Base_Column_List" />
    ,
    <include refid="cn.com.shopdepot.javaClient.RegisterdepottblMapper.Base_Column_List" />
    ,
    <include refid="cn.com.shopdepot.javaClient.StyletblMapper.Base_Column_List" />
    ,
    <include refid="cn.com.shopdepot.javaClient.KindtblMapper.Base_Column_List" />
    ,
    <include refid="cn.com.shopdepot.javaClient.ClotheskindtblMapper.Base_Column_List" />
    ,
    <include refid="cn.com.shopdepot.javaClient.ColortblMapper.Base_Column_List" />
    ,
    <include refid="cn.com.shopdepot.javaClient.SizetblMapper.Base_Column_List"/>
    from stockclothestbl stockclothestbl
    left join registerdepottbl registerdepottbl on registerdepottbl.REGISTERDEPOT_SERNO=stockclothestbl.REGISTERDEPOT_SERNO
    left join styletbl styletbl on styletbl.STYLE=stockclothestbl.STYLE
    left join kindtbl kindtbl on kindtbl.KIND=stockclothestbl.KIND
    left join clotheskindtbl clotheskindtbl on clotheskindtbl.CLOTHESKIND_CODE=stockclothestbl.CLOTHESKIND_CODE
    left join colortbl colortbl on colortbl.COLOR_SERNO=stockclothestbl.COLOR_SERNO
    left join sizetbl sizetbl on sizetbl.SIZE_CODE=stockclothestbl.SIZE_CODE
    and styletbl.DEL_FLAG='0'
    and kindtbl.DEL_FLAG='0'
    and clotheskindtbl.DEL_FLAG='0'
    and colortbl.DEL_FLAG='0'
    and sizetbl.DEL_FLAG='0'
    <if test="_parameter != null" >
        <where>
            <trim prefix="(" suffix=")" prefixOverrides="and" >
                <include refid="cn.com.shopdepot.javaClient.StockclothestblMapper.Example_Where_Clause_Complex" />
                <include refid="cn.com.shopdepot.javaClient.ColortblMapper.Example_Where_Clause_Complex"/>
            </trim>
          </where>

    </if>
    <if test="stockclothestblExample.orderByClause != null" >
      order by ${stockclothestblExample.orderByClause}
    </if>
    <include refid="cn.com.shopdepot.javaClient.StockclothestblMapper.mysqlDialect_Complex" />
  </select>

解释下这个sql
id="selectStockclothes",是以后java端调用的方法名。
resultMap="selectStockclothesResult"  这个是返回结果,引用selectStockclothesResult,下面是selectStockclothesResult

  <resultMap type="cn.com.shopdepot.javaModel.custom.Stockclothes" id="selectStockclothesResult">
      <association property="stockclothestbl" resultMap="cn.com.shopdepot.javaClient.StockclothestblMapper.BaseResultMap"></association>
      <association property="registerdepottbl" resultMap="cn.com.shopdepot.javaClient.RegisterdepottblMapper.BaseResultMap"></association>
      <association property="styletbl" resultMap="cn.com.shopdepot.javaClient.StyletblMapper.BaseResultMap"></association>
      <association property="kindtbl" resultMap="cn.com.shopdepot.javaClient.KindtblMapper.BaseResultMap"></association>
      <association property="clotheskindtbl" resultMap="cn.com.shopdepot.javaClient.ClotheskindtblMapper.BaseResultMap"></association>
      <association property="colortbl" resultMap="cn.com.shopdepot.javaClient.ColortblMapper.BaseResultMap"></association>
      <association property="sizetbl" resultMap="cn.com.shopdepot.javaClient.SizetblMapper.BaseResultMap"></association>
  </resultMap>
其中   <association property="colortbl" resultMap="cn.com.shopdepot.javaClient.ColortblMapper.BaseResultMap"></association>
就是ColortblMapper.xml文件中最上边的命名空间+ColortblMapper.xml文件中BaseResultMap的id。

继续上边,parameterType="cn.com.shopdepot.javaModel.custom.StockclothesExample" 参数就是我们自己添加的StockclothesExample.java文件的全路径。
<if test="stockclothestblExample.distinct" >,这个地方要修改,原来自动生成的是<if test="distinct" >修改成
 <if test="stockclothestblExample.distinct" >。这样才能反射找到是stockclothestblExample对象里的distinct。
stockclothestblExample---是我们自己添加StockclothesExample类里的成员

 <include refid="cn.com.shopdepot.javaClient.ColortblMapper.Base_Column_List" />,这个同理resultMap的,查询sql的字段,colortbl表的字段。
下面修改的相对比较多
    <if test="_parameter != null" >
        <where>
            <trim prefix="(" suffix=")" prefixOverrides="and" >
                <include refid="cn.com.shopdepot.javaClient.StockclothestblMapper.Example_Where_Clause_Complex" />
                <include refid="cn.com.shopdepot.javaClient.ColortblMapper.Example_Where_Clause_Complex"/>
            </trim>
          </where>
    </if>
自动生成的单表查询的以前的下面
    <if test="_parameter != null" >
      <include refid="Example_Where_Clause" />
    </if>

添加了colortbl的查询 <include refid="cn.com.shopdepot.javaClient.ColortblMapper.Example_Where_Clause_Complex"/>

以前的refid="Example_Where_Clause"------变成Example_Where_Clause_Complex,Example_Where_Clause是自动生成的,
Example_Where_Clause_Complex是我自己添加的,仿照Example_Where_Clause。下面是2个表的Example_Where_Clause_Complex。
分别写在2个自己的xml中。
StockclothestblMapper.xml添加的Example_Where_Clause_Complex
  <sql id="Example_Where_Clause_Complex" >
      <foreach collection="stockclothestblExample.oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
        </if>
      </foreach>
  </sql>

StockclothestblMapper.xml自动生成的Example_Where_Clause
  <sql id="Example_Where_Clause" >
    <where >
      <foreach collection="oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
比较看下,红色的去修改的,去掉where和trim,因为2个要在最外层加,上边画的蓝色部分。
collection="oredCriteria"变成collection="stockclothestblExample.oredCriteria",因为你的指定到你的类的具体对象。

同上
ColortblMapper.xml 添加的Example_Where_Clause_Complex
  <sql id="Example_Where_Clause_Complex" >
     <foreach collection="colortblExample.oredCriteria" item="criteria" separator="or" >
       <if test="criteria.valid" >
           <foreach collection="criteria.criteria" item="criterion" >
             <choose >
               <when test="criterion.noValue" >
                 and ${criterion.condition}
               </when>
               <when test="criterion.singleValue" >
                 and ${criterion.condition} #{criterion.value}
               </when>
               <when test="criterion.betweenValue" >
                 and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
               </when>
               <when test="criterion.listValue" >
                 and ${criterion.condition}
                 <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                   #{listItem}
                 </foreach>
               </when>
             </choose>
           </foreach>
       </if>
     </foreach>
  </sql>
ColortblMapper.xml自动生成的Example_Where_Clause
  <sql id="Example_Where_Clause" >
    <where >
      <foreach collection="oredCriteria" item="criteria" separator="or" >
        <if test="criteria.valid" >
          <trim prefix="(" suffix=")" prefixOverrides="and" >
            <foreach collection="criteria.criteria" item="criterion" >
              <choose >
                <when test="criterion.noValue" >
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue" >
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue" >
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue" >
                  and ${criterion.condition}
                  <foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>


继续上边的解释
    <if test="stockclothestblExample.orderByClause != null" >
      order by ${stockclothestblExample.orderByClause}
    </if>
也是指定到具体对象的排序,绿色部分。

sqlmap基本就改完了,剩下没解释的,是分页limit,就不解释了,贴一下代码
  <sql id="mysqlDialect_Complex" >
    <if test="stockclothestblExample.limitStart > -1">
      limit ${stockclothestblExample.limitStart}, ${stockclothestblExample.limitEnd}
    </if> 
  </sql>

以上基本就完成了。
然后在自动生成的StockclothestblMapper.java,添加方法
    /**
     * sqlmap id=selectStockclothes
     * 
     * @author wang
     * @param example
     * @return
     */
    List<Stockclothes> selectStockclothes(StockclothesExample example);

这样就可以使用了。大概写下

        StockclothesExample example = new StockclothesExample();

        StockclothestblExample stockclothestblExample = new StockclothestblExample();
        StockclothestblExample.Criteria stockclothestbl_criteria = stockclothestblExample.createCriteria();

        ColortblExample colortblExample = new ColortblExample();
        ColortblExample.Criteria colortbl_criteria = colortblExample.createCriteria();
        if (null != selBrand && !"".equals(selBrand)) {
            stockclothestbl_criteria.andBrandLike("%" + selBrand + "%");
        }
        if (null != selColor && !"".equals(selColor)) {
            colortbl_criteria.andColorLike("%" + selColor + "%");
        }
        example.setStockclothestblExample(stockclothestblExample);
        example.setColortblExample(colortblExample);
        .....   中间省略.......
      List <Stockclothes>list=stockclothestblMapper.selectStockclothes(example)

Stockclothes类封装的genertor自动生成的tbl

结束!