sqlplusから実行すると正常に返るSQLをSpring Data JPA (Hibernate) から実行するとエラーORA-00933が発生する

Question

いつもありがとうございます。

以下のSQLをsqlplusから実行すると正常に値が帰るのですが、
JPAの@Queryで実行するとORA-00933: SQL command not properly endedが発生します。
ログを見ると意図したSQLが出力されているのですが、どうすれば正しいSQLと認識してもらえるのでしょうか。

select * from app.messages where id in (select min(id) as id from app.messages group by room_id) order by id desc;

MessageRepository.java

package com.springTalk.domain;

import java.util.List;

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

@Repository
public interface MessageRepository extends JpaRepository<Message, Long> {
  List<Message> findAllByOrderByIdDesc();

  List<Message> findByRoomIdOrderById(Long roomId);

  List<Message> findBySenderIdOrderById(Long senderId);

  // native query dependent on Oracle DB
  final String QUERY = "select * from app.messages where id in (select min(id) as id from app.messages group by room_id) order by id desc;";

  @Query(value = QUERY, nativeQuery = true)
  List<Message> findFirstMessageOfRoomOrderByIdDesc();
}

Message.java

package com.springTalk.domain;

import java.util.Date;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name = "messages", schema = "app")
public class Message {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "message_id_sequence_generator")
  @SequenceGenerator(name = "message_id_sequence_generator", sequenceName = "message_id_sequence", schema = "app")
  private Long id;

  @JoinColumn(name = "sender_id", nullable = false)
  @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  private User senderId;

  @JoinColumn(name = "room_id", nullable = false)
  @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
  private Room roomId;

  @Column(nullable = false)
  private String text;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(insertable = false, updatable = false)
  private Date createdAt;

  // JPA requirement
  protected Message() {
  }

  public Message(User senderId, Room roomId, String text) {
    this.senderId = senderId;
    this.roomId = roomId;
    this.text = text;
  }

  public Long getId() {
    return id;
  }

  public User getSenderId() {
    return senderId;
  }

  public void setSenderId(User senderId) {
    this.senderId = senderId;
  }

  public Room getRoomId() {
    return roomId;
  }

  public void setRoomId(Room roomId) {
    this.roomId = roomId;
  }

  public String getText() {
    return text;
  }

  public void setText(String text) {
    this.text = text;
  }

  public Date getCreatedAt() {
    return createdAt;
  }
}

DDL

create table app.users (
  id number generated always as identity primary key,
  name nvarchar2(32) not null,
  password nvarchar2(32) not null);

create table app.rooms (
  id number generated always as identity primary key,
  owner_id number references app.users(id));

create table app.memberships (
  membership_id number generated always as identity primary key,
  room_id number references app.users(id),
  user_id number references app.rooms(id));

create table app.messages (
  id number generated always as identity primary key,
  sender_id number references app.users(id),
  room_id number references app.rooms(id),
  text nvarchar2(256) not null,
  created_at timestamp default systimestamp not null);

log

Hibernate: select * from app.messages where id in (select min(id) as id from app.messages group by room_id) order by id desc;
2018-02-14 20:20:51.804  WARN 4856 --- [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 933, SQLState: 42000
2018-02-14 20:20:51.804 ERROR 4856 --- [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00933: SQL command not properly ended

2018-02-14 20:20:51.826 ERROR 4856 --- [p-nio-80-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

oracle.jdbc.OracleDatabaseException: ORA-00933: SQL command not properly ended

application.yml

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        connection:
          characterEncoding: utf-8
          CharSet: utf-8
          useUnicode: true
        dialect: org.hibernate.dialect.Oracle12cDialect
  datasource:
    url: jdbc:oracle:thin:@localhost:1521:chatdb
    username: app
    password: password
    driverClassName: oracle.jdbc.driver.OracleDriver
    connectionProperties: useUnicode=true;characterEncoding=utf-8;
server:
  port: 80

環境
CentOS 7.3.1611
Oracle Database 12c Release2
OpenJDK 1.8.0_141
Spring Boot 1.5.9.RELEASE
Hibernate 5.0.12.Final

よろしくお願いいたします。

Answer 1

 final String QUERY = "select * from app.messages where id in (select min(id) as id from app.messages group by room_id) order by id desc;";

descの次の ; を削ったらどうなりますか？

Comments

[退会済みユーザー]

できました！！？？？
ここつけたらダメなんですか……ちょっと予想外の結果でした。
ありがとうございます！

[退会済みユーザー]

ドライバ側にSQLインジェクション対策がはいってるのか
（無意識に削除している気がする私がいる）

[Orlofsky]

sqlplusではSQLの最後に付ける ; がここでSQLが終わるから実行してね、って意味合いのようです。サポート契約が有効でしたら、オラクル社カスタマ・サポート・センターに問合せてみては？サポートからの回答はサポートの了解を得ない限り公開することは禁止されています。
EXECUTE IMMEDIATE で動的SQL (Oracle テーブル毎のレコード件数を取得)[https://qiita.com/Papageno/items/6d06f55c2c60cdfb82f3] を実行する時も最後のセミコロンは付けません。

[退会済みユーザー]

個人で趣味の開発なのでサポート契約はないのですが
;はあくまで対話的に入力するときのターミネーターであって
プログラムの中にSQL決め打ちするときはターミネーターつけないってことでしょうか？
皆さんありがとうございます。

Answer 2

アノテーションのQuery はHQMLで、通常のSQLはNativeQueryだったはず

Comments

[退会済みユーザー]

とおもっらパラメーターで指定してたのね