Mybatis(二)--映射

1 映射文件XXMapper.xml语法:

<mapper namespace="cn.kgc.mspper.UserMapper">
	<select id=" findCount " …
		……
	</select>
</mapper>

1.1 namespace:命名空间

namespace的命名必须跟某个接口同名

1.2 id: 命名空间中唯一的标识符**

接口中的方法与映射文件中的SQL语句id一一对应

1.3 parameterType: 参数类型

(单参可以省略,多参不可以)

传入SQL语句的参数类型

1.4 resultType:返回值类型

SQL语句返回值类型的完整类名或别名

1.5 实战:

需求: 根据用户名对用户表进行模糊查询

1.6 实现:

(1)导入库 smbms_db.sql

(2)创建工程,目录
Mybatis(二)--映射
(3)pom.xml

<dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.20</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.1</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.21</version>
</dependency>

(4)实体类 main/java/cn.kgc.entity/User

package cn.kgc.entity;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Getter
@Setter
public class User {
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private String birthday;
    private String phone;
    private String address;
    private Integer userRole;
    private String creationDate;
    private Integer modifyBy;
private String modifyDate;
}

(5)resources/ mybatis-config.xml、jdbc.properties
jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=zjj

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties" />
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <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>
    <mappers>
        <mapper resource="cn/kgc/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

(6) main/java/cn.kgc.mapper/UserMapper.java

package cn.kgc.mapper;

import cn.kgc.entity.User;
import org.apache.ibatis.annotations.Param;

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

public interface UserMapper {
    //查询记录数
    Integer findCount();
}

(7) resources/ cn.kgc.mapper/UserMapper.xml

注意 : resources里创建包直接这样写 cn/kgc/mapper

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.kgc.mapper.UserMapper">
    <select id="findCount" resultType="java.lang.Integer" >
        select count(*) from smbms_user
</select>
</mapper>

(8) main/java/cn.kgc.util/MyBatisUtil

package cn.kgc.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

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

public class MyBatisUtil {
    private static SqlSessionFactory factory = null;

