Mybatis学习笔记-基本概念与操作

迷南。 2022-10-22 05:26 208阅读 0赞

## 一、Mybatis简介 ##

**1、什么是Mybatis？**  
MyBatis 是一款优秀的持久层框架：

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

**2、优点：**

*  简单易学，灵活
 *  sql和代码的分离，提高了可维护性。
 *  提供映射标签，支持对象与数据库的orm字段关系映射
 *  提供对象关系映射标签，支持对象关系组建维护
 *  提供xml标签，支持编写动态sql

## 二、增删查改操作 ##

**1、编写主配置文件：**

<?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>
        
        <settings>
            <setting name="cacheEnabled" value="true" />
            <setting name="useGeneratedKeys" value="true" />
            <setting name="defaultExecutorType" value="REUSE" />
            <setting name="logImpl" value="STDOUT_LOGGING" />
        </settings>
        
        <environments default="development">
            <environment id="development">
                <transactionManager type="jdbc"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/day_3_14?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"/>
                    <property name="username" value="root"/>
                    <property name="password" value="password"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
            <mapper resource="com/atguigu/mybatis/Dao/StudentDao.xml"/>
        </mappers>
    </configuration>

**2、编写对应的Dao.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.atguigu.mybatis.Dao.StudentDao">
        <select id="selectAllStudent" resultType="com.atguigu.mybatis.Entry.Student">
            select * from student
        </select>
    
        <select id="selectById" resultType="com.atguigu.mybatis.Entry.Student">
            select id,name,phone,address from student where id=#{stuId}
        </select>
    
        <insert id="insertStudent" parameterType="com.atguigu.mybatis.Entry.Student">
            insert into student values(#{id},#{name},#{phone},#{address})
        </insert>
    
        <update id="updateStudent">
            update student set id=#{id},name=#{name},phone=#{phone},address=#{address}
            where id=#{id}
        </update>
    
        <delete id="deleteStudentById">
            delete from student where id=#{id}
        </delete>
    </mapper>

**3、编写相应的Dao.java**

import com.atguigu.mybatis.Entry.Student;
    import org.apache.ibatis.annotations.Param;
    
    import java.util.List;
    
    public interface StudentDao {
        
        //查询并返回列表结果
        public List<Student> selectAllStudent();
    
        //根据id进行查询
        public Student selectById(@Param("stuId") String id);
    
        //插入对象操作
        public int insertStudent(Student student);
    
        //更新对象操作
        public int updateStudent(Student student);
    
        //删除对象操作
        public int deleteStudentById(String id);
    }

**4、编写相应的实体类**

public class Student {
        
        String id;
        String name;
        String phone;
        String address;
    
        public String getId() {
        
            return id;
        }
    
        public String getName() {
        
            return name;
        }
    
        public String getPhone() {
        
            return phone;
        }
    
        public String getAddress() {
        
            return address;
        }
    
        public void setId(String id) {
        
            this.id = id;
        }
    
        public void setName(String name) {
        
            this.name = name;
        }
    
        public void setPhone(String phone) {
        
            this.phone = phone;
        }
    
        public void setAddress(String address) {
        
            this.address = address;
        }
    
        @Override
        public String toString() {
        
            return "Student{" +
                    "id='" + id + '\'' +
                    ", name='" + name + '\'' +
                    ", phone='" + phone + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

**5、编写mybatis工具类**

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 MybatisUtil {
        
        static SqlSessionFactory sqlSessionFactory = null;
    
        static {
        
            try {
        
                //使用Mybatis第一步 ：获取sqlSessionFactory对象
                String resource = "com/atguigu/mybatis/mybatis-mapper.xml";
                InputStream inputStream = Resources.getResourceAsStream(resource);
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            } catch (IOException e) {
        
                e.printStackTrace();
            }
        }
        public static SqlSession getSqlSession(){
        
            return sqlSessionFactory.openSession();
        }
    }

**6、编写测试类**

import com.atguigu.mybatis.Dao.StudentDao;
    import com.atguigu.mybatis.Entry.Student;
    import com.atguigu.mybatis.Util.MybatisUtil;
    import org.apache.ibatis.annotations.Param;
    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 org.junit.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class Testdemo1 {
        
        @Test
        public void testSelect() throws IOException {
        
            SqlSession session= MybatisUtil.getSqlSession();
            List<Student> list = session.selectList("com.atguigu.mybatis.Dao.StudentDao.selectAllStudent");
            System.out.println(list);
            //释放资源
            session.close();
        }
    
        @Test
        public void testSelectById(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.selectById";
            StudentDao studentDao=sqlSession.getMapper( StudentDao.class);
            Student student=studentDao.selectById("1");
            System.out.println(student);
        }
    
        @Test
        public void testInsert(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.insertStudent";
            Student student=new Student();
            student.setId("4");
            student.setName("zhangfei");
            student.setPhone("1435246357");
            student.setAddress("shuguo");
            int num=sqlSession.insert(sqlId,student);
            sqlSession.commit();
            sqlSession.close();
        }
    
        @Test
        public void testUpdate(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.updateStudent";
            Student student=new Student();
            student.setId("4");
            student.setName("guanyu");
            student.setPhone("4675");
            student.setAddress("shuguo");
            int num=sqlSession.insert(sqlId,student);
            sqlSession.commit();
            sqlSession.close();
        }
    
        @Test
        public void testDelete(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.deleteStudentById";
            int num=sqlSession.insert(sqlId,"4");
            sqlSession.commit();
            sqlSession.close();
        }
    }

**7、目录结构：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70]

## 三、模糊查询 ##

public List<Student> selectLike(String name);

<select id="selectLike" resultType="com.atguigu.mybatis.Entry.Student">
            select id,name,address,phone from student where name like  #{name}
        </select>

@Test
        public void testLike(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.selectLike";
            List<Student> students=sqlSession.selectList(sqlId,"%z%");
            System.out.println(students);
            sqlSession.commit();
            sqlSession.close();
        }

## **四、利用Map映射进行查询** ##

public List<Student> selectMap(Map<String,Object> map);

<select id="selectMap" resultType="com.atguigu.mybatis.Entry.Student">
            select id,name,address,phone from student where name like  #{name}
        </select>

@Test
        public void testMap(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.selectLike";
            Map<String,Object> map=new HashMap<>();
            map.put("name","%z%");
            List<Student> students=sqlSession.selectList(sqlId,map);
            System.out.println(students);
            sqlSession.commit();
            sqlSession.close();
        }

## **五、动态sql** ##

**1、标签**:如果if标签里的test条件不满足，则其标签体内部语句不会执行

public List<Student> selectIf(Student student);

<select id="selectIf" resultType="com.atguigu.mybatis.Entry.Student">
            select id,name,address,phone from student where
            <if test="name!=null">
                name=#{name}
            </if>
            <if test="id!=null">
                id=#{id}
            </if>
        </select>

@Test
        public void testIf(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.selectIf";
            StudentDao studentDao=sqlSession.getMapper(StudentDao.class);
            Student student=new Student();
            student.setId("1");
        //    student.setName("");
            student.setPhone("4675");
            student.setAddress("shuguo");
            List<Student> students=studentDao.selectIf(student);
            System.out.println(students);
            sqlSession.commit();
            sqlSession.close();
        }

**2、<where>标签**:这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

public Student selectWhere(Student student);

<select id="selectWhere" resultType="com.atguigu.mybatis.Entry.Student">
            select id,name,address,phone from student
            <where>
                <if test="name!=null">
                    name=#{name}
                </if>
                <if test="id!=null">
                    and id=#{id}
                </if>
            </where>
        </select>

@Test
        public void testWhere(){
        
            SqlSession sqlSession=MybatisUtil.getSqlSession();
            String sqlId="com.atguigu.mybatis.Dao.StudentDao.selectIf";
            StudentDao studentDao=sqlSession.getMapper(StudentDao.class);
            Student student=new Student();
            student.setId("1");
          //  student.setName("");
            student.setPhone("4675");
            student.setAddress("shuguo");
            Student students=studentDao.selectWhere(student);
            System.out.println(students);
            sqlSession.commit();
            sqlSession.close();
        }

此时，若name为空，则where语句会自动将id前的and符号删去，若name与id均为空，则where也会一同被删去。

**3、<set>标签**

<update id="updateUserById" parameterType="com.ys.po.User">
        update user u
            <set>
                <if test="username != null and username != ''">
                    u.username = #{username},
                </if>
                <if test="sex != null and sex != ''">
                    u.sex = #{sex}
                </if>
            </set>
         where id=#{id}
    </update>

**4、<choose>标签（when与otherwise）**

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
          select * from user
          <where>
              <choose>
                  <when test="id !='' and id != null">
                      id=#{id}
                  </when>
                  <when test="username !='' and username != null">
                      and username=#{username}
                  </when>
                  <otherwise>
                      and sex=#{sex}
                  </otherwise>
              </choose>
          </where>
      </select>

5、**<foreach>** 标签

<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
        select * from user
        <where>
            
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70]: /images/20221022/6d2c584afb3a4660aebbaee97b03fdba.png