java使用springboot3与mybatis连接数据库

上文分享了一个基于spring boot 3的基础框架，包含了日志与单元测试组件。

java自己搭建的spirngboot3基础框架

但很多时候我们做项目都是需要连接数据库的，所以这次分享的是一个基于spring boot3配合mybatis持久层框架来连接数据库的技术框架。

本框架同样整合了单元测试TesgNG，日志记录log4j2，并且对于分页，插入数据库，修改数据，批量插入等都编写了对应的单元测试

连接数据库先要配置application-dev.yml这个是用来做开发环境文件配置的

server:
  port: 8181 #端口号
  servlet:
    encoding:
      charset: UTF-8
      enabled: true
      force: true
  shutdown: graceful #开启优雅停机 默认为immediate立即停机
 
mybatis:
  configuration:
    call-setters-on-nulls: true
logging:
  config: classpath:log4j2.xml
 
 
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/springboot3mybatis?useUnicode=true&characterEncoding=utf8&allowMutiQueries=true&useTimezone=true&serverTimezone=GMT%2B8
    username: springboot3mybatis
    password: springboot3mybatis

有些时候我们需要对开发环境与测试环境，生产环境等进行不同环境的配置，所以就有了application-test.yml

在使用mybatis前应当先修改application使期设置要扫描哪些mapper也就是持久化的接口

package com.vsked;
 
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@MapperScan({"com.vsked.dao"})
@SpringBootApplication
public class Application {
     
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
     
}

然后我们就可以为数据库中的表设计对应持久层接口了，

有些时候，我们会一个项目分配一个数据库，并为这个数据库分配一个单独的用户名与密码，以下是初始化数据库用户名与数据库的脚本

CREATE USER 'springboot3mybatis'@'%' IDENTIFIED BY 'springboot3mybatis';
 
CREATE DATABASE `springboot3mybatis` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
grant all  on `springboot3mybatis`.* to 'springboot3mybatis'@'%' ;

