MyBatis笔记

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.by</groupId>
    <artifactId>MyBatis</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>pom</packaging>
    <modules>
        <module>proxy</module>
        <module>s2_MyBatis_Hello</module>
    </modules>

    <properties>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
    <build>
        <!-- 如果不添加此节点src/main/java目录下的所有配置文件都会被漏掉。 -->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
    </build>

</project>

2.2、配置log4j.properties

# Global logging configuration
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

2.3、创建一个实体类User

package com.by.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private  String address;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

2.4、创建UserMapper接口

建一个mapper包，在包下创建一个UserMapper接口，接口中写入一个查询方法

package com.by.mapper;

import com.by.pojo.User;

import java.util.List;

public interface UserMapper {
    public List<User> findAll();
}

2.5、创建UserMapper.xml（映射文件）

我们可以将UserMapper.xml先创建在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:引入命名空间，识别某个Mapper接口下的操作
   id：Mapper接口的方法名
   resultType:Mapper接口方法的返回类型
-->
<mapper namespace="com.by.mapper.UserMapper">
    <select id="findAll" resultType="com.by.pojo.User">
        select * from user;
    </select>
</mapper>

2.6、配置mybatis-config.xml映射文件

在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">//引入config约束
<configuration>
      <!--默认操作dev-->
    <environments default="dev">
         <!--dev为开发环境-->
        <environment id="dev">
            <!--配置事务的类型:type="JDBC | MANAGED" 两种方式
                 JDBC:表示使用JDBC中原生的事务管理方式
                 MANAGED:被管理，例如Spring-->
            <transactionManager type="JDBC"></transactionManager>
              <!--配置数据库连接信息，用的是数据源（连接池）-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/whn1?useSSL=false&amp;serverTimezone=Asia/Shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
           <!--告知mybatis映射文件位置-->
        <mapper resource="com/by/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

2.7、测试

我们可以在src/test/java下创建一个test包，用于我们对配置好的mybatis进行测试

package com.by.test;

import com.by.mapper.UserMapper;
import com.by.pojo.User;
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 MyBatisTest {
    @Test
    public void findAll() throws IOException {
       //加载配置文件
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        //创建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
        //获取会话实例
        SqlSession session = sqlSessionFactory.openSession();
        //通过session.getMapper调用代理得到userMapper
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> userList = userMapper.findAll();
        for ( User user:userList) {
            System.out.println(user);
        }
    }
}

测试结果

三、自定义MyBatis--了解（MyBatis运行原理）

3.1、MyBatis运行原理

在上一章节测试中，我们引入了几个对象以保证mybatis可以正常通过测试，而这些对象也是mybatis运行时需要经历的几个步骤。我们可以通过写入自定义对象以了解其中的运行原理。

3.2、添加utils工具类

我们需要添加三个工具类：

1.XMLConfigBuilder：解析XML配置文件

package com.by.utils;

import com.by.cfg.Configuration;
import com.by.io.Resources;
import com.by.mapping.MappedStatement;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *  用于解析配置文件
 */
public class XMLConfigBuilder {



