spring boot + Mybatis + MySQL+ 通用mapper增删改查,多表查询

1. MySQL自带库test添加表user、role

  • 角色表role
    在这里插入图片描述
  • 用户表user
    在这里插入图片描述

2. 添加依赖,配置属性

相关依赖:百度即可,此处略

application.properties

spring.application.name=clean-exe

server.port=8845
server.main.class=com.cdqd.app.main.Application

eureka.client.serviceUrl.defaultZone=http://192.168.3.231:8765/eureka/,http://192.168.3.232:8765/eureka/

logging.config=classpath:logback.xml

#测试数据库
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

#mybatis&&通用Mapper
mybatis.type-aliases-package=com.cdqd.app.bean
mybatis.mapper-locations=classpath:mapper/*.xml
mapper.mappers=com.cdqd.app.common.TkMapper
mapper.identity=MYSQL
mapper.not-empty=false
  • server.port为访问端口,访问方式为:localhost:[server.port]/[控制器名]
  • spring.datasource.url后需要添加两个属性,不然会发出警告

3.新建实体类User,Role,UserRole(省略getter、setter)

user.java
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name = "user")
public class User {
    //自增ID属性
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    //名称
    @Column(name = "name")
    private String name;
    //年龄
    @Column(name = "age")
    private Integer age;
    //身份编号
    @Column(name = "card_no")
    private Integer cardNo;
    //生日
    @Column(name = "birthday")
    private Date birthday;
    //角色id
    @Column(name = "r_id")
    private int rId;
}
UserRole.java

import com.cdqd.app.bean.User;

public class UserRole extends User {
    private int roleId;
    private String roleName;
    private  int atk;
    private int armor;
    private int penetrate;
}
  • Role.java省略,修改后貌似不需要此实体类

4. 通用mapper实现user表的增删改查

(1). 写一个自己的接口继承通用mapper,此接口不能被扫描到

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface TkMapper<T> extends Mapper<T>, MySqlMapper<T> {
}

(2). 添加UserMapper继承之前写的mapper,用于自己使用

import com.cdqd.app.entity.UserRole;
import com.cdqd.app.bean.User;
import com.cdqd.app.common.TkMapper;

import java.util.List;

public interface UserMapper extends TkMapper<User> {
    List<UserRole> getUserRoleByUserId(int id);
}
  • 注:此mapper需要被扫描到
  • getUserRoleByUserId用于映射多表查询,使用映射文件操作流程为:controller调用service,service调用mapper

(3). 编写controller

  • SqlTestController.java
import com.cdqd.app.bean.User;
import com.cdqd.app.entity.UserRole;
import com.cdqd.app.mapper.UserMapper;
import com.cdqd.app.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

@RestController
public class SqlTestController {
    //自动注入,一般放在顶部
    @Autowired
    UserMapper userMapper;

    @Autowired
    private UserService userService;

    //测试controller用
    @PostMapping("/hello")
    public String hello(String yourName) {
        return "Hello," + yourName;
    }

    //根据cardNo查询一条数据
    //只能用于主键查找,不用于主键的查找没试过
    //Get请求,查找user信息
    @GetMapping("/select_user_info/{id}")
    public Object searchOneDataByCardNo(@PathVariable int id) {
        User user = new User();
        user.setId(id);
        List<User> list = userMapper.select(user);
        return list;
        //return selsetOne.toString();
    }

    //根据id查询user表和role表
    @RequestMapping("/select/{id}")
    public Object searchUserAndRoleById(@PathVariable int id){
        List<UserRole> listUser =userService.getUserRoleByUserId(id);
        return listUser;
    }

   //插入一条数据
    @PostMapping("/insert")
    public String insertOneDate(@RequestParam String name,
                                @RequestParam String age, 
                                @RequestParam String cardNo,
                                @RequestParam String sDate,
                                @RequestParam int roleId) {
        try {
            User user = new User();
            user.setName(name);
            user.setAge(Integer.parseInt(age));
            user.setCardNo(Integer.parseInt(cardNo));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            user.setBirthday(sdf.parse(sDate));
            user.setrId(roleId);
            userMapper.insertSelective(user);
            return "插入数据成功:" + user.toString();
        } catch (ParseException e) {
            e.printStackTrace();
            return "ERROR!";
        }
    }

    //用姓名删除数据
    @PostMapping("/deleteByName")
    public String deleteByName(String name) {
        User user = new User();
        user.setName(name);
        userMapper.delete(user);
        return "删除成功";
    }

    //按主键删除
    @PostMapping("/deleteById")
    public String deleteById(String id) {
        userMapper.deleteByPrimaryKey(Integer.parseInt(id));
        return "删除成功";
    }

   //更新数据
    @PostMapping("/updateById")
    public String updateById(@RequestParam String id,
                             @RequestParam String name,
                             @RequestParam int age, 
                             @RequestParam int cardNo, 
                             @RequestParam String sDate,
                             @RequestParam int roleId) {
        try {
            User user = new User();
            user.setId(Integer.parseInt(id));
            user.setName(name);
            user.setAge(age);
            user.setCardNo(cardNo);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            user.setBirthday(sdf.parse(sDate));
            user.setrId(roleId);
            userMapper.updateByPrimaryKeySelective(user);
            return "更新完成:" + user.toString();
        } catch (ParseException e) {
            e.printStackTrace();
            return "ERROR!";
        }
    }
}
  • 最好用Json返回数据,传入参数过多同样推荐使用Json,而不是String,List等;
  • controller里最好不要用try/catch,try/catch在不知道会出现什么异常时使用较为适宜,而在controller中大概了解会出现那个类型的异常;
  • 增删改时推荐使用post请求(PostMapping),查询时推荐使用get请求(GetMapping);

5. 映射文件实现多表查询

(1). service

  • UserService.java
import com.cdqd.app.entity.UserRole;

import java.util.List;

public interface UserService {
    List<UserRole> getUserRoleByUserId(int id);
}

service访问接口

  • UserServiceImpl.java
  • service调用mapper实现功能
package com.cdqd.app.service;

import com.cdqd.app.entity.UserRole;
import com.cdqd.app.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

   @Autowired
    UserMapper userMapper;
    
    @Override
    public List<UserRole> getUserRoleByUserId(int id) {
        return userMapper.getUserRoleByUserId(id);

    }
}

(2). mapper 映射文件

  • userRoleMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cdqd.app.mapper.UserMapper">
    <resultMap id="UserResultMap" type="com.cdqd.app.entity.UserRole">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="role_id" property="roleId" jdbcType="INTEGER"/>
        <result column="role_name" property="roleName" jdbcType="VARCHAR"/>
        <result column="atk" property="atk" jdbcType="INTEGER"/>
        <result column="armor" property="armor" jdbcType="INTEGER"/>
        <result column="penetrate" property="penetrate" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="age" property="age" jdbcType="INTEGER"/>
        <result column="card_no" property="cardNo" jdbcType="INTEGER"/>
        <result column="birthday" property="birthday" jdbcType="DATE"/>
        <result column="r_id" property="rId" jdbcType="INTEGER"/>
    </resultMap>
    <!--查询-->
    <select id="getUserRoleByUserId" parameterType="java.lang.Integer" resultMap="UserResultMap">
        select
        u.*,
        r.role_id as role_id,
        r.role_name as role_name,
        r.atk as atk,
        r.armor as armor,
        r.penetrate as penetrate
        from user u,role r
        <where>
            u.r_id = r.role_id
            and u.id = #{id,jdbcType=INTEGER}
        </where>
    </select>
</mapper>
  • mapper:namespace填写所对应的mapper路径
  • resultMap:id为唯一主键;column(列) 为数据库中列名,property(属性)为Java实体类中属性名
  • select:id的值为对应mapper的对应方法名;parameterType参数类型;resultMap和上面相结合操作需要的数据

6.程序入口

Application.java
import com.cdqd.app.common.WebUtils;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import tk.mybatis.spring.annotation.MapperScan;

import javax.servlet.http.HttpServletRequest;

@SpringBootApplication
@EnableEurekaClient
@RestController
@MapperScan("com.cdqd.app.mapper" )
@ComponentScan(basePackages="com.cdqd.app")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}
  • MapperScan用于扫描mapper,此处填了UserMapper所在包名
好的,下面是示例代码: 首先,需要配置pom.xml文件,引入相应的依赖: ```xml <!-- Spring Boot 依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- MyBatis-Plus 依赖 --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.1</version> </dependency> <!-- MySQL 驱动依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.26</version> </dependency> ``` 接着,配置application.properties文件,配置数据库相关信息: ```properties # 数据库配置 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false spring.datasource.username=root spring.datasource.password=root # MyBatis-Plus 配置 mybatis-plus.mapper-locations=classpath:mapper/*.xml mybatis-plus.type-aliases-package=com.example.demo.entity mybatis-plus.global-config.db-config.id-type=auto ``` 其中,mapper/*.xml是指mapper目录下的所有xml文件都会被MyBatis-Plus自动扫描,并注册到Spring容器中。 下面,创建实体类和Mapper接口: ```java // User实体类 @Data @AllArgsConstructor @NoArgsConstructor public class User { private Long id; private String name; private Integer age; private String email; } // UserMapper接口 @Mapper public interface UserMapper extends BaseMapper<User> { } ``` 其中,@Mapper注解示该接口是一个Mapper接口,并由MyBatis-Plus自动实现其方法。 接下来,编写Controller层的增删改查方法: ```java @RestController @RequestMapping("/user") public class UserController { @Autowired private UserMapper userMapper; // 查询所有用户 @GetMapping("/list") public List<User> userList() { return userMapper.selectList(null); } // 根据id查询用户 @GetMapping("/{id}") public User getUserById(@PathVariable("id") Long id) { return userMapper.selectById(id); } // 添加用户 @PostMapping("/add") public String addUser(@RequestBody User user) { int rows = userMapper.insert(user); return rows > 0 ? "success" : "fail"; } // 修改用户 @PutMapping("/update") public String updateUser(@RequestBody User user) { int rows = userMapper.updateById(user); return rows > 0 ? "success" : "fail"; } // 根据id删除用户 @DeleteMapping("/{id}") public String deleteUserById(@PathVariable("id") Long id) { int rows = userMapper.deleteById(id); return rows > 0 ? "success" : "fail"; } } ``` 其中,@GetMapping、@PostMapping、@PutMapping、@DeleteMapping注解分别示对应的HTTP请求方法。 最后,启动Spring Boot应用,访问http://localhost:8080/user/list即可查询所有用户,访问http://localhost:8080/user/1即可根据id查询用户。其他增删改的操作也类似。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值