在idea中操作mybatis
前言:作为一个使用eclispe多年的人来说,确实对eclipse都有点感情了,但是没办法,技术在发展,时代在进步,所以我今天准备彻底从抛弃eclipse转向idea了,再见eclipse !!!
一:在idea关联maven
注:想要pom.xml中添加的依赖生效,必须勾选如下多选框!!
在idea中要勾选,自动导入maven,那么你在pom.xml引用的jar包才会生效!
二:在idea中使用lombok插件
1.在File-Setting-Plugins-Browse Repostitories中搜索Lombok Plugin插件安装
2.别急着重启,继续设置,在File-Setting-Build, Execution, Deployment-Compiler-Annotation Processors 中点击Enable annotation processors
3.在pom.xml中引入lombok依赖
4.使用lombok中的注解即可
- lombok的maven依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
<scope>provided</scope>
</dependency>
- lombok中注解的使用和描述
@Data :注解在类上;提供类所有属性的 getting 和 setting 方法,此外还提供了equals、canEqual、hashCode、toString 方法 @Setter:注解在属性上;为属性提供 setting 方法 @Getter:注解在属性上;为属性提供 getting 方法 @Log4j :注解在类上;为类提供一个 属性名为log 的 log4j 日志对象 @NoArgsConstructor:注解在类上;为类提供一个无参的构造方法 @AllArgsConstructor:注解在类上;为类提供一个全参的构造方法
三:在idea中连接MySQL
看报错的信息,说的是跟“时区”有关,回想起我之前在玩hibernate时也遇到过如下错误,所以解决办法是往后面加参数
jdbcUrl=jdbc:mysql://127.0.0.1:3306/mybatis?serverTimezone=UTC&useUnicode=true&charaterEncoding=utf-8&useSSL=false
四:在idea中使用junit
由于idea中已经集成了junit插件,因此只要在pom.xml添加依赖即可!!
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
public interface AccountDao {
void save(Account account);
void delete(Serializable id);
void update(Account account);
Account findById(Serializable id);
List<Account> findAll();
}
选中“AccountDao”, 右击generator–>点击Test
生成的效果如下
public class AccountDaoTest {
@Before
public void setUp() throws Exception {
}
@After
public void tearDown() throws Exception {
}
@Test
public void save() {
}
@Test
public void delete() {
}
}
想自动生成junit case,还需要下载一个插件JUnitGenerator V2.0
五:回顾mybatis
1、引入maven依赖
<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>8.0.11</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
<scope>provided</scope>
</dependency>
</dependencies>
2、创建mybatis的核心配置文件(自定义)
<?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">
<property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
<!--在配置文件中需要将: &改为&-->
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&charaterEncoding=utf-8&useSSL=false"></property>
<property name="username" value="root"></property>
<property name="password" value="123456"></property>
</dataSource>
</environment>
</environments>
<!--注册AccountMapper.xml-->
<mappers>
<!--如果下面这种方式写的话:程序就不知道是以.AccountMapper.xml结尾,还是.xml结尾,所以就换成了/-->
<!-- <mapper resource="cn.itcast.mapper.AccountMapper.xml"></mapper>-->
<mapper resource="cn/itcast/mapper/AccountMapper.xml"></mapper>
</mappers>
</configuration>
3、创建表和实体类
create table account
(
id int auto_increment primary key,
username varchar(20) not null,
password varchar(20) not null,
age int null
)
/**
* @data:为属性提供了getter/setter/equals/hashcode等方法
* @NoArgsConstructor:无参构造
* @AllArgsConstructor: 有参构造
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
private Integer id;
private String username;
private String password;
private Integer age;
}
Mybatis的开发方式
- 使用原生接口
- 自定义Mapper接口(代理实现)
一:使用原生接口
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--使用原生接口: namespace:不用去带.xml-->
<mapper namespace="cn.itcast.mapper.AccountMapper">
<!--
可以想象成一个方法
void saveAccount(Account);
#{username}: 就相当于去Account中取出username的值!,sql语句不需要带分号!
-->
<insert id="saveAccount" parameterType="cn.itcast.entity.Account">
insert into Account(username,password,age) values(#{username},#{password},#{age})
</insert>
</mapper>
namespace 通常设置为⽂文件所在包+⽂文件名的形式。
id 是实际调⽤用 MyBatis ⽅方法时需要⽤用到的参数。
parameterType 是调⽤用对应⽅方法时参数的数据类型。
1、在mybatis全局配置⽂文件中注册 AccountMapper.xml
<mappers>
<mapper resource="cn/itcast/mapper/AccountMapper.xml"></mapper>
</mappers>
2、测试
public class MybatisTest {
public static void main(String[] args) throws IOException {
InputStream inputStream = MybatisTest.class.getClassLoader().getResourceAsStream("mybatis.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
Account account = new Account(2,"dyt","123456",21);
sqlSession.insert("cn.itcast.mapper.AccountMapper.saveAccount", account);
//更新操作都需要提交才会生效!
sqlSession.commit();
sqlSession.close();
/**
* 遇到的异常
* 1.Caused by: java.io.IOException: Could not find resource cn/itcast/mapper/AccountMapper.xml
* 需要在pom.xml中加上一个bulid标签
*
* 2.Caused by: java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone.
* 需要:在核心配置文件中的url: 数据库?serverTimezone=UTC&useUnicode=true&charaterEncoding=utf-8&useSSL=false
* 注:在配置文件中需要将&改成&
*/
}
}
增删改查标签:统称为statement标签!
二:Mapper代理实现自定义接口
比起原生接口:更清晰,更加类型安全,还不用担心可能出错的字符串字面值以及强制类型转换。
public interface AccountDao {
void save(Account account);
void delete(Serializable id);
void update(Account account);
Account findById(Serializable id);
List<Account> findAll();
}
<?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的值,应该是接口的全类限定名!-->
<mapper namespace="cn.itcast.dao.AccountDao">
<!-- void save(Account account);-->
<insert id="save" parameterType="cn.itcast.entity.Account">
insert into account(username,password,age) values(#{username},#{password},#{age})
</insert>
<!--void delete(Serializable id); 注:Serializable作为参数的话可不写!-->
<delete id="delete" parameterType="java.io.Serializable">
delete from account where id = #{id}
</delete>
<!-- void update(Account account);-->
<update id="update" parameterType="cn.itcast.entity.Account">
update account set username = #{username}, password = #{password} where id = #{id}
</update>
<!--Account findById(Serializable id);-->
<select id="findById" resultType="cn.itcast.entity.Account">
select id,username,password,age from account where id = #{id}
</select>
<!-- List<Account> findAll(); 虽然返回类型是一个List<Account>,但我们要用集合的泛型类型-->
<select id="findAll" resultType="cn.itcast.entity.Account">
select id,username,password,age from account
</select>
<!--
Account findByUsernameAndAge(String username,Integer age);
问题:
1.两个参数我怎么写parameterType?
2.我怎么获取参数值? 通过arg0/arg1, 或者param1/param2
注:不要在select标签中写注释,会有问题!
-->
<select id="findByUsernameAndAge" resultType="cn.itcast.entity.Account">
select * from account where username = #{arg0} and age = #{arg1}
</select>
</mapper>
注:使用Mapper代理,那么namespace的值,应该是接口全限定名
注:如果有多个参数,可以用arg0/arg1或者param1/param2来取值!
1、在mybatis全局配置⽂文件中注册 AccountDaoMapper.xml
<!--2.注册AccountDaoMapper.xml-->
<mapper resource="cn/itcast/mapper/AccountDaoMapper.xml"></mapper>
2、测试:选中AccountDao接口,右击generator–>Test生成
public class AccountDaoTest {
AccountDao accountDao;
//SqlSession 的实例不是线程安全的,因此是不能被共享的,所以它的最佳的作用域是请求或方法作用域。 绝对不能将 SqlSession 实例的引用放在一个类的静态域,甚至一个类的实例变量也不行
SqlSession sqlSession;
@Before
public void setUp() throws Exception {
InputStream inputStream = AccountDaoTest.class.getClassLoader().getResourceAsStream("mybatis.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
sqlSession = sqlSessionFactory.openSession();
accountDao = sqlSession.getMapper(AccountDao.class);
}
@After
public void tearDown() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void save() {
//添加操作
Account account = new Account(3,"user1","123456",22);
//其实mapper接口使用了全限定名:cn.itcast.dao.AccountDao.save
//本质上和原生接口insert("cn.itcast.mapper.AccountMapper.saveAccount", account);差不多
accountDao.save(account);
}
@Test
public void delete() {
//删除操作
accountDao.delete(2);
}
@Test
public void update() {
//更新操作
Account account = accountDao.findById(1);
account.setPassword("dashuaibi");
accountDao.update(account);
}
@Test
public void findById() {
//查询操作
Account account = accountDao.findById(1);
System.out.println(account); //Account(id=1, username=wzj, password=123456, age=22)
}
@Test
public void findAll() {
//查询所有
List<Account> accounts = accountDao.findAll();
for(Account account : accounts){
System.out.println(account);
}
}
@Test
public void findByUsernameAndAge() {
Account account = accountDao.findByUsernameAndAge("wzj",22);
System.out.println(account);
}
}
六:级联查询
一对多
多对多
6.1:一对多(班级和学生)
1、建立表(班级和学生)
create table classes
(
id int auto_increment primary key,
name varchar(10) null
);
create table student
(
id int auto_increment primary key,
name varchar(20) null,
cid int null,
constraint stu_fk foreign key (cid) references classes (id)
);
2、建立对应的实体类:并且描述实体类之间的关系
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Classes {
private Integer id;
private String name;
/*一个班级有多个学生*/
private List<Student> students;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private Integer id;
private String name;
/*一个学生只属于一个班级*/
private Classes classes;
}
3、根据实体类建立Mapper接口
public interface StudentDao {
/*根据id查询学生对象*/
Student findById(Serializable id);
/*根据id查询学生信息,包括所在班级*/
Student findById2(Serializable id);
/*返回结果为Map*/
Map<String,Object> findById3(Serializable id);
}
public interface ClassesDao {
/*根据id查询班级*/
Classes findById(Serializable id);
/*根据id查询班级信息,及其包括哪些学生!使用的是resultType标签*/
Classes findById2(Serializable id);
/*根据id查询班级信息,及其包括哪些学生!使用的是resultMap标签*/
Classes findById3(Serializable id);
}
4、根据接口配置Mapper.xml文件(建议先把sql写好运行没问题,再粘贴进来!)
- StudentDaoMapping.xml(一个学生只属于一个班级)
<mapper namespace="cn.itcast.dao.StudentDao">
<!--
Student findById(Serializable id);
注:当参数只有一个的时候(不能是一个对象),#{}里面的值填什么都行
-->
<select id="findById" resultType="cn.itcast.entity.Student">
select id, name as sname from student where id = #{id}
</select>
<!--最终还是映射到Student实体类,所以类型还是Student -->
<resultMap id="studentMap" type="cn.itcast.entity.Student">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
<association property="classes" javaType="cn.itcast.entity.Classes">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</association>
</resultMap>
<!--Student findById2(Serializable id);
注:像查询出来的列名和实体类中的属性名不一致,则需要使用resultMap标签
-->
<select id="findById2" resultMap="studentMap">
select
s.id as sid, s.name as sname, c.id as cid, c.name as cname
from
student s
inner join
classes c on s.cid = c.id where s.id = #{id}
</select>
<!-- Map<String,Object> findById3(Serializable id);-->
<select id="findById3" parameterType="java.io.Serializable" resultType="hashmap">
select * from student where id = #{id}
</select>
</mapper>
- ClassesDaoMapping.xml(一个班级有多个学生)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.dao.ClassesDao">
<!-- Classes findById();-->
<select id="findById" resultType="cn.itcast.entity.Classes">
select id,name from classes where id = #{id};
</select>
<!-- Classes findById2(Serializable id);
注:如果写resultType的话,这里查询出来的s.id,s.name还是不会将值映射到Classes类中的students里面的属性(id和name)
但c.id,c.name是可以映射给Classes类中的id和name属性的,解决方式还是要使用resultMap标签
-->
<select id="findById2" resultType="cn.itcast.entity.Classes">
select
c.id, c.name, s.id, s.name
from
student s
inner join
classes c
on
s.cid = c.id
where
c.id = #{id}
</select>
<resultMap id="classesMap" type="cn.itcast.entity.Classes">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<!--Classes类中的List<Student> students-->
<collection property="students" ofType="cn.itcast.entity.Student"> <!--ofType:表示集合中泛型的类型-->
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
</collection>
</resultMap>
<!--3.Classes findById3(Serializable id);
注:与第二点就是使用的是resultMap而不是resultType
使用了resultMap就是列名和属性名,没有保持一模一样,我们也可以手动设置告诉mybatis这种映射关系
-->
<select id="findById3" resultMap="classesMap">
select
c.id as cid, c.name as cname, s.id as sid, s.name as sname
from
student s
inner join
classes c
on
s.cid = c.id
where
c.id = #{id}
</select>
</mapper>
5、将Mapper.xml引入到核心配置文件
<!--3.注册StudentDaoMapper.xml-->
<mapper resource="cn/itcast/mapper/StudentDaoMapper.xml"></mapper>
<!--4.注册ClassesDaoMapper.xml-->
<mapper resource="cn/itcast/mapper/ClassesDaoMapping.xml"></mapper>
6、根据Mapper接口自动生成测试类,进行测试
public class StudentDaoTest {
@Test
public void findById() {
Student student = studentDao.findById(1);
System.out.println(student); //Student(id=1, name=null, classes=null)
}
@Test
public void findById2() {
Student student = studentDao.findById2(1);
System.out.println(student);//Student(id=1, name=wzj, classes=Classes(id=1, name=一班, students=null))
}
@Test
public void findById3() {
Map<String,Object> student = studentDao.findById3(1);
System.out.println(student);//{name=wzj, id=1, cid=1}
}
}
public class ClassesDaoTest {
@Test
public void findById() {
Classes classes = classesDao.findById(1);
System.out.println(classes);
//Classes(id=1, name=一班, students=null)
}
@Test
public void findById2() {
Classes classes = classesDao.findById2(1);
System.out.println(classes);
//Classes(id=1, name=一班, students=null)
}
@Test
public void findById3() {
Classes classes = classesDao.findById3(1);
System.out.println(classes);
//Classes(id=1, name=一班, students=[Student(id=1, name=wzj, classes=null), Student(id=3, name=dyt, classes=null)])
}
}
总结流程:表 -->实体类(描述关系) -->Mapper接口 -->mapper.xml -->引入到核心配置文件 -->生成测试类
1.在mapper.xml中书写完的sql,先放到navicat中运行一下看sql有没有问题,再去测试!
2.当列名和属性名不一致的属性的时候或者当类中的一个关联字段也是个实体类时,使用resultMap属性
3.association标签表示映射的是一个实体类,其中javaType是指定实体类的类型
4.而collection标签表示映射的是一个集合,但ofType属性还是填泛型的类型
5.Mapper接口,本来写成xxxMapper.java会更加符合题意!
6.resultType=“hashmap”,那么列名就是key,值便是结果行中的对应值。
6.2:多对多(顾客和商品)
1、创建表(顾客和商品)
/*顾客和商品之间是多对多的关系*/
/*顾客表*/
create table customer(
cid int primary key auto_increment,
cname varchar(20)
);
insert into customer(cname) values('wzj'),('dyt');
select * from customer;
/*商品表*/
create table goods(
gid int primary key auto_increment,
gname varchar(20)
);
insert into goods(gname) values('电脑'),('投影仪'),('手机');
select * from goods;
/*中间表*/
create table cus_good_middle(
cid int,
gid int,
constraint customer_fk foreign key(cid) references customer(cid),
constraint goods_fk foreign key(gid) references goods(gid)
);
insert into cus_good_middle(cid,gid) values(1,1),(1,3),(2,2);
select * from cus_good_middle;
2、创建实体类:并且描述实体类之间的关系
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Customer {
private Integer cid;
private String cname;
/*一个顾客可以购买多个商品: 一对多*/
private List<Goods> goods;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Goods {
private Integer gid;
private String gname;
/*一个商品可以被多个顾客购买:一对多*/
private List<Customer> customers;
}
3、根据实体类编写一个对应的Mapper接口
public interface CustomerDao {
/*根据id查询顾客信息*/
Customer findById(Serializable id);
}
public interface GoodsDao {
/*根据id查询商品信息,及商品被哪些顾客购买!(虽然业务上可能没有这种要求),但这只是练习*/
Goods findById(Serializable id);
}
4、根据Mapper接口编写Mapper.xml映射文件
- CustomerDaoMapping.xml(一个顾客可以购买多个商品)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.dao.CustomerDao">
<resultMap id="customerMap" type="cn.itcast.entity.Customer">
<id column="cid" property="cid"></id>
<result column="cname" property="cname"></result>
<!--Customer类中:List<Goods> goods-->
<collection property="goods" ofType="cn.itcast.entity.Goods">
<id column="gid" property="gid"></id>
<result column="gname" property="gname"></result>
</collection>
</resultMap>
<!-- Customer findById(Serializable id);-->
<select id="findById" resultMap="customerMap">
select
c.cid, c.cname, g.gid, g.gname
from
customer c
inner join
goods g
inner join
cus_good_middle cgm
on
c.cid = cgm.cid and g.gid = cgm.gid
where
c.cid = #{id}
</select>
</mapper>
- GoodsDaoMapping.xml(一个商品可以被多个顾客购买)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.dao.GoodsDao">
<resultMap id="goodsMap" type="cn.itcast.entity.Goods">
<id column="gid" property="gid"></id>
<result column="gname" property="gname"></result>
<collection property="customers" ofType="cn.itcast.entity.Customer">
<id column="cid" property="cid"></id>
<result column="cname" property="cname"></result>
</collection>
</resultMap>
<!-- Goods findById(Serializable id);-->
<select id="findById" resultMap="goodsMap">
select
g.gid, g.gname, c.cid, c.cname
from
goods g
inner join
customer c
inner join
cus_good_middle cgm
on
cgm.gid = g.gid and cgm.cid = c.cid
where
g.gid = #{id}
</select>
</mapper>
5、引入到核心配置文件
<!--5.注册CustomerDaoMapper.xml-->
<mapper resource="cn/itcast/mapper/CustomerDaoMapping.xml"></mapper>
<!--6.注册GoodsDaoMapper.xml-->
<mapper resource="cn/itcast/mapper/GoodsDaoMapper.xml"></mapper>
6、生成测试类
public class CustomerDaoTest {
@Test
public void findById() {
Customer customer = customerDao.findById(1);
System.out.println(customer);
//Customer(cid=1, cname=wzj, goods=[Goods(gid=1, gname=电脑, customers=null), Goods(gid=3, gname=手机, customers=null)])
}
}
public class GoodsDaoTest {
@Test
public void findById() {
Goods goods = goodsDao.findById(1);
System.out.println(goods);
//Goods(gid=1, gname=电脑, customers=[Customer(cid=1, cname=wzj, goods=null)])
}
}
注:其实多对多,就是两个双向一对多!
七:mybatis的逆向工程
其实帮你根据表,生成实体类,Mapper接口,mapper映射文件(包含简单的crud),简化你的开发,提高效率
八:mybatis的延迟加载
延迟加载的作用就是为了提高程序的运行效率,减少和数据库的交互次数,在某些特定的情况下去访问特定的表,在其他情况下可以不访问某些表。
举个例子:在查询学生和班级的时候,学生和班级是两张不同的表,如果当前需求只需要获取学生的信息,那么只查询学生表即可,如果需要通过学生获取对应的班级信息,则必须查询两张表。
1、例子一:
<!--Student findById2(Serializable id);-->
<select id="findById2" resultMap="studentMap">
select
s.id as sid, s.name as sname, c.id as cid, c.name as cname
from
student s
inner join
classes c on s.cid = c.id where s.id = #{id}
</select>
<!-- Map<String,Object> findById3(Serializable id);-->
<select id="findById3" parameterType="java.io.Serializable" resultType="hashmap">
select * from student where id = #{id}
</select>
@Test
public void findById2() {
Student student = studentDao.findById2(1);
//System.out.println(student.getName()); //只想查询学生姓名
System.out.println(student);//Student(id=1, name=wzj, classes=Classes(id=1, name=一班, students=null))
}
==> Preparing: select
s.id as sid, s.name as sname, c.id as cid, c.name as cname
from
student s
inner join
classes c
on
s.cid = c.id
where
s.id = ?
==> Parameters: 1(Integer)
<== Columns: sid, sname, cid, cname
<== Row: 1, wzj, 1, 一班
<== Total: 1
wzj
如上示例:无论我只是想单独查询学生姓名,还是学生所在班级,都会去触发两个表。
可不可以这样,
①:我单独只想查学生姓名的时候,就只去查学生表,不要在去查班级表,
②:但当我想查学生所在的班级的时候,因为学生表中没有该信息,那么这时程序再帮我去班级表查询!
注:当我想要什么数据的时候,再去什么表中查询,这样就实现了懒加载!,很明显想要实现懒加载,需要 将多表关联查询,拆分成多个单表查询!!
2.、例子:(延迟加载的示例)
- StudentDaoMapping.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.dao.StudentDao">
<resultMap id="studentMap2" type="cn.itcast.entity.Student">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<!--一个学生所属一个班级-->
<association property="classes"
javaType="cn.itcast.entity.Classes"
select="cn.itcast.dao.ClassesDao.findById"
column="cid">
</association>
</resultMap>
<!--Student findById4Lazy(Serializable id);-->
<select id="findById4Lazy" resultMap="studentMap2">
select id,name,cid from student where id = #{id}
</select>
</mapper>
- ClassesDaoMapping.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.itcast.dao.ClassesDao">
<!-- Classes findById();-->
<select id="findById" resultType="cn.itcast.entity.Classes">
select id,name from classes where id = #{id};
</select>
</mapper>
先查询 :select id,name,cid from student where id = #{id},得到cid后,将cid作为条件放到cn.itcast.dao.ClassesDao.findById去查询!也就是,select id,name from classes where id = #{id};
- 在核心配置文件中开启延迟加载
<settings>
<!--打印sql日志!-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--开启延迟加载(懒加载)-->
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
- 测试
@Test
public void findById4Lazy() {
Student student = studentDao.findById4Lazy(1);
System.out.println(student.getName());
}
只查询学生姓名的时候,意味着只去学生表查就行,所以sql只有一条
> Preparing: select id,name,cid from student where id = ?
> Parameters: 1(Integer)
< Columns: id, name, cid
< Row: 1, wzj, 1
<== Total: 1
wzj
- 测试
@Test
public void findById4Lazy() {
Student student = studentDao.findById4Lazy(1);
System.out.println(student.getClasses().getName());
}
查询学生对应的班级名称的时候,此时学生表中没有,所以还得去班级表中查询,所以发送了两条sql
> Preparing: select id,name,cid from student where id = ?
> Parameters: 1(Integer)
< Columns: id, name, cid
< Row: 1, wzj, 1
<== Total: 1
> Preparing: select id,name from classes where id = ?;
> Parameters: 1(Integer)
< Columns: id, name
< Row: 1, 一班
<== Total: 1
一班
总结:
延迟加载
作用:在需要该数据的时候才去表中加载,能不加载就不加载,所以命名懒加载!
思想:需要将多表关联查询,拆分成多个单表查询,记得在配置文件中开启延迟加载!
九:mybatis的缓存
1、什么是Mybatis的缓存?
使用缓存可以减少java应用与数据库的交互次数,从而提升程序的运行效率2、Mybatis缓存分类
- 一级缓存:sqlSession级别,默认开启,并且不能关闭
①:操作数据库时需要创建 SqlSession 对象,在对象中有一个 HashMap ⽤于存储缓存数据,不同的SqlSession 之间缓存数据区域是互不影响的。
②:一级缓存的作用域是 SqlSession 范围的,当在同一个 SqlSession 中执⾏两次相同的 SQL 语句事,第⼀
次执⾏完毕会将结果保存到缓存中,第二次查询时直接从缓存中获取。
③:需要注意的是,如果 SqlSession 执⾏了了 DML 操作(insert、 update、 delete), MyBatis 必须将缓存清空以保证数据的准确性
- ⼆级缓存: Mapper 级别,默认关闭,可以开启
①:使⽤二级缓存时,多个 SqlSession 使⽤同⼀个 Mapper 的 SQL 语句句操作数据库,得到的数据会存在⼆级缓存区,同样是使用 HashMap 进行数据存储,相⽐比较于一级缓存,⼆级缓存的范围更大,多SqlSession 可以共⽤二级缓存,⼆级缓存是跨 SqlSession 的
②:二级缓存是多个 SqlSession 共享的,其作⽤域是 Mapper 的同⼀个 namespace,不同的 SqlSession
两次执行相同的 namespace 下的 SQL 语句,参数也相等,则第⼀次执行成功之后会将数据保存到⼆级
缓存中,第⼆次可直接从⼆级缓存中取出数据
我举个例子:就比较通俗易懂了,我在写hibernate中的缓存时举过这个例子
想上厕所的时候,我们都会先看看柜子中有纸没,如果有就直接用,如果没有就去超市中买,然后存储到柜子中
,第二次上厕所的时候,我们还是下意识会先去柜子里看。柜子(缓存区),超市(数据库)
注:sqlMapper级别的缓存,可以理解成对象,Mapper级别的缓存可以理解成static中的(类级别的)
对象之间的数据也互不影响,多个对象可以共享同一个static{}里面的东西!
9.1:sqlSession级别缓存:一级缓存
public class StudentDaoTest2 {
public static void main(String[] args) {
InputStream inputStream = StudentDaoTest2.class.getClassLoader().getResourceAsStream("mybatis.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student s1 = studentDao.findById(1);
Student s2 = studentDao.findById(1);
// 只有发送了一条sql
}
}
测试一级缓存的范围
public class StudentDaoTest2 {
public static void main(String[] args) {
InputStream inputStream = StudentDaoTest2.class.getClassLoader().getResourceAsStream("mybatis.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student s1 = studentDao.findById(1);
//关闭sqlSession,一级缓存只是SqlSession级别的,只能在同一个sqlSession才能拿到,我这里关闭了sqlSession,重新打开了一个
sqlSession.close();
sqlSession = sqlSessionFactory.openSession();
studentDao = sqlSession.getMapper(StudentDao.class);
Student s2 = studentDao.findById(1);
//发送了两条sql
}
}
9.2:Mapper级别缓存:二级缓存,默认关闭,需要开启
1、mybatis核心配置文件中
<!--开启二级缓存-->
<setting name="cacheEnabled" value="true"/>
2、实体类需要实现Serializable接口
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student implements Serializable {
private Integer id;
private String name;
/*一个学生只属于一个班级*/
private Classes classes;
}
3、mapper.xml中配置二级缓存
<cache></cache>
4、测试二级缓存
public class StudentDaoTest2 {
public static void main(String[] args) {
InputStream inputStream = StudentDaoTest2.class.getClassLoader().getResourceAsStream("mybatis.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student s1 = studentDao.findById(1);
//关闭sqlSession,一级缓存只是SqlSession级别的,只能在同一个sqlSession才能拿到,我这里关闭了sqlSession,重新打开了一个
sqlSession.close();
sqlSession = sqlSessionFactory.openSession();
studentDao = sqlSession.getMapper(StudentDao.class);
Student s2 = studentDao.findById(1);
//此时发送了1条sql,因为我开启了二级缓存是Mapper级别的,他们也在同一个namespace的命名空间下
}
}
9.3:第三方缓存(ehcache)
1、引入maven依赖
<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>
2、在resource下添加ehcache.xml配置文件
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../config/ehcache.xsd">
<diskStore/>
<defaultCache
maxElementsInMemory="1000"
maxElementsOnDisk="10000000"
eternal="false"
overflowToDisk="false"
timeToIdleSeconds="120"
timeToLiveSeconds="120"
diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU">
</defaultCache>
</ehcache>
3、mybatis核心配置文件:开启二级缓存
<!--开启二级缓存-->
<setting name="cacheEnabled" value="true"/>
4、实体类中不需要实现Serializable接口
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student{
private Integer id;
private String name;
/*一个学生只属于一个班级*/
private Classes classes;
}
5、StudentDaoMapper.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>
6、测试
public class StudentDaoTest2 {
public static void main(String[] args) {
InputStream inputStream = StudentDaoTest2.class.getClassLoader().getResourceAsStream("mybatis.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student s1 = studentDao.findById(1);
//关闭sqlSession,一级缓存只是SqlSession级别的,只能在同一个sqlSession才能拿到,我这里关闭了sqlSession,重新打开了一个
sqlSession.close();
sqlSession = sqlSessionFactory.openSession();
studentDao = sqlSession.getMapper(StudentDao.class);
Student s2 = studentDao.findById(1);
//此时发送了1条sql,因为我开启了二级缓存(ehcache)
}
}
十:动态sql
10.1:if标签
<!-- Account findByAccount(Account account);-->
<select id="findByAccount" parameterType="cn.itcast.entity.Account" resultType="cn.itcast.entity.Account">
select * from account
<where>
<if test="id != null">
id = #{id}
</if>
<if test="username != null">
and username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
<if test="age != null">
and age = #{age}
</if>
</where>
</select>
where标签可以根据情况动态的去删除and,
例子:比如上面的sql此时id == null,也就是没有传递id,那么按道理拼接的语句如下:
select * from account and username = #{username} and password = #{password} and age = #{age}
这很明显这个sql有语法错误,所以得使用where标签包含,当不需要and的时候将它删除即可!
10.2:choose 和 when标签
<!-- Account findByAccount(Account account);-->
<select id="findByAccount" parameterType="cn.itcast.entity.Account" resultType="cn.itcast.entity.Account">
select * from account
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="username != null">
username = #{username}
</when>
<when test="password != null">
password = #{password}
</when>
<otherwise>
age = #{age}
</otherwise>
</choose>
</where>
</select>
把它看成swich语句就好,case就是这里的when,default就是otherwise!
10.3:trim标签
<!-- Account findByAccount(Account account);-->
<select id="findByAccount" parameterType="cn.itcast.entity.Account" resultType="cn.itcast.entity.Account">
select * from account
<trim prefix="where" prefixOverrides="and">
<if test="id != null">
id = #{id}
</if>
<if test="username != null">
username = #{username}
</if>
<if test="password != null">
password = #{password}
</if>
<if test="age != null">
age = #{age}
</if>
</trim>
</select>
它可以根据情况动态决定,是在前面添加where,还是在前面删除and
例子:
①:假设我只给了username = ‘wzj’ ,其他都没给值,按道理的sql是:
select * from account username = #{username} 这很明显是不对的,所以trim标签会自动加上where
<!-- Account findByAccount(Account account);-->
<select id="findByAccount" parameterType="cn.itcast.entity.Account" resultType="cn.itcast.entity.Account">
select * from account
<trim prefix="where" prefixOverrides="and">
<if test="id != null">
and id = #{id}
</if>
<if test="username != null">
and username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
<if test="age != null">
and age = #{age}
</if>
</trim>
</select>
这里写的trim标签,它是根据情况决定是在前面添加where,还是在前面删除and
例子:
②:假设我只给了username = ‘wzj’ ,其他都没给值,按道理的sql是:select * from account and username = #{username} 这很明显sql也是不对的,这时trim标签会自动的将
and给去除掉!
10.4:set标签:用于update操作
<!--void updateById(Account account);-->
<select id="updateById" parameterType="cn.itcast.entity.Account">
update account
<set>
<if test="username != null">
username = #{username}
</if>
<if test="password != null">
password = #{password}
</if>
<if test="age != null">
age = #{age}
</if>
</set>
where id = #{id}
</select>
如果不传条件该怎么办? 那不是update account where id = #{id} 会出现语法错误!
10.5:foreach标签
1、实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
private Integer id;
private String username;
private String password;
private Integer age;
private List<Integer> ids;
}
2、Mapper接口中iterator方法
/*示范foreach标签*/
List<Account> iterator(Account account);
3、Mapper映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
List<Account> iterator(Account account);
select * from account where id in(1,3,4);
-->
<select id="iterator" parameterType="cn.itcast.entity.Account" resultType="cn.itcast.entity.Account">
select * from account
<where>
<foreach collection="ids" item="id" open="id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
collection属性是指定一个集合属性,item属性是每次循环从集合中迭代出的元素
4、测试
@Test
public void iterator() {
Account account = new Account();
List<Integer> ids = new ArrayList<Integer>(Arrays.asList(1,3,4));
account.setIds(ids);
accountDao.iterator(account);
/*
==> Preparing: select * from account WHERE id in ( ? , ? , ? )
==> Parameters: 1(Integer), 3(Integer), 4(Integer)
*/
}
注:动态sql:就是动态的根据条件决定是不是要拼接sql
- 总结
mybatis的方式:
方式一:原生接口
方式二:接口,接口实现类(传统实现方式)
方式三:Mapper接口,动态代理,不需要实现类,但是需要遵循一些规范
为了看到输出的sql语句,使用log4j.jar,和log4j.properties
log4j.rootLogger=info, stdout
将info改成debug否则出不来sql语句
log4j.rootLogger=debug, stdout
来自:虽然帅,但是菜的cxy