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

一、注解版

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;

/**
 * @author sky
 */
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<User> userList = userMapper.selectByName("%王%");

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

}

1.2 插入数据

• 功能接口中的方法

/**
* 插入数据
* @param user
*/
@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;

/**
 * @author sky
 */
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 更新数据

• 功能接口中的方法

/**
* 插入数据
* @param user
*/
@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;

/**
 * @author sky
 */
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 删除数据

• 功能接口中的方法

/**
* 通过id删除
* @param uid
*/
@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;

/**
 * @author sky
 */
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;

/**
 *
 Create Table

 CREATE TABLE `orders` (
 `oid` varchar(32) NOT NULL,
 `ordertime` datetime DEFAULT NULL,
 `total` double DEFAULT NULL,
 `state` int(11) DEFAULT NULL,
 `address` varchar(30) DEFAULT NULL,
 `name` varchar(20) DEFAULT NULL,
 `telephone` varchar(20) DEFAULT NULL,
 `uid` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`oid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 */
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;

/**
 * @author sky
 */
public interface OrderMapper {

    /**
     * 通过id查询详情
     * @param uid
     * @return
     */
    @Select("select * from orders where uid = #{uid}")
    public Order findOrdersByUserId(@Param("uid") String uid) ;

}

用户功能：关联查询

public interface UserMapper {
    /**
     * 查询所有
     * @return
     */
    @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<User> selectAll();

}

1.5.3 多对一

语法

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

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

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

需求&分析

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

• 分析：

  1）通过user_id查询用户详情

  2）通过id查询订单详情

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

/**
* 通过id查询详情
* @param uid
* @return
*/
@Select(“select * from user where uid = #{uid}”)
@ResultMap(“userResult”)
public User selectById(@Param(“uid”) String uid);

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

/**
*
* @param id
* @return
*/
@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;

/**
 * @author sky
 */
public interface StudentMapper {
    /**
     * 通过tid查询对应的学生
     * @param tid
     * @return
     * @throws Exception
     */
    @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.*;

/**
 * @author sky
 */
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;

/**
 * @author sky
 */
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 添加插件

<plugins>
	<plugin interceptor="com.github.pagehelper.PageHelper">
		<!-- 方言 -->
		<property name="dialect" value="mysql"/>
		<!-- 设置为true时，使用RowBounds分页会进行count查询 -->
		<property name="rowBoundsWithCount" value="true"/>
	</plugin>
</plugins>

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;

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

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

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

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

        // 打印分页信息
        long total = pageInfo.getTotal();
        List<User> 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<T>的接口，用于自动完成单表的增删改查操作。

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;

/**
 * @author sky
 */
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<SqlSession> local = new ThreadLocal<>();

    /**
     * 获得新会话
     * @return
     */
    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;
    }

    /**
     * 获得mapper
     * @param clazz
     * @return
     */
    public static <T> T getMapper(Class<T> 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;

/**
 * @author sky
 */
public interface UserMapper2 extends Mapper<User> {
}

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;

/**
 * @author sky
 */
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<User> 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<User> 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 {
/**
* 通过id查询详情
* @param uid
* @return
*/
public User selectById(String uid);
}

3.2.3 编写Mapper xml配置文件

<?xml version="1.0" encoding="UTF-8"?>

    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.czxy.ssm.mapper.UserMapper">
     <select id="selectById" parameterType="string" resultType="com.czxy.ssm.domain.User">
     select * from user where uid = #{id}
     </select>
     </mapper>

3.2.4 修改核心配置文件

核心配置文件 <mapper resource=""/> 用于确定mapper配置文件的位置

	<!-- 表示加载此包下的所有dao接口-->
	<mapper resource="mapper/UserMapper.xml"/>
</mappers>

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;

/**
 * @author sky
 */
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基本结构

<?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类">
	<!-- 查询 -->
	<select id="方法名">SQL语句</select>
	<!-- 添加 -->
	<insert id="方法名">SQL语句</insert>
    <!-- 更新 -->
	<update id="方法名">SQL语句</update>
    <!-- 删除 -->
	<delete id="方法名">SQL语句</delete>
</mapper>

3.3.2 Mapper 参数类型：parameterType

在映射文件mapper中，我们使用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="mapper类">
    <select id="方法名" parameterType="参数类型">SQL语句</select>
</mapper>

简单类型：

<!-- 通过id查询 -->
<select id="findUserById" parameterType="int" resultType="com.czxy.domain.User">
	select * from user where uid = #{id}
</select>

POJO类型：

<!-- 添加用户 -->
<insert id="insertUser" parameterType="com.czxy.domain.User">
	insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})
