1. MySQL自带库test添加表user、role
角色表role
用户表user
2. 添加依赖，配置属性
相关依赖：百度即可，此处略
application.properties
spring.application.name=clean-exeserver.port=8845server.main.class=com.cdqd.app.main.Applicationeureka.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/testspring.datasource.username=rootspring.datasource.password=passwordspring.datasource.driver-class-name=com.mysql.jdbc.Driver#mybatis&&通用Mappermybatis.type-aliases-package=com.cdqd.app.beanmybatis.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
importjava.util.Date;importjavax.persistence.Column;importjavax.persistence.GeneratedValue;importjavax.persistence.GenerationType;importjavax.persistence.Id;importjavax.persistence.Table;@Table(name ="user")publicclassUser{//自增ID属性@Id@GeneratedValue(strategy = GenerationType.IDENTITY)privateInteger id;//名称@Column(name ="name")privateString name;//年龄@Column(name ="age")privateInteger age;//身份编号@Column(name ="card_no")privateInteger cardNo;//生日@Column(name ="birthday")privateDate birthday;//角色id@Column(name ="r_id")privateintrId;}
UserRole.java
importcom.cdqd.app.bean.User;publicclassUserRoleextendsUser{privateint roleId;privateStringroleName;privateint atk;privateint armor;privateint penetrate;}
Role.java省略，修改后貌似不需要此实体类
4. 通用mapper实现user表的增删改查
(1). 写一个自己的接口继承通用mapper，此接口不能被扫描到
importtk.mybatis.mapper.common.Mapper;importtk.mybatis.mapper.common.MySqlMapper;publicinterfaceTkMapperextendsMapper,MySqlMapper {}
(2). 添加UserMapper继承之前写的mapper，用于自己使用
importcom.cdqd.app.entity.UserRole;importcom.cdqd.app.bean.User;importcom.cdqd.app.common.TkMapper;importjava.util.List;publicinterfaceUserMapperextendsTkMapper{ListgetUserRoleByUserId(intid);}
注：此mapper需要被扫描到
getUserRoleByUserId用于映射多表查询，使用映射文件操作流程为：
controller调用service，service调用mapper
(3). 编写controller
SqlTestController.java
importcom.cdqd.app.bean.User;importcom.cdqd.app.entity.UserRole;importcom.cdqd.app.mapper.UserMapper;importcom.cdqd.app.service.UserService;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.bind.annotation.*;importjavax.annotation.Resource;importjava.text.ParseException;importjava.text.SimpleDateFormat;importjava.util.List;@RestControllerpublicclassSqlTestController{//自动注入，一般放在顶部@AutowiredUserMapper userMapper;@Autowiredprivate UserService userService;//测试controller用@PostMapping("/hello") publicStringhello(StringyourName) {return"Hello,"+ yourName; }//根据cardNo查询一条数据//只能用于主键查找，不用于主键的查找没试过//Get请求，查找user信息@GetMapping("/select_user_info/{id}") publicObjectsearchOneDataByCardNo(@PathVariableintid) { User user =newUser(); user.setId(id);List list = userMapper.select(user);returnlist;//return selsetOne.toString();}//根据id查询user表和role表@RequestMapping("/select/{id}") publicObjectsearchUserAndRoleById(@PathVariableintid){List listUser =userService.getUserRoleByUserId(id);returnlistUser; }//插入一条数据@PostMapping("/insert") publicStringinsertOneDate(@RequestParamStringname,@RequestParamStringage,@RequestParamStringcardNo,@RequestParamStringsDate,@RequestParamintroleId) {try{ User user =newUser(); user.setName(name); user.setAge(Integer.parseInt(age)); user.setCardNo(Integer.parseInt(cardNo)); SimpleDateFormat sdf =newSimpleDateFormat("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") publicStringdeleteByName(Stringname) { User user =newUser(); user.setName(name); userMapper.delete(user);return"删除成功"; }//按主键删除@PostMapping("/deleteById") publicStringdeleteById(Stringid) { userMapper.deleteByPrimaryKey(Integer.parseInt(id));return"删除成功"; }//更新数据@PostMapping("/updateById") publicStringupdateById(@RequestParamStringid,@RequestParamStringname,@RequestParamintage,@RequestParamintcardNo,@RequestParamStringsDate,@RequestParamintroleId) {try{ User user =newUser(); user.setId(Integer.parseInt(id)); user.setName(name); user.setAge(age); user.setCardNo(cardNo); SimpleDateFormat sdf =newSimpleDateFormat("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
importcom.cdqd.app.entity.UserRole;importjava.util.List;publicinterfaceUserService{ListgetUserRoleByUserId(intid);}
service访问接口
UserServiceImpl.java
packagecom.cdqd.app.service;importcom.cdqd.app.entity.UserRole;importcom.cdqd.app.mapper.UserMapper;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importjava.util.List;@Servicepublic class UserServiceImpl implements UserService { @AutowiredUserMapper userMapper; @Overridepublic List getUserRoleByUserId(int id) {returnuserMapper.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"><!--查询-->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 ru.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
importcom.cdqd.app.common.WebUtils;importorg.springframework.boot.SpringApplication;importorg.springframework.boot.autoconfigure.SpringBootApplication;importorg.springframework.cloud.netflix.eureka.EnableEurekaClient;importorg.springframework.context.annotation.ComponentScan;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RestController;importtk.mybatis.spring.annotation.MapperScan;importjavax.servlet.http.HttpServletRequest;@SpringBootApplication@EnableEurekaClient@RestController@MapperScan("com.cdqd.app.mapper")@ComponentScan(basePackages="com.cdqd.app")publicclassApplication{publicstaticvoid main(String[] args) {SpringApplication.run(Application.class, args); }}
欢迎工作一到八年的Java工程师朋友们加入Java高级交流群：828697593
本群提供免费的学习指导 架构资料 以及免费的解答
不懂得问题都可以在本群提出来 之后还会有直播平台和讲师直接交流噢
哦对了，喜欢就别忘了关注一下哦~
