Mybatis高级
1. MyBatis 注解开发
1.1 常用注解介绍
-
我们除了可以使用映射配置文件来操作以外，还可以使用注解形式来操作。
-
常用注解
@Select(“查询的 SQL 语句”)：执行查询操作注解
@Insert(“新增的 SQL 语句”)：执行新增操作注解
@Update(“修改的 SQL 语句”)：执行修改操作注解
@Delete(“删除的SQL 语句”)：执行删除操作注解
1.2 注解实现"增删改查"操作
- 创建接口和查询方法
- 在核心配置文件中配置映射关系
- 编写测试类
StudentMapper.java
package com.itheima.mapper;
import com.itheima.bean.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
public interface StudentMapper {
//查询全部
@Select("SELECT * FROM student")
public abstract List<Student> selectAll();
//新增操作
@Insert("INSERT INTO student VALUES (#{id},#{name},#{age})")
public abstract Integer insert(Student stu);
//修改操作
@Update("UPDATE student SET name=#{name},age=#{age} WHERE id=#{id}")
public abstract Integer update(Student stu);
//删除操作
@Delete("DELETE FROM student WHERE id=#{id}")
public abstract Integer delete(Integer id);
}
核心配置文件MyBatisConfig.xml
- 增加如下配置,
<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration 核心根标签-->
<configuration>
<!--注解实现、配置映射关系-->
<mappers>
<package name="com.itheima.mapper"/>
<!-- 也可以配置的范围大一点 com下所有 -->
<!-- <package name="com"/> -->
</mappers>
</configuration>
Test01.java
package com.itheima.test;
import com.itheima.bean.Student;
import com.itheima.mapper.StudentMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAll() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
List<Student> list = mapper.selectAll();
//6.处理结果
for (Student student : list) {
System.out.println(student);
}
//7.释放资源
sqlSession.close();
is.close();
}
@Test
public void insert() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
Student stu = new Student(4,"赵六",26);
Integer result = mapper.insert(stu);
//6.处理结果
System.out.println(result);
//7.释放资源
sqlSession.close();
is.close();
}
@Test
public void update() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
Student stu = new Student(4,"赵六",36);
Integer result = mapper.update(stu);
//6.处理结果
System.out.println(result);
//7.释放资源
sqlSession.close();
is.close();
}
@Test
public void delete() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
Integer result = mapper.delete(4);
//6.处理结果
System.out.println(result);
//7.释放资源
sqlSession.close();
is.close();
}
}
1.3 注解开发小结
-
注解可以简化开发操作，省略映射配置文件的编写。
-
常用注解
@Select(“查询的 SQL 语句”)：执行查询操作注解
@Insert(“查询的 SQL 语句”)：执行新增操作注解
@Update(“查询的 SQL 语句”)：执行修改操作注解
@Delete(“查询的SQL 语句”)：执行删除操作注解
-
配置映射关系
2. MyBatis 注解实现多表操作
2.1 注解实现多表 一对一 (存在注意事项)
- 这种方式不推荐，推荐构建多表联查SQL并创建对应的查询结果集的Javabean进行接收数据- 详见扩展
CardMapper.java
package com.itheima.one_to_one;
import com.itheima.bean.Card;
import com.itheima.bean.Person;
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 java.util.List;
/*
@Results：封装映射关系的父注解。
Result[] value()：定义了Result 数组
@Result：封装映射关系的子注解。
column 属性：查询出的表中字段名称
property 属性：实体对象中的属性名称
javaType 属性：被包含对象的数据类型
one 属性：一对一查询固定属性
@One：一对一查询的注解。
select 属性：指定调用某个接口中的方
*/
public interface CardMapper {
//查询全部
@Select("SELECT * FROM card")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "number",property = "number"),
@Result(
property = "p", // 被包含对象的变量名
javaType = Person.class, // 被包含对象的实际数据类型
column = "pid", // 根据查询出的card表中的pid字段来查询person表
/*
one、@One 一对一固定写法
select属性：指定调用哪个接口中的哪个方法
*/
one = @One(select = "com.itheima.one_to_one.PersonMapper.selectById")
)
})
public abstract List<Card> selectAll();
}
PersonMapper.java
package com.itheima.one_to_one;
import com.itheima.bean.Person;
import org.apache.ibatis.annotations.Select;
public interface PersonMapper {
//根据id查询
@Select("SELECT * FROM person WHERE id=#{id}")
public abstract Person selectById(Integer id);
}
Test01.java
package com.itheima.one_to_one;
import com.itheima.bean.Card;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAll() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取CardMapper接口的实现类对象
CardMapper mapper = sqlSession.getMapper(CardMapper.class);
//5.调用实现类对象中的方法，接收结果
List<Card> list = mapper.selectAll();
//6.处理结果
for (Card card : list) {
System.out.println(card);
}
//7.释放资源
sqlSession.close();
is.close();
}
}
2.2 注解实现多表 一对多 (存在注意事项)
- 这种方式不推荐，推荐构建多表联查SQL并创建对应的查询结果集的Javabean进行接收数据- 详见扩展
ClassesMapper.java
package com.itheima.one_to_many;
import com.itheima.bean.Classes;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/*
@Results：封装映射关系的父注解。
Result[] value()：定义了Result 数组
@Result：封装映射关系的子注解。
column 属性：查询出的表中字段名称
property 属性：实体对象中的属性名称
javaType 属性：被包含对象的数据类型
many 属性：一对多查询固定属性
@Many：一对多查询的注解。
select 属性：指定调用某个接口中的方法
*/
public interface ClassesMapper {
//查询全部
@Select("SELECT * FROM classes")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(
property = "students", // 被包含对象的变量名
javaType = List.class, // 被包含对象的实际数据类型
column = "id", // 根据查询出的classes表的id字段来查询student表
/*
many、@Many 一对多查询的固定写法
select属性：指定调用哪个接口中的哪个查询方法
*/
many = @Many(select = "com.itheima.one_to_many.StudentMapper.selectByCid")
)
})
public abstract List<Classes> selectAll();
}
StudentMapper.java
package com.itheima.one_to_many;
import com.itheima.bean.Student;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface StudentMapper {
//根据cid查询student表
@Select("SELECT * FROM student WHERE cid=#{cid}")
public abstract List<Student> selectByCid(Integer cid);
}
Test01.java
package com.itheima.one_to_many;
import com.itheima.bean.Classes;
import com.itheima.bean.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAll() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取ClassesMapper接口的实现类对象
ClassesMapper mapper = sqlSession.getMapper(ClassesMapper.class);
//5.调用实现类对象中的方法，接收结果
List<Classes> list = mapper.selectAll();
//6.处理结果
for (Classes cls : list) {
System.out.println(cls.getId() + "," + cls.getName());
List<Student> students = cls.getStudents();
for (Student student : students) {
System.out.println("\t" + student);
}
}
//7.释放资源
sqlSession.close();
is.close();
}
}
2.3 注解实现多表 多对多 (存在注意事项)
- 这种方式不推荐，推荐构建多表联查SQL并创建对应的查询结果集的Javabean进行接收数据- 详见扩展
StudentMapper.java
package com.itheima.many_to_many;
import com.itheima.bean.Student;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/*
@Results：封装映射关系的父注解。
Result[] value()：定义了Result 数组
@Result：封装映射关系的子注解。
column 属性：查询出的表中字段名称
property 属性：实体对象中的属性名称
javaType 属性：被包含对象的数据类型
many 属性：一对多查询固定属性
@Many：一对多查询的注解。
select 属性：指定调用某个接口中的方法
*/
public interface StudentMapper {
//查询全部
@Select("SELECT DISTINCT s.id,s.name,s.age FROM student s,stu_cr sc WHERE sc.sid=s.id")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(column = "age",property = "age"),
@Result(
property = "courses", // 被包含对象的变量名
javaType = List.class, // 被包含对象的实际数据类型
column = "id", // 根据查询出student表的id来作为关联条件，去查询中间表和课程表
/*
many、@Many 一对多查询的固定写法
select属性：指定调用哪个接口中的哪个查询方法
*/
many = @Many(select = "com.itheima.many_to_many.CourseMapper.selectBySid")
)
})
public abstract List<Student> selectAll();
}
CourseMapper.java
package com.itheima.many_to_many;
import com.itheima.bean.Course;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface CourseMapper {
//根据学生id查询所选课程
@Select("SELECT c.id,c.name FROM stu_cr sc,course c WHERE sc.cid=c.id AND sc.sid=#{id}")
public abstract List<Course> selectBySid(Integer id);
}
Test01.java
package com.itheima.many_to_many;
import com.itheima.bean.Course;
import com.itheima.bean.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAll() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
List<Student> list = mapper.selectAll();
//6.处理结果
for (Student student : list) {
System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());
List<Course> courses = student.getCourses();
for (Course cours : courses) {
System.out.println("\t" + cours);
}
}
//7.释放资源
sqlSession.close();
is.close();
}
}
2.4 注解多表操作小结
- 这种玩法，并不是多表联查，而是执行了两次SQL，并且代码杂乱，不便于阅读和理解，不被企业所接受
/*
@Results：封装映射关系的父注解。
Result[] value()：定义了Result 数组
@Result：封装映射关系的子注解。
column 属性：查询出的表中字段名称
property 属性：实体对象中的属性名称
javaType 属性：被包含对象的数据类型
many 属性：一对多查询固定属性
@One：一对一查询的注解。
select 属性：指定调用某个接口中的方
@Many：一对多查询的注解。
select 属性：指定调用某个接口中的方法
*/
3. MyBatis 构建 SQL 语句 (不实用，了解)
3.1 SQL 构建对象介绍
- 我们之前通过注解开发时，相关 SQL 语句都是自己直接拼写的。一些关键字写起来比较麻烦、而且容易出错。
- MyBatis 给我们提供了 org.apache.ibatis.jdbc.SQL功能类，专门用于构建SQL 语句。
| 方法名 | 说明 |
|---|---|
| SELECT(String…column) | 根据字段拼接查询语句 |
| FROM(String…table) | 根据表名拼接语句 |
| WHERE(String…condition) | 根据条件拼接语句 |
| INSERT_INTO(String table) | 根据表名拼接新增语句 |
| VALUES(String column,Stringvalues) | 根据字段和值拼接插入数据语句 |
| UPDATE(String table) | 根据表名拼接修改语句 |
| DELETE_FROM(String table) | 根据表名拼接删除语句 |
| … … | … … |
3.2 SQL构建实现“增删改查”操作
StudentMapper.java
package com.itheima.mapper;
import com.itheima.bean.Student;
import com.itheima.sql.ReturnSql;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import java.util.List;
public interface StudentMapper {
//查询全部
//@Select("SELECT * FROM student")
@SelectProvider(type = ReturnSql.class , method = "getSelectAll")
public abstract List<Student> selectAll();
//新增功能
//@Insert("INSERT INTO student VALUES (#{id},#{name},#{age})")
@InsertProvider(type = ReturnSql.class , method = "getInsert")
public abstract Integer insert(Student stu);
//修改功能
//@Update("UPDATE student SET name=#{name},age=#{age} WHERE id=#{id}")
@UpdateProvider(type = ReturnSql.class , method = "getUpdate")
public abstract Integer update(Student stu);
//删除功能
//@Delete("DELETE FROM student WHERE id=#{id}")
@DeleteProvider(type = ReturnSql.class , method = "getDelete")
public abstract Integer delete(Integer id);
}
ReturnSql.java
package com.itheima.sql;
import com.itheima.bean.Student;
import org.apache.ibatis.jdbc.SQL;
public class ReturnSql {
//定义方法，返回查询的sql语句
public String getSelectAll() {
return new SQL() {
{
SELECT("*");
FROM("user");
}
}.toString();
}
//定义方法，返回新增的sql语句-- 可以不加参数
public String getInsert() {
// public String getInsert(Student stu) {
return new SQL() {
{
INSERT_INTO("user");
INTO_VALUES("#{id},#{name},#{age}");
}
}.toString();
}
//定义方法，返回修改的sql语句 -- 可以不加参数
public String getUpdate() {
// public String getUpdate(Student stu) {
return new SQL() {
{
UPDATE("user");
SET("name=#{name}","age=#{age}");
WHERE("id=#{id}");
}
}.toString();
}
//定义方法，返回删除的sql语句 -- 可以不加参数
public String getDelete() {
// public String getDelete(Integer id) {
return new SQL() {
{
DELETE_FROM("user");
WHERE("id=#{id}");
}
}.toString();
}
}
测试 Test01.java
package com.itheima.test;
import com.itheima.bean.Student;
import com.itheima.mapper.StudentMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAll() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
List<Student> list = mapper.selectAll();
//6.处理结果
for (Student student : list) {
System.out.println(student);
}
//7.释放资源
sqlSession.close();
is.close();
}
@Test
public void insert() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
Student stu = new Student(4,"赵六",26);
Integer result = mapper.insert(stu);
//6.处理结果
System.out.println(result);
//7.释放资源
sqlSession.close();
is.close();
}
@Test
public void update() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
Student stu = new Student(4,"赵六",36);
Integer result = mapper.update(stu);
//6.处理结果
System.out.println(result);
//7.释放资源
sqlSession.close();
is.close();
}
@Test
public void delete() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
Integer result = mapper.delete(4);
//6.处理结果
System.out.println(result);
//7.释放资源
sqlSession.close();
is.close();
}
}
3.2 构建 SQL 语句小结
-
org.apache.ibatis.jdbc.SQL：构建SQL 语句的功能类。通过一些方法来代替SQL 语句的关键字。
SELECT()
FROM()
WHERE()
INSERT_INTO()
VALUES()
UPDATE()
DELETE_FROM()
-
@SelectProvider：生成查询用的SQL 语句注解。
-
@InsertProvider：生成新增用的SQL 语句注解。
-
@UpdateProvider：生成修改用的SQL 语句注解。
-
@DeleteProvider：生成删除用的SQL 语句注解。
type 属性：生成 SQL 语句功能类对象
method 属性：指定调用方法
4. MyBatis 学生管理系统
4.1 学生管理系统
- 项目介绍和环境搭建。
- 学生信息的增删改查功能实现。
数据准备
-- 创建db3数据库
CREATE DATABASE db3;
-- 使用db3数据库
USE db3;
-- 创建用户表
CREATE TABLE USER(
uid VARCHAR(50) PRIMARY KEY, -- 用户id
ucode VARCHAR(50), -- 用户标识
loginname VARCHAR(100), -- 登录用户名
PASSWORD VARCHAR(100), -- 登录密码
username VARCHAR(100), -- 用户名
gender VARCHAR(10), -- 用户性别
birthday DATE, -- 出生日期
dutydate DATE -- 入职日期
);
-- 添加一条测试数据
INSERT INTO USER VALUES ('11111111', 'zhangsan001', 'zhangsan', '1234', '张三', '男', '2008-10-28', '2018-10-28');
-- 创建student表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
birthday DATE -- 学生生日
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'1999-09-23'),(NULL,'李四',24,'1998-08-10'),
(NULL,'王五',25,'1996-06-06'),(NULL,'赵六',26,'1994-10-20');
StudentDao.java
package com.itheima.dao;
import com.itheima.domain.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.ArrayList;
/*
Dao层接口
*/
public interface StudentDao {
//查询所有学生信息
@Select("SELECT * FROM student")
public abstract ArrayList<Student> findAll();
//条件查询，根据id获取学生信息
@Select("SELECT * FROM student WHERE sid=#{sid}")
public abstract Student findById(Integer sid);
//新增学生信息
@Insert("INSERT INTO student VALUES (#{sid},#{name},#{age},#{birthday})")
public abstract int insert(Student stu);
//修改学生信息
@Update("UPDATE student SET name=#{name},age=#{age},birthday=#{birthday} WHERE sid=#{sid}")
public abstract int update(Student stu);
//删除学生信息
@Delete("DELETE FROM student WHERE sid=#{sid}")
public abstract int delete(Integer sid);
}
StudentServiceImpl.java
package com.itheima.service.impl;
import com.itheima.dao.StudentDao;
import com.itheima.domain.Student;
import com.itheima.service.StudentService;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* 学生的业务层实现类
* @author 黑马程序员
* @Company http://www.itheima.com
*/
public class StudentServiceImpl implements StudentService {
@Override
public List<Student> findAll() {
ArrayList<Student> list = null;
SqlSession sqlSession = null;
InputStream is = null;
try{
//1.加载核心配置文件
is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentDao接口的实现类对象
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
//5.调用实现类对象的方法，接收结果
list = mapper.findAll();
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
if(sqlSession != null) {
sqlSession.close();
}
if(is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//7.返回结果
return list;
}
@Override
public Student findById(Integer sid) {
Student stu = null;
SqlSession sqlSession = null;
InputStream is = null;
try{
//1.加载核心配置文件
is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentDao接口的实现类对象
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
//5.调用实现类对象的方法，接收结果
stu = mapper.findById(sid);
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
if(sqlSession != null) {
sqlSession.close();
}
if(is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//7.返回结果
return stu;
}
@Override
public void save(Student student) {
SqlSession sqlSession = null;
InputStream is = null;
try{
//1.加载核心配置文件
is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentDao接口的实现类对象
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
//5.调用实现类对象的方法，接收结果
mapper.insert(student);
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
if(sqlSession != null) {
sqlSession.close();
}
if(is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
@Override
public void update(Student student) {
SqlSession sqlSession = null;
InputStream is = null;
try{
//1.加载核心配置文件
is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentDao接口的实现类对象
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
//5.调用实现类对象的方法，接收结果
mapper.update(student);
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
if(sqlSession != null) {
sqlSession.close();
}
if(is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
@Override
public void delete(Integer sid) {
SqlSession sqlSession = null;
InputStream is = null;
try{
//1.加载核心配置文件
is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentDao接口的实现类对象
StudentDao mapper = sqlSession.getMapper(StudentDao.class);
//5.调用实现类对象的方法，接收结果
mapper.delete(sid);
} catch (Exception e) {
e.printStackTrace();
} finally {
//6.释放资源
if(sqlSession != null) {
sqlSession.close();
}
if(is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
5. 扩展
5.1 多表联查-注解开发
5.1.0 数据准备
/*
SQLyog Enterprise v12.09 (64 bit)
MySQL - 5.7.29 : Database - mybatis
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatis` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mybatis`;
/*Table structure for table `card` */
DROP TABLE IF EXISTS `card`;
CREATE TABLE `card` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(30) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cp_fk` (`pid`),
CONSTRAINT `cp_fk` FOREIGN KEY (`pid`) REFERENCES `person` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `card` */
insert into `card`(`id`,`number`,`pid`) values (1,'12345',1),(2,'23456',2),(3,'34567',3);
/*Table structure for table `classes` */
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `classes` */
insert into `classes`(`id`,`name`) values (1,'黑马一班'),(2,'黑马二班');
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `course` */
insert into `course`(`id`,`name`) values (1,'语文'),(2,'数学');
/*Table structure for table `person` */
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `person` */
insert into `person`(`id`,`name`,`age`) values (1,'张三',23),(2,'李四',24),(3,'王五',25);
/*Table structure for table `stu_cr` */
DROP TABLE IF EXISTS `stu_cr`;
CREATE TABLE `stu_cr` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `sc_fk1` (`sid`),
KEY `sc_fk2` (`cid`),
CONSTRAINT `sc_fk1` FOREIGN KEY (`sid`) REFERENCES `student` (`id`),
CONSTRAINT `sc_fk2` FOREIGN KEY (`cid`) REFERENCES `course` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `stu_cr` */
insert into `stu_cr`(`id`,`sid`,`cid`) values (1,1,1),(2,1,2),(3,2,1),(4,2,2);
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cs_fk` (`cid`),
CONSTRAINT `cs_fk` FOREIGN KEY (`cid`) REFERENCES `classes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `student` */
insert into `student`(`id`,`name`,`age`,`cid`) values (1,'张三',23,1),(2,'李四',24,1),(3,'王五',25,2),(4,'赵六',26,2);
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert into `user`(`id`,`name`,`age`) values (1,'??',26),(1,'??',26),(1,'赵六',26),(10,'??',26);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
5.1.1 注解实现多表 一对一
PersonCardVo.java
package com.itheima.bean.vo;
public class PersonCardVo {
private Integer pid; //人表主键id
private String name; //人的姓名
private Integer age; //人的年龄
private Integer cid; //身份证号表主键
private String number; //身份证号
public PersonCardVo(){}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
@Override
public String toString() {
return "PersonCardVo{" +
"pid=" + pid +
", name='" + name + '\'' +
", age=" + age +
", cid=" + cid +
", number='" + number + '\'' +
'}';
}
}
CardMapper.java
package com.itheima.one_to_one;
import com.itheima.bean.Card;
import com.itheima.bean.Person;
import com.itheima.bean.vo.PersonCardVo;
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 java.util.List;
public interface CardMapper {
/*方式二 查询全部 */
@Select("SELECT c.id AS cid, c.number, c.pid, p.`name`, p.age FROM card c,person p WHERE c.pid=p.id")
public abstract List<PersonCardVo> selectAllZls();
}
Test01.java
package com.itheima.one_to_one;
import com.itheima.bean.Card;
import com.itheima.bean.vo.PersonCardVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAllZls() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取CardMapper接口的实现类对象
CardMapper mapper = sqlSession.getMapper(CardMapper.class);
//5.调用实现类对象中的方法，接收结果
List<PersonCardVo> list = mapper.selectAllZls();
//6.处理结果
for (PersonCardVo personCardVo : list) {
System.out.println(personCardVo);
}
//7.释放资源
sqlSession.close();
is.close();
}
}
5.1.2 注解实现多表 一对多
ClassesStudentVo.java
package com.itheima.bean.vo;
public class ClassesStudentVo {
private Integer cid; //班级的主键id
private String cname; //班级名称
private Integer sid; //学生的主键id
private String sname; //学生姓名
private Integer sage; //学生年龄
public ClassesStudentVo(){}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Integer getSage() {
return sage;
}
public void setSage(Integer sage) {
this.sage = sage;
}
@Override
public String toString() {
return "ClassesStudentVo{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", sid=" + sid +
", sname='" + sname + '\'' +
", sage=" + sage +
'}';
}
}
ClassesMapper.java
package com.itheima.one_to_many;
import com.itheima.bean.Classes;
import com.itheima.bean.vo.ClassesStudentVo;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface ClassesMapper {
//查询全部
@Select("SELECT\n"
+ " c.id AS cid,c.name AS cname,s.id AS sid,s.name AS sname,s.age AS sage\n"
+ " FROM\n"
+ " classes AS c,student AS s\n"
+ " WHERE\n"
+ " c.id=s.cid")
public abstract List<ClassesStudentVo> selectAllZls();
}
Test01.java
package com.itheima.one_to_many;
import com.itheima.bean.Classes;
import com.itheima.bean.Student;
import com.itheima.bean.vo.ClassesStudentVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAllZls() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取ClassesMapper接口的实现类对象
ClassesMapper mapper = sqlSession.getMapper(ClassesMapper.class);
//5.调用实现类对象中的方法，接收结果
List<ClassesStudentVo> list = mapper.selectAllZls();
//6.处理结果
for (ClassesStudentVo classesStudentVo : list) {
System.out.println(classesStudentVo);
}
//7.释放资源
sqlSession.close();
is.close();
}
}
5.1.23 注解实现多表 多对多
StudentCourseVo.java
package com.itheima.bean.vo;
public class StudentCourseVo {
private Integer sid; //学生的主键id
private String sname; //学生姓名
private Integer sage; //学生年龄
private Integer cid; //课程的主键id
private String cname; //课程名称
public StudentCourseVo(){}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Integer getSage() {
return sage;
}
public void setSage(Integer sage) {
this.sage = sage;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
@Override
public String toString() {
return "StudentCourseVo{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", sage=" + sage +
", cid=" + cid +
", cname='" + cname + '\'' +
'}';
}
}
StudentMapper.java
package com.itheima.many_to_many;
import com.itheima.bean.Student;
import com.itheima.bean.vo.StudentCourseVo;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface StudentMapper {
/*方式二 查询全部*/
@Select("SELECT\n"
+ " sc.sid, s.name sname, s.age sage, c.`id` cid, c.name cname\n"
+ " FROM\n"
+ " student s, course c, stu_cr sc\n"
+ " WHERE\n"
+ " sc.sid=s.id AND sc.cid=c.id")
public abstract List<StudentCourseVo> selectAllZls();
}
Test01.java
package com.itheima.many_to_many;
import com.itheima.bean.Course;
import com.itheima.bean.Student;
import com.itheima.bean.vo.StudentCourseVo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class Test01 {
@Test
public void selectAllZls() throws Exception{
//1.加载核心配置文件
InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
//2.获取SqlSession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//4.获取StudentMapper接口的实现类对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//5.调用实现类对象中的方法，接收结果
List<StudentCourseVo> list = mapper.selectAllZls();
//6.处理结果
for (StudentCourseVo studentCourseVo : list) {
System.out.println(studentCourseVo);
}
//7.释放资源
sqlSession.close();
is.close();
}
}
5.2 企业级注解示例代码
- 当注解“”中存在 set where if 等标签时，我们要在最外层 套上 标签， 没有其他标签，则不需要考虑
public interface DemoMapper {
/**
* 添加意见反馈
*
* @param sysFeedback 意见反馈
* @return Integer
*/
@Insert("INSERT INTO table_feedback (content, user_id, wx_name, create_time ) VALUES (#{content}, #{userId}, #{wxName}, #{createTime});")
Integer insertFeedback(TableFeedback sysFeedback);
/**
* 用户详细信息
*
* @param sex 性别
* @return AppResultListUserInfo
*/
@Select("<script>SELECT a.wx_sex, a.wx_url, a.wx_name, a.province, a.city, b.height, b.description, b.company, b.position, b.age, b.salary_range, b.user_id "
+ "FROM table_user a, table_user_info b WHERE a.id = b.user_id AND a.wx_sex = #{sex}</script>")
List<AppResultListUserInfo> getUserInfoByColumn(@Param("sex") String sex);
/**
* 查询用户基本信息
*
* @param sysUser 用户实体
* @return
* @Date xxxxxx
* @Author ZJW
*/
@Select("<script> SELECT * FROM table_user <trim prefix='WHERE' prefixOverrides='AND'> <if test='openid !=null and openid !=\"\" '> openid = #{openid} </if> "
+ " <if test='id !=null and id !=\"\" '> id = #{id} </if> </trim> </script>")
TableUser getUserByColumn(TableUser sysUser);
/**
* 修改订单信息
*
* @param sysOrder 订单信息实体
* @return
*/
@Update("<script> UPDATE table_order <trim prefix='set' suffixOverrides=','>"
+ " <if test= 'payState !=null and payState != \"\" '> pay_state = #{payState},</if>"
+ " <if test= 'payTime !=null and payTime != \"\" '> pay_time = #{payTime},</if>"
+ " <if test= 'isAgree !=null and isAgree != \"\" '> is_agree = #{isAgree},</if>"
+ " <if test= 'transactionId !=null and transactionId != \"\" '> transaction_id = #{transactionId},</if>"
+ " </trim> WHERE user_id= #{userId} </script>")
Integer updateOrder(TableOrder sysOrder);
/**
* 删除消息
*
* @param id 主键id
* @return
*/
@Delete("DELETE FROM table_message WHERE id = #{id}")
Integer deleteCollect(@Param("id") Integer id);
}
扫一扫
135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
