Mybatis学习

GEK1

已于 2022-02-15 15:23:44 修改

阅读量2.1k

点赞数 1

文章标签： java maven mybatis

于 2022-02-12 16:46:08 首次发布

本文链接：https://blog.csdn.net/yaoxie1534/article/details/122808521

版权

Mybatis配置步骤

总体配置目录结构

在resources下面新建mybatis-config.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>
    <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://127.0.0.1:3306/test?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/gek/dao/UserMapper.xml" />
    </mappers>
</configuration>

在pojo 包下新建User实体类

package com.gek.pojo;

public class User {
        private int id;
        private  String name;
        private int age;

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

    public User() {
    }

    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 int getAge() {
        return age;
    }

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

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

在dao包下，新建UserDao以及UserMapper.xml

package com.gek.dao;

import com.gek.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 引用userDao路径， id引用UserDao的实现方法  resultType引用User实体类路径
<mapper namespace="com.gek.dao.UserDao">
    <select id="getUserList" resultType="com.gek.pojo.User">
        select * from  test.user;
    </select>
</mapper>

在utild包下，新建MybatisUtils工厂类

package com.gek.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.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 (Exception e) {
           e.printStackTrace();
       }
   }
   public  static SqlSession getSqlSession(){
       return  sqlSessionFactory.openSession();
   }
}

在pom.xml中加入build 来防止资源导出失败的问题

<!--    在build中配置resources,来防止我们资源导出失败的问题-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>
                        **/*.properties
                    </include>
                    <include>
                        **/*.xml
                    </include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>
                        **/*.properties
                    </include>
                    <include>
                        **/*.xml
                    </include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

构建测试类，测试mybatis是否配置成功

package com.gek.dao;

import com.gek.pojo.User;
import com.gek.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserDaoTest {
    @Test
    public void  test(){
//        获得sqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
//       执行SQL
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();

    }
}

输出对应数据库数据

CRUD

namespace
namespace中的包名要和Dao/Mapper接口的包名一致
select
选择，查询语句
id:就是对应的namespace中的方法名
resultType:Sql语句执行的返回值
parameterType：参数类型

代码编写步骤
1:在userDao中定义相关方法
2.在userMapper.xml中编写select语句，需要填写id（对应的方法名），resultType(对应的返回值类型)，parameterType(对应的参数类型)
3.编写测试方法

update,delete,insert的步骤雷同，可以看代码示例如下

package com.gek.dao;

import com.gek.pojo.User;

import java.util.List;

public interface UserDao {
//    模糊查询
    List<User> getUserLike(String name);
//    查询全部用户
    List<User> getUserList();
//    根据ID查询用户
    User getUserById(int id);
//    insert一个用户
    int addUser(User user);
//    更新一个用户
    int updateUser(User user);
//    删除一个用户
    int deleteUser(int id);
}

