Mybatis总结笔记 -- 代码亲测有效

一、初始化项目

IDEA中创建一个空的Maven项目即可。

二、项目的搭建

2.1、引入相关的jar包(都使用最新)

1、因为要连接mysql并进行数据库的操作，所以引入mysql 的驱动包是必须的！

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.20</version>
</dependency>

2、因为要使用Mybatis框架，所以要引入mybatis的包！

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.5</version>
</dependency>

3、为了对增删改查进行测试，这里使用Junit，引入Junit的包！

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>test</scope>
</dependency>

4、为了了解mybatis中sql语句是怎么样被操作的，这里用打印日志来观察，引入log4j!

<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2.2、resources目录

resources目录进行一些资源的配置！

2.2.1、数据源的配置（使用Druid数据源）

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://49.233.29.63:3306/gj?characterEncoding=utf-8&useUnicode=true&serverTimezone=UTC
username=root
password=123456
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

validationQuery:SELECT 1
testWhileIdle:true
testOnBorrow:false
testOnReturn:false

2.2.2、日志文件的配置

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/gj.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

2.2.3、mybatis的配置（重要）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--引入外部配置文件-->
    <properties resource="druid.properties" />
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <!--全限定名的别名-->
    <typeAliases>
        <typeAlias type="com.gj.pojo.User" alias="User" />
        <!--<package name="com.gj.pojo">-->
        <!--pojo中类多的情况用第二种，别名为小写的类名-->
    </typeAliases>
    <!--可以有多套环境，default可根据id选择-->
    <environments default="mysql">
        <!--id不同，可配置多套环境-->
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClassName}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/gj/mapper/UserMapper.xml" />
    </mappers>
</configuration>

2.2.4、SQL语句编写的文件

Mybatis对数据库的操作进行了简化，使得可以专注与业务逻辑的编写。在src/main/java中写接口就好，具体的操作在配置文件中完成！

在resources目录下创建com/gj/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.gj.mapper.UserMapper">
    
</mapper>

namespace:可以使此xml文件与接口连接，实现操作！

2.3、代码部分

2.3.1、POJO

创建pojo包，注：类中的成员变量名称需要与数据库的字段名一致，否则mybatis找不到对应的数据库字段，会报错！！

package com.gj.pojo;

public class User {
    private int id;
    private String name;
    private String sex;
    private String perms;

    public User() {
    }

    public User(int id, String name, String sex, String perms) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.perms = perms;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", perms='" + perms + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getPerms() {
        return perms;
    }

    public void setPerms(String perms) {
        this.perms = perms;
    }
}

2.3.2、接口的实现

因为只是对mybatis的简单学习使用，就不创建service层！

创建mapper包，编写接口类，与之前的dao一样，只是名字的改变！

package com.gj.mapper;

import com.gj.pojo.User;

import java.util.List;

public interface UserMapper {
    
}

至此，mybatis的项目框架已搭建完成！

三、Mybatis的简单使用

1、编写接口

List<User> queryAll();

2、编写UserMapper.xml

<select id="queryAll" resultType="User">
    select *
    from user;
</select>

3、测试

@Test
public void test01() throws IOException {
    InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    SqlSessionFactory factory = builder.build(in);
    SqlSession session = factory.openSession();
    UserMapper userMapper = session.getMapper(UserMapper.class);
    List<User> users = userMapper.queryAll();
    for (User user : users){
        System.out.println(user);
    }
}

说明：

//读取配置文件
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        //创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //使用工厂生产SqlSession对象
        SqlSession session = factory.openSession();
        //使用SqlSession创建Dao接口的代理对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        //使用代理对象执行方法
        List<User> users = userMapper.queryAll();

四、CRUD操作

说明：使用mybatis操作就是上述几步，可以封装为一个工具类！

package com.gj.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 java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;

