spring boot es删除数据 springboot数据库删除操作

转载

墨色天香 2024-05-29 10:26:19

文章标签 spring boot es删除数据 spring boot spring java User 文章分类 spring boot 后端开发

第八章数据库操作之整合Mybatis和事务讲解

08-1 Spring2.x持久化数据方式介绍

简介：介绍近几年常用的访问数据库的方式和优缺点

1、原始java访问数据库
		开发流程麻烦
		1、注册驱动/加载驱动
			Class.forName("com.mysql.jdbc.Driver")
		2、建立连接
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname","root","root");
		3、创建Statement

		4、执行SQL语句

		5、处理结果集

		6、关闭连接，释放资源

	2、apache dbutils框架
		比上一步简单点
		官网:https://commons.apache.org/proper/commons-dbutils/
	3、jpa框架
		spring-data-jpa
		jpa在复杂查询的时候性能不是很好
	
	4、Hibernate   解释：ORM：对象关系映射Object Relational Mapping
		企业大都喜欢使用hibernate
	
	5、Mybatis框架   
		互联网行业通常使用mybatis
		不提供对象和关系模型的直接映射,半ORM

08-2 SpringBoot2.x整合 Mybatis3.x注解实战

1.创建SpringBoot基础结构

spring boot es删除数据 springboot数据库删除操作_spring

spring boot es删除数据 springboot数据库删除操作_java_02

spring boot es删除数据 springboot数据库删除操作_spring boot es删除数据_03

第三方依赖包：

<?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.6.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lcz</groupId>
    <artifactId>spring_demo14</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring_demo14</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>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </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>
    </dependencies>

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

</project>

2.application.properties

#mybatis.type-aliases-package=net.xdclass.base_project.domain
#可以自动识别
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

spring.datasource.url=jdbc:mysql://localhost:3306/project_lcz?useUnicode=true&characterEncoding=utf-8
spring.datasource.username =root
spring.datasource.password =123
#spring.datasource.type =com.alibaba.druid.pool.DruidDataSource

3.目录结构

spring boot es删除数据 springboot数据库删除操作_spring boot_04

3.1 domain

User.java

package com.lcz.spring_demo14.domain;

import java.util.Date;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:03
 * @Description:
 **/
public class User {
    private int id;
    private String name;
    private String phone;
    private int age;
    private Date createTime;
    public User(){

    }
    public User(int id, String name, String phone, int age, Date createTime) {
        this.id = id;
        this.name = name;
        this.phone = phone;
        this.age = age;
        this.createTime = createTime;
    }