    /**
     * 解析主配置文件，把里面的内容填充到DefaultSqlSession所需要的地方
     * 使用的技术：
     *      dom4j+xpath
     */
    public static Configuration loadConfiguration(InputStream config){
        try{
            //定义封装连接信息的配置对象（mybatis的配置对象）
            Configuration cfg = new Configuration();

            //1.获取SAXReader对象
            SAXReader reader = new SAXReader();
            //2.根据字节输入流获取Document对象
            Document document = reader.read(config);
            //3.获取根节点
            Element root = document.getRootElement();
            //4.使用xpath中选择指定节点的方式，获取所有property节点
            List<Element> propertyElements = root.selectNodes("//property");
            //5.遍历节点
            for(Element propertyElement : propertyElements){
                //判断节点是连接数据库的哪部分信息
                //取出name属性的值
                String name = propertyElement.attributeValue("name");
                if("driver".equals(name)){
                    //表示驱动
                    //获取property标签value属性的值
                    String driver = propertyElement.attributeValue("value");
                    cfg.setDriver(driver);
                }
                if("url".equals(name)){
                    //表示连接字符串
                    //获取property标签value属性的值
                    String url = propertyElement.attributeValue("value");
                    cfg.setUrl(url);
                }
                if("username".equals(name)){
                    //表示用户名
                    //获取property标签value属性的值
                    String username = propertyElement.attributeValue("value");
                    cfg.setUsername(username);
                }
                if("password".equals(name)){
                    //表示密码
                    //获取property标签value属性的值
                    String password = propertyElement.attributeValue("value");
                    cfg.setPassword(password);
                }
            }
            //取出mappers中的所有mapper标签，判断他们使用了resource还是class属性
            List<Element> mapperElements = root.selectNodes("//mappers/mapper");
            //遍历集合
            for(Element mapperElement : mapperElements){
                //判断mapperElement使用的是哪个属性
                Attribute attribute = mapperElement.attribute("resource");
                if(attribute != null){
                    System.out.println("使用的是XML");
                    //表示有resource属性，用的是XML
                    //取出属性的值
                    String mapperPath = attribute.getValue();//获取属性的值"com/by/dao/UserDao.xml"
                    //把映射配置文件的内容获取出来，封装成一个map
                    Map<String, MappedStatement> mappers = loadMapperConfiguration(mapperPath);
                    //给configuration中的mappers赋值
                    cfg.setMappers(mappers);
                }else{
                    System.out.println("使用的是注解");
                    //表示没有resource属性，用的是注解
                    //获取class属性的值
                    String daoClassPath = mapperElement.attributeValue("class");
                    //根据daoClassPath获取封装的必要信息
                    Map<String,MappedStatement> mappers = loadMapperAnnotation(daoClassPath);
                    //给configuration中的mappers赋值
                    cfg.setMappers(mappers);
                }
            }
            //返回Configuration
            return cfg;
        }catch(Exception e){
            throw new RuntimeException(e);
        }finally{
            try {
                config.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }

    }

    /**
     * 根据传入的参数，解析XML，并且封装到Map中
     * @param mapperPath    映射配置文件的位置
     * @return  map中包含了获取的唯一标识（key是由dao的全限定类名和方法名组成）
     *          以及执行所需的必要信息（value是一个Mapper对象，里面存放的是执行的SQL语句和要封装的实体类全限定类名）
     */
    private static Map<String,MappedStatement> loadMapperConfiguration(String mapperPath)throws IOException {
        InputStream in = null;
        try{
            //定义返回值对象
            Map<String,MappedStatement> mappers = new HashMap<String,MappedStatement>();
            //1.根据路径获取字节输入流
            in = Resources.getResourceAsStream(mapperPath);
            //2.根据字节输入流获取Document对象
            SAXReader reader = new SAXReader();
            Document document = reader.read(in);
            //3.获取根节点
            Element root = document.getRootElement();
            //4.获取根节点的namespace属性取值
            String namespace = root.attributeValue("namespace");//是组成map中key的部分
            //5.获取所有的select节点
            List<Element> selectElements = root.selectNodes("//select");
            //6.遍历select节点集合
            for(Element selectElement : selectElements){
                //取出id属性的值      组成map中key的部分
                String id = selectElement.attributeValue("id");
                //取出resultType属性的值  组成map中value的部分
                String resultType = selectElement.attributeValue("resultType");
                //取出文本内容            组成map中value的部分
                String queryString = selectElement.getText();
                //创建Key
                String key = namespace+"."+id;
                //创建Value
                MappedStatement mapper = new MappedStatement();
                mapper.setQueryString(queryString);
                mapper.setResultType(resultType);
                //把key和value存入mappers中
                mappers.put(key,mapper);
            }
            return mappers;
        }catch(Exception e){
            throw new RuntimeException(e);
        }finally{
            in.close();
        }
    }

    /**
     * 根据传入的参数，得到dao中所有被select注解标注的方法。
     * 根据方法名称和类名，以及方法上注解value属性的值，组成Mapper的必要信息
     * @param daoClassPath
     * @return
     */
    private static Map<String,MappedStatement> loadMapperAnnotation(String daoClassPath)throws Exception{
        //定义返回值对象
        Map<String,MappedStatement> mappers = new HashMap<String, MappedStatement>();

        //1.得到dao接口的字节码对象
        Class daoClass = Class.forName(daoClassPath);
        //2.得到dao接口中的方法数组
        Method[] methods = daoClass.getMethods();
        //3.遍历Method数组
        for(Method method : methods){
            //取出每一个方法，判断是否有select注解
           /* boolean isAnnotated = method.isAnnotationPresent(Select.class);
            if(isAnnotated){
                //创建Mapper对象
                Mapper mapper = new Mapper();
                //取出注解的value属性值
                Select selectAnno = method.getAnnotation(Select.class);
                String queryString = selectAnno.value();
                mapper.setQueryString(queryString);
                //获取当前方法的返回值，还要求必须带有泛型信息
                Type type = method.getGenericReturnType();//List<User>
                //判断type是不是参数化的类型
                if(type instanceof ParameterizedType){
                    //强转
                    ParameterizedType ptype = (ParameterizedType)type;
                    //得到参数化类型中的实际类型参数
                    Type[] types = ptype.getActualTypeArguments();
                    //取出第一个
                    Class domainClass = (Class)types[0];
                    //获取domainClass的类名
                    String resultType = domainClass.getName();
                    //给Mapper赋值
                    mapper.setResultType(resultType);
                }
                //组装key的信息
                //获取方法的名称
                String methodName = method.getName();
                String className = method.getDeclaringClass().getName();
                String key = className+"."+methodName;
                //给map赋值
                mappers.put(key,mapper);
            }*/
        }
        return mappers;
    }
}

2.DataSourceUtil:获取数据库连接对象

package com.by.utils;
import com.qf.cfg.Configuration;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * 用于创建数据源的工具类
 */
public class DataSourceUtil {

