Java之MyBatis【IDEA版】(一篇文章精通系列)增删改查【注解开发】 - 所有知识点(大全)

x33g5p2x  于2021-09-19 转载在 Java  
字(19.0k)|赞(0)|评价(0)|浏览(385)

**上一篇文章:**Java之MyBatis【IDEA版】(一篇文章精通系列)增删改查【XML开发】 - 所有知识点(大全

一、MyBatis的常用注解

这几年来注解开发越来越流行,Mybatis也可以使用注解开发方式,
这样我们就可以减少编写Mapper映射文件了。

我们先围绕一些基本的CRUD来学习,再学习复杂映射多表操作。

@Insert:实现新增
@Update:实现更新
@Delete:实现删除
@Select:实现查询
@Result:实现结果集封装
@Results:可以与@Result 一起使用,封装多个结果集
@One:实现一对一结果集封装
@Many:实现一对多结果集封装

1、工程搭建

(1)创建数据

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `order`
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` int(11) NOT NULL,
  `ordertime` bigint(60) NOT NULL,
  `total` double(60,0) NOT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('1', '1631783536660', '1', '1');
INSERT INTO `order` VALUES ('2', '1631783536660', '2', '1');
INSERT INTO `order` VALUES ('3', '1631783536660', '1', '2');

-- ----------------------------
-- Table structure for `role`
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `roleName` varchar(50) NOT NULL,
  `roleDesc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '老师', '教学');
INSERT INTO `role` VALUES ('2', '学生', '上学');
INSERT INTO `role` VALUES ('3', '班主任', '管理班级事务');

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `birthday` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'ssss', '123', '1631947408458');
INSERT INTO `user` VALUES ('2', 'eeee', 'sasass', '1631783536660');
INSERT INTO `user` VALUES ('3', '3sasas', 'rerere', '1631783536660');
INSERT INTO `user` VALUES ('4', 'gffdfdf', 'ddwdwd', '1631783536660');
INSERT INTO `user` VALUES ('5', 'sasasa', 'sasa', '1631783536660');
INSERT INTO `user` VALUES ('6', 'ceshi', 'abc', '1631783536660');
INSERT INTO `user` VALUES ('7', '测试数据', '123', '1631945928626');

-- ----------------------------
-- Table structure for `user_role`
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES ('1', '1', '1');
INSERT INTO `user_role` VALUES ('2', '1', '2');
INSERT INTO `user_role` VALUES ('3', '2', '2');
INSERT INTO `user_role` VALUES ('4', '3', '3');
(2)创建Maven工程

(3)引入依赖

<?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>cn.itbluebox</groupId>
    <artifactId>mybatis</artifactId>
    <version>1.0.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.32</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

</project>
(4)创建相关配置文件
  • jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test2
jdbc.username=root
jdbc.password=root
  • log4j.properties

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
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=debug, stdout
  • sqlMapConfig.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文件-->
    <properties resource="jdbc.properties"></properties>

    <!--自定义别名-->
    <typeAliases>
        <typeAlias type="cn.itbluebox.domain.User" alias="user"></typeAlias>
    </typeAliases>

    <!--数据源环境-->
    <environments default="developement">
        <environment id="developement">
            <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>

    <!--加载映射关系-->
    <mappers>
        <!--指定接口所在的包-->
        <package name="cn.itbluebox.mapper"></package>
    </mappers>

</configuration>
(5)创建包结构

(6)创建实体类
  • Order

package cn.itbluebox.domain;

import java.util.Date;

public class Order {

    private int id;
    private Date ordertime;
    private double total;

    public Order() {
    }

    public Order(int id, Date ordertime, double total) {
        this.id = id;
        this.ordertime = ordertime;
        this.total = total;
    }

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

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

package cn.itbluebox.domain;

public class Role {

    private int id;
    private String roleName;
    private String roleDesc;

    public Role() {
    }

    public Role(int id, String roleName, String roleDesc) {
        this.id = id;
        this.roleName = roleName;
        this.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;
    }

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

package cn.itbluebox.domain;

import java.util.Date;
import java.util.List;

public class User {

    private int id;
    private String username;
    private String password;
    private Date birthday;
    private List<Role> roleList;

    public User() {
    }