    public User(String name, String phone, int age, Date createTime) {
        this.name = name;
        this.phone = phone;
        this.age = age;
        this.createTime = createTime;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

JsonData.java：响应结果类

package com.lcz.spring_demo14.domain;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:05
 * @Description:
 **/

import java.io.Serializable;

/**
 * 响应结果类
 */
public class JsonData implements Serializable {

    private Integer code; //状态码 0成功 1处理中 -1处理失败
    private Object  data; //数据
    private String  msg;  //描述

    public JsonData(){

    }

    public JsonData(Integer code, Object data,String msg) {
        this.code = code;
        this.msg = msg;
        this.data = data;
    }
    // 成功，传入数据
    public static JsonData buildSuccess() {
        return new JsonData(0, null, null);
    }

    // 成功，传入数据
    public static JsonData buildSuccess(Object data) {
        return new JsonData(0, data, null);
    }

    // 失败，传入描述信息
    public static JsonData buildError(String msg) {
        return new JsonData(-1, null, msg);
    }

    // 失败，传入描述信息,状态码
    public static JsonData buildError(String msg, Integer code) {
        return new JsonData(code, null, msg);
    }

    // 成功，传入数据,及描述信息
    public static JsonData buildSuccess(Object data, String msg) {
        return new JsonData(0, data, msg);
    }

    // 成功，传入数据,及状态码
    public static JsonData buildSuccess(Object data, int code) {
        return new JsonData(code, data, null);
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }
}

3.2 mapper

访问数据库表的接口

package com.lcz.spring_demo14.mapper;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:10
 * @Description:
 **/

import com.lcz.spring_demo14.domain.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;

/**
 * 访问user数据表的mapper表
 */
public interface UserMapper {
    //推荐使用#{}取值，不要用${},因为存在注入的风险
    @Insert("INSERT INTO user(name,phone,create_time,age) VALUES(#{name}, #{phone}, #{createTime},#{age})")
    @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")   //keyProperty java对象的属性；keyColumn表示数据库的字段
    int insert(User user);
}

3.3 service

UserService

package com.lcz.spring_demo14.service;

import com.lcz.spring_demo14.domain.User;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:13
 * @Description:
 **/
public interface UserService {
    public int add(User user);
}

impl/UserServiceImpl

package com.lcz.spring_demo14.service.impl;

import com.lcz.spring_demo14.domain.User;
import com.lcz.spring_demo14.mapper.UserMapper;
import com.lcz.spring_demo14.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:14
 * @Description:
 **/
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public int add(User user) {
        userMapper.insert(user);
        int id = user.getId();
        return id;
    }
}

3.4 controller

package com.lcz.spring_demo14.controller;

import com.lcz.spring_demo14.domain.JsonData;
import com.lcz.spring_demo14.domain.User;
import com.lcz.spring_demo14.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:20
 * @Description:
 **/
@RestController
@RequestMapping(value = "/user")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping(value = "/add")
    public Object add(){
        User user = new User();
        user.setAge(18);
        user.setCreateTime(new Date());
        user.setName("lcz");
        user.setPhone("1xxxxxxxx");
        int id = userService.add(user);
        return JsonData.buildSuccess();
    }
}

3.5 启动类

package com.lcz.spring_demo14;

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

@MapperScan("com.lcz.spring_demo14.mapper")
@SpringBootApplication
public class SpringDemo14Application {

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

}

3.6 sql创建表

