项目结构图：
1.编写实体类。
[sourcecode language=”java” title=”com.bean.Goods.java”]
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 + "]";
}
}
[/sourcecode]
2.set标签在update语句中的使用。
使用动态SQL，我们要修改商品，可以不必先从数据库中查询出来。
[sourcecode language=”java” title=”com.mapper.GoodsMapper.java”]
//修改数据
public void updateByDynamicSQL(Goods goods);
[/sourcecode]
[sourcecode language=”xml” title=”com.mapper.GoodsMapper.xml”]
<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>
[/sourcecode]
3.where标签在查询语句中的使用。
先封装对象类。
[sourcecode language=”java” title=”com.vo.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 + "]";
}
}
[/sourcecode]
[sourcecode language=”java” title=”com.mapper.GoodsMapper.java”]
//封装对象类进行查询
public List<Map<String, Object>> findBySearchVO(GoodsSearchVO goodsSearchVO);
[/sourcecode]
[sourcecode language=”xml” title=”com.mapper.GoodsMapper.xml”]
<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 > #{minPrice}
</if>
<if test="maxPrice != null">
and goods_price < #{maxPrice}
</if>
</where>
</select>
[/sourcecode]
4.foreach标签的使用。
[sourcecode language=”java” title=”com.mapper.GoodsMapper.java”]
//批量增加数据
public void saveBat(List<Goods> goodsList);
//批量删除数据
public void delBat(List<Integer> seq);
[/sourcecode]
[sourcecode language=”xml” title=”com.mapper.GoodsMapper.xml”]
<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>
[/sourcecode]
5.编写测试类。
[sourcecode language=”java” title=”com.test.test.java”]
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();
}
}
}
[/sourcecode]
6.编写mybatis.xml文件。
[sourcecode language=”xml” title=”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&characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/mapper/GoodsMapper.xml" />
</mappers>
</configuration>
[/sourcecode]
[dangerbox title=”问题”]
前面配置好代码测试where标签在查询语句中的使用中发现一直都拿不到数据，发现myeclipse也没有报错。最终发现是因为封装的实体类GoodsSearchVO中的属性Double写成了double，希望大家都细心一点，绝望ing……
[/dangerbox]
数据库下载：[bdbtn]https://pan.benzhu.xyz/代码/源代码/MyBatis/数据库/spring3.sql[/bdbtn]
项目代码：[bdbtn]https://pan.benzhu.xyz/代码/源代码/MyBatis/源代码/MB009.rar[/bdbtn]