    public User(int id, String username, String password, Date birthday, List<Role> roleList) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.birthday = birthday;
        this.roleList = roleList;
    }

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

    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 + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", roleList=" + roleList +
                '}';
    }
}
  • 修改Order

package cn.itbluebox.domain;

import java.util.Date;

public class Order {

    private int id;
    private Date ordertime;
    private double total;

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

    public Order() {
    }

    public Order(int id, Date ordertime, double total, User user) {
        this.id = id;
        this.ordertime = ordertime;
        this.total = total;
        this.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、引入处理时间的类MyDateTypeHandler ,

因为数据库当中的时间使用的是long类型的数据,如果您使用的datetime则不需要

  • 创建MyDateTypeHandler

package cn.itbluebox.handler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class MyDateTypeHandler extends BaseTypeHandler<Date> {
    //将Java 类型转换为数据库需要的类型
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);
    }
    //将数据中的类型 转换为java类型
    //String 类型 要转换的字段名称
    //ResultSet 查询出的结果集
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        //获得结果集中需要的数据(long)转换为Date
        long aLong = resultSet.getLong(s);
        Date date = new Date(aLong);
        return date;
    }
    //将数据库当中的类型 转换为Java的类型
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long aLong = resultSet.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
    //将数据库中的类型 转换为Java类型
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}
  • typeHandlers标签

<!--注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="cn.itbluebox.handler.MyDateTypeHandler"></typeHandler>
    </typeHandlers>

3、注解替代的映射文件,所以我们只需要加载使用了注解的Mapper接口即可

在上面的配置文件当中以及配置好了,在这里只做简单的说明

4、User的增删改查注解使用

4.1 增加
(1)创建UserMapper

实现添加User的代码

package cn.itbluebox.mapper;

import cn.itbluebox.domain.User;
import org.apache.ibatis.annotations.Insert;

public interface UserMapper {

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

}
(2)创建MyBatisTestUser的测试类

package cn.itbluebox;

import cn.itbluebox.domain.User;
import cn.itbluebox.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.Date;

public class MyBatisTestUser {

    /* @Before方法生成一个对象,然后在@Test里调用。 */
    private UserMapper usermapper;
    @Before
    public void before() throws IOException{
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        usermapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testAdd(){
        User user = new User();
        user.setUsername("测试数据");
        user.setPassword("123");
        user.setBirthday(new Date());
        usermapper.save(user);
    }

}

运行测试

插入成功

4.2 修改
(1)在UserMapper当中创建update

@Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);
(2)在MyBatisTestUser的测试方法testUpdate

@Test
    public void testUpdate() throws IOException{
        User user = new User();
        user.setId(1);
        user.setUsername("张三改");
        user.setPassword("321");
        usermapper.update(user);
    }

运行测试
修改成功

4.3 删除
(1)在UserMapper当中创建delete

@Delete("delete from user where id = #{id}")
    public void delete(int id);
(2)在MyBatisTestUser的测试方法testDelete

@Test
    public void testDelete() throws IOException{
        usermapper.delete(1);
    }

运行测试

(3)为了后面的演示正常我们在插入id为1的数据字段

@Test
    public void testAdd(){
        User user = new User();
        user.setId(1);
        user.setUsername("莎莎");
        user.setPassword("123");
        user.setBirthday(new Date());
        usermapper.save(user);
    }

运行测试

4.4 查询一个
(1)在UserMapper当中创建findById

@Select("select * from user where id = #{id}")
    public User findById(int id);
(2)在MyBatisTestUser的测试方法testFindById

@Test
    public void testFindById() throws  IOException{
        User user = usermapper.findById(1);
        System.out.println(user);

    }

运行测试

4.5 查询所有
(1)在UserMapper当中创建findAll

@Select("select * from user")
    public List<User> findAll();
(2)在MyBatisTestUser的测试方法testFindAll

