Mybatis注解开发(十五)

CodeWang_NC

已于 2022-11-30 16:54:10 修改

阅读量230

点赞数

分类专栏： mybatis 文章标签： mybatis java spring

于 2022-10-11 18:40:01 首次发布

本文链接：https://blog.csdn.net/qq_45056135/article/details/127267423

版权

mybatis 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

介绍&步骤

视频教程: https://www.bilibili.com/video/BV1WZ4y1P7Bp/?p=186
官方笔记链接：https://pan.baidu.com/s/1dnL5hwOPHPMNgb81yzQIOQ
提取码：2022
目录结构:
在这里插入图片描述

1. 准备工作

1.1 导入坐标

<dependencies>
    <!--mybatis 依赖-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.5</version>
    </dependency>

    <!--mysql 驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>

    <!--junit 单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>

    <!-- 添加slf4j日志api -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.20</version>
    </dependency>
    <!-- 添加logback-classic依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
        <version>1.2.3</version>
    </dependency>
    <!-- 添加logback-core依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-core</artifactId>
        <version>1.2.3</version>
    </dependency>
</dependencies>

1.2 User数据库表和实体类

数据库表 user

drop table if exists user;

create table user(
	id int primary key auto_increment,
	username varchar(20),
	password varchar(20), 
	birthday bigint
);

insert into user values (null, "zhangsan", "123", null);
insert into user values (null, "lisi", "123", null);
insert into user values (null, "wangwu", "123", null);
select * from user;

数据库表 orders

drop table if exists orders;

create table orders(
	id int primary key auto_increment,
	ordertime DATE,
	total DOUBLE,
	uid INT
);

insert into orders VALUES (null, "2022-02-15", 5000, 1);
insert into orders VALUES (null, "2022-01-15", 5000, 1);
insert into orders VALUES (null, "2020-04-15", 580, 2);
insert into orders VALUES (null, "2020-04-15", 6000, 2);
insert into orders VALUES (null, "2022-10-10", 1200, 3);
insert into orders VALUES (null, "2021-06-25", 300, 3);
insert into orders VALUES (null, "2022-06-15", 5000, 1);

select * from orders;

关系库sys_role sys_user_role sys_user表：

