Maven项目集成Mybatis 增删改查操作(二)

作者: 乘风破浪的姐姐 | 来源:发表于2021-12-13 13:43 被阅读0次

在Maven项目集成Mybatis增删改查操作(一)的基础上，对用户进行批量增、删、改操作
1、实体类 TdyMemberInfo.class

package com.sc.pojo;

import java.math.BigDecimal;
import java.util.Date;


public class TdyMemberInfo {
    private Long id;
    private Long shopId;
    private String memberNo;
    private String mobile;
    private String openId;
    private int startIndex;
    private int pageSize;

    @Override
    public String toString() {
        return "TdyMemberInfo{" +
                "id=" + id +
                ", shopId=" + shopId +
                ", memberNo='" + memberNo + '\'' +
                ", mobile='" + mobile + '\'' +
                ", openId='" + openId + '\'' +
                '}';
    }



    public Long getShopId() {
        return shopId;
    }

    public int getStartIndex() {
        return startIndex;
    }

    public void setStartIndex(int startIndex) {
        this.startIndex = startIndex;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

   
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setShopId(Long shopId) {
        this.shopId = shopId;
    }
 
    public void setMemberNo(String memberNo) {
        this.memberNo = memberNo;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public void setMixMobile(String mixMobile) {
        this.mixMobile = mixMobile;
    }

    public void setOpenId(String openId) {
        this.openId = openId;
    }
 
    public String getMemberNo() {
        return memberNo;
    }
    public String getMobile() {
        return mobile;
    }
 
    public String getOpenId() {
        return openId;
    }

    public TdyMemberInfo() {
    }

    public TdyMemberInfo(Long id, Long shopId,String memberNo, String mobile, String openId) {
        this.id = id;
        this.shopId = shopId;
        this.memberNo = memberNo;
        this.mobile = mobile;
        this.openId = openId;
    }
}

上述实例类TdyMemberInfo 中定义的变量 id 对应表t_dy_member_info中的member_id，在TdyMemberInfoMapper.xml的resultMap 中会有对应的映射关系。
startIndex、pageSize 这两个字段用于分页查询。

2、TdyMemberInfoDao.class

package com.sc.dao;

import com.sc.pojo.TdyMemberInfo;

import java.util.List;
import java.util.Map;

public interface TdyMemberInfoDao {
    List<TdyMemberInfo> selectAllmember();

    /**
     * 根据手机号模糊查 询
     * @param value
     * @return
     */
    List<TdyMemberInfo> selectLikeMember(String value);

    /**
     * 分页查询
     * @param tdyMemberInfo
     * @return
     */
    List<TdyMemberInfo> selectMemberList(TdyMemberInfo tdyMemberInfo);

    /**
     * 通过会员ID查询会员
     * @param memberId
     * @return
     */
    TdyMemberInfo selectMemberById(String memberId);

    /**
     * 使用会员对象传参 新增单个会员
     * @param tdyMemberInfo
     * @return
     */
    int addMember(TdyMemberInfo tdyMemberInfo);

    /**
     * 使用map传参 新增会员
     * @param map
     * @return
     */
    int addMemberByMap(Map<String,Object>map);

    /**
     * 批量增加会员
     * @param list
     * @return
     */
    int addMemberBatch(List<TdyMemberInfo> list);

    /**
     * 修改单个会员
     * @param tdyMemberInfo
     * @return
     */
    int updateMember(TdyMemberInfo tdyMemberInfo);

    /**
     * 批量修改会员 使用 replace into
     * @param list
     * @return
     */
    int updateMemberBatch1(List<TdyMemberInfo> list);

    /**
     * 批量修改会员 使用 insert into
     * @param list
     * @return
     */
    int updateMemberBatch2(List<TdyMemberInfo> list);

    /**
     * 删除单个会员
     * @param memberId
     * @return
     */
    int deleteMember(long memberId);

/**
     * 删除多个会员
     * @param ids
     * @return
     */
    int deleteMemberBatch(long[] ids);

}

3、TdyMemberInfoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--上面2行的是约束依赖，固定照抄就好-->
<!--下面的才是要自己编写的地方-->
<!--写mapper的配置文件第一步就是要写<mapper></mapper>标签-->
<!--<mapper></mapper>标签里包含着各个CURD操作的SQL语句-->
<mapper namespace="com.sc.dao.TdyMemberInfoDao">
    <resultMap id="memberInfoMap" type="TdyMemberInfo">
        <!--column对应数据库中的字段，property对应实体类中的属性-->
        <result column="member_id" property="id"/>
    </resultMap>

   <sql id="selectAll">
        select member_no,mobile,open_id from t_dy_member_info
   </sql>
    <!--查找语句-->
    <select id="selectAllmember" resultType="TdyMemberInfo">
        select * from t_dy_member_info
    </select>

    <select id="selectMemberList" resultMap="memberInfoMap">
        <include refid="selectAll"/>
        <where>
            <if test="memberNo !=null and memberNo !=''">
                and member_no like #{memberNo}
            </if>
            <if test="mobile !=null and mobile !=''">
                and mobile like #{mobile}
            </if>
        </where>
        limit #{startIndex}, #{pageSize}
    </select>

    <select id="selectMemberById" resultMap="memberInfoMap">
        select * from t_dy_member_info where member_id =#{id}
    </select>

    <select id="selectLikeMember" parameterType="string" resultType="TdyMemberInfo">
        select * from t_dy_member_info where mobile like #{value}
    </select>

    <!--#{memberNo},#{mobile}必须是TdyMemberInfo中对 应的字段名称-->
    <insert id="addMember" parameterType="TdyMemberInfo">
        INSERT INTO t_dy_member_info
        (shop_id,member_code,member_no,mobile,mix_mobile,open_id,`level`,bind_status,bind_status_time,`point`,point_time,create_time,last_modify_time)
        VALUES (9730231,NULL,#{memberNo},#{mobile},NULL,#{openId},1,1,NULL,0,NULL,NULL,NULL)
    </insert>

   <insert id="addMemberByMap" parameterType="map">
        INSERT INTO t_dy_member_info
        (shop_id,member_code,member_no,mobile,mix_mobile,open_id,`level`,bind_status,bind_status_time,`point`,point_time,create_time,last_modify_time)
        VALUES (9730231,NULL,#{no},#{mobile},NULL,#{open},1,1,NULL,0,NULL,NULL,NULL)
   </insert>

    <insert id="addMemberBatch" parameterType="list">
         INSERT INTO t_dy_member_info
        (shop_id,member_code,member_no,mobile,mix_mobile,open_id,`level`,bind_status,bind_status_time,`point`,point_time,create_time,last_modify_time)
        VALUES 
        <foreach item="TdyMemberInfo" collection="list" separator=",">
            (9730231,NULL,#{TdyMemberInfo.memberNo},#{TdyMemberInfo.mobile},NULL,#{TdyMemberInfo.openId},1,1,NULL,0,NULL,NULL,NULL)
        </foreach>

    </insert>


    <update id="updateMember" parameterType="TdyMemberInfo">
        update t_dy_member_info set open_id=#{openId} ,member_no=#{memberNo} where member_id =#{id}
    </update>


    <!--修改需要传唯一主键member_id-->
    <update id="updateMemberBatch1" parameterType="list">
        replace into t_dy_member_info
         (member_id,member_no,mobile,open_id)
        values
        <foreach collection="list" item="TdyMemberInfo" separator=",">
            (#{TdyMemberInfo.id},#{TdyMemberInfo.memberNo},#{TdyMemberInfo.mobile},#{TdyMemberInfo.openId})
        </foreach>
    </update>

    <update id="updateMemberBatch2" parameterType="list">
        INSERT INTO t_dy_member_info
        (member_id,member_no,mobile,open_id)
        VALUES
        <foreach item="TdyMemberInfo" collection="list" separator=",">
            (#{TdyMemberInfo.id},#{TdyMemberInfo.memberNo},#{TdyMemberInfo.mobile},#{TdyMemberInfo.openId})
        </foreach>
        on duplicate key update
        member_no = values(member_no),mobile=values(mobile),open_id=values(open_id)
    </update>

    <delete id="deleteMember" parameterType="long">
        delete from t_dy_member_info where member_id=#{id}
    </delete>

    <delete id="deleteMemberBatch" parameterType="string">
        delete from t_dy_member_info where member_id in
        <foreach collection="array" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>

上述xml文件中，selectMemberList分页查询时，用到 include 标签引用，这里使用属性refid="selectAll"，引用的是查询所有用户的方法selectAll。如果 refid 指定的方法不在本文件中，那么需要在前面加上 namespace。
列表分页查询：

  <select id="selectMemberList" resultMap="memberInfoMap">
        <include refid="selectAll"/>
        <where>
            <if test="memberNo !=null and memberNo !=''">
                and member_no like #{memberNo}
            </if>
            <if test="mobile !=null and mobile !=''">
                and mobile like #{mobile}
            </if>
        </where>
        limit #{startIndex}, #{pageSize}
    </select>

上述SQL中：
parameterType 这里使用的是配置文件中取的别名，对应实体类。
resultMap 这里使用的是前面定义的 resultMap。
如果用 resultType ，则需要指定具体的类或者 MyBatis 默认的基本数据类型。
MyBatis 默认的基本数据类型有：int、string、long、map。
<where>标签会知道如果它包含的标签中有返回值的话，它就插入一个 where 。
<if>标签用于判断参数是否有值，有值则拼接标签中的 SQL 语句，没有值则不拼接，可以提高 SQL 查询效率和避免传值为 null 的语法错误。

{} 用于传递参数。

批量增加、修改、删除用到foreach标签，该标签中的属性：
collection：指定输入对象中的集合属性。
item：每次遍历生成的对象。
open：开始遍历时的拼接字符串。
close：结束时拼接的字符串。
separator：遍历对象之间需要拼接的字符串。

updateMemberBatch1，批量更新的SQL块中，用到replace into。它跟 insert 功能类似。
不同点在于：replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。否则直接插入新数据。

 <update id="updateMemberBatch1" parameterType="list">
        replace into t_dy_member_info
         (member_id,member_no,mobile,open_id)
        values
        <foreach collection="list" item="TdyMemberInfo" separator=",">
            (#{TdyMemberInfo.id},#{TdyMemberInfo.memberNo},#{TdyMemberInfo.mobile},#{TdyMemberInfo.openId})
        </foreach>
    </update>

这里需要注意：
1）插入数据的表中必须有主键或者是唯一索引！否则，replace into 会直接插入数据，这将导致表中出现重复的数据。
2）replace into 表名 (列名) values (列值)，中列名一定要包含主键，否则就会insert。列值需要使用item中的对象获取对应的值，例如：TdyMemberInfo.id。

updateMemberBatch2，批量更新的SQL块中，用到 on duplicate key update

  <update id="updateMemberBatch2" parameterType="list">
        INSERT INTO t_dy_member_info
        (member_id,member_no,mobile,open_id)
        VALUES
        <foreach item="TdyMemberInfo" collection="list" separator=",">
            (#{TdyMemberInfo.id},#{TdyMemberInfo.memberNo},#{TdyMemberInfo.mobile},#{TdyMemberInfo.openId})
        </foreach>
        on duplicate key update
        member_no = values(member_no),mobile=values(mobile),open_id=values(open_id)
    </update>

on duplicate key update是MySQL特有语法；
该语句是基于主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）使用的。
如果已存在该唯一标示或主键就更新，如果不存在该唯一标示或主键则作为新行插入。
该语句的后面可以放多个字段，用英文逗号分割。

4、测试类，MemberDaoTest.class

package dao;

import com.sc.config.MyBatisUtils;
import com.sc.dao.TdyMemberInfoDao;
import com.sc.pojo.TdyMemberInfo;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MemberDaoTest {

    @Test
    public void selectAll(){
       SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        List<TdyMemberInfo> allMembers = mapper.selectAllmember();
        for(TdyMemberInfo member:allMembers){
            System.out.println(member);
        }
        sqlSession.close();
    }

    @Test
    public void selectMemberList(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        int pageNum  =2;
        int pageSize =3;
        tdyMemberInfo.setStartIndex((pageNum - 1) * pageSize);
        tdyMemberInfo.setPageSize(pageSize);
        tdyMemberInfo.setMemberNo("202111%");
        tdyMemberInfo.setMobile("185%");
        List<TdyMemberInfo> allMembers = mapper.selectMemberList(tdyMemberInfo);
        for(TdyMemberInfo member:allMembers){
            System.out.println(member);
        }
        sqlSession.close();
    }

    @Test
    public void selectMemberById(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        TdyMemberInfo tdyMemberInfo = mapper.selectMemberById("481");
        System.out.println(tdyMemberInfo);

        sqlSession.close();
    }

    @Test
    public void selectLikeMember(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        List<TdyMemberInfo> tdyMemberInfos = mapper.selectLikeMember("18660467%");
        for (TdyMemberInfo tdyMemberInfo : tdyMemberInfos) {
            System.out.println(tdyMemberInfo);
        }
        sqlSession.close();
    }

    @Test
    public void addMember(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        tdyMemberInfo.setMemberNo("202112091000011");
        tdyMemberInfo.setMobile("18660467745");
        tdyMemberInfo.setOpenId("PDH2JKKE3JUKAG6M7VOX14");

        int code = mapper.addMember(tdyMemberInfo);
        if(code>0){
            System.out.println("新增员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 批量插入
     */
    @Test
    public void addMemberBatch(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        tdyMemberInfo.setMemberNo("202112091000011");
        tdyMemberInfo.setMobile("18660467745");
        tdyMemberInfo.setOpenId("PDH2JKKE3JUKAG6M7VOX14");

        TdyMemberInfo tdyMemberInfo2 = new TdyMemberInfo();
        tdyMemberInfo2.setMemberNo("202112091000012");
        tdyMemberInfo2.setMobile("18660467749");
        tdyMemberInfo2.setOpenId("PDH2JKKE3JUKAG6M7VOX149");

        List<TdyMemberInfo> list = new ArrayList<>();
        list.add(tdyMemberInfo);
        list.add(tdyMemberInfo2);

        int code = mapper.addMemberBatch(list);
        if(code>0){
            System.out.println("批量新增员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }


    @Test
    public void addMemberByMap(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("no","20211209100002");
        map.put("mobile","18660467799");
        map.put("open","111PDH2JKKE3JUKAG111");

        int code = mapper.addMemberByMap(map);
        if(code>0){
            System.out.println("使用Map传参，新增员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }



    @Test
    public void updateMember(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        tdyMemberInfo.setId(new Long(481));
        tdyMemberInfo.setMemberNo("20211209100001111");
        tdyMemberInfo.setOpenId("PDH2JKKE3JUKAG6M7VOX4555");
        int code = mapper.updateMember(tdyMemberInfo);
        if(code>0){
            System.out.println("修改员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 批量修改会员信息
     * replace into 首先尝试插入数据到表中，
     * 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
     * 2. 否则，直接插入新数据
     */
    @Test
    public void updateMemberBatch1(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        tdyMemberInfo.setId(new Long(484));
        tdyMemberInfo.setMemberNo("202112091000011484");
        tdyMemberInfo.setMobile("18660467747");
        tdyMemberInfo.setOpenId("PDH2JKKE3JUKAG6M7VOX14");

        TdyMemberInfo tdyMemberInfo2 = new TdyMemberInfo();
        tdyMemberInfo2.setId(new Long(485));
        tdyMemberInfo2.setMemberNo("202112091000012485");
        tdyMemberInfo2.setMobile("18660467740");
        tdyMemberInfo2.setOpenId("PDH2JKKE3JUKAG6M7VOX149");

        List<TdyMemberInfo> list = new ArrayList<>();
        list.add(tdyMemberInfo);
        list.add(tdyMemberInfo2);

        int code = mapper.updateMemberBatch1(list);
        if(code>0){
            System.out.println("批量修改员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 批量修改会员2
     */
    @Test
    public void updateMemberBatch2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        tdyMemberInfo.setId(new Long(484));
        tdyMemberInfo.setMemberNo("202112091001111");
        tdyMemberInfo.setMobile("18660467777");
        tdyMemberInfo.setOpenId("PDH2JKKE3JUKAG6M71111");

        TdyMemberInfo tdyMemberInfo2 = new TdyMemberInfo();
        tdyMemberInfo2.setId(new Long(485));
        tdyMemberInfo2.setMemberNo("202112091001112");
        tdyMemberInfo2.setMobile("18660467776");
        tdyMemberInfo2.setOpenId("PDH2JKKE3JUKAG6M72222");

        List<TdyMemberInfo> list = new ArrayList<>();
        list.add(tdyMemberInfo);
        list.add(tdyMemberInfo2);

        int code = mapper.updateMemberBatch2(list);
        if(code>0){
            System.out.println("批量修改员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 删除单个会员
     */

    @Test
    public void deleteMember(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        int code = mapper.deleteMember(new Long(481));
        if(code>0){
            System.out.println("删除员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    /**
     * 批量删除会员
     */
    @Test
    public void deleteMemberBatch(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TdyMemberInfoDao mapper = sqlSession.getMapper(TdyMemberInfoDao.class);
        long[] ids = {484,485};

        int code = mapper.deleteMemberBatch(ids);
        if(code>0){
            System.out.println("批量删除员工成功。");
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

Maven项目集成Mybatis 增删改查操作(二)

{} 用于传递参数。

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读