</insert>

3### .3.3 Mapper 结果类型：resultType

• 在映射文件mapper中，我们使用resultType设置查询结果

<?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类">
    <select id="方法名" resultType="结果类型">SQL语句</select>
</mapper>

简单类型

<!-- 总记录数 -->
<select id="findUserCount"  resultType="int">
	select count(*) from user
</select>

POJO类型

<!-- 通过id查询 -->
<select id="findUserById" parameterType="int" resultType="com.czxy.domain.User">
	select * from user where uid = #{id}
</select>

3.3.4 Mapper 映射关系：ResultMap

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

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

• 语法：

<!--声明映射-->
<resultMap type="JavaBean类型" id="resultMap名称">
	<id column="表列名" property="JavaBean属性名"/>		<!--用于配置主键的映射-->
	<result column="表列名" property="JavaBean属性名"/>	<!--用于配置普通结果集映射-->
</resultMap>
<!--使用映射-->
<select resultMap="resultMap名称">
</select>

实例：

	<resultMap type="com.czxy.ssm.domain.User" id="userResultMap">
		<id column="uid" property="uid"/>
		<result column="username" property="username"/>
	</resultMap>

    <select id="selectById" parameterType="string" resultMap="userResultMap">
		select * from user where uid = #{id}
	</select>

3.3.5 默认别名

3.3.6 自定义别名

声明别名，在 SqlMapConfig.xml 配置文件中声明

• 方式1：一次定义一个类

  <typeAliases>
  	<typeAlias type="类型" alias="别名"/>
  </typeAliases>
  

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

  <typeAliases>
  	<package name="包名"/>
  </typeAliases>
  

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

  <!--使用别名-->
  <select id="selectById" parameterType="string" resultType="user">
  	select * from user where uid = #{id}
  </select>
  

3.3.7 加载Mapper文件

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

• 方式1：加载指定文件

  	<mappers>
  		<!-- 表示加载此包下的所有dao接口-->
  		<mapper resource="mapper/UserMapper.xml"/>
  	</mappers>
  

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

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

  	<mappers>
  		<package name="com.czxy.ssm.mapper"/>
  	</mappers>
  

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);

  /**
   * 查询所有
   * @return
   */
  public List<User> selectAll();


  /**
   * 模糊查询
   * @param name
   * @return
   */
  public List<User> selectByName(@Param("name") String name);

  /**
   * 插入数据
   * @param user
   */
  public Integer insert(User user);

  /**
   * 插入数据
   * @param user
   */
  public Integer updateByPrimaryKey(User user);

  /**
   * 通过id删除
   * @param uid
   */
  public Integer deleteByPrimaryKey(@Param("uid") String uid);

  /**
   * 条件查询
   * @param userVo
   * @return
   */
  public List<User> condition(UserVo userVo);
}

3.4.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">
<mapper namespace="com.czxy.ssm.mapper.UserMapper">

	<resultMap type="com.czxy.ssm.domain.User" id="userResultMap">
		<id column="uid" property="uid"/>
		<result column="username" property="username"/>
	</resultMap>

    <select id="selectById" parameterType="string" resultMap="userResultMap">
		select * from user where uid = #{id}
	</select>

	<select id="selectAll" parameterType="string" resultMap="userResultMap">
		select * from user
	</select>

	<select id="selectByName" parameterType="string" resultMap="userResultMap">
		select * from user where name like #{name}
	</select>

	<insert id="insert" parameterType="user">
		insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})
	</insert>

	<update id="updateByPrimaryKey" parameterType="user">
		update user set username=#{username}, password=#{password}, name=#{name}, email=#{email},birthday=#{birthday},sex=#{sex}, state=#{state} where uid=#{uid}
	</update>

	<delete id="deleteByPrimaryKey" >
		delete from user where uid = #{uid}
	</delete>

</mapper>

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;

/**
 * @author sky
 */
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<User> list = UserMapper.selectAll();
        // 打印
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testSelectByName() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
        List<User> 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 <if>标签

• 语法：在mapper映射文件中，<if>标签就相当于Java中if语句，如果条件成立，标签体内的SQL语句有效。

<if test="条件">
	//有效的SQL语句片段
</if>
<select>

封装对象：UserVo

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

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

<select id="condition"        parameterType="userVo"resultType="user" >
		select * from user where 1=1
		<if test="name != null and name != ''">
			and name like '%${name}%'
		</if>
	</select>

测试

@Test
    public void testCondtion() {
        UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
        UserVo userVo = new UserVo();
        userVo.setName("张");
        List<User> list = UserMapper.condition(userVo);
        // 打印
        list.forEach(System.out::println);
        MyBatisUtils.commitAndclose();
    }
     

