Mybatis增删改查（注解、通用Mapper、xml）入门小白快收藏！

Mybatis增删改查

• 一、注解版
• • 1.1 模糊查询
  • 1.2 插入数据
  • 1.3 更新数据
  • 1.4 删除数据
  • 1.5 关联查询：一对多
  • • 1.5.1 用户和订单数据模型
    • 1.5.2 一对多
    • • 语法
      • 需求&分析
      • 订单功能：通过uid查询所有的订单
      • 用户功能：关联查询
    • 1.5.3 多对一
    • • 语法
      • 需求&分析
      • 用户功能：通过id查询用户详情（已有）
      • 订单功能：通过id查询订单详情
  • 1.6 关联查询：多对多
  • • 1.6.1 学生和老师数据模型
    • 1.6.2 多对多：老师-->学生
  • 1.7 分页查询
  • • 1.7.1 搭建环境
    • • 1.7.1.1 导入jar包
      • 1.7.1.2 1.2 添加插件
    • 1.7.2 语法
    • 1.7.3 使用
• 2、通用Mapper
• • 2.1 概述
  • 2.2 搭建环境
  • • 2.2.1 导入jar
    • 2.2.2 修改工具类
  • 2.3 编写Mapper
  • 2.4 通用API
  • • 2.4.1 通过主键查询
    • 2.4.2 查询所有
    • 2.4.3 添加
    • 2.4.4 修改
    • 2.4.5 删除
    • 2.4.6 多条件查询
• 3、基于XML
• • 3.1 搭建环境
  • • 3.1.1 创建项目
    • 3.1.2 添加jar包
    • 3.1.3 拷贝配置类
    • 3.1.4 拷贝工具类
    • 3.1.5 拷贝JavaBean
  • 3.2 入门案例：查询所有
  • • 3.2.1 编写流程
    • 3.2.2 编写Mapper接口
    • 3.2.3 编写Mapper xml配置文件
    • 3.2.4 修改核心配置文件
    • 3.2.5 测试类
  • 3.3 配置文件详解
  • • 3.3.1 Mapper基本结构
    • 3.3.2 Mapper 参数类型：parameterType
    • 3### .3.3 Mapper 结果类型：resultType
    • 3.3.4 Mapper 映射关系：ResultMap
    • 3.3.5 默认别名
    • 3.3.6 自定义别名
    • 3.3.7 加载Mapper文件
  • 3.4 增删改查
  • • 3.4.1 核心配置文件
    • 3.4.2 mapper接口
    • 3.4.3 Mapper 映射文件
    • 3.4.4 测试
  • 3.5 动态SQL
  • • 3.5.1 什么是动态SQL
    • 3.5.2

一、注解版 1.1 模糊查询

功能接口中的方法

• 如果参数简单类型，sql语句需要使用value [不推荐]

@Select(“select * from user where name like #{value}”)
public List selectByName(String name);

• 如果使用@Param，可以进行相应的命名 【推荐】

@Select(“select * from user where name like #{name}”)
public List selectByName(@Param(“name”) String name);

测试类

 package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class Test02_Like {
    public static void main(String[] args) throws IOException {

        //1 加载配置文件
        // 1.1 获得资源流
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 获得工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 获得会话(连接)
        SqlSession session = factory.openSession();

        //3获得功能接口
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 调用功能
        List userList = userMapper.selectByName("%王%");

        //5 打印查询结果
        for (User user : userList) {
            System.out.println(user);
        }
    }

}

1.2 插入数据

• 功能接口中的方法

@Insert(“insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})”)
public Integer insert(User user);

• 测试类（注意：需要提交事务）

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;


public class Test03_Insert {
    public static void main(String[] args) throws IOException {

        //1 加载配置文件
        // 1.1 获得资源流
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 获得工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 获得会话(连接)
        SqlSession session = factory.openSession();

        //3获得功能接口
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 调用功能
        User user = new User();
        user.setUid("1");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("杰克");
        user.setEmail("sky@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");
        Integer result = userMapper.insert(user);
        System.out.println(result);

        //5 提交资源
        session.commit();

        //6 释放资源
        session.close();

    }
}

1.3 更新数据

• 功能接口中的方法

