MyBatis (IDEA version) of Java (a skilled series of articles) Add/Remove Check (Note Development) - All Points of Knowledge (Daquan)

Last article: MyBatis (IDEA version) of Java (a skilled series of articles) Add/Remove Check (XML Development) - All Knowledge Points (Daquan)

1. Common Notes for MyBatis

Annotation development has become more and more popular over the years. Mybatis can also use annotation development.
This way we can reduce the number of Mapper mapping files that we write.

We'll learn about some basic CRUD s before learning about complex mapping multitable operations.

@Insert: Achieving additions
@Update: Implement Updates
@Delete: Implement Delete
@Select: Implement Query
@Result: Implement result set encapsulation
@Results: Can be used with@Result Use together to encapsulate multiple result sets
@One: Implement one-to-one result set encapsulation
@Many: Implement one-to-many result set encapsulation

1. Engineering Construction

(1) Create data

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', 'Teacher', 'teaching');
INSERT INTO `role` VALUES ('2', 'Student', 'Go to school');
INSERT INTO `role` VALUES ('3', 'Headmaster', 'Manage class affairs');

-- ----------------------------
-- 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', 'test data', '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) Create Maven Project



(3) Introducing dependency

<?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) Create related profiles

  • 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>


    <!--adopt properties Label Load Outside properties file-->
    <properties resource="jdbc.properties"></properties>

    <!--Custom Alias-->
    <typeAliases>
        <typeAlias type="cn.itbluebox.domain.User" alias="user"></typeAlias>
    </typeAliases>

    <!--Data Source Environment-->
    <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>

    <!--Load Mapping Relationships-->
    <mappers>
        <!--Specify the package where the interface is located-->
        <package name="cn.itbluebox.mapper"></package>
    </mappers>


</configuration>

(5) Create package structure


(6) Create entity classes

  • 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 +
                '}';
    }
}

  • Modify Order
package cn.itbluebox.domain;

import java.util.Date;

public class Order {

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

    //Which user does the current order belong to
    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. The class MyDateTypeHandler with processing time is introduced.

Because the time in the database is long data, if you use datetime, you do not need to

  • Create 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> {
    //Convert Java types to the types required by the database
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);
    }
    //Convert types in data to java types
    //Field name to convert for String type
    //ResultSet Queried Result Set
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        //Get the data needed in the result set (long) and convert it to Date
        long aLong = resultSet.getLong(s);
        Date date = new Date(aLong);
        return date;
    }
    //Convert types in the database to Java types
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long aLong = resultSet.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
    //Convert types in the database to Java types
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}

  • typeHandlers tag
    <!--Register Type Processor-->
    <typeHandlers>
        <typeHandler handler="cn.itbluebox.handler.MyDateTypeHandler"></typeHandler>
    </typeHandlers>

3. Annotation replaces the mapping file, so all we need to do is load the Mapper interface that uses annotations

In the configuration file above and configured, here is a simple explanation

4. Use of User's Additions and Deletions Review Notes

4.1 Increase

(1) Create UserMapper


Implement code to add 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) Create test classes for 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 Method generates an object and calls it in @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("test data");
        user.setPassword("123");
        user.setBirthday(new Date());
        usermapper.save(user);
    }


}

Run tests


Insert Successful

4.2 Modification

(1) Create an update in UserMapper

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

(2) Testing method testUpdate for MyBatis TestUser

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

Run tests
Successful modification

4.3 Delete

(1) Create delete in UserMapper

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

(2) Testing method testDelete for MyBatis TestUser

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

Run tests

(3) For the sake of the following demonstration, we are inserting a data field with id 1

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

Run tests

4.4 Query one

(1) Create findById in UserMapper

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

(2) Testing method testFindById for MyBatis TestUser

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

    }

Run tests

4.5 Query All

(1) Create findAll in UserMapper

    @Select("select * from user")
    public List<User> findAll();
(2) Testing method testFindAll in MyBatis TestUser

    @Test
    public void testFindAll() throws IOException{

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

Run tests

5. One-to-one query

(1) Model of one-to-one query

The relationship between the user table and the order table is that a user has multiple orders.
An order belongs to only one user
Requirements for one-to-one queries:
Query an order and at the same time query the user to whom the order belongs

(2) Statements for one-to-one queries

Corresponding sql statement:

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

The uid field in Order is used here, so we need to modify the Order table to add the uid field


Set up some data

The user_order intermediate table is temporarily unused

(3) Create the OrderMapper interface


Configure Mapper with annotations to create a findAll method

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) Create test classes


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 Method generates an object and calls it in @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);
        }

    }

}

Run tests

6. One-to-many queries

(1) One-to-many query model

The relationship between the user table and the order table is that a user has multiple orders and an order belongs to only one user
Requirement for one-to-many queries: Query a user and at the same time query the orders that the user has

(2) Statements for one-to-many queries

Corresponding sql statement:
select * from user;
select * from order where uid=Query out the user's id;
The results of the query are as follows:

(3) Modify User entity

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;
    //Which orders are available on behalf of the current user
    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) Create OrderMapper interface findByUid method

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

(5) Create UserMapper interface findAllUserAndOrder method

    @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) TesFindAllUserAndOrder in MyBatis TestUser

    @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("------------------");
        }
    }

Run tests

7. Many-to-many queries

(1) Model of many-to-many queries

The relationship between user table and role table is that a user has multiple roles and a role is used by multiple users
Requirements for many-to-many queries: Querying a user simultaneously queries all roles of that user

(2) Statements of many-to-many queries

Corresponding sql statement:

select * from user;
select * from role r,user_role ur 
where r.id=ur.role_id and ur.user_id=User's id

The results of the query are as follows:

(3) Add RoleMapper interface method

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) Configure UserMapper with annotations


    @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) TesFindAllUserAndRole in MyBatis TestUser

   @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("-----------------");
        }
    }

Run tests

Tags: Java Mybatis Spring IDEA

Posted on Sun, 19 Sep 2021 04:26:54 -0400 by qumar