3.5.3 <where>标签

• 多条件查询时，我们使用了一个小技巧“where 1=1”，mybatis提供了一个<where>进行取代。

• 修改映射文件

• 

<select id="condition" parameterType="userVo" resultType="user" >
		select * from user
		<where>
			<if test="name != null and name != ''">
				and name like '%${name}%'
			</if>
		</where>
	</select>

3.5.4 <foreach>标签

• 语法：

 <!--
 <foreach> 就是遍历一组数据，根据指定的内容拼凑成SQL语句片段
 		collection ，需要遍历的数据，如果实际参数就是数组本身，此处需要使用array
 		item ，每一次循环时存放数据的变量，在标签体中可以通过 ${item值}获得遍历数据
 		open ，SQL语句片段的开发位置
 		separator，分隔符号
 		close ，SQL语句片段的结束位置
 -->
 
 <!-- 例如：uid in (1,2,4,6) 片段的拼凑
 	开始 (变量 分隔符)*4 结束
 -->
 <foreach collection="数组" open="uid in (" item="变量" separator=","  close=")">
 </foreach>

• 封装对象

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

多条件查询

<select id="condition" parameterType="userVo" resultType="user" >
		select * from user
		<where>
			<!-- uid in (1,2,4,6) -->
			<foreach collection="ids" open="uid in (" item="id" separator="," close=")">
				'${id}'
			</foreach>
		</where>
	</select>

测试

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

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

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

        MyBatisUtils.commitAndclose();
    }

3.5.5 <choose>标签：多条件

语法

<select id="">
		<choose>
			<when test=""></when>
			<when test=""></when>
			<otherwise></otherwise>
		</choose>
	</select>

3.5.6 <sql>标签

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

<!-- 所有字段名称SQL片段 -->
<sql id="user_all_column">uid, username, password, name, email, birthday, sex, state</sql>

<select id="selectAll" parameterType="string" resultMap="userResultMap">
	select <include refid="user_all_column" /> from user
</select>

3.6 关系映射：一对多

3### .6.1 mapper接口

• UserMapper，已有

  public interface UserMapper {
      /**
       * 通过id查询详情
       * @param uid
       * @return
       */
      public User selectById(String uid);
  }
  

• OrderMapper

  package com.czxy.ssm.mapper;
  
  import com.czxy.ssm.domain.Order;
  import org.apache.ibatis.annotations.Param;
  
  /**
   * @author sky
   */
  public interface OrderMapper {
  
      /**
       * 查询指定用户的所有的订单
       * @param uid
       * @return
       */
      public Order findOrdersByUserId(@Param("uid") Integer uid) ;
  }
  
  

3### .6.2 Mapper 映射文件

• OrdersMapper.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.czxy.ssm.mapper.OrderMapper">
  
  
      <select id="findOrdersByUserId" parameterType="string" resultType="order" >
  		select * from orders where uid = #{uid}
  	</select>
  
  </mapper>
  

• UserMapper.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.czxy.ssm.mapper.UserMapper">
  
  	<resultMap type="com.czxy.ssm.domain.User" id="userResultMap">
  		<id column="uid" property="uid"/>
  		<result column="username" property="username"/>
  		<collection property="orderList" column="uid" select="com.czxy.ssm.mapper.OrderMapper.findOrdersByUserId" />
  	</resultMap>
  
  
  
      <select id="selectById" parameterType="string" resultMap="userResultMap">
  		select * from user where uid = #{id}
  	</select>
      
  </mapper>
  

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;

/**
 * @author sky
 */
public interface OrderMapper {

    /**
     *
     * @param oid
     * @return
     */
    public Order selectById(@Param("oid") String oid);
}

3.7.2 映射文件OrdersMapper.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.czxy.ssm.mapper.OrderMapper">

	<resultMap id="ordersResultMap" type="order">
		<id property="oid" column="oid"></id>
		<result property="ordertime" column="ordertime" ></result>
		<result property="total" column="total" ></result>
		<result property="state" column="state" ></result>
		<result property="address" column="address" ></result>
		<result property="name" column="name" ></result>
		<result property="telephone" column="telephone" ></result>
		<result property="uid" column="uid" ></result>
		<association property="user" select="com.czxy.ssm.mapper.UserMapper.selectById" column="uid" />
	</resultMap>
	<select id="selectById" resultMap="ordersResultMap">
		select * from orders where oid = #{oid}
	</select>

</mapper>

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;

/**
 * @author sky
 */
public class TestOrderMapper {


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

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

        MyBatisUtils.commitAndclose();
    }

}