Java Maven：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），区别如下：
  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，传参较为方便

如对本文有疑问， 点击进行留言回复！！