一只倔强的笨猪

【MyBatis】动态SQL

项目结构图:
《【MyBatis】动态SQL》
1.编写实体类。

package com.bean;

public class Goods {
	private Integer goodsId;
	private String goodsName;
	private Double goodsPrice;
	private Integer goodsNum;
	private Integer goodsType;
	
	public Goods() {
	}

	public Goods(Integer goodsId, String goodsName, Double goodsPrice,
			Integer goodsNum, Integer goodsType) {
		this.goodsId = goodsId;
		this.goodsName = goodsName;
		this.goodsPrice = goodsPrice;
		this.goodsNum = goodsNum;
		this.goodsType = goodsType;
	}
	
	public Goods(String goodsName, Double goodsPrice,
			Integer goodsNum, Integer goodsType) {
		this.goodsName = goodsName;
		this.goodsPrice = goodsPrice;
		this.goodsNum = goodsNum;
		this.goodsType = goodsType;
	}

	public Integer getGoodsId() {
		return goodsId;
	}

	public void setGoodsId(Integer goodsId) {
		this.goodsId = goodsId;
	}

	public String getGoodsName() {
		return goodsName;
	}

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

	public Double getGoodsPrice() {
		return goodsPrice;
	}

	public void setGoodsPrice(Double goodsPrice) {
		this.goodsPrice = goodsPrice;
	}

	public Integer getGoodsNum() {
		return goodsNum;
	}

	public void setGoodsNum(Integer goodsNum) {
		this.goodsNum = goodsNum;
	}

	public Integer getGoodsType() {
		return goodsType;
	}

	public void setGoodsType(Integer goodsType) {
		this.goodsType = goodsType;
	}

	@Override
	public String toString() {
		return "Goods [goodsId=" + goodsId + ", goodsName=" + goodsName
				+ ", goodsPrice=" + goodsPrice + ", goodsNum=" + goodsNum
				+ ", goodsType=" + goodsType + "]";
	}
}

2.set标签在update语句中的使用。
使用动态SQL,我们要修改商品,可以不必先从数据库中查询出来。

	//修改数据
	public void updateByDynamicSQL(Goods goods);
<update id="updateByDynamicSQL" parameterType="com.bean.Goods">
		update goods
		<set>
			<if test="goodsName != null">
				goods_name = #{goodsName},
			</if>
			<if test="goodsPrice != null">
				goods_price = #{goodsPrice},
			</if>
			<if test="goodsNum != null">
				goods_num = #{goodsNum},
			</if>
			<if test="goodsType != null and goodsType.typeId != null">
				goods_type = #{goodsType.typeId},
			</if>
		</set>
		where goods_id = #{goodsId}
</update>

3.where标签在查询语句中的使用。
先封装对象类。

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 + "]";
	}
		
}
	//封装对象类进行查询
	public List<Map<String, Object>> findBySearchVO(GoodsSearchVO goodsSearchVO);
<select id="findBySearchVO" parameterType="com.vo.GoodsSearchVO" resultType="java.util.Map">
		select
		goods_name as 'goodsName',
		goods_price as 'goodsPrice',
		goods_num as 'goodsNum',
		typeName as 'typeName'
		from goods
		left join goods_type
		on goods_type.typeId = goods.goods_type
		<where>
			<if test="goodsName != null">
				goods_name like concat('%', #{goodsName}, '%')
			</if>
			<if test="minPrice != null">
				and goods_price &gt; #{minPrice}
			</if>
			<if test="maxPrice != null">
				and goods_price &lt; #{maxPrice}
			</if>
		</where>
</select>

4.foreach标签的使用。

	//批量增加数据
	public void saveBat(List<Goods> goodsList);
	//批量删除数据
	public void delBat(List<Integer> seq);
	<insert id="saveBat" parameterType="java.util.List">
		insert into goods (goods_name, goods_price, goods_num, goods_type) values
		<foreach collection="list" item="goods" separator=",">
			(#{goods.goodsName}, #{goods.goodsPrice}, #{goods.goodsNum},#{goods.goodsType})
		</foreach>
	</insert>
	<delete id="delBat" parameterType="java.util.List">
		delete from goods
		where goods_id in 
		<foreach collection="list" item="id" separator="," open="(" close=")" index="index">
			#{id}
		</foreach>
	</delete>

5.编写测试类。

package com.test;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.bean.Goods;
import com.mapper.GoodsMapper;
import com.vo.GoodsSearchVO;

public class test {
	
	private SqlSession sqlSession;

	public void test01(){
		try{
			GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
			Goods goods = new Goods();
			goods.setGoodsId(3);
			goods.setGoodsPrice(70.0);
			goodsMapper.updateByDynamicSQL(goods);
			this.sqlSession.commit();  //提交事务
			System.out.println("修改数据成功!");
		} catch(Exception e) {
			e.printStackTrace();
			this.sqlSession.rollback();
			throw new RuntimeException(e);
		} finally {
			this.sqlSession.close();
		}
	}
	
	public void test02(){
		try{
			GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
			GoodsSearchVO goodsSearchVO = new GoodsSearchVO();
			goodsSearchVO.setGoodsName("篮球");
			List<Map<String, Object>> data = goodsMapper.findBySearchVO(goodsSearchVO);
			System.out.println("拿到了" + data.size() + "条数据");
			for (Map<String, Object> map : data) {
				for (Entry<String, Object> ent : map.entrySet()) { 
					System.out.println(ent.getKey() + "|" + ent.getValue());
				}
				System.out.println("============================");
			}
		} finally {
			this.sqlSession.close();
		}
	}
	
	public void test03(){
		try{
			GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
			List<Goods> goodsList = new ArrayList<Goods>();
			goodsList.add(new Goods("排球",50d,3,1));
			goodsList.add(new Goods("网球",20d,3,1));
			goodsMapper.saveBat(goodsList);
			this.sqlSession.commit();  //提交事务
			System.out.println("批量插入数据成功!");
		} catch(Exception e) {
			e.printStackTrace();
			this.sqlSession.rollback();
			throw new RuntimeException(e);
		} finally {
			this.sqlSession.close();
		}
	}
	
	public void test04(){
		try{
			GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
			List<Integer> seq = 
					new ArrayList<Integer>(Arrays.asList(new Integer[]{4,5}));
			goodsMapper.delBat(seq);
			this.sqlSession.commit();
			System.out.println("批量删除数据成功!");
		} catch(Exception e) {
			e.printStackTrace();
			this.sqlSession.rollback();
			throw new RuntimeException(e);
		} finally{
			this.sqlSession.close();
		}
	}
	@Test 
	public void testF(){
		test04();
	}
	
	@Before
	public void before() {
		try {
			String resource = "mybatis.xml";
			Reader reader = Resources.getResourceAsReader(resource);
			SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
			this.sqlSession = ssf.openSession();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

6.编写mybatis.xml文件。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://127.0.0.1:3306/spring?useUnicode=true&amp;characterEncoding=utf8" />
				<property name="username" value="root" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/mapper/GoodsMapper.xml" />
	</mappers>
</configuration>

问题

前面配置好代码测试where标签在查询语句中的使用中发现一直都拿不到数据,发现myeclipse也没有报错。最终发现是因为封装的实体类GoodsSearchVO中的属性Double写成了double,希望大家都细心一点,绝望ing……

数据库下载: 本地下载
项目代码: 本地下载

点赞

发表评论

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