<?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.gek.dao.UserDao">
    <select id="getUserList" resultType="com.gek.pojo.User">
        select * from  test.user;
    </select>
    <select id="getUserById" resultType="com.gek.pojo.User" parameterType="int">
        select * from test.user where id=#{id}
    </select>
    <insert id="addUser" parameterType="com.gek.pojo.User">
        insert  into test.user (id,name,age) values (#{id},#{name},#{age})
    </insert>
    <update id="updateUser" parameterType="com.gek.pojo.User">
        update test.user set name=#{name},age=#{age} where id=#{id}
    </update>
    <delete id="deleteUser" parameterType="int">
        delete  from test.user where id=#{id}
    </delete>
    <select id="getUserLike" resultType="com.gek.pojo.User">
        select  * from test.user where name like "%"#{value}"%"
    </select>
</mapper>

package com.gek.dao;

import com.gek.pojo.User;
import com.gek.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserDaoTest {
    @Test
    public void  test(){
        //        获得sqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try {
//       执行SQL
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            List<User> userList = userDao.getUserList();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }

    }
    @Test
    public  void  getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try {
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            User userById = mapper.getUserById(1);
            System.out.println(userById);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    public  void addUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try {
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            int gek2222 = mapper.addUser(new User(8, "gek2222", 222));
            //提交事务
            sqlSession.commit();
            System.out.println(gek2222);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    public  void  updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        try {
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            mapper.updateUser(new User(8, "哈哈", 11));
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    public  void  deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        try {
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            mapper.deleteUser(8);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    public  void  getUserLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        try {
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            List<User> userLike = mapper.getUserLike("gek");
            for (User user : userLike) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
}

配置解析

Environments

default 表示选择的是哪个enviroment ,本文中选择了development，表示选择的是第一个development 环境

 <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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>
        <environment id="test">
            <transactionManager type="JDBC"/>
            <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>

properites

可以直接引入外部文件
可以在其中增加一些属性配置
如果两个文件有同一个字段，优先使用外部配置文件

 <properties resource="db.properties">
 <property name="username" value="dev_user"/>
  <property name="password" value="F2Fa3!33TYyg"/>
</properties>

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=root

类型别名（typeAliases）
类型别名可为 Java 类型设置一个缩写名字。它仅用于 XML 配置，意在降低冗余的全限定类名书写。例如

   <typeAliases>
        <typeAlias alias="User" type="com.gek.pojo.User"/>
    </typeAliases>

<select id="getUserList" resultType="User">
        select * from  test.user;
</select>

也可以指定一个包名，MyBatis 会在包名下面搜索需要的 Java Bean，比如：
扫描实体类的包，他的默认别名就是这个类的类名，首字母小写

    <typeAliases>
<!--        <typeAlias alias="User" type="com.gek.pojo.User"/>-->
        <package name="com.gek.pojo"/>
    </typeAliases>

指定包名的情况下，要改别名需要在实体类上加注解

@Alias("author")
public class Author {
    ...
}

设置

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

映射器

MapperRegistry:注册绑定我们的Mapper文件

方式1：使用相对于类路径的资源引用

<!-- 使用相对于类路径的资源引用 -->
   <mappers>
        <mapper resource="com/gek/dao/UserMapper.xml" />
    </mappers>

方式2和方式3实现的前提条件：
1：接口和他的Mapper配置文件必须同名
2：接口和她的Mapper配置文件必须在同一个包下

方式2：使用映射器接口实现类的完全限定类名

    <mappers>
<!--        <mapper resource="com/gek/dao/UserMapper.xml" />-->
        <mapper class="com.gek.dao.UserMapper" />
    </mappers>

方式3：将包内的映射器接口实现全部注册为映射器

    <mappers>
<!--        <mapper resource="com/gek/dao/UserMapper.xml" />-->
<!--        <mapper class="com.gek.dao.UserMapper" />-->
        <package name="com.gek.dao"/>
    </mappers>

生命周期和作用域

SqlSessionFactoryBuilder

这个类可以被实例化、使用和丢弃，一旦创建了 SqlSessionFactory，就不再需要它了。因此 SqlSessionFactoryBuilder 实例的最佳作用域是方法作用域（也就是局部方法变量）。你可以重用 SqlSessionFactoryBuilder 来创建多个 SqlSessionFactory 实例，但最好还是不要一直保留着它，以保证所有的 XML 解析资源可以被释放给更重要的事情。

SqlSessionFactory

SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在，没有任何理由丢弃它或重新创建另一个实例。使用 SqlSessionFactory 的最佳实践是在应用运行期间不要重复创建多次，多次重建 SqlSessionFactory 被视为一种代码“坏习惯”。因此 SqlSessionFactory 的最佳作用域是应用作用域。有很多方法可以做到，最简单的就是使用单例模式或者静态单例模式。

SqlSession

每个线程都应该有它自己的 SqlSession 实例。SqlSession 的实例不是线程安全的，因此是不能被共享的，所以它的最佳的作用域是请求或方法作用域。绝对不能将 SqlSession 实例的引用放在一个类的静态域，甚至一个类的实例变量也不行。也绝不能将 SqlSession 实例的引用放在任何类型的托管作用域中，比如 Servlet 框架中的 HttpSession。如果你现在正在使用一种 Web 框架，考虑将 SqlSession 放在一个和 HTTP 请求相似的作用域中。换句话说，每次收到 HTTP 请求，就可以打开一个 S

在这里插入图片描述

处理数据库字段和实体类名字不一致的情况

ResultMap
下面的示例，数据库字段是name,实体类定义的是uname

id name age
id uname age

  <resultMap id="UserMap" type="User">
        <result column="id" property="id"/>
        <result column="name" property="uname"/>
        <result column="age" property="age"/>
    </resultMap>
    <select id="getUserList" resultMap="UserMap">
        select * from  test.user;
    </select>

6、日志

在这里插入图片描述

SLF4J
LOG4J 【掌握】
LOG4J2
JDK_LOGGING
COMMONS_LOGGING
STDOUT_LOGGING 【掌握】
NO_LOGGING

在Mybatis中具体使用那个一日志实现，在设置中设定！
STDOUT_LOGGING标准日志输出！
在mybatis核心配置文件中，配置我们的日志！

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

在这里插入图片描述

6.2、Log4j

什么是Log4j？

Log4j是Apache的一个开源项目，通过使用Log4j，我们可以控制日志信息输送的目的地是控制台、文件、GUI组件
我们也可以控制每一条日志的输出格式；
通过定义每一条日志信息的级别，我们能够更加细致地控制日志的生成过程。
通过一个配置文件来灵活地进行配置，而不需要修改应用的代码。

1、先导入log4j的包
在这里插入图片描述

    <dependencies>
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

2、新建log4j.properties并配置
在这里插入图片描述

#将等级为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/gek.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

3、配置log4j为日志的实现

   <settings>
<!--        标准的日志工厂实现-->
<!--        <setting name="logImpl" value="STDOUT_LOGGING"/>-->
<!--        log4g-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>

测试运行
在这里插入图片描述

分页

使用limit分页
语法： SELECT * from user limit startIndex,pageSize;

1、接口
在这里插入图片描述

//    分页
    List<User> getUserLimit(Map<String,Integer> map);

2.UserMapper.xml

    <resultMap id="UserMap" type="User">
        <result column="id" property="id"/>
        <result column="name" property="uname"/>
        <result column="age" property="age"/>
    </resultMap>
    <select id="getUserLimit" parameterType="map" resultMap="UserMap">
        select * from  test.user limit #{startIndex},#{pageSize}
    </select>

3.测试方法

   @Test
   public  void  getUserLimit(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       try {
           UserMapper mapper = sqlSession.getMapper(UserMapper.class);
           HashMap<String, Integer> map = new HashMap<>();
           map.put("startIndex",0);
           map.put("pageSize",3);
           List<User> userLimit = mapper.getUserLimit(map);
           for (User user : userLimit) {
               System.out.println(user);
           }
       } catch (Exception e) {
           e.printStackTrace();
       } finally {
           sqlSession.close();
       }
   }

4.输出
在这里插入图片描述

7.2、RowBounds分页

不再使用SQL实现分页。

1.接口

    // 分页2
    List<User> getUserByRowBounds();

2.UserMapper.xml

   <!-- 分页2 -->
    <select id="getUserByRowBounds" resultMap="userMap">
        select * from  mybatis.user
    </select>

3.测试

    @Test
    public void getUserByRowBounds(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

//      RowBounds实现
        RowBounds rowBounds = new RowBounds(1, 2);

//      通过Java代码层面实现分页
        List<User> userList = sqlSession.selectList("com.github.subei.dao.UserMapper.getUserByRowBounds",null,rowBounds);

        for (User user : userList) {
            System.out.println(user);
        }

        sqlSession.close();
    }

7.3、分页插件

地址：https://mybatis.io/

8、使用注解开发

1.注解在接口上实现

@Select("select * from user")
List<User> getUsers();

2.需要在核心配置文件中绑定接口！

   <!--绑定接口-->
    <mappers>
        <mapper class="com.gek.dao.UserMapper"/>
    </mappers>

3.测试

package com.github.subei.test;

import com.github.subei.dao.UserMapper;
import com.github.subei.pojo.User;
import com.github.subei.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> users = mapper.getUsers();
        for(User user : users){
            System.out.println(user);
        }

        sqlSession.close();
    }
}

多对一处理

在这里插入图片描述

SQL

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` )
VALUES
	( 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` )
VALUES
	( '1', '小明', '1' );
	
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
	( '2', '小红', '1' );
	
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
	( '3', '小张', '1' );
	
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
	( '4', '小李', '1' );
	
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
	( '5', '小王', '1' );

按照查询嵌套处理

StudentMapper接口

package com.gek.dao;

import com.gek.pojo.Student;

import java.util.List;

public interface StudentMapper {
    //查询所有的学生信息，以及对应的老师信息
      List<Student> findAllStudent();
}

StudentMapper.xml文件

 <select id="findAllStudent" resultMap="StudentTeacher">
        select * from student;
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--        复杂的属性，我们需要单独处理
         对象 ：association
         集合：collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from  teacher where id = #{id}
    </select>

测试类

  @Test
    public  void  findAllStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> allStudent = mapper.findAllStudent();
        for (Student student : allStudent) {
            System.out.println(student);
        }

        sqlSession.close();
    }

输出结果

按照结果嵌套查询

就是修改一下StudentMapper.xml文件

  <select id="findAllStudent2" resultMap="StudentTeacher2">
        select s.name sname,s.id sid,t.name tname
        from  student s,teacher t where s.tid=t.id;
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="name" column="sname"/>
        <result property="id" column="sid"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

一对多处理(按照结果嵌套)

实体类

package com.gek.pojo;


import lombok.Data;

@Data
public class Student {
    private  int id;
    private  String name;
    private  int tid;
//    private  Teacher teacher;
}

package com.gek.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private  int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> students;
}

