質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.01%

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 1,806

mjxg

score 18

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

以下の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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+2

 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の次の ; を削ったらどうなりますか?

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/14 21:35

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

    キャンセル

  • 2018/02/14 21:38

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

    キャンセル

  • 2018/02/14 21:46

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

    キャンセル

  • 2018/02/14 21:52

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

    キャンセル

0

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/14 21:15

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.01%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • Javaに関する質問
  • sqlplusから実行すると正常に返るSQLをSpring Data JPA (Hibernate) から実行するとエラーORA-00933が発生する