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