TeacherMapper接口

package com.gek.dao;

import com.gek.pojo.Student;
import com.gek.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TeacherMapper {
    //获取老师
    Teacher getTeacherById(@Param("tid") int id);
}

编写TeacherMapper.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.gek.dao.TeacherMapper">
    <select id="getTeacherById" resultMap="StudentTeacher">
        select  s.name sname,s.id sid,t.name tname,t.id tid
        from  student s,teacher t where tid=#{tid} and t.id =s.tid;
    </select>
    <resultMap id="StudentTeacher" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
          <!--复杂的属性，需要单独处理
        对象： association 集合： collection
        javaType="" 指定属性的类型！
        集合中的泛型信息，我们使用ofType获取
        -->
        <collection property="students" ofType="Student">
            <result property="name" column="sname"/>
             <result property="id" column="sid"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

测试类

   @Test
   public  void  getTeacherById(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
       Teacher teacherById = mapper.getTeacherById(1);
       System.out.println(teacherById);
       sqlSession.close();
   }

输出

子查询的方式查询

package com.gek.dao;

import com.gek.pojo.Student;
import com.gek.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TeacherMapper {
    Teacher getTeacherById2(@Param("tid") int id);
}

!--    子查询方式-->
    <select id="getTeacherById2" resultMap="StudentTeacher2">
        select * from teacher;
    </select>
