数据库里有两张表
tb_bursary和tb_student
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 {
@Override
public 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 {
}
@Override
public 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 mapper
PUBLIC "-//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 a
from 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=123456mybatis-config.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>
<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类了，直接把需要的字段写出来
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("tb_bursary")
public class Bursary {
@TableId(value = "id",type = IdType.AUTO)
Integer id;
//Student students; //这里不能直接用Student类了
Integer student_id;
Integer student_sno;
String student_name;
String bursary;
String is_approve;
String approver;
String approve_datetime;
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 {
@Resource
private BursaryMapper bursaryMapper;
public BursaryServiceImpl() throws IOException {
}
@Override
public 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();
}
@Override
public 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的三个字段，这里很明显重复了。
于是修改为：
@Override
public 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是可以和Mybatis-plus共存于项目的，但是不推荐，因为代码整体风格会不统一。
使用了Mybatis-plus-join则不能同时用Mybatis,需要删除所有Mybatis的配置。
记录一下，便于以后查阅。
