一只倔强的笨猪

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

准备前提

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

一.只有一个参数查询。
1.增加接口。

public Goods findById(int id);

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

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

3.测试方法。

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.增加接口。

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

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

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

3.测试方法。

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.增加接口。

public void save(Goods goods);

2.对应的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.测试方法。

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.创建对象类。

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.增加接口。

public List<Goods> findBySearchVO(GoodsSearchVO goodsSearchVO);

3.对应的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.测试方法。

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.创建对象类。

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.增加接口。

public GoodsGroupVO findByGroup();

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

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

4.测试方法。

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();
		}
	}

源代码下载: 本地下载

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注