Mybatis基本使用总括（单表/多表/动态sql）

淩亂°似流年 2023-10-02 10:09 15阅读 0赞

其实就是持久层的数据控制框架

## 1 基本工具包 ##

### （1）导入包的 方式！ ###

准备：jar包，自己找，mybatis的包和mysql的驱动

maven网址：[https://mvnrepository.com/artifact/org.mybatis/mybatis/3.5.6][https_mvnrepository.com_artifact_org.mybatis_mybatis_3.5.6]，简言之，自己准备包，根据需求选择！

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qWgfuaeqw_size_17_color_FFFFFF_t_70_g_se_x_16][]

### （2）maven依赖管理方式(根据需求选择合适包)！ ###

<dependencies>
            
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.5</version>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.48</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper</artifactId>
                <version>5.1.4</version>
            </dependency>
            <dependency>
                <groupId>com.github.jsqlparser</groupId>
                <artifactId>jsqlparser</artifactId>
                <version>1.1</version>
            </dependency>
    
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                
                
            </dependency>
    
            
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-log4j12</artifactId>
                <version>1.7.20</version>
            </dependency>
        </dependencies>

## 2基本文件 ##

### （1）mybatis的主配置文件(MyBatis.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="jdbc.properties"/>
        
        <settings>
            <setting name="logImpl" value="log4j"/>
        </settings>
    
        
        <typeAliases>
            <package name="bean"/>   
        </typeAliases>
        
        <plugins>
            <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
        </plugins>
    
        
        <environments default="mysql">
            
            <environment id="mysql">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    
                    <property name="driver" value="${driver}"/>
                    <property name="url" value="${url}"/>
                    <property name="username" value="${username}"/>
                    <property name="password" value="${password}"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
            
            <mapper resource="Mapper.xml"></mapper>
            
            
        </mappers>
    </configuration>

### （2）数据库配置文件（jdbc.properties） ###

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql:///mybatismussary
    username=root
    password=root

### (3)日志配置文件（log4j.properties） ###

# 日志格式设置
    # Global logging configuration
    # ERROR WARN INFO DEBUG
    log4j.rootLogger=DEBUG, stdout
    # Console output...
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

## 3 配置文件的加载方式（作为参考）： ##

