mybatis进行增删改查（跨数据库）crud

凌零落

已于 2024-04-13 12:03:34 修改

阅读量105

点赞数 2

文章标签： mybatis java 开发语言

于 2024-04-13 12:02:38 首次发布

本文链接：https://blog.csdn.net/m0_70877232/article/details/137711428

版权

对数据库，进行增加删除修改查找+一对多

1. 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TbOrder implements Serializable {
    private static final long serialVersionUID = -20242147533909159L;
    /**
     * 订单id
     */
    private Long id;

    private Long userId;
    /**
     * 商品名称
     */
    private String name;
    /**
     * 商品价格
     */
    private Long price;
    /**
     * 商品数量
     */
    private Integer num;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TbUser implements Serializable {
    private static final long serialVersionUID = 589108997460815304L;
    private Long id;
    /**
     * 收件人
     */
    private String username;
    /**
     * 地址
     */
    private String address;

}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class TbUserDto extends TbUser implements Serializable {

    private List<TbOrder> orderDetailList;
}

2. controller

@RestController
@RequestMapping("tbUser")
public class TbUserController {
    /**
     * 服务对象
     */
    @Resource
    private TbUserService tbUserService;

    /**
     * 全查询
     *
     * @param tbUser 筛选条件
     * @return 查询结果
     */
    @GetMapping
    public List<TbUser> queryAll(TbUser tbUser) {
        return tbUserService.queryAll(tbUser);
    }

    /**
     * 通过主键查询单条数据
     *
     * @param id 主键
     * @return 单条数据
     */
    @GetMapping("/{id}")
    public TbUser queryById(@PathVariable Long id) {
        return tbUserService.queryById(id);
    }

    /**
     * 根据用户id查询用户信息，以及该用户的订单信息
     *
     * @param id
     * @return
     */
    @GetMapping("/other/{id}")
    public List<TbUserDto> queryUserOrders(@PathVariable Long id) {

        return tbUserService.queryUserOrders(id);
    }

    /**
     * 新增数据
     *
     * @param tbUser 实体
     * @return 新增结果
     */
    @PostMapping
    public String insert(TbUser tbUser) {
        return tbUserService.insert(tbUser);


    }

    /**
     * 修改数据
     *
     * @param tbUser 实体
     * @return 编辑结果
     */
    @PutMapping
    public String update(TbUser tbUser) {
        return tbUserService.update(tbUser);

    }

    /**
     * 删除数据
     *
     * @param id 主键
     * @return 删除是否成功
     */
    @DeleteMapping("/{id}")
    public String deleteById(@PathVariable Long id) {
        return tbUserService.deleteById(id);
    }

}

3. service

@Service
@RequiredArgsConstructor
public class TbUserService {

    private final TbUserMapper tbUserMapper;

    public TbUser queryById(Long id) {
        return tbUserMapper.queryById(id);
    }

    public List<TbUser> queryAll(TbUser tbUser) {
        return tbUserMapper.queryAll(tbUser);
    }

    public List<TbUserDto> queryUserOrders(Long id) {
        return tbUserMapper.queryUserOrders(id);
    }

    public String insert(TbUser tbUser) {
        if (tbUser != null) {
            int insertRow = tbUserMapper.insert(tbUser);
            if (insertRow != 0) {
                return "Insert Successful";
            }
            return "Insert Fail,id is already exist";
        }

        return "Insert Fail,data is null";
    }

    public String update(TbUser tbUser) {
        if (tbUser != null) {
            int updateRow = tbUserMapper.update(tbUser);
            if (updateRow > 0) {
                return "Update Successful";
            }
            return "UpdateFail, id or param is not exist  ";
        }
        return "Update Fail, data is null";

    }

    public String deleteById(Long id) {
        if (id != null) {
            int deleteRow = tbUserMapper.deleteById(id);
            if (deleteRow > 0) {
                return "Delete Successful";
            }
            return "Delete Fail,id is not exist";
        }
        return "Delete Fail,id is null";
    }


}

4. mapper

@Mapper
public interface TbUserMapper {

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    TbUser queryById(Long id);

    /**
     * 查询指定行数据
     *
     * @param tbUser 查询条件
     * @return 对象列表
     */
    List<TbUser> queryAll(TbUser tbUser);

    /**
     * 根据用户id查询用户信息，以及该用户的订单信息
     * @param id
     * @return
     */
    List<TbUserDto> queryUserOrders(Long id);

    /**
     * 新增数据
     *
     * @param tbUser 实例对象
     * @return 影响行数
     */
    int insert(TbUser tbUser);


    /**
     * 修改数据
     *
     * @param tbUser 实例对象
     * @return 影响行数
     */
    int update(TbUser tbUser);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    int deleteById(Long id);

}

5. resource/mapper

<mapper namespace="com.niit.mapper.TbUserMapper">

    <!--查询单个-->
    <select id="queryById" resultType="TbUser">
        select id,
               username,
               address
        from tb_user
        where id = #{id}
    </select>

    <!--查询表所有数据-->
    <select id="queryAll" resultType="TbUser">
        select id,
               username,
               address
        from tb_user
    </select>

    <select id="queryUserOrders" resultMap="userOrderResultMap">
        select u.*,o.id as oid,
        o.user_id as uid,o.name,o.price,o.num
        from `cloud-user`.tb_user u
        right join `cloud-order`.tb_order o
        on u.id=o.user_id
        <where>
            <if test="id != null">
                u.id = #{id}
            </if>
        </where>
    </select>

    <resultMap id="userOrderResultMap" type="TbUserDto">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="orderDetailList" ofType="TbOrder">
            <id property="id" column="oid"/>
            <result property="userId" column="uid"/>
            <result property="price" column="price"/>
            <result property="name" column="name"/>
            <result property="num" column="num"/>
        </collection>
    </resultMap>
    <!--新增所有列-->
    <insert id="insert" parameterType="TbUser">
        insert into tb_user(username, address)
        values (#{username}, #{address})
    </insert>

    <!--通过主键修改数据-->
    <update id="update" parameterType="TbUser">
        update tb_user
        <set>
            <if test="username != null and username != ''">
                username = #{username},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
        </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById" parameterType="long">
        delete
        from tb_user
        where id = #{id}
    </delete>

</mapper>

6. application.yml

server:
  port:
    8081
spring:
  datasource:
    username: root
    password: 1234
    driver-class-name: com.mysql.cj.jdbc.Driver
    url:  jdbc:mysql://127.0.0.1:3306/cloud-user?serverTimezone=GMT%2B8&characterEncoding=utf-8
    druid:
      # 初始连接数
      initialSize: 5
      # 最小连接池数量
      minIdle: 10
      # 最大连接池数量
      maxActive: 20

#mybatis
mybatis:
  mapper-locations: classpath:mapper/*.xml #配置mybaits的映射文件
  type-aliases-package: com.niit.entity #配置实体的别名
  configuration:
    map-underscore-to-camel-case: true   #开启驼峰

7. pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.9.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.niit</groupId>
    <artifactId>homework1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>pom</packaging>
    <name>homework1</name>
    <description>homework1</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

8. 数据库创建

/*
 Navicat Premium Data Transfer

 Source Server         : mysql8.0
 Source Server Type    : MySQL
 Source Server Version : 80023
 Source Host           : localhost:3306
 Source Schema         : cloud-user

 Target Server Type    : MySQL
 Target Server Version : 80023
 File Encoding         : 65001

*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP DATABASE IF EXISTS `cloud-user`;
CREATE DATABASE IF NOT EXISTS `cloud-user`;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收件人',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, '柳岩', '湖南省衡阳市');
INSERT INTO `tb_user` VALUES (2, '文二狗', '陕西省西安市');
INSERT INTO `tb_user` VALUES (3, '华沉鱼', '湖北省十堰市');
INSERT INTO `tb_user` VALUES (4, '张必沉', '天津市');
INSERT INTO `tb_user` VALUES (5, '郑爽爽', '辽宁省沈阳市大东区');
INSERT INTO `tb_user` VALUES (6, '范兵兵', '山东省青岛市');
INSERT INTO `tb_user` VALUES (7, 'Jason', 'guangxi');
INSERT INTO `tb_user` VALUES (9, 'Foy', 'guangxi');

SET FOREIGN_KEY_CHECKS = 1;

/*
 Navicat Premium Data Transfer

 Source Server         : mysql8.0
 Source Server Type    : MySQL
 Source Server Version : 80023
 Source Host           : localhost:3306
 Source Schema         : cloud-order

 Target Server Type    : MySQL
 Target Server Version : 80023
 File Encoding         : 65001

*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP DATABASE IF EXISTS `cloud-order`;
CREATE DATABASE IF NOT EXISTS `cloud-order`;

-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `price` bigint NOT NULL COMMENT '商品价格',
  `num` int NULL DEFAULT 0 COMMENT '商品数量',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 113 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES (101, 1, 'Apple 苹果 iPhone 12 ', 699900, 1);
INSERT INTO `tb_order` VALUES (102, 2, '雅迪 yadea 新国标电动车', 209900, 1);
INSERT INTO `tb_order` VALUES (103, 3, '骆驼（CAMEL）休闲运动鞋女', 43900, 1);
INSERT INTO `tb_order` VALUES (104, 4, '小米10 双模5G 骁龙865', 359900, 1);
INSERT INTO `tb_order` VALUES (105, 5, 'OPPO Reno3 Pro 双模5G 视频双防抖', 299900, 1);
INSERT INTO `tb_order` VALUES (106, 6, '美的（Midea) 新能效 冷静星II ', 544900, 1);
INSERT INTO `tb_order` VALUES (107, 2, '西昊/SIHOO 人体工学电脑椅子', 79900, 1);
INSERT INTO `tb_order` VALUES (108, 3, '梵班（FAMDBANN）休闲男鞋', 31900, 1);
INSERT INTO `tb_order` VALUES (109, 1, 'mi su71', 200000, 2);

SET FOREIGN_KEY_CHECKS = 1;