1 介绍
1.1 汇总
01.汇总
a.ORM
Mybatis是ORM的一个实现，通过操作对象来操作数据库表
b.映射
对象名 对应 表名、属性 对应 字段名
c.MyBatis项目
不涉及web开发，因此环境仅需要【JDK + mybatis-3.5.4.jar + ojdbc6-11.2.0.3.jar】
1.2 Person.java
01.sql
create table person(id number, name varchar2(20), age number);
SCOTT@ORCL>desc person;
名称 是否为空? 类型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ID NUMBER
NAME VARCHAR2(20)
AGE NUMBER
02.java
public class Person {
private int id;
private String name;
private int age;
public Person() {
}
public Person(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
2 环境搭建
2.1 汇总
00.汇总：单独StudentMapper.xml
映射：对象名 对应 表名、属性 对应 字段名
+ conf.xml配置mapper，<mapper resource="org/myslayers/entity/PersonMapper.xml" />
+ session.selectOne/selectList/insert/delete/update方法
+ StudentMapper.xml中parameterType/resultType，使用“全类名”
2.2 Person.java
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE person';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table person(id number, name varchar2(20), age number);
insert into person values(1, 'zs', 23);
commit;
02.java
public class Person {
private int id;
private String name;
private int age;
public Person() {
}
public Person(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
2.3 conf.xml
01.conf.xml
<?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="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<!-- 配置数据库信息 -->
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载映射文件 -->
<mapper resource="org/myslayers/entity/PersonMapper.xml" />
</mappers>
</configuration>
2.4 PersonMapper.xml
01.PersonMapper.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="org.myslayers.entity.PersonMapper">
映射文件路径
<select id="queryPersonBySno" resultType="org.myslayers.entity.Person" parameterType="int">
标识id 返回类型 传入类型
select * from person where id = #{id}
动态id
</select>
</mapper>
2.5 Test.java
01.Test.java
public class TestMyBatis {
public static void main(String[] args) throws IOException {
// connection - SqlSession
// 1.conf.xml - reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// 2.reader - SqlSession
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
//处理sql
String statement = "org.myslayers.entity.PersonMapper.queryPersonBySno";
Person person = session.selectOne(statement,1);
System.out.println(person);
//关闭session
session.close();
}
}
3 基础方式的CRUD
3.1 汇总
00.汇总：单独StudentMapper.xml
映射：对象名 对应 表名、属性 对应 字段名
+ conf.xml配置mapper，<mapper resource="org/myslayers/entity/PersonMapper.xml" />
+ session.selectOne/selectList/insert/delete/update方法
+ StudentMapper.xml中parameterType/resultType，使用“全类名”
01.select单个/全部，insert/delete/update
a.说明
查：单个，parameterType为int，resultType为entity
查：全部，parameterType不存在，resultType为entity
增删改：只有parameterType，没有resultType
b.代码
<mapper namespace="org.myslayers.entity.StudentMapper">
<!-- 增:无resultType -->
<insert id="addStudent" parameterType="org.myslayers.entity.Student">
insert into student(stuno, stuname, stuage, graname) values(#{stuNo}, #{stuName}, #{stuAge}, #{graName})
</insert>
<!-- 删:无resultType -->
<delete id="deleteStudentBySno" parameterType="int">
delete from student where stuno = #{stuNo}
</delete>
<!-- 改:无resultType-->
<update id="updateStudentBySno" parameterType="org.myslayers.entity.Student" >
update student set stuname = #{stuName}, stuage = #{stuAge}, graname=#{graName} where stuno=#{stuNo}
</update>
<!-- 查：全部信息，无parameterType，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryAllStudents" resultType="org.myslayers.entity.Student" >
select * from student
</select>
<!-- 查：单个信息，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryStudentBySno" parameterType="int" resultType="org.myslayers.entity.Student" >
select * from student where stuno = #{stuNo}
</select>
</mapper>
02.输入参数
a.说明
主要针对于将信息存入到数据库中，拼sql
输入参数parameterType：在形式上只能有一个。但是可以用数组（集合）表示多个，org.myslayers.entity.Student
b.简单类型（8个基本类型+引用类型String）可以使用任何占位符 #{xxx}
<select id="queryStudentBySno" parameterType="int" resultType="org.myslayers.entity.Student" >
select * from student where stuno = #{stuNo}
</select>
c.对象类型，则必须是对象的属性 #{属性名}
<!-- 增:无resultType -->
<insert id="addStudent" parameterType="org.myslayers.entity.Student">
insert into student(stuno, stuname, stuage, graname) values(#{stuNo}, #{stuName}, #{stuAge}, #{graName})
</insert>
03.输出参数
a.说明
主要针对于从数据库中提取相应的数据出来
输出参数resultType：在形式上只能有一个
b.简单类型
<!-- 删:无resultType -->
<delete id="deleteStudentBySno" parameterType="int">
delete from student where stuno = #{stuNo}
</delete>
c.对象类型，无论返回一个、还是多个，统一resultType="org.lanqiao.entity.Student"
<!-- 查：全部信息，无parameterType，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryAllStudents" resultType="org.myslayers.entity.Student" >
select * from student
</select>
<!-- 查：单个信息，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryStudentBySno" parameterType="int" resultType="org.myslayers.entity.Student" >
select * from student where stuno = #{stuNo}
</select>
3.2 Student.java
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
public Student() {
}
public Student(int stuNo, String stuName, int stuAge, String graName) {
this.stuNo = stuNo;
this.stuName = stuName;
this.stuAge = stuAge;
this.graName = graName;
}
public int getStuNo() {
return stuNo;
}
public void setStuNo(int stuNo) {
this.stuNo = stuNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getstuAge() {
return stuAge;
}
public void setstuAge(int stuAge) {
this.stuAge = stuAge;
}
public String getGraName() {
return graName;
}
public void setGraName(String graName) {
this.graName = graName;
}
public String toString() {
return this.stuNo + "-" + this.stuName + "-" + this.stuAge + "-" + this.graName;
}
}
3.3 conf.xml
01.conf.xml
<?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="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载映射文件 -->
<mapper resource="org/myslayers/entity/studentMapper.xml" />
</mappers>
</configuration>
3.4 StudentMapper.java
01.StudentMapper.java
<?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="org.myslayers.entity.StudentMapper">
<!-- 增:无resultType -->
<insert id="addStudent" parameterType="org.myslayers.entity.Student">
insert into student(stuno, stuname, stuage, graname) values(#{stuNo}, #{stuName}, #{stuAge}, #{graName})
</insert>
<!-- 删:无resultType -->
<delete id="deleteStudentBySno" parameterType="int">
delete from student where stuno = #{stuNo}
</delete>
<!-- 改:无resultType-->
<update id="updateStudentBySno" parameterType="org.myslayers.entity.Student" >
update student set stuname = #{stuName}, stuage = #{stuAge}, graname=#{graName} where stuno=#{stuNo}
</update>
<!-- 查：全部信息，无parameterType，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryAllStudents" resultType="org.myslayers.entity.Student" >
select * from student
</select>
<!-- 查：单个信息，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryStudentBySno" parameterType="int" resultType="org.myslayers.entity.Student" >
select * from student where stuno = #{stuNo}
</select>
</mapper>
3.5 Test.java
01.Test.java
public class Test {
// 查询单个学生
public static void queryStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
String statement = "org.myslayers.entity.studentMapper." + "queryStudentBySno";
// selectOne
Student student = session.selectOne(statement, 1);// 输入：int 返回：Student
System.out.println(student);
session.close();
}
// 查询全部学生
public static void queryAllStudent() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
String statement = "org.myslayers.entity.studentMapper." + "queryAllStudents";
// selectList
List<Student> students = session.selectList(statement);// 输入：无 返回：List<Student>
System.out.println(students);
session.close();
}
// 增加一个学生
public static void addStudent() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
String statement = "org.myslayers.entity.studentMapper." + "addStudent";
Student student = new Student(4, "ww", 25, "s1");
// insert
// statement：指定执行的SQL student:sql中需要的参数
int count = session.insert(statement, student);// 输入：对象 返回：int
session.commit();// 提交事务
System.out.println("增加" + count + "个学生");
session.close();
}
// 删除学生
public static void deleteStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
String statement = "org.myslayers.entity.studentMapper." + "deleteStudentBySno";
// delete
int count = session.delete(statement, 3);// 输入：int 返回：int
session.commit();// 提交事务
System.out.println("删除" + count + "个学生");
session.close();
}
// 修改学生
public static void updateStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
String statement = "org.myslayers.entity.studentMapper." + "updateStudentBySno";
// 修改参数
Student student = new Student();
// 修改哪个人，where stuno=2
student.setStuNo(2);
// 修改成什么样子
student.setStuName("lxs");
student.setstuAge(44);
student.setGraName("s2");
// 执行
int count = session.update(statement, student);
// 提交事务
session.commit();
// 显示结果
System.out.println("修改" + count + "个学生");
session.close();
}
public static void main(String[] args) throws IOException {
// queryStudentBySno();
// queryAllStudent();
addStudent();
// deleteStudentBySno();
// updateStudentBySno();
// queryAllStudent();
}
}
4 Mapper动态代理方式的CRUD
4.1 汇总
00.汇总：StudentMapper.java 对应 StudentMapper.xml
映射：对象名 对应 表名、属性 对应 字段名
+ conf.xml配置mapper，<mapper resource="org/myslayers/entity/PersonMapper.xml" />
+ 动态代理getMapper+反射xxx.class StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySno(1);
+ StudentMapper.xml中parameterType/resultType，使用“全类名”
01.MyBatis的核心组件
a.定义
SqlSessionFactoryBuilder(构造器)： 它可以从XML、注解或者手动配置Java代码来创建SqlSessionFactory
SqlSessionFactory: 用于创建SqlSession (会话) 的工厂
SqlSession: SqlSession是Mybatis最核心的类，可以用于执行语句、提交或回滚事务以及获取映射器Mapper的接口
SQL Mapper： 它是由一个Java接口和XML文件（或注解）构成的，需要给出对应的SQL和映射规则，它负责发送SQL去执行，并返回结果
b.查询单个学生
// 查询单个学生：【session.selectOne/selectList/insert/delete/update方法】
public static void queryStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
String statement = "org.myslayers.entity.studentMapper." + "queryStudentBySno";
// selectOne
Student student = session.selectOne(statement, 1);// 输入：int 返回：Student
System.out.println(student);
session.close();
}
c.查询单个学生
// 查询单个学生：【session.getMapper(xxx.class).queryStudentBySno(1)】
public static void queryStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
// 动态代理getMapper + 反射xxx.class
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySno(1);
System.out.println(student);
session.close();
}
02.三个必须约定+一个尽量约定
a.三个必须约定
方法名，与mapper.xm文件中标签的id值相同
输入参数，与mapper.xml文件中标签的parameterType类型一致
返回值，与mapper.xml文件中标签的resultType类型一致
b.一个尽量约定
形参，可以不一致，但尽量一致#{stuNo)
03.以下2点可以保证：当我们调用接口中的方法时，程序能自动定位到某一个XxxMapper.xml文件中的sqL标签
接口名 -> mapper.xml文件 （namespace=接口全类名）
接口方法名 -> mapper.xml文件中的SQL标签（方法名=SQL标签Id值）
04.约定习惯：Mapper.xml与Mapper接口放同一包
SQL映射文件（xxxMapper.xml）和接口（StudentMapper）放同一个包中
05.接口绑定
就是在MyBatis中任意定义接口，然后把接口里面的方法和SQL语句绑定，
我们直接调用接口方法就可以，这样比起原来了SqlSession提供的方法我们可以有更加灵活的选择和设置。
06.接口绑定有两种实现方式
通过注解绑定，就是在接口的方法上面加上 @Select、@Update等注解，里面包含Sql语句来绑定；
通过xml里面写SQL来绑定， 在这种情况下，要指定xml映射文件里面的namespace必须为接口的全路径名。当Sql语句比较简单时候，用注解绑定， 当SQL语句比较复杂时候，用xml绑定，一般用xml绑定的比较多。
07.使用MyBatis的mapper接口调用时有哪些要求？
1.Mapper接口方法名和mapper.xml中定义的每个sql的id相同。
2.Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql 的parameterType的类型相同。
3.Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同。
4.Mapper.xml文件中的namespace即是mapper接口的类路径。
4.2 Mapper编写方式
01.第一种：接口实现类继承 SqlSessionDaoSupport：使用此种方法需要编写mapper 接口，mapper 接口实现类、mapper.xml 文件。
a.在 sqlMapConfig.xml 中配置 mapper.xml 的位置
<mappers>
<mapper resource="mapper.xml 文件的地址" />
<mapper resource="mapper.xml 文件的地址" />
</mappers>
b.定义 mapper 接口
c.实现类集成 SqlSessionDaoSupport
mapper 方法中可以 this.getSqlSession()进行数据增删改查。
d.spring 配置
<bean id=" " class="mapper 接口的实现">
<property name="sqlSessionFactory"
ref="sqlSessionFactory"></property>
</bean>
02.第二种：使用 org.mybatis.spring.mapper.MapperFactoryBean
a.在 sqlMapConfig.xml 中配置 mapper.xml 的位置，如果 mapper.xml 和mappre 接口的名称相同且在同一个目录，这里可以不用配置
<mappers>
<mapper resource="mapper.xml 文件的地址" />
<mapper resource="mapper.xml 文件的地址" />
</mappers>
b.定义 mapper 接口
c.mapper.xml 中的 namespace 为 mapper 接口的地址
d.mapper 接口中的方法名和 mapper.xml 中的定义的 statement 的 id 保持一致
e.Spring 中定义
<bean id="" class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="mapperInterface" value="mapper 接口地址" />
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
03.第三种：使用 mapper 扫描器
a.mapper.xm<x>l 文件编写
mapper.xm<x>l 中的 namespace 为 mapper 接口的地址
mapper 接口中的方法名和 mapper.xm<x>l 中的定义的 statement 的 id 保持一致；
如果将 mapper.xm<x>l 和 mapper 接口的名称保持一致则不用在 sqlMapConfig.xm<x>l中进行配置。
b.定义 mapper 接口
注意 mapper.xm<x>l 的文件名和 mapper 的接口名称保持一致，且放在同一个目录
c.配置 mapper 扫描器
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="ba<x>sePackage" value="mapper 接口包地址
"></property>
<property name="sqlSessionFactoryBeanName"
value="sqlSessionFactory"/>
</bean>
4.3 Student.java
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
public Student() {
}
public Student(int stuNo, String stuName, int stuAge, String graName) {
this.stuNo = stuNo;
this.stuName = stuName;
this.stuAge = stuAge;
this.graName = graName;
}
public int getStuNo() {
return stuNo;
}
public void setStuNo(int stuNo) {
this.stuNo = stuNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getstuAge() {
return stuAge;
}
public void setstuAge(int stuAge) {
this.stuAge = stuAge;
}
public String getGraName() {
return graName;
}
public void setGraName(String graName) {
this.graName = graName;
}
public String toString() {
return this.stuNo + "-" + this.stuName + "-" + this.stuAge + "-" + this.graName;
}
}
4.4 conf.xml
01.conf.xml
<?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="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载映射文件 -->
<mapper resource="org/myslayers/mapper/StudentMapper.xml" />
</mappers>
</configuration>
4.5 StudentMapper.java
01.StudentMapper.java
//操作MyBatis的接口
public interface StudentMapper {
//增加
void addStudent(Student student);
//删除
void deleteStudentBySno(int stuNo);
//更新
void updateStudentBySno(Student student);
//查看单个
Student queryStudentBySno(int stuNo);
//查询全部
List<Student> queryAllStudents();
}
4.6 StudentMapper.xml
01.StudentMapper.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="org.myslayers.mapper.StudentMapper">
<!-- 增:无resultType -->
<insert id="addStudent" parameterType="org.myslayers.entity.Student">
insert into student(stuno, stuname, stuage, graname) values(#{stuNo}, #{stuName}, #{stuAge}, #{graName})
</insert>
<!-- 删:无resultType -->
<delete id="deleteStudentBySno" parameterType="int">
delete from student where stuno = #{stuNo}
</delete>
<!-- 改:无resultType-->
<update id="updateStudentBySno" parameterType="org.myslayers.entity.Student" >
update student set stuname = #{stuName}, stuage = #{stuAge}, graname=#{graName} where stuno=#{stuNo}
</update>
<!-- 查：全部信息，无parameterType，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryAllStudents" resultType="org.myslayers.entity.Student" >
select * from student
</select>
<!-- 查：单个信息，对象类型无论返回一个还是多个，都是resultType="org.myslayers.entity.Student" -->
<select id="queryStudentBySno" parameterType="int" resultType="org.myslayers.entity.Student" >
select * from student where stuno = #{stuNo}
</select>
</mapper>
4.7 Test.java
01.Test.java
public class Test {
// 查询单个学生
public static void queryStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
// 动态代理getMapper + 反射xxx.class
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySno(1);
System.out.println(student);
session.close();
}
// 查询全部学生
public static void queryAllStudent() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<Student> students = studentMapper.queryAllStudents();
System.out.println(students);
session.close();
}
// 增加一个学生
public static void addStudent() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student stu = new Student(5,"ll",23,"c2");
studentMapper.addStudent(stu);
session.commit();
System.out.println(stu);
session.close();
}
// 删除学生
public static void deleteStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
studentMapper.deleteStudentBySno(3);
session.commit();
System.out.println("删除成功！");
session.close();
}
// 修改学生
public static void updateStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
// 修改参数
Student student = new Student();
// 修改哪个人，where stuno=2
student.setStuNo(2);
// 修改成什么样子
student.setStuName("lxs");
student.setstuAge(44);
student.setGraName("s2");
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
studentMapper.updateStudentBySno(student);
session.commit();
System.out.println("修改成功！");
session.close();
}
public static void main(String[] args) throws IOException {
// queryStudentBySno();
// queryAllStudent();
// addStudent();
// deleteStudentBySno();
updateStudentBySno();
}
}
5 优化Mapper动态代理方式CRUD
5.1 汇总
00.汇总：StudentMapper.java 对应 StudentMapper.xml
映射：对象名 对应 表名、属性 对应 字段名
+ conf.xml配置mapper，<mapper resource="org/myslayers/entity/PersonMapper.xml" />
+ 动态代理getMapper+反射xxx.class StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySno(1);
+ StudentMapper.xml中parameterType/resultType，使用“设置单个别名/批量设置别名（parameterType="student"）”
+ conf.xml抽离出db.properties
01.图示
内置别名 映射的类型 内置别名 映射的类型
_byte byte _double double
_long long _float float
_short short _boolean boolean
_int int string String
_integer int byte Byte
long Long short Short
int Integer double Double
integer Integer float Float
boolean Boolean date Date
decimal BigDecimal bigdecimal BigDecimal
object Object map Map
hashmap HashMap list List
arraylist ArrayList collection Collection
02.三种别名
a.int内置别名
<select id="findUserByld" parameterType="int" resultMap="userResultMap">
select id, username from user where id#(id)
</select>
b.String内置别名
<select id="findUserByName" parameterType="java.lang.String" resultType="user">
select from user where username like '%$(value)%'
</select>
c.自定义别名
<typeAliases>
<typeAlias type="me.gacl.domain.User" alias="_User"/>
</typeAliases>
<insert id="addUser2" parameterType="_User">
insert into users(name,age) values(#{name},#{age})
</insert>
5.2 Student.java
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
public Student() {
}
public Student(int stuNo, String stuName, int stuAge, String graName) {
this.stuNo = stuNo;
this.stuName = stuName;
this.stuAge = stuAge;
this.graName = graName;
}
public int getStuNo() {
return stuNo;
}
public void setStuNo(int stuNo) {
this.stuNo = stuNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getstuAge() {
return stuAge;
}
public void setstuAge(int stuAge) {
this.stuAge = stuAge;
}
public String getGraName() {
return graName;
}
public void setGraName(String graName) {
this.graName = graName;
}
public String toString() {
return this.stuNo + "-" + this.stuName + "-" + this.stuAge + "-" + this.graName;
}
}
5.3 conf.xml
01.conf.xml
<?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="db.properties"/>
<!-- 设置单个/多个别名 -->
<typeAliases>
<!-- 单个别名 -->
<typeAlias type="org.myslayers.entity.Student" alias="student"/>
<!-- 批量定义别名，以下会自动将该包中的所有类批量定义别名： 别名就是类名（不带包名的类名） -->
<package name="org.myslayers.entity"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<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="org/myslayers/mapper/StudentMapper.xml" />
</mappers>
</configuration>
5.4 db.properties
01.db.properties
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL
username=scott
password=tiger
5.5 StudentMapper.java
01.StudentMapper.java
//操作MyBatis的接口
public interface StudentMapper {
//增加
void addStudent(Student student);
//删除
void deleteStudentBySno(int stuNo);
//更新
void updateStudentBySno(Student student);
//查看单个
Student queryStudentBySno(int stuNo);
//查询全部
List<Student> queryAllStudents();
}
5.6 StudentMapper.xml
01.StudentMapper.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="org.myslayers.mapper.StudentMapper">
<!-- 增:无resultType -->
<insert id="addStudent" parameterType="student">
insert into student(stuno, stuname, stuage, graname) values(#{stuNo}, #{stuName}, #{stuAge}, #{graName})
</insert>
<!-- 删:无resultType -->
<delete id="deleteStudentBySno" parameterType="int">
delete from student where stuno = #{stuNo}
</delete>
<!-- 改:无resultType-->
<update id="updateStudentBySno" parameterType="student" >
update student set stuname = #{stuName}, stuage = #{stuAge}, graname=#{graName} where stuno=#{stuNo}
</update>
<!-- 查：全部信息，无parameterType，对象类型无论返回一个还是多个，都是resultType="student" -->
<select id="queryAllStudents" resultType="student" >
select * from student
</select>
<!-- 查：单个信息，对象类型无论返回一个还是多个，都是resultType="student" -->
<select id="queryStudentBySno" parameterType="int" resultType="student" >
select * from student where stuno = #{stuNo}
</select>
</mapper>
5.7 Test.java
01.Test.java
public class Test {
// 查询单个学生
public static void queryStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
// 动态代理getMapper + 反射xxx.class
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySno(1);
System.out.println(student);
session.close();
}
// 查询全部学生
public static void queryAllStudent() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<Student> students = studentMapper.queryAllStudents();
System.out.println(students);
session.close();
}
// 增加一个学生
public static void addStudent() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student stu = new Student(5,"ll",23,"c2");
studentMapper.addStudent(stu);
session.commit();
System.out.println(stu);
session.close();
}
// 删除学生
public static void deleteStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
studentMapper.deleteStudentBySno(3);
session.commit();
System.out.println("删除成功！");
session.close();
}
// 修改学生
public static void updateStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
// 修改参数
Student student = new Student();
// 修改哪个人，where stuno=2
student.setStuNo(2);
// 修改成什么样子
student.setStuName("lxs");
student.setstuAge(44);
student.setGraName("s2");
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
studentMapper.updateStudentBySno(student);
session.commit();
System.out.println("修改成功！");
session.close();
}
public static void main(String[] args) throws IOException {
// queryStudentBySno();
// queryAllStudent();
// addStudent();
// deleteStudentBySno();
updateStudentBySno();
}
}
6 类型处理器：内置、自定义
6.1 汇总
00.汇总：StudentMapper.java 对应 StudentMapper.xml
映射：对象名 对应 表名、属性 对应 字段名
+ conf.xml配置mapper，<mapper resource="org/myslayers/entity/PersonMapper.xml" />
+ 动态代理getMapper+反射xxx.class StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySno(1);
+ StudentMapper.xml中parameterType/resultType，使用“设置单个别名/批量设置别名（parameterType="student"）”
+ conf.xml抽离出db.properties
+ 自带常见类型转换器 / 自定义MyBatis类型处理器
01.内置常见类型转换器
| 类型处理器 | Java类型 | JDBC类型 |
| ---------------------------- | --------------------- | ----------------------------------------------------------- |
| BooleanTypeHandler | Boolean，boolean | 任何兼容的布尔值 |
| ByteTypeHandler | Byte，byte | 任何兼容的数字或字节类型 |
| ShortTypeHandler | Short，short | 任何兼容的数字或短整型 |
| IntegerTypeHandler | Integer，int | 任何兼容的数字和整型 |
| LongTypeHandler | Long，long | 任何兼容的数字或长整型 |
| FloatTypeHandler | Float，float | 任何兼容的数字或单精度浮点型 |
| DoubleTypeHandler | Double，double | 任何兼容的数字或双精度浮点型 |
| BigDecimalTypeHandler | BigDecimal | 任何兼容的数字或十进制小数类型 |
| StringTypeHandler | String | CHAR和VARCHAR类型 |
| ClobTypeHandler | String | CLOB和LONGVARCHAR类型 |
| NStringTypeHandler | String | NVARCHAR和NCHAR类型 |
| NClobTypeHandler | String | NCLOB类型 |
| ByteArrayTypeHandler | byte[] | 任何兼容的字节流类型 |
| BlobTypeHandler | byte[] | BLOB和LONGVARBINARY类型 |
| DateTypeHandler | Date（java.util） | TIMESTAMP类型 |
| DateOnlyTypeHandler | Date（java.util） | DATE类型 |
| TimeOnlyTypeHandler | Date（java.util） | TIME类型 |
| SqlTimestampTypeHandler | Timestamp（java.sql） | TIMESTAMP类型 |
| SqlDateTypeHandler | Date（java.sql） | DATE类型 |
| SqlTimeTypeHandler | Time（java.sql） | TIME类型 |
| ObjectTypeHandler | 任意 | 其他或未指定类型 |
| EnumTypeHandler | Enumeration类型 | VARCHAR。任何兼容的字符串类型，作为代码存储（而不是索引）。 |
02.映射Enum枚举类
Mybatis可以映射枚举类，不单可以映射枚举类，Mybatis可以映射任何对象到表的一列上。映射方式为自定义一个TypeHandler，实现TypeHandler的setParameter()和getResult()接口方法。
TypeHandler有两个作用，一是完成从javaType至jdbcType的转换，二是完成jdbcType至javaType的转换，体现为setParameter()和getResult()两个方法，分别代表设置sql问号占位符参数和获取列查询结果。
6.2 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
insert into student values(3, 'ww', 23, 'g1');
insert into student values(4, 'zl', 24, 'g1');
commit;
--增加一列stusex
alter table student add stusex number;
update student set stusex=0;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
public Student() {
}
public Student(int stuNo, String stuName, int stuAge, String graName, boolean stuSex) {
this.stuNo = stuNo;
this.stuName = stuName;
this.stuAge = stuAge;
this.graName = graName;
this.stuSex = stuSex;
}
public int getStuNo() {
return stuNo;
}
public void setStuNo(int stuNo) {
this.stuNo = stuNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getStuAge() {
return stuAge;
}
public void setStuAge(int stuAge) {
this.stuAge = stuAge;
}
public String getGraName() {
return graName;
}
public void setGraName(String graName) {
this.graName = graName;
}
public boolean isStuSex() {
return stuSex;
}
public void setStuSex(boolean stuSex) {
this.stuSex = stuSex;
}
public String toString() {
return this.stuNo + "-" + this.stuName + "-" + this.stuAge + "-" + this.graName + "-性别" + this.stuSex;
}
}
6.3 自定义：DBtoJava
01.自定义MyBatis类型处理器（DBtoJava）
a.创建转换器，需要实现TypeHandler接口，通过阅读源码发现，此接口有一个实现类BaseTypeHandler，因此实现转换器有两种选择：
①实现接口TypeHandler接口
②继承BaseTypeHandler
//BaseTypeHandler<java类型>
public class BooleanAndIntConverter extends BaseTypeHandler<Boolean>{
/*
* 1.ps：PreparedStatement对象
* 2.i：PreparedStatement对象操作参数的位置
* 3.parameter：java值（true/false）
* 4.jdbcType：jabc操作的数据库类型（1/0）
*/
//java(boolean) -> DB(number)
public void setNonNullParameter(PreparedStatement ps, int i, Boolean parameter, JdbcType jdbcType)
throws SQLException {
if (parameter) {
ps.setInt(i, 1);
}else {
ps.setInt(i, 0);
}
}
//DB(number) -> java(boolean)
//列名：rs.getInt("stuno")
public Boolean getNullableResult(ResultSet rs, String columnName) throws SQLException {
int sexNum = rs.getInt(columnName);
return sexNum==0? true: false;
}
//下标：rs.getInt(1)
public Boolean getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
int sexNum = rs.getInt(columnIndex);
return sexNum==0? true: false;
}
//存储过程：CallableStatement
public Boolean getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
int sexNum = cs.getInt(columnIndex);
return sexNum==0? true: false;
}
}
b.配置conf.xml
<!-- 转换器 -->
<typeHandlers>
<typeHandler handler="org.myslayers.converter.BooleanAndIntConverter" javaType="Boolean" jdbcType="INTEGER"/>
</typeHandlers>
c.配置StudentMapper.xml
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- 查询：使用类型转换器
情况一：如果类中属性 和 表中的字段类型都能合理识别(string-varchar2)，则可以使用resultType;
否则(boolean-number)，则使用resultMap
情况二：如果类中属性名 和 表中的字段名都能合理识别(stuNo-stuno)，则可以使用resultType;
否则(id-stuno)，则使用resultMap
-->
<!-- 本次使用情况一 -->
<!-- 查询（转换器） -->
<select id="queryStudentBySnoWithConverter" parameterType="int" resultMap="studentResult">
select * from student where stuno = #{stuNo}
</select>
<!-- 指定resultMap -->
<resultMap type="Student" id="studentResult">
<!-- 主键 -->
<id property="stuNo" column="stuNo"/>
<!-- 非主键 -->
<result property="stuName" column="stuName"/>
<result property="stuAge" column="stuAge"/>
<result property="graName" column="graName"/>
<result property="stuSex" column="stuSex" javaType="boolean" jdbcType="INTEGER"/>
</resultMap>
</mapper>
d.测试
public class Test {
// 查询单个学生(使用转换器)
public static void queryStudentBySnoWithConverter() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
// 动态代理getMapper + 反射xxx.class
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySnoWithConverter(1);
System.out.println(student);
session.close();
}
public static void main(String[] args) throws IOException {
queryStudentBySnoWithConverter();
}
}
6.4 自定义：JavaToDB
01.自定义MyBatis类型处理器（JavaToDB）
a.创建转换器，需要实现TypeHandler接口，通过阅读源码发现，此接口有一个实现类BaseTypeHandler，因此实现转换器有两种选择：
①实现接口TypeHandler接口
②继承BaseTypeHandler
//BaseTypeHandler<java类型>
public class BooleanAndIntConverter extends BaseTypeHandler<Boolean>{
/*
* 1.ps：PreparedStatement对象
* 2.i：PreparedStatement对象操作参数的位置
* 3.parameter：java值（true/false）
* 4.jdbcType：jabc操作的数据库类型（1/0）
*/
//java(boolean) -> DB(number)
public void setNonNullParameter(PreparedStatement ps, int i, Boolean parameter, JdbcType jdbcType)
throws SQLException {
if (parameter) {
ps.setInt(i, 1);
}else {
ps.setInt(i, 0);
}
}
//DB(number) -> java(boolean)
//列名：rs.getInt("stuno")
public Boolean getNullableResult(ResultSet rs, String columnName) throws SQLException {
int sexNum = rs.getInt(columnName);
return sexNum==0? true: false;
}
//下标：rs.getInt(1)
public Boolean getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
int sexNum = rs.getInt(columnIndex);
return sexNum==0? true: false;
}
//存储过程：CallableStatement
public Boolean getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
int sexNum = cs.getInt(columnIndex);
return sexNum==0? true: false;
}
}
b.配置conf.xml
<!-- 转换器 -->
<typeHandlers>
<typeHandler handler="org.myslayers.converter.BooleanAndIntConverter" javaType="Boolean" jdbcType="INTEGER"/>
</typeHandlers>
c.配置StudentMapper.xml
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- 增加（转换器） -->
<insert id="addStudentWithConverter" parameterType="student">
insert into student(stuno, stuname, stuage, graname, stusex) values(#{stuNo}, #{stuName}, #{stuAge}, #{graName}, #{stuSex, javaType=boolean, jdbcType=INTEGER})
</insert>
</mapper>
d.测试
public class Test {
// 增加一个学生(使用转换器)
public static void addStudentWithConverter() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student stu = new Student(5,"ll",23,"c2",true);//true：男
studentMapper.addStudentWithConverter(stu);
session.commit();
System.out.println(stu);
session.close();
}
public static void main(String[] args) throws IOException {
addStudentWithConverter();
}
}
7 parameterType：输入
7.1 汇总
01.parameterType（输入参数）
a.简单类型(8个基本类型+引用类型String)
#{任意值}
${任意值}，只能是value
b.对象类型
#{属性名}
${属性名}
02.推荐使用parameterType而非parameterMap
ParameterMap和resultMap类似，表示将查询结果集中列值的类型一一映射到java对象属性的类型上，在开发过程中不推荐这种方式。
一般使用parameterType直接将查询结果列值类型自动对应到java对象属性类型上，不再配置映射关系一一对应，
例如上述代码中下划线部分表示将查询结果类型自动对应到hdu.terence.bean.Message的Bean对象属性类型。
7.2 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
insert into student values(3, 'ww', 23, 'g1');
insert into student values(4, 'zl', 24, 'g1');
commit;
--增加一列stusex
alter table student add stusex number;
update student set stusex=0;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
-- 增加列homeaddress、schooladdress
alter table student add homeaddress varchar2(20);
alter table student add schooladdress varchar2(20);
-- 增加数据
update student set homeaddress='xa' where stuno=1;
update student set homeaddress='bj' where stuno>1;
update student set schooladdress='x' where stuno>1;
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
...
}
public class Address {
private String homeAddress;
private String schoolAddress;
...
}
7.3 #{}与${}的不同点：简单类型，8个基本类型+引用类型String
01.#{}与${}的不同点（输入：简单类型，8个基本+String）
a.区别一
a.#{任意值}
<select id="queryStudentBySno" parameterType="int" resultType="student" >
select * from student where stuno = #{aaa}
</select>
b.${value}，只能是value
<select id="queryStudentBySno" parameterType="int" resultType="student" >
select * from student where stuno = ${value}
</select>
b.区别二
a.#{}自动给String类型加上''单引号（自动类型转换）
<select id="queryStudentByName" parameterType="String" resultType="student" >
select stuno,stuname,stuage from student where stuname = #{stuName}
</select>
-------------------------------------------------------------------------------------------------
当实参username="Amy"时，
SQL将解析为：select stuno,stuname,stuage from student where stuname = "Amy"
b.${}原样输出，可以手动单引号，但是适合于动态排序（动态字段）
<select id="queryStudentByName" parameterType="String" resultType="student" >
select stuno,stuname,stuage from student where stuname = '${value}'
</select>
-------------------------------------------------------------------------------------------------
当实参username="Amy"时，
SQL将解析为：select stuno,stuname,stuage from student where stuname = 'Amy'
c.区别三
a.#{}无法动态排序，报错
<select id="queryStudentorderByColumn" parameterType="string" resultType="student">
select stuno,stuname,stuage from student order by #{column} asc
</select>
-------------------------------------------------------------------------------------------------
当实参username="sex"时，select stuno,stuname,stuage from student order by 'sex' asc
很明显ORDER子句的字段名错误的被加上了引号，致使查询结果没有按期排序输出
b.${}可以动态排序
<select id="queryStudentorderByColumn" parameterType="string" resultType="student">
select stuno,stuname,stuage from student order by ${value} asc
</select>
-------------------------------------------------------------------------------------------------
当实参username="sex"时，select stuno,stuname,stuage from student order by sex asc
很明显ORDER子句，符合SQL语法
d.区别四
#{}可以防止SQL注入，自动添加了''引号
相当于“定义好SQL，去填写”
相当于PreparedStatement，先进行预编译，然后再将参数中的内容替换进来
#{}传入参数是以字符串传入，会将SQL中的#{}替换为?号，调用PreparedStatement的set方法去赋值
-----------------------------------------------------------------------------------------------------
${}不可以防止SQL注入，就是字符串拼接
相当于“自己写规则定义sql”
相当于Statement
${}传入参数是原值传入，就是把{}替换成变量的值
7.4 #{}与${}的相同点：对象、对象数组、嵌套对象、HashMap
01.#{}与${}的相同点（输入：对象、对象集合、嵌套[级联查询]、HashMap）
a.输入：对象
a.模糊查询1
<!-- 查询：姓名or年龄 -->
<select id="queryStudentBystuageOrstuname" parameterType="student" resultType="student" >
select stuno,stuname,stuage from student where stuage=#{stuAge} or stuname like #{stuName}
</select>
//查询：姓名or年龄
List<Student> queryStudentBystuageOrstuname(Student student);
// 查询：姓名or年龄
public static void queryStudentBystuageOrstuname() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = new Student();
student.setStuAge(24);
student.setStuName("%z%");// 模糊查询
List<Student> students = studentMapper.queryStudentBystuageOrstuname(student);
System.out.println(students);
session.close();
}
b.模糊查询2
<!-- 查询：姓名or年龄 -->
<select id="queryStudentBystuageOrstuname" parameterType="student" resultType="student" >
select stuno,stuname,stuage from student where stuage=#{stuAge} or stuname like '%${stuName}%'
</select>
//查询：姓名or年龄
List<Student> queryStudentBystuageOrstuname(Student student);
// 查询：姓名or年龄
public static void queryStudentBystuageOrstuname() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = new Student();
student.setStuAge(24);
student.setStuName("z");// 模糊查询
List<Student> students = studentMapper.queryStudentBystuageOrstuname(student);
System.out.println(students);
session.close();
}
b.输入：对象数组
<!-- 动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2} -->
<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="student" separator=",">
#{student.stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2}
List<Student> queryStudentsWithObjectArray(Student[] students);
//动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2}
public static void queryStudentsWithObjectArray() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//对象数组
Student stu1 = new Student();
Student stu2 = new Student();
Student stu3 = new Student();
stu1.setStuNo(1);
stu2.setStuNo(2);
stu3.setStuNo(3);
Student[] stus = new Student[] {stu1, stu2, stu3} ;
List<Student> students = studentMapper.queryStudentsWithObjectArray(stus);
System.out.println(students);
session.close();
}
c.输入：嵌套[级联查询]，拼接出SQL为目的
a.嵌套对象
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
}
public class Address {
private String homeAddress;
private String schoolAddress;
}
-------------------------------------------------------------------------------------------------
SCOTT@ORCL>desc student;
名称 是否为空? 类型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
STUNO NUMBER
STUNAME VARCHAR2(20)
STUAGE NUMBER
GRANAME VARCHAR2(20)
STUSEX NUMBER
HOMEADDRESS VARCHAR2(20)
SCHOOLADDRESS VARCHAR2(20)
b.Address来查询Student：直接输入
<select id="queryStudentByaddress" parameterType="address" resultType="student">
select stuno,stuname,stuage from student where homeaddress=#{homeAddress} or schooladdress=#{schoolAddress}
</select>
//查询：嵌套属性Address来查询Student
List<Student> queryStudentByaddress(Address address);
public static void queryStudentByaddress() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Address address = new Address();
address.setHomeAddress("xa");
address.setSchoolAddress("x");
List<Student> students = studentMapper.queryStudentByaddress(address);
System.out.println(students);
session.close();
}
c.Student嵌套Address（参数为级联属性）：级联输入
<select id="queryStudentByaddress" parameterType="student" resultType="student" >
select stuno,stuname,stuage from student where homeaddress=#{address.homeAddress} or schooladdress='${address.schoolAddress}'
</select>
//查询：Student嵌套Address 级联属性
List<Student> queryStudentByaddress(Student address);
public static void queryStudentByaddress() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = new Student();
Address address = new Address();
address.setHomeAddress("xa");
address.setSchoolAddress("x");
student.setAddress(address);
List<Student> students = studentMapper.queryStudentByaddress(student);
System.out.println(students);
session.close();
}
d.输入：HashMap（put添加属件）
a.嵌套对象
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
}
public class Address {
private String homeAddress;
private String schoolAddress;
}
b.用map中key的值匹配占位符#{stuAge}，如果匹配成功就用map的value替换占位符
<select id="queryStudentByaddressWithHashMap" parameterType="HashMap" resultType="student" >
select stuno,stuname,stuage from student where stuage=#{stuAge} or stuname=#{stuName}
</select>
//查询：输入参数HashMap
List<Student> queryStudentByaddressWithHashMap(Map<String,Object> map);
public static void queryStudentByaddressWithHashMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Map<String, Object> studentMap = new HashMap<String, Object>();
studentMap.put("stuAge", 24);
studentMap.put("stuName", "zs");
List<Student> students = studentMapper.queryStudentByaddressWithHashMap(studentMap);
System.out.println(students);
session.close();
}
c.结果
[1-zs-24-null-性别false, 2-zs-24-null-性别false]
7.5 [重]传递参数：4种
00.汇总
a.输入：简单类型，8个基本+String
#{任意值}
b.parameterType配置参数
【在一个参数时可以忽略（可写可不写）】
多个相同参数要写
多个不同参数不写
c.返回值
查 resultType="student" / resultMap="employeeResult"
增改 void
删 void
01.配置文件
mybatis:
#mapper配置文件
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.sky.entity
configuration:
#开启驼峰命名
map-underscore-to-camel-case: true
02.流程
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
useGeneratedKeys参数只针对 insert 语句生效，默认为 false；
useGeneratedKeys设置为 true 时，表示如果插入的表id以自增列为主键，允许 JDBC 支持自动生成主键；
keyProperty="id"代表可将自动生成的主键id返回，keyProperty的值是对象的属性值而不是数据库表中的字段名；
@Insert("insert into user(name,age) values (#{name},#{age})")
@Options(useGeneratedKeys = true,keyProperty = "id")
void insert(User user);
// 向菜品表插入1条数据
dishMapper.insert(dish);
// 获取insert语句生成的主键值
Long dishId = dish.getId();
03.foreach的collection属性
a.定义
foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合
-----------------------------------------------------------------------------------------------------
如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可
b.示例1
public List<Blog> dynamicForeachTest(List<Integer> ids);
<select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
select * from t_blog where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
c.示例2
public List<Blog> dynamicForeach2Test(int[] ids);
<select id="dynamicForeach2Test" parameterType="java.util.ArrayList" resultType="Blog">
select * from t_blog where id in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
d.示例4
int batchInsert(@Param("list") List<User> list);
<insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
<!--@mbg.generated-->
insert into m_user
(username, `password`, email, gender, avatar, `status`, lasted, created, modified
)
values
<foreach collection="list" item="item" separator=",">
(#{item.username}, #{item.password}, #{item.email}, #{item.gender}, #{item.avatar},
#{item.status}, #{item.lasted}, #{item.created}, #{item.modified})
</foreach>
</insert>
e.示例5
int deleteByIds(Long[] ids);
<delete id="deleteByIds" parameterType="String">
delete from m_user where id in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
90.方法0：单个传参法
a.示例1
<select id="getById" parameterType="Long" resultType="Employee">
SELECT
<include refid="EmployeeVo"/>
FROM employee
WHERE id = #{id,jdbcType=BIGINT}
</select>
b.示例2
<select id="getByUsername" parameterType="String" resultMap="employeeResult">
SELECT
<include refid="EmployeeVo"/>
FROM employee
WHERE username = #{username,jdbcType=VARCHAR}
</select>
91.方法1：顺序传参法
a.示例
public User selectUser(String name, int deptId);
<select id="selectUser" resultMap="UserResultMap">
select * from user
where user_name = #{0} and dept_id = #{1}
</select>
b.说明
#{}里面的数字代表传入参数的顺序
这种方法不建议使用，sql层表达不直观，且一旦顺序调整容易出错
92.方法2：@Param注解传参法
a.示例
public User selectUser(@Param("userName") String name, int @Param("deptId") deptId);
<select id="selectUser" resultMap="UserResultMap">
select * from user
where user_name = #{userName} and dept_id = #{deptId}
</select>
b.说明
#{}里面的名称对应的是注解@Param括号里面修饰的名称。
这种方法在参数不多的情况还是比较直观的，推荐使用。
93.方法3：Map传参法
a.示例
@PostMapping("/test2")
public ResponseEntity getMapBody(@RequestBody Map<String, Object> params) {
return postService.getMapBody(params);
}
public ResponseEntity getMapBody(Map<String, Object> params) {
String title = params.get("title");
String content = params.get("content");
List<Map> ls = postMapper.updateByPrimaryKey2(params);
for (Map map : ls) {
String title = MapUtil.getStr(map, "title");
String content = MapUtil.getStr(map, "content");
}
return ResponseEntity.ok(ls);
}
List<Map> updateByPrimaryKey2(Map<String, Object> params);
<select id="selectUser" parameterType="java.util.Map" resultMap="UserResultMap">
select * from user
where user_name = #{userName} and dept_id = #{deptId}
</select>
b.说明
#{}里面的名称对应的是Map里面的key名称。
这种方法适合传递多个参数，且参数易变能灵活传递的情况。
94.方法4：Java Bean传参法
a.代码
public User selectUser(User user);
<select id="selectUser" parameterType="com.jourwon.pojo.User" resultMap="UserResultMap">
select * from user
where user_name = #{userName} and dept_id = #{deptId}
</select>
-----------------------------------------------------------------------------------------------------
#{}里面的名称对应的是User类里面的成员属性。
这种方法直观，需要建一个实体类，扩展不容易，需要加属性，但代码可读性强，业务逻辑处理方便，推荐使用。
b.代码
public User selectUser(User user);
<select id="selectUser" resultMap="UserResultMap">
select * from user
where user_name = #{userName} and dept_id = #{deptId}
</select>
parameterType配置参数：【在一个参数时可以忽略（可写可不写）】
7.6 [重]输入类型案例：3种
00.parameterType 配置参数
a.参数的使用说明
SQL语句传参，使用标签的parameterType属性来设定。
该属性的取值可以是基本类型，引用类型（例如:String 类型），还可以是实体类类型（POJO 类）。
同时也可以使用实体类的包装类
b.参数配置的注意事项
parameterType配置参数：
【在一个参数时可以忽略（可写可不写）】
多个相同参数要写
多个不同参数不写
-----------------------------------------------------------------------------------------------------
基本类型和String可以直接写类型名称也可以使用包名.类名的方式，例如：java.lang.String。
实体类类型，目前我们只能使用全限定类名。
究其原因，是mybaits在加载时已经把常用的数据类型注册了别名，从而我们在使用时可以不写包名，而我们的是实体类并没有注册别名，所以必须写全限定类名。
00.输入类型案例：3种
a.一个参数
只有一个参数一个值【8个基本类型+引用类型String】时，使用#{value}
使用对象封装的一个参数多个值 / 使用(bean、pojo或entity来指代实体类)封装的一个参数多个值
对象数组
-----------------------------------------------------------------------------------------------------
Array数组 1.数组，Array {'1','2','3','4'}
Collection集合 = List列表封装in + Set列表封装in 2.集合，Collection = List，ArrayList + Set，HashSet ['1','2','3','4']
Map封装的一个参数多个值、Map封装的一个参数多个值（复杂） 3.Map，HashMap [id="11234",code="ABCD"]
-----------------------------------------------------------------------------------------------------
collection: 表示对哪一个集合或数组做迭代；如果参数是数组类型，此时Map的key为array；如果参数是List类型，此时Map的key为list
item: 变量名。即从迭代的对象中取出的每一个值
index: 索引的属性名。当迭代的对象为Map时，该值为Map中的Key
open: 循环开头的字符串
close: 循环结束的字符串
separator: 每次循环的分隔符
b.使用@param指定一个参数/多个参数
一个参数
多个参数（重点）
c.多个参数，基于参数顺序
参数顺序，{0}、{1}
混合参数类型
01.一个参数
a.只有一个参数一个值【8个基本类型+引用类型String】时，使用#{value}
数据对象：
8个基本类型+引用类型String
-----------------------------------------------------------------------------------------------------
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(String name);
}
-----------------------------------------------------------------------------------------------------
mapper.xml：
<select id="getUserList" parameterType="java.lang.String" resultType="SysUser">
select t.* from sys_user t where t.name= #{value}
</select>
-------------------------------------------------------------------------------------------------
<select id="getUserList" parameterType="java.lang.String" resultType="SysUser">
select t.* from sys_user t where t.name= #{aaa}
</select>
-------------------------------------------------------------------------------------------------
<select id="getUserList" parameterType="java.lang.String" resultType="SysUser">
select t.* from sys_user t where t.name= #{任意名}
</select>
b.使用对象封装的一个参数多个值 / 使用(bean、pojo或entity来指代实体类)封装的一个参数多个值
数据对象：
public class UserQueryVO {
private Integer id;
private String code;
// getter/setter....
}
-----------------------------------------------------------------------------------------------------
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(UserQueryVO userQueryVO);
}
-----------------------------------------------------------------------------------------------------
mapper.xml：
<select id="getUserList" parameterType="com.xxx.entity.vo.UserQueryVO" resultType="SysUser">
select t.* from sys_user t where id=#{id} code = #{code}
</select>
c.对象数组
<!-- 动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2} -->
<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="student" separator=",">
#{student.stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2}
List<Student> queryStudentsWithObjectArray(Student[] students);
//动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2}
public static void queryStudentsWithObjectArray() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//对象数组
Student stu1 = new Student();
Student stu2 = new Student();
Student stu3 = new Student();
stu1.setStuNo(1);
stu2.setStuNo(2);
stu3.setStuNo(3);
Student[] stus = new Student[] {stu1, stu2, stu3} ;
List<Student> students = studentMapper.queryStudentsWithObjectArray(stus);
System.out.println(students);
session.close();
}
c.Map封装的一个参数多个值
数据对象：
HashMap <String, Object> params = new HashMap<String, Object>();
params.put("id", "1234");
params.put("code ", "ABCD");
-----------------------------------------------------------------------------------------------------
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(Map params);
}
-----------------------------------------------------------------------------------------------------
mapper.xml：
<select id="getUserList" parameterType="map" resultType="SysUser">
select t.* from sys_user t where id=#{id} and code = #{code}
</select>
d.Map封装的一个参数多个值（复杂）
数据对象：如果参数既要包含String类型，又包含List类型，使用Map来封装参数，将参数放入Map，再取出Map中的List遍历。
// 定义一个map
Map<String, Object> params = new HashMap<String, Object>();
params.put("status", "0");
// List类型
List<String> ids= new ArrayList<String>();
ids.add("1");
ids.add("2");
params.put("ids", ids);
-----------------------------------------------------------------------------------------------------
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(Map params);
}
-----------------------------------------------------------------------------------------------------
mapper.xml：
<select id="getUserList" parameterType="map" resultType="SysUser">
select t.*
from sys_user t
WHERE t.status = #{status}
<if test=' ids != null and ids.size() > 0 '>
and t.id not in
<foreach collection="ids" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
</select>
e.List列表封装in / Collection集合
数据对象：
idsIn = ['1','2','3','4']
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(List<String> idsIns);
}
mapper.xml：
<select id="getUserList" resultType="SysUser">
select t.* from sys_user t
where id in
<foreach collection="idsIns" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
foreach最后效果：
select 字段... from XXX where id in ('1','2','3','4')
-----------------------------------------------------------------------------------------------------
<!-- 动态SQL: foreach，多个元素放入集合中，List<Tnteger> stuNos 值 {1,2,3} -->
<select id="queryStudentsWithList" parameterType="list" resultType="student">
select * from student
<where>
<if test="stuNos!=null and stuNos.size>0">
<foreach collection="stuNos" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入集合中
List<Student> queryStudentsWithList(List<Integer> stuNos);
//动态SQL: foreach，多个元素放入集合中
public static void queryStudentsWithList() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//集合
List<Integer> stuNos = new ArrayList<Integer>();
stuNos.add(1);
stuNos.add(2);
stuNos.add(5);
List<Student> students = studentMapper.queryStudentsWithList(stuNos);
System.out.println(students);
session.close();
}
-----------------------------------------------------------------------------------------------------
public MyUser selectMyUserByList(List<Integer> ids);
<!-- #{collection[0]}：取出参数值,若为 List 还可使用 #{list[0]} -->
<select id="selectMyUserByList" resultType="myUser">
select * from myuser where id = #{list[0]}
</select>
f.Array数组
<!-- 动态SQL: foreach，多个元素放入数组中int[] stuNos = {1,2,3} -->
<select id="queryStudentsWithArray" parameterType="int[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入数组中
List<Student> queryStudentsWithArray(int[] stuNos);
//动态SQL: foreach，多个元素放入数组中
public static void queryStudentsWithArray() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//数组
int[] stuNos = {1,2,5};
List<Student> students = studentMapper.queryStudentsWithArray(stuNos);
System.out.println(students);
session.close();
}
-----------------------------------------------------------------------------------------------------
public MyUser selectMyUserByArray(Integer[] integers);
<!-- #{array[0]}：取出参数值 -->
<select id="selectMyUserByArray" resultType="myUser">
select * from myuser where id = #{array[0]}
</select>
02.使用@param指定一个参数/多个参数
a.一个参数
数据对象：
8个基本类型+String
-----------------------------------------------------------------------------------------------------
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(@param("id")String id);
}
-----------------------------------------------------------------------------------------------------
mapper.xml：
<select id="getUserList" parameterType="java.lang.String" resultType="SysUser">
select t.* from sys_user t where t.id= #{id}
</select>
-----------------------------------------------------------------------------------------------------
mapper.xml 中 #{id} 的 id ，对应的是 @param("id") 中指定的名称 id ，而不是String id 的 id
b.多个参数（重点）
数据对象：
// List类型
List<String> ids= new ArrayList<String>();
ids.add("1");
ids.add("2");
params.put("ids", ids);
String code = "1";
-----------------------------------------------------------------------------------------------------
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(@Param("idsIn")String ids, @Param("codeStr")String code);
}
-----------------------------------------------------------------------------------------------------
mapper.xml：
<select id="getUserList" resultType="SysUser">
select t.*
from sys_user t
where id in
<foreach collection="idsIn" item="item" open="(" separator="," close=")">
#{item}
</foreach>
and code= #{ codeStr }
</select>
-----------------------------------------------------------------------------------------------------
多参数时，@Param() 中指定参数的名称，在 mapper.xml 中引用
03.多个参数，基于参数顺序
a.参数顺序，{0}、{1}
数据对象：
相对于【01.一个参数（对象）：只有一个参数一个值【8个基本类型+引用类型String】时，使用#{value}】
-----------------------------------------------------------------------------------------------------
Mapper接口：
public interface UserMapper{
public List<SysUser> getUserList(String id , String code);
}
-----------------------------------------------------------------------------------------------------
mapper.xml：不需要写parameterType参数
<select id="getUserList" resultType="SysUser">
select t.* from sys_user t
where id = #{0} and name = #{1}
</select>
-----------------------------------------------------------------------------------------------------
这里不再使用参数名，使用索引，即 #{ index }。index 索引从 0 开始递增
b.混合参数类型
数据对象：
Integer id
MyUser user
-----------------------------------------------------------------------------------------------------
Mapper接口：
public MyUser selectMyUserIdAndAge(Integer id, @Param("user") MyUser user);
-----------------------------------------------------------------------------------------------------
mapper.xml：
<select id="selectMyUserIdAndAge" resultType="myUser">
select * from myuser where id = #{arg0} and age = #{user.age}
</select>
7.7 模糊查询like语句
00.汇总
a.'%${question}%'
可能引起SQL注入，不推荐
b."%"#{question}"%"
因为#{…}解析成sql语句时候，会在变量外侧自动加单引号’ '，
所以这里 % 需要使用双引号" "，不能使用单引号 ’ '，不然会查不到任何结果
c.CONCAT('%',#{question},'%')
使用CONCAT()函数，推荐
d.使用bind标签
<select id="listUserLikeUsername" resultType="com.jourwon.pojo.User">
<bind name="pattern" value="'%' + username + '%'" />
select id,sex,age,username,password from person where username LIKE #{pattern}
</select>
01.示例
<select id="pageQuery" resultMap="employeeResult">
SELECT
<include refid="EmployeeVo"/>
FROM employee
<where>
<if test="name != null and name != ''">
and name like concat('%',#{name},'%')
</if>
</where>
order by create_time desc
</select>
8 resultType：输出，属性与表字段一致
8.1 汇总
00.resultType（输出参数）
resultType 表示的是bean中的对象类，此时可以省略掉resultMap标签的映射
但是必须保证查询结果集中的属性和bean对象类中的属性是【一一对应】，此时大小写不敏感，但是有限制
01.注意事项
1.简单类型（8个基本+String）
2.输出类型为实体对象类型（resultType="Student"）
3.输出参数为实体对象类型的集合：虽然输出类型为集合，但是resultType依然写集合的元素类型(resultType:="Student")
4.输出参数类型为HashMap，HashMap本身是一个集合，可以存放多个元素，但是根据提示发现。返回值为HashMap时。查询结果只能是一个学生(no,name)
结论：一个HashMap对应一个学生的多个元素（多个属性）[一个Map,一个学生]，
类似“二维数组”
[
{no=1, name=zs}, 一个HashMap对象
{no=2, name=1s}, 一个HashMap对象
{no=3, name=ww}, 一个HashMap对象
{no=4, name=zl} 一个HashMap对象
]
8.2 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
insert into student values(3, 'ww', 23, 'g1');
insert into student values(4, 'zl', 24, 'g1');
commit;
--增加一列stusex
alter table student add stusex number;
update student set stusex=0;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
-- 增加列homeaddress、schooladdress
alter table student add homeaddress varchar2(20);
alter table student add schooladdress varchar2(20);
-- 增加数据
update student set homeaddress='xa' where stuno=1;
update student set homeaddress='bj' where stuno>1;
update student set schooladdress='x' where stuno>1;
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
...
}
public class Address {
private String homeAddress;
private String schoolAddress;
...
}
8.3 [重]输出类型案例：4种
00.输出类型案例：4种
a.简单类型(8个基础+引用string)
b.实体对象类型(resultType="student")
c.实体对象类型集合(resultType="student")
d.resultType="HashMap"(别名作为key值)
01.简单类型(8个基础+引用string)
<!-- 查询：输出int -->
<select id="queryStudentCount" resultType="int" >
select count(*) from student
</select>
//查询：输出int
int queryStudentCount();
// 查询：输出int
public static void queryStudentCount() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
int count = studentMapper.queryStudentCount();
System.out.println(count);
session.close();
}
02.实体对象类型(resultType="student")
<!-- 查询：输出实体对象类型student -->
<select id="queryStudentBySno" parameterType="int" resultType="student" >
select * from student where stuno = ${value}
</select>
//查询：输出实体对象类型student
Student queryStudentBySno(int stuNo);
// 查询：输出实体对象类型student
public static void queryStudentBySno() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentBySno(1);
System.out.println(student);
session.close();
}
03.实体对象类型集合(resultType="student")
<!-- 查询：输出实体对象类型集合list<student>，但resultType仍为student -->
<select id="queryAllStudents" resultType="student" >
select * from student
</select>
//查询：输出实体对象类型集合list<student>
List<Student> queryAllStudents();
// 查询全部学生
public static void queryAllStudents() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<Student> students = studentMapper.queryAllStudents();
System.out.println(students);
session.close();
}
04.resultType="HashMap"
a.查询单个：一个学生，一个Maps
<!-- 查询：输出HashMap，通过别名作为map的key值，一个学生 -->
<select id="queryStudentOutByHashMap" resultType="HashMap" >
select stuno "no", stuname "name" from student where stuno=1
</select>
//查询：输出HashMap，通过别名作为map的key值，一个学生
HashMap<String, Object> queryStudentOutByHashMap();
//查询：输出HashMap，通过别名作为map的key值，一个学生
public static void queryStudentOutByHashMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
HashMap<String,Object> studentMap = studentMapper.queryStudentOutByHashMap();
System.out.println(studentMap);
session.close();
}
-----------------------------------------------------------------------------------------------------
结果：
{no=1, name=zs}
b.查询全部：多个学生，多个Map
<!-- 查询：输出HashMap，通过别名作为map的key值，多个学生多个Map -->
<select id="queryAllStudentOutByHashMap" resultType="HashMap" >
select stuno "no", stuname "name" from student
</select>
//查询：输出HashMap，通过别名作为map的key值，多个学生多个Map
List<HashMap<String, Object>> queryAllStudentOutByHashMap();
//查询：输出HashMap，通过别名作为map的key值，多个学生多个Map
public static void queryAllStudentOutByHashMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<HashMap<String, Object>> studentMap = studentMapper.queryAllStudentOutByHashMap();
System.out.println(studentMap);w
session.close();
}
-----------------------------------------------------------------------------------------------------
结果：
[{no=1, name=zs}, {no=2, name=1s}, {no=3, name=ww}, {no=4, name=zl}]
04.resultType="HashMap"
00.表设计
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number);
insert into student values(1, 'zs', 23);
insert into student values(2, '1s', 24);
insert into student values(3, 'ww', 23);
insert into student values(4, 'zl', 24);
commit;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
01.key=字段名的别名,value=表值
<!--resultType="HashMap" -->
<select id="queryStudentOutByHashMap" parameterType="int" resultType="HashMap" >
select stuNo "no", stuName "name", stuAge "age"
from student where stuNo = #{stuNo}
</select>
//resultType="HashMap"
HashMap<String,Object> queryStudentOutByHashMap(int stuNo);
// 查询单个学生
public static void queryStudentOutByHashMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
HashMap<String, Object> studentMap = studentMapper.queryStudentOutByHashMap(1);
System.out.println(studentMap.get("no") + "," + studentMap.get("name") + "," + studentMap.get("age"));
System.out.println(studentMap);
session.close();
}
---------------------------------------------------------------------------------------------------------
结果：
1,zs,23
{no=1, name=zs, age=23}
02.key=字段名,value=表值
<!--resultType="HashMap",无别名 -->
<select id="queryStudentOutByHashMap" parameterType="int" resultType="HashMap" >
select stuNo, stuName , stuAge
from student where stuNo = #{stuNo}
</select>
//resultType="HashMap"
HashMap<String,Object> queryStudentOutByHashMap(int stuNo);
// 查询单个学生
public static void queryStudentOutByHashMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
HashMap<String, Object> studentMap = studentMapper.queryStudentOutByHashMap(1);
System.out.println(studentMap.get("STUNO") + "," + studentMap.get("STUNAME") + "," + studentMap.get("STUAGE"));
System.out.println(studentMap);
session.close();
}
---------------------------------------------------------------------------------------------------------
结果：
1,zs,23
{STUNAME=zs, STUAGE=23, STUNO=1}
03.key=@MapKey("STUNO")，value=该行记录
<!--多个key、value：不指定stuno值 -->
<select id="queryStudentsOutByHashMap" resultType="HashMap" >
select stuNo, stuName, stuAge from student
</select>
//多个key、value：不指定stuno值
@MapKey("STUNO")
HashMap<Integer, Student> queryStudentsOutByHashMap();
//多个key、value：不指定stuno值
public static void queryStudentsOutByHashMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
HashMap<Integer, Student> studentMaps = studentMapper.queryStudentsOutByHashMap();
// System.out.println(studentMap.get("no") + "," + studentMap.get("name") + "," + studentMap.get("age"));
System.out.println(studentMaps);
session.close();
}
---------------------------------------------------------------------------------------------------------
结果：
{4={STUNAME=zl, STUAGE=24, STUNO=4}, 3={STUNAME=ww, STUAGE=23, STUNO=3}, 2={STUNAME=1s, STUAGE=24, STUNO=2}, 1={STUNAME=zs, STUAGE=23, STUNO=1}}
9 resultMap：输出，属性与表字段不一致
9.1 汇总
00.resultMap
a.定义
resultMap表示将查询结果集中的列一一映射到bean对象的各个属性。
映射的查询结果集中的列标签可以根据需要灵活变化，
并且在映射关系中，还可以通过typeHandler设置实现查询结果值的类型转换，比如布尔型与0/1的类型转换
b.实体类的属性、数据表的字段，二者类型、名相同(stuno,stunos)
resultType
c.实体类的属性、数据表的字段，二者类型、名不同(stuno,id)
1.resultMap="queryStudentById"
2.resultType="student" + id "stuno", name "stuname"
01.第1种：通过在查询的SQL语句中定义字段名的别名，让字段名的别名和实体类的属性名一致。
<select id="getOrder" parameterType="int" resultType="com.jourwon.pojo.Order">
select order_id id, order_no orderno ,order_price price form orders where order_id=#{id};
</select>
02.第2种： 通过<resultMap>来映射字段名和实体类属性名的一一对应的关系。
<select id="getOrder" parameterType="int" resultMap="orderResultMap">
select * from orders where order_id=#{id}
</select>
<resultMap type="com.jourwon.pojo.Order" id="orderResultMap">
<!–用id属性来映射主键字段–>
<id property="id" column="order_id">
<!–用result属性来映射非主键字段，property为实体类属性名，column为数据库表中的属性–>
<result property ="orderno" column ="order_no"/>
<result property="price" column="order_price" />
</reslutMap>
9.2 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
insert into student values(3, 'ww', 23, 'g1');
insert into student values(4, 'zl', 24, 'g1');
commit;
--增加一列stusex
alter table student add stusex number;
update student set stusex=0;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
-- 增加列homeaddress、schooladdress
alter table student add homeaddress varchar2(20);
alter table student add schooladdress varchar2(20);
-- 增加数据
update student set homeaddress='xa' where stuno=1;
update student set homeaddress='bj' where stuno>1;
update student set schooladdress='x' where stuno>1;
-- 修改字段
alter table student rename column stuno to id;
alter table student rename column stuname to name;
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
...
}
public class Address {
private String homeAddress;
private String schoolAddress;
...
}
9.3 解决1：[重]resultMap=“queryStudentById”
01.字段不同：resultMap
<!-- 查询：resultMap，二者类型、名字不同 -->
<select id="queryStudentById" parameterType="int" resultMap="queryStudentById" >
select id,name from student where id=#{id}
</select>
<resultMap type="student" id="queryStudentById" >
<!-- 指定类中属性和表中的字段关系 -->
<id property="stuNo" column="id"/>
<result property="stuName" column="name"/>
</resultMap>
//查询：resultMap，二者类型、名字不同
Student queryStudentById(int stuno);
// 查询：resultMap，二者类型、名字不同
public static void queryStudentById() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentById(2);
System.out.println(student);
session.close();
}
9.4 解决2：[重]resultType=“student” + 别名id “stuno”, name “stuname”
01.字段不同：resultType="student" + id "stuno", name "stuname"
<!-- 查询：resultType="student"，二者类型、名字不同 -->
<select id="queryStudentByIdWithHashMap" parameterType="int" resultType="student" >
select id "stuno", name "stuname" from student where id=#{id}
</select>
//查询：resultType="student"，二者类型、名字不同
Student queryStudentByIdWithHashMap(int stuno);
// 查询：resultType="student"，二者类型、名字不同
public static void queryStudentByIdWithHashMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentByIdWithHashMap(2);
System.out.println(student);
session.close();
}
9.5 关联查询：单表、一对一、一对多
00.单表
<mapper namespace="org.myslayers.mapper.OrdersMapper">
<select id="queryOrderByUserId" parameterType="int" resultMap="OrdersUserResultMap" >
select * from Order where userId = #{userId}
</select>
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
<!-- 配置映射的订单信息 -->
<!-- id：指定查询列中的唯 一标识，订单信息的中的唯 一标识，如果有多个列组成唯一标识，配置多个id
column：订单信息的唯 一标识 列
property：订单信息的唯 一标识 列所映射到Orders中哪个属性
-->
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
</resultMap>
</mapper>
01.一对一
public class Student {
private int stuNo;
private String stuName;
private int cardId;
private StudentCard studentCard;
...
}
public class StudentCard {
private int cardId;
private String cardInfo;
...
}
-------------------------------------------------------------------------------------------------------------
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- 一对一：resultMap -->
<select id="queryStudentByNoWithOO" parameterType="int" resultMap="student_card_map">
select s.*,c.* from student s
inner join studentcard c
on s.cardid = c.cardid
where s.stuno = #{stuNo}
</select>
<resultMap type="student" id="student_card_map">
<!-- 学生信息：普通类型 -->
<id property="stuNo" column="stuNo"/>
<result property="stuName" column="stuName"/>
<result property="cardId" column="cardId"/>
<!-- 学生证信息：对象类型 -->
<!-- 一对一对象成员使用association映射，javaType指定该属性的类型 ->List<Student> students;中的Students -->
<association property="studentCard" javaType="StudentCard">
<id property="cardId" column="cardId"/>
<result property="cardInfo" column="cardInfo"/>
</association>
</resultMap>
</mapper>
---------------------------------------------------------------------------------------------------------
1个学生 对应 1张学生证
association
javaType
02.一对多
public class StudentClass {
private int classId;
private String className;
...
}
public class Student {
private int stuNo;
private String stuName;
private int cardId;
...
}
---------------------------------------------------------------------------------------------------------
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- 一对多：resultMap -->
<select id="queryClassAndStudents" parameterType="int" resultMap="class_student_map">
<!-- 查询g1班的班级信息，g1班的所有学生信息 -->
select c.*,s.* from student s
inner join studentclass c
on c.classid=s.classid
where c.classid = #{classId}
</select>
<resultMap type="StudentClass" id="class_student_map">
<!-- 因为type的主类是班级，因此 先配置班级的信息 -->
<!-- 班级信息：普通类型 -->
<id property="classId" column="classid"/>
<result property="className" column="classname"/>
<!-- 所有学生信息：对象类型 -->
<!-- 一对多对象成员使用collection映射，ofType指定该属性的元素类型 ->List<Student> students;中的Student -->
<collection property="students" ofType="Student">
<id property="stuNo" column="stuno"/>
<result property="stuName" column="stuname"/>
</collection>
</resultMap>
</mapper>
-------------------------------------------------------------------------------------------------------------
1个班级 对应 多名学生
collection
ofType
9.6 jdbcType：插入、更新、删除
01.什么时候必须加上 jdbcType？插入、更新、删除
在 coding 的时候，有时候会发现，比如在 User.xml 中，
不管是用 userId = #{userId} 还是 userId = #{userId, jdbcType = Integer}，都没有问题。
02.什么时候必须要加上 jdbcType 呢？
Mybatis 的官方文档是这样描述 jdbcType 的：
The JDBC Type from the list of supported types that follows this table. The JDBC type is only required for nullable columns upon insert, update or delete. This is a JDBC requirement, not a MyBatis one. So even if you were coding JDBC directly, you’d need to specify this type – but only for nullable values.
意思是说，只有当将一个空值作用于插入、更新、删除操作时，jdbcType 才是必须的。这是 JDBC 的一个需求，并不是 MyBatis 的。所以即使你是直接编写 JDBC，当它是空的时候，你也一定要指定它的类型，其他情况不需要指定它的类型。
---------------------------------------------------------------------------------------------------------
在 Stack Overflow 上，同样有这样的解释：
Most of the times you don’t need to specify the jdbcType as MyBatis is smart enough to figure out the type from the objects you are working with. But if you send your parameters to the MyBatis statement inside a HashMap, for example, and one of the parameters is null, MyBatis won’t be able to determine the type of the parameter by looking at the HashMap because the HashMap is just a generic container and null itself carries no type information. At that point it would be o good idea to provide the jdbcType so that switching the database implementation later on does not cause any issues with null values.
就是说，在大部分时候，我们不需要指定 jdbcType，因为 MyBatis 足够智能地可以辨别出对象的类型。但是如果你传过来的参数是一个 HashMap，其中一个参数是空的，MyBatis 将不能通过 HashMap 来决定对象的类型。因为 HashMap 是一个通用的容器并且如果它本身是空的话，将不会携带任何的类型信息。所以最好的做法是，当值是空的时候去指定它的 jdbcType，这样在之后转换为 db 实现的时候就不会再引起任何问题了。
03.Mybatis中javaType和jdbcType对应关系
JDBC Type Java Type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT boolean
BOOLEAN boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
DOUBLE double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
CLOB Clob
BLOB Blob
ARRAY Array
DISTINCT mapping of underlying type
STRUCT Struct
REF Ref
DATALINK java.net.URL[color=red][/color]
9.7 类别名alias+鉴别器discriminator
00.表设计
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
-- 更改字段名
alter table student rename column stuno to sno;
alter table student rename column stuname to sname;
alter table student rename column stuage to sage;
alter table student rename column graname to gname;
-- 添加主键
alter table student add constraint pk_student3 primary key(sno);
-- 增加字段
alter table student add nickname varchar2(10);
-- 增加数据
insert into student(sno, sname, nickname, gname) values(1, 'zs', 'zxs', 'a');
insert into student(sno, sname, nickname, gname) values(2, 'ls', 'zss', 'a');
insert into student(sno, sname, nickname, gname) values(3, 'ww', 'www', 'b');
insert into student(sno, sname, nickname, gname) values(4, 'nn', 'nns', 'b');
commit;
SCOTT@ORCL>select * from student;
SNO SNAME SAGE GNAME NICKNAME
---------- -------------------- ---------- -------------------- ----------
1 zs a zxs
2 ls a zss
3 ww b www
4 nn b nns
01.类别名
<!-- 设置单个/多个别名 -->
<typeAliases>
<!-- 单个别名 -->
<typeAlias type="org.myslayers.entity.Student" alias="student"/>
<!-- 批量定义别名，以下会自动将该包中的所有类批量定义别名： 别名就是类名（不带包名的类名） -->
<package name="org.myslayers.entity"/>
</typeAliases>
org/myslayers/entity/Student.java // 别名student
public class Student {
private Integer stuNo;
private String stuName;
private int stuAge;
private String graName;
...
}
org/myslayers/entity/a/Student.java // 别名mystudent
@Alias("mystudent")
public class Student {
}
02.鉴别器
a.目的
对查询结果进行分支处理，如果是a年级，使用真名sname；如果是b年级，使用昵称nickname
b.代码
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!--字段和属性名的对应关系 -->
<select id="queryStudentsByNoWithResultMap" resultMap="studentResultMap" >
select sno, sname, nickname, sage, gname from student
</select>
<!--如果字段名、属性名不一致，需要resultMap指定对应关系-->
<resultMap type="student" id="studentResultMap">
<!--主键-->
<id column="sno" property="stuNo"/>
<!--普通字段-->
<!--
<result column="sname" property="stuName"/>
-->
<result column="sage" property="stuAge"/>
<result column="gname" property="graName"/>
<!-- 鉴别器：对查询结果进行分支处理，如果是a年级，使用真名sname; 如果是b年级，使用昵称nickname-->
<discriminator javaType="string" column="gname">
<case value="a" resultType="student">
<result column="sname" property="stuName"/>
</case>
<case value="b" resultType="student">
<result column="nickname" property="stuName"/>
</case>
</discriminator>
</resultMap>
</mapper>
//Student:属性名stuNo, stuName, stuAge, graName
//字段名:sno, sname, sage, gname
List<Student> queryStudentsByNoWithResultMap();
public static void queryStudentsByNoWithResultMap() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<Student> students = studentMapper.queryStudentsByNoWithResultMap();
System.out.println(students);
session.close();
}
c.结果
[1-zs-0-a, 2-ls-0-a, 3-www-0-b, 4-nns-0-b]
10 调用存储过程
10.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
insert into student values(3, 'ww', 23, 'g1');
insert into student values(4, 'zl', 24, 'g1');
commit;
--增加一列stusex
alter table student add stusex number;
update student set stusex=0;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
-- 增加列homeaddress、schooladdress
alter table student add homeaddress varchar2(20);
alter table student add schooladdress varchar2(20);
-- 增加数据
update student set homeaddress='xa' where stuno=1;
update student set homeaddress='bj' where stuno>1;
update student set schooladdress='x' where stuno>1;
commit;
-- 查询：查询某个年级的学生总数（存储过程）
-- 查询某个年级的学生总数 输入：年级 输出：该年级的学生总数
create or replace procedure queryCountByGradeWithProcedure(gName in varchar, scount out number )
as
begin
select count(*) into scount from student where graname = gname ;
end;
/
-- 删除：根据学号删除学生（存储过程）
create or replace procedure deleteStuBynoWithProcedure(sno in number)
as
begin
delete from student where stuno = sno ;
end;
/
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
...
}
public class Address {
private String homeAddress;
private String schoolAddress;
...
}
10.2 存储过程：查询
10.2.1 存储SQL
01.sql
-- 查询：查询某个年级的学生总数（存储过程）
-- 查询某个年级的学生总数 输入：年级 输出：该年级的学生总数
create or replace procedure queryCountByGradeWithProcedure(gName in varchar, scount out number )
as
begin
select count(*) into scount from student where graname = gname ;
end;
/
10.2.2 代码实现
01.代码实现
<!-- 通过调用[存储过程]实现查询 statementType="CAlLABLE"
存储过程的输入参数，在MyBatis用Map传递（HashMap）
-->
<select id="queryCountByGradeWithProcedure" statementType="CALLABLE" parameterType="HashMap">
{
CALL queryCountByGradeWithProcedure(
#{gName,jdbcType=VARCHAR,mode=IN},
#{sCount,jdbcType=INTEGER,mode=OUT}
)
}
</select>
//查询：通过存储过程查询某个年级的学生总数
void queryCountByGradeWithProcedure(Map<String, Object> params);
//查询：查询某个年级的学生总数（存储过程）
public static void queryCountByGradeWithProcedure() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Map<String, Object> params = new HashMap<String, Object>();
params.put("gName", "g1");//指定存储过程的输入参数gName的值是g1
studentMapper.queryCountByGradeWithProcedure(params);//调用存储过程，并传入参数
//获取存储过程的输出参数
Object count = params.get("sCount");
System.out.println(count);
session.close();
}
10.3 存储过程：增删改
10.3.1 存储SQL
01.sql
-- 删除：根据学号删除学生（存储过程）
create or replace procedure deleteStuBynoWithProcedure(sno in number)
as
begin
delete from student where stuno = sno ;
end;
/
10.3.2 代码实现
01.代码实现
<!-- 通过调用[存储过程]实现增删改 -->
<delete id="deleteStuBynoWithProcedure" statementType="CALLABLE" parameterType="HashMap">
{
CALL deleteStuBynoWithProcedure(
#{sno,jdbcType=VARCHAR,mode=IN}
)
}
</delete>
//删除：通过存储过程根据学号删除学生
void deleteStuBynoWithProcedure(Map<String, Object> params);
//删除：根据学号删除学生（存储过程）
public static void deleteStuBynoWithProcedure() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Map<String, Object> params = new HashMap<String, Object>();
params.put("sno", 3);//指定存储过程的输入参数gName的值是g1
studentMapper.deleteStuBynoWithProcedure(params);//调用存储过程，并传入参数
//获取存储过程的输出参数
Object count = params.get("sCount");
System.out.println(count);
session.commit();
session.close();
}
11 动态SQL
11.1 汇总
00.9种动态SQL标签：if、choose、when、otherwise、trim、where、set、foreach、bind
if: 根据条件判断
choose、when、otherwise: 组合使用，选择多个条件中的一个
trim: 定制类似 where 标签的功能
where: where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除
set: 用于动态包含需要更新的列，忽略其它不更新的列
foreach: 对集合进行遍历
bind: 允许在OGNL表达式以外创建一个变量，并将其绑定到当前的上下文
00.MyBatis通过OGNL来进行动态SQL的使用的。目前，动态SQL支持以下几种标签：
元素 作用 备注
if 判断语句 单条件分支
choose(when.otherwise) 相当于Java中的if else 多条件分支
trim(where、set) 辅助元素 用于处理SQL拼接问题
foreach 循环语句 批量插入，更新，查询时经常用到
bind 创建一个变量，并绑定到上下文中 用于兼容不同数据库
01.if标签：在查询、删除、更新的时候很可能会使用到。必须结合test属性联合使用。
a.方式1：在 WHERE 条件中使用 if 标签
查询条件：根据输入的学生信息进行条件检索
当只输入用户名时， 使用用户名进行模糊检索；
当只输入性别时， 使用性别进行完全匹配
当用户名和性别都存在时， 用这两个条件进行查询匹配查询
-----------------------------------------------------------------------------------------------------
<select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
where 1=1
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</select>
b.方式2：在 UPDATE 更新列中使用 if 标签
更新条件：只更新有变化的字段， 空值不更新
-----------------------------------------------------------------------------------------------------
<update id="updateByPrimaryKeySelective" parameterType="com.homejim.mybatis.entity.Student">
update student
<set>
<if test="name != null">
`name` = #{name,jdbcType=VARCHAR},
</if>
<if test="phone != null">
phone = #{phone,jdbcType=VARCHAR},
</if>
<if test="email != null">
email = #{email,jdbcType=VARCHAR},
</if>
<if test="sex != null">
sex = #{sex,jdbcType=TINYINT},
</if>
<if test="locked != null">
locked = #{locked,jdbcType=TINYINT},
</if>
<if test="gmtCreated != null">
gmt_created = #{gmtCreated,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
</if>
</set>
where student_id = #{studentId,jdbcType=INTEGER}
c.方式3：在 INSERT 动态插入中使用 if 标签
插入条件：只有非空属性才插入
-----------------------------------------------------------------------------------------------------
<insert id="insertSelective" parameterType="com.homejim.mybatis.entity.Student">
insert into student
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="studentId != null">
student_id,
</if>
<if test="name != null">
`name`,
</if>
<if test="phone != null">
phone,
</if>
<if test="email != null">
email,
</if>
<if test="sex != null">
sex,
</if>
<if test="locked != null">
locked,
</if>
<if test="gmtCreated != null">
gmt_created,
</if>
<if test="gmtModified != null">
gmt_modified,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="studentId != null">
#{studentId,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="phone != null">
#{phone,jdbcType=VARCHAR},
</if>
<if test="email != null">
#{email,jdbcType=VARCHAR},
</if>
<if test="sex != null">
#{sex,jdbcType=TINYINT},
</if>
<if test="locked != null">
#{locked,jdbcType=TINYINT},
</if>
<if test="gmtCreated != null">
#{gmtCreated,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
#{gmtModified,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
02.choose标签：choose when otherwise 标签可以帮我们实现 if else 的逻辑。一个 choose 标签至少有一个 when, 最多一个 otherwise
a.查询条件
假设 name 具有唯一性，查询一个学生
当 studen_id 有值时， 使用 studen_id 进行查询；
当 studen_id 没有值时， 使用 name 进行查询；
否则返回空
b.动态SQL
接口方法：Student selectByIdOrName(Student record);
对应SQL：
<select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
where 1=1
<choose>
<when test="studentId != null">
and student_id=#{studentId}
</when>
<when test="name != null and name != ''">
and name=#{name}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
03.trim(set、where)
a.wher：不使用 where 1=1
a.查询条件
根据输入的学生信息进行条件检索
当只输入用户名时， 使用用户名进行模糊检索；
当只输入性别时， 使用性别进行完全匹配
当用户名和性别都存在时， 用这两个条件进行查询匹配查询
b.动态 SQL
接口方法：List<Student> selectByStudentSelectiveWhereTag(Student student);
对应SQL：
<select id="selectByStudentSelectiveWhereTag" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
<where>
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where>
</select>
b.set
set 和 where 其实都是 trim 标签的一种类型， 该两种功能都可以使用 trim 标签进行实现。
c.trim
trim 来表示 where：
<trim prefix="where" prefixOverrides="AND |OR"></trim>
当 trim 中含有内容时， 添加 where， 且第一个为 and 或 or 时， 会将其去掉。 而如果没有内容， 则不添加 where。
trim 来表示 set：
<trim prefix="SET" suffixOverrides=","></trim>
表示当 trim 中含有内容时，添加set，且最后的内容为,时，会将其去掉。而没有内容，不添加set
trim 的几个属性
prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀
prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀
suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀
suffixOverrides： 当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀
-------------------------------------------------------------------------------------------------
prefix：在trim标签中的内容的 前面添加某些内容
prefixOverrides：在trim标签中的内容的 前面去掉某些内容
suffix：在trim标签中的内容的 后面添加某些内容
suffixOverrides：在trim标签中的内容的 后面去掉某些内容
04.foreach
a.<foreach>迭代的类型
①对象
②对象数组
③数组
④集合
b.foreach中有以下几个属性：
collection: 表示对哪一个集合或数组做迭代；如果参数是数组类型，此时Map的key为array；如果参数是List类型，此时Map的key为list
item: 变量名。即从迭代的对象中取出的每一个值
index: 索引的属性名。当迭代的对象为Map时，该值为Map中的Key
open: 循环开头的字符串
close: 循环结束的字符串
separator: 每次循环的分隔符
c.collection 中的值应该怎么设定呢？跟接口方法中的参数相关。
1. 只有一个数组参数或集合参数
默认情况： 集合 collection=list， 数组是 collection=array
推荐： 使用 @Param 来指定参数的名称， 如我们在参数前 @Param("ids")， 则就填写 collection=ids
2. 多参数
多参数请使用 @Param 来指定， 否则 SQL 中会很不方便
3. 参数是 Map
指定为 Map 中的对应的 Key 即可。 其实上面的 @Param 最后也是转化为 Map 的。
4. 参数是对象
使用属性. 属性即可
d.在 where 中使用 foreach
查询条件：查询用户 id 集合中的所有用户信息
动态SQL：
接口方法：List<Student> selectByStudentIdList(List<Integer> ids);
对应SQL：
<select id="selectByStudentIdList" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from student
where student_id in
<foreach collection="list" item="id" open="(" close=")" separator="," index="i">
#{id}
</foreach>
</select>
e.foreach实现批量插入
插入条件：可以通过 foreach 来实现批量插入
动态SQL：
接口方法：int insertList(List<Student> students);
对应SQL：
<insert id="insertList">
insert into student(name, phone, email, sex, locked)
values
<foreach collection="list" item="student" separator=",">
(
#{student.name}, #{student.phone},#{student.email},
#{student.sex},#{student.locked}
)
</foreach>
</insert>
05.bind
bind 标签是通过 OGNL 表达式去定义一个上下文的变量，这样方便我们使用。
如在selectByStudentSelective方法中，有如下
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
在MySQL中，该函数支持多参数，但在Oracle中只支持两个参数。那么我们可以使用bind来让该SQL达到支持两个数据库的作用
<if test="name != null and name !=''">
<bind />
and name like #{nameLike}
</if>
05.sql、include、bind 标签
sql标签: 使用<sql>片段来封装表的全部字段, 然后通过<include>来引入
bind: 使用OGNL表达式创建一个变量,并将其绑定在上下文中.
<mapper namespace="com.sunny.dao.EmployeeMapper">
<!--多个查询共同使用的sql-->
<sql id="Base_where">
<where>
<if test="keyword!=null">
<bind name="keywordLike" value="'%' + keyword +'%'"/>
<!-- 下面 #{KeywordLike} 用 %keyword%来替代, 这个keyword是实体的属性 -->
AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
<!--AND (name LIKE concat('%', #{keyword}, '%') OR sn LIKE concat('%', #{keyword}, '%'))-->
</if>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <=#{maxSalary}
</if>
<if test="deptId!=null">
AND deptId = #{deptId}
</if>
</where>
</sql>
<!--根据查询条件来查询符合条件的查询-->
<select id="queryForList" resultType="Employee">
SELECT * FROM employee
<include refid="Base_where"></include>
</select>
<!--查询符合条件的员工数量-->
<select id="queryForEmpCount" resultType="int">
SELECT count(*) FROM employee
<include refid="Base_where"></include>
</select>
</mapper>
11.2 where标签
11.2.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
insert into student values(3, 'ww', 23, 'g1');
insert into student values(4, 'zl', 24, 'g1');
commit;
--增加一列stusex
alter table student add stusex number;
update student set stusex=0;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
-- 增加列homeaddress、schooladdress
alter table student add homeaddress varchar2(20);
alter table student add schooladdress varchar2(20);
-- 增加数据
update student set homeaddress='xa' where stuno=1;
update student set homeaddress='bj' where stuno>1;
update student set schooladdress='x' where stuno>1;
-- 修改字段
alter table student rename column stuno to id;
alter table student rename column stuname to name;
-- 修改字段
alter table student rename column id to stuno;
alter table student rename column name to stuname;
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
...
}
public class Address {
private String homeAddress;
private String schoolAddress;
...
}
11.2.2 where 1=1
01.汇总
a.xml
<!-- 动态SQL: 1=1 true处理语句-->
<select id="queryStudentByNorAWishSQLTag" parameterType="student" resultType="student" >
select stuno, stuname, stuage from student where 1=1
<!-- if test="student有stuname属性，且不为Null" -->
<if test="stuName!=null and stuName!='' ">
and stuname = #{stuName}
</if>
<if test="stuAge!=null and stuAge!=0 ">
and stuage = #{stuAge}
</if>
</select>
b.mapper
//动态SQL
Student queryStudentByNorAWishSQLTag(Student student);
c.service
//动态SQL
public static void queryStudentByNorAWishSQLTag() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setStuAge(24);
stu.setStuName("ls");
Student student = studentMapper.queryStudentByNorAWishSQLTag(stu);
System.out.println(student);
session.close();
}
11.2.3 where标签
01.代码
select
stuno,stuname,stuage
from
student
<where>
and stuname=#(stuName)
and stuage=#(stuAge)
02.说明
<where>会自动处理第一个<if>标签中的and，但不会处理之后<if>中的and
03.代码
<!-- 动态SQL: where标签-->
<select id="queryStudentByNorAWishSQLTag" parameterType="student" resultType="student" >
select stuno, stuname, stuage from student
<where>
<!-- if test="student有stuname属性，且不为Null" -->
<if test="stuName!=null and stuName!='' ">
and stuname = #{stuName}
</if>
<if test="stuAge!=null and stuAge!=0 ">
and stuage = #{stuAge}
</if>
</where>
</select>
//动态SQL
Student queryStudentByNorAWishSQLTag(Student student);
//动态SQL
public static void queryStudentByNorAWishSQLTag() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setStuAge(24);
stu.setStuName("ls");
Student student = studentMapper.queryStudentByNorAWishSQLTag(stu);
System.out.println(student);
session.close();
}
11.3 foreach标签
11.3.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
create table student(stuno number, stuname varchar2(20), stuage number, graname varchar2(20));
insert into student values(1, 'zs', 23, 'g1');
insert into student values(2, '1s', 24, 'g1');
insert into student values(3, 'ww', 23, 'g1');
insert into student values(4, 'zl', 24, 'g1');
commit;
--增加一列stusex
alter table student add stusex number;
update student set stusex=0;
--给student增加主键
alter table student add constraint pk_student_stuno primary key(stuno);
-- 增加列homeaddress、schooladdress
alter table student add homeaddress varchar2(20);
alter table student add schooladdress varchar2(20);
-- 增加数据
update student set homeaddress='xa' where stuno=1;
update student set homeaddress='bj' where stuno>1;
update student set schooladdress='x' where stuno>1;
-- 修改字段
alter table student rename column stuno to id;
alter table student rename column stuname to name;
-- 修改字段
alter table student rename column id to stuno;
alter table student rename column name to stuname;
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
private boolean stuSex;
private Address address;//家庭、学校
...
}
public class Address {
private String homeAddress;
private String schoolAddress;
...
}
public class Grade {
//年级学号
private List<Integer> stuNos;
...
}
11.3.2 执行顺序
01.foreach的collection属性
foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合
如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可
02.代码实现
a.示例1
public List<Blog> dynamicForeachTest(List<Integer> ids);
<select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
select * from t_blog where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
b.示例2
public List<Blog> dynamicForeach2Test(int[] ids);
<select id="dynamicForeach2Test" parameterType="java.util.ArrayList" resultType="Blog">
select * from t_blog where id in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
c.示例3
int batchInsert(@Param("list") List<User> list);
<insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
<!--@mbg.generated-->
insert into m_user
(username, `password`, email, gender, avatar, `status`, lasted, created, modified
)
values
<foreach collection="list" item="item" separator=",">
(#{item.username}, #{item.password}, #{item.email}, #{item.gender}, #{item.avatar},
#{item.status}, #{item.lasted}, #{item.created}, #{item.modified})
</foreach>
</insert>
d.示例4
int deleteByIds(Long[] ids);
<delete id="deleteByIds" parameterType="String">
delete from m_user where id in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
e.示例5
collection="list" 与 collection="stuNos" 等价
<select id="queryStudentsWithNosINGrade" parameterType="grade" resultType="student">
select * from student
<where>
<if test="stuNos!=null and stuNos.size>0">
<foreach collection="list" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
<select id="queryStudentsWithNosINGrade" parameterType="grade" resultType="student">
select * from student
<where>
<if test="stuNos!=null and stuNos.size>0">
<foreach collection="stuNos" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
11.3.3 案例：对象列表（Collection集合）
01.实体类
public class Grade {
//年级学号
private List<Integer> stuNos;
02.代码实现
<!-- 动态SQL: foreach，多个元素放入对象的属性 -->
<select id="queryStudentsWithNosINGrade" parameterType="grade" resultType="student">
select * from student
<where>
<if test="stuNos!=null and stuNos.size>0">
<foreach collection="list" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入对象的属性
List<Student> queryStudentsWithNosINGrade(Grade grade);
//动态SQL: foreach，多个元素放入对象的属性
public static void queryStudentsWithNosINGrade() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Grade grade = new Grade();
List<Integer> stuNos = new ArrayList<Integer>();
stuNos.add(1);
stuNos.add(2);
stuNos.add(5);
grade.setStuNos(stuNos);
List<Student> students = studentMapper.queryStudentsWithNosINGrade(grade);
System.out.println(students);
session.close();
}
11.3.4 案例：对象数组（Array数组）
00.汇总
对象数组：约定parameterType必须为"Object[]"
Student queryStudentsWithObjectArray(Object[] students)
01.代码实现
<!-- 动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2} -->
<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="student" separator=",">
#{student.stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2}
List<Student> queryStudentsWithObjectArray(Student[] students);
//动态SQL: foreach，多个元素放入对象数组中，Student[] students = {student0,student1,student2}
public static void queryStudentsWithObjectArray() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//对象数组
Student stu1 = new Student();
Student stu2 = new Student();
Student stu3 = new Student();
stu1.setStuNo(1);
stu2.setStuNo(2);
stu3.setStuNo(3);
Student[] stus = new Student[] {stu1, stu2, stu3} ;
List<Student> students = studentMapper.queryStudentsWithObjectArray(stus);
System.out.println(students);
session.close();
}
11.3.5 案例：Array数组
00.汇总
简单类型的数组：约定，无论编写代码时，传递的是什么参数名(stuNos)，在mapper.xml中必须用array代替该数组
01.代码实现
<!-- 动态SQL: foreach，多个元素放入数组中int[] stuNos = {1,2,3} -->
<select id="queryStudentsWithArray" parameterType="int[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入数组中
List<Student> queryStudentsWithArray(int[] stuNos);
//动态SQL: foreach，多个元素放入数组中
public static void queryStudentsWithArray() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//数组
int[] stuNos = {1,2,5};
List<Student> students = studentMapper.queryStudentsWithArray(stuNos);
System.out.println(students);
session.close();
}
11.3.6 案例：Collection集合
00.汇总
集合：约定，无论编写代码时，传递的是什么参数名(stuNos)，在mapper.xml中必须用list代替该数组
01.代码实现
<!-- 动态SQL: foreach，多个元素放入集合中，List<Tnteger> stuNos 值 {1,2,3} -->
<select id="queryStudentsWithList" parameterType="list" resultType="student">
select * from student
<where>
<if test="list!=null and list.size>0">
<foreach collection="list" open=" and stuno in (" close=")"
item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
//动态SQL: foreach，多个元素放入集合中
List<Student> queryStudentsWithList(List<Integer> stuNos);
//动态SQL: foreach，多个元素放入集合中
public static void queryStudentsWithList() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//集合
List<Integer> stuNos = new ArrayList<Integer>();
stuNos.add(1);
stuNos.add(2);
stuNos.add(5);
List<Student> students = studentMapper.queryStudentsWithList(stuNos);
System.out.println(students);
session.close();
}
11.4 SQL片段
00.SQL片段
a.提取相似代码
java：方法
b.引用
数据库：存储过程、存储函数
MyBatis：SQL片段
11.4.1 同一个XML
01.片段1
<!-- SQL片段 -->
<sql id="objectArrayStunos">
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="student" separator=",">
#{student.stuNo}
</foreach>
</if>
</where>
</sql>
02.片段2
<!-- 动态SQL: foreach，多个元素放入对象数组中，使用SQL片段 -->
<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
select * from student
<include refid="objectArrayStunos"></include>
</select>
11.4.2 不同的XML：全类名
01.片段1
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- SQL片段 -->
<sql id="objectArrayStunos">
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="student" separator=",">
#{student.stuNo}
</foreach>
</if>
</where>
</sql>
</mapper>
02.片段2
<!-- 动态SQL: foreach，多个元素放入对象数组中，使用SQL片段 -->
<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
select * from student
<include refid="org.myslayers.mapper.StudentMapper.objectArrayStunos"></include>
</select>
12 关联查询
00.联合查询、嵌套查询
联合查询是几个表联合查询，只查询一次，通过在resultMap里面的association，collection节点配置一对一，一对多的类就可以完成
嵌套查询是先查一个表，根据这个表里面的结果的外键id，去再另外一个表里面查询数据，也是通过配置association，collection，但另外一个表的查询通过select节点配置。
12.1 一对一
12.1.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentcard';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- studentCard
create table studentcard(cardid number primary key, cardinfo varchar2(20));
insert into studentcard values(1, 'zs info...' );
insert into studentcard values(2, 'ls info...' );
--student
create table student(stuno number, stuname varchar2(20));
insert into student values(1,'zs');
insert into student values(2,'ls');
--外键
alter table student add cardid number;
update student set cardid=1 where stuno=1;
update student set cardid=2 where stuno=2;
--外键约束
alter table student add constraint fk_student_studentcard_cardid foreign key(cardid) references studentcard(cardid);
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int cardId;
...
}
public class StudentCard {
private int cardId;
private String cardInfo;
}
public class StudentBusiness extends Student{//学生业务扩展类
private int cardId;
private String cardInfo;
...
}
12.1.2 业务扩展类
12.2 一对一
12.2.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentcard';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- studentCard
create table studentcard(cardid number primary key, cardinfo varchar2(20));
insert into studentcard values(1, 'zs info...' );
insert into studentcard values(2, 'ls info...' );
--student
create table student(stuno number, stuname varchar2(20));
insert into student values(1,'zs');
insert into student values(2,'ls');
--外键
alter table student add cardid number;
update student set cardid=1 where stuno=1;
update student set cardid=2 where stuno=2;
--外键约束
alter table student add constraint fk_student_studentcard_cardid foreign key(cardid) references studentcard(cardid);
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int cardId;
...
}
public class StudentCard {
private int cardId;
private String cardInfo;
}
12.2.2 resultMap：1个学生对应1张学生证
12.3 一对多
12.3.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentcard';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentclass';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- studentCard
create table studentcard(cardid number primary key, cardinfo varchar2(20));
insert into studentcard values(1, 'zs info...' );
insert into studentcard values(2, 'ls info...' );
--student
create table student(stuno number, stuname varchar2(20));
insert into student values(1,'zs');
insert into student values(2,'ls');
--外键
alter table student add cardid number;
update student set cardid=1 where stuno=1;
update student set cardid=2 where stuno=2;
--外键约束
alter table student add constraint fk_student_studentcard_cardid foreign key(cardid) references studentcard(cardid);
commit;
--给student添加classid字段
alter table student add classid number;
-- 建立学生班级表
create table studentclass(classid number primary key, classname varchar2(10));
insert into studentclass values(1,'g1');
insert into studentclass values(2,'g2');
-- 外键
alter table student add constraint fk_student_class foreign key(classid) references studentclass(classid);
-- student中的classid
update student set classid=1 ;
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int cardId;
...
}
public class StudentClass {
private int classId;
private String className;
//增加学生属性(通过该字段让student和studentclass建立其关联)
List<Student> students;
...
}
12.3.2 resultMap：1个班级对应多名学生
13 Log4j
13.1 开启日志：conf.xml
01.conf.xml
<configuration>
<settings>
<!-- 开启日志，并指定使用的具体日志 -->
<setting name="logImpl" value="LOG4J"/>
</settings>
</configuration>
13.2 配置文件：log4j-properties
01.log4j-properties
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
14 延迟加载
14.1 汇总
00.延迟加载（懒加载）
1.一对一
2.一对多
3.多对一
4.多对多
01.一对一
学生->学生证，先查学生，再按需查找学生证
02.一对多
班级->学生，先查班级，再按需查找学生
如果不采用延迟加载（立即加载），查询时会将”一对一”和“"一对多”都查询，即班级、班级中的所有学生。
如果想要暂时只查询“班级”的一方，而多的一方先不查询，而是在需要的时候再去查询 ->"延迟加载”
03.延迟加载
<association property="card" javaType="studentCard" select="org.myslayers.mapper.StudentCardMapper.queryCardById" column="cardId"> //引用【关键代码】
<collection property="students" ofType="student" select="org.myslayers.mapper.StudentMapper.queryStudentsByClassId" column="classid"> //引用【关键代码】
14.2 开启延迟加载：conf.xml
01.conf.xml
<configuration>
<properties resource="db.properties"/>
<settings>
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 关闭延迟加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
</configuration>
14.3 一对一
14.3.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentcard';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- studentCard
create table studentcard(cardid number primary key, cardinfo varchar2(20));
insert into studentcard values(1, 'zs info...' );
insert into studentcard values(2, 'ls info...' );
--student
create table student(stuno number, stuname varchar2(20));
insert into student values(1,'zs');
insert into student values(2,'ls');
--外键
alter table student add cardid number;
update student set cardid=1 where stuno=1;
update student set cardid=2 where stuno=2;
--外键约束
alter table student add constraint fk_student_studentcard_cardid foreign key(cardid) references studentcard(cardid);
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int cardId;
...
}
public class StudentCard {
private int cardId;
private String cardInfo;
}
14.3.2 先查学生，再查学生证
01.未开启延迟前
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- 一对一：resultMap -->
<select id="queryStudentByNoWithOO" parameterType="int" resultMap="student_card_map">
select s.*,c.* from student s inner join studentcard c
on s.cardid = c.cardid
where s.stuno = #{stuNo}
</select>
<resultMap type="student" id="student_card_map">
<!-- 学生信息：普通类型 -->
<id property="stuNo" column="stuNo"/>
<result property="stuName" column="stuName"/>
<result property="cardId" column="cardId"/>
<!-- 学生证信息：对象类型 -->
<!-- 一对一对象成员使用association映射，javaType指定该属性的类型 ->List<Student> students;中的Students -->
<association property="studentCard" javaType="StudentCard">
<id property="cardId" column="cardId"/>
<result property="cardInfo" column="cardInfo"/>
</association>
</resultMap>
</mapper>
public interface StudentMapper {
//一对一：resultMap
Student queryStudentByNoWithOO(int stuNo);
}
//一对一：resultMap
public static void queryStudentByNoWithOO() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentByNoWithOO(2);
System.out.println(student);
session.close();
}
02.已开启延迟后
<mapper namespace="org.myslayers.mapper.StudentCardMapper">
<!-- 查询学生证信息 -->
<select id="queryCardById" parameterType="int" resultType="studentCard"> //【关键代码】
<!-- 查询学生对应的学生证, 需要外键cardid关联 -->
select * from studentCard where cardid=#{cardId}
</select>
<!-- 根据cardid查询学生证的sql：org.myslayers.mapper.StudentCardMapper.queryCardById -->
</mapper>
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- //查询全部学生，并延迟加载每个学生对应的学生证 -->
<select id="queryStudentWithOOLazyLoad" resultMap="student_card_lazyLoad_map">
<!-- 先查学生 -->
select * from student
</select>
<!-- resultMap -->
<resultMap type="student" id="student_card_lazyLoad_map">
<!-- 学生信息：普通类型 -->
<id property="stuNo" column="stuNo"/>
<result property="stuName" column="stuName"/>
<result property="cardId" column="cardId"/>
<!-- 一对一对象成员使用association映射，javaType指定该属性的类型 ->List<Student> students;中的Students
此次采用延迟加载：在查询学生时，并不立即加载学生证信息
-->
<!-- 再查学生证信息：对象类型，通过select在需要的时候再查学生证 -->
<association property="card" javaType="studentCard" select="org.myslayers.mapper.StudentCardMapper.queryCardById" column="cardId"> //引用【关键代码】
<!--
<id property="cardId" column="cardId"/>
<result property="cardInfo" column="cardInfo"/>
-->
</association>
</resultMap>
</mapper>
public interface StudentMapper {
//查询全部学生，并延迟加载每个学生对应的学生证
List<Student> queryStudentWithOOLazyLoad();
}
//查询全部学生，并延迟加载每个学生对应的学生证
public static void queryStudentWithOOLazyLoad() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<Student> students = studentMapper.queryStudentWithOOLazyLoad();
for(Student student:students) {
//获取全部学生
System.out.println(student.getStuNo() + "," + student.getStuName());
//获取每个学生对应的学生证
StudentCard studentCard = student.getCard();
System.out.println(studentCard.getCardId() + "," + studentCard.getCardInfo());
}
session.close();
}
14.4 一对多
14.4.1 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentcard';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentclass';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- studentCard
create table studentcard(cardid number primary key, cardinfo varchar2(20));
insert into studentcard values(1, 'zs info...' );
insert into studentcard values(2, 'ls info...' );
--student
create table student(stuno number, stuname varchar2(20));
insert into student values(1,'zs');
insert into student values(2,'ls');
--外键
alter table student add cardid number;
update student set cardid=1 where stuno=1;
update student set cardid=2 where stuno=2;
--外键约束
alter table student add constraint fk_student_studentcard_cardid foreign key(cardid) references studentcard(cardid);
commit;
--给student添加classid字段
alter table student add classid number;
-- 建立学生班级表
create table studentclass(classid number primary key, classname varchar2(10));
insert into studentclass values(1,'g1');
insert into studentclass values(2,'g2');
-- 外键
alter table student add constraint fk_student_class foreign key(classid) references studentclass(classid);
-- student中的classid
update student set classid=1 ;
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int cardId;
...
}
public class StudentClass {
private int classId;
private String className;
//增加学生属性(通过该字段让student和studentclass建立其关联)
List<Student> students;
...
}
14.4.2 先查班级，再查班级学生
01.未开启延迟前
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- 一对多：resultMap -->
<select id="queryClassAndStudents" parameterType="int" resultMap="class_student_map">
<!-- 查询g1班的班级信息，g1班的所有学生信息 -->
select c.*,s.* from student s
inner join studentclass c
on c.classid=s.classid
where c.classid = #{classId}
</select>
<resultMap type="StudentClass" id="class_student_map">
<!-- 因为type的主类是班级，因此 先配置班级的信息 -->
<!-- 班级信息：普通类型 -->
<id property="classId" column="classid"/>
<result property="className" column="classname"/>
<!-- 所有学生信息：对象类型 -->
<!-- 一对多对象成员使用collection映射，ofType指定该属性的元素类型 ->List<Student> students;中的Student -->
<collection property="students" ofType="Student">
<id property="stuNo" column="stuno"/>
<result property="stuName" column="stuname"/>
</collection>
</resultMap>
</mapper>
public interface StudentMapper {
//一对多
StudentClass queryClassAndStudents(int classid);
}
public static void queryClassAndStudents() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
//班级信息
StudentClass studentClass = studentMapper.queryClassAndStudents(1);
System.out.println(studentClass.getClassId() + "," + studentClass.getClassName());
//班级中的学生信息
List<Student> students = studentClass.getStudents();
for(Student student: students) {
System.out.println(student.getStuNo() + "," + student.getStuName());
}
session.close();
}
02.已开启延迟后
<mapper namespace="org.myslayers.mapper.StudentMapper">
<!-- 一对多，再查询班级里面的所有学生 -->
<select id="queryStudentsByClassId" parameterType="int" resultType="student"> //【关键代码】
select * from student where classid=#{classId}
</select>
</mapper>
<mapper namespace="org.myslayers.mapper.StudentClassMapper">
<!-- 一对多：查询全部班级，并延迟加载班级里面的每一个学生-->
<select id="queryClassAndStudentsLazyLoad" resultMap="class_student_lazyLoad_map">
<!-- 先查询班级 -->
select c.* from studentclass c
</select>
<resultMap type="studentClass" id="class_student_lazyLoad_map">
<!-- 因为type的主类是班级，因此 先配置班级的信息 -->
<!-- 班级信息：普通类型 -->
<id property="classId" column="classid"/>
<result property="className" column="classname"/>
<!-- 再查所有学生信息：对象类型 -->
<!-- 一对多对象成员使用collection映射，ofType指定该属性的元素类型 ->List<Student> students;中的Student -->
<collection property="students" ofType="student" select="org.myslayers.mapper.StudentMapper.queryStudentsByClassId" column="classid"> //引用【关键代码】
</collection>
</resultMap>
</mapper>
public interface StudentClassMapper {
//查询全部班级，并延迟加载班级里面的每一个学生
List<StudentClass> queryClassAndStudentsLazyLoad();
}
public static void queryClassAndStudentsLazyLoad() throws IOException {
Reader reader = Resources.getResourceAsReader("conf.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
SqlSession session = sessionFactory.openSession();
StudentClassMapper studentClassMapper = session.getMapper(StudentClassMapper.class);
//班级信息
List<StudentClass> studentClasses = studentClassMapper.queryClassAndStudentsLazyLoad();
//查询班级信息
for(StudentClass studentClass: studentClasses) {
System.out.println(studentClass.getClassId() + "," + studentClass.getClassName());
//查询班级的全部学生
for(Student student: studentClass.getStudents()) {
System.out.println(student.getStuNo() + "," + student.getStuName());
}
}
}
15 查询缓存
15.1 汇总
00.汇总
对于缓存数据更新机制，当某一个作用域(一级缓存 Session/二级缓存Namespaces)的进行了C/U/D 操作后
（CRUD创建、读取、更新和删除的缩写），默认该作用域下所有 select 中的缓存将被 clear
01.一级缓存
MyBatis默认开启一级缓存
---------------------------------------------------------------------------------------------------------
如果用同样的SqlSession对象查询相同的数据，
则只会在第一次查询时向数据库发送SQL语句，并将查询的结果放入到SQLSESSION中（作为缓存区）：
后续再次查询该同样的对象时，则直接从缓存中查询该对象即可（即省略了数据库的访问）
---------------------------------------------------------------------------------------------------------
一级缓存是SqlSession级别的缓存。在操作数据库时需要构造 sqlSession对象，
在对象中有一个(内存区域)数据结构（HashMap）用于存储缓存数据。
不同的sqlSession之间的缓存数据区域（HashMap）是互相不影响的
---------------------------------------------------------------------------------------------------------
基于 PerpetualCache 的 HashMap 本地缓存，其存储作用域为 Session，
当 Session flush 或 close 之后，该 Session 中的所有 Cache 就将清空，默认打开一级缓存。
02.二级缓存
默认关闭二级缓存
---------------------------------------------------------------------------------------------------------
Mybatis自带二级缓存（第三方提供）：【同一个namespace】生成的mapperx对象
namespace的值就是接口的全类名（包名.类名），通过接口可以产生代理对象（studentMapper对象）
结论：只要产生的xxMapperx对象来自于同一个namespace，则这些对象共享二级缓存。
---------------------------------------------------------------------------------------------------------
注意：二级缓存的范围是同一个namespace，如果有多个xxxMapper.xml的namespace值相同，
则通过这些xxxMapper.xml产生的xxxMapper对象仍然共享二级缓存。
-------------------------------------------------------------------------------------------------------------
Application级别的缓存，它可以提高对数据库查询的效率，以提高应用的性能。
二级缓存与一级缓存其机制相同，默认也是采用 PerpetualCache，HashMap 存储，
不同在于其存储作用域为 Mapper(Namespace)，并且可自定义存储源，如 Ehcache。
默认不打开二级缓存，要开启二级缓存，使用二级缓存属性类需要实现Serializable序列化接口(可用来保存对象的状态)，可在它的映射文件中配置<cache/> ；
15.2 表设计
01.sql
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE student';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE studentcard';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
-- studentCard
create table studentcard(cardid number primary key, cardinfo varchar2(20));
insert into studentcard values(1, 'zs info...' );
insert into studentcard values(2, 'ls info...' );
--student
create table student(stuno number, stuname varchar2(20));
insert into student values(1,'zs');
insert into student values(2,'ls');
--外键
alter table student add cardid number;
update student set cardid=1 where stuno=1;
update student set cardid=2 where stuno=2;
--外键约束
alter table student add constraint fk_student_studentcard_cardid foreign key(cardid) references studentcard(cardid);
commit;
02.java
public class Student {
private int stuNo;
private String stuName;
private int cardId;
...
}
public class StudentCard {
private int cardId;
private String cardInfo;
}
15.3 开启二级缓存
01.conf.xml
<configuration>
<properties resource="db.properties"/>
<settings>
<!-- 开启二级缓存 -->
<setting name="cacheEnabled" value="true"/>
</settings>
</configuration>
16 逆向工程
