使用Springboot 进对数据库mysql进行增删改查

首先建立项目 bigdata 创建相应的包和文件

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.3.3.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.nmgdz</groupId> <artifactId>bigdata</artifactId> <version>0.0.1-SNAPSHOT</version> <name>bigdata</name> <description>init project</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>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- druid数据库连接池--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.10</version> </dependency> <!--lombok依赖--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!--redis依赖配置--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-redis</artifactId> </dependency> <!--Swagger-UI API文档生产工具--> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger2</artifactId> <version>2.7.0</version> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger-ui</artifactId> <version>2.7.0</version> </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> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>

Swagger2Config 文件

package com.nmgdz.bigdata.config; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import springfox.documentation.builders.ApiInfoBuilder; import springfox.documentation.builders.PathSelectors; import springfox.documentation.builders.RequestHandlerSelectors; import springfox.documentation.service.ApiInfo; import springfox.documentation.spi.DocumentationType; import springfox.documentation.spring.web.plugins.Docket; import springfox.documentation.swagger2.annotations.EnableSwagger2; /** 作者: xhd 创建时间: 2020/3/13 17:32 版本: V1.0 */ /** * Swagger2API文档的配置 */ @Configuration @EnableSwagger2 public class Swagger2Config { @Bean public Docket createRestApi(){ return new Docket(DocumentationType.SWAGGER_2) .apiInfo(apiInfo()) .select() //为当前包下controller生成API文档 .apis(RequestHandlerSelectors.basePackage("com.nmgdz.bigdata.controller")) //为有@Api注解的Controller生成API文档 // .apis(RequestHandlerSelectors.withClassAnnotation(Api.class)) //为有@ApiOperation注解的方法生成API文档 // .apis(RequestHandlerSelectors.withMethodAnnotation(ApiOperation.class)) .paths(PathSelectors.any()) .build(); } private ApiInfo apiInfo() { return new ApiInfoBuilder() .title("SwaggerUI演示") .description("内蒙古电子大数据工坊") .contact("xhd") .version("1.0") .build(); } }

UserController 文件

package com.nmgdz.bigdata.controller; import com.nmgdz.bigdata.entity.User; import com.nmgdz.bigdata.service.RedisService; import com.nmgdz.bigdata.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; /** * @auther: xhd * @Date: 2020/9/1/11:15 */ @RestController @RequestMapping("user") public class UserController { @Autowired private UserService userService; @Autowired private RedisService redisService; /** * 查询 * * @param id * @return */ @GetMapping("/queryById") public User queryById(int id) { return userService.queryById(id); } /** * 增 改 * @param user * @return */ @PostMapping("/saveOrUpdate") public User saveOrUpdateUser(User user) { return userService.save(user); } /** * 删除用户 * @param id * @return */ @DeleteMapping("/delete") public Boolean deleteUser(int id) { userService.deletUserById(id); return true; } }

UserDao 文件

package com.nmgdz.bigdata.dao; import com.nmgdz.bigdata.entity.User; import org.springframework.data.jpa.repository.JpaRepository; import java.util.List; /** * 作者: xhd * 创建时间: 2020/3/12 19:51 * 版本: V1.0 */ public interface UserDao extends JpaRepository<User, Integer> { //List<User> findByAge(int age); User findById(int id); }

User 文件

package com.nmgdz.bigdata.entity; import lombok.Data; import org.hibernate.annotations.CreationTimestamp; import org.springframework.format.annotation.DateTimeFormat; import javax.persistence.*; import java.io.Serializable; import java.math.BigDecimal; import java.math.BigInteger; import java.util.Date; /** * 作者: xhd * 创建时间: 2020/3/12 19:43 * 版本: V1.0 */ @Table(name = "company_monitor_sensor") @Entity @Data public class User implements Serializable { @Id //主键 @GeneratedValue(strategy = GenerationType.IDENTITY) //private Integer id; //private String name; //private Integer age; private Integer id; private String company_code; private Integer monitor_type; private Integer sys_name; private Integer equipment_type_id; private String equipment_type_name; private Integer sensor_type_id; private String sensor_type_name; private Integer technology_type_id; private String technology_type_name; private String department; private String sensor_code; private String location; private String storage_material; private BigInteger level_alarm_high; private BigInteger secondary_alarm_high; private BigInteger level_alarm_low; private BigInteger secondary_alarm_low; private Integer operate_id; private String operate_username; @CreationTimestamp @Column(name="create_time") @DateTimeFormat(pattern = "yyyy-mm-dd") private Date create_time; @CreationTimestamp @Column(name="update_time") @DateTimeFormat(pattern = "yyyy-mm-dd") private Date update_time; } // public User(Integer id,String company_code,Integer monitor_type,String sys_name, //Integer equipment_type_id, //String equipment_type_name, Integer sensor_type_id, // String sensor_type_name, Integer technology_type_id, // String technology_type_name, String department, //String sensor_code, String location, //String storage_material, BigDecimal level_alarm_high, //BigDecimal secondary_alarm_high, BigDecimal level_alarm_low, //BigDecimal secondary_alarm_low, //Integer operate_id, String operate_username, //Timestamp create_time, Timestamp update_time){ // this.id=id; // this.sys_name=sys_name; // this.company_code = company_code; // this.monitor_type = monitor_type; // this.equipment_type_id = equipment_type_id; // this.equipment_type_name=equipment_type_name; // this.sensor_type_id=sensor_type_id; // this.sensor_type_name=sensor_type_name; // this.technology_type_id=technology_type_id; // this.technology_type_name=technology_type_name; // this.department=department; // this.sensor_code=sensor_code; // this.location=location; // this.storage_material=storage_material; // this.level_alarm_high=level_alarm_high; // this.secondary_alarm_high=secondary_alarm_high; // this.secondary_alarm_low=secondary_alarm_low; // this.level_alarm_low=level_alarm_low; // this.operate_id=operate_id; // this.operate_username=operate_username; // this.create_time=create_time; // this.update_time=update_time; //} // // public User() { // } //}

RedisServiceImpl 文件

package com.nmgdz.bigdata.service.impl; import com.nmgdz.bigdata.service.RedisService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.redis.core.StringRedisTemplate; import org.springframework.stereotype.Service; import java.util.concurrent.TimeUnit; /** * @auther: xhd * @Date: 2020/9/2/0:26 */ @Service public class RedisServiceImpl implements RedisService { @Autowired private StringRedisTemplate stringRedisTemplate; @Override public void set(String key, String value) { stringRedisTemplate.opsForValue().set(key, value); } @Override public String get(String key) { return stringRedisTemplate.opsForValue().get(key); } @Override public boolean expire(String key, long expire) { return stringRedisTemplate.expire(key, expire, TimeUnit.SECONDS); } @Override public void remove(String key) { stringRedisTemplate.delete(key); } @Override public Long increment(String key, long delta) { return stringRedisTemplate.opsForValue().increment(key, delta); } }

UserServiceImpl 文件

package com.nmgdz.bigdata.service.impl; import com.nmgdz.bigdata.service.RedisService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.redis.core.StringRedisTemplate; import org.springframework.stereotype.Service; import java.util.concurrent.TimeUnit; /** * @auther: xhd * @Date: 2020/9/2/0:26 */ @Service public class RedisServiceImpl implements RedisService { @Autowired private StringRedisTemplate stringRedisTemplate; @Override public void set(String key, String value) { stringRedisTemplate.opsForValue().set(key, value); } @Override public String get(String key) { return stringRedisTemplate.opsForValue().get(key); } @Override public boolean expire(String key, long expire) { return stringRedisTemplate.expire(key, expire, TimeUnit.SECONDS); } @Override public void remove(String key) { stringRedisTemplate.delete(key); } @Override public Long increment(String key, long delta) { return stringRedisTemplate.opsForValue().increment(key, delta); } }

RedisService 文件

package com.nmgdz.bigdata.service; /** * @auther: xhd * @Date: 2020/9/2/0:26 */ public interface RedisService { /** * 存储数据 */ void set(String key, String value); /** * 获取数据 */ String get(String key); /** * 设置超期时间 */ boolean expire(String key, long expire); /** * 删除数据 */ void remove(String key); /** * 自增操作 * * @param delta 自增步长 */ Long increment(String key, long delta); }

UserService 文件

package com.nmgdz.bigdata.service; /** * @auther: xhd * @Date: 2020/9/2/0:26 */ public interface RedisService { /** * 存储数据 */ void set(String key, String value); /** * 获取数据 */ String get(String key); /** * 设置超期时间 */ boolean expire(String key, long expire); /** * 删除数据 */ void remove(String key); /** * 自增操作 * * @param delta 自增步长 */ Long increment(String key, long delta); }

BigdataApplication 文件

package com.nmgdz.bigdata; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class BigdataApplication { public static void main(String[] args) { SpringApplication.run(BigdataApplication.class, args); } }

application.yml 文件

spring: datasource: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai username: root password: 123456 initialSize: 20 minIdle: 50 maxActive: 500 jpa: hibernate: ddl-auto: update show-sql: true redis: host: localhost # Redis服务器地址 database: 0 # Redis数据库索引（默认为0） port: 6379 # Redis服务器连接端口 password: 123456 # Redis服务器连接密码（默认为空） jedis: pool: max-active: 8 # 连接池最大连接数（使用负值表示没有限制） max-wait: -1ms # 连接池最大阻塞等待时间（使用负值表示没有限制） max-idle: 8 # 连接池中的最大空闲连接 min-idle: 0 # 连接池中的最小空闲连接 timeout: 3000ms # 连接超时时间（毫秒）

BigdataApplicationTests 文件

package com.nmgdz.bigdata; import org.junit.jupiter.api.Test; import org.springframework.boot.test.context.SpringBootTest; @SpringBootTest class BigdataApplicationTests { @Test void contextLoads() { } }

RedisTest 文件

package com.nmgdz.bigdata.service; /** * @auther: xhd * @Date: 2020/9/2/0:26 */ public interface RedisService { /** * 存储数据 */ void set(String key, String value); /** * 获取数据 */ String get(String key); /** * 设置超期时间 */ boolean expire(String key, long expire); /** * 删除数据 */ void remove(String key); /** * 自增操作 * * @param delta 自增步长 */ Long increment(String key, long delta); }

数据库

/* Navicat Premium Data Transfer Source Server : antai1 Source Server Type : MySQL Source Server Version : 100323 Source Host : 116.63.43.201:3306 Source Schema : safety Target Server Type : MySQL Target Server Version : 100323 File Encoding : 65001 Date: 03/09/2020 11:54:42 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for company_monitor_sensor -- ---------------------------- DROP TABLE IF EXISTS `company_monitor_sensor`; CREATE TABLE `company_monitor_sensor` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `company_code` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '企业编号', `monitor_type` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '监测类型，1-存储监测，0-作业场所监测', `sys_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '系统名称', `equipment_type_id` int(11) NULL DEFAULT 0 COMMENT '设备类型ID', `equipment_type_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '设备类型名称', `sensor_type_id` int(11) NULL DEFAULT 0 COMMENT '传感器类型ID', `sensor_type_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '传感器类型名称', `technology_type_id` int(11) NULL DEFAULT 0 COMMENT '危险工艺类型ID', `technology_type_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '危险工艺类型名称', `department` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '车间/部门', `sensor_code` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '传感器编号', `location` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '传感器位置', `storage_material` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '存储物质', `level_alarm_high` decimal(11, 1) NULL DEFAULT 0.0 COMMENT '一级报警（高值）', `secondary_alarm_high` decimal(11, 1) NULL DEFAULT 0.0 COMMENT '二级报警（高值）', `level_alarm_low` decimal(11, 1) NULL DEFAULT 0.0 COMMENT '一级报警（低值）', `secondary_alarm_low` decimal(11, 1) NULL DEFAULT 0.0 COMMENT '二级报警（低值）', `operate_id` int(11) NULL DEFAULT NULL COMMENT '操作人ID', `operate_username` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人用户名', `create_time` timestamp(0) NOT NULL DEFAULT current_timestamp() COMMENT '创建时间', `update_time` timestamp(0) NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '监控传感器表' ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;

遇到的问题就是 create_time 是null 导致无法写入数据

解决办法是修改表的配置 这样就可以写入了。

运行然后用访问这个网址进行验证

http://localhost:8080/swagger-ui.html

今天的学习就到此结束了