MyBatis 笔记记录
一、MyBatis基础
1. MyBatis介绍及快速入门
引入依赖:
写sql:
<?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="StudentMapper">
<select id="find" resultType="com.jou.domain.Student">
select * from student
</select>
</mapper>
核心配置文件:
<?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>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/db1"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="StudentMapper.xml"></mapper>
</mappers>
</configuration>
方法调用:
结果输出:
2. 相关API介绍
2.1 Resources
本质还是类加载器的调用
2.2 SqlSessionFactory&&SqlSessionFactoryBuilder
2.3 SqlSession
3. 映射配置文件
xml的使用
import com.jou.domain.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 StudentTest {
@Test
public void findAll() throws Exception {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sessionFactory.openSession();
List<Student> students = sqlSession.selectList("StudentMapper.find");
for (Student student : students) {
System.out.println(student);
}
}
}
4. 核心配置文件
4.1 规范写法
4.2 参数和返回类型_起别名
核心配置中:
mapper中:
5. 引入Log4j
输出:
二、MyBatis进阶
1. 接口代理
mapper实现类去除,直接再实现层调用mapper对象
2. 动态sql使用
2.1 where&if
使用1:
2.2 foreach
2.3 片段抽取 include&sql
3. 分页使用
3.1 pagehelper使用
mybatis-config中插入
分页功能类PageInfo
4.多表操作
4.1 一对一
数据准备:sql
create database db2;
use db2;
create table person(
id int PRIMARY key auto_increment,
name varchar(20),
age int
);
insert into person values(null,'张三',24);
insert into person values(null,'李四',26);
insert into person values(null,'王五',28);
create table card(
id int PRIMARY key auto_increment,
number varchar(30),
pid int,
CONSTRAINT cp_fk FOREIGN key (pid) REFERENCES person(id)
);
insert into card values (null,'12345',1);
insert into card values (null,'23456',2);
insert into card values (null,'34567',3);
实体类:
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Card {
private Integer id;
private String number;
private Person p;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Person {
private Integer id;
private String name;
private Integer age;
}
mybatis-config:引入Mapper
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="com.jou.mapper.OneToOneMapper">
<resultMap id="oneToOne" type="card">
<!-- id主键映射字段column是数据库字段,property=是实体类字段,result为其他字段映射-->
<id column="cid" property="id"></id>
<result column="number" property="number"></result>
<!-- 联表查询两个表两个实体,结果包含另一个实体-->
<!-- association:配置被包含对象的映射关系,property:为实体类变量名-->
<association property="p" javaType="person">
<id column="pid" property="id"></id>
<result column="name" property="name"></result>
<result column="age" property="age"></result>
</association>
</resultMap>
<select id="selectAll" resultMap="oneToOne">
select c.id cid,number,pid,name,age from card c,person p where c.pid=p.id;
</select>
</mapper>
4.2 一对多
数据准备:
create table classes(
id int PRIMARY key auto_increment,
name varchar(20)
);
insert into classes values(null,'计算机一班');
insert into classes values(null,'计算机二班');
create table student(
id int primary key auto_increment,
name varchar(30),
age int ,
cid int ,
constraint cs_fk FOREIGN key (cid) REFERENCES classes (id)
);
insert into student values(null,'张三',23,1);
insert into student values(null,'李四',24,1);
insert into student values(null,'王五',25,2);
insert into student values(null,'赵六',26,2);
实体类:
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private Integer id;
private String name;
private Integer age;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Classes {
private Integer id;
private String name;
private List<Student> students;
}
mybatis-config:引入Mapper
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="com.jou.mapper.OneToManyMapper">
<resultMap id="OneToMany" type="classes">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<!-- association用来做一对一的,现在是一对多不能用啦,一对多可以使用collection
property:被包含对象的变量名,ofType:被包含对象的实际数据类型-->
<collection property="students" ofType="student">
<id column="sid" property="id"></id>
<result column="sname" property="name" ></result>
<result column="sage" property="age" ></result>
</collection>
</resultMap>
<select id="selectAll" resultMap="OneToMany">
select c.id cid,c.`name` cname,s.id sid,s.`name` sname, s.age sage from classes c,student s where s.cid=c.id;
</select>
</mapper>
4.3 多对多
数据准备:sql
create table course(
id int primary key auto_increment,
name varchar(20)
);
insert into course values(null,'语文');
insert into course values(null,'数学');
create table stu_col(
id int primary key auto_increment,
sid int,
cid int,
constraint sc_fk1 FOREIGN key (sid) REFERENCES student(id),
constraint sc_fk2 FOREIGN key (cid) REFERENCES course(id)
);
insert into stu_col values(null,1,1);
insert into stu_col values(null,1,2);
insert into stu_col values(null,2,1);
insert into stu_col values(null,2,2);
实体类:
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Course {
private Integer id;
private String name;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private Integer id;
private String name;
private Integer age;
private List<Course> courses;
}
mybatis-config:引入Mapper
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="com.jou.mapper.ManyToManyMapper">
<resultMap id="manyToMany" type="student">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
<result column="sage" property="age"></result>
<collection property="courses" ofType="course">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</collection>
</resultMap>
<select id="selectAll" resultMap="manyToMany">
select sc.sid,s.`name` sname,s.age sage,sc.cid,c.`name` cname from student s,course c,stu_col sc where sc.sid=s.id and sc.cid=c.id
</select>
</mapper>
三、MyBatis高级
1. 注解开发
注解开发流程:
1.引入依赖:
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.2</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.3.0</version>
</dependency>
</dependencies>
- 写入配置
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db2?useSSL=false
username=root
password=root
#ERROR WARN INFO DEBUG
log4j.rootLogger=DEBUG,stdout
#----------------输出为控制台-------------------#
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配置改写引入mapper形式:
<?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>
<settings>
<setting name="logImpl" value="log4j"/>
</settings>
<typeAliases>
<!-- <typeAlias type="com.jou.bean.Students" alias="student"></typeAlias>-->
<package name="com.zfz.bean"></package>
</typeAliases>
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- <mappers>-->
<!-- <mapper resource="StudentMapper.xml"></mapper>-->
<!-- </mappers>-->
<!-- 改为注解形式不需要上述形式-->
<mappers>
<package name="com.zfz.mapper"/>
</mappers>
</configuration>
3. 写接口
4. 测试
2. 注解形式多表操作
2.1 一对一
每个人有独立的身份证号 一对一关系
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Card {
private Integer id;
private String number;
private Person p;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Person {
private Integer id;
private String name;
private Integer age;
}
PersonMapper 中根据id查询用户信息
public interface PersonMapper {
@Select("select * from person where id=#{id}")
Person selectById(Integer id);
}
CardMapper 查询所有card信息,card中有id,number,pid三个字段,其中pid是Person表的主键,作为card表的外键,一对一关系中查询出card信息后,同时得到pid外键信息,将pid作为参数调用PersonMapper中的查询用户信息,查询card接口的同时查询了该身份证号所包含的用户信息,其中p为实体类映射,pid为card表查询出的字段
public interface CardMapper {
@Select("select * from card")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "number",property = "number"),
@Result(property = "p",//被包含的变量名,这里的p是一个list的名称
javaType = Person.class, //被包含的实际数据类型,
column = "pid", //根据查询出的card表中的pid字段来查询person表
/***
*one、@one 一对一的固定写法
* select :指的是调用哪个接口中的哪些方法
*/
one=@One(select ="com.zfz.mapper.PersonMapper.selectById")
)
})
List<Card> selectAll();
}
测试:
@Test
public void selectAll(){
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
CardMapper mapper = sqlSession.getMapper(CardMapper.class);
List<Card> cards = mapper.selectAll();
for (Card card : cards) {
System.out.println(card);
}
sqlSession.close();
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
结果:
2.2 一对多
一个班级有多个学生 一对多关系
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private Integer id;
private String name;
private Integer age;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Classes {
private Integer id;
private String name;
private List<Student> students;
}
StudentMapper 中接口方法根据id查询学生信息
public interface StudentMapper {
@Select("select * from student where cid=#{id}")
List<Student> selectByCid(Integer id);
}
ClassesMapper 中一个班级有多个学生,因此查询出班级信息后,班级的主键id作为学生表中的外键,有一对多的关系,根据id查询学生信息
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表数据,这里的id作为参数
/***
* many、@Many 一对多固定写法
* select 属性:指定调用哪个接口中的哪个方法
*/
many = @Many(select = "com.zfz.mapper.StudentMapper.selectByCid")
)
})
List<Classes> selectAll();
}
测试:
@Test
public void selectAll(){
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
ClassesMapper mapper = sqlSession.getMapper(ClassesMapper.class);
List<Classes> classes = mapper.selectAll();
for (Classes classes1 : classes) {
System.out.println(classes1);
}
sqlSession.close();
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
2.3 多对多
学生与课程 一个学生可以选择多个课程,一个课程也可以被多个学生选择
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Course {
private Integer id;
private String name;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private Integer id;
private String name;
private Integer age;
private List<Course> courses;
}
CourseMapper 中的方法用来根据学生id查询学生所选择课程
public interface CourseMapper {
@Select("select c.id,c.name from stu_col sc,course c where sc.cid=c.id and sc.sid=#{id}")
List<Course> selectBySid(Integer id);
}
StudentMapper中方法用来查询所有学生信息,并将学生信息的id用来做二次查询查询该学生所选择的课程
public interface StudentMapper {
//只查询已经选课的学生 没有选课的不查询 看中间表有没有数据即可
@Select("SELECT distinct s.id,s.name,s.age FROM `student` s,stu_col sc where s.id=sc.sid")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(column = "age",property = "age"),
@Result(
property = "courses",
javaType = List.class,
column = "id",
/***
* many、@Many 一对多固定写法
* select 属性:指定调用哪个接口中的哪个方法
*/
many = @Many(select = "com.zfz.mapper.CourseMapper.selectBySid")
)
})
List<Student> selectAll();
}
测试:
@Test
public void selectAll(){
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.selectAll();
for (Student stu : students) {
System.out.println(stu);
}
sqlSession.close();
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
2.4 注解 联表小结
3. 构建SQL语句
SQL2为SQL功能类写的sql
public class SQLTest {
public static void main(String[] args) {
String sql = getSQL2();
System.out.println(sql);
}
public static String getSQL(){
String sql="select * from student";
return sql;
}
public static String getSQL2(){
String string = new SQL() {
{
SELECT("*");
FROM("student");
}
}.toString();
return string;
}
}
3.1 查询功能
构建SQL工具类
public class ReturnSql {
public String getSelectAll(){
String sql = new SQL() {
{
SELECT("*");
FROM("student");
}
}.toString();
return sql;
}
}
Mapper中使用
/* type:用来指定SQL工作类对象,method:用来指定使用的SQL方法*/
@SelectProvider(type = ReturnSql.class,method = "getSelectAll")
List<Student> selectAll1();
3.2 新增功能
public String getInsert(Student student){
return new SQL() {
{
INSERT_INTO("student");
INTO_VALUES("#{id},#{name},#{age},#{cid}");
}
}.toString();
}
@InsertProvider(type = ReturnSql.class,method = "getInsert")
Integer insert(Student student);
3.3 修改功能
public String getUpdate(Student student){
return new SQL() {
{
UPDATE("student");
SET("name=#{name},age=#{age}");
WHERE("id=#{id}");
}
}.toString();
}
@UpdateProvider(type = ReturnSql.class,method = "getUpdate")
Integer update(Student student);
3.4 删除功能
public String getDelete(Integer id){
return new SQL() {
{
DELETE_FROM("student");
WHERE("id=#{id}");
}
}.toString();
}
@DeleteProvider(type = ReturnSql.class,method = "getDelete")
Integer delete(Integer id);