然后数据库中建表张表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `uid` int NOT NULL,
  `uname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `upass` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `ubirth` date NULL DEFAULT NULL,
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
 
SET FOREIGN_KEY_CHECKS = 1;

表建立完成以后就可以写持久化接口了，这里我们使用注解方式进行开发

package com.vsked.dao;
 
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
 
import java.util.List;
import java.util.Map;
 
@Mapper
public interface UserDao {
 
    @Insert("INSERT INTO users(uid, uname,upass,ubirth) VALUES(#{uid,jdbcType=INTEGER},#{uname,jdbcType=VARCHAR},#{upass,jdbcType=VARCHAR},#{ubirth,jdbcType=DATE})")
    int insertUser(Map<String, Object> user);
 
    @Insert("<script> insert into users(uid, uname,upass,ubirth) values <foreach collection=\"myList\" item=\"emp\" separator=\",\">(#{emp.uid,jdbcType=INTEGER},#{emp.uname},#{emp.upass},#{emp.ubirth,jdbcType=DATE}) </foreach> </script>")
    int insertUsers(Map<String, Object> dataMap);
 
    @Update("<script>" +
            "UPDATE users " +
            "SET upass = CASE uid " +
            "<foreach collection='myList' item='emp'>" +
            "WHEN #{emp.uid,jdbcType=INTEGER} THEN #{emp.upass} " +
            "</foreach> " +
            "END " +
            "WHERE uid IN " +
            "<foreach collection='myList' item='emp' open='(' separator=',' close=')'>" +
            "#{emp.uid,jdbcType=INTEGER}" +
            "</foreach>" +
            "</script>")
    int updateUsers(Map<String, Object> dataMap);
 
    /**
     * 根据id查询用户,也可以不写Results注解，默认会根据属性名映射字段名
     *
     * [url=home.php?mod=space&uid=952169]@Param[/url] id
     * @return
     */
    @Select("SELECT uid, uname, upass FROM users WHERE uid = #{id}")
    @Results({
            @Result(column = "uid", property = "id"),
            @Result(column = "uname", property = "uname"),
            @Result(column = "upass", property = "upass")
    })
    Map<String, Object> getUserById(@Param("id") Long id);
 
    @Select("${sql}")
    List<Map<String,Object>> listvsked(Map<String, Object> m);
 
    @Select("select * from users")
    List<Map<String,Object>> findAll();
 
 
}

开发完成持久化接口以后，我们编写对应的单元测试，测试一下数据库连接，与持久化接口是否正常。

package com.vsked.dao;
 
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.vsked.test.BaseTestWithTransactional;
import jakarta.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.test.annotation.Rollback;
import org.testng.annotations.Test;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
 
public class UserDaoTest extends BaseTestWithTransactional {
 
    private static final Logger log = LoggerFactory.getLogger(UserDaoTest.class);
 
    @Resource
    UserDao userDao;
 
    @Rollback(value = false) //事务不回滚
    @Test
    public void insert(){
        Map<String,Object> user=new HashMap<>();
        user.put("uid","2");
        user.put("uname","useraaaaaa");
        user.put("upass","passbbbb");
        user.put("ubirth","1988-03-02");
        userDao.insertUser(user);
    }
 
    @Rollback(value = false) //事务不回滚
    @Test
    public void insertBatch(){
        List<Map<String,Object>> userList=new LinkedList<>();
 
        for(int i=0;i<100;i++){
            Map<String,Object> user=new HashMap<>();
            user.put("uid","1"+i);
            user.put("uname","user1"+i);
            user.put("upass","pass1"+i);
            user.put("ubirth","1988-03-02");
            userList.add(user);
        }
 
        Map<String,Object> dataMap=new HashMap<>();
        dataMap.put("myList",userList);
 
        int row=userDao.insertUsers(dataMap);
        log.debug("{}",row);
    }
 
    @Rollback(value = false) //事务不回滚
    @Test
    public void updateBatch(){
        List<Map<String,Object>> userList=new LinkedList<>();
 
        for(int i=0;i<100;i++){
            Map<String,Object> user=new HashMap<>();
            user.put("uid","1"+i);
            user.put("upass","passzzzzzzzzz1"+i);
            userList.add(user);
        }
 
        Map<String,Object> dataMap=new HashMap<>();
        dataMap.put("myList",userList);
 
        int row=userDao.updateUsers(dataMap);
        log.debug("{}",row);
    }
 
    @Test
    public void getUserById(){
        Map<String,Object> user=userDao.getUserById(12L);
        log.debug("{}",user);
    }
 
    @Test
    public void listvsked(){
        Map<String,Object> parMap=new HashMap<>();
        parMap.put("sql","select * from users");
        List<Map<String,Object>> dataList=userDao.listvsked(parMap);
        log.debug("{}",dataList);
    }
 
    @Rollback(value = false) //事务不回滚
    @Test
    public void list1vsked(){
        Map<String,Object> parMap=new HashMap<>();
        parMap.put("sql","insert into users values(55,'mynameisvsked','password',null)");
        List<Map<String,Object>> dataList=userDao.listvsked(parMap);
        log.debug("{}",dataList);
    }
 
    @Test
    public void pageTest(){
        int pageNum = 1;
        int pageSize = 10;
        PageHelper.startPage(pageNum, pageSize);
        List<Map<String,Object>> users = userDao.findAll(); // 查询后自动分页
        PageInfo<Map<String,Object>> pageInfo = new PageInfo<>(users); // 获取分页信息 包括总条数，总页数，每页条数，当前页等信息
        log.debug("{}",pageInfo); // 打印分页信息
        //PageInfo{pageNum=1, pageSize=10, size=10, startRow=1, endRow=10, total=100, pages=10, list=Page{count=true, pageNum=1, pageSize=10, startRow=0, endRow=10, total=100, pages=10, reasonable=false, pageSizeZero=false}[{uid=0, upass=pass10, uname=user10, ubirth=1988-03-02}, {uid=1, upass=pass11, uname=user11, ubirth=1988-03-02}, {uid=2, upass=pass12, uname=user12, ubirth=1988-03-02}, {uid=3, upass=pass13, uname=user13, ubirth=1988-03-02}, {uid=4, upass=pass14, uname=user14, ubirth=1988-03-02}, {uid=5, upass=pass15, uname=user15, ubirth=1988-03-02}, {uid=6, upass=pass16, uname=user16, ubirth=1988-03-02}, {uid=7, upass=pass17, uname=user17, ubirth=1988-03-02}, {uid=8, upass=pass18, uname=user18, ubirth=1988-03-02}, {uid=9, upass=pass19, uname=user19, ubirth=1988-03-02}], prePage=0, nextPage=2, isFirstPage=true, isLastPage=false, hasPreviousPage=false, hasNextPage=true, navigatePages=8, navigateFirstPage=1, navigateLastPage=8, navigatepageNums=[1, 2, 3, 4, 5, 6, 7, 8]}
    }
 
}

这里进行单元测试的时候要注意，应当加上数据库不回滚的注解，不然数据库里看不到数据，如果仅想做单元测试，不想看到数据，可以将@Rollback(value = false) //事务不回滚

这一段代码删除或修改为@Rollback(value = true)

单元测试完成以后，就可以看控制台执行结果，并查看数据库是否有没有变化了。

以下是详细代码附件