Mybatis学习笔记 

于 2021-03-12 12:10:21 发布
阅读量229 收藏
点赞数
分类专栏： Mybatis 文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_39039228/article/details/114658069
版权

Mybatis学习笔记 

  1. 项目搭建
    建立普通maven项目，pom.xml如下 
    <?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>org.example</groupId>
    <artifactId>Mybatis3</artifactId>
    <version>1.0-SNAPSHOT</version>
<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.6</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>
</dependencies>

</project>

     

  2. 建立数据库表 
    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT '' COMMENT '姓名',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `age` int(11) DEFAULT '0' COMMENT '年龄',
  `sex` char(5) DEFAULT '' COMMENT '性别',
  `email` varchar(50) DEFAULT '' COMMENT '邮箱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_user_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_role
-- ----------------------------
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '角色名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_classroom
-- ----------------------------
DROP TABLE IF EXISTS `t_classroom`;
CREATE TABLE `t_classroom` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `number` varchar(50) DEFAULT '' COMMENT '教室门牌号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_classroom_id` (`id`,`number`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user_classroom
-- ----------------------------
DROP TABLE IF EXISTS `t_user_classroom`;
CREATE TABLE `t_user_classroom` (
  `id` bigint(20) NOT NULL,
  `uid` int(11) DEFAULT NULL COMMENT '用户ID',
  `cid` bigint(20) DEFAULT NULL COMMENT '教室ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
    SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user_role
-- ----------------------------
DROP TABLE IF EXISTS `t_user_role`;
CREATE TABLE `t_user_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `uid` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `rid` bigint(20) DEFAULT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

     

  3. 建立实体类和Mapper类
    Mybatis是利用get set或有参构造器来构造实体类的结果集，所以必须要有get 和set方法或lombok插件的注解
    Mybatis的缓存需要序列化实体类，所以实体类必须要实现Serializable接口 
    import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

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


@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {
    Long id;
    String name;
    String password;
    int age;
    String sex;
    String email;
    ClassRoom classRoom;
    List<Role> roles;
}
    import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role implements Serializable {
    Long id;
    String name;
}
    import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ClassRoom implements Serializable {
    Long id;
    String number;
}
    import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserRole implements Serializable {
    Long id;
    Long uid;
    Long rid;
}

    建立Mapper接口类，以UserMapper为例，其他类似
     

    import com.entity.User;

import java.util.List;
import java.util.Map;

public interface UserMapper {

}

     

  4. 创建SqlSession工具类 
    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 SqlSessionUtil {
    //这里的SqlSessionFactory不要每次都创建，要不然二级缓存会失效
    private static SqlSessionFactory sqlSessionFactory;
   public static SqlSession getSqlSession(boolean autoCommit){
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (sqlSessionFactory==null){
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }
        //自动提交
        return sqlSessionFactory.openSession(autoCommit);
    }
}
  5. 在resources目录下新建*Mapper.xml，namespace的值必须和Mapper类的全路径保持一致，这是Mybatis区分不同sql语句的重要参数之一 
    <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.UserMapper">
    
</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.dao.RoleMapper">
    
</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.dao.ClassRoomMapper">
    
</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.dao.UserRoleMapper">
    
</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.dao.UserClassRoomMapper">
    
</mapper>

     

  6. 创建mybatis-config.xml
    配置必要的参数以及数据源，最重要的是必须配置*Mapper.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>

    <settings>
        <!--输出sql日志-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--对关联的属性进行懒加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--自动返回生成的id值 必须要先保证数据库id已经设置了主键自增-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--触发懒加载的方法-->
        <setting name="lazyLoadTriggerMethods" value=""/>
        <!--相当于是否关闭懒加载-->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!--当返回类型resultType为map的时候，mybatis会忽略查询结果为null的字段。假如数据库中的字段为null,是否依然在返回结果的map中put该字段，默认false-->
        <setting name="callSettersOnNulls" value="true"/>
        <!--当返回值类型为list集合且查询不到任何数据时，是否返回null或者空list。目前测试效果加不加都返回空list-->
        <setting name="returnInstanceForEmptyRow" value="true"/>
        <!--当sql结果集存在字段不能自动映射实体类的属性时，需要执行的策略-->
        <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
        <!--<setting name="defaultExecutorType" value="BATCH"/>-->
        <!--一级缓存的范围，默认是 SESSION，多个session同时执行sql的时候，会发生脏读，所以建议statement-->
        <setting name="localCacheScope" value="SESSION"/>
        <!--开启二级缓存-->
        <setting name="cacheEnabled" value="true"/>
    </settings>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/lim?serverTimezone=UTC&amp;characterEncoding=UTF-8&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/ClassRoomMapper.xml"/>
        <mapper resource="mappers/UserMapper.xml"/>
        <mapper resource="mappers/RoleMapper.xml"/>
        <mapper resource="mappers/UserRoleMapper.xml"/>
        <mapper resource="mappers/UserClassRoomMapper.xml"/>
    </mappers>

</configuration>

     

  7. 对用户User进行新增
    完善UserMapper.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.dao.UserMapper">

    <!--普通插入-->
    <insert id="addUser">
        insert into t_user(id, password, name, age, sex, email)
        values (#{id}, #{name}, #{password}, #{age}, #{sex}, #{email})
    </insert>
    
</mapper>

    Mapper接口添加方法
     

    import com.entity.User;

import java.util.List;
import java.util.Map;

public interface UserMapper {

    //普通插入
     Long addUser(User user);

}

    测试新增
     

    import com.dao.UserMapper;
import com.entity.User;
import com.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;


public class UserInsertTest {
    public static void main(String[] args) {
        //不开启自动提交，数据库才可以回滚
        SqlSession sqlSession = SqlSessionUtil.getSqlSession(false);
        try {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user = new User();
            user.setId(6L);
            user.setName("zhang6");
            user.setPassword("zhang");
            user.setAge(23);
            user.setSex("0");
            user.setEmail("1234567@qq.com");
            mapper.addUser(user);

            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }
}

    自动生成id，并自动设置到User的id属性上，需要保证数据库表的主键字段启用了自增属性，并且在Mybatis的配置文件中设置启用自增
     

    <settings>
   
        <!--自动返回生成的id值 必须要先保证数据库id已经设置了主键自增-->
        <setting name="useGeneratedKeys" value="true"/>
        
</settings>

    在具体的查询语句中也要指定需要返回的数据库表主键字段对应实体类的哪个属性，所以之前的<insert>语句可以优化成下面这样
     

    <!--插入用户，自动生成ID 必须设置useGeneratedKeys=true（全局mybatis-config.xml中的setting中设置，或者在具体的sql中设置） 和 keyProperty(id对应的实体类字段名)-->
    <insert id="addUserWithOutId" keyProperty="id">
        insert into t_user(name, password, age, sex, email)
        values (#{name}, #{password}, #{age}, #{sex}, #{email})
    </insert>


    测试如下

    SqlSession sqlSession = SqlSessionUtil.getSqlSession(false);
        try {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            User user = new User();
            //user.setId(6L);
            user.setName("zhang8");
            user.setPassword("zhang");
            user.setAge(23);
            user.setSex("0");
            user.setEmail("1234567@qq.com");
            mapper.addUserWithOutId(user);
            System.out.println("用户ID："+user.getId());
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }

    运行结果

    /Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/bin/java "-javaagent:/Applications/IntelliJ IDEA.app/Contents/lib/idea_rt.jar=59756:/Applications/IntelliJ IDEA.app/Contents/bin" -Dfile.encoding=UTF-8 -classpath /Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/charsets.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/deploy.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/cldrdata.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/dnsns.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/jaccess.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/jfxrt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/localedata.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/nashorn.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/sunec.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/sunjce_provider.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/sunpkcs11.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/ext/zipfs.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/javaws.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/jce.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/jfr.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/jfxswt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/jsse.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/management-agent.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/plugin.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/resources.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/jre/lib/rt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/lib/ant-javafx.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/lib/dt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/lib/javafx-mx.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/lib/jconsole.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/lib/packager.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/lib/sa-jdi.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0_221.jdk/Contents/Home/lib/tools.jar:/Users/liming/mydemo/Mybatis3/target/test-classes:/Users/liming/mydemo/Mybatis3/target/classes:/Users/liming/.m2/repository/org/mybatis/mybatis/3.4.6/mybatis-3.4.6.jar:/Users/liming/.m2/repository/org/projectlombok/lombok/1.18.12/lombok-1.18.12.jar:/Users/liming/.m2/repository/mysql/mysql-connector-java/6.0.6/mysql-connector-java-6.0.6.jar user.UserInsertTest
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1375995437.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5204062d]
==>  Preparing: insert into t_user(name, password, age, sex, email) values (?, ?, ?, ?, ?) 
==> Parameters: zhang8(String), zhang(String), 23(Integer), 0(String), 1234567@qq.com(String)
<==    Updates: 1
用户ID：9
Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5204062d]
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5204062d]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5204062d]
Returned connection 1375995437 to pool.

Process finished with exit code 0

     

  8. 查询User
    查询方法需要指定结果集，以及入参类型（可以不用写）
    select标签中的id是此sql语句的唯一标识，同一namespace中的语句id不能重复，方法不能重载，select语句的结果集可以选择resultMap也可以选择resultType
    resultMap的值写对应resultMap标签对应的id
    resultMap标签中的result子标签property属性是实体类的属性名，column是对应结果集列标签名（数据库字段名或别名alias） 
    <resultMap id="userResultMap" type="com.entity.User">
        <id property="id" column="id"/>
        <result property="age" column="age"/>
        <result property="name" column="name"/>
        <result property="password" column="password"/>
        <result property="sex" column="sex"/>
        <result property="email" column="email"/>
</resultMap>
<select id="selectUserById" resultMap="userResultMap">
        select *
        from t_user
        where id = #{id}
</select>

    resultType写对应的实体类的全路径 ,但是数据列标签必须和实体属性名保持一直，否则不能自动映射，属性的值会为null

    <select id="selectUserById" resultType="com.entity.User">
        select * from t_user where id = #{id}
</select>

    也可以指定结果集resultType的类型为map，map的key是结果集的列标签或别名

    <select id="selectUserRoleToMapById" resultType="map">
        select * from t_user where id = #{id}
</select>

    一对一查询classroom之结果映射查询
    association标签代表一对一查询，property是user中的复杂对象classroom的属性名称，当指定结果集的类型为resultMap时，需要在select语句中直接写出join on语句，执行时mybatis会发出一条sql,不支持懒加载,由此可以看出resultMap只是对结果集和实体类属性映射的一个配置

     <select id="selectUserById2" resultMap="userResultMap2">
        select u.* , c.id as cid , c.number
        from t_user u
                 left join t_user_classroom uc on uc.cid = u.id left join t_classroom c on uc.cid = c.id
        where u.id = #{id}
 </select>
<resultMap id="userResultMap" type="com.entity.User">
        <id property="id" column="id"/>
        <result property="age" column="age"/>
        <result property="name" column="name"/>
        <result property="password" column="password"/>
        <result property="sex" column="sex"/>
        <result property="email" column="email"/>
        <!--association代表一对一-->
        <association property="classRoom" resultMap="classroom"/>
</resultMap>
<resultMap id="classroom" type="com.entity.ClassRoom">
        <id property="id" column="cid"/>
        <result property="number" column="number"/>
</resultMap>

    执行结果 

    Created connection 1873859565.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6fb0d3ed]
==>  Preparing: select u.* , c.id as cid , c.number from t_user u left join t_user_classroom uc on uc.cid = u.id left join t_classroom c on uc.cid = c.id where u.id = ? 
==> Parameters: 1(Long)
<==    Columns: id, name, password, age, sex, email, cid, number
<==        Row: 1, admin, admin, 20, 0, 1216656953@qq.com, 1, 九二班
<==      Total: 1
User(id=1, name=admin, password=admin, age=20, sex=0, email=1216656953@qq.com, classRoom=ClassRoom(id=1, number=九二班), roles=null)

    association还有第二种查询方式：嵌套查询
    在主查询语句中，我们不再需要写join语句了，我们可以写在association中的select属性指定级联查询语句的方法路径，column属性指定sql语句的参数

    <!-- 嵌套查询-->
<select id="selectUserById" resultMap="userResultMap">
     select *
     from t_user
     where id = #{id}
</select>
<resultMap id="userResultMap" type="com.entity.User">
        <id property="id" column="id"/>
        <result property="age" column="age"/>
        <result property="name" column="name"/>
        <result property="password" column="password"/>
        <result property="sex" column="sex"/>
        <result property="email" column="email"/>
        <resultMap id="userResultMap" type="com.entity.User">
        <!--column表示级联查询的入参，如果有多个参数，以{param1 = value1 , param2 = value2}来表示-->
        <association property="classRoom" column="{id = id}" select="com.dao.ClassRoomMapper.selectClassRoomById"/>
</resultMap>
    import com.entity.ClassRoom;

public interface ClassRoomMapper {
     ClassRoom selectClassRoomById(Long id);
}

    ClassRoomMapper中的sql语句就只需要按照入参来查询对应的classroom即可

    <?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.dao.ClassRoomMapper">
    <select id="selectClassRoomById" resultType="com.entity.ClassRoom">
      select * from t_classroom where id = #{id}
    </select>
</mapper>

    查询结果，可以看到发送了两次sql
     

    import com.dao.UserMapper;
import com.entity.User;
import com.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;
import java.util.Map;

public class UserSelectTest {
    public static void main(String[] args) {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession(false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectUserById(1L);
        System.out.println(user.toString());
    }
}
    Opening JDBC Connection
Created connection 1608230649.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5fdba6f9]
==>  Preparing: select * from t_user where id = ? 
==> Parameters: 1(Long)
<==    Columns: id, name, password, age, sex, email
<==        Row: 1, admin, admin, 20, 0, 1216656953@qq.com
<==      Total: 1
==>  Preparing: select * from t_classroom where id = ? 
==> Parameters: 1(Long)
<==    Columns: id, number
<==        Row: 1, 九二班
<==      Total: 1
User(id=1, name=admin, password=admin, age=20, sex=0, email=1216656953@qq.com, classRoom=ClassRoom(id=1, number=九二班), roles=null)

    启用懒加载，在mybatis配置文件中开启懒加载

    <!--对关联的属性进行懒加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
<!--触发懒加载的方法-->
        <setting name="lazyLoadTriggerMethods" value=""/>
    import com.dao.UserMapper;
import com.entity.User;
import com.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;
import java.util.Map;

public class UserSelectTest {
    public static void main(String[] args) {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession(false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectUserById(1L);
        //先不要调用toString方法，会触发懒加载
    }
}

    可以看到只发送了一条sql语句，并未查询classroom属性 

    Opening JDBC Connection
Created connection 1608230649.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5fdba6f9]
==>  Preparing: select * from t_user where id = ? 
==> Parameters: 1(Long)
<==    Columns: id, name, password, age, sex, email
<==        Row: 1, admin, admin, 20, 0, 1216656953@qq.com
<==      Total: 1

    在测试类中调用toString或者其他查看classroom的方法试试
     

    import com.dao.UserMapper;
import com.entity.User;
import com.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;
import java.util.Map;

public class UserSelectTest {
    public static void main(String[] args) {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession(false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectUserById(1L);
        //触发懒加载
        user.getClassRoom();
    }
}

    查询结果，发送了两条sql
     

    Opening JDBC Connection
Created connection 1873859565.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6fb0d3ed]
==>  Preparing: select * from t_user where id = ? 
==> Parameters: 1(Long)
<==    Columns: id, name, password, age, sex, email
<==        Row: 1, admin, admin, 20, 0, 1216656953@qq.com
<==      Total: 1
==>  Preparing: select * from t_classroom where id = ? 
==> Parameters: 1(Long)
<==    Columns: id, number
<==        Row: 1, 九二班
<==      Total: 1

    一对多查询<collection>及其懒加载
    collection标签中的javaType属性指的是容纳该一对多属性的集合类型，ofType指的是集合中的数据类型

    <resultMap id="userRoleResultMap" type="com.entity.User">
        <!--因为有集合属性的存在，所以user的其他属性必须写上，要不然只有collection标签会出现两行数据-->
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="email" column="email"/>
        <collection property="roles" javaType="java.util.List" ofType="com.entity.Role" resultMap="roleResultMap"/>
</resultMap>
<!--查询用户带角色-->
<resultMap id="roleResultMap" type="com.entity.Role">
     <result property="id" column="rid"/>
     <result property="name" column="rname"/>
</resultMap>
    <!--利用join一次性加载出来-->
    <select id="selectUserRoleById" resultMap="userRoleResultMap">
        select u.*, r.id as rid, r.name as rname
        from t_user u
                 left join t_user_role ur
                           on u.id = ur.uid
                 left join t_role r
                           on ur.rid = r.id

        where u.id = #{id}
    </select>

    懒加载
     

    <resultMap id="userRoleResultMap2" type="com.entity.User">
        <!--这里结果集的用户属性必须写上，要不然只有role字段会出现两行数据，无法自动加入到集合中去-->
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="email" column="email"/>
        <!--javaType：集合类型 ，ofType:集合里面的数据类型 column:入数 select：查询语句，与resultMap二选一 选用resultMap的话，可以不指定column-->
        <association property="classRoom" column="id" select="com.dao.UserClassRoomMapper.getClassRoomByUserId"/>
        <collection property="roles" column="id" javaType="java.util.List" ofType="com.entity.Role" select="com.dao.RoleMapper.selectRoleByUserId"/>
    </resultMap>
    <!--利用collection进行延迟加载，而不是用join一次性加载出来-->
    <select id="selectUserRoleById2" resultMap="userRoleResultMap2" useCache="true">
        select *
        from t_user
        where id = #{id}
    </select>

     

  9. 动态sql
     

    <!--动态sql-->
    <insert id="addUserFieldIfExist" keyProperty="id">
        insert into t_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name,
            </if>
            <if test="password != null">
                password,
            </if>
            <if test="age != 0">
                age,
            </if>
            <if test="sex">
                sex,
            </if>
            <if test="email">
                email
            </if>
        </trim>
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="password != null">
                #{password},
            </if>
            <if test="age != 0">
                #{age},
            </if>
            <if test="sex">
                #{sex},
            </if>
            <if test="email">
                #{email}
            </if>
        </trim>
    </insert>

     

  10.  

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值