通⽤Mapper进⾏多表查询
通⽤Mapper⼤⼤简化了Mybatis的单表CRUD操作。今天我测试了⼀下⽤Mapper进⾏⼀对⼀操作，将过程进⾏⼀下整理。⽔平有限希望批评指正。
使⽤环境：
OS：win7 X64
IED：idea 2017.2.6
JAVA：1.9
MAVEN：maven 3
mysql：5.5.27
框架：SpringBoot
具体步骤如下：
0. 数据库准备：
在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;
1. ⽤idea新建⼀个maven⼯程，不使⽤脚⼿架
2. l⽂件内容如下：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="/POM/4.0.0"
xsi="/2001/XMLSchema-instance"
schemaLocation="/POM/4.0.0 /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>
安卓系统c语言编程软件>抹茶粉可以做什么美食<artifactId>lombok</artifactId>
<version>1.18.0</version>
</dependency>
<dependency>
<groupId&batis</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是阿⾥巴巴开发的连接池⼯具
3. 创建Student和Classes的java bean，由于查结果需要在学⽣表中包含班级信息，所以Student类中要有Classes属性。源码如
下：
student类：
package lin.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
batis.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.cid = cid;
}
}
classes类：
package lin.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
batis.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;
}
4. 创建持久层的Mapper接⼝
每个表要对应⼀个类，所以需要有班级和学成两个Mapper接⼝。
由于通⽤Mapper插件提供了所有的单表操作⽅法，所以不⽤单独编写单表⽅法，只需要在Mapper接⼝继承通⽤Mapper。
Class表是从表，所以建⽴ClassesMapper仅需要单表操作，源码如下：
package lin.mapper;
import lin.pojo.Classes;
public interface ClassesMapper batis.mappermon.Mapper<Classes>{
虚拟交易php网站源码}
Student表是主表，就需要单独编写⼀对多双表查询的⽅法，我写了两个⽅法，⼀个是查询所有的学⽣信息包括所在班级，⼀个是按照学⽣ID查询⼀个学⽣信息包括所在班级。源码如下：
package lin.mapper;
import lin.pojo.Student;
import org.apache.ibatis.annotations.One;
distinction读博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 batis.mappermon.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”
5. 创建application启动类和配置⽂件l，注意的是如果⽤通⽤Mapper，@MapperScan注解不能再import mybatis，⽽
是要 batis.spring.annotation.MapperScan
Application.java源码如下：
package lin;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
batis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan("lin.mapper")
mysql语句多表查询public class Application {
public static void main(String[] args){
SpringApplication.run(Application.class);
}
}
6. 业务层、表现层的内容没有什么特别的，在这⾥就不展⽰了。项⽬⽂件结构图如下：
有时间我再写⼀个多对多的内容。
第⼀次在CSDN写博客，希望⼤家多多指正。谢谢！