你不得不知道的MyBatis基础知识 MyBatis的CURD之多表（3）

---

  这里我们以之前山科超市项目中的商品类和商品类别类为例进行MyBatis的多表操作。

商品类和商品类别类的封装

package com.lazy.domain;

public class Products {
    private int pid;
    private String pcode;
    private String pname;
    private PType ptid; // 你依赖谁 就让谁成为你的属性
    private float pprice;
    private String addtime;


    public Products(){}

    public Products(int pid, float pprice) {
        this.pid = pid;
        this.pprice = pprice;
    }

    public Products(int pid, String pname, float pprice) {
        this.pid = pid;
        this.pname = pname;
        this.pprice = pprice;
    }

    public Products(int pid, String pcode, String pname, PType ptid, float pprice, String addtime) {
        this.pid = pid;
        this.pcode = pcode;
        this.pname = pname;
        this.ptid = ptid;
        this.pprice = pprice;
        this.addtime = addtime;
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPcode() {
        return pcode;
    }

    public void setPcode(String pcode) {
        this.pcode = pcode;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public PType getPtid() {
        return ptid;
    }

    public void setPtid(PType ptid) {
        this.ptid = ptid;
    }

    public float getPprice() {
        return pprice;
    }

    public void setPprice(float pprice) {
        this.pprice = pprice;
    }

    public String getAddtime() {
        return addtime;
    }

    public void setAddtime(String addtime) {
        this.addtime = addtime;
    }
}

package com.lazy.domain;

public class PType {
    private int ptid;
    private String ptname;

    public  PType(){}

    public PType(int ptid, String ptname) {
        this.ptid = ptid;
        this.ptname = ptname;
    }

    public int getPtid() {
        return ptid;
    }

    public void setPtid(int ptid) {
        this.ptid = ptid;
    }

    public String getPtname() {
        return ptname;
    }

    public void setPtname(String ptname) {
        this.ptname = ptname;
    }
}

ProductsMapper.java

package com.lazy.mappers;

import com.lazy.domain.Products;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface ProductsMapper {
    List<Products> findByPName(@Param("parPName") String parPName); // 使用@param可以解决mybatis对string的限制
}

ProductsMapper.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="com.lazy.mappers.ProductsMapper">
    <resultMap id="newProducts" type="com.lazy.domain.Products">
        <id property="pid" column="pid"/>
        <result property="pname" column="pname"/>
        <result property="pprice" column="pprice"/>
        <!--嵌套对象标签-->
        <association property="ptid" javaType="com.lazy.domain.PType">
            <id property="ptid" column="ptid"/>
            <result property="ptname" column="ptname"/>
        </association>
    </resultMap>

    <select id="findByPName" resultMap="newProducts">
        SELECT p.pid,p.pcode,p.pname,pt.ptid,pt.ptname,p.pprice,p.addtime
        FROM products p inner join ptype pt on p.ptid = pt.ptid
        <if test="parPName != null and parPName != ''"> /*判空判断*/
            where pname like CONCAT('%',#{parPName},'%') and p.pprice <![CDATA[>]]> 13
        </if>
    </select>

</mapper>

App.java 测试

package com.lazy;

import com.lazy.domain.Products;
import com.lazy.mappers.ProductsMapper;
import com.lazy.mappers.UsersMapper;
import com.lazy.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;


/**
 * Hello world!
 *
 */
public class App
{
    public static void main( String[] args )
    {
    
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);

        ProductsMapper mapper = sqlSession.getMapper(ProductsMapper.class);
        List<Products> data = mapper.findByPName("");
        for (Products p: data) {
            System.out.println(p.getPname());
            System.out.println(p.getPtid().getPtname());
        }
        MybatisUtil.closeSqlSession();
    }
}

测试结果