いつもありがとうございます。
以下のSQLをsqlplusから実行すると正常に値が帰るのですが、
JPAの@Queryで実行するとORA-00933: SQL command not properly endedが発生します。
ログを見ると意図したSQLが出力されているのですが、どうすれば正しいSQLと認識してもらえるのでしょうか。
lang
1select * from app.messages where id in (select min(id) as id from app.messages group by room_id) order by id desc;
MessageRepository.java
lang
1package com.springTalk.domain; 2 3import java.util.List; 4 5import org.springframework.data.jpa.repository.JpaRepository; 6import org.springframework.data.jpa.repository.Query; 7import org.springframework.stereotype.Repository; 8 9@Repository 10public interface MessageRepository extends JpaRepository<Message, Long> { 11 List<Message> findAllByOrderByIdDesc(); 12 13 List<Message> findByRoomIdOrderById(Long roomId); 14 15 List<Message> findBySenderIdOrderById(Long senderId); 16 17 // native query dependent on Oracle DB 18 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;"; 19 20 @Query(value = QUERY, nativeQuery = true) 21 List<Message> findFirstMessageOfRoomOrderByIdDesc(); 22}
Message.java
lang
1package com.springTalk.domain; 2 3import java.util.Date; 4 5import javax.persistence.CascadeType; 6import javax.persistence.Column; 7import javax.persistence.Entity; 8import javax.persistence.FetchType; 9import javax.persistence.GeneratedValue; 10import javax.persistence.GenerationType; 11import javax.persistence.Id; 12import javax.persistence.JoinColumn; 13import javax.persistence.ManyToOne; 14import javax.persistence.SequenceGenerator; 15import javax.persistence.Table; 16import javax.persistence.Temporal; 17import javax.persistence.TemporalType; 18 19@Entity 20@Table(name = "messages", schema = "app") 21public class Message { 22 @Id 23 @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "message_id_sequence_generator") 24 @SequenceGenerator(name = "message_id_sequence_generator", sequenceName = "message_id_sequence", schema = "app") 25 private Long id; 26 27 @JoinColumn(name = "sender_id", nullable = false) 28 @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL) 29 private User senderId; 30 31 @JoinColumn(name = "room_id", nullable = false) 32 @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL) 33 private Room roomId; 34 35 @Column(nullable = false) 36 private String text; 37 38 @Temporal(TemporalType.TIMESTAMP) 39 @Column(insertable = false, updatable = false) 40 private Date createdAt; 41 42 // JPA requirement 43 protected Message() { 44 } 45 46 public Message(User senderId, Room roomId, String text) { 47 this.senderId = senderId; 48 this.roomId = roomId; 49 this.text = text; 50 } 51 52 public Long getId() { 53 return id; 54 } 55 56 public User getSenderId() { 57 return senderId; 58 } 59 60 public void setSenderId(User senderId) { 61 this.senderId = senderId; 62 } 63 64 public Room getRoomId() { 65 return roomId; 66 } 67 68 public void setRoomId(Room roomId) { 69 this.roomId = roomId; 70 } 71 72 public String getText() { 73 return text; 74 } 75 76 public void setText(String text) { 77 this.text = text; 78 } 79 80 public Date getCreatedAt() { 81 return createdAt; 82 } 83}
DDL
lang
1create table app.users ( 2 id number generated always as identity primary key, 3 name nvarchar2(32) not null, 4 password nvarchar2(32) not null); 5 6create table app.rooms ( 7 id number generated always as identity primary key, 8 owner_id number references app.users(id)); 9 10create table app.memberships ( 11 membership_id number generated always as identity primary key, 12 room_id number references app.users(id), 13 user_id number references app.rooms(id)); 14 15create table app.messages ( 16 id number generated always as identity primary key, 17 sender_id number references app.users(id), 18 room_id number references app.rooms(id), 19 text nvarchar2(256) not null, 20 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
lang
1spring: 2 jpa: 3 show-sql: true 4 properties: 5 hibernate: 6 connection: 7 characterEncoding: utf-8 8 CharSet: utf-8 9 useUnicode: true 10 dialect: org.hibernate.dialect.Oracle12cDialect 11 datasource: 12 url: jdbc:oracle:thin:@localhost:1521:chatdb 13 username: app 14 password: password 15 driverClassName: oracle.jdbc.driver.OracleDriver 16 connectionProperties: useUnicode=true;characterEncoding=utf-8; 17server: 18 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
よろしくお願いいたします。
回答2件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2018/02/14 12:35
退会済みユーザー
2018/02/14 12:38
2018/02/14 12:46
退会済みユーザー
2018/02/14 12:52