1、实体类 TdyMemberInfo.class
代码语言：javascript复制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
代码语言：javascript复制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
代码语言：javascript复制<?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。 列表分页查询：
代码语言：javascript复制 <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 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 否则直接插入新数据。
代码语言：javascript复制 <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
代码语言：javascript复制 <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
代码语言：javascript复制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();
}
}