@Test
    public void testFindAll() throws IOException{

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

运行测试

5、 一对一查询

(1)一对一查询的模型

用户表和订单表的关系为,一个用户有多个订单,
一个订单只从属于一个用户
一对一查询的需求:
查询一个订单,与此同时查询出该订单所属的用户

(2)一对一查询的语句

对应的sql语句:

select * from user u,`order` o where u.id = o.uid

在这里使用的是Order当中的uid字段所以我们需要修改Order表添加uid字段

设置一些数据

user_order中间表暂时不用

(3)创建OrderMapper接口

使用注解配置Mapper,创建findAll方法

package cn.itbluebox.mapper;

import cn.itbluebox.domain.Order;
import cn.itbluebox.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;

public interface OrderMapper {

    @Select("select * from `order`")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "ordertime", column = "ordertime"),
            @Result(property = "total",column = "total"),
            @Result(property = "user",
                    column = "uid" ,
                    javaType = User.class,
                    one = @One(select = "cn.itbluebox.mapper.UserMapper.findById"))
    })
    public List<Order> findAll();

}
(4)创建测试类

package cn.itbluebox;

import cn.itbluebox.domain.Order;
import cn.itbluebox.mapper.OrderMapper;
import cn.itbluebox.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 MyBatisTestOrder {

    /* @Before方法生成一个对象,然后在@Test里调用。 */
    private OrderMapper orderMapper;
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        orderMapper = sqlSession.getMapper(OrderMapper.class);
    }
    @Test
    public void testSelectOrderAndUser(){

        List<Order> orders = orderMapper.findAll();

        for (Order order : orders) {
            System.out.println(order);
        }

    }

}

运行测试

6、一对多查询

(1) 一对多查询的模型

用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单

(2) 一对多查询的语句

对应的sql语句:
select /* from user;
select /* from order where uid=查询出用户的id;
查询的结果如下:

(3)修改User实体

package cn.itbluebox.domain;

import java.util.Date;
import java.util.List;

public class User {

    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
    private List<Role> roleList;

    public User() {
    }

    public User(int id, String username, String password, Date birthday, List<Order> orderList, List<Role> roleList) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.birthday = birthday;
        this.orderList = orderList;
        this.roleList = roleList;
    }

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

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    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 + '\'' +
                ", password='" + password + '\'' +
                ", birthday=" + birthday +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                '}';
    }
}
(4)创建OrderMapper 接口findByUid方法

@Select("select * from `order` where uid= # {uid}")
    List<Order> findByUid(int uid);
(5)创建UserMapper 接口findAllUserAndOrder方法

@Select("select * from user")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "orderList",column = "id" ,
                    javaType = List.class,
                    many = @Many(select = "cn.itbluebox.mapper.OrderMapper.findByUid"))
    })
    List<User> findAllUserAndOrder();
(6)在MyBatisTestUser当中的testFindAllUserAndOrder

@Test
    public void testFindAllUserAndOrder() throws  IOException{
        List<User> allUserAndOrder = usermapper.findAllUserAndOrder();
        for (User user : allUserAndOrder) {
            System.out.println(user.getUsername());
            List<Order> orderList = user.getOrderList();
            for (Order order : orderList) {
                System.out.println(order);
            }
            System.out.println("------------------");
        }
    }

运行测试

7、多对多查询

(1)多对多查询的模型

用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求:查询用户同时查询出该用户的所有角色

(2) 多对多查询的语句

对应的sql语句:

select * from user;
select * from role r,user_role ur 
where r.id=ur.role_id and ur.user_id=用户的id

查询的结果如下:

(3)添加RoleMapper 接口方法

package cn.itbluebox.mapper;

import cn.itbluebox.domain.Role;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface RoleMapper {

    @Select("select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=#{uid}")
    List<Role> findByUid(int id);

}
(4)使用注解配置UserMapper

@Select("select * from user")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "roleList",column = "id",
                javaType = List.class,
                    many = @Many(select = "cn.itbluebox.mapper.RoleMapper.findByUid")
            ),
    })
    List<User> findAllUserAndRole();
(5)MyBatisTestUser当中testFindAllUserAndRole

@Test
    public void testFindAllUserAndRole() throws IOException{
        List<User> allUserAndRole = usermapper.findAllUserAndRole();

        for (User user : allUserAndRole) {
            System.out.println(user.getUsername());
            List<Role> roleList = user.getRoleList();
            for (Role role : roleList) {
                System.out.println(role);
            }
            System.out.println("-----------------");
        }
    }

运行测试

相关文章

微信公众号

最新文章

更多

目录