Mybatis快速配置-不适合入门

Mybatis

1、什么是Mybatis

​ MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

mybatis官网：https://mybatis.org/mybatis-3/zh/getting-started.html

2、快速入门

创建一个maven项目，导入依赖,我们需要导入mybatis和数据库驱动的依赖。

 <dependency>
     <groupId>org.mybatis</groupId>
     <artifactId>mybatis</artifactId>
     <version>3.5.7</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.24</version>
</dependency>

配置文件：

db.properties 主要是配置链接数据的相关参数

log4j.properties 主要是配置一些日志信息，比如控制台输出sql语句等

MybatisConfig.xml 主要是mybatis的一些相关信息

2.1、db.properties

这个配置文件单独拉出来主要是为了后期修改数据库链接而不用修改mybatis的配置。

username=root
password=123456
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.100.129:3306/hand_mybatis?serverTimezone=UTC

2.2、 log4j.properties

这个文件是非必须的，但是为了我们方便调试，我们可以将sql语句输出在控制台上。

# 日志级别
log4j.rootLogger=debug,stdout,logfile
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#定位到项目的dao层
log4j.logger.com.hand.chenbin.dao=TRACE

2.3、MybatisConfig.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>
    <!--引入外部数据源-->
    <properties resource="db.properties"></properties>

    <!--配置日志-->
    <settings>
        <!--在控制台打印sql-->
        <setting name="logImpl" value="LOG4J"/>
        <setting name="cacheEnabled" value="true"></setting>
    </settings>

    <!--配置环境链接-->
    <environments default="">
        <!--单个数据库配置信息-->
        <environment id="">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <!--配置用户名-->
                <property name="username" value="${username}"/>
                <!-- 配置密码-->
                <property name="password" value="${password}"/>
                <!--配置驱动-->
                <property name="driver" value="${driver}"/>
                <!--地址-->
                <property name="url" value="${url}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/UsersDao.xml" />
        <mapper resource="mapper/RoleDao.xml"/>

    </mappers>
</configuration>

2.4、编写java类

dao层为数据访问层

entity 为实体类

service 我们来编写一个sqlsessionFactory

mapper 编写动态sql

为了简便我们忽略controller和service

service层的MybatisUtil

public class MybatisUtil {
    //为了方便将得到SqlSessionFactory对象封装成一个方法
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "MybatisConfig.xml";  //读取配置文件
        InputStream inputStream =
                Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

}

dao层

public interface UsersDao {

    Users queryById(Long id);

    List<Users> queryAllByLimit(@Param("offset") int offset, @Param("limit") int limit);

    List<Users> queryAll(Users users);

    int insert(Users users);

    int update(Users users);

    int deleteById(Long id);

    void insert();

    List<Users> queryAllByAge(Integer age);

    List<Users> queryAllByIdList(List<Integer> list);
}

entity 层 可以用Lombok来简化getter、setter

public class Users implements Serializable {
    private static final long serialVersionUID = 284578963790589002L;
    
    private Long id;
    
    private String userName;
    
    private String name;
    
    private Date birthday;
    
    private Integer age;