@Insert(“update user set username=#{username}, password=#{password}, name=#{name}, email=#{email},birthday=#{birthday},sex=#{sex}, state=#{state} where uid=#{uid}”)
public Integer update(User user);

• 测试类

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;


public class Test04_Update {
    public static void main(String[] args) throws IOException {

        //1 加载配置文件
        // 1.1 获得资源流
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 获得工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 获得会话(连接)
        SqlSession session = factory.openSession();

        //3获得功能接口
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 调用功能
        User user = new User();
        user.setUid("1");
        user.setUsername("jack1");
        user.setPassword("12341");
        user.setName("杰克");
        user.setEmail("sky@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");
        Integer result = userMapper.update(user);
        System.out.println(result);

        //5 提交资源
        session.commit();

        //6 释放资源
        session.close();


    }

}

1.4 删除数据

• 功能接口中的方法

@Delete(“delete from user where uid = #{uid}”)
public Integer deleteByPrimaryKey(@Param(“uid”) Integer uid);

• 测试类

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;


public class Test05_Delete {
    public static void main(String[] args) throws IOException {

        //1 加载配置文件
        // 1.1 获得资源流
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 1.2 获得工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //2 获得会话(连接)
        SqlSession session = factory.openSession();

        //3获得功能接口
        UserMapper userMapper = session.getMapper(UserMapper.class);

        //4 调用功能

        Integer result = userMapper.deleteByPrimaryKey(1);
        System.out.println(result);

        //5 提交资源
        session.commit();

        //6 释放资源
        session.close();


    }

}

1.5 关联查询：一对多 1.5.1 用户和订单数据模型

表关系

CREATE TABLE orders (
oid VARCHAr(32) PRIMARY KEY NOT NULL,
ordertime DATETIME DEFAULT NULL, #下单时间
total_price DOUBLE DEFAULT NULL, #总价
state INT(11) DEFAULT NULL, #订单状态：1=未付款;2=已付款,未发货;3=已发货,没收货;4=收货,订单结束
address VARCHAr(30) DEFAULT NULL, #收获地址
name VARCHAr(20) DEFAULT NULL, #收获人
telephone VARCHAr(20) DEFAULT NULL, #收货人电话
uid VARCHAr(32) DEFAULT NULL,
ConSTRAINT order_fk_0001 FOREIGN KEY (uid) REFERENCES user (uid)
) ;
INSERT INTO orders VALUES (‘x001’,‘2010-10-10’,10,1,‘江苏’,‘张三’,‘12345’,‘u001’);
INSERT INTO orders VALUES (‘x002’,‘2010-11-11’,20,2,‘河北’,‘李四’,‘67890’,‘u001’);
INSERT INTO orders VALUES (‘x003’,‘2011-10-10’,30,3,‘山西’,‘王五’,‘66666’,‘u002’)

JavaBean

• 默认情况下，两个JavaBean没有关系

package com.czxy.ssm.domain;

import java.util.Date;


public class Order {

	private String oid; 		// 订单编号
	private Date ordertime; 	// 下单时间
	private Double total; 		// 订单总金额
	private Integer state; 		// 订单状态 0 未支付 1 已支付 2已发货 3已收货
	private String address;		// 收货人地址
	private String name;		// 收货人姓名
	private String telephone;	// 收货人电话
	private String uid;

	@Override
	public String toString() {
		return "Order{" +
				"oid='" + oid + ''' +
				", ordertime=" + ordertime +
				", total=" + total +
				", state=" + state +
				", address='" + address + ''' +
				", name='" + name + ''' +
				", telephone='" + telephone + ''' +
				", uid='" + uid + ''' +
				'}';
	}

	public String getOid() {
		return oid;
	}

	public void setOid(String oid) {
		this.oid = oid;
	}

	public Date getOrdertime() {
		return ordertime;
	}

	public void setOrdertime(Date ordertime) {
		this.ordertime = ordertime;
	}

	public Double getTotal() {
		return total;
	}

	public void setTotal(Double total) {
		this.total = total;
	}

	public Integer getState() {
		return state;
	}

	public void setState(Integer state) {
		this.state = state;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getTelephone() {
		return telephone;
	}

	public void setTelephone(String telephone) {
		this.telephone = telephone;
	}

	public String getUid() {
		return uid;
	}

	public void setUid(String uid) {
		this.uid = uid;
	}


	public Order(String oid, Date ordertime, Double total, Integer state, String address, String name, String telephone, String uid) {
		this.oid = oid;
		this.ordertime = ordertime;
		this.total = total;
		this.state = state;
		this.address = address;
		this.name = name;
		this.telephone = telephone;
		this.uid = uid;
	}

	public Order() {

	}
}

JavaBean关系

• 以对象的方法，描述两个JavaBean之间的关系
  
• JavaBean：User

public class User {
private String uid;
private String username;
private String password;
private String name;
private String email;
private Date birthday;
private String sex;
private Integer state;
private String code;
// 一对多：一个用户 拥有【多个用户】
private List orderList = new ArrayList<>();
// …
}

JavaBean：Order

public class Order {
private String oid; // 订单编号
private Date ordertime; // 下单时间
private Double total; // 订单总金额
private Integer state; // 订单状态 0 未支付 1 已支付 2已发货 3已收货
private String address; // 收货人地址
private String name; // 收货人姓名
private String telephone; // 收货人电话
private String uid;
// 多对一， 多个订单 属于 【一个用户】
private User user;
// …
}

1.5.2 一对多 语法

在Mybatis注解开发中，需要通过@Result进行关联关系的描述。

• 一对多：需要使用many属性和@Many注解。

@Result(
property = “1表JavaBean属性名”,
column = “1表字段名”,
many = @Many(select = “多表Mapper的方法签名”)
)

需求&分析

• 需求：查询用户的同时，查询每个用户对应的订单
• 分析：
  • 修改OrderMapper，完成通过uid查询所有的订单
  • 修改UserMapper，完成查询用户信息时，查询对应的订单

订单功能：通过uid查询所有的订单

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


public interface OrderMapper {

    
    @Select("select * from orders where uid = #{uid}")
    public Order findOrdersByUserId(@Param("uid") String uid) ;

}

用户功能：关联查询

public interface UserMapper {
    
    @Select("select * from user")
    @Results(id = "userResult", value = {
            @Result(property = "uid", column = "uid", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property="orderList" , many=@Many(select="com.czxy.ssm.mapper.OrderMapper.findOrdersByUserId"), column="uid")
    })
    public List selectAll();

}

1.5.3 多对一 语法

在Mybatis注解开发中，需要通过@Result进行关联关系的描述。

• 多对一：需要使用one属性和@One注解

@Result(
property = “多表JavaBean属性名”,
column = “多表字段名”,
one = @One(select =“1表Mapper的方法签名”)
)

需求&分析

• 需求：查询订单时，查询关联的用户信息

• 分析：

  1）通过user_id查询用户详情

  2）通过id查询订单详情

用户功能：通过id查询用户详情（已有）

@Select(“select * from user where uid = #{uid}”)
@ResultMap(“userResult”)
public User selectById(@Param(“uid”) String uid);

订单功能：通过id查询订单详情

@Select(“select * from orders where oid = #{oid}”)
@Results({
@Result(property=“oid” , column=“oid”),
@Result(property=“ordertime” , column=“ordertime”),
@Result(property=“total” , column=“total”),
@Result(property=“state” , column=“state”),
@Result(property=“address” , column=“address”),
@Result(property=“name” , column=“name”),
@Result(property=“telephone” , column=“telephone”),
@Result(property=“uid” , column=“uid”),
@Result(property=“user” , one=@One(select=“com.czxy.ssm.mapper.UserMapper.selectById”) , column=“uid”),
})
public Order selectById(@Param(“oid”) String id);

1.6 关联查询：多对多 1.6.1 学生和老师数据模型

表间关系

#老师表
CREATE TABLE teacher(
tid INT PRIMARY KEY,
NAME VARCHAr(50)
);
#学生表
CREATE TABLE student(
sid INT PRIMARY KEY,
NAME VARCHAr(50)
);
#中间表
CREATE TABLE teacher_student(
teacher_id INT ,
student_id INT,
ConSTRAINT ts_t_fk FOREIGN KEY (teacher_id) REFERENCES teacher(tid),
ConSTRAINT ts_s_fk FOREIGN KEY (student_id) REFERENCES student(sid)
);
INSERT INTO teacher VALUES (1,‘肖老师’);
INSERT INTO teacher VALUES (2,‘马老师’);
INSERT INTO student VALUES (1,‘张三’);
INSERT INTO student VALUES (2,‘李四’);
INSERT INTO student VALUES (3,‘王五’);
INSERT INTO teacher_student VALUES (1,1);
INSERT INTO teacher_student VALUES (1,2);
INSERT INTO teacher_student VALUES (1,3);
INSERT INTO teacher_student VALUES (2,1);
INSERT INTO teacher_student VALUES (2,2);

JavaBean及其关系1

JavaBean：Student

package com.czxy.ssm.domain;
import java.util.ArrayList;
import java.util.List;
public class Student {
private Integer sid;
private String name;
private List teacherList = new ArrayList<>();
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List getTeacherList() {
return teacherList;
}
public void setTeacherList(List teacherList) {
this.teacherList = teacherList;
}
@Override
public String toString() {
return “Student{” +
“sid=” + sid +
“, name=’” + name + ‘’’ +
“, teacherList=” + teacherList +
‘}’;
}
}

JavaBean：Teacher

package com.czxy.ssm.domain;
import java.util.ArrayList;
import java.util.List;
public class Teacher {
private Integer tid;
private String name;
private List studentList = new ArrayList<>();
public Integer getTid() {
return tid;
}
public void setTid(Integer tid) {
this.tid = tid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List getStudentList() {
return studentList;
}
public void setStudentList(List studentList) {
this.studentList = studentList;
}
@Override
public String toString() {
return “Teacher{” +
“tid=” + tid +
“, name=’” + name + ‘’’ +
“, studentList=” + studentList +
‘}’;
}
}

1.6.2 多对多：老师–>学生

需要根据老师tid查询中间表中，对应的所有学生id

student 映射

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


public interface StudentMapper {
    
    @Select("select * from student s where s.sid in (select student_id from teacher_student where teacher_id = #{tid} )")
    public Student findStudentByTeacherId(@Param("tid") Integer tid) throws Exception;
}

teacher 映射

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Teacher;
import org.apache.ibatis.annotations.*;


public interface TeacherMapper {

    @Select("select * from teacher t where  t.tid = #{tid}")
    @Results({
            @Result(property="tid" , column="tid"),
            @Result(property="name" , column="name"),
            @Result(property="studentList" , many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") , column="tid"),
    })
    public Teacher selectById(@Param("tid") Integer tid);

}

测试

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.utils.MyBatisUtils;


public class Test09_SelectTeacher {
    public static void main(String[] args) {
        TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);

        Teacher teacher = teacherMapper.selectById(1);
        // 打印
        System.out.println(teacher);

        MyBatisUtils.commitAndclose();
    }
}

1.7 分页查询

MyBatis没有提供分页支持，需要自己编写limit语句。

开发中我们采用PageHelper插件。

1.7.1 搭建环境 1.7.1.1 导入jar包

1.7.1.2 1.2 添加插件

1.7.2 语法

1. 设置分页数据
   PageHelper.startPage(int pageNum, int pageSize)
   参数1：pageNum 第几页
   参数2：pageSize 页面显示个数
   2) 封装分页结果 PageInfo
   new PageInfo(查询结果) //创建分页对象
   pageInfo.getTotal(), //自动查询总条数
   pageInfo.getPages(), //总分页数

1.7.3 使用

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

import java.util.List;


public class Test11_Page {
    public static void main(String[] args) {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        // 设置分页 **
        PageHelper.startPage(1,2);

        // 查询
        List users = userMapper.selectAll();

        // 获得封装对象 ** 
        PageInfo pageInfo = new PageInfo<>(users);

        // 打印分页信息
        long total = pageInfo.getTotal();
        List list = pageInfo.getList();
        System.out.println("总条数：" + total);
        System.out.println("分页数据：");
        list.forEach(user -> {
            System.out.println(user);
        });


        MyBatisUtils.commitAndclose();
    }
}

2、通用Mapper 2.1 概述

• 通用Mapper对MyBatis进行简化的第三方工具包。

• 通用Mapper提供了一个名为Mapper的接口，用于自动完成单表的增删改查操作。

public interface UserMapper extends Mapper {
}

• 如果通用Mapper中的方法不足以满足你的需求，直接添加自定义方法即可。

2.2 搭建环境 2.2.1 导入jar

2.2.2 修改工具类

• 添加内容从官方文档中拷贝

package com.czxy.ssm.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
import tk.mybatis.mapper.entity.Config;
import tk.mybatis.mapper.mapperhelper.MapperHelper;

import java.io.InputStream;


public class MyBatisUtils {

    // 会话工厂
    private static SqlSessionFactory factory;
    static{
        try {
            // 1.1 加载核心配置文件
            InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
            // 1.2 获得工厂
            factory = new SqlSessionFactoryBuilder().build(is);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static ThreadLocal local = new ThreadLocal<>();

    
    private static SqlSession openSession(){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            sqlSession = factory.openSession();

            //创建一个MapperHelper
            MapperHelper mapperHelper = new MapperHelper();
            //特殊配置
            Config config = new Config();
            // 设置UUID生成策略
            // 配置UUID生成策略需要使用OGNL表达式
            // 默认值32位长度:@java.util.UUID@randomUUID().toString().replace("-", "")
            //config.setUUID("");
            // 主键自增回写方法,默认值MYSQL,详细说明请看文档
//	        config.setIDENTITY("HSQLDB");
            // 支持方法上的注解
            // 3.3.1版本增加
            config.setEnableMethodAnnotation(true);
            config.setNotEmpty(true);
            // 序列的获取规则,使用{num}格式化参数，默认值为{0}.nextval，针对Oracle
            // 可选参数一共3个，对应0,1,2,分别为SequenceName，ColumnName, PropertyName
            //config.setSeqFormat("NEXT VALUE FOR {0}");
            // 设置全局的catalog,默认为空，如果设置了值，操作表时的sql会是catalog.tablename
            //config.setCatalog("");
            // 设置全局的schema,默认为空，如果设置了值，操作表时的sql会是schema.tablename
            // 如果同时设置了catalog,优先使用catalog.tablename
            //config.setSchema("");
            // 主键自增回写方法执行顺序,默认AFTER,可选值为(BEFORE|AFTER)
            //config.setOrder("AFTER");
            //设置配置
            mapperHelper.setConfig(config);
            // 注册通用tk.mybatis.mapper.common.Mapper接口 - 可以自动注册继承的接口
            mapperHelper.registerMapper(Mapper.class);
            mapperHelper.registerMapper(MySqlMapper.class);
//	        mapperHelper.registerMapper(SqlServerMapper.class);
//	        mapperHelper.registerMapper(IdsMapper.class);
            //配置完成后，执行下面的操作
            mapperHelper.processConfiguration(sqlSession.getConfiguration());


            local.set(sqlSession);
        }
        return sqlSession;
    }

    
    public static  T getMapper(Class clazz){
        return openSession().getMapper(clazz);
    }

    
    public static void close() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.close();
        }
    }

    
    public static void commitAndclose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.commit();
            close();
        }
    }

    
    public static void rollbackAndclose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.rollback();
            close();
        }
    }

}

2.3 编写Mapper

编写接口，继承tk.mybatis.mapper.common.Mapper接口即可

• 注意：Mapper接口以tk开头

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;
import tk.mybatis.mapper.common.Mapper;


public interface UserMapper2 extends Mapper {
}

2.4 通用API

• 查询方法
  
• 插入方法
  
• 更新方法
• 删除方法
  

2.4.1 通过主键查询

1）确定主键，否则所有字段都是主键

2）测试

package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.mapper.UserMapper2;
import com.czxy.ssm.utils.MyBatisUtils;
import org.junit.Test;

import java.io.IOException;
import java.util.List;


public class Test13_Mapper {
    @Test
    public void testSelectByPrimaryKey() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
        User user = userMapper2.selectByPrimaryKey("1");
        System.out.println(user);

        MyBatisUtils.commitAndclose();

    }
}

2.4.2 查询所有

@Test
    public void testSelectAll() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
        List list = userMapper2.selectAll();
        // 打印
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

2.4.3 添加

@Test
    public void testInsert() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("杰克");
        user.setEmail("sky@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");

        int result = userMapper2.insert(user);

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

2.4.4 修改

@Test
    public void testUpdate() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("杰克");
        user.setEmail("sky@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");

        int result = userMapper2.updateByPrimaryKey(user);

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

2.4.5 删除

 @Test
    public void testDelete() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        int result = userMapper2.deleteByPrimaryKey("2");

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

2.4.6 多条件查询

语法：

// 获得多条件对象
Example example = new Example(对象.class);
Example.Criteria criteria = example.createCriteria();
// 常见条件方法
andLike() //模糊查询
andEqualTo() //等值查询
andLessThanOrEqualTo() //<=查询
andGreaterThanOrEqualTo() //>=查询
andBetween() //区间查询

@Test
    public void testCondition() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andLike("name", "%王%");
        criteria.andEqualTo("sex", "男");

        List list = userMapper2.selectByExample(example);
        for (User user : list) {
            System.out.println(user);
        }

        MyBatisUtils.commitAndclose();
    }

3、基于XML 3.1 搭建环境 3.1.1 创建项目

1）选择Web 应用

2）创建项目mybatis-demo02

3.1.2 添加jar包

3.1.3 拷贝配置类

3.1.4 拷贝工具类 3.1.5 拷贝JavaBean

3.2 入门案例：查询所有 3.2.1 编写流程

• 步骤1：编写Dao接口，用于确定方法名称

• 步骤2：编写Mapper.xml文件，用于编写SQL语句

• 步骤3：编写SqlMapConfig.xml 核心配置文件，并添加mapper xml文件

• 步骤4：测试

3.2.2 编写Mapper接口

package com.czxy.ssm.mapper;
import com.czxy.ssm.domain.User;
public interface UserMapper {

public User selectById(String uid);
}

3.2.3 编写Mapper xml配置文件

    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
     
     select * from user where uid = #{id}
     
     
 

3.2.4 修改核心配置文件 
核心配置文件  用于确定mapper配置文件的位置
 
 
 
 
	
	

 
3.2.5 测试类 
 
package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import org.junit.Test;


public class TestUserMapper {


    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("1");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }

}


 
3.3 配置文件详解 
3.3.1 Mapper基本结构 

        
	
	SQL语句
	
	SQL语句
    
	SQL语句
    
	SQL语句

3.3.2 Mapper 参数类型：parameterType

在映射文件mapper中，我们使用parameterType设置请求参数的类型

        
    SQL语句

简单类型：

	select * from user where uid = #{id}

 
POJO类型：
 

	insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})

 
3### .3.3 Mapper 结果类型：resultType 
在映射文件mapper中，我们使用resultType设置查询结果
 


        
    SQL语句

简单类型

	select count(*) from user

 
POJO类型
 

	select * from user where uid = #{id}

3.3.4 Mapper 映射关系：ResultMap

• resultType可以指定pojo将查询结果封装到该pojo中，但需要pojo的属性名和sql查询的列名保持一致。

• 如果sql查询字段名和pojo的属性名不一致，可以通过resultMap将字段名和属性名作一个对应关系。

• 语法：

实例：

	
		
		
	

    
		select * from user where uid = #{id}
	
 
3.3.5 默认别名 
 
3.3.6 自定义别名 
声明别名，在 SqlMapConfig.xml 配置文件中声明
 
 
方式1：一次定义一个类
 

	

 
 
方式2：一次定义一个包下的所有类
 

	

 
 
 
 
使用别名，在mapper中直接使用
 

	select * from user where uid = #{id}

3.3.7 加载Mapper文件

• 在SqlMapConfig.xml文件中，通过和加载映射文件

• 方式1：加载指定文件

• 方式2：加载指定包下的所有映射文件

  • 要求：要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中

3.4 增删改查 3.4.1 核心配置文件

3.4.2 mapper接口

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.vo.UserVo;
import org.apache.ibatis.annotations.;
import java.util.List;

public interface UserMapper {

  public User selectById(String uid);

  
  public List selectAll();


  
  public List selectByName(@Param("name") String name);

  
  public Integer insert(User user);

  
  public Integer updateByPrimaryKey(User user);

  
  public Integer deleteByPrimaryKey(@Param("uid") String uid);

  
  public List condition(UserVo userVo);
}

3.4.3 Mapper 映射文件

	
		
		
	

    
		select * from user where uid = #{id}
	

	
		select * from user
	

	
		select * from user where name like #{name}
	

	
		insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})
	

	
		update user set username=#{username}, password=#{password}, name=#{name}, email=#{email},birthday=#{birthday},sex=#{sex}, state=#{state} where uid=#{uid}
	

	
		delete from user where uid = #{uid}
	


 
3.4.4 测试 
package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import org.junit.Test;

import java.util.Date;
import java.util.List;


public class TestUserMapper {


    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("1");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testSelectAll() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
        List list = UserMapper.selectAll();
        // 打印
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testSelectByName() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
        List list = UserMapper.selectByName("%张%");
        // 打印
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testInsert() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("杰克");
        user.setEmail("sky@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");

        int result = UserMapper.insert(user);

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testUpdate() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("杰克");
        user.setEmail("sky@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");

        int result = UserMapper.updateByPrimaryKey(user);

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testDelete() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);

        int result = UserMapper.deleteByPrimaryKey("2");

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

}

 
3.5 动态SQL 
3.5.1 什么是动态SQL 
动态SQL就是Mybatis允许在映射文件中通过标签控制SQL语句最后的拼凑结果。
 
3.5.2 标签 
语法：在mapper映射文件中，标签就相当于Java中if语句，如果条件成立，标签体内的SQL语句有效。
 
 
 

	//有效的SQL语句片段

封装对象：UserVo

public class UserVo {
private String name; // 模糊查询
private String beginTime; // 开始时间
private String endTime; // 结束时间
// …
}

多条件查询，拼凑恒等条件 where 1=1

		select * from user where 1=1
		
			and name like '%${name}%'
		
	
 
测试
 
@Test
    public void testCondtion() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
        UserVo userVo = new UserVo();
        userVo.setName("张");
        List list = UserMapper.condition(userVo);
        // 打印
        list.forEach(System.out::println);
        MyBatisUtils.commitAndclose();
    }
     
 
3.5.3 标签 
 
多条件查询时，我们使用了一个小技巧“where 1=1”，mybatis提供了一个进行取代。
 
 
修改映射文件
 
 
 
 

		select * from user
		
			
				and name like '%${name}%'
			
		
	

3.5.4 标签

• 语法：

• 封装对象

public class UserVo {
    private List ids = new ArrayList<>();
    private String name;            // 模糊查询
    private String beginTime;       // 开始时间
    private String endTime;         // 结束时间
    // ... 
}

多条件查询

		select * from user
		
			
			
				'${id}'
			
		
	
 
测试
 
   @Test
    public void testCondtion() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);

        UserVo userVo = new UserVo();
        userVo.setIds(Arrays.asList("u001","u002"));

        List list = UserMapper.condition(userVo);
        // 打印
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

 
3.5.5 标签：多条件 
语法
 

		
			
			
			
		
	

3.5.6 标签

• MyBatis为我们提供了代码片段，使用定义公共SQL语句，使用将需要的SQL片段拼凑到指定的位置。

uid, username, password, name, email, birthday, sex, state


	select  from user

 
3.6 关系映射：一对多 
3### .6.1 mapper接口 
 
UserMapper，已有
 
public interface UserMapper {
    
    public User selectById(String uid);
}
 
 
OrderMapper
 
package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;


public interface OrderMapper {

    
    public Order findOrdersByUserId(@Param("uid") Integer uid) ;
}

 
 
3### .6.2 Mapper 映射文件 
 
OrdersMapper.xml
 





    
		select * from orders where uid = #{uid}
	

UserMapper.xml

	
		
		
		
	



    
		select * from user where uid = #{id}
	
    

  
3.6.3 核心配置文件 
 
3.6.4 测试 
public class TestUserMapper {

    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("u001");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }
    
}

  



 
3.7 关系映射：多对一 
3.7.1 mapper接口 
package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface OrderMapper {

    
    public Order selectById(@Param("oid") String oid);
}

 
3.7.2 映射文件OrdersMapper.xml 




	
		
		
		
		
		
		
		
		
		
	
	
		select * from orders where oid = #{oid}
	

3.7.3 测试类

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.czxy.ssm.vo.UserVo;
import org.junit.Test;

import java.util.Arrays;
import java.util.Date;
import java.util.List;


public class TestOrderMapper {


    @Test
    public void testSelectById() {
        OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class);

        Order order = orderMapper.selectById("x001");
        System.out.println(order);

        MyBatisUtils.commitAndclose();
    }

}