    static {
        try {
            InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = null;
        if (factory != null){
            sqlSession = factory.openSession();
        }
        return sqlSession;
    }
}

(9) test/java/cn.kgc.test/TestUserMapper.java

package cn.kgc.test;

import cn.kgc.entity.User;
import cn.kgc.mapper.UserMapper;
import cn.kgc.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestUserMapper {
    @Test
    public void testFindCount(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        Integer count = sqlSession.getMapper(UserMapper.class).findCount();
        System.out.println("记录数:"+count);
        sqlSession.close();
}
}

1.7 parameterType 基础数据类型/复杂数据类型

parameterType
(1)基础数据类型:
int、String、Date等
只能传入一个,通过#{参数名}即可获取传入的值

(2)复杂数据类型:
Java实体类、Map等
通过#{属性名}或者#{map的keyName}即可获取传入值

1.8 多参实战:

需求:通过用户名模糊匹配和用户角色id 条件查询
Mybatis(二)--映射

1.9第1-3种实现:

(1)UserMapper.java

//1.通过用户名和用户角色查询列表
    List<User> findByUser(User user);
//2.通过用户mine和用户角色Map容器参数查询列表
    List<User> findByMap(Map<String,Object> map);
//3.通过注解方式实现多参查询
List<User> findByConditions(@Param("name") String userName,@Param("role") Integer userRole);

(2)UserMapper.xml

<select id="findByUser" resultType="cn.kgc.entity.User" parameterType="cn.kgc.entity.User">
        select * from smbms_user where userName like concat('%',#{userName},'%')
                        and userRole = #{userRole}
    </select>
    <select id="findByMap" resultType="cn.kgc.entity.User" parameterType="java.util.Map">
--                         MyBatis的Aliase配置  可以省略 cn.kgc.entity
        select * from smbms_user where userName like concat('%',#{userName},'%')
                        and userRole = #{userRole}
    </select>
    <select id="findByConditions" resultType="cn.kgc.entity.User" >
        select * from smbms_user where userName like concat('%',#{name},'%')
                        and userRole = #{role}
    </select>

(3)TestUserMapper

@Test
    public void testFindByUser(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        User user = new User();
        user.setUserName("王");
        user.setUserRole(3);
        List<User> list = sqlSession.getMapper(UserMapper.class).findByUser(user);
        for (User u: list) {
            System.out.println("userName:" + u.getUserName() + "    userPassword:" + u.getUserPassword());
        }
        //数据库里时间有个0000000 可能会报错 脏数据,记得改掉
        sqlSession.close();
    }
    @Test
    public void testFindByMap(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        Map<String, Object> map = new HashMap<>();
        map.put("userName","孙");
        map.put("userRole",3);
        List<User> list = sqlSession.getMapper(UserMapper.class).findByMap(map);
        for (User u: list) {
            System.out.println("userName:" + u.getUserName() + "    userPassword:" + u.getUserPassword());
        }
        sqlSession.close();
    }
    @Test
    public void findByConditions(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        List<User> list = sqlSession.getMapper(UserMapper.class).findByConditions("孙",3);
        for (User u: list) {
            System.out.println("userName:" + u.getUserName() + "    userPassword:" + u.getUserPassword());
        }
        sqlSession.close();
}

1.10 resultMap 描述如何将结果集映射到Java对象

(面试题)

**resultType :**直接表示返回类型
基本数据类型
复杂数据类型

**resultMap :**对外部resultMap的引用
应用场景:
数据库字段信息与对象属性不一致
复杂的联合查询,*控制映射结果
二者不能同时存在,本质上都是Map数据结构

1.11 通过用户名和角色编码查询用户列表

(1)User.java

private String userRoleName;

(2)UserMapper.java

//ResultMap实战
List<User> FindByResultMap(User user);

(3)UserMapper.xml

<select id="FindByResultMap" parameterType="cn.kgc.entity.User" resultMap="userList">
        select a.id,a.userCode,b.roleName from smbms_user a,smbms_role b where a.userRole = b.id
                        and userName like concat('%',#{userName},'%')
                        and userRole = #{userRole}
--         select a.id,a.userCode,b.roleName from smbms_user a left join smbms_role b on a.userRole = b.id
--                         and userName like concat('%',#{userName},'%')
--                         and userRole = #{userRole}
    </select>
    <resultMap id="userList" type="cn.kgc.entity.User">
        <result property="id" column="id"></result>
        <result property="userCode" column="userCode"></result>
        <result property="userRoleName" column="roleName"></result>
    </resultMap>
<!--    不用上述方法,就用别名as 上述方法针对杠精 id因为两个一样-->

(4)TestUserMapper.java

@Test
    public void findByConditions2(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        User user = new User();
        user.setUserName("王");
        user.setUserRole(3);
        List<User> list = sqlSession.getMapper(UserMapper.class).FindByResultMap(user);
        for (User u: list) {
            System.out.println("用户id:" + u.getId() + "    用户编码:" + u.getUserCode() + "    角色名称:" + u.getUserRoleName());
        }
        sqlSession.close();
    }

2.增删改(Mybatis (一) 有讲过)

核心注意点:
insert、update、delete元素均没有resultType属性
但是都要有sqlSession.commit();

(1)实现用户表的增加操作(省略)

(2)实现用户表的修改操作(省略)

(3)实现修改当前用户密码的功能-@Param(省略)

核心思想:超过三个参数建议封装成对象,两个参数建议使用@Param

用注解就不用写参数

(4)根据用户id删除用户信息(省略)

3.resultMap

核心思想

id
一般对应数据库中该行的主键id,设置此项可提高MyBatis性能

result
映射到JavaBean的某个“简单类型”属性

association
映射到JavaBean的某个“复杂类型”属性,比如JavaBean类

collection
映射到JavaBean的某个“复杂类型”属性,比如集合

(1)根据用户角色id获取用户列表-association

A.核心思想:

association
复杂的类型关联,一对一
内部嵌套
映射一个嵌套JavaBean属性

属性
property:映射数据库列的实体对象的属性
javaType:完整Java类名或者别名
resultMap:引用外部resultMap

子元素
id
result
property:映射数据库列的实体对象的属性
column:数据库列名或者别名

B.实战:
利用User-Role实现查询用户列表

根据smbms_user 里的userRole
去对应的smbms_role对应的id
然后查出对应的roleName

Role:

package cn.kgc.entity;

import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
public class Role {
    private Integer id;
    private String roleCode;
    private String roleName;
    private Integer createdBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;
}

User

private Role role;

UserMapper

//利用User-Role实现查询用户列表
List<User> findUserRole(Integer userRole);

UserMapper.xml

<select id="findUserRole" parameterType="java.lang.Integer" resultMap="userRoleResult">
        SELECT a.id,a.userCode,b.roleName FROM smbms_user a,smbms_role b
            WHERE a.userRole = b.id AND a.userRole = #{userRole}
    </select>
    <!--    resultMap 将查询到的复杂数据(比如查询到几个表中数据)映射到一个结果集当中。-->
<!--    映射User类的结果集-->
    <!-- resultMap最终还是要将结果映射到User上,type就是指定映射到哪一个User -->
    <!--     property:主键在pojo中的属性名 -->
    <!--     column:主键在数据库中的列名 -->
    <resultMap id="userRoleResult" type="cn.kgc.entity.User">
        <id property="id" column="id"></id>
        <result property="userCode" column="userCode"></result>
<!--        association映射关联对象Role的结果集-->
        <association property="role" javaType="cn.kgc.entity.Role">
            <result property="roleName" column="roleName"></result>
        </association>
    </resultMap>

TestUserMapper

@Test
public void findUserRole() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    List<User> userRole = sqlSession.getMapper(UserMapper.class).findUserRole(1);
    for (User u: userRole) {
        System.out.println(
                "Id:" + u.getId() + "    Code:" + u.getUserCode() + "    RoleName:" + u.getRole().getRoleName());
    }
    sqlSession.close();
}

结果:
Mybatis(二)--映射
(2)获取指定用户的相关信息及其地址列表-collection

A核心思想:

collection
复杂类型集合,一对多
内部嵌套
映射一个嵌套结果集到一个列表

属性
property:映射数据库列的实体对象的属性
ofType:完整Java类名或者别名(集合所包括的类型)
resultMap:引用外部resultMap

子元素
id
result
property:映射数据库列的实体对象的属性
column:数据库列名或者别名

B实战:
collection实现查询用户地址集合列表

根据smbms_user的id
去对应smbms_address里的userId
其中用户地址可能不止一个

Address.java

package cn.kgc.entity;

import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
public class Address {
    private Integer id;
    private String contact;
    private String addressDesc;
    private String postCode;
    private String tel;
    private Integer createdBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;
    private Integer userId;
}

User.java

private List<Address> addressList;

UserMapper.java

//collection实现查询用户地址集合列表
List<User> findUserAddressListById(@Param("id") Integer id);

UserMapper.xml

<select id="findUserAddressListById" resultMap="UserAddressResult">
    select a.id,a.userCode,b.addressDesc from smbms_user a ,smbms_address b
    where a.id = b.userid
    and a.id = #{id}
</select>
<resultMap id="UserAddressResult" type="cn.kgc.entity.User">
    <id property="id" column="id"></id>
    <result property="userCode" column="userCode"></result>
    <collection property="addressList" ofType="cn.kgc.entity.Address">
        <result property="addressDesc" column="addressDesc" />
    </collection>
</resultMap>

TestUserMapper

@Test
public void findUserAddressListById() {
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    List<User> userRole = sqlSession.getMapper(UserMapper.class).findUserAddressListById(1);
    for (User u: userRole) {
        System.out.println("userId:" + u.getId() + "    userCode:" + u.getUserCode());
        List<Address> addressList = u.getAddressList();
        for (Address a :addressList){
            System.out.println("   addressDesc:" + a.getAddressDesc());
        }
    }
    sqlSession.close();
}

Mybatis(二)--映射
题外话:

shift + alt + \ 能显示时间

----2021.11.30&12.01

上一篇:[Spring]10.Spring整合Mybatis


下一篇:MyBatis01——初始mybatis