    public Long getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getName() {
        return name;
    }

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

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

}

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.hand.chenbin.dao.UsersDao">

    <resultMap id="BaseResultMap" type="com.hand.chenbin.entity.Users">
        <!--@Table users-->
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="userName" column="user_name" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="birthday" column="birthday" jdbcType="TIMESTAMP"/>
        <result property="age" column="age" jdbcType="INTEGER"/>
       <!--<collection  property="userRole" ofType="com.hand.chenbin.entity.UserRole">
           <id property="id" jdbcType="INTEGER" />
           <id property="roleId" jdbcType="INTEGER"></id>
           <id property="userId" jdbcType="INTEGER"></id>
        </collection>-->
    </resultMap>

    <!--查询单个-->

    <select id="queryById" resultMap="BaseResultMap">
        select
          id, user_name, name, birthday, age
        from hand_mybatis.users
       where id=#{id}
    </select>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="BaseResultMap">
        select
          id, user_name, name, birthday, age
        from hand_mybatis.users
        limit #{offset}, #{limit}
    </select>

    <!--通过实体作为筛选条件查询-->
    <select id="queryAll" resultMap="BaseResultMap">
        select
          id, user_name, name, birthday, age
        from hand_mybatis.users
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="userName != null and userName != ''">
                and user_name = #{userName}
            </if>
            <if test="name != null and name != ''">
                and name = #{name}
            </if>
            <if test="birthday != null">
                and birthday = #{birthday}
            </if>
            <if test="age != null">
                and age = #{age}
            </if>
        </where>
    </select>

    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into hand_mybatis.users(user_name, name, birthday, age)
        values (#{userName}, #{name}, #{birthday}, #{age})
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update hand_mybatis.users
        <set>
            <if test="userName != null and userName != ''">
                user_name = #{userName},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="birthday != null">
                birthday = #{birthday},
            </if>
            <if test="age != null">
                age = #{age},
            </if>
        </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        delete from hand_mybatis.users where id = #{id}
    </delete>




    <!--
   跟据年龄age参数条件判断，如果参数有值，就取参数范围的数据；如果age参数值为空，取age大于20数据。
   -->

    <select id="queryAllByAge" resultMap="BaseResultMap">
        select
        id, user_name, name, birthday, age
        from hand_mybatis.users
        <where>
            <choose>
                <when test="age!=null">
                    and age=#{age}
                </when>
                <otherwise>
                    and age=20
                </otherwise>
            </choose>
        </where>
    </select>
<!--
    /*
    * 掌握foreach，根据参数传入的IdList, 取list范围的数据。
    *
    * */-->

    <select id="queryAllByIdList" resultMap="BaseResultMap">
        select id, user_name, name, birthday, age from hand_mybatis.users where id in
            <foreach collection="list" index="index" item="item"  open="(" separator="," close=")">
                #{item}
            </foreach>
    </select>


</mapper>

2.5编写测试类

public class MybatisMapperTest {
    MybatisUtil mybatisUtil = new MybatisUtil();
    @Test
    public void insert() throws Exception {
        //1、得到sqlSessionFactory对象

        SqlSessionFactory sqlSessionFactory = mybatisUtil.getSqlSessionFactory();
        //获取sqlSession对象
        SqlSession opSession = sqlSessionFactory.openSession();
        //获取接口的实现类
        try {
            Users users = new Users();
            users.setBirthday(new Date());
            users.setAge(24);
            users.setName("lisi");
            users.setUserName("李四");
            UsersDao userDao = opSession.getMapper(UsersDao.class);
            userDao.insert(users);
        } finally {
            opSession.commit();
            opSession.close();
        }
    }

    @Test
    public void select() throws Exception {
        //1、得到sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = mybatisUtil.getSqlSessionFactory();
        //获取sqlSession对象
        SqlSession opSession = sqlSessionFactory.openSession();
        //获取接口的实现类
        try {
            UsersDao mapper = opSession.getMapper(UsersDao.class);
            Users users = mapper.queryById(8L);
        } finally {
            opSession.close();
        }
    }

    @Test
    public void selectList() throws Exception {
        //1、得到sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = mybatisUtil.getSqlSessionFactory();
        //获取sqlSession对象
        SqlSession opSession = sqlSessionFactory.openSession();
        //获取接口的实现类
        try {
            UsersDao mapper = opSession.getMapper(UsersDao.class);
            Users users = new Users();
            users.setAge(23);
            List<Users> users1 = mapper.queryAll(users);
        } finally {
            opSession.close();
        }
    }


/*     1. 改造select查询，支持id, name, user_name的查询, 都是非必输条件*/
    @Test
    public void queryAll() throws Exception {
        //1、得到sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = mybatisUtil.getSqlSessionFactory();
        //获取sqlSession对象
        SqlSession opSession = sqlSessionFactory.openSession();
        //获取接口的实现类
        try {
            UsersDao mapper = opSession.getMapper(UsersDao.class);
            Users users = new Users();
            users.setAge(23);
            List<Users> users1 = mapper.queryAll(users);
        } finally {
            opSession.close();
        }
    }

    /*
    * 跟据年龄age参数条件判断，如果参数有值，就取参数范围的数据；如果age参数值为空，取age大于20数据。
    * */
    @Test
    public void queryAllByAge() throws Exception {
        //1、得到sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = mybatisUtil.getSqlSessionFactory();
        //获取sqlSession对象
        SqlSession opSession = sqlSessionFactory.openSession();
        //获取接口的实现类
        try {
            UsersDao mapper = opSession.getMapper(UsersDao.class);
            List<Users> users1 = mapper.queryAllByAge(null);
        } finally {
            opSession.close();
        }
    }

    /*
    * 掌握foreach，根据参数传入的IdList, 取list范围的数据。
    *
    * */

    @Test
    public void queryAllIDList() throws Exception {
        //1、得到sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = mybatisUtil.getSqlSessionFactory();
        //获取sqlSession对象
        SqlSession opSession = sqlSessionFactory.openSession();
        //获取接口的实现类
        try {
            UsersDao mapper = opSession.getMapper(UsersDao.class);
            List<Integer> list = new ArrayList<Integer>();
            list.add(8);
            list.add(9);
            list.add(10);
            List<Users> users1 = mapper.queryAllByIdList(list);
        } finally {
            opSession.close();
        }
    }

}