static{
    try {
        //获取SqlSessionFactory对象
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

//openSession(true)会自动提交事务
public static SqlSession getSqlSession(){
    return sqlSessionFactory.openSession(true);
}
}

1、查询已经在上述说明，此处为带参查询！

1-1编写接口

User getUserById(int id);

1-2编写xml

<select id="getUserById" resultType="User" parameterType="int">
        select * from user where id = #{id};
</select>

1-3测试

 @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User userById = mapper.getUserById(56);
        System.out.println(userById);
        sqlSession.close();
    }
    

2、删除操作

2-1编写接口

void deleteUser(int id);

2-2编写xml

<delete id="deleteUser" parameterType="int">
        delete from user where id = #{id};
    </delete>

2-3测试

@Test
    public void deletetUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(61);
        sqlSession.commit();
        sqlSession.close();
    }

说明：增删改操作要提交事务，但在工具类中可以定义，不需要在此处提交事务！

3、增加操作

3-1编写接口

void insertUser(User user);

3-2编写xml

<insert id="insertUser" parameterType="com.gj.pojo.User" >
        insert into user(username, birthday, sex, address) VALUES (#{username}, #{birthday}, #{sex}, #{address});
    </insert>

3-3测试

@Test
    public void insertUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUsername("钱三");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("吕梁市离石区");
        mapper.insertUser(user);
        sqlSession.commit();
        sqlSession.close();
    }

4、修改操作

4-1编写接口

void updateUser(User user);

4-2编写xml

<update id="updateUser" parameterType="com.gj.pojo.User">
        update user set username = #{username}, birthday = #{birthday}, sex = #{sex}, address = #{address} where id = #{id};
    </update>

4-3测试

@Test
    public void updatetUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(61);
        user.setUsername("钱六");
        user.setBirthday(new Date());
        user.setSex("女");
        user.setAddress("吕梁市离石区");
        mapper.updateUser(user);
        sqlSession.commit();
        sqlSession.close();
    }

五、高阶用法

注意：在mybatis中，有一个万能的传参方式，使用map集合！！！

1、模糊查询

1-1编写接口

List<User> getUserLike(String username);

1-2编写xml

<select id="getUserLike" resultType="com.gj.pojo.User" parameterType="String">
        select * from user where username like concat('%', #{username}, '%');
    </select>

1-3测试

@Test
    public void getUserLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userLike = mapper.getUserLike("小");
        for (User user : userLike) {
            System.out.println(user);
        }
        sqlSession.close();
    }

2、分页查询

2-1编写接口

List<User> getUserByLimit(Map<String,Integer> map);

2-2编写xml

<select id="getUserByLimit" parameterType="map" resultType="User">
        select * from user limit #{startIndex}, #{pagesSize}
    </select>

2-3测试

@Test
    public void getUserByLimit(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Integer> map= new HashMap<String, Integer>();
        map.put("startIndex",2);
        map.put("pagesSize",2);
        List<User> userByLimit = mapper.getUserByLimit(map);
        for (User user : userByLimit) {
            System.out.println(user);
        }
        sqlSession.close();
    }

3、多条件查询（if）

3-1编写接口

List<User> queryUserByIf(Map map);

3-2编写xml

<select id="queryUserByIf" parameterType="map" resultType="User">
        select * from user
        <where>
			<if test="username != null">
                username like concat('%', #{username}, '%')
           </if>-->
            <if test="sex != null">
                and sex = #{sex}
            </if>
            <include refid="if-username-sex"></include>
        </where>
    </select>

3-3测试

@Test
    public void queryUserByIf(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap map = new HashMap();
        map.put("username","小");
        map.put("sex","女");
        List<User> users = mapper.queryUserByIf(map);
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }

4、查询几条数据（foreach）

4-1编写接口

List<User> queryByForeach(Map map);

4-2编写xml

<select id="queryByForeach" parameterType="map" resultType="User">
        select * from user
            <where>
                <foreach collection="ids" item="id" open=" id in (" separator="," close=")">
                    #{id}
                </foreach>
            </where>
    </select>

4-3测试

@Test
    public void queryByForeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap map = new HashMap();
        List<Integer> list = new ArrayList<Integer>();
        list.add(57);
        list.add(58);
        list.add(59);
        map.put("ids",list);
        List<User> users = mapper.queryByForeach(map);
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }