mybatis不重复列插入例子详细说明

之前有做过不重复列插入的需求，当时是在插入时判断对应的列在数据库中有没有对应的数据有则返回false 无则插入，但是这加大了数据库的查询负担也增加了插入的时间，故今天研究一下使用sql来简化了这一点

使用的知识点是 daul表

insert into tb_account(rname) select '非管理员' 
WHERE not exists 
(SELECT id FROM sys_role WHERE rname='非管理员');

1.表结构

-- newTechnologyTest.tb_account definition
CREATE TABLE `tb_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int NOT NULL,
  `birthday` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` int NOT NULL DEFAULT '1' COMMENT '0非正常 1 正常',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO newTechnologyTest.tb_account (user_name,age,birthday,status) VALUES
	 ('张三',18,'2020-01-11 00:00:00',1),
	 ('李四',19,'2021-03-21 00:00:00',1),
	 ('李六10',23,'2024-03-05 12:26:35',1),
	 ('李六11',23,'2024-03-05 12:28:43',1);

在这里插入图片描述

使用mybatis 自定义的sql来实现功能

    @Insert("<script>" +
    "insert into tb_account(" +
    "<if test=\"userName != null and userName != '' \">user_name,</if>" +
    "<if test=\"age != null and age != '' \">age,</if>" +
    "<if test=\"birthday != null and birthday != '' \">birthday,</if>" +
    "<if test=\"status != null and status != '' \">status,</if>" +
    "id) " +
    "select " +
    "<if test=\"userName != null and userName != '' \">#{userName},</if>" +
    "<if test=\"age != null and age != '' \">#{age},</if>" +
    "<if test=\"birthday != null and birthday != '' \">#{birthday},</if>" +
    "<if test=\"status != null and status != '' \">#{status},</if>" +
    " #{id} " +
    "WHERE not exists " +
//             重复列查找
    "(SELECT id FROM tb_account WHERE user_name=#{userName} and age = #{age})" +
    "</script>")
    boolean insertNoRepeat(Account account);
    // 注意一定要判断是否为null “”  因为在输入数据时不一定是全列的而且一般表都有默认值列

调用此方法实现功能(当前本人使用的框架是 mybatisFlex)

Account account2 = new Account();
        account2.setUserName("李六11");
        account2.setAge(23);
        System.out.println(account2);
        System.out.println(accountMapper.insertNoRepeat(account2));