通用Mapper进行多表查询
通用Mapper大大简化了Mybatis的单表CRUD操作。今天我测试了一下用Mapper进行一对一两表操作,将过程进行一下整理。水平有限希望批评指正。
使用环境:
- OS:win7 X64
- IED:idea 2017.2.6
- JAVA:1.9
- MAVEN:maven 3
- mysql:5.5.27
- 框架:SpringBoot
具体步骤如下:
-
数据库准备:
在test库中建立学生表和班级表,两个表靠班级ID连接,一个学生对应一个班级,所以最终实现的是一对一查询。sql语句如下:
学生表:
CREATE TABLE students (
sid int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
number int(11) DEFAULT NULL,
gender varchar(255) DEFAULT NULL,
cid int(11) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
班级表:
CREATE TABLE class (
cid int(11) NOT NULL AUTO_INCREMENT,
classname varchar(255) DEFAULT NULL,
monitor_sid int(11) DEFAULT NULL,
masterteacher_id int(11) DEFAULT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -
用idea新建一个maven工程,不使用框架
-
pom.xml文件内容如下:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.lin</groupId>
<artifactId>demo58springCloud02</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.2.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.0</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.0.2</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.3.7.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<properties>
<java.version>1.9</java.version>
</properties>
</project>
其中:
- mapper-spring-boot-starter是通用Mapper
- lombok用来简化Java Bean操作
- druid是阿里巴巴开发的连接池工具
- 创建Student和Classes的java bean,由于查找结果需要在学生表中包含班级信息,所以Student类中要有Classes属性。源码如下:
student类:
package lin.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import tk.mybatis.mapper.annotation.KeySql;
import javax.persistence.Id;
import javax.persistence.Table;
@Data // 自动创建getter/setter/toString等方法
@AllArgsConstructor // 自动创建全参构造函数
@Table(name = "students") // 定义应对应的数据库表名
public class Student {
@Id // 定义主键
@KeySql(useGeneratedKeys = true) // 自增长
Integer sid;
String name;
Integer number;
String gender;
Integer cid;
Classes classes;
// 创建一个不包含Classes类的构造函数
public Student(Integer sid, String name, Integer number, String gender, Integer cid) {
this.sid = sid;
this.name = name;
this.number = number;
this.gender = gender;
this.cid = cid;
}
}
classes类:
package lin.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import tk.mybatis.mapper.annotation.KeySql;
import javax.persistence.Id;
import javax.persistence.Table;
@Data
@AllArgsConstructor
@Table(name="class")
public class Classes {
@Id
@KeySql(useGeneratedKeys = true)
Integer cid;
String classname;
Integer monitor_sid;
Integer masterteacher_id;
}
- 创建持久层的Mapper接口
每个表要对应一个类,所以需要有班级和学成两个Mapper接口。
由于通用Mapper插件提供了所有的单表操作方法,所以不用单独编写单表方法,只需要在Mapper接口继承通用Mapper。
Class表是从表,所以建立ClassesMapper仅需要单表操作,源码如下:
package lin.mapper;
import lin.pojo.Classes;
public interface ClassesMapper extends tk.mybatis.mapper.common.Mapper<Classes>{
}
Student表是主表,就需要单独编写一对多双表查询的方法,我写了两个方法,一个是查询所有的学生信息包括所在班级,一个是按照学生ID查询一个学生信息包括所在班级。源码如下:
package lin.mapper;
import lin.pojo.Student;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface StuMapper extends tk.mybatis.mapper.common.Mapper<Student> {
@Select("select * from students")
@Results(id = "AllStuWithClass", value = {
@Result(id = true, property = "sid", column = "sid"),
@Result(property = "name", column = "name"),
@Result(property = "number", column = "number"),
@Result(property = "gender", column = "gender"),
@Result(property = "classes", column = "cid",
one = @One(select = "lin.mapper.ClassesMapper.selectByPrimaryKey"))
})
public List<Student> selectAllStuWithClass();
@Select("select * from students where sid = #{sid}")
@Results(id = "StuWithClassBySId", value = {
@Result(id = true, property = "sid", column = "sid"),
@Result(property = "name", column = "name"),
@Result(property = "number", column = "number"),
@Result(property = "gender", column = "gender"),
@Result(property = "classes", column = "cid",
one = @One(select = "lin.mapper.ClassesMapper.selectByPrimaryKey"))
})
public List<Student> selectStuWithClassBySId(Integer sid);
}
可以看到和mybatis的写法一样,只是将自己写的单表操作方法替换通用Mapper的,这里是“lin.mapper.ClassesMapper.selectByPrimaryKey”
- 创建application启动类和配置文件application.yml,注意的是如果用通用Mapper,
@MapperScan
注解不能再import mybatis,而是要import tk.mybatis.spring.annotation.MapperScan
Application.java源码如下:
package lin;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan("lin.mapper")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class);
}
}
- 业务层、表现层的内容没有什么特别的,在这里就不展示了。项目文件结构图如下:
有时间我再写一个多对多的内容。
第一次在CSDN写博客,希望大家多多指正。谢谢!
本文地址:https://blog.csdn.net/golin_malta/article/details/107659889