    /**
     * 用于获取一个连接
     * @param cfg
     * @return
     */
    public static Connection getConnection(Configuration cfg){
        try {
            Class.forName(cfg.getDriver());
            return DriverManager.getConnection(cfg.getUrl(), cfg.getUsername(), cfg.getPassword());
        }catch(Exception e){
            throw new RuntimeException(e);
        }
    }
}

3.Executor：执行SQL，封装我们想要的数据

package com.by.utils;

import com.by.mapping.MappedStatement;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * 负责执行SQL语句，并且封装结果集
 */
public class Executor {

    public <E> List<E> selectList(MappedStatement mappedStatement, Connection conn) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //1.取出mapper中的数据
            String queryString = mappedStatement.getQueryString();//select * from user
            String resultType = mappedStatement.getResultType();//com.by.pojo.User
            Class domainClass = Class.forName(resultType);
            //2.获取PreparedStatement对象
            pstm = conn.prepareStatement(queryString);
            //3.执行SQL语句，获取结果集
            rs = pstm.executeQuery();
            //4.封装结果集
            List<E> list = new ArrayList<E>();//定义返回值
            while(rs.next()) {
                //实例化要封装的实体类对象
                E obj = (E)domainClass.newInstance();

                //取出结果集的元信息：ResultSetMetaData
                ResultSetMetaData rsmd = rs.getMetaData();
                //取出总列数
                int columnCount = rsmd.getColumnCount();
                //遍历总列数
                for (int i = 1; i <= columnCount; i++) {
                    //获取每列的名称，列名的序号是从1开始的
                    String columnName = rsmd.getColumnName(i);
                    //根据得到列名，获取每列的值
                    Object columnValue = rs.getObject(columnName);
                    //给obj赋值
                    PropertyDescriptor pd = new PropertyDescriptor(columnName,domainClass);//要求：实体类的属性和数据库表的列名保持一种
                    获取该属性的set方法
                    Method writeMethod = pd.getWriteMethod();
                    //调用set方法
                    writeMethod.invoke(obj,columnValue);
                }
                //把赋好值的对象加入到集合中
                list.add(obj);
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(pstm,rs);
        }
    }


    private void release(PreparedStatement pstm,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }

        if(pstm != null){
            try {
                pstm.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }
}

3.3、Resources

在Resources类中我们可以通过类加载器加载配置文件，例如加载mybatis-config.xml，加载之后我们可以获取一个流。

package com.by.io;

import java.io.InputStream;

public class Resources {
    //根据文件名称，加载类路径下面的配置文件
    public static InputStream getResourceAsStream(String filePath){
           //加载配置文件，返回一个InputStream流
        return Resources.class.getClassLoader().getResourceAsStream(mybatis-config.xml);
    }
}

3.4、SqlSessionFactoryBuilder

这是SqlSessionFactoryBuilder类，里面有一个build方法，build方法里有传流的参数，通过XMLConfigBuilder.loadConfiguration(in)将流放进去，这样会创建一个Configuration对象。这就是

SqlSessionFactoryBuilder的作用。

package com.by.builder;

import com.by.cfg.Configuration;
import com.by.factory.DefaultSqlSessionFactory;
import com.by.factory.SqlSessionFactory;
import com.by.utils.XMLConfigBuilder;

import java.io.InputStream;

public class SqlSessionFactoryBuilder {

    /**
     * 构建SqlSessionFactory对象
     * @param in
     * @return
     */
    public SqlSessionFactory build(InputStream in){
        Configuration configuration = XMLConfigBuilder.loadConfiguration(in);
        return  new DefaultSqlSessionFactory(configuration);
    }
}

3.5、Configuration

配置类存储所有的配置信息

package com.by.cfg;

import com.by.mapping.MappedStatement;

import java.util.HashMap;
import java.util.Map;

public class Configuration {

    private String driver;
    private String url;
    private String username;
    private String password;
	
    /**
       Map<"com.by.dao.UserDao.findAll", MappedStatement>     
    */
    private Map<String, MappedStatement> mappers = new HashMap<String,MappedStatement>();

    public Map<String, MappedStatement> getMappers() {
        return mappers;
    }