DROP TABLE IF EXISTS `sys_role`;

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `roleName` varchar(50) DEFAULT NULL,
  `roleDesc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `sys_role` */

insert  into `sys_role`(`id`,`roleName`,`roleDesc`) values (1,'院长','负责全面工作'),(2,'研究员','课程研发工作'),(3,'讲师','授课工作'),(4,'助教','协助解决学生的问题');

/*Table structure for table `sys_user` */

DROP TABLE IF EXISTS `sys_user`;

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `password` varchar(80) DEFAULT NULL,
  `phoneNum` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `sys_user` */

insert  into `sys_user`(`id`,`username`,`email`,`password`,`phoneNum`) values (1,'zhangsan','zhangsan@itcast.cn','123','13888888888'),(2,'lisi','lisi@itcast.cn','123','13999999999'),(3,'wangwu','wangwu@itcast.cn','123','18599999999');

/*Table structure for table `sys_user_role` */

DROP TABLE IF EXISTS `sys_user_role`;

CREATE TABLE `sys_user_role` (
  `userId` bigint(20) NOT NULL,
  `roleId` bigint(20) NOT NULL,
  PRIMARY KEY (`userId`,`roleId`),
  KEY `roleId` (`roleId`),
  CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `sys_user` (`id`),
  CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleId`) REFERENCES `sys_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `sys_user_role` */

insert  into `sys_user_role`(`userId`,`roleId`) values (1,1),(1,2),(2,2),(2,3);

User实体类

public class User {

    private int id;
    private String username;
    private String password;
    private Date birthday;


    public int getId() {
        return id;
    }

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

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

    public Date getBirthday() {
        return birthday;
    }

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

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

1.3 resource下的配置文件

jdbc.properties文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///csdn?useSSL=false&useServerPrepStmts=true
# useSSL 忽略警告信息， userServer 开启PrepareStatement的预编译功能
jdbc.username=root
jdbc.password=123456
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

sqlMapConfig.xml文件这里注意使用注解一定要使用mapper标签包扫描不然扫描不到注解

<?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="jdbc.properties"></properties>

    <!--  定义别名 -->
    <typeAliases>
        <package name="com.itheima.domain"/>
    </typeAliases>
<!--    <typeAlias type="com.itheima.domain.User" alias="User"></typeAlias>-->


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <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>
    <mappers>
        <!--  配置文件的路径地址 就是Usermapper的地址  这里要用斜杠 不能用点 -->
        <!--        <mapper resource="com/itheima/mapper/UserMapper.xml"/>-->

        <!--        <-->
        <package name="com.itheima.mapper"/>
    </mappers>

</configuration>

log4j.properties文件

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=info, stdout

logback.xml 彩色文件

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <!--
        CONSOLE ：表示当前的日志信息是可以输出到控制台的。
    -->
    <appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
        </encoder>
    </appender>

    <logger name="com.itheima" level="DEBUG" additivity="false">
        <appender-ref ref="Console"/>
    </logger>


    <!--

      level:用来设置打印级别，大小写无关：TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF
     ， 默认debug
      <root>可以包含零个或多个<appender-ref>元素，标识这个输出位置将会被本日志级别控制。
      -->
    <root level="DEBUG">
        <appender-ref ref="Console"/>
    </root>
</configuration>

1.4 UserMapper接口

com.ithiema.mapper.UserMapper 路径

public interface UserMapper {

    @Insert("insert into user values (#{id}, #{username}, #{password}, #{birthday})")
    public void save(User user);

    @Update("update user set username=#{username}, password=#{password} where id = #{id}")
    public void update(User user);

    @Delete("delete from user where id = #{id}")
    public void delete(int id);

    @Select(" select * from user where id = #{id}")
    public User findById(int id);

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

1.5 简单的测试代码

    private UserMapper mapper;

    @Before
    public void before() throws IOException {
        System.out.println("测试方法之前执行.......");

        String resource = "sqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testSave(){
        User user = new User();
        user.setUsername("jijiguowang");
        user.setPassword("12456");
//        user.setBirthday(new Date());
        mapper.save(user);
    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setId(13);
        user.setUsername("lijingliang");
        user.setPassword("123");
        mapper.update(user);
    }

    @Test
    public void testDelete(){
        mapper.delete(13);
    }

    @Test
    public void testFindById(){
        User user = mapper.findById(2);
        System.out.println(user);
    }

    @Test
    public void testFindAll(){
        List<User> userList = mapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
        System.out.println();
    }

1.6 MyBatis的常用注解

这几年来注解开发越来越流行，Mybatis也可以使用注解开发方式，这样我们就可以减少编写Mapper
映射文件了。我们先围绕一些基本的CRUD来学习，再学习复杂映射多表操作。
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result 一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封装

1.7 注解实现复杂映射开发

实现复杂关系映射之前我们可以在映射文件中通过配置来实现，使用注解开发后，我们可以使用@Results注解
，@Result注解，@One注解，@Many注解组合完成复杂关系的配
在这里插入图片描述

2. 一对一查询(注解实现)

2.1 创建订单类

需求: 查询一个订单，与此同时查询出该订单所属的用户

public class Order {
    private int id;
    private Date ordertime;
    private double total;


    //表示当前订单属于哪一个用户
    private User user;

    public int getId() {
        return id;
    }

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

    public Date getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(Date ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotal() {
        return total;
    }

    public void setTotal(double total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime=" + ordertime +
                ", total=" + total +
                ", user=" + user +
                '}';
    }
}

2.2 创建OrderMapper接口

import com.itheima.domain.Order;
import com.itheima.domain.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;
注解方式1:

public interface OrderMapper {
    @Select("select *, o.id oid from orders o, user u where o.uid=u.id")
    @Results({
            @Result(column = "oid", property="id"),
            @Result(column = "ordertime", property="ordertime"),
            @Result(column = "total", property="total"),
            @Result(column = "uid", property="user.id"),
            @Result(column = "username", property="user.username"),
            @Result(column = "password", property="user.password")
    })
    List<Order> findAll();
}

注解方式2:

public interface OrderMapper {
    //先查order表  通过得到的uid 调用UserMapper接口的findById方法 传入参数为uid
    @Select("select * from orders")
    @Results({
            @Result(column = "id", property="id"),
            @Result(column = "ordertime", property="ordertime"),
            @Result(column = "total", property="total"),

            @Result(
                    property = "user", //要封装的属性名称
                    column = "uid", // 根据那个字段去查询user表数据
                    javaType = User.class, // 要封装的实体类型
                    // select 属性 查询那个接口的方法获取数据
                    one = @One(select = "com.itheima.mapper.UserMapper.findById") //
            )

    })
    List<Order> findAll();

}

2.3 测试代码

如果遇到时间戳的问题可以弄一个日期转化器弄成long格式存入数据库中
参考链接: https://blog.csdn.net/qq_45056135/article/details/127261188

https://blog.csdn.net/qq_45056135/article/details/127261188

import com.itheima.domain.Order;
import com.itheima.mapper.OrderMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTestOrder {

    private OrderMapper mapper;

    @Before
    public void before() throws IOException {
        System.out.println("测试方法之前执行.......");

        String resource = "sqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(OrderMapper.class);
    }

    @Test
    public void testFindAllOrder(){

        List<Order> orderList = mapper.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }
}

3. 一对多查询(注解实现)

需求: 查询一个用户，与此同时查询出该用户具有的订单

3.1 添加User类中的属性

添加属性之后添加set&get方法重写toString方法

//    描述当前用户具有的订单
private List<Order> orderList;

3.2 编写OrderMapper接口和UserMapper接口

OrderMapper接口
思路: 先查询user表然后通过uid作为参数通过findByUid方法查询uid用户所有的订单

@Select("select * from orders where uid = #{uid}")
List<Order> findByUid(int uid);

UserMapper接口

// 先查询user表 然后通过uid作为参数  通过findByUid方法查询uid用户所有的订单
@Select("select * from user")
@Results({
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "username", property = "username"),
        @Result(column = "password", property = "password"),
        @Result(
                property = "orderList",
                column = "id",
                javaType = List.class,
                many =  @Many(select = "com.itheima.mapper.OrderMapper.findByUid")
        )
})
List<User> findUserAndOrderAll();

3.3 测试代码

import com.itheima.domain.User;
import com.itheima.mapper.UserMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisTestUser {
    private UserMapper mapper;

    @Before
    public void before() throws IOException {
        System.out.println("测试方法之前执行.......");

        String resource = "sqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testFindUserAndOrderAll(){
        List<User> userList = mapper.findUserAndOrderAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }


}

4. 多对多查询(注解实现)

需求：查询用户同时查询出该用户的所有角色

4.1 添加User类中的属性创建Role实体类

添加属性之后添加set&get方法重写toString方法

//表示当前用户具备那些角色
private List<Role> roleList;

创建Role实体类

public class Role {
    private int id;
    private String roleName;
    private String roleDesc;

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

    public int getId() {
        return id;
    }

    public void setId(int 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;
    }
}

4.2 编写UserMapper接口和RoleMapper接口

RoleMapper接口

public interface RoleMapper {
	// 查询uid所对应的角色 信息
    @Select("select * from sys_role r, sys_user_role ur where r.id = ur.roleId and ur.userId = #{uid}")
    List<Role> findByUid(int uid);

}

UserMapper接口

// 思路: 先查询user表 通过得到的uid 去查询所对应的角色
@Select("select * from user")
@Results({
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "username", property = "username"),
        @Result(column = "password", property = "password"),
        @Result(
                property = "roleList",      // //要封装的属性名称
                column = "id",      // 方法传入的参数
                javaType = List.class, // 要封装的实体类型
                many =  @Many(select = "com.itheima.mapper.RoleMapper.findByUid")
        )
})
List<User> findUserAndRoleAll();

4.3 测试方法

public class MybatisTestUser {
    private UserMapper mapper;

    @Before
    public void before() throws IOException {
        System.out.println("测试方法之前执行.......");

        String resource = "sqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test // 一对多
    public void testFindUserAndOrderAll(){
        List<User> userList = mapper.findUserAndOrderAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test // 多对多
    public void testFindUserAndRoleAll(){
        List<User> userList = mapper.findUserAndRoleAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

}