<!--    resultMap 上的type标识的是实体类的路径，因为这边取了别名所以可以直接用Teacher-->
    <resultMap id="StudentTeacher2" type="Teacher">
    			//javatype表示实体类返回值类型，oftype表示对应实体类类名 property对应属性名字
            <collection property="students" javaType="ArrayList" ofType="Student" select="GetStudent" column="id"/>
    </resultMap>
    <select id="GetStudent" resultType="Student">
        select * from student where tid= #{tid};
    </select>

  @Test
   public  void  getTeacherById2(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
       Teacher teacherById2 = mapper.getTeacherById2(1);
       System.out.println(teacherById2);
       sqlSession.close();
   }

小结

关联-association [多对一]
集合-collection[一对多]
javaType & oftype
javaType 用来指定实体类中属性的类型
oftype 用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

注意点

保证SQL的可读性，尽量保证通俗易懂！
注意一对多和多对一中，属性名和字段的问题！
如果问题不好排查错误，可以使用日志，建议使用 Log4j！

动态 SQL

定义：根据不同的条件执行不同的sql

IF

接口文件

//查询博客
    List<Blog> queryBlog(Map map);

xml文件

  <select id="queryBlog" parameterType="Map" resultType="blog">
        select  * from  blog
        <where>
            <if test="title != null">
                title= #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>

    </select>

  @Test
    public void  queryBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        hashMap.put("title","gektest");
        List<Blog> blogs = mapper.queryBlog(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

choose (when otherwise)

//    chooseWhen
    List<Blog> queryChoose(Map map);

    <select id="queryChoose" parameterType="Map" resultType="blog">
        select * from blog
     <where>
        <choose>
            <when test="title !=null">
                title=#{title}
            </when>
            <when test="author !=null">
                and author =#{author}
            </when>
            <otherwise>
                and views =#{views}
            </otherwise>
        </choose>
     </where>
    </select>

  @Test
    public void  queryChoose(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
//        hashMap.put("title","gektest");
        hashMap.put("views",99999);
        List<Blog> blogs = mapper.queryChoose(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

trim (where,set)

select * from mybatis.blog
<where>
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</where>

<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>

sql片段

上面的sql定义好id,下面需要用到这个片段的，用include标签 refid填写上面定义的id

<!--    sql片段-->
    <sql id="if-title-author">
        <if test="title != null">
            title= #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
    <select id="queryBlog" parameterType="Map" resultType="blog">
        select  * from  blog
        <where>
            <include refid="if-title-author"></include>
        </where>

    </select>

注意事项：

最好基于单表来定义SQL片段！
不要存在where标签

Foreach

BlogMapper

//    foreach练习
    List<Blog> queryBlogForeach(Map map);

BlogMapper.xml

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

测试方法

 @Test
    public  void queryBlogForeach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap hashMap = new HashMap();
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(1);
        integers.add(2);
        integers.add(3);
        hashMap.put("ids",integers);
        System.out.println(hashMap);
        List<Blog> blogs = mapper.queryBlogForeach(hashMap);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

GEK1

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mybatis学习

Mybatis配置步骤总体配置目录结构在resources下面新建mybatis-config.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"><configur
复制链接

扫一扫