java - Spring Boot 查询未更新 mysql 数据库

Question

查询更新未更新 MySQL 数据库。正在执行方法 updateStatusTest 并通过测试，但是当我查看数据库时，数据是相同的。下面是我使用的代码:

mysql> SELECT * FROM spring_course_db.request;

+----+---------------------+-------------------------------------------+-------+----------------+----------+
| id | creation_date       | description                               | state | subject        | owner_id |
+----+---------------------+-------------------------------------------+-------+----------------+----------+
| 1  | 2019-09-24 20:03:28 | Pretendo obter um laptop HP, de RAM 16 GB | OPEN  | Novo Laptop HP | 1        |
+----+---------------------+-------------------------------------------+-------+----------------+----------+
1 row in set (0,01 sec)

The state show be IN_PROCESS.

RequestStageRepository

package com.spring.course.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.spring.course.domain.Request;
import com.spring.course.domain.RequestStage;
import com.spring.course.enums.RequestState;

@Repository
public interface RequestStageRepository extends JpaRepository<RequestStage, Long>{

    public List<RequestStage> findAllByRequestId(Long id);

    @Query("UPDATE request SET state = :state WHERE id = :id")
    public Request updateStatus(Long id, RequestState state);

}

RequestRepositoryTests

package com.spring.course.repository;

import static org.assertj.core.api.Assertions.assertThat;

import java.util.Date;
import java.util.List;
import java.util.Optional;

import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.MethodSorters;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.spring.course.domain.Request;
import com.spring.course.domain.User;
import com.spring.course.enums.RequestState;

@FixMethodOrder(MethodSorters.NAME_ASCENDING)
@RunWith(SpringRunner.class)
@SpringBootTest
public class RequestRepositoryTests {

    @Autowired
    private RequestRepository requestRepository;

    @Test
    public void AsaveTest() {

        User owner = new User();

        owner.setId(1L);

        Request request = new Request(null, "Novo Laptop HP", "Pretendo obter um laptop HP", new Date(), RequestState.OPEN, owner, null);  

        Request createdRequest = requestRepository.save(request);

        assertThat(createdRequest.getId()).isEqualTo(1L);
    }

    @Test
    public void updateTest() {

        User owner = new User();

        owner.setId(1L);

        Request request = new Request(1L, "Novo Laptop HP", "Pretendo obter um laptop HP, de RAM 16 GB", null, RequestState.OPEN, owner, null);  

        Request updatedRequest = requestRepository.save(request);

        assertThat(updatedRequest.getDescription()).isEqualTo("Pretendo obter um laptop HP, de RAM 16 GB");
    }

    @Test
    public void getByIdTest() {

        Optional<Request> result = requestRepository.findById(1L);

        Request request = result.get();

        assertThat(request.getSubject()).isEqualTo("Novo Laptop HP");
    }

    @Test
    public void listTest() {

        List<Request> requests = requestRepository.findAll();

        assertThat(requests.size()).isEqualTo(1);
    }

    @Test
    public void listByOwnerIdTest() {

        List<Request> requests = requestRepository.findAllByOwnerId(1L);

        assertThat(requests.size()).isEqualTo(1);
    }

    @Test
    public void updateStatusTest() {

        int affectedRows = requestRepository.updateStatus(1L, RequestState.IN_PROCESS);
        assertThat(affectedRows).isEqualTo(1);  
        }
}

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 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.8.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.spring.course</groupId>
    <artifactId>spring-course2</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>spring-course2</name>
    <description>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-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    </dependencies>

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

</project>

Answer 1

native 更新查询或 JPQL 更新查询只返回 int 没有更新的记录，而且它们需要 @Modifying 注解 Update Queries

JPQL

@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status, 
  @Param("name") String name);

The return value defines how many rows the execution of the query updated.

原生

您也可以使用 native 查询修改数据库的状态——我们只需要添加@Modifying 注释

@Modifying
@Query(value = "update Users u set u.status = ? where u.name = ?", 
nativeQuery = true)

int updateUserSetStatusForNameNative(整数状态, 字符串名称);

5.3.8. Modifying Queries来自带有以下示例的 spring data jpa 文档

@Modifying
@Query("update User u set u.firstname = ?1 where u.lastname = ?2")
int setFixedFirstnameFor(String firstname, String lastname);

如果你想根据id更新记录并且需要返回更新后的记录，那么你可以使用save方法

requestRepository.save(request)

注意:确保您已为所有属性设置值，否则您最终会看到null 或默认值