MyBatis

MyBatis简介

MyBatis是一个半自动的ORM框架
ORM（object Relation Mapping）对象关系映射，将Java中的一个对象，与数据表中的一行记录进行一一对应。
ORM框架提供了实体类与数据表的映射关系，通过映射文件的配置，实现对象的持久化。

MyBatis优点

支持自定义SQL语句、存储过程
对原有的JDBC进行了封装，几乎消除了所有JDBC代码，让开发者只需关注SQL本身
支持XML和注解配置方式自动完成ORM操作，实现结果映射

MyBatis框架部署 1.创建Maven项目 2.添加MyBatis依赖

pom.xml中 mybatis 和 mysql driver

         
         
             mysql
             mysql-connector-java
             5.1.47
         

         
             
             org.mybatis
             mybatis
             3.4.6
         
     

3.创建MyBatis配置文件 mybatis-config

MyBatis框架使用 MyBatis进行数据库操作的步骤

1.在接口中定义方法
2.在mapper中定义方法
3.调用方法进行操作

学生信息的数据库操作 1.创建数据表

create table tb_student(
     sid int primary key auto_increment,
     stu_num varchar(20) not null unique,
     stu_name varchar(20) not null,
     stu_age int not null
     );

创建实体类

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private int stu_id;
    private String stu_num;
    private String stu_name;
    private int stu_age;
}

创建DAO接口，并定义操作方法

package com.lxr.dao;
import com.lxr.bean.Student;
public interface StudentDAO {
    public int insertStudent(Student stduent);
}

创建DAO接口映射文件

在resources目录下新建名为mappers文件夹
在mappers中新建名为StudentMapper.xml文件
在映射文件中对DAO中的方法进行实现：

    
    
        insert into tb_student(stu_num,stu_name,stu_age)
        values(#{stu_num}.#{stu_name},#{stu_age})
    

    
        delete from tb_student where stu_num = #{stu_num}
    



    

将映射文件添加到主配置文件

mybatis-config.xml

单元测试 创建单元测试类

在被测试类后 Alt+insert 选择Test

添加单元测试依赖

测试

public class StudentDAOTest {

    @org.junit.Test
    public void testInsertStudent() {
        //加载mybatis配置文件

        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            //会话工厂
            SqlSessionFactory factory = builder.build(resourceAsStream);
            //会话（连接）
            SqlSession sqlSession = factory.openSession();
            //通过会话取得DAO对象
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); //可以得到任何接口的对象
            //测试StudentDAO中的insert方法
            int i = studentDAO.insertStudent(new Student(0,"10001","name1",20));
            //提交事务
            sqlSession.commit();
            System.out.println(i);

        } catch (IOException e) {
            e.printStackTrace();
        }


    }

    @org.junit.Test
    public void deleteStudent() {`在这里插入代码片`
    }
}

有关MyBatis的CRUD操作 添加操作