CREATE TABLE `user` (
			  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
			  `name` varchar(128) DEFAULT NULL COMMENT '名称',
			  `phone` varchar(16) DEFAULT NULL COMMENT '用户手机号',
			  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
			  `age` int(4) DEFAULT NULL COMMENT '年龄',
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

4.结果图

spring boot es删除数据 springboot数据库删除操作_User_05

spring boot es删除数据 springboot数据库删除操作_java_06

spring boot es删除数据 springboot数据库删除操作_spring boot_07

小结：：

SpringBoot2.x整合Mybatis3.x注解配置实战
	1、使用starter, maven仓库地址：http://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter

	2、加入依赖(可以用 http://start.spring.io/ 下载)
				
		<!-- 引入starter-->
				<dependency>
				    <groupId>org.mybatis.spring.boot</groupId>
				    <artifactId>mybatis-spring-boot-starter</artifactId>
				    <version>1.3.2</version>
				    <scope>runtime</scope>			    
				</dependency>
	 			
	 	<!-- MySQL的JDBC驱动包	-->	
	 			<dependency>
					<groupId>mysql</groupId>
					<artifactId>mysql-connector-java</artifactId>
					<scope>runtime</scope>
				</dependency> 
		<!-- 引入第三方数据源 -->		
				<dependency>
					<groupId>com.alibaba</groupId>
					<artifactId>druid</artifactId>
					<version>1.1.6</version>
				</dependency>

	3、加入配置文件
		#mybatis.type-aliases-package=net.xdclass.base_project.domain
		#可以自动识别
		#spring.datasource.driver-class-name =com.mysql.jdbc.Driver

		spring.datasource.url=jdbc:mysql://localhost:3306/movie?useUnicode=true&characterEncoding=utf-8
		spring.datasource.username =root
		spring.datasource.password =password
		#如果不使用默认的数据源 （com.zaxxer.hikari.HikariDataSource）
		spring.datasource.type =com.alibaba.druid.pool.DruidDataSource

	加载配置，注入到sqlSessionFactory等都是springBoot帮我们完成

	4、启动类增加mapper扫描
		@MapperScan("net.xdclass.base_project.mapper")

		 技巧：保存对象，获取数据库自增id 
		 @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")

	4、开发mapper
		参考语法 http://www.mybatis.org/mybatis-3/zh/java-api.html

	5、sql脚本
		CREATE TABLE `user` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `name` varchar(128) DEFAULT NULL COMMENT '名称',
		  `phone` varchar(16) DEFAULT NULL COMMENT '用户手机号',
		  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
		  `age` int(4) DEFAULT NULL COMMENT '年龄',
		  PRIMARY KEY (`id`)
		) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
相关资料：
	http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/#Configuration

	https://github.com/mybatis/spring-boot-starter/tree/master/mybatis-spring-boot-samples

	整合问题集合：

08-3 SpringBoot2.x整合Mybatis3.x增删改查实操和控制台打印sql语句

在上一小结的基础上，继续增加删改查的操作。

1.application.properties

#mybatis.type-aliases-package=net.xdclass.base_project.domain
#可以自动识别
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

spring.datasource.url=jdbc:mysql://localhost:3306/project_lcz?useUnicode=true&characterEncoding=utf-8
spring.datasource.username =root
spring.datasource.password =123
#spring.datasource.type =com.alibaba.druid.pool.DruidDataSource
#增加打印sql语句，一般用于本地开发测试
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

2.目录结构

2.1 mapper

UserMapper.java

package com.lcz.spring_demo14.mapper;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:10
 * @Description:
 **/

import com.lcz.spring_demo14.domain.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 访问user数据表的mapper表
 */
public interface UserMapper {
    //推荐使用#{}取值，不要用${},因为存在注入的风险
    @Insert("INSERT INTO user(name,phone,create_time,age) VALUES(#{name}, #{phone}, #{createTime},#{age})")
    @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")   //keyProperty java对象的属性；keyColumn表示数据库的字段
    int insert(User user);

    /**
     * 根据id查找对象
     * @param id
     * @return
     */
    @Select("select * from user where id=#{id}")
    @Results({@Result(column = "create_time",property = "createTime")})
    User findById(int id);

    /**
     * 查找全部
     * @return
     */
    @Select("SELECT * FROM user")
    @Results({
            @Result(column = "create_time",property = "createTime")  //javaType = java.util.Date.class
    })
    List<User> getAll();

    /**
     * 功能描述：更新对象
     * @param user
     */
    @Update("UPDATE user SET name=#{name} WHERE id =#{id}")
    void update(User user);

    /**
     * 功能描述：根据id删除用户
     * @param userId
     */
    @Delete("DELETE FROM user WHERE id =#{userId}")
    void delete(int userId);

}

2.2 service

UserService

package com.lcz.spring_demo14.service;

import com.lcz.spring_demo14.domain.User;

import java.util.List;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:13
 * @Description:
 **/
public interface UserService {
    public int add(User user);

    public User findById(int id);

    public List<User> getAll();

    public void update(User user);

    public void delete(int id);

}

UserServiceImpl

package com.lcz.spring_demo14.service.impl;

import com.lcz.spring_demo14.domain.User;
import com.lcz.spring_demo14.mapper.UserMapper;
import com.lcz.spring_demo14.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author : codingchao
 * @date : 2021-11-25 10:14
 * @Description:
 **/
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public int add(User user) {
        userMapper.insert(user);
        int id = user.getId();
        return id;
    }

    @Override
    public User findById(int id) {
        User user = userMapper.findById(id);
        return user;
    }

    @Override
    public List<User> getAll() {
        List<User> list = userMapper.getAll();
        return list;
    }

    @Override
    public void update(User user) {
        userMapper.update(user);
        return;
    }

    @Override
    public void delete(int id) {
        userMapper.delete(id);
        return;
    }
}

