maven对mysql数据库增删改查_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 extends Mapper, MySqlMapper {

}

(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 {

List 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 list = userMapper.select(user);

return list;

//return selsetOne.toString();

}

//根据id查询user表和role表

@RequestMapping("/select/{id}")

public Object searchUserAndRoleById(@PathVariable int id){

List 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 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 getUserRoleByUserId(int id) {

return userMapper.getUserRoleByUserId(id);

}

}

service调用mapper实现功能

(2). mapper映射文件

userRoleMapper.xml

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

u.r_id = r.role_id

and u.id = #{id,jdbcType=INTEGER}

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，传参较为方便