springboot jpa增删改查

原创

mb613739c94fb76 2021-09-13 16:36:54 博主文章分类：javaweb ©著作权

文章标签 c++ spring java mysql maven 文章分类 Android 移动开发

©著作权归作者所有：来自51CTO博客作者mb613739c94fb76的原创作品，请联系作者获取转载授权，否则将追究法律责任

step1:pom

<?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.5.4</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.dongmingdi</groupId>
  <artifactId>spring-boot-06-data-jpa</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>spring-boot-06-data-jpa</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-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <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>
  </dependencies>

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

</project>

step2: yml

spring:
  datasource:
    url: jdbc:mysql://localhost/mysql?characterEncoding=utf8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      #更新或者创建数据表结构
      ddl-auto: create
      #控制台显示sql
    show-sql: true

step3:

package com.org;


import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DataJpaApplication {

    //post 查询  http://localhost:8080/test/query?id=209
    //post 新增  http://localhost:8080/test/add?email=jft@qq.com&last_name=李丽芬&id=1
    //post 删除  http://localhost:8080/test/delete?id=20
    public static void main(String[] args) {
        SpringApplication.run(DataJpaApplication.class, args);
    }

}

step4:

package com.org.repository;


import com.org.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepostitory extends JpaRepository<User, Integer> {
}

step5:

package com.org.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "tbl_user") // 默认是类名小写
public class User {

    @Id  //    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自增主键
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;

    @Column(name = "last_name", length = 50)
    private String lastName;

    @Column // 不写属性名就是默认字段名
    private String email;


    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

step6:

package com.org.controller;


import com.org.entity.User;
import com.org.repository.UserRepostitory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Optional;


@RestController
public class TestController {

    @Autowired
    UserRepostitory userRepostitory;

    @RequestMapping(value = {"/test/add"}, method = RequestMethod.POST)
    public User getAdd(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        String email = request.getParameter("email");
        String last_name = request.getParameter("last_name");
        System.out.println(id + "\t" + email + "\t" + last_name);
        User user = new User();
        user.setId(Integer.parseInt(id));
        user.setEmail(email);
        user.setLastName(last_name);
        userRepostitory.save(user);
        return user;
    }

    @RequestMapping(value = {"/test/query"}, method = RequestMethod.POST)
    public Optional<User> getQuery(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        System.out.println(id + "\t");
        Optional<User> user = userRepostitory.findById(Integer.parseInt(id));
        return user;
    }

    @RequestMapping(value = {"/test/delete"}, method = RequestMethod.POST)
    public String getDelete(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        System.out.println(id + "\t");
        userRepostitory.deleteById(Integer.parseInt(id));
        return "delete";
    }


}

sql: user

tbl_user,"CREATE TABLE `tbl_user` (
  `id` int NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"

sql:address

tbl_address,"CREATE TABLE `tbl_address` (
  `id` int NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"

更新版本

step1:

package com.org.entity;


import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.time.LocalDateTime;
import java.util.Date;

@Setter
@Getter
//@AllArgsConstructor
@NoArgsConstructor
public class ProductDTO {

    private Integer id;
    private String email;
    private String last_name;
    private String address;
    private String city;

    public ProductDTO(Integer id, String email, String last_name, String address, String city) {
        this.id = id;
        this.email = email;
        this.last_name = last_name;
        this.address = address;
        this.city = city;
    }

    public Integer getId() {
        return id;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getLast_name() {
        return last_name;
    }

    public void setLast_name(String last_name) {
        this.last_name = last_name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }
}

step2:

package com.org.repository;


import com.org.entity.Address;
import com.org.entity.ProductDTO;
import com.org.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface UserRepostitory extends JpaRepository<User, Integer> {

    @Query("SELECT u FROM User u WHERE u.id = ?1")
    public User findByEmail(int id);

    @Query("SELECT u FROM User u WHERE u.lastName = ?1")
    public User findByName(String name);

    @Query("SELECT u.lastName FROM User u WHERE u.id = ?1")
    public String findByName2(int id);

    @Query(value = "SELECT u.id,u.email,u.lastName,a.address,a.city FROM User u INNER JOIN Address a ON u.id = a.id ")
    List<Map<String,ProductDTO>> findByDeletedAtIsNotNull();

    @Query(value = "SELECT new com.org.entity.ProductDTO(u.id,u.email,u.lastName,a.address,a.city) FROM User u INNER JOIN Address a ON u.id = a.id ")
    List<ProductDTO> findByDeletedAtIsNotNull2();
}

step3:

package com.org.controller;


import com.org.entity.Address;
import com.org.entity.ProductDTO;
import com.org.entity.User;
import com.org.repository.UserRepostitory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Optional;


@RestController
public class TestController {

    @Autowired
    UserRepostitory userRepostitory;

    @RequestMapping(value = {"/test/add"}, method = RequestMethod.POST)
    public User getAdd(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        String email = request.getParameter("email");
        String last_name = request.getParameter("last_name");
        System.out.println(id + "\t" + email + "\t" + last_name);
        User user = new User();
        user.setId(Integer.parseInt(id));
        user.setEmail(email);
        user.setLastName(last_name);
        userRepostitory.save(user);
        return user;
    }

    @RequestMapping(value = {"/test/query"}, method = RequestMethod.POST)
    public Optional<User> getQuery(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        System.out.println(id + "\t");
        Optional<User> user = userRepostitory.findById(Integer.parseInt(id));
        return user;
    }

    @RequestMapping(value = {"/test/delete"}, method = RequestMethod.POST)
    public String getDelete(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        System.out.println(id + "\t");
        userRepostitory.deleteById(Integer.parseInt(id));
        return "delete";
    }

    @RequestMapping(value = {"/test/select"}, method = RequestMethod.POST)
    public User getSelect(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        System.out.println(id + "\t");


        User user = userRepostitory.findByEmail(Integer.parseInt(id));
        System.out.println(user);
        return user;
    }

    @RequestMapping(value = {"/test/select2"}, method = RequestMethod.POST)
    public User getSelect2(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String name = request.getParameter("name");
        System.out.println(name + "\t");

        User user = userRepostitory.findByName(name);
        System.out.println(user);
        return user;
    }

    @RequestMapping(value = {"/test/select3"}, method = RequestMethod.POST)
    public String getSelect3(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String id = request.getParameter("id");
        System.out.println(id + "\t");

        String name = userRepostitory.findByName2(Integer.parseInt(id));
        System.out.println(name);
        return name;
    }

    @RequestMapping(value = {"/test/select4"}, method = RequestMethod.GET)
    public String getSelect4(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        System.out.println("click_get_test");
        List<Map<String, ProductDTO>> list = userRepostitory.findByDeletedAtIsNotNull();
        List<ProductDTO> list2 = userRepostitory.findByDeletedAtIsNotNull2();
        System.out.println(list+"\t"+list2);
        return "name";
    }

}

end