三、MyBatis

一、概述

MyBatis是一款优秀的持久层框架，用于JDBC开发
官网：https://mybatis.org/mybatis-3/zh_CN/index.html

1、持久层

负责将数据保存到数据库的那一层代码
JavaEE三层架构：表现层、业务层、持久层

二、快速入门

1、准备工作

（1）创建`tb_user`表

sql

create table tb_user(
    id int primary key not null auto_increment unique comment '主键',
    name varchar(255) not null comment '名字',
    age int not null comment '年龄'
) comment '用户表';

insert into tb_user values (1,'张三',19);
insert into tb_user values (2,'李四',27);

（2）编写`User`实体类

java

public class User {
    private Integer id;
    private String name;
    private Integer age;


    public User() {
    }

    public User(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    /**
     * 获取
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 设置
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取
     * @return name
     */
    public String getName() {
        return name;
    }

    /**
     * 设置
     * @param name
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * 获取
     * @return age
     */
    public Integer getAge() {
        return age;
    }

    /**
     * 设置
     * @param age
     */
    public void setAge(Integer age) {
        this.age = age;
    }

    public String toString() {
        return "User{id = " + id + ", name = " + name + ", age = " + age + "}";
    }
}

2、引入mybatis坐标

xml

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.14</version>
    </dependency>
</dependencies>

3、编写`UserMapper.xml`文件

命名规则：实体类名+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">

<!--namespace:名称空间-->
<!--id:-->
<!--resultType:返回类型-->
<mapper namespace="test">
    <select id="selectAll" resultType="org.example.pojo.User">
        select * from tb_user
    </select>
</mapper>

4、配置`mybatis-config.xml`

xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--            数据库连接信息-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/flask?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--        加载sql映射文件-->
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

xml

<property name="url" value="jdbc:mysql://localhost/flask?useSSL=false&amp;serverTimezone=GMT%2B8"/>

5、运行

java

public class Main {
    public static void main(String[] args) throws IOException {
//        加载mybatis核心配置文件
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
//        获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);

//      获取SqlSession对象,用它来执行sql
        SqlSession sqlSession=sqlSessionFactory.openSession();

//        执行sql
        List<User> users = sqlSession.selectList("test.selectAll");

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

//        释放资源
        sqlSession.close();
    }
}

三、抽离工厂代码

新建工具类

java

public class SqlSessionFactoryUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

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

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }
}

使用

java

SqlSessionFactory sqlSessionFactory= SqlSessionFactoryUtils.getSqlSessionFactory();

四、代理开发

1、规则

定义与SQL映射文件同名的Mapper接口，并且将Mapper接口和SQL映射文件放置在同一目录下
设置SQL映射文件的namespace属性为Mapper接口全限定名
在Mapper接口中定义方法，方法名就是SQL映射文件中的sql语句的id，并保持参数类型和返回值类型一致
编码
1. 通过SqlSession的getMapper方法获取Mapper接口的代理对象
2. 调用对应方法完成sql的执行

2、使用

（1）在`java.org.example`文件夹下创建`mapper`文件夹，新建`UserMapper`文件

其中定义了一个抽象方法selectAll

java

public interface UserMapper {
    List<User> selectAll();
}

（2）在`resource.org.example.mapper`文件夹下新建`UserMapper.xml`文件

namespace需要和java.org.example.mapper下的UserMapper保持一致
id需要和UserMapper定义的抽象方法的方法名一致
resultType为SQL语句返回值类型

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">

<!--namespace:名称空间-->
<!--id:方法名称-->
<!--resultType:返回类型-->
<mapper namespace="org.example.mapper.UserMapper">
    <select id="selectAll" resultType="org.example.pojo.User">
        select * from tb_user
    </select>
</mapper>

（3）加载sql映射文件

xml

<mappers>
  	<!--方式一：单独加载sql映射文件-->
	<!-- <mapper resource="org/example/mapper/UserMapper.xml"/>-->
    
	<!-- 方式二：自动加载mapper文件夹所有映射文件-->
	<package name="org.example.mapper"/>
</mappers>

（4）编写核心代码

java