    public void setMappers(Map<String, MappedStatement> mappers) {
        this.mappers.putAll(mappers);//此处需要使用追加的方式
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

3.6、MappedStatement

MappedStatement是用来封装sql语句和查询结果集

package com.by.mapping;

public class MappedStatement {

    private String queryString;//SQL
    private String resultType;//实体类的全限定类名

    public String getQueryString() {
        return queryString;
    }

    public void setQueryString(String queryString) {
        this.queryString = queryString;
    }

    public String getResultType() {
        return resultType;
    }

    public void setResultType(String resultType) {
        this.resultType = resultType;
    }
}

3.7、SqlSessionFactory

创建的configuration会传递给SqlSessionFactory这个工厂，SqlSessionFactory拿到configuration对象会创建一个SqlSession.

package com.by.factory;
public interface SqlSessionFactory {
    //获取SQLSession对象
    public SqlSession openSession();
}

package com.by.factory;

import com.by.cfg.Configuration;
import com.by.session.DefaultSqlSession;
import com.by.session.SqlSession;

public class DefaultSqlSessionFactory implements SqlSessionFactory {

    private Configuration cfg;

    public DefaultSqlSessionFactory(Configuration cfg) {
        this.cfg = cfg;
    }

    /**
     * 获取一个SqlSession对象
     * @return
     */
    @Override
    public SqlSession openSession() {
        return new DefaultSqlSession(cfg);
    }
}

3.8、SqlSession

通过SqlSessionFactory.openSession会创建一个Sqlsession会话，SqlSession会通过调用getMapper方法传递到ProxyFactory工厂，ProxyFactory通过反射获得MappedStatement(sql, resultType), conn。

public interface SqlSession {
    //获取代理对象
    public <T> T getMapper(Class<T> tClass);
     //释放资源
    void close();
}

package com.by.session;

import com.by.utils.DataSourceUtil;
import com.by.cfg.Configuration;

import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.SQLException;

public class DefaultSqlSession implements SqlSession {

    private Configuration cfg;
    private Connection conn;

    public DefaultSqlSession(Configuration cgf){
        this.cfg = cgf;
        this.conn = DataSourceUtil.getConnection(cfg);
    }


    /*
    * 创建代理对象
    */
    @Override
    public <T> T getMapper(Class<T> tClass) {
        /**
         * tClass.getClassLoader()：类加载器
         * new Class[]{tClass}：Class数组，让代理对象和被代理对象有相同的行为
         *  new ProxyFactory：调用真是角色，附加自己的操作
         */
        return (T) Proxy.newProxyInstance(tClass.getClassLoader(),
                new Class[]{tClass},
                new ProxyFactory(cfg.getMappers(),conn));
    }

    @Override
    public void close() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3.9、ProxyFactory

ProxyFactory会解析得到MappedStatement(sql, resultType), conn数据，并会调用 Executor工具类查询

package com.by.session;

import com.by.mapping.MappedStatement;
import com.by.utils.Executor;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.Map;

public class ProxyFactory implements InvocationHandler {

    private Map<String, MappedStatement> mappers;
    private Connection conn;
    public ProxyFactory(Map<String, MappedStatement> mappers, Connection conn){
       this.mappers = mappers;
       this.conn = conn;
    }
    //调用代理对象的任何方法，都会在这执行
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        //1.获取方法名
        String methodName = method.getName();
        //2.获取方法所在类的名称
        String className = method.getDeclaringClass().getName();
        //3.组合key
        String key = className+"."+methodName;
        //4.获取mappers中的Mapper对象
        MappedStatement mappedStatement = mappers.get(key);
        //5.判断是否有mapper
        if(mappedStatement == null){
            throw new IllegalArgumentException("传入的参数有误");
        }
        //6.调用工具类执行查询所有
        return new Executor().selectList(mappedStatement,conn);
    }
}

3.10、测试

//1.读取配置文件
    InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
    //2.创建SqlSessionFactory工厂
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    SqlSessionFactory factory = builder.build(in);
    System.out.println("-----" + factory);
    //3.使用工厂生产SqlSession对象
    SqlSession session = factory.openSession();
    //4.使用SqlSession创建Dao接口的代理对象
    UserDao userDao = session.getMapper(UserDao.class);
    //5.使用代理对象执行方法
    List<User> users = userDao.findAll();
    for(User user : users){
        System.out.println(user);
    }
    //6.释放资源
    session.close();
    in.close();

四、MyBatis基本的CRUD

4.1、查询

4.1.1、单个参数绑定

当查询语句需要一个传参条件时，#{value} value值是任意的

写入Mapper层

package com.by.mapper;
import com.by.pojo.User;

public interface UserMapper {
    User findByUserId(Integer id);
}

写Mapper.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.by.mapper.UserMapper">
<!--
    resultType:接口返回类型的全类名；
    #{id}:#{id}：它代表占位符，相当于原来 jdbc 部分所学的?，都是用于替换实际的数据。
-->
<select id="findByUserId" resultType="com.by.pojo.User">
    select * from user where id=#{id};<!--当参数只有一个时,#{值是任意的}-->
</select>
</mapper>

..测试

4.1.2、MyBatis传入多个参数（重点）

1.通过序号传参：#{arg0},#{arg1},将需要查询的参数条件按顺序写入。

Mapper层：比如通过id和username查询用户

package com.by.mapper;

import com.by.pojo.User;


public interface UserMapper {
    User findByIdAndUsername(Integer id,String username);}

Mapper.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.by.mapper.UserMapper">

    <select id="findByIdAndUsername" resultType="com.by.pojo.User">
        select * from user where id=#{arg0} and username=#{arg1};<!--参数需要按顺序书写#{arg0},#{arg1},#{arg2}-->
    </select>
</mapper>

2.通过注解绑定多个参数：(@Param("value") Integer id) 通过注解传递注解的#{value}值

Mapper层：

package com.by.mapper;

import com.by.pojo.User;
import org.apache.ibatis.annotations.Param;


public interface UserMapper {
      <!--("value")与查询的#{value}中value值要保持一致-->
    User findByIdAndUsername2(@Param("id") Integer id, @Param("username") String username);
}

Mapper.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.by.mapper.UserMapper">
     <!--查询的#{value}与("value")中的value值保持一致-->
    <select id="findByIdAndUsername2" resultType="com.by.pojo.User">
        select * from user where id=#{id} and username=#{username};
    </select>

3. 对象参数绑定：传入的参数为对象时查询条件为 #{对象的属性名}。

Mapper层：

package com.by.mapper;

import com.by.pojo.User;

public interface UserMapper {
    User findUser(User user);
}

Mapper.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.by.mapper.UserMapper">
      
    <select id="findUser" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
        select * from user where id=#{id} and username=#{username};<!--传参为#{对象的属性名}-->
    </select>
</mapper>

4.Map传参：传入的参数为Map,查询条件为#{map的key}

Mapper层:

package com.by.mapper;
import com.by.pojo.User;
import java.util.Map;

public interface UserMapper {

    User findUserByMap(Map<String,Object> map);
}

Mapper.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.by.mapper.UserMapper">

    <select id="findUserByMap" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
        select * from user where id=#{id} and username=#{username};<!--#{map的key}-->
    </select>
</mapper>

4.1.3、模糊查询

传递单个参数通过CONCAT('%',#{value},'%')或者'%${value}%'，使用${} 括号中只能是value。

Mapper层：

package com.by.mapper;
import com.by.pojo.User;
import java.util.List;

public interface UserMapper {

    List<User> findUserByName(String username);}

Mapper.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.by.mapper.UserMapper">

 <!--select * from user where username like CONCAT('%',#{username},'%');-->
    <select id="findUserByName" parameterType="string" resultType="com.by.pojo.User">
    select * from user where username like '%${value}%';<!--${}只能是value-->
    </select>
</mapper>

4.1.4、聚合函数查询

Mapper层：

package com.by.mapper;
import com.by.pojo.User;

public interface UserMapper {
   //聚合函数查询
public Integer getTotal();

}

Mapper.xml

<!--聚合函数查询-->
<select id="getTotal" resultType="int">
    SELECT COUNT(id) FROM user
</select>

4.1.5、sql注入

#{}可以防止sql注入，${}不可以

Mapper层：

package com.by.mapper;
import com.by.pojo.User;


public interface UserMapper {
    User login(User userParam);
}

Mapper.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.by.mapper.UserMapper">

    <select id="login" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
   <!-- select * from user where username =#{username} and password=#{password};-->
        select * from user where username ='${username}' and password='${password}';
    </select>
</mapper>

Test.java

package com.by.test;

import com.by.mapper.UserMapper;
import com.by.pojo.User;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;



public class MyBatisTest {
    private SqlSession session;
    private InputStream is;

    @Before
    public void createSqlSession() throws IOException{
        //加载配置文件
        is = Resources.getResourceAsStream("mybatis-config.xml");
        //创建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
        //获取会话实例
        session = sqlSessionFactory.openSession();
    }
    
@Test
    public void login() throws IOException{
        UserMapper userMapper=session.getMapper(UserMapper.class);
        User userParam = new User();
       <!--
        输入"' #"会把查询语句中#后面的内容注释掉
-->
        userParam.setUsername("张三丰' #");
        userParam.setPassword("123");
        User user = userMapper.login(userParam);
        System.out.println(user);

    }

    @After
    public void closeSqlSession() throws IOException{
        session.close();
        is.close();
    }
}

4.2、#{}和${}的区别

当传入单个类型参数：#{任意值}，${value},${}括号中的值只能是value。
类型转换：#{}可以进行自动类型转换，${}不可以
底层：#{}底层为PreparedStatement，${}底层为Statement
sql注入：#{}可以防止sql注入，${}不可以。

4.3、删除

Mapper:

     void deleteUserById(Integer id);

Mapper.xml:

<delete id="deleteUserById">
    delete from user where id=#{id}
</delete>

4.4、修改

Mapper:

void updateUser(User user);

Mapper.xml:

    <update id="updateUser"  parameterType="com.by.pojo.User" >
     update user set username=#{username},password=#{password},sex=#{sex},birthday=#{birthday},address=#{address}
        where id=#{id}
    </update>

4.5、添加

Mapper:

  void addUser(User user);

Mapper.xml:

 <insert id="addUser" useGeneratedKeys="true" keyProperty="id" parameterType="com.by.pojo.User">
        insert into user(username,password,sex,birthday,address)
        values (#{username},#{password},#{sex},#{birthday},#{address})
    </insert>

五、MyBatis的ORM映射

5.1、什么是ORM映射

MyBatis只能自动维护库表”列名“与”属性名“相同时的对应关系，二者不同时无法自动ORM，如下：

5.2、列的别名

在SQL语句中通过as为查询字段添加列别名来匹配该查询类的属性名：

Mapper：

package com.by.mapper;
import com.by.pojo.Role;
import java.util.List;

public interface RoleMapper {
    List<Role>  findRole();
}

Mapper.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.by.mapper.RoleMapper">
    <select id="findRole" resultType="com.by.pojo.Role">
        select role_name as roleName,
               role_desc as roleDesc
        from role;
    </select>
</mapper>

5.3、结果映射

使用ResultMap标签手动映射，解决实体字段和数据表字段不一致的问题。

Mapper:

    List<Role>  findRole();

Mapper.xml

  <!--
      id:与select查询标签返回值保持一致
      type：映射实体类全类名
-->    
<resultMap id="findRoleResultMap" type="com.by.pojo.Role">
          <!--
            property:实体属性名
            column:数据库表字段名
        -->
        <id property="id" column="id" />
        <result property="roleName" column="role_name" />
        <result property="roleDesc" column="role_desc"/>

    </resultMap>
    <select id="findRole" resultMap="findRoleResultMap">
        select * from role ;
    </select>

六、MyBatis的配置文件

6.1、properties标签

1.我们一般会把数据库配置信息定义在一个独立的配置文件里面，比如db.properties：

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/whn1?useSSL=false&serverTimezone=Asia/Shanghai
jdbc.username=root
jdbc.password=123456

2.在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>
      <!--引入properties配置文件-->      
    <properties resource="db.properties"></properties>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
     <!--使用${}占位符获取配置信息-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
  
</configuration>

6.2、typeAliases标签

我们可以通过typeAliases标签为实体类定义别名，提高书写效率，在mybatis-config.xml中引入typeAliases

<?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>
    
    <typeAliases>
        <!--批量起别名-->
        <package name="com.by.pojo"/>
    </typeAliases>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
        </environment>
    </environments>

</configuration>

使用别名

<?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.by.dao.UserDao">
    <!--使用别名 通过批量起别名引用时用小写-->
    <select id="findAll" resultType="user">
        select * from user
    </select>
</mapper>

6.3、Mappers标签

Mappers标签的作用是用来在核心配置文件里面引入映射文件，引入方式有如下两种：

使用mapper映射文件路径：

<mappers>
        <mapper resource="com/by/dao/UserDao.xml"/>
    </mappers>

使用mapper接口的包名批量引入：

    <mappers>
        <package name="com.by.dao"></package>
    </mappers>

七、MyBatis的关联查询

7.1、关联查询

1.实体间的关系（拥有、属于）

OneToOne:一对一关系（account ←→ user ）

OneToMany:一对多关系(user←→ account )

ManyToMany:多对多关系(user←→ role )

2.什么是关联查询

当访问关系的一方时，如果需要查看与之关联的另一方数据，则必须使用表连接查询，将查询到的另一方数据保存至本方属性中

3.关联查询语法：

指定“一方”关系时（对象），使用<associaion javaType="">

指定“多方”关系时（集合），使用<collection ofType="">

7.2、一对一查询

需求：查询银行卡信息通过关联查询用户信息

分析：一个银行卡只能有一个用户信息，所以从查询银行卡信息关联查询用户信息时一对一关系。我们通过查询银行卡id查询对应用户信息

Pojo:加入User类对象作为Account类的一个属性

package com.by.pojo;

public class Account {
      private   Integer id;
      private Integer uid;
      private Double money;
      
      private  User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

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

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

Mapper

package com.by.mapper;

import com.by.pojo.Account;
import com.by.pojo.User;

import java.util.List;

public interface AccountMapper {

   Account findAccountById(Integer id);
}

Mapper.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.by.mapper.AccountMapper">
<!--结果映射-->
<resultMap id="findAccountByIdResultMap" type="com.by.pojo.Account">
    <id column="aid" property="id"/>
    <result column="uid" property="uid"/>
    <result column="money" property="money"/>
    <!--指定关系表数据的封装规则-->
    <association property="user" javaType="com.by.pojo.User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="sex" column="sex"/>
    <result property="birthday" column="birthday"/>
    <result property="address" column="address"/>
    </association>
</resultMap>
    <select id="findAccountById" resultMap="findAccountByIdResultMap">
        select a.id aid,
                   a.uid uid,
                   a.money money,
                   u.*
            from account a left join user u on a.uid=u.id
        where a.id=#{id};
    </select>

</mapper>

7.3、一对多查询

需求：查询用户信息及其关联的银行卡信息

分析：一个用户可以拥有多个银行卡，因此用户和卡之间关联关系为一对多。

Pojo：在User类中加入accountList属性

package com.by.pojo;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private  String address;
    private String password;
    List<Account> accountList;
    List<Role> roleList;

    public List<Account> getAccountList() {
        return accountList;
    }

    public void setAccountList(List<Account> accountList) {
        this.accountList = accountList;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", roleList=" + roleList +
                '}';
    }
}

Mapper

package com.by.mapper;
import com.by.pojo.Account;
import com.by.pojo.User;
import java.util.List;

public interface UserMapper {
    List<Account> selectAccountByUserId(Integer id);
}

Mapper.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.by.mapper.UserMapper">
 <!--结果映射User-->
    <resultMap id="selectAccountByUserIdResultMap" type="com.by.pojo.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <result property="birthday" column="birthday"/>
        <result property="address" column="address"/>
    <!--
      collection 是用于建立一对多集合属性的对应关系
      ofType：用于指定集合元素的数据类型

-->
        <collection property="accountList" ofType="com.by.pojo.Account">
                <id property="id" column="aid"/>
                <result property="uid" column="uid"/>
                <result property="money" column="money"/>
        </collection>
    </resultMap>

    <select id="selectAccountByUserId" resultMap="selectAccountByUserIdResultMap">
        select u.*,
                   a.id aid,
                   a.uid,
                   a.money
            from user u left join account a on u.id=a.uid
        where u.id=#{id}
    </select>
</mapper>

7.4、多对多查询

需求：查询角色及角色赋予的用户信息。

分析：一个用户可以拥有多个角色，一个角色也可以赋予多个用户，用户和角色为双向的一对多关系，多对多关系我们看成是双向的一对多关系。

Pojo：加入Role角色类用于关联用户表，在User类加入roleList属性

package com.by.pojo;

public class Role {
   private Integer id;
   private  String roleName;
   private  String roleDesc;
public Role(){

}

    public Role(Integer id, String roleName, String roleDesc) {
        this.id = id;
        this.roleName = roleName;
        this.roleDesc = roleDesc;
    }

    public Integer getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}

Mapper

package com.by.mapper;

import com.by.pojo.Account;
import com.by.pojo.User;

import java.util.List;

public interface UserMapper {
    List<User> selectAll();
}

Mapper.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.by.mapper.UserMapper">

<resultMap id="selectAllResultMap" type="com.by.pojo.User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="sex" column="sex"/>
    <result property="birthday" column="birthday"/>
    <result property="address" column="address"/>
    <collection property="roleList" ofType="com.by.pojo.Role">
        <id property="id" column="rid"/>
        <result property="roleName" column="role_name"/>
        <result property="roleDesc" column="role_desc"/>
    </collection>
</resultMap>
    <select id="selectAll" resultMap="selectAllResultMap">
        select u.*,
               r.id rid,
               r.role_name,
               r.role_desc
         from user u left join user_role ur on u.id=ur.uid<!--关联多表查询用户和角色信息-->
         left join role r on ur.rid=r.id;
    </select>

</mapper>

八、MyBatis的延迟加载

8.1、什么是延迟加载

开启延迟加载后，在真正使用数据时会发起级联查询，不用的时候不用查。

一对一延迟加载：

Mapper：

package com.by.mapper;
import com.by.pojo.Account;
import com.by.pojo.User;
import java.util.List;

public interface AccountMapper {
   Account findAccountById(Integer id);
}

Mapper.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.by.mapper.AccountMapper">

<resultMap id="findAccountByIdResultMap" type="com.by.pojo.Account">
    <id column="id" property="id"/>
    <result column="uid" property="uid"/>
    <result column="money" property="money"/>
<!--
          property：属性名
          select： 要调用的 select 映射的 id
          column ： 传递给 select 映射的参数
          fetchType="lazy"：懒加载，默认情况下是没有开启延迟加载的，局部配置
         -->
    <association property="user" javaType="com.by.pojo.User" select="com.by.mapper.UserMapper.getUserById" column="uid" fetchType="lazy">
    </association>
</resultMap>
    <select id="findAccountById" resultMap="findAccountByIdResultMap">
       select * from account where id=#{id}
    </select>

</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.by.mapper.UserMapper">
<select id="getUserById" resultType="com.by.pojo.User">
    select * from user where id=#{id}
</select>
</mapper>

一对多延迟加载：

Mapper：

package com.by.mapper;
import com.by.pojo.Account;
import com.by.pojo.User;
import java.util.List;

public interface UserMapper {
     User selectAccountByUserId(Integer id);
}

Mapper.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.by.mapper.UserMapper">
   <resultMap id="selectAccountByUserIdResultMap" type="com.by.pojo.User">
       <id property="id" column="id"/>
       <result property="username" column="username"/>
       <result property="birthday" column="birthday"/>
       <result property="sex" column="sex"/>
       <result property="address" column="address"/>
       <result property="password" column="password"/>
<!--
          property：属性名
          select： 要调用的 select 映射的 id
          column ： 传递给 select 映射的参数
          fetchType="lazy"：懒加载，默认情况下是没有开启延迟加载的，局部配置
         -->
       <collection property="accountList"
                   ofType="com.by.pojo.Account"
                   select="com.by.mapper.AccountMapper.getAccountById"
                   column="id"
                   fetchType="lazy"/>
   </resultMap>

    <select id="selectAccountByUserId" resultMap="selectAccountByUserIdResultMap">
        select * from user where id=#{id}
    </select>
</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.by.mapper.AccountMapper">
<select id="getAccountById" resultType="com.by.pojo.Account">
    select * from account where uid=#{uid}
</select>
</mapper>

多对多延迟加载：

Mapper:

package com.by.mapper;
import com.by.pojo.Role;
import java.util.List;

public interface RoleMapper {
    List<Role> selectRole();
}

Mapper.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.by.mapper.RoleMapper">
    <resultMap id="selectRoleResultMap" type="com.by.pojo.Role">
        <id property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleDesc" column="role_desc"/>
<!--
          property：属性名
          select： 要调用的 select 映射的 id
          column ： 传递给 select 映射的参数
          fetchType="lazy"：懒加载，默认情况下是没有开启延迟加载的，局部配置
         -->
        <collection property="userList"
                    ofType="com.by.pojo.User"
                    select="com.by.mapper.UserMapper.selectUserById"
                    column="id"
                    fetchType="lazy"/>
    </resultMap>

    <select id="selectRole" resultMap="selectRoleResultMap">
        select * from role;
    </select>


</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.by.mapper.UserMapper">
 <select id="selectUserById" resultType="com.by.pojo.User">
        select
            u.*
        from user u left join  user_role ur on u.id=ur.uid
        left join role r on r.id=ur.rid
        where rid=#{rid}
    </select>
</mapper>

8.2、全局开启懒加载

在mybatis-config.xml中全局懒加载配置

 <!-- 全局配置延迟加载策略 -->
    <settings>
        <!--  打开延迟加载的开关  -->
        <setting name="lazyLoadingEnabled" value="true"/>
    </settings>

九、MyBatis的动态SQL

9.1、什么是动态SQL

MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接称完整的SQL之后再执行，以达到SQL复用、简化编程的效果。

9.2、if标签

    List<User> findUser(User user);

Mapper.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.by.mapper.UserMapper">
    <select id="findUser" resultType="com.by.pojo.User">
        select * from user where 1=1
        <if test="username!=null and username!=''">
            and username=#{username}
        </if>
        <if test="birthday!=null">
            and birthday=#{birthday}
        </if>
        <if test="sex!=null and sex!=''">
            and sex=#{sex}
        </if>
        <if test="address!=null and address!=''">
            and address=#{address}
        </if>
    </select>
</mapper>

9.3、where标签

为了简化上面where 1=1的条件拼装，我们可以使用where标签将if标签代码块包起来，将1=1条件去掉。

若查询条件的开头为 “AND” 或 “OR”，where 标签会将它们去除。

    List<User> findUser2(User user);

Mapper.xml

<select id="findUser2" resultType="com.by.pojo.User">
        select * from user
        <where>
            <if test="username!=null and username!=''">
                and username=#{username}
            </if>
            <if test="birthday!=null">
                and birthday=#{birthday}
            </if>
            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
            <if test="address!=null and address!=''">
                and address=#{address}
            </if>
        </where>
    </select>

9.4、set标签

set标签用于动态包含需要更新的列，并会删掉额外的逗号

    void updateUserById(User user);

Mapper.xml

<update id="updateUserById" parameterType="com.by.pojo.User">
        update user
        <set>
            <if test="username!=null and username!=''">
                 username=#{username},
            </if>
            <if test="birthday!=null">
              birthday=#{birthday},
            </if>
            <if test="sex!=null and sex!=''">
            sex=#{sex},
            </if>
            <if test="address!=null and address!=''">
             address=#{address},
            </if>
        </set>
        where id=#{id}
    </update>

9.5、trim标签

trim标签可以代替where标签、set标签，可以添加前缀和后缀，去除多余的前缀和后缀。

void addUser(User user);

Mapper.xml

<insert id="addUser" parameterType="com.by.pojo.User">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null and username!=''">
                username,
            </if>
            <if test="birthday!=null ">
                birthday,
            </if>
            <if test="sex!=null and sex!=''">
                sex,
            </if>
            <if test="address!=null and address!=''">
               address,
            </if>
        </trim>
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="username!=null and username!=''">
              #{username},
            </if>
            <if test="birthday!=null ">
               #{birthday},
            </if>
            <if test="sex!=null and sex!=''">
              #{sex},
            </if>
            <if test="address!=null and address!=''">
              #{address},
            </if>
        </trim>
    </insert>

9.6、foreach标签

foreach标签的常见使用场景是集合进行遍历

   void deleteById(@Param("ids") List<Integer> ids);

Mapper.xml

    <delete id="deleteById" parameterType="list">
        delete from user where id
    <!--
            collection：取值list、array、map、@Param("keyName")、对象的属性名
            item：循环取出的具体对象
            open：起始符
            separator：分隔符
            close：结束符
        -->
        <foreach collection="ids" item="id" separator="," open="in(" close=")">
                  #{id}
        </foreach>
    </delete>

test

  @Test
    public void deleteUserById(){
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<Integer> ids=new ArrayList<>();
        ids.add(51);
        ids.add(52);
        ids.add(53);
        userMapper.deleteById(ids);
        }

Bbbcccccc

关注

37
点赞
踩
39

收藏

觉得还不错? 一键收藏
0
评论
MyBatis笔记

在介绍MyBatis之前，我们首先要对框架进行一个理解。框架（Framework）它既是一个框子也是一个架子，框架具有一个约束和支撑的作用。它是对已经基础的代码进行了封装并提供相应的API，开发者使用框架即直接可调用封装好的api，这样可以提高工作效率和开发速度。MyBatis前身是apache的ibatis，是一个封装了jdbc的持久层框架，使开发者只需关注sql语句。
复制链接

扫一扫