三个常用查询：根据用户名 / token查询用户信息+链表分页条件查询

目录

1.根据用户名或者token查询用户信息

会员信息实体类

统一状态Result类

 controller层

service层及实现类

dao层

测试：

2.链表分页条件查询

会员等级实体类

封装条件类PageVo 

controller层

service层及实现类

dao层

Mapper.xml层

测试

vue前端参考

---

---

1.根据用户名或者token查询用户信息

mybatisplus...

微服务项目前期环境：项目搭建，依赖注入...

会员表数据库

SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for mbs_member
-- ----------------------------
DROP TABLE IF EXISTS `mbs_member`;
CREATE TABLE `mbs_member` (`id` int NOT NULL AUTO_INCREMENT COMMENT '会员客户ID',`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '账号',`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '密码',`pw_salt` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '盐值列',`status` int DEFAULT NULL COMMENT '账号状态（帐号启用状态:0->启用；1->禁用）',`id_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '(身份证，护照，军官证，台胞证，港澳台来往内地通行证)',`path_front` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '正面',`path_reverse` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '反面',`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '客户名字',`contact_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '联系人',`contact_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '联系电话',`contact_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '联系地址',`license_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '驾照号',`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别',`phone_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电话',`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地址',`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',`member_level_id` int DEFAULT NULL COMMENT '会员级别（普通，金卡，白金，钻石）',`Account_Balance` double DEFAULT '0' COMMENT '账户余额',`integration` int DEFAULT '0' COMMENT '积分',`growth` double DEFAULT NULL COMMENT '成长值',`history_integration` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '历史积分数量(只加不减)',`open_id` int DEFAULT NULL COMMENT '微信返回ID',`token` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '登录验证token',`id_card` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '证件编号',`head_pic` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '头像',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of mbs_member
-- ----------------------------
INSERT INTO `mbs_member` VALUES ('71', 'zhanghaojie', 'f5455390512559851fdffdc9dc71e1debe7261c41509579a3cb8d8085b86bee95d3808d9345e79790a2b2a153effe2615b1b4302694018d0d369f13a4a6e5005', '8efbe27a-4', '0', '1', null, null, '小浩杰', null, null, null, '410100123457', null, '15136204889', null, '[e-mail]', '1', '11240', '5610', '3760', '2260', null, null, null, 'https://wangzherongyao.oss-cn-beijing.aliyuncs.com/upload/files/1605770604552-7c5b7014-09cb-4d0a.jpg');
INSERT INTO `mbs_member` VALUES ('78', 'test111', '111591072d3b1a693158b77a16df045daf87fca20ea7fc00c32411b6ace2d2c7ac0a70777619883fac37417ca9a09593465c51e7ab56cfb2b1debc174e2ffb3a', 'fcba1cda-2ce7-4d49-9658-976ff2b5aa9d', '0', '1', null, null, 'test111', null, null, null, '511621200011011212', null, '18398591677', null, '[e-mail]', '3', '94620.96000000002', '1000', '65790.4', '0', null, null, '51162******212', 'https://qy002-bookstore.oss-cn-hangzhou.aliyuncs.com/staff_headicon/9ddb6b4b-8332-47a6-84ae-d5b9234e5065bg5.jpg');
INSERT INTO `mbs_member` VALUES ('88', 'cjj', '836bac861900ae9df5bf390ede7c2a45c1287f346139d3d3576300be822e1f17746c95d622955cc292427748dc12959447a28ba46ce13eb3c41fdcab09956161', 'c5546504-f2a1-4494-a738-8297e786acee', '0', '1', null, null, '成JJ', null, null, null, null, null, '13645678922', null, null, '1', '200', '50', '10500', '500', null, null, null, 'https://qy002-bookstore.oss-cn-hangzhou.aliyuncs.com/staff_headicon/29727801-0855-484d-bf5d-c6f69e8a72ad111.jpg');

会员信息实体类

@TableName("mbs_member")
@Data
@ApiModel("会员信息实体类")
public class Member implements Serializable {/*** 会员客户ID*/@ApiModelProperty("会员客户ID")@TableIdprivate Integer id;/*** 账号*/@ApiModelProperty("账号")private String username;/*** 密码*/@ApiModelProperty("密码")private String password;/*** 盐值列*/@ApiModelProperty("盐值列")private String pwSalt;/*** 账号状态（帐号启用状态:0->启用；1->禁用）*/@ApiModelProperty("账号状态（帐号启用状态:0->启用；1->禁用）")private Integer status;/*** (身份证，护照，军官证，台胞证，港澳台来往内地通行证)*/@ApiModelProperty("(身份证，护照，军官证，台胞证，港澳台来往内地通行证)")private String idType;/*** 正面*/@ApiModelProperty("正面")private String pathFront;/*** 反面*/@ApiModelProperty("反面")private String pathReverse;/*** 客户名字*/@ApiModelProperty("客户名字")private String name;/*** 联系人*/@ApiModelProperty("联系人")private String contactName;/*** 联系电话*/@ApiModelProperty("联系电话")private String contactPhone;/*** 联系地址*/@ApiModelProperty("联系地址")private String contactAddress;/*** 驾照号*/@ApiModelProperty("驾照号")private String licenseNo;/*** 性别*/@ApiModelProperty("性别")private String sex;/*** 电话*/@ApiModelProperty("电话")private String phoneNum;/*** 地址*/@ApiModelProperty("地址")private String address;/*** 邮箱*/@ApiModelProperty("邮箱")private String email;/*** 会员级别（普通，金卡，白金，钻石）*/@ApiModelProperty("会员级别（普通，金卡，白金，钻石）")private Integer memberLevelId;/*** 账户余额*/@ApiModelProperty("账户余额")private Double accountBalance;/*** 积分*/@ApiModelProperty("积分")private Integer integration;/*** 成长值*/@ApiModelProperty("成长值")private Double growth;/*** 历史积分数量(只加不减)*/@ApiModelProperty("历史积分数量(只加不减)")private String historyIntegration;/*** 微信返回ID*/@ApiModelProperty("微信返回ID")private Integer openId;/*** 登录验证token*/@ApiModelProperty("登录验证token")private String token;/*** 证件编号*/@ApiModelProperty("证件编号")private String idCard;/*** 头像*/@ApiModelProperty("头像")private String headPic;
//---------------------------------------------------------------------
//    @ApiModelProperty("对应会员级别，一对一")@TableField(exist = false)private MemberLevel memberLevel;//会员等级名}

统一状态Result类

@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel("统一的json对象")
public class Result<T> {@ApiModelProperty("状态码")private Integer code;@ApiModelProperty("提示消息")private String msg;@ApiModelProperty("响应data数据")private T data;}

 controller层

@RestController
@RequestMapping("member")
@Api(tags = "客户管理--会员管理")
public class MemberController {@Autowiredprivate MemberService memberService;@GetMapping("/getByUsername")@ApiOperation("根据会员名称查询会员信息")public Member getByUsername(@RequestParam String username){return memberService.getByUsername(username);}@GetMapping("/getMember")@ApiOperation("根据token查询会员基础信息")public Result<Member> getMember(@RequestParam String token){return memberService.getMember(token);}
}

service层及实现类

public interface MemberService  {Member getByUsername(String username);Result<Member> getMember(String token);
}//-------------------------------------------------------------@Service
public class MemberServiceImpl implements MemberService {@Autowiredprivate MemberDao memberDao;@Overridepublic Member getByUsername(String username) {QueryWrapper wrapper = new QueryWrapper();//会员名称wrapper.eq("username",username);//账号状态（帐号启用状态:0->启用；1->禁用）wrapper.eq("status",0);Member member = memberDao.selectOne(wrapper);return member;}@Autowiredprivate StringRedisTemplate redisTemplate;@Overridepublic Result<Member> getMember(String token) {ValueOperations<String, String> ops = redisTemplate.opsForValue();String memberCache = ops.get("sso:member:" + token);Member member = JSON.parseObject(memberCache, Member.class);return new Result<>(200,"成功",member);}}

dao层

@Mapper
public interface MemberDao extends BaseMapper<Member> {
}

测试：

2.链表分页条件查询

等级表数据库

SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for mbs_member_level
-- ----------------------------
DROP TABLE IF EXISTS `mbs_member_level`;
CREATE TABLE `mbs_member_level` (`id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`growth_point` int DEFAULT NULL COMMENT '成长值',`default_status` int DEFAULT NULL COMMENT '是否为默认等级：0->不是；1->是',`free_freight_point` decimal(10,2) DEFAULT NULL COMMENT '免运费标准',`priviledge_comment` int DEFAULT NULL COMMENT '是否有评论获奖励特权',`priviledge_promotion` int DEFAULT NULL COMMENT '是否有专享活动特权',`priviledge_member_price` int DEFAULT NULL COMMENT '是否有会员价格特权',`priviledge_birthday` int DEFAULT NULL COMMENT '是否有生日特权',`note` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '说明',`ratio` double DEFAULT NULL COMMENT '积分 普通1 白银1.2 黄金1.5....',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1684561963877740550 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of mbs_member_level
-- ----------------------------
INSERT INTO `mbs_member_level` VALUES ('1', '普通', '10000', '1', '200.00', '1', '0', '1', '0', '等级1', '1');
INSERT INTO `mbs_member_level` VALUES ('2', '白银', '20000', '0', '100.00', '1', '1', '1', '1', '等级2', '1.2');
INSERT INTO `mbs_member_level` VALUES ('3', '黄金', '50000', '0', '50.00', '1', '1', '1', '1', '等级3', '1.5');
INSERT INTO `mbs_member_level` VALUES ('4', '钻石', '90000', '0', '0.00', '1', '0', '1', '1', '等级4', '1.8');
INSERT INTO `mbs_member_level` VALUES ('10', '星耀', '140000', '0', '800.00', '1', '1', '1', '11', '最高级', '2');

当前需要链表查询会员等级

会员等级实体类

@TableName("mbs_member_level")
@Data
@ApiModel("客户管理--会员等级-实体类")
public class MemberLevel implements Serializable {/*** */@ApiModelProperty("")@TableId(type = IdType.AUTO)private String id;/*** */@ApiModelProperty("等级名")
//    @TableField(value = "name")private String name;/*** 成长值*/@ApiModelProperty("成长值")private Integer growthPoint;/*** 是否为默认等级：0->不是；1->是*/@ApiModelProperty("是否为默认等级：0->不是；1->是")private Integer defaultStatus;/*** 免运费标准*/@ApiModelProperty("免运费标准")private BigDecimal freeFreightPoint;/*** 是否有评论获奖励特权*/@ApiModelProperty("是否有评论获奖励特权")private Integer priviledgeComment;/*** 是否有专享活动特权*/@ApiModelProperty("是否有专享活动特权")private Integer priviledgePromotion;/*** 是否有会员价格特权*/@ApiModelProperty("是否有会员价格特权")private Integer priviledgeMemberPrice;/*** 是否有生日特权*/@ApiModelProperty("是否有生日特权")private Integer priviledgeBirthday;/*** 说明*/@ApiModelProperty("说明")private String note;/*** 积分 普通1 白银1.2 黄金1.5....*/@ApiModelProperty("积分 普通1 白银1.2 黄金1.5....")private Double ratio;}

封装条件类PageVo 

@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "分页条件查询--用户管理")
public class PageVo {Integer pageNo;Integer pageSize;CouponVo Data;}//---------------------------------------@Data
public class CouponVo {String name;String phoneNum;String email;String userName;String orderSn;}

controller层

@RestController
@RequestMapping("member")
@Api(tags = "客户管理--会员管理")
public class MemberController {@Autowiredprivate MemberService memberService;//分页条件查询@PostMapping("queryByPage222")public Result<Map<String,Object>> pageResult(@RequestBody PageVo pageVo){return memberService.selectAllPage(pageVo);}
}

service层及实现类

public interface MemberService  {//分页条件查询Result<Map<String,Object>> selectAllPage(PageVo pageVo);}//-------------------------------------------------------------@Service
public class MemberServiceImpl implements MemberService {@Autowiredprivate MemberDao memberDao;//分页条件查询@Overridepublic Result<Map<String,Object>> selectAllPage(PageVo pageVo) {Page<Member> page = new Page<>(pageVo.getPageNo(), pageVo.getPageSize());List<Member> members = memberDao.selectByMyPage(page,pageVo);// page.setRecords(members);//page.setTotal(members.size());//获取分页总条数long total = page.getTotal();Map<String,Object> data = new HashMap<>();data.put("records",members);data.put("total",total);return new Result<>(200,"查询成功",data);}
}

dao层

@Mapper
public interface MemberDao extends BaseMapper<Member> {//分页查询List<Member> selectByMyPage(Page<Member> page, PageVo pageVo);
}

Mapper.xml层

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fifth.member.dao.MemberDao"><!--链表查询条件查询-一对一--><resultMap id="selectAll110" type="com.fifth.entity.Member" autoMapping="true"><id property="id" column="id"/><association property="memberLevel" javaType="com.fifth.entity.MemberLevel" autoMapping="true"><id property="id" column="id"/><result property="name" column="memberLevelName"/></association></resultMap><select id="selectByMyPage" resultMap="selectAll110">SELECT m.*,ml.name memberLevelNameFROM mbs_member m JOIN mbs_member_level ml ON m.member_level_id = ml.id<where><if test="pageVo.data.phoneNum != null and pageVo.data.phoneNum != ''">AND m.phone_num LIKE CONCAT('%', #{pageVo.data.phoneNum}, '%')</if><if test="pageVo.data.email != null and pageVo.data.email != ''">AND m.email LIKE CONCAT('%', #{pageVo.data.email}, '%')</if><if test="pageVo.data.userName != null and pageVo.data.userName != ''">AND m.username LIKE CONCAT('%', #{pageVo.data.userName}, '%')</if><if test="pageVo.data.name != null and pageVo.data.name != ''">AND m.name LIKE CONCAT('%', #{pageVo.data.name}, '%')</if></where></select></mapper>

测试

vue前端参考

<template><div><!--搜索查询--><el-row><el-col :span="3"><el-inputv-model="searchForm.name"size="mini"clearableplaceholder="请输姓名"></el-input></el-col><el-col :span="3"><el-inputv-model="searchForm.userName"size="mini"clearableplaceholder="请输用户名"></el-input></el-col><el-col :span="3"><el-inputv-model="searchForm.email"size="mini"clearableplaceholder="邮箱"></el-input></el-col><el-col :span="3"><el-inputv-model="searchForm.phoneNum"size="mini"clearableplaceholder="电话"></el-input></el-col><el-col :span="3"><el-buttontype="primary"size="mini"@click="search()"icon="el-icon-search"v-has="'member:search'">搜索</el-button></el-col><el-col :span="3"><el-buttontype="primary"size="mini"v-has="'member:flush'"@click="flush()"icon="el-icon-refresh">刷新</el-button></el-col></el-row><el-row><el-table :data="tableData" style="width: 100%"><el-table-column label="用户头像"><template slot-scope="scope"><el-imagestyle="width: 60px; height: 60px":src="scope.row.headPic"></el-image></template></el-table-column><el-table-columnprop="name"label="客户姓名"align="center"width="100"></el-table-column><el-table-columnprop="username"align="center"label="用户名"width="100"></el-table-column><el-table-column prop="memberLevel.name" width="150" align="center" label="会员级别"></el-table-column><el-table-column prop="accountBalance" width="150" align="center" label="账户余额"></el-table-column><el-table-column prop="integration" width="150" align="center" label="积分"></el-table-column><el-table-column prop="licenseNo" width="180" align="center" label="驾照号"></el-table-column><el-table-column prop="email" width="180" align="center" label="邮箱"></el-table-column><el-table-column prop="phoneNum" width="180" align="center" label="电话"></el-table-column><el-table-column prop="idType" width="180" align="center" :formatter="zjFormat" label="证件类型"></el-table-column><el-table-column prop="idCard" width="180" align="center" label="身份证号"><template slot-scope="scope">{{scope.row.idCard? scope.row.idCard.replace(/^(.{6})(?:\w+)(.{4})$/, "\$1********\$2"):""}}</template></el-table-column><el-table-column prop="address" width="180" align="center" label="地址"></el-table-column><el-table-column prop="contactName" width="180" align="center" label="联系人"></el-table-column><el-table-column prop="contactAddress" width="280" align="center" label="联系地址"></el-table-column><el-table-columnprop="contactPhone"label="联系人电话"align="center"width="180"></el-table-column><el-table-column prop="sex" :formatter="sexFormat" label="性别"></el-table-column><el-table-column prop="status" :formatter="zhFormat" label="账号状态"></el-table-column><el-table-column width="150" label="用户状态"><template slot-scope="scope"><el-switchv-model="scope.row.status"active-color="#13ce66"inactive-color="#ff4949":active-value=0:inactive-value=1active-text="启用"inactive-text="禁用"@change="handleChangeStatus(scope.$index, scope.row)"></el-switch></template></el-table-column></el-table></el-row><el-row><!--分页插件--><el-pagination@size-change="handleSizeChange"@current-change="handleCurrentChange":current-page="currentPage":page-sizes="[2, 3, 5, 10, 20]":page-size="pageSize"layout="total, sizes, prev, pager, next, jumper":total="total"></el-pagination></el-row></div>
</template><script>
export default {data() {return {value: true,optionsA: [],tableData: [],imgUrl: "",currentPage: 1, //页码 第几页pageSize: 5, //每页显示数量total: 100, //总条数searchForm: { name: "", phoneNum: "", email: "", userName: "" }, //搜索form表单dialogFormVisible: false, //是否显示对话框  默认是不显示form: { passold: "", pass: "", checkPass: "" },formLabelWidth: "120px",condition: "",};},created() {//初始化方法 类似于jquery  $({})this.loadData();var _this = this;_this.$http.post("/member/memberLevel/queryByPage", {pageNo: this.currentPage,pageSize: 100,data: {},}).then(function (response) {console.log(JSON.stringify(response));if (response.data.code == 200) {_this.optionsA = response.data.data.records;//_this.total = response.data.data.total;}}).catch(function (error) {//错误的处理// handle errorconsole.log(error);}).then(function () {//总执行 最终处理      类似java  异常处理的finally// always executed});},//所有函数methods: {//加载数据loadData() {var _this = this;//复制 $http官网代码// var token  = localStorage.getItem("token");_this.$http.post("/member/queryByPage222", {pageNo: this.currentPage,pageSize: this.pageSize,data: {name: this.searchForm.name,phoneNum: this.searchForm.phoneNum,email: this.searchForm.email,userName: this.searchForm.userName,},}).then(function (response) {console.log(JSON.stringify(response.data.data))if (response.data.code == 200) {_this.tableData = response.data.data.records;_this.total = response.data.data.total;}}).catch(function (error) {console.log(error);_this.$message.error("你错了。。。");});},zjFormat(row, column, cellValue, index){return cellValue == "1" ? "身份证" : cellValue == "2" ? "护照" : cellValue == "3" ? "军官证" : cellValue == "4" ? "台胞证" : "港澳台来往内地通行证";},//格式化性别方法sexFormat(row, column, cellValue, index) {//row当前行数据//column 列名称//cellValue 列的值//index 下标//alert(cellValue);return cellValue == 0 ? "男" : cellValue == 1 ? "女" : "未知";},zhFormat(row, column, cellValue, index) {//row当前行数据//column 列名称//cellValue 列的值//index 下标//alert(cellValue);return cellValue == 0 ? "正常" : "异常";},//处理每页数量发生变化handleSizeChange(val) {console.log(`每页 ${val} 条`);//让每页数量发生变化this.pageSize = val;//重载数据this.loadData();},//处理当前页变化handleCurrentChange(val) {console.log(`当前页: ${val}`);//让每页数量发生变化this.currentPage = val;//重载数据this.loadData();},//查询search() {this.loadData();},//刷新flush() {location.reload();}, //修改用户状态（禁用/启用）handleChangeStatus(index, row) {//     //确认框提示//     this.$confirm("此操作将永久删除该数据, 是否继续?", "提示", {//       confirmButtonText: "确定",//       cancelButtonText: "取消",//       type: "warning",//     })//       .then(() => {//     var _this = this; //复制 $http官网代码 // var token  = localStorage.getItem("token");//     //alert(JSON.stringify(row));//     _this.$http//       .post("/member/edit", row)//       .then(function (response) {//         if (response.data.code == 200) {//           //alert("操作成功");//           _this.loadData;//         }//       })//       .catch(function (error) {//         console.log(error);//         _this.$message.error("你错了。。。");//       });console.log(index, row);//确认框提示this.$confirm("此操作将关乎账号的启用禁用, 是否继续?", "提示", {confirmButtonText: "确定",cancelButtonText: "取消",type: "warning",}).then(() => {//让_this等于当前vue对象var _this = this;// alert(111);//赋值 $http官网代码  this.form =form:{deptName:'',description:'',status:1}_this.$http.post("/member/edit", row) //指定请求的url及参数  get.then(function (response) {//正确返回对象的处理// handle successconsole.log(response);//JSON.stringify   把json转换为json字符串，方便查看console.log(JSON.stringify(response));//直接使用this 会指 $http对象  错误// this.tableData//判断返回值是否正确if (response.data.code == 200) {//提示添加成功_this.$message({message: "恭喜你，这是一条成功消息",type: "success",});//重新加载数据_this.loadData();} else {//错误提示信息_this.$message.error("错了哦，这是一条错误消息");}}).catch(function (error) {//错误的处理// handle errorconsole.log(error);});}).catch(() => {this.$message({type: "info",message: "已取消",});});},},
};
</script>
<style>
</style>

链表前端拿数据