public class Main {
    public static void main(String[] args) throws IOException {
		// 加载mybatis核心配置文件
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
		//获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);

		//获取SqlSession对象,用它来执行sql
        SqlSession sqlSession=sqlSessionFactory.openSession();

		//取UserMapper接口的代理对象
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.selectAll();

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

		//释放资源
        sqlSession.close();
    }
}

五、核心配置文件

官方文档：https://mybatis.org/mybatis-3/zh_CN/configuration.html#typeAliases

六、配置文件进行增删改查

1、准备工作

（1）SQL

sql

create table tb_user(
    id int primary key not null auto_increment unique comment '主键',
    name varchar(255) not null comment '名字',
    age int not null comment '年龄',
    create_time datetime not null comment '创建时间',
    status int not null default 0 comment '状态'
) comment '用户表';

insert into tb_user values (1,'张三',19);
insert into tb_user values (2,'李四',27);

（2）实体类

java

package org.example.pojo;

import java.util.Date;

public class User {
    private Integer id;
    private String name;
    private Integer age;
    private Date createTime;
    private Integer status;

    public User() {
    }

    public User(Integer id, String name, Integer age, Date createTime, Integer status) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.createTime = createTime;
        this.status = status;
    }

    /**
     * 获取
     *
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 设置
     *
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取
     *
     * @return name
     */
    public String getName() {
        return name;
    }

    /**
     * 设置
     *
     * @param name
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * 获取
     *
     * @return age
     */
    public Integer getAge() {
        return age;
    }

    /**
     * 设置
     *
     * @param age
     */
    public void setAge(Integer age) {
        this.age = age;
    }

    /**
     * 获取
     *
     * @return createTime
     */
    public Date getCreateTime() {
        return createTime;
    }

    /**
     * 设置
     *
     * @param createTime
     */
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    /**
     * 获取
     *
     * @return status
     */
    public Integer getStatus() {
        return status;
    }

    /**
     * 设置
     *
     * @param status
     */
    public void setStatus(Integer status) {
        this.status = status;
    }

    public String toString() {
        return "User{id = " + id + ", name = " + name + ", age = " + age + ", createTime = " + createTime + ", status = " + status + "}";
    }
}

1、查询

（1）查询所有

UserMapper
java
```
 List<User> selectAll();
```
1

UserMapper.xml

xml

<select id="selectAll" resultType="org.example.pojo.User">
    select * from tb_user
</select>

测试

java

@Test
public void selectAll() throws IOException {
    String resource="mybatis-config.xml";
    InputStream inputStream= Resources.getResourceAsStream(resource);
    
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession=sqlSessionFactory.openSession();
    
    List<User> users = sqlSession.getMapper(UserMapper.class).selectAll();
    
    System.out.println(users);
    
    sqlSession.close();
}

出现null值

解决办法

xml

<mapper namespace="org.example.mapper.UserMapper">
    <resultMap id="userResultMap" type="org.example.pojo.User">
        <result column="create_time" property="createTime"/>
    </resultMap>

    <select id="selectAll" resultMap="userResultMap">
        select * from tb_user
    </select>
</mapper>

（2）单字段条件查询

UserMapper

java

public interface UserMapper {
    User selectById(int id);
}

UserMapper.xml

xml

<mapper namespace="org.example.mapper.UserMapper">
    <resultMap id="userResultMap" type="org.example.pojo.User">
        <result column="create_time" property="createTime"/>
    </resultMap>

    <select id="selectById" resultMap="userResultMap">
        select * from tb_user where id= #{id}
    </select>
</mapper>

测试

java

@Test
public void selectById() throws IOException{
    String resource ="mybatis-config.xml";
    InputStream resourceAsStream = Resources.getResourceAsStream(resource);
    
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    
    int id=1;
    
    User user = sqlSession.getMapper(UserMapper.class).selectById(id);
    
    System.out.println(user);
    
    sqlSession.close();
}

（3）多字段条件查询

三种方式查询：

散装参数：需要使用@Param("SQL中参数占位符名称")
实体类封装参数
map集合

散装参数

1、UserMapper

java

public interface UserMapper {
    List<User> selectByCondition(@Param("age") int age , @Param("status") int status);
}

