【MyBatis】MyBatis带参数和使用对象参数查询

介绍

这里我们直接用之前MyBatis项目的goods实体类直接作为演示，源代码中直接再test.java调试各个方法就行。

案例代码下载：本地下载

案例

一.只有一个参数查询

1.增加接口

GoodsMapper.java

public Goods findById(int id);

2.对应的xml文件添加内容

GoodsMapper.xml

<select id="findById" parameterType="int" resultMap="resultGoods">
select * from goods
where goods_id = #{id}
</select>

3.测试方法

test.java

public void test04() {
    try {
        GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
        Goods goods = goodsMapper.findById(2);
        System.out.println(goods);
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        this.sqlSession.close();
    }
}

二.多参数查询

1.增加接口

GoodsMapper.java

public List<Goods> findByPrice(@Param("minPrice") double minPrice, @Param("maxPrice") double maxPrice);

2.对应的xml文件添加内容

GoodsMapper.xml

<select id="findByPrice" resultMap="resultGoods">
select * from goods
where goods_price between #{minPrice} and #{maxPrice}
</select>

3.测试方法

test.java

public void test05(){
    try {
        GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
        List<Goods> goodsList = goodsMapper.findByPrice(0, 4);
        for (Goods goods : goodsList) {
            System.out.println(goods);
        }
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        this.sqlSession.close();
    }
}

三.使用对象作为参数写入数据

1.增加接口

GoodsMapper.java

public void save(Goods goods);

2.对应的xml文件添加内容

GoodsMapper.xml

<insert id="save" parameterType="com.bean.Goods">
insert into goods(goods_id,goods_name,goods_price,goods_num,goods_type)
value(#{goodsId},#{goodsName},#{goodsPrice},#{goodsNum},#{goodsType})
</insert>

3.测试方法

test.java

public void test06(){
    try{
        GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
        Goods goods = new Goods();
        goods.setGoodsName("篮球");
        goods.setGoodsPrice(80.0);
        goods.setGoodsNum(5);
        goods.setGoodsType(1);
        goodsMapper.save(goods);
        this.sqlSession.commit(); //提交事务
        System.out.println("添加数据成功！");
    } catch(Exception e) {
        e.printStackTrace();
        this.sqlSession.rollback();
        throw new RuntimeException(e);
    } finally {
        this.sqlSession.close();
    }
}

四.使用对象作为多条件查询

1.创建对象类

GoodsSearchVO.java

package com.vo;

public class GoodsSearchVO {

    //封装实体类进行查询。
    private String goodsName;
    private double minPrice;
    private double maxPrice;

    public GoodsSearchVO() {
    }

    public GoodsSearchVO(String goodsName, double minPrice, double maxPrice) {
        this.goodsName = goodsName;
        this.minPrice = minPrice;
        this.maxPrice = maxPrice;
    }

    public String getGoodsName() {
        return goodsName;
    }

    public void setGoodsName(String goodsName) {
        this.goodsName = goodsName;
    }

    public double getMinPrice() {
        return minPrice;
    }

    public void setMinPrice(double minPrice) {
        this.minPrice = minPrice;
    }

    public double getMaxPrice() {
        return maxPrice;
    }

    public void setMaxPrice(double maxPrice) {
        this.maxPrice = maxPrice;
    }

    @Override
    public String toString() {
        return "GoodsSearchVO [goodsName=" + goodsName + ", minPrice="
                + minPrice + ", maxPrice=" + maxPrice + "]";
    }

}

2.增加接口

GoodsMapper.java

public List<Goods> findBySearchVO(GoodsSearchVO goodsSearchVO);

3.对应的xml文件添加内容

GoodsMapper.xml

<select id="findBySearchVO" parameterType="com.vo.GoodsSearchVO" resultMap="resultGoods">
select * from goods
where goods_name like concat(‘%’, concat(#{goodsName}, ‘%’))
and goods_price between #{minPrice} and #{maxPrice}
</select>

4.测试方法

test.java

public void test07(){
    try{
        GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
        GoodsSearchVO goodsSearchVO = new GoodsSearchVO("球",10,100);
//goodsSearchVO.setGoodsName("球");
//goodsSearchVO.setMaxPrice(100);
//goodsSearchVO.setMinPrice(10);
        List<Goods> goodsList = goodsMapper.findBySearchVO(goodsSearchVO);
        for (Goods goods : goodsList) {
            System.out.println(goods);
        }
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        this.sqlSession.close();
    }
}

五.使用对象封装聚合查询的结果

1.创建对象类

GoodsGroupVO.java

package com.vo;

public class GoodsGroupVO {
    //查询结果封装的实体类
    private double minPrice;
    private double maxPrice;

    public GoodsGroupVO() {
    }

    public GoodsGroupVO(double minPrice, double maxPrice) {
        this.minPrice = minPrice;
        this.maxPrice = maxPrice;
    }

    public double getMinPrice() {
        return minPrice;
    }

    public void setMinPrice(double minPrice) {
        this.minPrice = minPrice;
    }

    public double getMaxPrice() {
        return maxPrice;
    }

    public void setMaxPrice(double maxPrice) {
        this.maxPrice = maxPrice;
    }

    @Override
    public String toString() {
        return "GoodsGroupVO [minPrice=" + minPrice + ", maxPrice=" + maxPrice
                + "]";
    }

}

2.增加接口

GoodsMapper.java

public GoodsGroupVO findByGroup();

3.对应的xml文件添加内容

GoodsMapper.xml

<select id="findByGroup" resultType="com.vo.GoodsGroupVO">
select
min(goods_price) as ‘minPrice’,
max(goods_price) as ‘maxPrice’
from goods
</select>

4.测试方法

test.java

public void test08(){
    try{
        GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
        GoodsGroupVO goodGroupVO = goodsMapper.findByGroup();
        System.out.println(goodGroupVO);
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        this.sqlSession.close();
    }
}