基于SpringBoot的增删改查操作
基于SpringBoot的增删改查
一、环境搭建
1.数据库表的建立
/*创建City城市表*/
CREATE TABLE `city` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '城市编号',
`provinceId` int(10) unsigned DEFAULT NULL COMMENT '省份编号',
`cityName` varchar(20) DEFAULT NULL COMMENT '城市名称',
`description` text COMMENT '城市描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
2.项目搭建
创建SpringBoot项目,引入pom依赖;
a.项目结构如图所示:
b.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>
<groupId>com.shixi</groupId>
<artifactId>springboot</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- Spring Boot Mybatis 依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-test</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.3.7.RELEASE</version>
<configuration>
<mainClass>com.shixi.springboot.SpringbootApplication</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
</project>
c.application.properties配置文件
# 应用名称
spring.application.name=springboot
#端口号
server.port=8080
# 环境设置:dev、test、prod
spring.profiles.active=dev
# 数据源基本配置
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdb?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=GMT
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=034722
#实体所在包
mybatis.type-aliases-package=com.shixi.springboot.entity
mybatis-plus.mapper-locations=classpath*:com/shixi/springboot/dao/**/*.xml
二、增删改查的具体操作
1.添加城市信息
a.mapper层
先写mapper接口
/**
* 添加城市信息
* @param city
* @return
*/
int addCity(City city);
再写xml中的sql语句
<insert id="addCity" parameterType="com.shixi.springboot.entity.City">
insert into city (id,provinceId,cityName,description) values (#{id},#{provinceId},#{cityName},#{description})
</insert>
b.service层,将mapper层注入到service层
先写service接口
/**
* 添加城市信息
* @param city
* @return
*/
int addCity(City city);
再写Impl
@Override
public int addCity(City city) {
return cityMapper.addCity(city);
}
c.controller层,将service层注入到controller层
/**
* 添加信息
* @param city
* @return
*/
@RequestMapping(value = "/addCity",method = RequestMethod.POST)
public int addCity(City city){
return cityService.addCity(city);
}
d.API测试
如:http://localhost:8080/addCity?id=101&provinceId=123&cityName=123&description=123
2.根据id删除城市信息
a.mapper层
先写mapper接口
/**
* 根据id删除信息
* @param id
* @return
*/
int deleteCity(int id);
再写xml中的sql语句
<delete id="deleteCity" parameterType="int">
delete from city where id=#{id}
</delete>
b.service层,将mapper层注入到service层
先写service接口
/**
* 根据id删除信息
* @param id
* @return
*/
int deleteCity(int id);
再写Impl
@Override
public int deleteCity(int id) {
return cityMapper.deleteCity(id);
}
c.controller层,将service层注入到controller层
/**
* 根据id删除信息
* @param id
* @return
*/
@RequestMapping(value = "/deleteCity", method = RequestMethod.GET)
public int deleteCity(Integer id) {
return cityService.deleteCity(id);
}
d.API测试I测试
如:http://localhost:8080/deleteCity?id=101
3.根据id修改d.API测试城市信息
a.mapper层
先写mapper接口
/**
* 修改信息
* @param city
* @return
*/
@RequestMapping(value = "/updateCity",method = RequestMethod.POST)
public int updateCity(City city){
return cityService.updateCity(city);
}
再写xml中的sql语句
<update id="updateCity" parameterType="com.shixi.springboot.entity.City">
update city set provinceId=#{provinceId},cityName=#{cityName},description=#{description} where id=#{id}
</update>
b.service层,将mapper层注入到service层
先写service接口
/**
* 更新城市信息
* @param city
* @return
*/
int updateCity(City city);
再写Impl
@Override
public int updateCity(City city) {
return cityMapper.updateCity(city);
}
c.controller层,将service层注入到controller层
/**
* 修改信息
* @param city
* @return
*/
@RequestMapping(value = "/updateCity",method = RequestMethod.POST)
public int updateCity(City city){
return cityService.updateCity(city);
}
d.API测试
如:http://localhost:8080/updateCity?provinceId=123&cityName=123&description=123456&id=101
4.查询所有城市信息
a.mapper层
先写mapper接口
/**
* 查询所有用户的集合
* @return
*/
List<City> selectAllCity();
再写xml中的sql语句
<select id="selectAllCity" resultType="com.shixi.springboot.entity.City">
select * from city
</select>
b.service层,将mapper层注入到service层
先写service接口
/**
* 查询所有数据
* @return
*/
List<City> selectAllCity();
再写Impl
@Override
public List<City> selectAllCity() {
return cityMapper.selectAllCity();
}
c.controller层,将service层注入到controller层
/**
* 查询所有信息
* @return
*/
@RequestMapping(value = "/selectAllCity",method = RequestMethod.GET)
public List<City> selectCity(){
return cityService.selectAllCity();
}
d.API测试
如:http://localhost:8080/selectAllCity
三、遇到的问题
1.org.apache.ibatis.binding.BindingException:Invalid bound bound statement(not found)
原因:xml没有被编译,可以查看target
中并不存在如图中的CityMapper.xml
文件;
解决方法:在项目的pom.xml
文件中加入resources
,注:放在中。
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
2.java.sql.Dulicate entiy ‘3’ for key ‘PRIMARY’
原因:执行update和add操作时违反了主键唯一性;但是自己设置的主键并未冲突;
遇到问题的具体情况及解决方法:
执行update操作时,sql语句为:
<update id="updateCity" parameterType="com.shixi.springboot.entity.City"> update city set id=#{id}, provinceId=#{provinceId},cityName=#{cityName},description=#{description} </update>
更改之后:
<update id="updateCity" parameterType="com.shixi.springboot.entity.City"> update city set provinceId=#{provinceId},cityName=#{cityName},description=#{description} where id=#{id} </update>