Java Maven：spring boot + Mybatis连接MySQL，通用mapper的增删改查，映射实现多表查询...

最新推荐文章于 2022-07-19 10:33:01 发布

weixin_33976072

最新推荐文章于 2022-07-19 10:33:01 发布

阅读量246

点赞数

文章标签： java 数据库 json

原文链接：http://www.cnblogs.com/rosa-king/p/10039156.html

版权

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

角色表role
表role
用户表user
表user

2. 添加依赖，配置属性

(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），区别如下：
GET和POST两种基本请求方法的区别（他讲的比我讲的有趣）
PS：命名不规范，不要学我。

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

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

    }
}

service调用mapper实现功能

(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所在包名
在浏览器填写对应的url进行访问测试，推荐使用postman，传参较为方便

转载于:https://www.cnblogs.com/rosa-king/p/10039156.html

weixin_33976072

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Java Maven：spring boot + Mybatis连接MySQL，通用mapper的增删改查，映射实现多表查询...

1. MySQL自带库test添加表user、role角色表role用户表user2. 添加依赖，配置属性相关依赖：百度即可，此处略application.propertiesspring.application.name=clean-exeserver.port=8845server.main.class=com.cdqd.app.main.Applicationeur...
复制链接

扫一扫