package 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;
    
    /**
     *2021.9.19
     */
    public class MybatisUtils {
        private static SqlSessionFactory build;
    
        static {
            try {
                InputStream resourceAsStream = Resources.getResourceAsStream("MyBatis.xml");
                build = new SqlSessionFactoryBuilder().build(resourceAsStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * @param b 传递进来的参数的值，true自动提交事务，默认的不会自动提交事务
         */
        public static SqlSession getSqlSession(boolean b) {
            return build.openSession(b);
        }
    
        /**
         * 默认的不会自动提交事务
         */
        public static SqlSession getSqlSession() {
            return build.openSession();
        }
    
    }

## 4 使用方法： ##

### （1）基于xml的使用的用法1： ###

xml映射文件（注意sql的写法，配置文件中已经做了说明）：

（接口省略），在持久层mapper完成这一步，其他层级依次调用即可：

package Mapper;
    
    import bean.User;
    import org.apache.ibatis.session.SqlSession;
    import utils.MybatisUtils;
    
    import java.util.List;
    
    public class UserMapperImp implements UserMapper {
        //开启自动提交事务的方式
        SqlSession sqlSession = MybatisUtils.getSqlSession(true);
    
        @Override
        public List<User> selectAll() {
            //这块指明映射文件中的方法即可
            List<User> objects = sqlSession.selectList("mapper.selectAll");
            sqlSession.close();
            return objects;
        }
    
        @Override
        public User selectOneByCondition(int id) {
            User user = sqlSession.selectOne("mapper.selectOneById", id);
            sqlSession.close();
            return user;
        }
    
        @Override
        public User selectOneByCondition(String name) {
            User user = sqlSession.selectOne("mapper.selectOneByName", name);
            sqlSession.close();
            return user;
        }
    
        @Override
        public void insertUser(User user) {
            int insert = sqlSession.insert("mapper.insert", user);
            sqlSession.close();
            System.out.println(insert);
        }
    
        @Override
        public void deleteUser(User user) {
            int delete = sqlSession.delete("mapper.delete", user);
            sqlSession.close();
            System.out.println(delete);
        }
    }

### （2）基于xml的用法2 ###

xml映射文件（注意sql的写法，配置文件中已经做了说明）：

<?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.PeopleMapper">
        
        <select id="selectAll" resultType="bean.People">
            SELECT * FROM people
        </select>
    
        <select id="selectOneById" resultType="bean.People" parameterType="int">
            SELECT * FROM people WHERE id = #{id}
        </select>
    
        <select id="selectOneByName" resultType="bean.People" parameterType="string">
            SELECT * FROM people WHERE name = #{name}
        </select>
    
        <insert id="insert" parameterType="people">
            
            INSERT INTO people VALUES (#{id},#{name},#{age},#{area},#{hobby},#{grade},#{number})
        </insert>
    
        <update id="update" parameterType="people">
            UPDATE people SET name = #{name},age = #{age} ,area=#{area},hobby=#{hobby},grade=#{grade},number=#{number} WHERE id = #{id}
        </update>
    
        <delete id="delete" parameterType="people">
            DELETE FROM people where name=#{name}
        </delete>
    
        
    
        
        <select id="selectByIds" resultType="bean.People" parameterType="list">
            select * from people
            <where>
                <foreach collection="list" open="id IN (" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </where>
        </select>
    
        
        
        <select id="selectByCondition" resultType="bean.People" parameterType="people">
            select * from people
            <where>
                <if test="id != 0">
                    id = #{id}
                </if>
                <if test="name != null">
                    AND name = #{name}
                </if>
                <if test="age != 0">
                    AND age = #{age}
                </if>
            </where>
        </select>
    </mapper>

dao层，增删改的sql的执行结果是数字，代表改变几行数据发生改变，但是mybatis将执行的结果转化成Boolean类型了，下面的例子没有进行增删改的结果回收，其实boolean结果类型是可以回收到的，这块了解一下即可！

package mapper;
    
    
    import bean.People;
    
    import java.util.List;
    
    public interface PeopleMapper {
        List<People> selectAll();
    
        People selectOneById(int id);
    
        People selectOneByName(String name);
    
        void insert(People people);
    
        void delete(People people);
    
        void update(People people);
    
        //动态sql
        List<People> selectByIds(List<Integer> list);
    
        List<People> selectByCondition(People people);
    
    }

业务层做如下逻辑处理，持久层只写接口，完成配置文件的映射即可

package service;
    
    import bean.People;
    import com.github.pagehelper.PageHelper;
    import mapper.PeopleMapper;
    import org.apache.ibatis.session.SqlSession;
    import utils.MybatisUtils;
    
    import java.util.List;
    
    
    public class ServiceImp implements PeoplerService {
        private SqlSession session;
        private PeopleMapper mapper;
    
        public ServiceImp() {
            session = MybatisUtils.getSqlSession(true);
            mapper = session.getMapper(PeopleMapper.class);
        }
    
        @Override
        public List<People> selectAll(int index,int pageSize ) {
            //分页插件必须先做初始化操作
            PageHelper.startPage(index,pageSize);
            List<People> peoples = mapper.selectAll();
            session.close();
            return peoples;
        }
    
        /**
         * 动态sql.参数是集合示例：
         * @param list
         * @return
         */
        @Override
        public List<People> selectByIds(List<Integer> list) {
            List<People> lists = mapper.selectByIds(list);
            session.close();
            return lists;
        }
        /**
         * 动态sql.参数是不定向条件：
         * @param people
         * @return
         */
        @Override
        public List<People> selectByCondition(People people) {
            List<People> list = mapper.selectByCondition(people);
            session.close();
            return list;
        }
    
    
        /************** 下面的未做实现，只作为举例*********************/
        @Override
        public People selectOneByCondition(int id) {
            return null;
        }
    
        @Override
        public People selectOneByCondition(String name) {
            return null;
        }
    
        @Override
        public void insertUser(People people) {
    
        }
        @Override
        public void deleteUser(People people) {
    
        }
    }

**补充：**

注意：以上xml映射文件是使用的单表的形式，下面举例一对多，一对一，多对多关系，作为写法参考，sql基础还是需要有的：

<?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.Mapper">
        
    
        
        <resultMap id="studentOne" type="student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
            <result column="grade" property="grade"/>
            <result column="mainteacher" property="mainteacher"/>
            <result column="sex" property="sex"/>
            <association property="c" javaType="card">
                <id column="cid" property="id"/>
                <result column="cname" property="name"/>
            </association>
        </resultMap>
        
        <select id="selectAllStudent" resultMap="studentOne">
            select
             s.id id,s.name name,s.age age,s.grade grade,s.mainteacher mainteacher,s.sex sex,c.id cid,c.name cname
            from student s,card c
            where s.id=c.sid
        </select>
    
    
        <resultMap id="oneToMany" type="teacher">
            <id column="tid" property="id"/>
            <result column="tname" property="name"/>
            <result column="coures" property="course"/>
            
            <collection property="list" ofType="student">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
                <result column="age" property="age"/>
                <result column="grade" property="grade"/>
                <result column="mainteacher" property="mainteacher"/>
                <result column="sex" property="sex"/>
            </collection>
        </resultMap>
        <select id="selectAllTeacher" resultMap="oneToMany">
            select
            s.id id,s.name name,s.age age,s.grade grade,s.mainteacher mainteacher,s.sex sex,t.id tid,t.name tname,t.coures coures
            from student s,teacher t
            where t.name = s.mainteacher
        </select>
    
    
        <resultMap id="many" type="student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
            <result column="grade" property="grade"/>
            <result column="mainteacher" property="mainteacher"/>
            <result column="sex" property="sex"/>
            <collection property="list" ofType="classes">
                <id column="cid" property="id"/>
                <result column="cname" property="name"/>
            </collection>
        </resultMap>
        <select id="selectAllStudentClasses" resultMap="many">
            select
             s.id id,s.name name,s.age age,s.grade grade,s.mainteacher mainteacher,s.sex sex,c.cid cid,c.cname cname
            from student s,mid m,course c
            where m.sid=s.id and m.cid=c.cid
        </select>
    </mapper>

mapper文件

package mapper;
    
    import bean.Student;
    import bean.Teacher;
    
    import java.util.List;
    
    public interface Mapper {
        //one to one
        List<Student> selectAllStudent();
        //one to many
        List<Teacher> selectAllTeacher();
    
        List<Student> selectAllStudentClasses();
    
    }

### （3）纯注解的使用方式：作为参考样例：唯一变只是在映射文件是否使用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="jdbc.properties"/>
        
        <settings>
            <setting name="logImpl" value="log4j"/>
        </settings>
    
        
        <typeAliases>
            <package name="bean"/>   
        </typeAliases>
        
        <plugins>
            <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
        </plugins>
    
        
        <environments default="mysql">
            
            <environment id="mysql">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    
                    <property name="driver" value="${driver}"/>
                    <property name="url" value="${url}"/>
                    <property name="username" value="${username}"/>
                    <property name="password" value="${password}"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
          <package name="mapper"/>
        </mappers>
    
    
    </configuration>

单表使用示例（主要还是在mapper映射接口上做操作）：

说明：通过注解@select@dealte@insertt@update进行标注方法写sql的形式

package mapper;
    
    
    import bean.Student;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface Mappers {
        /**
         * 简单的单表查询示例示例
         * @return
         */
        @Select("select * from student")
        List<Student> selectAll();
    
        /**
         * 多表查询，用到看资料就行，太烦！
         * @return
         */
        @Select("")
        List<Student> selectAllCard();
    
    }

多表示例（很少用到，用到来查）：

一对一：

import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface CardMapper {
        //查询全部
        @Select("SELECT * FROM card")
        @Results({
                @Result(column = "id",property = "id"),
                @Result(column = "number",property = "number"),
                @Result(
                        property = "p",             // 被包含对象的变量名
                        javaType = Person.class,    // 被包含对象的实际数据类型
                        column = "pid",             // 根据查询出的card表中的pid字段来查询person表
                        /*
                            one、@One 一对一固定写法
                            select属性：指定调用哪个接口中的哪个方法
                         */
                        one = @One(select = "com.itheima.one_to_one.PersonMapper.selectById")
                )
        })
        public abstract List<Card> selectAll();
    }

public interface PersonMapper {
        //根据id查询
        @Select("SELECT * FROM person WHERE id=#{id}")
        public abstract Person selectById(Integer id);
    }

一对多：

import org.apache.ibatis.annotations.Many;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface ClassesMapper {
        //查询全部
        @Select("SELECT * FROM classes")
        @Results({
                @Result(column = "id",property = "id"),
                @Result(column = "name",property = "name"),
                @Result(
                        property = "students",  // 被包含对象的变量名
                        javaType = List.class,  // 被包含对象的实际数据类型
                        column = "id",          // 根据查询出的classes表的id字段来查询student表
                        /*
                            many、@Many 一对多查询的固定写法
                            select属性：指定调用哪个接口中的哪个查询方法
                         */
    
                       many = @Many(select = "com.itheima.one_to_many.StudentMapper.selectByCid")
                )
        })
        public abstract List<Classes> selectAll();
    }

import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface StudentMapper {
        //根据cid查询student表
        @Select("SELECT * FROM student WHERE cid=#{cid}")
        public abstract List<Student> selectByCid(Integer cid);
    }

多对多：

import org.apache.ibatis.annotations.Many;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface StudentMapper {
        //查询全部
        @Select("SELECT DISTINCT s.id,s.name,s.age" +
                " FROM " +
                "student s,stu_cr sc " +
                "WHERE " +
                "  sc.sid=s.id")
    
        @Results({
                @Result(column = "id",property = "id"),
                @Result(column = "name",property = "name"),
                @Result(column = "age",property = "age"),
                @Result(
                        property = "courses",   // 被包含对象的变量名
                        javaType = List.class,  // 被包含对象的实际数据类型
                        column = "id",          // 根据查询出student表的id来作为关联条件，去查询中间表和课程表
                        /*
                            many、@Many 一对多查询的固定写法
                            select属性：指定调用哪个接口中的哪个查询方法
                         */
                        many = @Many(select = "com.itheima.many_to_many.CourseMapper.selectBySid")
                )
        })
        public abstract List<Student> selectAll();
    }

import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface CourseMapper {
        //根据学生id查询所选课程
        @Select("SELECT c.id,c.name " +
                "FROM stu_cr sc,course c " +
                "+WHERE sc.cid=c.id AND sc.sid=#{id}")
        public abstract List<Course> selectBySid(Integer id);
    }

最后最后一种纯注解用法，用到再说，很烦的那种：在我的资料中有样例，用到再参考

**参数获取方式补充：**

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qWgfuaeqw_size_20_color_FFFFFF_t_70_g_se_x_16][]

[https_mvnrepository.com_artifact_org.mybatis_mybatis_3.5.6]: https://mvnrepository.com/artifact/org.mybatis/mybatis/3.5.6
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qWgfuaeqw_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/20210923004736367.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qWgfuaeqw==,size_17,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qWgfuaeqw_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/20210924045414180.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qWgfuaeqw==,size_20,color_FFFFFF,t_70,g_se,x_16