3.结果图

插入语句insert

spring boot es删除数据 springboot数据库删除操作_User_08

spring boot es删除数据 springboot数据库删除操作_spring boot es删除数据_09

查询findall

spring boot es删除数据 springboot数据库删除操作_User_10

查询findById

spring boot es删除数据 springboot数据库删除操作_java_11

删除数据

spring boot es删除数据 springboot数据库删除操作_spring boot es删除数据_12

更新数据update

spring boot es删除数据 springboot数据库删除操作_spring_13

小结：

讲解:SpringBoot2.x整合Mybatis3.x增删改查实操, 控制台打印sql语句

1、控制台打印sql语句		
	#增加打印sql语句，一般用于本地开发测试
	mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

2、增加mapper代码		
    @Select("SELECT * FROM user")
    @Results({
        @Result(column = "create_time",property = "createTime")  //javaType = java.util.Date.class        
    })
    List<User> getAll();
  
    @Select("SELECT * FROM user WHERE id = #{id}")
    @Results({
    	 @Result(column = "create_time",property = "createTime")
    })
    User findById(Long id);

    @Update("UPDATE user SET name=#{name} WHERE id =#{id}")
    void update(User user);

    @Delete("DELETE FROM user WHERE id =#{userId}")
    void delete(Long userId);
 
 3、增加API

	@GetMapping("find_all")
	public Object findAll(){
       return JsonData.buildSuccess(userMapper.getAll());
	}
	
	@GetMapping("find_by_Id")
	public Object findById(long id){
       return JsonData.buildSuccess(userMapper.findById(id));
	}
	
	@GetMapping("del_by_id")
	public Object delById(long id){
	userMapper.delete(id);
       return JsonData.buildSuccess();
	}
	
	@GetMapping("update")
	public Object update(String name,int id){
		User user = new User();
		user.setName(name);
		user.setId(id);
		userMapper.update(user);
	    return JsonData.buildSuccess();
	}

08-4 事务介绍和常见的隔离级别，传播行为

简介：讲解什么是数据库事务，常见的隔离级别和传播行为

1、介绍什么是事务，单机事务，分布式事务处理等

2、讲解场景的隔离级别
	Serializable： 最严格，串行处理，消耗资源大
	Repeatable Read：保证了一个事务不会修改已经由另一个事务读取但未提交（回滚）的数据
	Read Committed：大多数主流数据库的默认事务等级
	Read Uncommitted：保证了读取过程中不会读取到非法数据。


3、讲解常见的传播行为
	PROPAGATION_REQUIRED--支持当前事务，如果当前没有事务，就新建一个事务,最常见的选择。

	PROPAGATION_SUPPORTS--支持当前事务，如果当前没有事务，就以非事务方式执行。

	PROPAGATION_MANDATORY--支持当前事务，如果当前没有事务，就抛出异常。

	PROPAGATION_REQUIRES_NEW--新建事务，如果当前存在事务，把当前事务挂起, 两个事务之间没有关系，一个异常，一个提交，不会同时回滚

	PROPAGATION_NOT_SUPPORTED--以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

	PROPAGATION_NEVER--以非事务方式执行，如果当前存在事务，则抛出异常

08-5 SpringBoot整合mybaits之事务处理实战

简介：SpringBoot整合Mybatis之事务处理实战

1、service逻辑引入事务 @Transantional(propagation=Propagation.REQUIRED)

2、service代码
		@Override
	    @Transactional
		public int addAccount() {
			User user = new User();
			user.setAge(9);
			user.setCreateTime(new Date());
			user.setName("事务测试");
			user.setPhone("000121212");
			
			userMapper.insert(user);
	        int a = 1/0;
	
			return user.getId();
		}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。