2、UserMapper.xml

xml

<mapper namespace="org.example.mapper.UserMapper">
    <resultMap id="userResultMap" type="org.example.pojo.User">
        <result column="create_time" property="createTime"/>
    </resultMap>
	<select id="selectByCondition" resultMap="userResultMap">
    	select *
    	from tb_user
    	where age = #{age}
      	and status = #{status}
	</select>
</mapper>

3、测试

java

//方式一：散装参数
int age=21;
int status=1;

List<User> users = sqlSession.getMapper(UserMapper.class).selectByCondition(age, status);

System.out.println(users);

实体类封装参数

1、UserMapper

java

List<User> selectByCondition(User user);

2、UserMapper.xml

xml

<mapper namespace="org.example.mapper.UserMapper">
    <resultMap id="userResultMap" type="org.example.pojo.User">
        <result column="create_time" property="createTime"/>
    </resultMap>
	<select id="selectByCondition" resultMap="userResultMap">
    	select *
    	from tb_user
    	where age = #{age}
      	and status = #{status}
	</select>
</mapper>

3、测试

java

//方式二：实体类封装参数
int age=21;
int status=1;

User user=new User();
user.setAge(age);
user.setStatus(status);

List<User> users = sqlSession.getMapper(UserMapper.class).selectByCondition(user);

System.out.println(users);

map集合

1、UserMapper

java

List<User> selectByCondition(Map map);

2、UserMapper.xml

xml

<mapper namespace="org.example.mapper.UserMapper">
    <resultMap id="userResultMap" type="org.example.pojo.User">
        <result column="create_time" property="createTime"/>
    </resultMap>
	<select id="selectByCondition" resultMap="userResultMap">
    	select *
    	from tb_user
    	where age = #{age}
      	and status = #{status}
	</select>
</mapper>

3、测试

java

//方式三：map集合
int age=21;
int status=1;

HashMap map = new HashMap();
map.put("age",age);
map.put("status",status);

List<User> users = sqlSession.getMapper(UserMapper.class).selectByCondition(map);
System.out.println(users);

sqlSession.close();

（4）动态SQL查询-多字段条件

UserMapper.xml

xml

<select id="selectByCondition" resultMap="userResultMap">
    select *
    from tb_user
    <where>
        <if test="age!=null and age !=0">
            and age = #{age}
        </if>
        <if test="status!=null">
            and status = #{status}
        </if>
    </where>
</select>

测试

java

@Test
public void selectByCondition() throws IOException{
    String resource ="mybatis-config.xml";
    InputStream resourceAsStream = Resources.getResourceAsStream(resource);

    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    int age=25;
    Integer status=null;

    HashMap map = new HashMap();
    map.put("age",age);
    map.put("status",status);

    List<User> users = sqlSession.getMapper(UserMapper.class).selectByCondition(map);
    System.out.println(users);

    sqlSession.close();
}

（5）动态SQL查询-单字段条件

UserMapper.xml

xml

<select id="selectByConditionSingle" resultMap="userResultMap">
    select *
    from tb_user where
    <choose>
        <when test="age!=null and age !=0">
            age = #{age}
        </when>
        <when test="status!=null">
            status = #{status}
        </when>
        <otherwise>
            1=1
        </otherwise>
    </choose>
</select>

测试

java

