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