JAVA

【MyBatis】动态SQL

项目结构图

数据库SQL脚本：本地下载
案例代码：本地下载

教程

1.编写实体类

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 + "]";
    }
}

2.set标签在update语句中的使用

使用动态SQL，我们要修改商品，可以不必先从数据库中查询出来。

GoodsMapper.java

//修改数据
public void updateByDynamicSQL(Goods goods);

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>

3.where标签在查询语句中的使用

先封装对象类。

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 + "]";
    }
}

GoodsMapper.java

//封装对象类进行查询
public List<Map<String, Object>> findBySearchVO(GoodsSearchVO goodsSearchVO);

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 &gt; #{minPrice}
</if>
<if test="maxPrice != null">
and goods_price &lt; #{maxPrice}
</if>
</where>
</select>

4.foreach标签的使用

GoodsMapper.java

//批量增加数据
public void saveBat(List<Goods> goodsList);
//批量删除数据
public void delBat(List<Integer> seq);

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>

5.编写测试类

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

6.编写mybatis.xml文件

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……

如果觉得文章对你有用，请随意赞赏

MyBatis 动态sql

【MyBatis】动态SQL

https://www.benzhu.xyz/archives/mybatis009

作者

小猪

发布于

2019-04-03

更新于

2024-11-24

许可协议

CC BY 4.0

【MyBatis】动态SQL

项目结构图

教程

1.编写实体类

2.set标签在update语句中的使用

3.where标签在查询语句中的使用

4.foreach标签的使用

5.编写测试类

6.编写mybatis.xml文件

问题

作者

发布于

更新于

许可协议

评论