0

I'm trying to run a custom query, and getting a strange (Seemingly unrelated) error. Error I'm getting is: o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 90110, SQLState: 90110 Values of types "BOOLEAN" and "INTEGER" are not comparable; SQL statement:

select episode0_.episode_id as episode_1_1_, episode0_.air_date as air_date2_1_, episode0_.episode_number as episode_3_1_, episode0_.name as name4_1_, episode0_.season as season5_1_, episode0_.show_id as show_id7_1_, episode0_.watched as watched6_1_ from episode episode0_ where episode0_.episode_number=(select min(episode1_.episode_number) from episode episode1_ where episode1_.watched=0) and episode0_.season=(select min(episode2_.season) from episode episode2_ where episode2_.watched=0) and episode0_.show_id=? [90110-210]

Things I've tried: native query, and this: Custom Query H2 - Spring Boot

My repository:

import model.Episode;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Optional;

public interface EpisodeRepository extends JpaRepository<Episode, Long> {
    Optional<Episode> findByEpisodeId(Long episodeId);

    @Query("from EPISODE where episodeNumber = (SELECT MIN(episodeNumber) FROM EPISODE WHERE watched is false)" +
            " AND season = (SELECT MIN(season) FROM EPISODE WHERE watched is false) " +
            " AND show.showId = :showId")
    Optional<Episode> findNextUnwatchedEpisode(@Param("showId") Long showId);
}

My entity:

package model;

import lombok.*;

import javax.persistence.*;
import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
@Entity(name = "EPISODE")
public class Episode {
    @Id
    @Column(name = "EPISODE_ID", nullable = false)
    private Long episodeId;

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

    @Column(name = "SEASON", nullable = false)
    private Integer season;

    @Column(name = "EPISODE_NUMBER", nullable = false)
    private Integer episodeNumber;

    @Column(name = "AIR_DATE", nullable = false)
    private Date airDate;

    @Column(name = "WATCHED", nullable = false)
    private boolean watched;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="SHOW_ID")
    private TVShow show;
}

application.properties:

spring.datasource.url=jdbc:h2:mem:my_tv;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.