Mybatis

1、搭建环境

1.1新建maven项目

1.2.删除src文件夹

1.3 导入依赖

<dependencies>
    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.2</version>
    </dependency>

    <!--mybatis-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>

    <!--junit-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

1.4 创建模块

• 编写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>
    <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/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="org/mybatis/example/BlogMapper.xml"/>
    </mappers>
</configuration>

• 编写mybatis工具类mybatisUtil

package dao;

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;

//sqlSessionFactory
public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        //获取sqlSessionFactory对象
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);


    }

    //获取sqlSession对象
    public static SqlSession getSqlSessionFactory() {
        return sqlSessionFactory.openSession();
    }


}

1.5编写代码

• 实体类

  package pojo;
  
  import lombok.Data;
  
  @Data
  public class User {
      private int id;
      private String name;
      private String pwd;
  }
  

• Dao层接口

  package com.dao;
  
  import com.pojo.User;
  
  import java.util.List;
  
  public interface UserDao {
      //获取用户信息
      List<User> getUserList();
  }
  

• 接口实现类

  <?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 命名空间 绑定接口地址-->
  <mapper namespace="com.dao.UserDao">
  <!--id 接口中的方法-->
  <!--resultType 返回的类型-->
      <select id="getUserList" resultType="com.pojo.User">
      select * from mybatis.user
    </select>
  </mapper>
  

• 测试类

  import com.dao.UserDao;
  import com.pojo.User;
  import com.utils.MybatisUtil;
  import org.apache.ibatis.session.SqlSession;
  import org.junit.Test;
  
  import java.util.List;
  
  public class UserDaoTest {
  
  
      @Test
      public void test(){
          //第一步 获得SqlSession对象
          SqlSession sqlSession = MybatisUtil.getSqlSessionFactory();
  
          //执行sql
          UserDao userDao = sqlSession.getMapper(UserDao.class);
  
          List<User> userList = userDao.getUserList();
  
          for (User user : userList) {
              System.out.println(user);
          }
  
      }
  }
  

注册配置文件(mapper文件不再原来的resources文件夹下)

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
                <include>**/*.properties</include>
            </includes>
        </resource>
    </resources>
</build>

2、CRUD

2.1 namespace

namespace中的包名要与对应接口包名保持一致、

2.2 select

选择，查询语句；

• id: 就是对应namespace中的方法名
• resultType: sql语句执行的返回值！
• paramterType: 参数类型

<select id="getUserInfoById" resultType="com.pojo.User" parameterType="int">
    select * from mybatis.user where id = #{id}
</select>

2.3 insert

<!--对象参数中的属性可以直接使用-->
<insert id="addUser" parameterType="com.pojo.User">
    insert into mybatis.user(id, name, pwd) value (#{id},#{name},#{pwd})
</insert>

2.4 delete

<delete id="delUserInfo" parameterType="int">
    delete from mybatis.user where id=#{id}
</delete>

2.5 update

<update id="setUser" parameterType="com.pojo.User">
    update mybatis.user set name = #{name} where id = #{id}
</update>

2.6模糊查询

（传参时候带通配符“%parm%”或者xml字符串拼接）

<select id="getUserInfoById" resultType="com.pojo.User" parameterType="int">
    select * from mybatis.user where id like "%"#{id}"%"
</select>

注意：增删改需要提交事务

2.7 多对一（可以理解成一对一）这里是有一个对象处理

建表

 CREATE TABLE `teacher`(
	`id` INT(10) NOT NULL,
	`name` VARCHAR(30) DEFAULT NULL,
	PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8
 
 
 
INSERT INTO teacher(`id`,`name`) VALUE (1,'刘老师') 
 
  CREATE TABLE `student`(
	`id` INT(10) NOT NULL,
	`name` VARCHAR(30) DEFAULT NULL,
	`tid` INT(10) DEFAULT NULL,
	PRIMARY KEY(`id`),
	KEY `fktid`(`tid`),
	CONSTRAINT `fktid` FOREIGN KEY(`tid`) REFERENCES `teacher` (`id`) 
 )ENGINE=INNODB DEFAULT CHARSET=utf8
 
INSERT INTO student(`id`,`name`,`tid`) VALUE (2,'小王',1);
INSERT INTO student(`id`,`name`,`tid`) VALUE (3,'小二',1);
INSERT INTO student(`id`,`name`,`tid`) VALUE (4,'熊大',1);
INSERT INTO student(`id`,`name`,`tid`) VALUE (5,'西西',1);
INSERT INTO student(`id`,`name`,`tid`) VALUE (6,'弟弟',1); 
 

按照查询嵌套查询

association ：对象

collection ：集合

先查出student 通过tid 嵌套查询teacher信息。最终结果放到resultMap中返回。

    <select id="getStudentInfo" resultMap="studentTeacher">
        select * from student
    </select>
    <resultMap id="studentTeacher" type="com.pojo.Student">
        <result property="id" column="id"/>
        <result property="name" column="id"/>
        <association property="teacher" column="tid" javaType="com.pojo.Teacher" select="getTeacherInfo"/>
    </resultMap>

    <select id="getTeacherInfo" resultType="com.pojo.Teacher">
        select * from teacher where id = #{tid}
    </select>

按照结果嵌套查询

<select id="getStudentInfo2" resultMap="studentTeacher2">
    select a.id as sid,
    a.name as sname,
    b.name as tname
    from student a,teacher b
    where a.tid = b.id
</select>

<resultMap id="studentTeacher2" type="com.pojo.Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="com.pojo.Teacher">
        <result property="name" column="tname" />
    </association>
</resultMap>

2.7 一对多查询(一个老师对应多个学生)

javaType: java类型

ofType:泛型类型

这里用的是结果查询 对于单个数据进行封装。所以用不到javaType直接写泛型就好。

要是用的子查询 是针对list进行封装 所以要加上javatype=“ArrayList”

<select id="getTeacherInfo" resultMap="teacherStudents">
    select
    a.id as sid,
    a.name as sname,
    b.name as tname,
    b.id as tid
    from student a,teacher b
    where a.tid = b.id
    and b.id = #{id}
</select>

<resultMap id="teacherStudents" type = "com.pojo.Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="com.pojo.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
    </collection>
</resultMap>

3、动态SQL

根据不同的条件生成不同的sql语句

where 1= 1 可以换成where标签

3.1 建表

 CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

if 标签

  <select id="getOne" resultType="com.pojo.Blog" parameterType="map">
        select * from mybatis.blog where 1=1
        <if test="title != null">
            AND title like "%"#{title}"%"
        </if>
        <if test="author != null">
            AND author = #{author}
        </if>
    </select>

    @Test
    public void test9() {

        HashMap map = new HashMap<String,String>();
        map.put("title","哈");
        map.put("author","刘某人");
        List<Blog> blogs = blogMapper.getOne(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

choose标签

可以理解成java中的switch when->case,break->otherwise

  <select id="getOne" resultType="com.pojo.Blog" parameterType="map">
        select * from mybatis.blog where 1=1
      <choose>
        <when test="title != null">
            AND title like "%"#{title}"%"
        </when>
        <when test="author != null">
            AND author = #{author}
        </when>
        <otherwise>
            and view=#{view}
        </otherwise>
      </choose>
    </select>

set标签

别漏了逗号

<update id="updateBlog" parameterType="map">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>