Mybatis对数据库进行增删查改以及单元测试

这篇写的草率了，是好几天前学到，以后用来自己复习 

 UserInfo

import lombok.Data;@Data
public class UserInfo {private int id;private String name;private int age;private String email;//LocalDateTime可用于接收  时间}

Mapper 

UserMapper

package com.example.demo1014.mapper;import com.example.demo1014.entity.UserInfo;
import lombok.Data;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.*;@Mapper
public interface UserMapper {// UserInfo getUserById(Integer id);UserInfo getUserById(@Param("user_id") Integer id);//这里的参数“user_id”与resources.mybatis.UserMapper.xml里面的id的值相对应//查所有用户信息List<UserInfo> getAll();//插入信息int add(UserInfo userinfo);//添加并返回用户的自增ID：int addGetId(UserInfo userinfo);//修改操作：int upUserName(UserInfo userinfo);//删除操作：int delById(@Param("id") Integer id);List<UserInfo> getListByOrder(@Param("order") String order);//类似登录逻辑的实现————根据name和id一起查询信息,只有一个就不行;UserInfo login(@Param("name") String name,@Param("email") String email);//进行模糊查询List<UserInfo> getListByName(@Param("name") String name);int add2(UserInfo userinfo);int add3(UserInfo userinfo);List<UserInfo>   getListByParam(String name,Integer id);int update2(UserInfo userinfo);int dels(List<Integer> ids);}
/**mapper里面有接口有xml文件;* */
/**接口：* 接口中的方法都没有方法体都是抽象方法* **//**单元测试：* 1.在需要进行单元测试的类中选择generate* */

UserMapper.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.example.demo1014.mapper.UserMapper"><select id="getUserById" resultType="com.example.demo1014.entity.UserInfo">SELECT * FROM UserInfo WHERE id = #{user_id}<!--这里的#{user_id}是与Java对象中的属性对应的，而不是数据库的字段--></select><select id="getAll" resultType="com.example.demo1014.entity.UserInfo">select * from userinfo</select><insert id="add">insert into userinfo(id,name,age,email) values(#{id},#{name},#{age},#{email})</insert><!--insert默认返回值就是int--><!--返回自增id,这里的“”里面一定是程序中实体类的属性而不是数据库中的字段--><insert id="addGetId" useGeneratedKeys="true" keyProperty="id">insert into userinfo(id,name,age,email) values(#{id},#{name},#{age},#{email})</insert><update id="upUserName">update userinfo set name= #{name} where id=#{id}</update><delete id="delById">delete from userinfo where id = #{id}</delete><select id="getListByOrder" resultType="com.example.demo1014.entity.UserInfo">select* from userinfo order by id ${order}</select><select id="login" resultType="com.example.demo1014.entity.UserInfo">select* from userinfo where name='${name}' and email =#{email}</select><select id="getListByName"  resultType="com.example.demo1014.entity.UserInfo">select* from userinfo where name like  CONCAT('%', #{name}, '%')</select><!--必填和非必填的<if>标签--><insert id="add2">insert into userinfo(id,<if test="name!=null">name,</if>age) values(#{id},<if test="name!=null">#{name},</if>#{age})</insert><insert id="add3">insert into userinfo<trim prefix="(" suffix=")" suffixOverrides=","><if test="id!=null">id,</if><if test="name!=null">name,</if><if test="age!=null">age,</if><if test="email!=null">email,</if></trim> values<trim prefix="(" suffix=")" suffixOverrides=","><if test="id!=null">#{id},</if><if test="name!=null">#{name},</if><if test="age!=null">#{age},</if><if test="email!=null">#{email},</if></trim></insert><select id="getListByParam" resultType="com.example.demo1014.entity.UserInfo">select* from userinfo
<!--        <where>-->
<!--            <if test="name!=null">-->
<!--              and  name=#{name}-->
<!--            </if>-->
<!--            <if test="id!=null">-->
<!--              and  id=#{id}--><!--            </if>-->
<!--        </where>--><trim prefix="where" prefixOverrides="and"><if test="name!=null">and name=#{name}</if><if test="id!=null">and id=#{id}</if></trim></select><update id="update2">update userinfo<set><if test="id!=null">id=#{id},</if><if test="name!=null">name=#{name},</if></set>where id=#{id}</update><delete id="dels">     <!--delete from userinfo where id in ()-->delete from userinfo where id in<foreach collection="ids" open="(" close=")" item="id" separator=",">#{id}</foreach></delete></mapper>
<!--namsespace是xml实现接口的全路径（包名+接口名） id是实现的方法名  resultType是返回的类型 ＄{}是标签,用来传递参数-->

service

package com.example.demo1014.service;import com.example.demo1014.entity.UserInfo;
import com.example.demo1014.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;@Service
public class UserService {@Autowired//依赖注入private UserMapper userMapper;public UserInfo getUserById(Integer id){return userMapper.getUserById(id);}
}

controller

package com.example.demo1014.controller;import com.example.demo1014.entity.UserInfo;
import com.example.demo1014.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;@RestController//=@ResponseBody+@Controller
@RequestMapping("/user")public class UserController {@Autowiredprivate UserService userservice;@RequestMapping("/getuserbyid")public UserInfo getUserById(Integer id ){if(id==null) return null;return userservice.getUserById(id);}}

//此时就可以用postman查询了使用http://127.0.0.1:8080/user/getuserbyid?id=1
//查询到{
    "id": 1,
    "name": "John",
    "age": 25,
    "email": "[e-mail]"
}
 

