2020苦瓜伦理片七仙

当前位置: 移动技术网 > IT编程>开发>Java > Java Maven:spring boot + Mybatis连接MySQL,通用mapper的增删改查,映射实现多表查询

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

2  人参与 | 时间:2018-11-30 | 我要评论

请记住本站备用网址:,收藏本站链接地址:https://www.461116.com/index.html

常鹏本,彩酷酷预测,小江开心吧

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

角色表role
表role
用户表user
表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]/[控制器名]
  • 2020苦瓜伦理片七仙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用于映射多表查询,使用映射文件操作流程为:
  • 2020苦瓜伦理片七仙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),区别如下:
    (他讲的比我讲的有趣)
  • 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);

    }
}
  • 2020苦瓜伦理片七仙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,传参较为方便

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复2020苦瓜伦理片七仙

2020苦瓜伦理片七仙相关的文章:

◎已有 0 人评论

移动技术网