spring boot 使用mybatis方便连接数据库+连表查询

创建maven项目

idea配置maven

配置本地仓库

1. 在maven安装目录里找到 conf>settings.xml>

2. 设置本地仓库的绝对路径 <localRepository>本地仓库的路径，也就是存放jar的位置</localRepository>

3. 配置远程仓库
   <mirrors> //放到这里面 </mirrors>

4. 设置全局本地仓库。 文件（settings.xml）,copy一份到本地仓库 Copy一份到c盘.2>里面

5. Pom.xml文件简单介绍
   //工程的属性，构建源码的编码方式，编译的时候的版本
   <properties></properties>
   //工程依赖的jar包的相关描述
   <dependencies></dependencies>

目录结构

src\
├── main\
│   ├── java\
│   │   └── com\
│   │       └── qhit\
│   │           └── demo\
│   │               ├── DemoApplication.java
│   │               ├── bean\
│   │               │   ├── Admin.java
│   │               │   ├── Dictionary.java
│   │               │   ├── Mode.java
│   │               │   ├── Register.java
│   │               │   ├── Student.java
│   │               │   └── Vehicles.java
│   │               ├── controller\
│   │               │   ├── StudentController.java
│   │               │   └── TeacherController.java
│   │               ├── dao\
│   │               │   ├── AdminDao.java
│   │               │   ├── DictionaryDao.java
│   │               │   ├── ModeDao.java
│   │               │   ├── RegisterDao.java
│   │               │   ├── StudentDao.java
│   │               │   └── VehiclesDao.java
│   │               ├── exception\
│   │               │   ├── GlobalException.java
│   │               │   └── LoginException.java
│   │               ├── service\
│   │               │   ├── StudentService.java
│   │               │   └── TeacherService.java
│   │               ├── utils\
│   │               │   ├── MD5Util.java
│   │               │   └── UUIDUtil.java
│   │               └── vo\
│   │                   ├── Msg.java
│   │                   └── ReDc.java
│   └── resources\
│       └── application.yml
└── test\
└── java\
└── com\
└── qhit\
└── demo\
└── DemoApplicationTests.java

创建表语句

-- 1. 管理员表
drop table if exists tbl_admin;
create table tbl_admin (
u_id varchar(255) primary key comment '管理员ID',
u_name varchar(255) not null comment '管理员姓名',
u_password varchar(255) not null comment '管理员密码'
) engine=InnoDB default charset=utf8mb4;
-- 2. 外来人信息表
drop table if exists tbl_mode;
create table tbl_mode (
m_id varchar(255) primary key comment '外来人ID',
m_name varchar(255) not null comment '姓名',
m_phone varchar(255) comment '电话',
m_relation varchar(255) comment '关系'
) engine=InnoDB default charset=utf8mb4;
-- 3. 交通工具表
drop table if exists tbl_vehicles;
create table tbl_vehicles (
v_id varchar(255) primary key comment '交通工具ID',
v_name varchar(255) not null comment '交通工具名称',
v_modeId varchar(255) comment '关联外来人ID',
constraint fk_vehicles_mode foreign key (v_modeId) references tbl_mode(m_id)
) engine=InnoDB default charset=utf8mb4;
-- 4. 登记表
drop table if exists tbl_register;
create table tbl_register (
r_id varchar(255) primary key comment '登记ID',
r_name varchar(255) not null comment '姓名',
r_startDate varchar(255) comment '回去时间',
r_vehiclesId varchar(255) comment '关联交通工具ID',
r_endDate varchar(255) comment '归来时间',
r_address varchar(255) comment '地址',
constraint fk_register_vehicles foreign key (r_vehiclesId) references tbl_vehicles(v_id)
) engine=InnoDB default charset=utf8mb4;
-- 5. 学生表
drop table if exists tbl_student;
create table tbl_student (
s_id varchar(255) primary key comment '学生ID',
s_name varchar(255) not null comment '学生姓名',
s_number varchar(255) not null comment '学号',
s_password varchar(255) not null comment '密码',
s_class varchar(255) comment '班级',
s_register_id varchar(255) comment '关联登记表ID',
constraint fk_student_register foreign key (s_register_id) references tbl_register(r_id)
) engine=InnoDB default charset=utf8mb4;
-- 6. 字典
drop table if exists tbl_student;
create table tbl_student (
s_id varchar(255) primary key comment '学生ID',
s_name varchar(255) not null comment '学生姓名',
s_number varchar(255) not null comment '学号',
s_password varchar(255) not null comment '密码',
s_class varchar(255) comment '班级',
s_register_id varchar(255) comment '关联登记表ID',
constraint fk_student_register foreign key (s_register_id) references tbl_register(r_id)
) engine=InnoDB default charset=utf8mb4;

