踩坑Mybatis + Mybatis-plus + MyBatis-plus-join

devtools/2024/9/23 3:55:36/

数据库里有两张表

tb_bursary和tb_student

--tb_bursaryCREATE TABLE `tb_bursary` (`id` int(11) NOT NULL AUTO_INCREMENT,`student_id` int(11) NOT NULL,`bursary` int(11) NOT NULL,`is_approve` char(1) NOT NULL DEFAULT '0',`approver` varchar(50) DEFAULT NULL,`approve_datetime` timestamp NULL DEFAULT NULL,`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`remarks` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;--tb_studentCREATE TABLE `tb_student` (`id` int(11) NOT NULL AUTO_INCREMENT,`sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`telephone` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`introducation` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`portrait_path` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`clazz_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

tb_bursary里关联了tb_student.id作为外键

由于tb_student表可以单独操作，而tb_bursary需要联合tb_student查询

所以一开始，我是用mybatis-plus + mybaits混合的模式

mybatis-plus单独操作tb_student表，mybatis操作tb_bursary.

tb_student部分代码如下:

@Data
@TableName("tb_student")
public class Student {@TableId(value = "id",type = IdType.AUTO)private Integer id;private String sno;private String name;private char gender = '男';private String password;private String email;private String telephone;private String address;private String introducation;private String portraitPath;private String clazzName;}
public interface StudentService extends IService<Student> {Student login(LoginForm loginForm);Student getStudentById(Long userId);List<Student> getAllStudent();IPage<Student> getStudentByOpr(Page<Student> pageParam, Student student);
}
@Repository
public interface StudentMapper extends BaseMapper<Student> {}
@Service("stuService") 
@Transactional 
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {@Overridepublic Student login(LoginForm loginForm) {QueryWrapper<Student> queryWrapper=new QueryWrapper<>();queryWrapper.eq("name",loginForm.getUsername());queryWrapper.eq("password",MD5.encrypt(loginForm.getPassword()));Student student = baseMapper.selectOne(queryWrapper);return student;}
//.....省略其他方法
}

tb_bursary则使用mybatis来联合查询：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Bursary {Integer id;Student students;String bursary;String is_approve;String approver;String approve_datetime;String create_time;String remarks;
}

public interface BursaryMapper {List<Bursary> getAllBursary(String name);void editBursary(Bursary bursary);void addBursary(Bursary bursary);void delBursaryByIds(@Param("ids") List<Integer> ids);void delBursaryById(Integer id);
}
public interface BursaryService {List<Bursary> getAllBursary(String name);void editBursary(Bursary bursary);void addBursary(Bursary bursary);void delBursaryByIds(List<Integer> ids);void delBursaryById(Integer id);
}
@Service
@Transactional
public class BursaryServiceImpl implements BursaryService {InputStream is = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);SqlSession sqlSession = sqlSessionFactory.openSession(true);BursaryMapper mapper = sqlSession.getMapper(BursaryMapper.class);public BursaryServiceImpl() throws IOException {}@Overridepublic List<Bursary> getAllBursary(String name) {if(!name.isEmpty()){name = "%" + name + "%";}List<Bursary> result = mapper.getAllBursary(name);return result;}
//...省略其他方法
}

BursaryMapper.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.example.demo.mapper.BursaryMapper"><resultMap id="bursaryResultMap" type="com.example.demo.pojo.Bursary"><id column="id" property="id"/><result property="bursary" column="bursary" /><result property="is_approve" column="is_approve" /><result property="approver" column="approver" /><result property="approve_datetime" column="approve_datetime" /><result property="create_time" column="create_time" /><result property="remarks" column="remarks" /><collection property="students" ofType="com.example.demo.pojo.Student" ><id column="student_id" property="id"/><result property="sno" column="student_sno"/><result property="name" column="student_name"/></collection></resultMap><select id="getAllBursary" resultMap="bursaryResultMap">select b.id, b.bursary,b.is_approve, b.approver, b.approve_datetime,b.create_time, b.remarks,s.id as student_id, s.sno as student_sno, s.name afrom tb_bursary b left join tb_student s on b.student_id  = s.id<where><if test="name!=null and name!='' ">and s.name like #{name}</if></where></select><update id="editBursary">update tb_bursary set bursary = #{bursary}, is_approve = #{is_approve},<if test="is_approve !=null and is_approve ==1">approver = #{approver}, approve_datetime = now(),</if>remarks = #{remarks} where id = #{id}</update><insert id="addBursary">INSERT INTO tb_bursary(student_id, bursary, is_approve,<if test="is_approve !=null and is_approve ==1">approver, approve_datetime,</if>create_time, remarks)VALUES(#{students.id}, #{bursary}, #{is_approve},<if test="is_approve !=null and is_approve ==1">#{approver}, now(),</if>CURRENT_TIMESTAMP, #{remarks});</insert><delete id="delBursaryById" parameterType="Integer">delete from tb_bursary where id = #{id}</delete><!--批量删除--><delete id="delBursaryByIds" parameterType="Integer">delete from tb_bursary where id in<foreach collection="ids" separator="," item="id" open="(" close=")">#{id}</foreach></delete>
</mapper>

jdbc.properties:

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/zhxy_db?serverTimezone=GMT%2B8
jdbc.username=aaa
jdbc.password=123456

mybatis-config.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><properties resource="jdbc.properties"></properties><typeAliases><package name="com.example.demo.pojo" /></typeAliases><plugins><plugin interceptor="com.github.pagehelper.PageInterceptor"><property name="helperDialect" value="mysql"/><property name="offsetAsPageNum" value="true"/><property name="rowBoundsWithCount" value="true"/><property name="pageSizeZero" value="true"/><property name="reasonable" value="true"/><property name="params" value="pageNum=start;pageSize=limit;"/><property name="supportMethodsArguments" value="true"/><property name="returnPageInfo" value="check"/></plugin></plugins><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><mappers><mapper resource="mapper/BursaryMapper.xml"/></mappers>
</configuration>

项目运行后，发现mybatis和mybatis-plus是可以共存于项目中的，互不干扰。但是tb_bursury使用mybatis需要额外配置config和mapper。于是决定使用MyBatis-plus-join解决联表查询的问题。

Bursary修改如下:

Bursary不能直接用student类了，直接把需要的字段写出来。注意mybatisplus是使用驼峰命名法去映射属性字段，所以如果数据库字段名带有下划线，那么类属性里就要用驼峰命名法，或者使用@TableField直接告诉mybatisplus映射某个字段

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("tb_bursary")
public class Bursary {@TableId(value = "id",type = IdType.AUTO)Integer id;//Student students;@TableField(value = "student_id")Integer student_id;@TableField(value = "student_sno")Integer student_sno;@TableField(value = "student_name")String student_name;String bursary;@TableField(value = "is_approve")String is_approve;String approver;@TableField(value = "approve_datetime")String approve_datetime;@TableField(value = "create_time")String create_time;String remarks;
}
@Repository
public interface BursaryMapper extends MPJBaseMapper<Bursary> {}
public interface BursaryService{List<Bursary> getAllBursary(int currentPage, int pageSize, String name);void editBursary(Bursary bursary);void addBursary(Bursary bursary);void delBursaryByIds(List<Integer> ids);void delBursaryById(Integer id);
}
@Service
@Transactional
public class BursaryServiceImpl implements BursaryService {@Resourceprivate BursaryMapper bursaryMapper;public BursaryServiceImpl() throws IOException {}@Overridepublic List<Bursary> getAllBursary(int currentPage, int pageSize, String name) {MPJLambdaWrapper<Bursary> mpjLambdaWrapper = new MPJLambdaWrapper<>();mpjLambdaWrapper.selectAll(Bursary.class).selectAs(Student::getId, Bursary::getStudent_id).selectAs(Student::getSno, Bursary::getStudent_sno).selectAs(Student::getName, Bursary::getStudent_name).leftJoin(Student.class, Student::getId,Bursary::getStudent_id);if(!name.isEmpty()){mpjLambdaWrapper.like(Student::getName, name);}IPage<Bursary> ipage = bursaryMapper.selectJoinPage(new Page<>(currentPage, pageSize), Bursary.class,mpjLambdaWrapper);return ipage.getRecords();}@Overridepublic void addBursary(Bursary bursary) {try {bursaryMapper.insert(bursary);} catch (Exception e) {throw new RuntimeException(e);}}//...省略其他方法
}

修改完毕，运行，报错:

Ambiguous collection type for property 'students'. You must specify 'javaType' or 'resultMap'

经过检查，发现是因为mybatis-plus-join和mybatis不能共存，一旦使用plus-join插件，就必须删除BursaryMapper.xml和mybatis-config.xml

再次运行，错误没有了，但是又报错了:

java.lang.NoSuchMethodError: com.baomidou.mybatisplus.core.metadata.TableInfo.havePK()Z

再仔细检查，发现是我用了selectAll(Bursary.class)去检索bursary的所有字段，但是下面又用selectAs(Student::getId, Bursary::getStudent_id)去检索student的三个字段，这里很明显重复了。

于是修改为：

@Overridepublic List<Bursary> getAllBursary(int currentPage, int pageSize, String name) {MPJLambdaWrapper<Bursary> mpjLambdaWrapper = new MPJLambdaWrapper<>();//mpjLambdaWrapper.selectAll(Bursary.class)mpjLambdaWrapper.select(Bursary::getId).select(Bursary::getBursary).select(Bursary::getIs_approve).select(Bursary::getApprover).select(Bursary::getApprove_datetime).select(Bursary::getCreate_time).select(Bursary::getRemarks).selectAs(Student::getId, Bursary::getStudent_id).selectAs(Student::getSno, Bursary::getStudent_sno).selectAs(Student::getName, Bursary::getStudent_name).leftJoin(Student.class, Student::getId,Bursary::getStudent_id);if(!name.isEmpty()){mpjLambdaWrapper.like(Student::getName, name);}IPage<Bursary> ipage = bursaryMapper.selectJoinPage(new Page<>(currentPage, pageSize), Bursary.class,mpjLambdaWrapper);return ipage.getRecords();}

成功:

生成的SQL:

 SELECT t.id,t.bursary,t.is_approve,t.approver,t.approve_datetime,t.create_time,t.remarks,t1.id AS student_id,t1.sno AS student_sno,t1.name AS student_name FROM tb_bursary t LEFT JOIN tb_student t1 ON t1.id = t.student_id

但是我在使用mybatis-plus-join时遇到一些奇怪的问题，不知道是不是bug:

问题1: Bursary类无法使用@TableField指定字段与属性的映射

必须使用驼峰命名法，否则属性名带下划线的全部结果为null，只能照如下命名:

    @TableId(value = "id",type = IdType.AUTO)Integer id;Integer StudentId;Integer StudentSno;String StudentName;String bursary;String IsApprove;String approver;String ApproveDatetime;String CreateTime;String remarks;

其实这严格来说也不算问题，因为推荐就用驼峰命名法

问题2，生成SQL有误。这个问题要老命，一直没有找到原因。

用Unit Test调试service时一切正常，但是用controller去调用service就报错:

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 't.sno' in 'field list'

这个错误是什么导致的呢？sno是tb_student表的字段，不是tb_bursary的

mybatis-plus-join生成的SQL，会给tb_bursary加别名t，tb_student加别名t1。也就是说，正确的SQL应该是t1.sno，而不是t.sno。

然后我又对比了下Unit Test生成的SQL，是正确的。也就是说一旦我使用controller去调用service，生成的SQL就错误了！！

完全找不到解决方法，网络上也没有搜索到类似的问题。

不得已只能放弃mybatis-plus-join，那么不用mybatis-plus-join，能不能使用多表联合查询呢？

可以，就是用@Select，如下：

(注意@TableName里要添加autoResultMap = true，否则student_id会等于null)

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "tb_bursary", autoResultMap = true)
public class Bursary {@TableId(value = "id",type = IdType.AUTO)Integer id;//Student students;@TableField(value = "student_id")Integer student_id;@TableField(value = "student_sno")Integer student_sno;@TableField(value = "student_name")String student_name;String bursary;@TableField(value = "is_approve")String is_approve;String approver;@TableField(value = "approve_datetime")String approve_datetime;@TableField(value = "create_time")String create_time;String remarks;
}
@Repository
public interface BursaryMapper extends BaseMapper<Bursary> {@ResultMap("mybatis-plus_Bursary")@Select("SELECT t.id," +"t.bursary," +"t.is_approve," +"t.approver," +"t.approve_datetime," +"t.create_time," +"t.remarks," +"t1.id AS student_id," +"t1.sno AS student_sno," +"t1.name AS student_name " +"FROM tb_bursary t " +"LEFT JOIN tb_student t1 ON t1.id = t.student_id " + "${ew.customSqlSegment}")IPage<Bursary> getAllBursary(IPage<Bursary> page, @Param("ew") Wrapper wrapper);
}
public interface BursaryService{IPage<Bursary> getAllBursary(int currentPage, int pageSize, String name);
}

@Service
@Transactional
public class BursaryServiceImpl extends ServiceImpl<BursaryMapper, Bursary> implements BursaryService {@Overridepublic IPage<Bursary> getAllBursary(int currentPage, int pageSize, String name) {QueryWrapper<Bursary> queryWrapper = new QueryWrapper<>();queryWrapper.like(StringUtils.hasText(name), "t1.name", name);return bursaryMapper.getAllBursary(new Page<>(currentPage, pageSize), queryWrapper);}
}

以上的代码就可以实现啦。

但是，又一个但是，这样会让方法带SQL脚本，也会增加以后的维护成本和时间。

我还有另外一种笨方法。

首先，Busary类同上不需要修改。

然后我们查看我们想要实现的SQL：

SELECT t.id,
t.bursary,
t.is_approve,
t.approver,
t.approve_datetime,
t.create_time,
t.remarks,
t1.id AS student_id,
t1.sno AS student_sno,
t1.name AS student_name 
FROM tb_bursary t 
LEFT JOIN tb_student t1 
ON t1.id = t.student_id
Where t1.name like '%%'

tb_bursary通过student_id去join了tb_student表，又根据student name进行了一个条件模糊查询。

所以我们首先查找匹配模糊查询的student数据

StudentService添加新的方法，能通过模糊查询得到student数据:

public interface StudentService extends IService<Student> {List<Student> getStudentByName(String name);
}@Service("stuService") 
@Transactional 
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {@Overridepublic List<Student> getStudentByName(String name) {QueryWrapper<Student> queryWrapper = new QueryWrapper<Student>();queryWrapper.like(StringUtils.hasText(name),"name", name);return baseMapper.selectList(queryWrapper);}}

 然后再通过得到的student做条件，查询bursary，最后填充bursary的student_name，student_sno:

@Service
@Transactional
public class BursaryServiceImpl extends ServiceImpl<BursaryMapper, Bursary> implements BursaryService {@Overridepublic List<Bursary> getBursaryByStudentName(int currentPage, int pageSize, String name) {//先根据student name获取对应的student 数据List<Student> studentList = studentService.getStudentByName(name);List<Integer> StuIdList = new ArrayList<>();//提取student idstudentList.forEach(l->StuIdList.add(l.getId()));QueryWrapper<Bursary> queryWrapper = new QueryWrapper<>();//根据student id 去查询bursary数据queryWrapper.in("student_id", StuIdList);List<Bursary> bursaryList = baseMapper.selectPage(new Page<>(currentPage, pageSize),queryWrapper).getRecords();//填充 bursary的student_sno, student_namebursaryList.forEach(b->{studentList.forEach(s->{if(b.getStudent_id().equals(s.getId())){b.setStudent_name(s.getName());b.setStudent_sno(Integer.valueOf(s.getSno()));}});});return bursaryList;}
}

这种方法，哎....一言难尽。

结论：

Mybatis是可以和Mybatis-plus共存于项目的，但是不推荐，因为代码整体风格会不统一。

使用了Mybatis-plus-join则不能同时用Mybatis,需要删除所有Mybatis的配置。

使用Mybatis-plus也可以多表联合查询，方法还挺多，各有优缺点，看自己选择。

如果能解决Mybatis-plus-join使用中遇到的问题，个人还是推荐使用Mybatis-plus-join

记录一下，便于以后查阅。


http://www.ppmy.cn/devtools/28786.html

相关文章

Java高阶私房菜：JVM分代收集算法介绍和各垃圾收集器原理分解

Java高阶私房菜：JVM分代收集算法介绍和各垃圾收集器原理分解

目录 什么是分代收集算法 GC的分类和专业术语 什么是垃圾收集器 垃圾收集器的分类及组合 ​编辑 应关注的核心指标 Serial和ParNew收集器原理 Serial收集器 ParNew收集器 Parallel和CMS收集器原理 Parallel 收集器 CMS收集器 新一代垃圾收集器G1和ZGC G1垃圾收集器…
阅读更多...
On evaluating adversarial robustness of large vision language models - 论文翻译

On evaluating adversarial robustness of large vision language models - 论文翻译

论文链接&#xff1a;https://arxiv.org/pdf/2305.16934 项目代码&#xff1a;https://github.com/yunqing-me/AttackVLM On evaluating adversarial robustness of large vision language models Abstract1 Introduction2 Related work3 Methodology3.1 Preliminary3.2 Transf…
阅读更多...
k8s如何写yaml文件

k8s如何写yaml文件

k8s&&如何写yaml文件 metadataspecresources: 如何设置request和limit&#xff1f; status metadata metadata: 通常用于填写一些对象&#xff08;如pod、deployment等&#xff09;的描述信息&#xff08;类似人的名字、年龄等&#xff09;。 spec spec: 通常代表着…
阅读更多...
AI大模型探索之路-实战篇2：基于CVP架构-企业级知识库实战落地

AI大模型探索之路-实战篇2：基于CVP架构-企业级知识库实战落地

目录 前言 一、概述 二、本地知识库需求分析 1. 知识库场景分析 2. 知识库应用特点 3. 知识库核心功能 三、本地知识库架构设计 1. RAG架构分析 2. 大模型方案选型 3. 应用技术架构选型 4. 向量数据库选型 5. 模型选型 三、本地知识库RAG评估 四、本地知识库代码落地 1. 文件…
阅读更多...
LNMP部署及应用（Linux+Nginx+MySQL+PHP)

LNMP部署及应用（Linux+Nginx+MySQL+PHP)

LNMP 我们为什么采用LNMP这种架构? 采用Linux、PHP、MySQL的优点我们不必多说。 Nginx是一个小巧而高效的Linux下的Web服务器软件&#xff0c;是由 Igor Sysoev 为俄罗斯访问量第二的 Rambler.ru 站点开发的&#xff0c;已经在一些俄罗斯的大型网站上运行多年&#xff0c;目…
阅读更多...
XML：简介

XML：简介

一、何为XML XML 指可扩展标记语言&#xff08;EXtensible Markup Language&#xff09;&#xff0c;设计宗旨是传输数据&#xff0c;而非显示数据&#xff0c;其是W3C&#xff08;指万维网联盟&#xff08;World Wide Web Consortium&#xff09;&#xff09;的推荐标准。 实例…
阅读更多...
10G MAC层设计系列-（2）MAC RX模块

10G MAC层设计系列-（2）MAC RX模块

一、概述 MAC RX模块的需要进行解码、对齐、CRC校验。 因为在空闲的时候10G PCS/PMA会一直向外吐空闲符&#xff08;x07&#xff09;所以需要根据开始符、结束符将有效数据从码流中截取&#xff0c;也就是解码。 因为开始字符的所在位置有两种形式&#xff0c;而结束字符的位…
阅读更多...
亲子公园实景剧本杀小程序系统开发

亲子公园实景剧本杀小程序系统开发

亲子公园实景剧本杀小程序系统开发涉及到多个方面的内容&#xff0c;具体步骤如下&#xff1a; 1. 系统需求分析&#xff1a;了解客户的需求和期望&#xff0c;明确开发目标和功能需求。 2. 系统架构设计&#xff1a;根据需求分析结果&#xff0c;设计系统的整体架构&#xf…
阅读更多...
最新文章

Copyright @ 2022~2023