 如何进行单元测试？

在UserMApper类里面，右键->generate ->Test->勾选你想要的方法

进入Test

需要注意@Transaction可开启事务，不污染数据库。

package com.example.demo1014.mapper;
/**单元测试：* 1.在需要进行单元测试的类中选择generate* */import com.example.demo1014.entity.UserInfo;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;import java.util.List;import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest//1. 表明当前单元测试是运行在SpringBoot中
//@Transactional //Spring 将会在方法执行前开启一个事务，在方法执行结束后根据方法的执行结果进行事务的提交或回滚
class UserMapperTest {@Autowired//2. 注入测试对象private UserMapper userMapper;@Testvoid getUserById() {//3. 添加单元测试的业务代码UserInfo userinfo=userMapper.getUserById(1);System.out.println(userinfo);/**使用断言->判断最终结果是否符合预期！？** */Assertions.assertEquals("John",userinfo.getName());}@Testvoid getAll() {List<UserInfo> list=userMapper.getAll();Assertions.assertEquals(8,list.size());}@Testvoid add() {UserInfo userinfo = new UserInfo();userinfo.setId(11);userinfo.setName("小龙女");userinfo.setAge(18);userinfo.setEmail("[e-mail]");int result = userMapper.add(userinfo);assertEquals(1, result); // 验证插入是否成功// sqlSession.commit(); // 提交事务}@Testvoid addGetId() {//UserInfo userinfo=new UserInfo();userinfo.setId(9);userinfo.setAge(30);userinfo.setName("小龙女");userinfo.setEmail("[e-mail]");//调用mybatis添加方法执行添加操作int result=userMapper.addGetId(userinfo);System.out.println("添加:"+result);int uid=userinfo.getId();System.out.println("用户id:"+uid);Assertions.assertEquals(1,result);//2023-10-15 16:50:24.135  INFO 23020 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...//2023-10-15 16:50:24.327  INFO 23020 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.//添加:1//用户id:5}@Testvoid upUserName() {//构建测试数据UserInfo userinfo=new UserInfo();userinfo.setId(6);userinfo.setName("孙悟空");//受影响的行数：int result=userMapper.upUserName(userinfo);System.out.println("修改："+result);Assertions.assertEquals(1,result);}@Test// @Transactional//加上后就不会污染我们的数据库信息；void delById() {Integer id=6;int result= userMapper.delById(id);System.out.println("删除："+result);Assertions.assertEquals(1,result);}@Testvoid   getListByOrder() {List<UserInfo> list=userMapper.getListByOrder("asc");System.out.println(list);}@Testvoid login() {String email="[e-mail]";// String name="mike";String name="' or 1='1";UserInfo userinfo=userMapper.login(name,email);System.out.println("用户登陆状态："+(userinfo==null?"失败":"成功"));}@Testvoid add2() {UserInfo userinfo=new UserInfo();userinfo.setId(11);userinfo.setName(null);userinfo.setAge(111111);// userinfo.setEmail("222");int result=userMapper.add2(userinfo);System.out.println("result:"+result);}@Testvoid add3() {UserInfo userinfo=new UserInfo();userinfo.setId(13);userinfo.setName("sss");userinfo.setEmail("12432`");int result=userMapper.add3(userinfo);Assertions.assertEquals(1,result);}@Testvoid getListByParam() {List<UserInfo> list=userMapper.getListByParam("John",1);//select* from userinfo WHERE name=? and id=?List<UserInfo> list1=userMapper.getListByParam("John",null);// select* from userinfo WHERE name=?List<UserInfo> list2=userMapper.getListByParam(null,1);//select* from userinfo WHERE id=?List<UserInfo> list3=userMapper.getListByParam(null,null);//select* from userinfo}@Testvoid update2() {UserInfo userinfo=new UserInfo();userinfo.setId(2);userinfo.setName("小李子zi");userMapper.update2(userinfo);}/**在pom.xml中添加配置* # 开启mybatis sql 日志打印；*   configuration:*     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl** # 配置打印MyBatis执行的sql;* logging:*   level:*     com:*       example:*         demo1014: debug*  后，可进行打印日志* ==>  Preparing: update userinfo set name= ? where id=?* ==> Parameters: 孙悟空(String), 4(Integer)* <==    Updates: 1* **/}/**  参数占位符 #{} 和 ${} :* #{}:预编译处理；* ${}: 字符直接替换；** Mybatis在处理#{}时，会把SQL中的#{}替换成？，使用PaeparedStatement的set方法来复制，直接替换：Mybatis在处理${}，是把${}替换成变量的值；* $的应用场景：使用Java中的关键字的时候！* ${sort}可以实现排序查询，而是用#{sort}就不能实现排序查询了，因为当使用#{sort}查询的时候，如果查询的值是String,则会加单引号，就会导致sql错误；** **/

这个插件好用，推荐MyBatisx，