本来想做一个学生出行登记系统，但只做了一部分。记录一下。

根据代码分析，该项目是一个基于Spring Boot的学生管理系统，主要实现以下功能：

1. 核心功能模块

• 学生管理 ：包括学生登录（通过学号和密码验证）、学生注册（输入姓名、密码、学号、班级等信息）
• 管理员管理 ：管理员登录、查询登记信息（支持分页查询）
• 登记管理 ：学生出行登记（记录出行时间、交通工具、归来时间、地址等信息）

2. 技术栈

• 后端框架：Spring Boot 2.4.4
• ORM框架：MyBatis
• 数据库：MySQL
• 其他依赖：Fastjson（JSON处理）、PageHelper（分页查询）

3. 数据模型

• 学生表(tbl_student) ：存储学生基本信息
• 管理员表(tbl_admin) ：存储管理员账户信息
• 登记表(tbl_register) ：存储学生出行登记信息
• 外来人信息表 ：存储相关外来人员信息
• 交通工具表 ：存储交通工具信息
• 数据字典表 ：存储字典数据

4. 系统架构

• 采用经典的三层架构：Controller层（处理请求）、Service层（业务逻辑）、Dao层（数据访问）
• 使用全局异常处理机制处理业务异常
• 采用RESTful风格API设计
  项目整体用于学校场景下的学生信息管理和出行登记管理，帮助学校实现学生管理的信息化和规范化。

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
  <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.4.4</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
  <properties>
  <java.version>1.8</java.version>
  </properties>
  <dependencies>
    <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
    </dependency>
    <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    </dependency>
    <dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.0.4</version>
    </dependency>
    <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.47</version>
    </dependency>
    <dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.0.0</version>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

配置文件

application.yml

server:
port: 8081
spring:
datasource:
username: root
password: 1234
url: jdbc:mysql://127.0.0.1:3306/test
mybatis:
type-aliases-package: com.qhit.demo

启动类

package com.qhit.demo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan("com.qhit.demo.dao")
public class DemoApplication
{
public static void main(String[] args) {
System.out.println();
SpringApplication.run(DemoApplication.class, args)
;
}
}

工具类-uuid工具类

package com.qhit.demo.utils;
import java.util.UUID;
public class UUIDUtil
{
public static String getUUID() {
return UUID.randomUUID().toString().replaceAll("-", "");
}
}

统一返回格式

