Java Maven：spring boot + Mybatis连接MySQL，通用mapper的增删改查，映射实现多表查询...-优快云博客

MySQL自带库test添加表user、role

角色表role

用户表user

添加依赖，配置属性

相关依赖：百度即可，此处略

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省略，修改后貌似不需要此实体类

通用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：命名不规范，不要学我。

映射文件实现多表查询

(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

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

本群提供免费的学习指导架构资料以及免费的解答

不懂得问题都可以在本群提出来之后还会有直播平台和讲师直接交流噢

哦对了，喜欢就别忘了关注一下哦~