本文主要是介绍Java研学-MyBatis加强(二)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
一 关系
1 应用
数据是存在关系的，将有关系的数据通过 MyBatis 持久化到数据库，且存储的数据也能表示出来这种关系，再由数据库中把有关系的数据查询出来在页面展示。
保存：页面数据 —> Java 对象封装 —> MyBatis —> 数据库表数据
查询：数据库表数据 —> MyBatis —> 封装成 Java 对象 —> 页面展示数据
使用数据库表设计来表示数据之间关系；使用 Java 类设计来表示对象之间关系；通过 MyBatis 配置来映射上面两者。
2 对象关系分类
关联关系 聚合关系 组合关系
泛化关系 实现关系 依赖关系
3 关联关系分类
A 对象依赖 B 对象，并把 B 作为 A 的一个成员变量，则 A 和 B 存在关联关系。在 UML 中依赖通常使用实线箭头表示。
① 按导航分
若通过 A 对象中的某一个属性可以访问到 B 对象，则说 A 可以导航到 B。
单向：只能从 A 通过属性导航到 B，B 不能导航到 A。
双向：A 可以通过属性导航到 B，B 也可以通过属性导航到 A。
② 按多重性分
一对一：一个人对一个身份证号
一对多：一个部门对多个员工
多对一：多个员工对一个部门
多对多：多个班级对多个老师
4 判断对象关系
判断关系必须确定具体需求，根据对象的属性，从对象的实例上进行判断
5 注释
① useGeneratedKeys
useGeneratedKeys=“true” 和 keyProperty=“id” 是 MyBatis 的映射文件中用于处理自动插入 ID 的配置选项。
useGeneratedKeys=“true”（默认为false）: 让MyBatis 启用 JDBC 的 getGeneratedKeys 方法获取数据库自动生成的键值。通常，这个键值是主键，例如 MySQL 的 AUTO_INCREMENT 字段。
keyProperty=“id”: 这个设置指定了将自动生成的键值设置到哪个 Java 对象的属性中。在这它是 id，所以 MyBatis 会将自动生成的键值设置到 Java 对象的 id 属性中。
这两个设置通常一起使用，以便在插入新记录后自动填充 Java 对象的 ID 属性。调用这个插入方法并传入一个对象时，MyBatis 会将这个对象插入到数据库中，然后获取自动生成的 ID 值，并将其设置到这个对象的 id 属性中。
6 项目准备
pom
<?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>cn.tj</groupId><artifactId>play</artifactId><version>1.0-SNAPSHOT</version><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><maven.compiler.source>1.7</maven.compiler.source><maven.compiler.target>1.7</maven.compiler.target></properties><dependencies><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.4.5</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.45</version><scope>runtime</scope></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope><!-- 测试阶段才用 --></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.22</version><scope>provided</scope><!-- 编译阶段和测试阶段才使用 --></dependency></dependencies>
</project>
db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///play_web
jdbc.username=root
jdbc.password=root
log4j.properties
log4j.rootLogger=error, stdout
log4j.logger.cn.tj=TRACE
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
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="db.properties"></properties><typeAliases><package name="cn.tj.domain"/></typeAliases><environments default="dev"><environment id="dev"><transactionManager type="JDBC"></transactionManager><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目录扫描：mapper接口和xml文件必须目录结构一致--><package name="cn.tj.mapper"/></mappers>
</configuration>
二 单向多对一保存
1 数据库表
建立一个部门和两个员工，这两个员工属于这个部门
// 部门表
CREATE TABLE `department` (`id` bigint(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;// 员工表
CREATE TABLE `employee` (`id` bigint(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,`salary` double(10,2) DEFAULT NULL,`dept_id` bigint(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
2 实体类
部门实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Department {private Long id;private String name;
}
员工实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {private Long id;private String name;private Double salary;//设置部门属性private Department department;
}
3 Mapper 接口
部门接口
public interface DepartmentMapper {/*增加部门*/public int insertDept(Department department);/*根据id查询*/public Department queryById(Long id);
}
员工接口
public interface EmployeeMapper {/*增加员工*/int insertEmp(Employee employee);/*根据id查询*/Employee queryById(Long id);/*根据员工id关联查询*/Employee selectById(Long id);
}
4 Mapper XML
部门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="cn.tj.mapper.DepartmentMapper"><!--增加部门useGeneratedKeys="true" keyProperty="id" 获取增加数据的自增主键值--><insert id="insertDept" useGeneratedKeys="true" keyProperty="id">insert into department (name)values(#{name})</insert><select id="queryById" resultType="department">SELECT * from department WHERE id=#{id}</select>
</mapper>
员工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="cn.tj.mapper.EmployeeMapper">
<!--增加员工--><insert id="insertEmp">insert into employee (name,salary,dept_id)values(#{name},#{salary},#{department.id})</insert><!--自定义返回结果，常用在多表关联查询返回结果集--><!--resultMap标签可封装自定义的结果集，给多个查询语句使用type查询的返回结果对应的javaBean的全类名获别名--><resultMap id="empMap" type="employee"><!--配置javaBean属性和数据库表字段的映射关系:column是数据库字段，property是实体类属性id配置主键字段关联关系 result配置非主键关联--><id column="id" property="id"></id><result column="name" property="name"></result><result column="salary" property="salary"></result><result column="dept_id" property="department.id"></result></resultMap><!--根据员工id查询--><select id="queryById" resultMap="resMap">SELECT * from employee WHERE id=#{id}</select><!--通过框架参数来设置发送sql--><resultMap id="resMap" type="employee"><!--配置javaBean属性和数据库表字段的映射关系:id配置主键字段关联关系 result配置非主键关联--><id column="id" property="id"></id><result column="name" property="name"></result><!--配置多对一关系中的一的一方:property多方实体类中设置的一方的属性名称column执行多方sql需要传递给下一条sql的字段名(id)select需要调用的一方的sql执行的方法(namespace+id)javaType一方实体类全类名或别名--><association property="department" column="dept_id"select="cn.tj.mapper.DepartmentMapper.queryById"javaType="department" ></association></resultMap><resultMap id="baseMap" type="employee"><!--配置多方实体类属性参数--><id column="id" property="id"></id><result column="name" property="name"></result><result column="salary" property="salary"></result><!--配置一方的实体类属性参数--><association property="department" javaType="department"><id column="did" property="id"></id><result property="name" column="dname"></result></association></resultMap><!--多表关联查询员工部门信息--><select id="selectById" resultMap="baseMap">SELECT e.id,e.`name`,e.salary salary,d.id did,d.`name` dnamefrom employee e,department dWHERE e.dept_id=d.id and e.id=#{id};</select>
</mapper>
5 测试类
public class EmployeeMapperTest {// 保存两个同部门下的员工@Testpublic void emp_save(){//1.创建sqlsessionSqlSession sqlSession = MybatisUtil.getSqlSession();//2.创建mapper对象EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);//3.增加一个部门Department dept=new Department();dept.setName("安保部");departmentMapper.insertDept(dept);//4.新增的部门下面增加两个员工Employee emp1 =new Employee();emp1.setName("石昊");emp1.setSalary(new Double(8848));emp1.setDepartment(dept);//建立员工和部门的关系Employee emp2 =new Employee();emp2.setName("叶凡");emp2.setSalary(new Double(8848));emp2.setDepartment(dept);employeeMapper.insertEmp(emp1);employeeMapper.insertEmp(emp2);}
}
三 单向多对一的额外 SQL 查询
1 介绍
额外sql的本质就是分步sql，通过前一个的结果集进行下一条语句的查询
2 Mapper 接口
员工
Employee queryById(Long id);
部门
Department queryById(Long id);
3 Mapper XML
员工
<!--自定义返回结果，常用在多表关联查询返回结果集-->
<!--resultMap标签可封装自定义的结果集，给多个查询语句使用type查询的返回结果对应的javaBean的全类名获别名
-->
<resultMap id="empMap" type="employee"><!--配置javaBean属性和数据库表字段的映射关系:column是数据库字段，property是实体类属性id配置主键字段关联关系 result配置非主键关联--><id column="id" property="id"></id><result column="name" property="name"></result><result column="salary" property="salary"></result><result column="dept_id" property="department.id"></result>
</resultMap>
<!--根据员工id查询-->
<select id="queryById" resultMap="empMap">SELECT * from employee WHERE id=#{id}
</select>
部门
<select id="queryById" resultType="department">SELECT * from department WHERE id=#{id}
</select>
4 测试类
// 额外sql之手动发送sql:查询15号员工所在部门@Testpublic void emp_queryById() {SqlSession sqlSession=MybatisUtil.getSqlSession();EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);//根据员工id查询Employee employee = employeeMapper.queryById(15l);//根据员工所在 的部门id查部门Department department = departmentMapper.queryById(employee.getDepartment().getId());System.out.println(employee+department.getName());}
5 association 发送额外 SQL
① 员工 Mapper XML
<select id="queryById" resultMap="resMap">SELECT * from employee WHERE id=#{id}</select><!--通过框架参数来设置发送sql--><resultMap id="resMap" type="employee"><!--配置javaBean属性和数据库表字段的映射关系:id配置主键字段关联关系 result配置非主键关联--><id column="id" property="id"></id><result column="name" property="name"></result><!--配置多对一关系中的一的一方:property多方实体类中设置的一方的属性名称column执行多方sql需要传递给下一条sql的字段名(id)select需要调用的一方的sql执行的方法(namespace+id)javaType一方实体类全类名或别名--><association property="department" column="dept_id"select="cn.tj.mapper.DepartmentMapper.queryById"javaType="department" ></association></resultMap>
② 员工 Mapper 接口
Employee queryById(Long id);
③ 测试类
@Testpublic void emp_queryByEmpId() {SqlSession sqlSession=MybatisUtil.getSqlSession();EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);//根据员工id查询Employee employee = employeeMapper.queryById(15l);System.out.println(employee);}
四 单向多对一的关联查询
1 员工 mapper 接口
Employee selectById(Long id);
2 员工 mapper xml
<resultMap id="baseMap" type="employee"><!--配置多方实体类属性参数--><id column="id" property="id"></id><result column="name" property="name"></result><result column="salary" property="salary"></result><!--配置一方的实体类属性参数，column可用别名--><association property="department" javaType="department"><id column="did" property="id"></id><result property="name" column="dname"></result></association></resultMap><!--多表关联查询员工部门信息--><select id="selectById" resultMap="baseMap">SELECT e.id,e.`name`,e.salary salary,d.id did,d.`name` dnamefrom employee e,department dWHERE e.dept_id=d.id and e.id=#{id};</select>
3 测试类
@Testpublic void emp_selectByEmpId() {SqlSession sqlSession=MybatisUtil.getSqlSession();EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);//根据员工id查询Employee employee = employeeMapper.selectById(15l);System.out.println(employee);}
五 单向一对多的保存与查询
1 数据库表
无变化
2 实体类
员工
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {private Long id;private String name;private Double salary;// 封装员工的部门id 非关联属性private Long dept_id;
}
部门 – 一对多时在一方设置多方属性
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Department {private Long id;private String name;/*设置多方的属性*/private List<Employee> employeeList=new ArrayList<>();
}
3 Mapper 接口
员工
public interface EmployeeMapper {/*增加员工*/int insertEmp(Employee employee);/*根据部门id查询员工*/List<Employee> queryById(Long id);
}
部门
public interface DepartmentMapper {/*增加部门*/public int insertDept(Department department);/*根据id查询*/public Department queryById(Long id);
}
4 Mapper 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="cn.tj.mapper.EmployeeMapper"><!--增加员工--><insert id="insertEmp">insert into employee (name,salary,dept_id)values(#{name},#{salary},#{dept_id})</insert><!--根据部门id查询员工--><select id="queryById" resultType="employee">SELECT * from employee WHERE dept_id=#{id}</select>
</mapper>
部门
<?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="cn.tj.mapper.DepartmentMapper"><!--增加部门useGeneratedKeys="true" keyProperty="id" 获取增加数据的自增主键值--><insert id="insertDept" useGeneratedKeys="true" keyProperty="id">insert into department (name)values(#{name})</insert><!--部门查询的自定义结果集对象--><resultMap id="deptMap" type="department"><!--一方实体类参数配置 ofType多方属性对应的实体类全类名或别名--><id column="id" property="id"></id><result column="name" property="name"></result><collection property="employeeList" select="cn.tj.mapper.EmployeeMapper.queryById"column="id" ofType="employee"></collection></resultMap><!--根据id查询--><select id="queryById" resultMap="deptMap">SELECT * from department WHERE id=#{id}</select>
</mapper>
5 测试类
员工
public class EmployeeMapperTest {// 保存一个部门和两个员工，且这两个员工都是这个部门的@Testpublic void emp_save(){//1.创建sqlsessionSqlSession sqlSession = MybatisUtil.getSqlSession();//2.创建mapper对象EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);DepartmentMapper departmentMapper = sqlSession.getMapper(DepartmentMapper.class);//3.增加一个部门Department department=new Department();department.setName("实施部");departmentMapper.insertDept(department);//4.新增的部门下面增加两个员工Employee emp1=new Employee();emp1.setName("利威尔");emp1.setSalary(new Double(8848));//设置部门关联idemp1.setDept_id(department.getId());employeeMapper.insertEmp(emp1);Employee emp2=new Employee();emp2.setName("艾伦");emp2.setSalary(new Double(8848));//设置部门关联idemp2.setDept_id(department.getId());employeeMapper.insertEmp(emp2);}
}
部门
public class DepartmentMapperTest {// 根据 id 查询部门，并把其部门的员工信息也查询出来。@Testpublic void dept_list() {SqlSession sqlSession = MybatisUtil.getSqlSession();DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);Department department = mapper.queryById(805l);System.out.println(department);}
}
六 单向多对多的保存
1 数据库表
//学生表
CREATE TABLE `student` (`id` bigint(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
//老师表
CREATE TABLE `teacher` (`id` bigint(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
//中间表
CREATE TABLE `student_teacher` (`student_id` bigint(20) DEFAULT NULL,`teacher_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2 实体类
// 学生
@Data
public class Student {private Long id;private String name;/*设置老师属性*/private List<Teacher> teacherList=new ArrayList<>();
}
// 老师
@Data
public class Teacher {private Long id;private String name;
}
3 Mapper 接口
// 学生
public interface StudentMapper {/*增加学生*/public int insertStudent(Student student);/*增加中间表数据*/public int insertRelation(@Param("sid")Long sid,@Param("tid")Long tid);
}// 老师
public interface TeacherMapper {/*增加老师*/int insertTeacher(Teacher teacher);
}
4 Mapper 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="cn.tj.mapper.StudentMapper"><!--增加学生--><insert id="insertStudent" useGeneratedKeys="true" keyProperty="id">insert into student(name) values(#{name})</insert><!--增加中间表数据--><insert id="insertRelation">insert into student_teacher (student_id,teacher_id)values(#{sid},#{tid})</insert>
</mapper>
// 老师
<?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="cn.tj.mapper.TeacherMapper"><!--增加老师--><insert id="insertTeacher" useGeneratedKeys="true" keyProperty="id">insert into teacher(name) VALUES(#{name})</insert>
</mapper>
5 测试类
public class StudentMapperTest {//保存两个学生和两个老师，且这两个老师都教了这个两个学生@Testpublic void student_save() {SqlSession sqlSession = MybatisUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);//1.保存学生Student student1=new Student();student1.setName("陈汉生");studentMapper.insertStudent(student1);Student student2=new Student();student2.setName("穿山甲");studentMapper.insertStudent(student2);//2.保存老师Teacher teacher1=new Teacher();teacher1.setName("沈幼楚");teacherMapper.insertTeacher(teacher1);Teacher teacher2=new Teacher();teacher2.setName("慕容鱼");teacherMapper.insertTeacher(teacher2);//3.保存中间表数据studentMapper.insertRelation(student1.getId(),teacher1.getId());studentMapper.insertRelation(student1.getId(),teacher2.getId());studentMapper.insertRelation(student2.getId(),teacher1.getId());studentMapper.insertRelation(student2.getId(),teacher2.getId());}
}
七 单向多对多的额外 SQL 查询
1 mapper 接口
// 学生
/*根据id查询学生*/
public Student queryById(Long id);// 老师
/*根据学生id查询老师信息*/
List<Teacher> queryBySid(Long sid);
2 mapper xml
<!--学生--><resultMap id="studentMap" type="student"><id column="id" property="id"></id><result column="name" property="name"></result><collection property="teacherList" column="id"select="cn.tj.mapper.TeacherMapper.queryBySid"ofType="teacher"></collection></resultMap><!--根据id查询--><select id="queryById" resultMap="studentMap">SELECT * from student WHERE id =#{id}</select><!--老师--><!--查询学生关联的老师--><select id="queryBySid" resultType="Teacher">SELECT * from teacher t,student_teacher stWHERE t.id=st.teacher_id and st.student_id=#{sid}</select>
3 测试类
// 根据 id 查询学生，并查询教过他的老师@Testpublic void student_query() {SqlSession sqlSession = MybatisUtil.getSqlSession();StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);Student student = mapper.queryById(2l);System.out.println(student);}
八 单向多对多的删除
先删中间表(删除学生)
1 mapper 接口
/*根据id删除*/public int deleteById(Long id);/*根据学生id删除中间表数据*/public int deleteBySid(Long sid);
2 mapper xml
<!--根据id删除--><delete id="deleteById">DELETE from student WHERE id=#{id}</delete><!--根据学生id删除中间表数据--><delete id="deleteBySid">DELETE from student_teacher WHERE student_id=#{sid}</delete>
3 测试类
/*根据id删除学生*/@Testpublic void student_delete() {SqlSession sqlSession = MybatisUtil.getSqlSession();StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);mapper.deleteBySid(2l);//需要先删除中间表数据mapper.deleteById(2l);//再删除学生表数据}
这篇关于Java研学-MyBatis加强(二)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
