Spring Boot アプリケーションでOracleに意図しないSQLが発行されてORA-00904エラーとなる

Question

ご覧いただきありがとうございます。
Spring Boot でチャットアプリを作成しています。
/roomsにアクセスしてRoomRepository.findAllByOrderByIdDesc()メソッドを呼んで
select * from app.rooms order by id descを発行したかったのですが、
テーブル名と列名が意図しない変換をされて以下のようなエラーとなりました。
なぜこのように変換されてしまうのでしょうか。
よろしくお願いいたします。

2018-01-23 00:09:55.172  INFO 28016 --- [p-nio-80-exec-2] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select room0_.id as id1_2_, room0_.owner_id_id as owner_id_id2_2_ from app.rooms room0_ order by room0_.id desc
2018-01-23 00:09:55.363  WARN 28016 --- [p-nio-80-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 904, SQLState: 42000
2018-01-23 00:09:55.363 ERROR 28016 --- [p-nio-80-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00904: "ROOM0_"."OWNER_ID_ID": invalid identifier

2018-01-23 00:09:55.374 ERROR 28016 --- [p-nio-80-exec-2] 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-00904: "ROOM0_"."OWNER_ID_ID": invalid identifier

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);

RoomsController.java

package com.springTalk.web;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.validation.Valid;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

import com.springTalk.domain.Message;
import com.springTalk.domain.Room;
import com.springTalk.domain.User;
import com.springTalk.service.MessageService;
import com.springTalk.service.RoomService;
import com.springTalk.service.UserService;

@Controller
public class RoomsController {
  @Autowired
  private UserService userService;
  @Autowired
  private RoomService roomService;
  @Autowired
  private MessageService messageService;

  @GetMapping("/rooms")
  public String roomsGet(Model model) {
    List<Room> rooms = roomService.getRoomsOrderByIdDesc();
    model.addAttribute("room", rooms);
    model.addAttribute("roomsForm", new RoomsForm());
    return "rooms";
  }

  @PostMapping("/rooms")
  public String roomsPost(Model model, @Valid RoomsForm roomsForm, BindingResult bindingResult,
      HttpServletRequest request) {
    if (bindingResult.hasErrors()) {
      List<Room> rooms = roomService.getRoomsOrderByIdDesc();
      model.addAttribute("room", rooms);
      model.addAttribute("roomsForm", new RoomsForm());
      return "rooms";
    }

    // get dummy user
    // ToDo: get user id from request
    final Long OWNER_ID = 1L;
    User user = userService.getUserById(OWNER_ID).get(0);
    Room room = roomService.saveAndFlush(new Room(user));
    messageService.save(new Message(user, room, roomsForm.getText()));
    return "redirect:/rooms";
  }
}

RoomService.java

package com.springTalk.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.springTalk.domain.Room;
import com.springTalk.domain.RoomRepository;

@Service
public class RoomService {

  @Autowired
  private RoomRepository repository;

  public List<Room> getRoomsOrderByIdDesc() {
    return repository.findAllByOrderByIdDesc();
  }

  public List<Room> getRoomsById(Long id) {
    return repository.findById(id);
  }

  @Transactional
  public void save(Room room) {
    repository.save(room);
  }

  @Transactional
  public Room saveAndFlush(Room room) {
    return repository.saveAndFlush(room);
  }
}

RoomRepository.java

package com.springTalk.domain;

import java.util.List;

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

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

  List<Room> findById(Long id);

  // ToDo: find where userId and room pair is in membership

}

Room.java

package com.springTalk.domain;

import java.util.List;

import javax.persistence.CascadeType;
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.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "rooms", schema = "app")
public class Room {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  @JoinColumn(nullable = false)
  @ManyToOne(cascade = CascadeType.ALL)
  private User ownerId;

  @OneToMany(mappedBy = "roomId", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  private List<Membership> memberships;

  @OneToMany(mappedBy = "roomId", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  private List<Message> messages;

  // JPA requirement
  protected Room() {
  }

  public Room(User ownerId) {
    this.ownerId = ownerId;
  }

  public Long getId() {
    return id;
  }

  public User getOwnerId() {
    return ownerId;
  }

  public void setOwnerId(User ownerId) {
    this.ownerId = ownerId;
  }
}

application.yml

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        connection:
          characterEncoding: utf-8
          CharSet: utf-8
          useUnicode: true
  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

環境
Spring Boot 1.5.9 RELEASE
Oracle VM VirtualBox 5.1.22 r115126 (Qt5.6.2)
CentOS 7.3.1611
openjdk version "1.8.0_141"
Oracle Database 12c Release 2

Answer 1

Entityの定義が間違っているように見えます
結合してテーブルを取得する場合、JoinColumnで外部キーカラムを指定しましょう
以下のようにRoom.javaを変更してみてください

Room.java

// ~~~~~~~~~~~~~~~~~~
  @JoinColumn(name = "owner_id", nullable = false)
  @ManyToOne(cascade = CascadeType.ALL)
  private User user;
// ~~~~~~~~~~~~~~~~~~
  public User getUser() {
    return user;
  }

  public void setUser(User user) {
    this.user = user;
  }
// ~~~~~~~~~~~~~~~~~~
}

※ JoinColumnのname指定がない場合、外部キー名はプロパティ名 + 参照先の主キー名 となります
プロパティ名をownerとすれば、JoinColumnのname属性は不要となります

Comments

[退会済みユーザー]

できましたー！
ありがとうございます！