MyBatis
ORMapping: Object Relationship Mapping 对象关系映射
对象指⾯向对象
关系指关系型数据库
Java 到 MySQL 的映射,开发者可以以⾯向对象的思想来管理数据库。
Mybatis是对JDBC的一个封装
SQL写在了XML里面,耦合度不高。(java程序不用怎么改了)
缺点是要写很多的SQL语句,然后你写的这些SQL语句都是依赖于你的数据库的,你数据库改起来不是很方便,移植性差。
(hibernate是一个全自动的orm框架,hibernate可以自动生成SQL语句,自动执行)
如何使用
- 新建Maven工程,pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.southwind</groupId>
<artifactId>aiMybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>
在这里插入代码片
- 新建数据表
use mybatis;
create table t_account(
id int primary key auto_increment,
username varchar(11),
password varchar(11),
age int
)
- 新建数据表对应的实体类Account(在 java 里创建一个com.southwind.entity的package)
package com.southwind.entity;
import lombok.Data;
@Data
public class Account {
private long id;
private String username;
private String password;
private int age;
}
- 创建MyBatis的配置文件config.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>
<!-- 配置MyBatis运⾏环境 -->
<environments default="development">
<environment id="development">
<!-- 配置JDBC事务管理 -->
<transactionManager type="JDBC"></transactionManager>
<!-- POOLED配置JDBC数据源连接池 -->
<dataSource type="POOLED">
<!--因为我是mysql5,所以这里是com.mysql.jdbc.Driver,没有cj-->
<!-- 这里是在配置你的数据库-->
<property name="driver" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</dataSource>
</environment>
</environments>
<!-- 注册AccountMapper.xml -->
<mappers>
<mapper resource="com/southwind/mapper/AccountMapper.xml"></mapper>
</mappers>
</configuration>
使用原生接口
1、MyBatis 框架需要开发者⾃定义 SQL 语句,写在 Mapper.xml ⽂件中,实际开发中,会为每个实体
类创建对应的 Mapper.xml ,定义管理该对象数据的 SQL。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.mapper.AccoutMapper">
<insert id="save" parameterType="com.southwind.entity.Account">
insert into t_account(username,password,age) values(#{username},#{password},#{age})
</insert>
</mapper>
(插播一下我们的表t_account长这个样子,怕看到后面忘了)
- namespace 通常设置为⽂件所在包+⽂件名的形式。
- insert 标签表示执⾏添加操作。
- select 标签表示执⾏查询操作。
- update 标签表示执⾏更新操作。
- delete 标签表示执⾏删除操作。
- id 是实际调⽤ MyBatis ⽅法时需要⽤到的参数。
- parameterType 是调⽤对应⽅法时参数的数据类型。
- #会把传入的数据都当成一个字符串来处理,会在传入的数据上面加一个双引号来处理。而$则是把传入的数据直接显示在sql语句中,不会添加双引号。
2、在全局配置⽂件 config.xml 中注册 AccountMapper.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>
<!-- 配置MyBatis运⾏环境 -->
<environments default="development">
<environment id="development">
<!-- 配置JDBC事务管理 -->
<transactionManager type="JDBC"></transactionManager>
<!-- POOLED配置JDBC数据源连接池 -->
<dataSource type="POOLED">
<!--因为我是mysql5,所以这里是com.mysql.jdbc.Driver,没有cj-->
<!-- 这里是在配置你的数据库-->
<property name="driver" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</dataSource>
</environment>
</environments>
<!-- 注册AccountMapper.xml -->
<mappers>
<mapper resource="com/southwind/mapper/AccountMapper.xml"></mapper>
</mappers>
</configuration>
3、调⽤ MyBatis 的原⽣接⼝执⾏添加操作。
package com.southwind.test;
import com.southwind.entity.Account;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test {
public static void main(String[] args) {
//加载MyBatis配置
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
String statement = "com.southwind.mapper.AccountMapper.save";
Account account = new Account(1L, "张三", "123123", 22);
sqlSession.insert(statement, account);
sqlSession.commit();
sqlSession.close();
}
}
通过mapper代理实现自定义接口
- 自己定义增删改查的接口(也就是写相应的业务代码)
- 自己去写接口对应的mapper.xml,mapper里写sql,与对象数据进行交互
1、自定义接口
D:\mysoft_download\aiMybatis\src\main\java\com\southwind\repository\AccountRepository.java
package com.southwind.repository;
import com.southwind.entity.Account;
import java.util.List;
public interface AccountRepository {
//返回int值
public int save(Account account);
public int update(Account account);
public int deleteById(long id);
public List<Account> findAll();
public Account find(long id);
}
2.创建对应的Mapper.xml,定义接⼝⽅法对应的 SQL 语句。
statement 标签可根据 SQL 执⾏的业务选择 insert、delete、update、select。
MyBatis 框架会根据规则⾃动创建接⼝实现类的代理对象。
规则:
- Mapper.xml 中 namespace 为接⼝的全类名。
- Mapper.xml 中 statement 的 id 为接⼝中对应的⽅法名。
- Mapper.xml 中 statement 的 parameterType 和接⼝中对应⽅法的参数类型⼀致。
- Mapper.xml 中 statement 的 resultType 和接⼝中对应⽅法的返回值类型⼀致。
D:\mysoft_download\aiMybatis\src\main\java\com\southwind\repository\AccountRepository.xml
resultType是int可以不用写,list<“T”> 作为返回值写T的全类名就行。
全类名是指com.southwind.entity.Account,Account.java是实体类
返回int是指这次sql操作改变了多少行数据
没有输入参数,可以不写parameterType
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.AccountRepository">
<insert id="save" parameterType="com.southwind.entity.Account">
<!--写sql语句啦-->
insert into t_account(username,password,age) values(#{username},#{password},#{age})
</insert>
<update id="update" parameterType="com.southwind.entity.Account">
update t_account set username = #{username},password = #{password},age = #{age} where id = #{id}
</update>
<delete id="deleteById" parameterType="long">
delete from t_account where id = #{id}
</delete>
<select id="findAll" resultType="com.southwind.entity.Account">
select * from t_account
</select>
<select id="find" parameterType="long" resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
</mapper>
3、在 config.xml 中注册 AccountRepository.xml
D:\mysoft_download\aiMybatis\src\main\resources\config.xml
<!-- 注册AccountMapper.xml -->
<mappers>
<mapper resource="com/southwind/mapper/AccountMapper.xml"></mapper>
<mapper resource="com/southwind/repository/AccountRepository.xml"></mapper>
</mappers>
4、调⽤接⼝的代理对象完成相关的业务操作
D:\mysoft_download\aiMybatis\src\main\java\com\southwind\test\Test1.java
package com.southwind.test;
import com.southwind.entity.Account;
import com.southwind.repository.AccountRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
public class Test1 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取实现接口的代理对象
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
// //1.测试添加对象
// Account account1 = new Account(6L,"张三","1231111",18);
// accountRepository.save(account1);
// //改变之后要提交commit
// sqlSession.commit();
// //使用完要关闭close
// sqlSession.close();
// //2.测试更新对象
// Account account1 = new Account(6L,"张三","1231111",22);
// int result = accountRepository.update(account1);
// System.out.println(result);
// sqlSession.commit();
// sqlSession.close();
// //3.测试删除方法
// int result = accountRepository.deleteById(5L);
// sqlSession.commit();
// sqlSession.close();
// //4.测试findAll方法
// List<Account> lists= accountRepository.findAll();
// for(Account list:lists){
// System.out.println(list);
// }
// sqlSession.close();
//5.测试find方法
System.out.println(accountRepository.find(6L));
sqlSession.close();
}
}
(以上代码配合Navicat观察数据库对象食用,注意好像用Navicat直接删除对象之后,再添加对象的id也就是主键是有问题的!!!)
mapper.xml
- statement 标签:select、update、delete、insert 分别对应查询、修改、删除、添加操作
- parameterType:参数数据类型
1、剧本数据类型,通过id查询Account
src/main/java/com/southwind/repository/AccountRepository.xml
<select id="findById" parameterType="long"
resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
2、String 类型,通过 name 查询 Account
src/main/java/com/southwind/repository/AccountRepository.xml
//6.测试findByname方法
System.out.println(accountRepository.findByName("张三"));
sqlSession.close();
3、包装类,通过 id 查询 Account
这里采用long的包装类
src/main/java/com/southwind/repository/AccountRepository.java 添加
public Account findById(Long id);
src/main/java/com/southwind/test/Test1.java添加
//7.测试findById方法
Long s = Long.parseLong("6");
System.out.println(accountRepository.findById(s));
sqlSession.close();
src/main/java/com/southwind/repository/AccountRepository.xml添加
<select id="findById" parameterType="java.lang.Long" resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
4、多个参数,通过 name 和 age 查询 Account
src/main/java/com/southwind/repository/AccountRepository.java
public Account findByNameAndAge(String name,int age);
src/main/java/com/southwind/repository/AccountRepository.xml
<select id="findByNameAndAge" resultType="com.southwind.entity.Account">
select * from t_account where username = #{arg0} and age = #{arg1}
</select>
src/main/java/com/southwind/test/Test1.java
//8.测试findByNameAndAge方法
System.out.println(accountRepository.findByNameAndAge("张三",22));
sqlSession.close();
5、java Bean
src/main/java/com/southwind/repository/AccountRepository.xml
<update id="update" parameterType="com.southwind.entity.Account">
update t_account set username = #{username},password = #{password},age =
#{age} where id = #{id}
</update>
- resultType: 结果类型
这个resultType也就是mapper.xml里的一个标签嘛,和前面的id啊,statement啊,parameterType啊,都是一样的,下面的变化比较简单吧,就是说我的resultType可以很多遍,可以是int,可以是String,可以是包装类,也可以是java bean对吧,取决于你在前面写的这个接口想要的是啥呀,下面举了几个例子,但是自己就跑过了,比较简单哈。
1、基本数据类型,统计 Account 总数
<select id="count" resultType="int">
select count(id) from t_account
</select>
2、包装类,统计 Account 总数
<select id="count2" resultType="java.lang.Integer">
select count(id) from t_account
</select>
3、String 类型,通过 id 查询 Account 的 name
<select id="findNameById" resultType="java.lang.String">
select username from t_account where id = #{id}
</select>
4、Java Bean
<select id="findById" parameterType="long"
resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
级联查询
一对多的使用
输出student对象
创建的数据库长这样
一个叫classes,一个叫student
创建的sql代码分别如下
create table Student(
id int primary key auto_increment,
name varchar(11),
cid varchar(11)
)
ALTER TABLE mybatis.Student CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
create table Classes(
id int primary key auto_increment,
name varchar(11)
)
ALTER TABLE mybatis.Classes CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
创建类对象Student 和Classes
src/main/java/com/southwind/entity/Student.java
package com.southwind.entity;
import lombok.Data;
@Data
public class Student {
private long id;
private String name;
private Classes classes;
}
src/main/java/com/southwind/entity/Classes.java
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Classes {
private long id;
private String name;
private List<Student> students;
}
可以看到一个班级是有多个学生,一个学生只有一个班级
创建学生接口方法与对应的Mapper
src/main/java/com/southwind/repository/StudentRepository.java
package com.southwind.repository;
import com.southwind.entity.Student;
public interface StudentRepository {
public Student findById(long id);
}
src/main/java/com/southwind/repository/StudentRepository.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="com.southwind.repository.StudentRepository">
<resultMap id="StudentMap" type="com.southwind.entity.Student">
<id column="student_id" property="id"></id>
<result column="student_name" property="name"></result>
<association property="classes" javaType="com.southwind.entity.Classes">
<id column="class_id" property="id"></id>
<result column="class_name" property="name"></result>
</association>
</resultMap>
<select id="findById" parameterType="long" resultMap="StudentMap">
select s.id as student_id,s.name student_name,c.id as class_id,c.name as class_name from student s,classes c where s.id = #{id} and s.cid = c.id
</select>
</mapper>
在config.xml中注册StudentRepository.xml
<!-- 注册AccountMapper.xml -->
<mappers>
<mapper resource="com/southwind/mapper/AccountMapper.xml"></mapper>
<mapper resource="com/southwind/repository/AccountRepository.xml"></mapper>
<mapper resource="com/southwind/repository/StudentRepository.xml"></mapper>
</mappers>
建立测试函数
src/main/java/com/southwind/test/Test2.java
package com.southwind.test;
import com.southwind.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test2 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取实现接口的代理对象
StudentRepository studentRepository = sqlSession.getMapper(StudentRepository.class);
System.out.println(studentRepository.findById(2L));
sqlSession.close();
}
}
主体思路就是,先写一个接口类StudentRepository.java,因为我要写关于他的一些操作方法,增删改查之类的,这里只写了一个方法findById。
通过student 的id,返回一个Student的对象,具体怎么实现,怎么从Mysql中拿到数据,然后再映射到Student这个对象上,要看下面的Mapper怎么写。
StudentRepository.xml 先利用sql语句从Mysql中拿数据,然后通过studentMap将Mysql的结果与对象进行映射。
Test2.java,具体实现就比较简单啦!
输出Classes对象
创建学生接口方法与对应的Mapper
src/main/java/com/southwind/repositoryClassesRepository.java
package com.southwind.repository;
import com.southwind.entity.Classes;
public interface ClassesRepository {
public Classes findById(long id);
}
src/main/java/com/southwind/repository/ClassesRepository.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="com.southwind.repository.ClassesRepository">
<resultMap id="ClassesMap" type="com.southwind.entity.Classes">
<id column="class_id" property="id"></id>
<result column="class_name" property="name"></result>
<collection property="students" ofType="com.southwind.entity.Student">
<id column="student_id" property="id"/>
<result column="student_name" property="name"/>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="ClassesMap">
select s.id as student_id,s.name as student_name,c.id as class_id,c.name as class_name from student s,classes c where c.id = #{id} and s.cid = c.id
</select>
</mapper>
在config.xml中注册ClassesRepository.xml
<!-- 注册AccountMapper.xml -->
<mappers>
<mapper resource="com/southwind/mapper/AccountMapper.xml"></mapper>
<mapper resource="com/southwind/repository/AccountRepository.xml"></mapper>
<mapper resource="com/southwind/repository/StudentRepository.xml"></mapper>
<mapper resource="com/southwind/repository/ClassesRepository.xml"></mapper>
</mappers>
建立测试函数
src/main/java/com/southwind/test/Test2.java
package com.southwind.test;
import com.southwind.repository.ClassesRepository;
import com.southwind.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test2 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// //获取实现接口的代理对象
// StudentRepository studentRepository = sqlSession.getMapper(StudentRepository.class);
// System.out.println(studentRepository.findById(2L));
// sqlSession.close();
//获取实现接口的代理对象
ClassesRepository classesRepository = sqlSession.getMapper(ClassesRepository.class);
System.out.println(classesRepository.findById(2L));
sqlSession.close();
}
}
得到结果
成功!
附上sql查询的结果
可以看到sql输出的结果和我们输出的Classes对象时对应上的!
多对多的使用
输出Customer对象
建表sql代码
create table customer(
id int primary key auto_increment,
name varchar(11)
)
ALTER TABLE mybatis.customer CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
create table goods(
id int primary key auto_increment,
name varchar(11)
)
ALTER TABLE mybatis.goods CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
create table customer_goods(
id int primary key auto_increment,
cid INT NOT NULL,
gid INT NOT NULL
) DEFAULT CHARSET=utf8;
写src/main/java/com/southwind/entity/Customer.java
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Customer {
private long id;
private String name;
private List<Goods> goods;
}
写src/main/java/com/southwind/entity/Goods.java
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Goods {
private long id;
private String name;
private List<Customer> customers;
}
写src/main/java/com/southwind/repository/CustomerRepository.java
package com.southwind.repository;
import com.southwind.entity.Customer;
public interface CustomerRepository {
public Customer findById(long id);
}
写src/main/java/com/southwind/repository/CustomerRepository.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="com.southwind.repository.CustomerRepository">
<resultMap id="CustomerMap" type="com.southwind.entity.Customer">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="goods" ofType="com.southwind.entity.Goods">
<id column="gid" property="id"/>
<result column="gname" property="name"/>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="CustomerMap">
SELECT c.id cid,c.name cname,g.id gid,g.name gname FROM customer c ,goods g ,customer_goods cg WHERE c.id = #{id} and cg.cid = c.id and cg.gid=g.id;
</select>
</mapper>
写src/main/resources/config.xml
<!-- 注册AccountMapper.xml -->
<mappers>
<mapper resource="com/southwind/mapper/AccountMapper.xml"></mapper>
<mapper resource="com/southwind/repository/AccountRepository.xml"></mapper>
<mapper resource="com/southwind/repository/StudentRepository.xml"></mapper>
<mapper resource="com/southwind/repository/ClassesRepository.xml"></mapper>
<mapper resource="com/southwind/repository/CustomerRepository.xml"></mapper>
</mappers>
写src/main/java/com/southwind/test/Test3.java
package com.southwind.test;
import com.southwind.repository.CustomerRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test3 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
CustomerRepository customerRepository =sqlSession.getMapper(CustomerRepository.class);
System.out.println(customerRepository.findById(1L));
sqlSession.close();
}
}
最后输出结果如下,符合预期
输出Goods对象
在输出customer对象的时候就已经定义好了Customer 和Goods这两个对象,只需要开始写对应的方法接口和mapper文件,并在config.xml中注册,并调用业务方法进行测试就行了。
src/main/java/com/southwind/repository/GoodsRepository.java
package com.southwind.repository;
import com.southwind.entity.Goods;
public interface GoodsRepository {
public Goods findById(long id);
}
src/main/java/com/southwind/repository/GoodsRepository.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="com.southwind.repository.GoodsRepository">
<resultMap id="GoodsMap" type="com.southwind.entity.Goods">
<id column="gid" property="id"></id>
<result column="gname" property="name"></result>
<collection property="customers" ofType="com.southwind.entity.Customer">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="GoodsMap">
SELECT c.id cid,c.name cname,g.id gid,g.name gname FROM customer c ,goods g ,customer_goods cg WHERE g.id = #{id} and cg.cid = c.id and cg.gid=g.id;
</select>
</mapper>
src/main/resources/config.xml
<!-- 注册AccountMapper.xml -->
<mappers>
<mapper resource="com/southwind/mapper/AccountMapper.xml"></mapper>
<mapper resource="com/southwind/repository/AccountRepository.xml"></mapper>
<mapper resource="com/southwind/repository/StudentRepository.xml"></mapper>
<mapper resource="com/southwind/repository/ClassesRepository.xml"></mapper>
<mapper resource="com/southwind/repository/CustomerRepository.xml"></mapper>
<mapper resource="com/southwind/repository/GoodsRepository.xml"></mapper>
</mappers>
src/main/java/com/southwind/test/Test4.java
package com.southwind.test;
import com.southwind.repository.CustomerRepository;
import com.southwind.repository.GoodsRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test4 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// CustomerRepository customerRepository =sqlSession.getMapper(CustomerRepository.class);
// System.out.println(customerRepository.findById(1L));
GoodsRepository goodsRepository =sqlSession.getMapper(GoodsRepository.class);
System.out.println(goodsRepository.findById(3L));
sqlSession.close();
}
}
逆向工程
逆向⼯程
MyBatis 框架需要:实体类、⾃定义 Mapper 接⼝、Mapper.xml
传统的开发中上述的三个组件需要开发者⼿动创建,逆向⼯程可以帮助开发者来⾃动创建三个组件,减
轻开发者的⼯作量,提⾼⼯作效率。
如何使用
MyBatis Generator,简称 MBG,是⼀个专⻔为 MyBatis 框架开发者定制的代码⽣成器,可⾃动⽣成
MyBatis 框架所需的实体类、Mapper 接⼝、Mapper.xml,⽀持基本的 CRUD 操作,但是⼀些相对复
杂的 SQL 需要开发者⾃⼰来完成。
- 新建 Maven ⼯程,pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.southwind</groupId>
<artifactId>aimbg</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</project>
-
创建 MBG 配置⽂件 generatorConfig.xml
1、jdbcConnection 配置数据库连接信息。
2、javaModelGenerator 配置 JavaBean 的⽣成策略。
3、sqlMapGenerator 配置 SQL 映射⽂件⽣成策略。
4、javaClientGenerator 配置 Mapper 接⼝的⽣成策略。
5、table 配置⽬标数据表(tableName:表名,domainObjectName:JavaBean 类名)。
(一定要有这个数据表呀,这个逆向工程就是帮你根据数据表创建这些东西的) -
创建 Generator 执⾏类
根据这一张表
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="testTables" targetRuntime="MyBatis3">
<jdbcConnection
driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8"
userId="root"
password="root"
></jdbcConnection>
<javaModelGenerator targetPackage="com.southwind.entity"
targetProject="./src/main/java"></javaModelGenerator>
<sqlMapGenerator targetPackage="com.southwind.repository"
targetProject="./src/main/java"></sqlMapGenerator>
<javaClientGenerator type="XMLMAPPER" targetPackage="com.southwind.repository" targetProject="./src/main/java">
</javaClientGenerator>
<table tableName="t_account" domainObjectName="Account"></table>
</context>
</generatorConfiguration>
- 创建 Generator 执⾏类。
package com.southwind.test;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Main {
public static void main(String[] args) {
List<String> warings = new ArrayList<String>();
boolean overwrite = true;
String genCig = "/generatorConfig.xml";
File configFile = new File(Main.class.getResource(genCig).getFile());
ConfigurationParser configurationParser = new
ConfigurationParser(warings);
Configuration configuration = null;
try {
configuration = configurationParser.parseConfiguration(configFile);
} catch (IOException e) {
e.printStackTrace();
} catch (XMLParserException e) {
e.printStackTrace();
}
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = null;
try {
myBatisGenerator = new
MyBatisGenerator(configuration,callback,warings);
} catch (InvalidConfigurationException e) {
e.printStackTrace();
}
try {
myBatisGenerator.generate(null);
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
- 结果
可以看到实体类,接口,Mapper文件都已经创建出来了。
MyBatis 延迟加载
-
什么是延迟加载
延迟加载也叫懒加载、惰性加载,使⽤延迟加载可以提⾼程序的运⾏效率,针对于数据持久层的操作,在某些特定的情况下去访问特定的数据库,在其他情况下可以不访问某些表,从⼀定程度上减少了 Java应⽤与数据库的交互次数。
查询学⽣和班级的时,学⽣和班级是两张不同的表,如果当前需求只需要获取学⽣的信息,那么查询学⽣单表即可,如果需要通过学⽣获取对应的班级信息,则必须查询两张表。
不同的业务需求,需要查询不同的表,根据具体的业务需求来动态减少数据表查询的⼯作就是延迟加载(考虑多采取单表查询迭代的方式,而不是采用联合表查询方式) -
首先我们将多表联合查询修改为单表查询,这样才能使用延迟加载的特性
src/main/java/com/southwind/repository/ClassesRepository.java
(加一个查询接口)
public Classes findByIdLazy(long id);
src/main/java/com/southwind/repository/ClassesRepository.xml
(写好对应的sql,注意如果你使用了与Classes类不同的列名的话,就一定要写ResultMap参数,然后一一映射上去,不然是得不到相应结果的,这里是没有改名字,将sql查询到的id和name直接映射到了Classes类里的成员变量id和name)
<select id="findByIdLazy" parameterType="long" resultType="com.southwind.entity.Classes">
select c.id ,c.name from classes c where c.id = #{id}
</select>
src/main/java/com/southwind/repository/StudentRepository.java
(写好方法接口)
public Student findByIdLazy(long id);
src/main/java/com/southwind/repository/StudentRepository.xml
<resultMap id="StudentMapLazy" type="com.southwind.entity.Student">
<id column="student_id" property="id"></id>
<result column="student_name" property="name"></result>
<association property="classes" javaType="com.southwind.entity.Classes" select="com.southwind.repository.ClassesRepository.findByIdLazy" column="class_id"></association>
</resultMap>
<select id="findByIdLazy" parameterType="long" resultMap="StudentMapLazy">
select s.id as student_id,s.name as student_name,s.cid as class_id from student s where s.id = #{id}
</select>
- 写好测试类
src/main/java/com/southwind/test/Test5.java
package com.southwind.test;
import com.southwind.entity.Student;
import com.southwind.repository.ClassesRepository;
import com.southwind.repository.GoodsRepository;
import com.southwind.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test5 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
// CustomerRepository customerRepository =sqlSession.getMapper(CustomerRepository.class);
// System.out.println(customerRepository.findById(1L));
StudentRepository studentRepository =sqlSession.getMapper(StudentRepository.class);
Student student = studentRepository.findByIdLazy(1L);
System.out.println(student.getName());
sqlSession.close();
}
}
- 在config.xml中加入延迟加载功能
<settings>
<!-- 打印SQL-->
<setting name="logImpl" value="STDOUT_LOGGING" />
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
得到实验结果,可以看到在获取name的时候只进行了一次sql,因为获取name在延迟加载过程只需要查student表就好了。
可以看到
- 关闭延迟加载功能
从实验结果可以看出来,进行了两次sql,所以采用延迟加载可以减少查表次数。
MyBatis 缓存
- 什么是 MyBatis 缓存
使⽤缓存可以减少 Java 应⽤与数据库的交互次数,从⽽提升程序的运⾏效率。⽐如查询出 id = 1 的对象,第⼀次查询出之后会⾃动将该对象保存到缓存中,当下⼀次查询时,直接从缓存中取出对象即可,⽆需再次访问数据库。
1、⼀级缓存:SqlSession 级别,默认开启,并且不能关闭。
操作数据库时需要创建 SqlSession 对象,在对象中有⼀个 HashMap ⽤于存储缓存数据,不同的SqlSession 之间缓存数据区域是互不影响的。⼀级缓存的作⽤域是 SqlSession 范围的,当在同⼀个 SqlSession 中执⾏两次相同的 SQL 语句事,第⼀次执⾏完毕会将结果保存到缓存中,第⼆次查询时直接从缓存中获取。需要注意的是,如果 SqlSession 执⾏了 DML 操作(insert、update、delete),MyBatis 必须将缓存清空以保证数据的准确性。
2、⼆级缓存:Mapper 级别,默认关闭,可以开启。
使⽤⼆级缓存时,多个 SqlSession 使⽤同⼀个 Mapper 的 SQL 语句操作数据库,得到的数据会存在⼆级缓存区,同样是使⽤ HashMap 进⾏数据存储,相⽐较于⼀级缓存,⼆级缓存的范围更⼤,多个SqlSession 可以共⽤⼆级缓存,⼆级缓存是跨 SqlSession 的。
⼆级缓存是多个 SqlSession 共享的,其作⽤域是 Mapper 的同⼀个namespace,不同的 SqlSession两次执⾏相同的 namespace 下的 SQL 语句,参数也相等,则第⼀次执⾏成功之后会将数据保存到⼆级缓存中,第⼆次可直接从⼆级缓存中取出数据。
(https://blog.csdn.net/jinbaizhe/article/details/81158514 MyBatis中调用SqlSession.commit()和SqlSession.close()对二级缓存的影响)
代码
- 一级缓存
可以通过查看sql的日志文件来看sql查询了多少次,缓存是可以减少sql查询的次数的。
src/main/java/com/southwind/test/Test5.java
package com.southwind.test;
import com.southwind.entity.Account;
import com.southwind.entity.Student;
import com.southwind.repository.AccountRepository;
import com.southwind.repository.ClassesRepository;
import com.southwind.repository.GoodsRepository;
import com.southwind.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test5 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
Account account = accountRepository.findById(6L);
System.out.println(account);
Account account1 = accountRepository.findById(6L);
System.out.println(account1);
sqlSession.close();
}
}
上面的代码,在同一个sqlsession中查询了两次id为6L的过程,从下面的实验结果可以发现,sql只执行了一次,在执行第二次的时候是直接从内存中获取,不再进行sql查询操作,节省了sql查询时间。
src/main/java/com/southwind/test/Test5.java
package com.southwind.test;
import com.southwind.entity.Account;
import com.southwind.entity.Student;
import com.southwind.repository.AccountRepository;
import com.southwind.repository.ClassesRepository;
import com.southwind.repository.GoodsRepository;
import com.southwind.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test5 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
Account account = accountRepository.findById(6L);
System.out.println(account);
sqlSession.close();
sqlSession = sqlSessionFactory.openSession();
accountRepository = sqlSession.getMapper(AccountRepository.class);
Account account1 = accountRepository.findById(6L);
System.out.println(account1);
sqlSession.close();
}
}
不同sqlsession在一级缓存中并不适用,开启了不同sqlsession之后就要重新查询一次,这里总共就查询了两次。
- 二级缓存
1、MyBatis ⾃带的⼆级缓存 - config.xml 配置开启⼆级缓存
<settings>
<!-- 打印SQL-->
<setting name="logImpl" value="STDOUT_LOGGING" />
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 开启⼆级缓存 -->
<setting name="cacheEnabled" value="true"/>
</settings>
- Mapper.xml 中配置⼆级缓存
<cache></cache>
src/main/java/com/southwind/repository/AccountRepository.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="com.southwind.repository.AccountRepository">
<cache></cache>
<insert id="save" parameterType="com.southwind.entity.Account">
<!--写sql语句啦-->
insert into t_account(username,password,age) values(#{username},#{password},#{age})
</insert>
<update id="update" parameterType="com.southwind.entity.Account">
update t_account set username = #{username},password = #{password},age = #{age} where id = #{id}
</update>
<delete id="deleteById" parameterType="long">
delete from t_account where id = #{id}
</delete>
<select id="findAll" resultType="com.southwind.entity.Account">
select * from t_account
</select>
<select id="find" parameterType="long" resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
<select id="findByName" parameterType="java.lang.String" resultType="com.southwind.entity.Account">
select * from t_account where username = #{name}
</select>
<select id="findById" parameterType="java.lang.Long" resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
<select id="findByNameAndAge" resultType="com.southwind.entity.Account">
select * from t_account where username = #{arg0} and age = #{arg1}
</select>
</mapper>
- 实体类实现序列化接⼝
src/main/java/com/southwind/entity/Account.java
package com.southwind.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Account implements Serializable {
private long id;
private String username;
private String password;
private int age;
}
测试类
2、ehcache ⼆级缓存
- pom.xml 添加相关依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>net.sf.ehcache</groupId>
<artifactId>ehcache-core</artifactId>
<version>2.4.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-simple -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>1.7.25</version>
</dependency>
- 添加 ehcache.xml
src/main/resources/ehcache.xml
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd">
<diskStore/>
<defaultCache
maxElementsInMemory="1000"
maxElementsOnDisk="10000000"
eternal="false"
overflowToDisk="false"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU">
</defaultCache>
</ehcache>
- config.xml 配置开启⼆级缓存
<settings>
<!-- 打印SQL-->
<setting name="logImpl" value="STDOUT_LOGGING" />
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="false"/>
<!-- 开启⼆级缓存 -->
<setting name="cacheEnabled" value="true"/>
</settings>
- Mapper.xml 中配置⼆级缓存
src/main/java/com/southwind/repository/AccountRepository.xml
<cache type="org.mybatis.caches.ehcache.EhcacheCache">
<!-- 缓存创建之后,最后⼀次访问缓存的时间⾄缓存失效的时间间隔 -->
<property name="timeToIdleSeconds" value="3600"/>
<!-- 缓存⾃创建时间起⾄失效的时间间隔 -->
<property name="timeToLiveSeconds" value="3600"/>
<!-- 缓存回收策略,LRU表示移除近期使⽤最少的对象 -->
<property name="memoryStoreEvictionPolicy" value="LRU"/>
</cache>
- 实体类不需要实现序列化接⼝(不需要像自带二级缓存那样了)
package com.southwind.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Account implements Serializable {
private long id;
private String username;
private String password;
private int age;
}
- 测试方法
src/main/java/com/southwind/test/Test6.java
package com.southwind.test;
import com.southwind.entity.Account;
import com.southwind.repository.AccountRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test6 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
Account account = accountRepository.findById(6L);
System.out.println(account);
sqlSession.close();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
AccountRepository accountRepository2 = sqlSession2.getMapper(AccountRepository.class);
Account account1 = accountRepository2.findById(6L);
System.out.println(account1);
sqlSession.close();
System.out.println("account:"+account.hashCode()+" account1:"+account1.hashCode());
}
}
结果
从实验结果可以看到,不同sqlsession只进行了一次查询,并且得到的两个对象的hashcode是相同的。
MyBatis 动态 SQL
- 现在先尝试说修改一下接口和mapper,来演示一下动态sql的用处,因为sql是写在mapper里的嘛,所以mapper文件肯定是要改的,然后就是接口方法可能就是说用来演示sql需要用到新的查询方法。教程里是根据一个部分初始化的Account的对象,从数据库中找到对应的数据,然后再返回一个或者多个完整的Account对象,看起来有点脱裤子放屁的感觉,不过为了演示的话,就这样了吧,之后遇到实际的场景再说。
src/main/java/com/southwind/repository/AccountRepository.java
public Account findByAccount(Account account);
src/main/java/com/southwind/repository/AccountRepository.xml
<select id="findByAccount" resultType="com.southwind.entity.Account" parameterType="com.southwind.entity.Account">
select * from t_account where
<if test="id!=0">
id =#{id}
</if>
<if test="username!=null">
and username =#{username}
</if>
<if test="password!=null">
and password =#{password}
</if>
<if test="age!=0">
and age =#{age}
</if>
</select>
测试类
package com.southwind.test;
import com.southwind.entity.Account;
import com.southwind.repository.AccountRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test7 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
Account accountInput = new Account();
accountInput.setId(6L);//设置id
accountInput.setAge(22);//设置age
Account account = accountRepository.findByAccount(accountInput);
System.out.println(account);
sqlSession.close();
}
}
可以看到使用的sql语句是
select * from t_account where id =? and age = ?,只有赋值过的部分才写好sql语句。
if 标签可以⾃动根据表达式的结果来决定是否将对应的语句添加到 SQL 中,如果条件不成⽴则不添加,
如果条件成⽴则添加。
但前面的代码有一个问题,如果我没有对输入的Account的id进行赋值的话,直接到username,就会出现sql语句是这样的情况
select * from t_account where and username =?
这样的sql语句是错误的!怎么解决呢,整体加一个where标签就可以啦!
where 标签
<select id="findByAccount" resultType="com.southwind.entity.Account" parameterType="com.southwind.entity.Account">
select * from t_account
<where>
<if test="id!=0">
id =#{id}
</if>
<if test="username!=null">
and username =#{username}
</if>
<if test="password!=null">
and password =#{password}
</if>
<if test="age!=0">
and age =#{age}
</if>
</where>
</select>
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
Account accountInput = new Account();
accountInput.setUsername("张三");
accountInput.setAge(22);
Account account = accountRepository.findByAccount(accountInput);
System.out.println(account);
sqlSession.close();
没有报错成功!
where 标签可以⾃动判断是否要删除语句块中的 and 关键字,如果检测到 where 直接跟 and 拼接,则
⾃动删除 and,通常情况下 if 和 where 结合起来使⽤。
choose 、when 标签
感觉这个
这两个配合使用感觉类似于switch
<select id="findByAccount" resultType="com.southwind.entity.Account" parameterType="com.southwind.entity.Account">
select * from t_account
<where>
<choose>
<when test="id!=0">
id = #{id}
</when>
<when test="username=!null">
username = #{username}
</when>
<when test="password!=null">
password = #{password}
</when>
<when test="age!=0">
age = #{age}
</when>
</choose>
</where>
</select>
trim 标签
trim 标签中的 prefix 和 suffix 属性会被⽤于⽣成实际的 SQL 语句,会和标签内部的语句进⾏拼接,如
果语句前后出现了 prefixOverrides 或者 suffixOverrides 属性中指定的值,MyBatis 框架会⾃动将其删
除。
<select id="findByAccount" parameterType="com.southwind.entity.Account"
resultType="com.southwind.entity.Account">
select * from t_account
<trim prefix="where" prefixOverrides="and">
<if test="id!=0">
id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
<if test="password!=null">
and password = #{password}
</if>
<if test="age!=0">
and age = #{age}
</if>
</trim>
</select>
set 标签
set 标签⽤于 update 操作,会⾃动根据参数选择⽣成 SQL 语句。
public int updateLazy(Account account);
<update id="updateLazy" parameterType="com.southwind.entity.Account">
update t_account
<set>
<if test="username!=null">
username = #{username},
</if>
<if test="password!=null">
password = #{password},
</if>
<if test="age!=0">
age = #{age}
</if>
</set>
where id = #{id}
</update>
package com.southwind.test;
import com.southwind.entity.Account;
import com.southwind.repository.AccountRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test9 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
Account accountInput = new Account();
accountInput.setId(0L);
accountInput.setUsername("王五麻子");
accountRepository.updateLazy(accountInput);
sqlSession.commit();
sqlSession.close();
}
}
sql语句如下,可以看到根据id查询,只更新了username。
可以看到对username进行了更改
foreach 标签
src/main/java/com/southwind/entity/Account.java
package com.southwind.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Account implements Serializable {
private long id;
private String username;
private String password;
private int age;
private List<Long> ids;
public Account(long id, String username, String password, int age) {
this.id = id;
this.username = username;
this.password = password;
this.age = age;
}
}
src/main/java/com/southwind/test/Test10.java
package com.southwind.test;
import com.southwind.entity.Account;
import com.southwind.repository.AccountRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class Test10 {
public static void main(String[] args) {
//加载MyBatis配置⽂件
InputStream inputStream =
Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new
SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory =
sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
AccountRepository accountRepository = sqlSession.getMapper(AccountRepository.class);
Account accountInput = new Account();
List<Long> ids = new ArrayList<>();
ids.add(2L);
ids.add(6L);
accountInput.setIds(ids);
System.out.println(accountRepository.findByIds(accountInput));
sqlSession.close();
}
}
src/main/java/com/southwind/repository/AccountRepository.xml
<select id="findByIds" parameterType="com.southwind.entity.Account"
resultType="com.southwind.entity.Account">
select * from t_account
<where>
<foreach collection="ids" open="id in (" close=")" item="id"
separator=",">
#{id}
</foreach>
</where>
</select>
结果
也算是用到for-each。