SpringBoot+MybatisPlus+Mysql小冒险

0 前提

• 安装了MySQL
• 一款适合自己的开发环境

1 数据库准备

创建数据库和表格

创建数据库的SQL语句如下：

CREATE DATABASE springboottestuser;

创建表格的SQL语句如下：

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `score` varchar(20) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('1', 'cll', 'nan', '120', '20');
INSERT INTO `t_user` VALUES ('2', 'xc', 'nan', '20', '80');
INSERT INTO `t_user` VALUES ('3', 'kl', 'nv', '60', '20');
INSERT INTO `t_user` VALUES ('4', 'ok', 'nv', '80', '24');

数据准备完毕

2 Springboot相关

2.1 构建Springboot项目

在网址为https://start.spring.io/的网页里，进行选择后，下载压缩包

 

完成初始化项目

2.2 编写代码

要测试的数据库为

项目结构如下图：

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 http://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.1.3.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>sbtMybatisP</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>sbtMybatisP</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>11</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</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-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
		<groupId>com.baomidou</groupId>
		<artifactId>mybatis-plus-boot-starter</artifactId>
		<version>3.1.0</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

pom.xml文件中关于mybatisPlus的依赖是在mvn repository中查询后添加的。

添加mapper和entity后，项目结构如下

 

 

数据库配置文件application.properties如下：

密码处填自己数据库的密码

URL中3306/后填自己的数据库

jdbc.type=mysql
spring.datasource.url=jdbc:mysql://localhost:3306/springboottestuser?useUnicode=true&\
  characterEncoding=utf8&serverTimezone=UTC
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=123

//实例代码
package com.example.sbtMybatisP.entity;

import lombok.Data;

@Data
public class T_User {
    private Integer id;
    private String name;
    private String sex;
    private String score;
    private Integer age;
}

//UserMapper文件
package com.example.sbtMybatisP.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.sbtMybatisP.entity.T_User;

public interface UserMapper extends BaseMapper<T_User>{
}

主启动程序

package com.example.sbtMybatisP;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.sbtMybatisP.mapper")
public class SbtMybatisPApplication {

	public static void main(String[] args) {
		SpringApplication.run(SbtMybatisPApplication.class, args);
	}

}

测试代码

package com.example.sbtMybatisP;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.example.sbtMybatisP.entity.T_User;
import com.example.sbtMybatisP.mapper.UserMapper;

import java.util.List;
import javax.annotation.Resource;

@RunWith(SpringRunner.class)
@SpringBootTest
public class SbtMybatisPApplicationTests {

	@Test
	public void contextLoads() {
	}
	@Resource
	private UserMapper um;
	@Test
	public void testSelect(){
		System.out.println("-begin select-");
		List<T_User> user = um.selectList(null);
		user.forEach(System.out::println);
	}

}

运行测试代码即可。

出现以下异常

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table 'springboottestuser.t__user' doesn't exist
### The error may exist in com/example/sbtMybatisP/mapper/UserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  id,name,sex,score,age  FROM t__user
### Cause: java.sql.SQLSyntaxErrorException: Table 'springboottestuser.t__user' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'springboottestuser.t__user' doesn't exist

说明表名是错的，改成T_user,得到以下结果

-begin select-
T_user(id=1, name=cll, sex=nan, score=120, age=20)
T_user(id=2, name=xc, sex=nan, score=20, age=80)
T_user(id=3, name=kl, sex=nv, score=60, age=20)
T_user(id=4, name=ok, sex=nv, score=80, age=24)

完成！

3 总结

刚开始在启动程序中MapperScan()直接写到了具体的mapper文件，所以失败了

路漫漫其修远兮，继续学习