package com.qhit.demo.vo;
import java.util.HashMap;
import java.util.Map;
public class Msg
{
private int code;
private Object msg;
//用户要返回给浏览器的数据
private Map<
String,Object> data = new HashMap<
String, Object>();
public static Msg success(){
Msg result = new Msg();
result.setCode(200);
result.setMsg("处理成功");
return result;
}
public static Msg fail(){
Msg result = new Msg();
result.setCode(500);
result.setMsg("处理失败");
return result;
}
//链式操作
public Msg add(String key, Object value){
this.getData().put(key, value);
return this;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public Object getMsg() {
return msg;
}
public void setMsg(Object msg) {
this.msg = msg;
}
public Map<
String, Object> getData() {
return data;
}
public void setData(Map<
String, Object> data) {
this.data = data;
}
}

连表查询返回封装

public class ReDc
{
private String r_id;
private String r_name;
private String r_startDate;
private String d_name;
private String r_endDate;
private String r_address;
/* 忽略get set */
}
//dao
import com.qhit.demo.bean.Register;
import com.qhit.demo.vo.ReDc;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
import java.util.List;
@Repository
public interface RegisterDao extends Mapper<
Register> {
@Select("select r.r_id,r.r_name,r.r_startDate,d.d_name,r.r_endDate,r.r_address from tbl_register as r left join tbl_dictionary as d on r.r_vehiclesId = d.d_id")
public List<
ReDc> getSelectAll();
}
// service
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.qhit.demo.bean.Admin;
import com.qhit.demo.dao.AdminDao;
import com.qhit.demo.dao.RegisterDao;
import com.qhit.demo.exception.LoginException;
import com.qhit.demo.vo.ReDc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.util.StringUtil;
import java.util.List;
@Service
public class TeacherService
{
@Autowired
AdminDao adminDao;
@Autowired
RegisterDao registerDao;
public Admin login(Admin admin) throws LoginException {
if(StringUtil.isEmpty(admin.getU_id())){
throw new LoginException("管理账号为空，请检查账号");
}
if(StringUtil.isEmpty(admin.getU_password())){
throw new LoginException("密码为空，请检查密码");
}
//select * from student where s_number = n1 and s_password = 123;
Example example = new Example(Admin.class)
;
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("u_id",admin.getU_id());
criteria.andEqualTo("u_password",admin.getU_password());
return adminDao.selectOneByExample(example);
}
public PageInfo<
ReDc> select(int pageNum, int pageSize) {
PageHelper.startPage(pageNum,pageSize);
List<
ReDc> list = registerDao.getSelectAll();
PageInfo<
ReDc> pageInfo = new PageInfo<
>(list);
return pageInfo;
}
}
// controller
import com.github.pagehelper.PageInfo;
import com.qhit.demo.bean.Admin;
import com.qhit.demo.exception.LoginException;
import com.qhit.demo.service.TeacherService;
import com.qhit.demo.vo.Msg;
import com.qhit.demo.vo.ReDc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("teacher")
public class TeacherController
{
@Autowired
TeacherService teacherService;
@PostMapping("doLogin")
public Msg doLogin(Admin admin) throws LoginException {
Admin admin1 = teacherService.login(admin);
if(!ObjectUtils.isEmpty(admin1)){
return Msg.success().add("admin",admin1);
}else{
throw new LoginException("管理员账号或密码不正确");
}
}
@PostMapping("select")
public Msg select(@RequestParam(value = "pageNum",defaultValue = "1") int pageNum,@RequestParam(value = "pageSize",defaultValue = "10") int pageSize){
PageInfo<
ReDc> list = teacherService.select(pageNum,pageSize);
return Msg.success().add("list",list);
}
}

其他简单的实体类

@Table(name = "tbl_admin")
@NameStyle(Style.normal)
public class Admin
{
private String u_id;
private String u_name;
private String u_password;
/* 忽略get set */
}
/**
* 数据字典
*/
public class Dictionary
{
private String d_id;
private String d_name;
/* 忽略get set */
}
/**
* 外来人信息表
*/
public class Mode
{
private String m_id;
private String m_name;
private String m_phone;
private String m_relation;
//关系
/* 忽略get set */
}
/**
* 登记表
*/
@Table(name = "tbl_register")
@NameStyle(Style.normal)
public class Register
{
private String r_id;
private String r_name;
private String r_startDate;
//回去时间
private String r_vehiclesId;
private String r_endDate;
//归来时间
private String r_address;
//地址
/* 忽略get set */
}
/**
* 学生表
*/
@Table(name = "tbl_student")
@NameStyle(Style.normal)
public class Student
{
private String s_id;
private String s_name;
private String s_number;
private String s_password;
private String s_class;
private String s_register_id;
/* 忽略get set */
}
/**
* 交通工具
*/
public class Vehicles
{
private String v_id;
private String v_name;
private String v_modeId;
/* 忽略get set */
}

dao层

import com.qhit.demo.bean.Admin;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
@Repository
public interface AdminDao extends Mapper<
Admin> {
Admin login(Admin admin);
}
import com.qhit.demo.bean.Dictionary;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
@Repository
public interface DictionaryDao extends Mapper<
Dictionary> {
}
import com.qhit.demo.bean.Mode;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
@Repository
public interface ModeDao extends Mapper<
Mode> {
}
import com.qhit.demo.bean.Register;
import com.qhit.demo.vo.ReDc;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
import java.util.List;
@Repository
public interface RegisterDao extends Mapper<
Register> {
@Select("select r.r_id,r.r_name,r.r_startDate,d.d_name,r.r_endDate,r.r_address from tbl_register as r left join tbl_dictionary as d on r.r_vehiclesId = d.d_id")
public List<
ReDc> getSelectAll();
}
import com.qhit.demo.bean.Student;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
@Repository
public interface StudentDao extends Mapper<
Student> {
// @Select("select * from tbl_student where s_number = ${id} and s_password = ${password} ")
Student login(Student student);
}
import com.qhit.demo.bean.Vehicles;
import tk.mybatis.mapper.common.Mapper;
public interface VehiclesDao extends Mapper<
Vehicles> {
}

service层

import com.qhit.demo.bean.Register;
import com.qhit.demo.bean.Student;
import com.qhit.demo.dao.RegisterDao;
import com.qhit.demo.dao.StudentDao;
import com.qhit.demo.exception.LoginException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.util.StringUtil;
@Service
public class StudentService
{
@Autowired
StudentDao studentDao;
@Autowired
RegisterDao registerDao;
public Student login(Student student) throws LoginException {
if(StringUtil.isEmpty(student.getS_number())){
throw new LoginException("学号为空，请检查学号");
}
if(StringUtil.isEmpty(student.getS_password())){
throw new LoginException("密码为空，请检查密码");
}
//select * from student where s_number = n1 and s_password = 123;
Example example = new Example(Student.class)
;
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("s_number",student.getS_number());
criteria.andEqualTo("s_password",student.getS_password());
return studentDao.selectOneByExample(example);
}
//注册：输入姓名，密码，学号，班级
public int registered(Student student){
return studentDao.insert(student);
}
public int doByBus(Register register) {
return registerDao.insert(register);
}
public void insert(Register register) {
}
}

controller层

import com.qhit.demo.bean.Register;
import com.qhit.demo.bean.Student;
import com.qhit.demo.exception.LoginException;
import com.qhit.demo.service.StudentService;
import com.qhit.demo.vo.Msg;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("student")
public class StudentController
{
@Autowired
StudentService studentService;
//注册：输入姓名，密码，学号，班级
// @PostMapping("doRegistered")
// public Msg doRegistered(Student student){
//
// }
@PostMapping("doLogin")
public Msg doLogin(Student student) throws LoginException {
Student student1 = studentService.login(student);
if (!ObjectUtils.isEmpty(student1)) {
return Msg.success().add("student", student1);
} else {
throw new LoginException("学号或密码不正确");
}
}
@PostMapping("insert")
public Msg insert(Student student) throws LoginException {
if (student != null) {
int registered = studentService.registered(student);
return Msg.success().add("success", registered);
} else {
throw new LoginException("");
}
}
@PostMapping("byBus")
public Msg byBus(Register register) {
int i = studentService.doByBus(register);
return Msg.success().add("success", i);
}
}

自定义全局异常

import com.alibaba.fastjson.JSONObject;
import com.qhit.demo.vo.Msg;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletRequest;
@ControllerAdvice
public class GlobalException
{
private static final Logger logger = LoggerFactory.getLogger(com.qhit.demo.exception.GlobalException.class)
;
/**
* 处理自定义的业务异常
* @param req
* @param e
* @return
*/
@ExceptionHandler(value = LoginException.class)
@ResponseBody
public Msg bizExceptionHandler(HttpServletRequest req, LoginException e){
e.printStackTrace();
logger.error("发生业务异常！原因是：{} ", JSONObject.toJSONString(e.getMessage()));
Msg fail = Msg.fail();
fail.setMsg(e.getMessage());
return fail;
}
//
// /**
// * SQL唯一约束的异常
// * @param req
// * @param e
// * @return
// */
// @ExceptionHandler(value = DuplicateKeyException.class)
// @ResponseBody
// public Result exceptionHandler(HttpServletRequest req, DuplicateKeyException e){
// logger.error("SQL唯一约束异常！原因是:{}",e.getMessage());
// return ResultGenerator.genFailResult("SQL唯一约束异常");
// }
//
// /**
// * SQL外键约束的异常
// * @param req
// * @param e
// * @return
// */
// @ExceptionHandler(value = DataIntegrityViolationException.class)
// @ResponseBody
// public Result exceptionHandler(HttpServletRequest req, DataIntegrityViolationException e){
// logger.error("SQL外键约束异常！原因是:{}",e.getMessage());
// return ResultGenerator.genFailResult("SQL外键约束异常");
// }
//
//
// /**
// * SQL查询的异常
// * @param req
// * @param e
// * @return
// */
// @ExceptionHandler(value = TooManyResultsException.class)
// @ResponseBody
// public Result exceptionHandler(HttpServletRequest req, TooManyResultsException e){
// logger.error("SQL查询异常！原因是: {}",e.getMessage());
// return ResultGenerator.genFailResult("SQL查询异常");
// }
//
// /**
// * MyBatis异常
// * @param req
// * @param e
// * @return
// */
// @ExceptionHandler(value = MyBatisSystemException.class)
// @ResponseBody
// public Result exceptionHandler(HttpServletRequest req, MyBatisSystemException e){
// logger.error("MyBatis异常！原因是: {}",e.getMessage());
// return ResultGenerator.genFailResult("MyBatis异常");
// }
//
// /**
// * 绑定的异常
// * @param req
// * @param e
// * @return
// */
// @ExceptionHandler(value = BindException.class)
// @ResponseBody
// public Result exceptionHandler(HttpServletRequest req, BindException e){
// logger.error("绑定异常！原因是:{}",e.getMessage());
// return ResultGenerator.genFailResult("API接口参数错误");
// }
// /**
// * 处理空指针的异常
// * @param req
// * @param e
// * @return
// */
// @ExceptionHandler(value =NullPointerException.class)
// @ResponseBody
// public Result exceptionHandler(HttpServletRequest req, NullPointerException e){
// e.printStackTrace();
// logger.error("发生空指针异常！原因是:{}",e.getMessage());
// return ResultGenerator.genFailResult("空指针异常");
// }
//// /**
//// * 请求头失效的异常
//// * @param req
//// * @param e
//// * @return
//// */
//// @ExceptionHandler(value = ExpiredJwtException.class)
//// @ResponseBody
//// public Result exceptionHandler(HttpServletRequest req,ExpiredJwtException e){
//// logger.error("发生空指针异常！原因是:{}",e.getMessage());
//// return ResultGenerator.genUnauthorizedResult().setMessage("Token失效");
//// }
//
//
// /**
// * 处理其他异常
// * @param req
// * @param e
// * @return
// */
// @ExceptionHandler(value =Exception.class)
// @ResponseBody
// public Result exceptionHandler(HttpServletRequest req, Exception e) {
// e.printStackTrace();
// logger.error("未知异常！原因是:{}", e.getMessage());
// return ResultGenerator.genFailResult("未知异常");
// }
}

登录异常

public class LoginException extends Exception{
public LoginException() {
super();
}
public LoginException(String message) {
super(message);
}
}