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

数据库里有两张表

tb_bursary和tb_student

--tb_bursary

CREATE 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_student

CREATE 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 {
    @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=123456

mybatis-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类了，直接把需要的字段写出来。注意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 {

    @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-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 {
    @Override
    public 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 {
    @Override
    public 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 {

    @Override
    public List<Bursary> getBursaryByStudentName(int currentPage, int pageSize, String name) {
        //先根据student name获取对应的student 数据
        List<Student> studentList = studentService.getStudentByName(name);
        List<Integer> StuIdList = new ArrayList<>();
        //提取student id
        studentList.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_name
        bursaryList.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

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