@Test
public void selectByConditionSingle() throws IOException{
    String resource ="mybatis-config.xml";
    InputStream resourceAsStream = Resources.getResourceAsStream(resource);
    
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    
    Integer age=null;
    Integer status=1;
    
    HashMap map = new HashMap();
    map.put("age",age);
    map.put("status",status);
    
    List<User> users = sqlSession.getMapper(UserMapper.class).selectByConditionSingle(map);
    System.out.println(users);
    
    sqlSession.close();

2、插入

（1）插入数据

UserMapper

java

public interface UserMapper {
	void insertInfo(User user);
}

UserMapper.xml

xml

<insert id="insertInfo">
        insert into tb_user (name,age,create_time,status) values (#{name},#{age},#{createTime},#{status})
</insert>

测试

java

@Test
  public void insertInfo() throws IOException{
      String resource ="mybatis-config.xml";
      InputStream resourceAsStream = Resources.getResourceAsStream(resource);
      
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
      SqlSession sqlSession = sqlSessionFactory.openSession();
      
      String name="小明2";
      Integer age=19;
      Date createTime=new Timestamp(new Date().getTime());
      Integer status=0;
      
      User user=new User();
      user.setName(name);
      user.setAge(age);
      user.setCreateTime(createTime);
      user.setStatus(status);
      
      sqlSession.getMapper(UserMapper.class).insertInfo(user);
      
      sqlSession.commit();
      
      sqlSession.close();
}

（2）插入数据后返回主键信息

UserMapper.xml

xml

<insert id="insertInfo" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user (name,age,create_time,status) values (#{name},#{age},#{createTime},#{status})
</insert>

测试
java
```
System.out.println(user.getId());
```
1

3、修改

（1）修改所有字段

UserMapper
java
```
int updateInfo(User user);
```
1

UserMapper.xml

xml

<update id="updateInfo">
    update tb_user
    set name=#{name},
        age=#{age},
        create_time=#{createTime},
        status=#{status}
    where id = #{id}
</update>

测试

java

@Test
public void updateInfo() throws IOException{
    String resource ="mybatis-config.xml";
    InputStream resourceAsStream = Resources.getResourceAsStream(resource);
    
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    
    Integer age=19;
    String name="23234gvdfv";
    Date createTime=new Timestamp(new Date().getTime());
    Integer status=0;
    Integer id=2;
    
    User user=new User();
    user.setName(name);
    user.setAge(age);
    user.setStatus(status);
    user.setCreateTime(createTime);
    user.setId(id);
    
    int i = sqlSession.getMapper(UserMapper.class).updateInfo(user);
    System.out.println(i);
    
    sqlSession.commit();
    sqlSession.close();
}

（2）修改多个字段

UserMapper
java
```
int updateInfo(User user);
```
1

UserMapper.xml

xml

<update id="updateInfo">
    update tb_user
    <set>
        <if test="name!=null and name!=''">
            name=#{name},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
        <if test="createTime!=null">
            create_time=#{createTime},
        </if>
        <if test="status!=null">
            status=#{status}
        </if>
    </set>
    where id =#{id}
</update>

测试

java

@Test
public void updateInfo() throws IOException{
    String resource ="mybatis-config.xml";
    InputStream resourceAsStream = Resources.getResourceAsStream(resource);
    
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    
    Integer age=19;
    String name="羽霆峰";
    Integer id=9;
    
    User user=new User();
    user.setName(name);
    user.setAge(age);
    user.setId(id);
    
    int i = sqlSession.getMapper(UserMapper.class).updateInfo(user);
    System.out.println(i);
    
    sqlSession.commit();
    sqlSession.close();
}

4、删除

（1）删除单条数据

UserMapper
java
```
void deleteInfo(Integer id);
```
1

UserMapper.xml

xml

<delete id="deleteInfo">
    delete from tb_user where id = #{id}
</delete>

测试

java

@Test
public void deleteInfo() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream resourceAsStream = Resources.getResourceAsStream(resource);
    
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    
    Integer id = 35;
    sqlSession.getMapper(UserMapper.class).deleteInfo(id);
    
    sqlSession.commit();
    sqlSession.close();
}

（2）删除多条数据

UserMapper

java

void deleteInfoList(@Param("ids") ArrayList<Integer> ids);

UserMapper.xml

xml

<delete id="deleteInfoList">
    delete from tb_user where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach>
</delete>

测试

java

 @Test
 public void deleteInfo() throws IOException {
     String resource = "mybatis-config.xml";
     InputStream resourceAsStream = Resources.getResourceAsStream(resource);
     
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
     SqlSession sqlSession = sqlSessionFactory.openSession();
     
     ArrayList<Integer> ids=new ArrayList<>();
     ids.add(7);
     ids.add(8);
     ids.add(9);
     ids.add(34);
     
     sqlSession.getMapper(UserMapper.class).deleteInfoList(ids);
     sqlSession.commit();
     sqlSession.close();
 }

七、注解进行增删改查

1、查询

java

@Select("select * from tb_user")
List<User> selectAll();

2、插入

java

@Insert("insert into tb_user (name,age,create_time,status) values (#{name},#{age},#{create_time},#{status})")
void insertInfo(User user);

3、修改

java

@Update("update tb_user set name=#{name} where id = #{id}")
void updateInfo(User user);

4、删除

java

@Delete("delete from tb_user where id = #{id}")
void deleteInfo(id);

三、MyBatis ​

一、概述 ​

1、持久层 ​

二、快速入门 ​

1、准备工作 ​

（1）创建tb_user表 ​

（2）编写User实体类 ​

2、引入mybatis坐标 ​

3、编写UserMapper.xml文件 ​

4、配置mybatis-config.xml ​

5、运行 ​

三、抽离工厂代码 ​

四、代理开发 ​

1、规则 ​

2、使用 ​

（1）在java.org.example文件夹下创建mapper文件夹，新建UserMapper文件 ​

（2）在resource.org.example.mapper文件夹下新建UserMapper.xml文件 ​

（3）加载sql映射文件 ​

（4）编写核心代码 ​

五、核心配置文件 ​

六、配置文件进行增删改查 ​

1、准备工作 ​

（1）SQL ​

（2）实体类 ​

1、查询 ​

（1）查询所有 ​

UserMapper ​

UserMapper.xml ​

测试 ​

（2）单字段条件查询 ​

UserMapper ​

UserMapper.xml ​

测试 ​

（3）多字段条件查询 ​

散装参数 ​

实体类封装参数 ​

map集合 ​

（4）动态SQL查询-多字段条件 ​

UserMapper.xml ​

测试 ​

（5）动态SQL查询-单字段条件 ​

UserMapper.xml ​

测试 ​

2、插入 ​

（1）插入数据 ​

UserMapper ​

UserMapper.xml ​

测试 ​

（2）插入数据后返回主键信息 ​

UserMapper.xml ​

测试 ​

3、修改 ​

（1）修改所有字段 ​

UserMapper ​

UserMapper.xml ​

测试 ​

（2）修改多个字段 ​

UserMapper ​

UserMapper.xml ​

测试 ​

4、删除 ​

（1）删除单条数据 ​

UserMapper ​

UserMapper.xml ​

测试 ​

（2）删除多条数据 ​

UserMapper ​

UserMapper.xml ​

测试 ​

七、注解进行增删改查 ​

1、查询 ​

2、插入 ​

3、修改 ​

4、删除 ​

三、MyBatis

一、概述

1、持久层

二、快速入门

1、准备工作

（1）创建`tb_user`表

（2）编写`User`实体类

2、引入mybatis坐标

3、编写`UserMapper.xml`文件

4、配置`mybatis-config.xml`

5、运行

三、抽离工厂代码

四、代理开发

1、规则

2、使用

（1）在`java.org.example`文件夹下创建`mapper`文件夹，新建`UserMapper`文件

（2）在`resource.org.example.mapper`文件夹下新建`UserMapper.xml`文件

（3）加载sql映射文件

（4）编写核心代码

五、核心配置文件

六、配置文件进行增删改查

1、准备工作

（1）SQL

（2）实体类

1、查询

（1）查询所有

UserMapper

UserMapper.xml

测试

（2）单字段条件查询

UserMapper

UserMapper.xml

测试

（3）多字段条件查询

散装参数

实体类封装参数

map集合

（4）动态SQL查询-多字段条件

UserMapper.xml

测试

（5）动态SQL查询-单字段条件

UserMapper.xml

测试

2、插入

（1）插入数据

UserMapper

UserMapper.xml

测试

（2）插入数据后返回主键信息

UserMapper.xml

测试

3、修改

（1）修改所有字段

UserMapper

UserMapper.xml

测试

（2）修改多个字段

UserMapper

UserMapper.xml

测试

4、删除

（1）删除单条数据

UserMapper

UserMapper.xml

测试

（2）删除多条数据

UserMapper

UserMapper.xml

测试

七、注解进行增删改查

1、查询

2、插入

3、修改

4、删除