java - Spring JPA在不同的查询中返回空列表

Question

我有一个奇怪的怪癖，当我运行一个调用单个列中所有不同值的 nativeQuery 时，我没有得到任何结果。但是，当我从查询中删除“不同”运算符时，它工作得很好，告诉我这不是数据库连接或内容问题。

控制器方法：

    @GetMapping("/getGenres")
    public ResponseEntity<Object> getGenres() {
        return ResponseEntity.ok(genreRepo.findDistinctGenre());
    }

存储库接口方法

    @Query(value = "SELECT DISTINCT genre FROM genres", nativeQuery = true)
    List<String> findDistinctGenre();

此方法的结果

[]

“工作”，但不是不同的方法

    @Query(value = "SELECT genre FROM genres", nativeQuery = true)
    List<String> findDistinctGenre();

这种方法的结果

["2D","Survival","Battle Royal","Builder","Shooter","RPG"]

我显然可以编写一个“不同”的方法来遍历这个列表，但对我来说查询不起作用似乎很奇怪

编辑：添加存储库和实体类，因为在评论中要求这些

存储库接口：

@Repository
public interface GenreRepository extends CrudRepository<Genre, Integer> {
    @Query(value = "SELECT DISTINCT genre FROM genres", nativeQuery = true)
    List<String> findDistinctGenre();
}

流派实体类：

@Entity
@Table(name = "genres")
@IdClass(GenreId.class)
public class Genre{

    @Id
    @Column(name = "game_id", nullable = false)
    private int gameId;

    @Id
    @Column(name = "genre", nullable = false)
    private String genre;

    public int getGameId() {
        return gameId;
    }

    public void setGameId(int gameId) {
        this.gameId = gameId;
    }

    public String getGenre() {
        return genre;
    }

    public void setGenre(String genre) {
        this.genre = genre;
    }
}

GenreId 类：

public class GenreId implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    private int gameId;

    private int genre;

    public UsersInGroupId() {

    }

    public UsersInGroupId(int gameId, String genre) {
        this.gameId= gameId;
        this.genre= genre;
    }

    public int getGameId() {
        return gameId;
    }

    public void setGameId(int gameId) {
        this.gameId = gameId;
    }

    public String getGenre() {
        return genre;
    }

    public void setGenre(String genre) {
        this.genre = genre;
    }

    public boolean equals(Object o) {
        if (!(o instanceof GenreId)) {
            return false;
        }

        GenreId id = (GenreId) o;

        return id.gameId == this.gameId && id.genre.equals(this.genre);
    }
}

日志（包括从 spring.jpa.show-sql=true 显示的查询）

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.2.RELEASE)

2020-11-20 17:52:19.853  INFO 13112 --- [           main] edu.edgewood.ApplicationRuntime          : Starting ApplicationRuntime on LAPTOP-V9B5V6E5 with PID 13112 (C:\Users\logan\eclipse-workspace\final-project\target\classes started by logan in C:\Users\logan\eclipse-workspace\final-project)
2020-11-20 17:52:19.856  INFO 13112 --- [           main] edu.edgewood.ApplicationRuntime          : No active profile set, falling back to default profiles: default
2020-11-20 17:52:20.846  INFO 13112 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFERRED mode.
2020-11-20 17:52:20.929  INFO 13112 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 73ms. Found 6 JPA repository interfaces.
2020-11-20 17:52:22.060  INFO 13112 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2020-11-20 17:52:22.072  INFO 13112 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2020-11-20 17:52:22.073  INFO 13112 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.37]
2020-11-20 17:52:22.191  INFO 13112 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-11-20 17:52:22.192  INFO 13112 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2272 ms
2020-11-20 17:52:22.640  INFO 13112 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2020-11-20 17:52:22.706  INFO 13112 --- [         task-1] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2020-11-20 17:52:22.752  WARN 13112 --- [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2020-11-20 17:52:22.784  INFO 13112 --- [         task-1] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 5.4.18.Final
2020-11-20 17:52:23.052  INFO 13112 --- [         task-1] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2020-11-20 17:52:23.266  INFO 13112 --- [         task-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-11-20 17:52:24.214  INFO 13112 --- [         task-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-11-20 17:52:24.255  INFO 13112 --- [         task-1] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL8Dialect
2020-11-20 17:52:24.593  WARN 13112 --- [         task-1] org.hibernate.mapping.RootClass          : HHH000039: Composite-id class does not override hashCode(): edu.edgewood.data.UsersInGroupId
2020-11-20 17:52:25.157  INFO 13112 --- [         task-1] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2020-11-20 17:52:25.168  INFO 13112 --- [         task-1] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2020-11-20 17:52:25.230  INFO 13112 --- [           main] o.s.b.a.e.web.EndpointLinksResolver      : Exposing 2 endpoint(s) beneath base path '/actuator'
2020-11-20 17:52:25.298  INFO 13112 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-11-20 17:52:25.300  INFO 13112 --- [           main] DeferredRepositoryInitializationListener : Triggering deferred initialization of Spring Data repositories…
2020-11-20 17:52:26.029  INFO 13112 --- [           main] DeferredRepositoryInitializationListener : Spring Data repositories initialized!
2020-11-20 17:52:26.039  INFO 13112 --- [           main] edu.edgewood.ApplicationRuntime          : Started ApplicationRuntime in 6.627 seconds (JVM running for 7.162)
2020-11-20 17:52:31.094  INFO 13112 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-11-20 17:52:31.094  INFO 13112 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2020-11-20 17:52:31.102  INFO 13112 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 8 ms
Hibernate: select distinct(c.genre) from genres c

创建和修改表的 SQL

CREATE TABLE `genres` (
  `game_id` int(8) NOT NULL,
  `genre` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `genres` (`game_id`, `genre`) VALUES
(1, 'Survival'),
(1, '2D'),
(2, 'Shooter'),
(2, 'Battle Royal'),
(2, 'Builder'),
(3, 'RPG');

ALTER TABLE `genres`
  ADD PRIMARY KEY (`game_id`,`genre`),
  ADD KEY `genres_ibfk1` (`game_id`),
  ADD FULLTEXT(`genre`);

ALTER TABLE `genres`
  ADD CONSTRAINT `genres_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `games` (`game_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

标签： javaspringspring-bootjpaspring-data-jpa

Answer 1

使用 ADD FULLTEXT( ) 更改表后会出现区别genre；