添加操作主键回填

 
 
 
     insert into tb_student(stu_num,stu_name,stu_age)
     values(#{stu_num},#{stu_name},#{stu_age})
 

删除操作 1.在StudentDAO中定义删除方法

public int deleteStudent(String stu_num);

2.在StudentMapper中实现删除方法

     
        delete from tb_student where stu_num = #{stu_num}
    

3.测试delete方法

public void testDeleteStudent() {
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
            //
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            //SqlSessionFactory表示MyBatis的会话工厂
            SqlSessionFactory factory = builder.build(resourceAsStream);
            //SqlSession表示MyBatis与数据库之间的会话：通过工厂设计模式
            SqlSession sqlSession = factory.openSession();
            //通过SqlSession对象调用getMapper方法获取DAO接口对象
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); //可以得到任何接口的对象
            //测试StudentDAO中的delete方法
            int i = studentDAO.deleteStudent("10001");
            //提交事务
            sqlSession.commit();
            System.out.println(i);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

MyBatis修改操作 1.在StudentDAO中创建修改方法

public int updateStudent(Student student);

2.在StudentMapper中实现更新方法

        update  tb_student
        set
            stu_name = #{stu_name} ,
            stu_age = #{stu_age}
        where
            stu_num = #{stu_num}
    

3.测试update操作

public void testUpdateStudent(){
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");

            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

            SqlSessionFactory factory = builder.build(resourceAsStream);

            SqlSession sqlSession = factory.openSession();

            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);


            System.out.println(studentDAO);
            int i = studentDAO.updateStudent(new Student(0,"10002","name2",21));
            sqlSession.commit();
            assertEquals(1,i);

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

查询 查询所有 1.在StudentDAO中创建查询所有方法

public List listStudent();

2.在StudentMapper中实现查询所有方法

方法一：使用别名

    
    
        select sid stu_id ,stu_num , stu_name , stu_age from tb_student
    
 
方法二：使用resultMap标签指定映射关系
 


        
        
    


     select sid ,stu_num , stu_name , stu_age from tb_student
 

3.测试查询所有方法

public void testListStudent(){
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");

            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

            SqlSessionFactory factory = builder.build(resourceAsStream);

            SqlSession sqlSession = factory.openSession();

            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);



            List list = studentDAO.listStudent();

            assertNotNull(list);

            for(Student i:list){
                System.out.println(i);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

查询一条信息

public Student oneStudent(String stu_num);

        select sid , stu_num , stu_name , stu_age from tb_student where stu_num = #{stu_num}
    
 
public void testoneStudent(){
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");

            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

            SqlSessionFactory factory = builder.build(resourceAsStream);

            SqlSession sqlSession = factory.openSession();

            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);


            Student student = studentDAO.oneStudent("10002");


            System.out.println(student);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
查询总记录数 
public int getCount();
 
通过resultType指定当前操作的返回类型
 

       select count(1) from tb_student
   

public void testGetCount(){
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");

            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

            SqlSessionFactory factory = builder.build(resourceAsStream);

            SqlSession sqlSession = factory.openSession();

            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);


            int count = studentDAO.getCount();


            System.out.println(count);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

多条件查询

分页查询

1.在StudentDAO中创建分页查询方法

在传递参数有多个的情况下使用注解@Param 声明参数的别名

public List dividePage(@Param("start") int start, @Param("pageSize")int pageSize);

2.在StudentMapper中实现分页查询的方法

        select sid , stu_num , stu_name , stu_age
        from tb_student
        limit #{start},#{pageSize}

 
3.测试分页查询代码 
public void testDividePage(){
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");

            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

            SqlSessionFactory factory = builder.build(resourceAsStream);

            SqlSession sqlSession = factory.openSession();

            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
            System.out.println(studentDAO);

            List list = studentDAO.dividePage(0,2);

            for(Student i:list) {
                System.out.println(i);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
或者直接用arg0和arg1
 或者使用param1和param2
 
public List dividePage(int start,int pageSize);
 

        select sid , stu_num , stu_name , stu_age
        from tb_student
        limit #{arg0},#{arg1}
    

封装MyBatis工具类

public class MyBatisUtils {
    private static SqlSessionFactory factory;
    private static final ThreadLocal local = new ThreadLocal();

    static {
        InputStream resourceAsStream = null;
        try {
            resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            factory = builder.build(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            sqlSession = factory.openSession();
            local.set(sqlSession);
        }
        return sqlSession;
    }

    public static T getMapper(Class c){
        SqlSession sqlSession = getSqlSession();
        return  sqlSession.getMapper(c);
    }
}

事务管理 SqlSession对象作用

1.getMapper(DAO.class)获取Mapper(DAO接口实例)
2.事务管理
3.提交事务 sqlSession.commit()
4.事务回滚 sqlSession.rollback()

1.手动事务管理

public void testInsertStudent() {


        //1.当获取sqlSession对象时，开启事务
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        
        
        try {
            StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class); //可以得到任何接口的对象
            int i = studentDAO.insertStudent(new Student(0, "10004", "name4", 24));
            //2.操作完成并成功之后，需要手动提交
            sqlSession.commit();
        }catch (Exception e){
            //3.操作出现异常，调用rollback方法进行回滚
            sqlSession.rollback();
        }
    }

2.自动提交事务

通过sqlSessionFactory对象调用openSession方法获取sqlSession对象时，可以通过参数设置事务是否自动提交

 SqlSession sqlSession = factory.openSession(true);
 //factory.openSession(boolean isAutoCommit)

MyBatis主配置文件

mybatis-config.xnl是MyBatis框架的主配置文件，主要用于配置MyBatis数据源及属性信息

configuration

1.properties                           加载属性文件
2.settings                             设置MyBatis工作的属性
3.typeAliases                          给实体类取别名,在映射文件中可以直接使用别名来替代实体类的权限定名           
4.typeHanddlers                        类型拦截器
5.objectFactory
6.plugins                              配置插件
7.environments                         配置数据库的连接形式
    environment
       transactionManager
       dataSource
8.databaseIdProvider
9.mappers                              加载映射配置

1.properties

1.在resources目录下创建mysql_properties.properties文件
2.在mybatis-config.xml标签中引用properties文件

3.引入之后在配置environment时可以直接使用${key}获取对应的value

2.settings

3.typeAliases

4.plugins

5.environments

6.mapper

映射文件 1.mapper根标签

2.insert标签

声明添加操作
常用属性
id属性：绑定对应DAO中的方法
parameterType属性 用于指定接口中对应方法的参数类型（可省略）
useGeneratedKeys属性 设置添加操作是否需要回填生成的主键
keyProperty属性 设置回填的主键值复制到参数对象的哪个属性
timeout属性设置此操作的超时时间，如果不设置则一直等待

 
        insert into tb_student(stu_num,stu_name,stu_age)
        values(#{stu_num},#{stu_name},#{stu_age})
    

3.delete标签

声明删除操作

        delete from tb_student where stu_num = #{stu_num}
    

3.update标签

声明修改操作

        update  tb_student
        set
            stu_name = #{stu_name} ,
            stu_age = #{stu_age}
        where
            stu_num = #{stu_num}
    

4.select标签

声明查询操作
id属性 指定绑定方法的方法名
parameterType设置参数类型
resultType属性 指定当前sql返回数据封装的对象类型（实体类）但是可能要给数据库中查询的值取别名
resultMap属性，指定从数据表到实体类的字段和属性的对应关系
useCache属性，指定此操作查询操作是否需要缓存
timeout属性，设置超时时间

    
    
        select sid stu_id ,stu_num , stu_name , stu_age from tb_student
    
    
    
        
        

    
    
        select sid ,stu_num , stu_name , stu_age from tb_student
    

5.resultMap标签

用于定义实体类与数据表的映射关系

6.cache标签

设置当前DAO进行数据库操作时的缓存属性设置

7.sql和include

SQL片段

可以把

        select sid , stu_num , stu_name , stu_age from tb_student where stu_num = #{stu_num}

 
转化为
 

        select  from tb_student where stu_num = #{stu_num}

分页插件

可以使用 PageHelper

1.添加分页插件依赖

     com.github.pagehelper
     pagehelper
     5.1.10

2.配置插件

在mybatis的主配置文件 mybatis-config.xml 中通过 plugins标签进行配置

3.使用分页插件

public void testListStudentByPage(){
        StudentDAO studentDAO = MyBatisUtils.getMapper(StudentDAO.class);

        PageHelper.startPage(1,2);
        //执行查询之前进行分页操作
        List students = studentDAO.listStudent();

        //PageInfo中包含了数据和分页信息
        PageInfo pageInfo = new PageInfo<>(students);

        List list = pageInfo.getList();

        for(Student stu:list){
            System.out.println(stu);
        }
    }

4.带条件分页

先进行条件查询在PageInfo

关联映射 1.实体关系

主要指数据实体，数据与数据之间的关系
用户和角色 房屋和楼栋 订单和商品

实体关系分为以下四种：

1.一对一关联

实例：学生，学生证
数据表关系：
1.主键关联
2.唯一外键关联

2.一对多关联

实例：班级和学生 类别和商品
数据表关系：在多的一段添加外键和一的一端添加关联

3.多对一关联

实例：学生和班级 
数据表关系：在多的一段添加外键和一的一端添加关联

4.多对多关联

 实例：订单和商品
 数据表关系：建立第三张关系表添加两个外键分别与两张表主键相关联

1.一对一关联 1.创建数据表

用户信息表

create table users(
    user_id int primary key auto_increment,
    user_name varchar(20) not null unique ,
    user_pwd varchar(20) not null,
    user_realname varchar(20) not null,
    user_img varchar(100) not null
);

详情表

create table details(
    detail_id int primary key auto_increment,
    user_addr varchar(50) not null,
    user_tel char(11) not null,
    user_desc varchar(200),
    uid int not null unique
    #物理关联
    #uid int not null unique,
    #constraint FK_USER foreign key (uid) references users(user_id)
)

2.创建实体类

User

public class User {
   private int userId;
   private String userName;
   private String userPwd;
   private String userRealname;
   private String userImg;

}

Detal

public class Detail {
        private int detailId;
        private String userAddr;
        private String userTel;
        private String userDesc;
        private int userId;

}

3.创建DAO接口定义操作方法 4.添加操作（事务管理） 5.单元测试

    junit
    junit
    4.12
    test

事务管理，添加用户，回填，添加详情

public void testInsertUser() {

        //用户注册提交了基本信息到Servlet,Servlet接收组测信息封装到User和Detail对象里
        User user = new User(0,"nisi","123123","李四","02.jpj");
        Detail detail = new Detail(0,"深圳市","13030303300","这个人很懒，没有签名",0);
        SqlSession sqlSession = MyBatisUtil.getSession(false);;
        try {
            
            UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
            int i = userDAO.insertUser(user);

            detail.setUserId(user.getUserId());
            DetailDAO detailDAO = sqlSession.getMapper(DetailDAO.class);

            int j = detailDAO.insertDetail(detail);

            System.out.println(i + " " + j);
            
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();
        }
       
    }

6.关联查询

实体
User中加入关联的Detail属性

public class User {
   private int userId;
   private String userName;
   private String userPwd;
   private String userRealname;
   private String userImg;

   private Detail detail;
}

映射
方案一：连接查询

        
        
        
        
        
        
        
        
        
        
    


    
        select user_id ,user_name ,user_pwd , user_realname , user_img,detail_id,user_addr,user_tel,user_desc
        from users u
        INNER JOIN details d
        on u.user_id = d.uid
        where u.user_name = #{username}
    
 
方案二：子查询
 1.DetailDAO
 
public Detail queryDetailByUid(int uid);
 
2.DetailMapper
 

          
          
          
          
    

    
        select detail_id,user_addr,user_tel,user_desc
        from details
        where uid = #{uid}
    

3.UserMapper

        
        
        
        
        
        

        
        
    


    
        select user_id ,user_name ,user_pwd , user_realname , user_img
        from users u
        INNER JOIN details d
        on u.user_id = d.uid
        where u.user_name = #{username}
    
 
2.一对多关联查询 
2.1 创建班级学生表 
create table classes(
    cid int primary key  auto_increment,
    cname varchar(30) not null unique ,
    cdesc varchar(100)
)

create table students(
    sid char(5) primary key ,
    sname varchar(20) not null,
    sage int not null ,
    cid int not null
)
 
2.2 创建实体类 
班级
 
public class Clazz {
    private int classId;
    private String className;
    private String classDesc;

    private List stus;
    
}
 
*学生
 
public class Student {
    private String stuId;
    private String stuName;
    private int stuAge;
    private int stuCid;
}
 
2.3 关联查询 
当查询一个班级的时候 关联查询出学生
 一对多 看一
 2.3.1 连接查询
 

    
    
    
    
    
    
        
        
        
    




    select c.cid ,  cname , cdesc , sid , sname , sage
    from classes c INNER JOIN students s
    on c.cid = s.cid
    where c.cid = #{classId}

2.3.2 子查询
StudentMapper

    
        
        
        
        
    

    select sid,sname,sage
    from students
    where cid = #{cid}

 
ClazzMapper
 



    
    
    
    




    select cid ,  cname , cdesc
    from classes
    where cid = #{classId}

3.多对一关联 4.多对多关联 4.1 创建数据表

create table courses(
    courseId int primary key auto_increment,
    courseName varchar(50) not null
);

create table grades(
    sid char(5) not null ,
    courseId int not null ,
    score int not null
)

4.2 查询课程时，同时查询选择此课程的学生

连接查询

    
        
        

        

            
            
            

        

    

    select c.courseId,c.courseName,s.sid,s.sname,s.sage
    from courses c INNER JOIN grades g INNER JOIN students s
         on c.courseId = g.courseId and g.sid = s.sid
    where c.courseId = #{corseId};