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

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

ただいまの
回答率

87.49%

jpqlで3つのテーブルの結合結果を返したい

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 5,489

score 9

@Queryアノテーションを用いて3つのテーブルを結合した結果をDBから取得したいのですが、うまくいきません。
実行すると以下のようなエラーがでます。

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'i.category' [SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id)FROM jp.co.sss.shop.entity.OrderItem oi INNER JOIN  oi.item iINNER JOIN i.category c GROUP BY i.id,i.name,i.image, i.price, c.name ORDER BY count(i.id) DESC ]

package jp.co.sss.shop.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import jp.co.sss.shop.entity.Category;
import jp.co.sss.shop.entity.Item;
import java.util.List;
/**
 * itemsテーブル用リポジトリ
 *
 * @author System Shared
 */
@Repository
public interface ItemRepository extends JpaRepository<Item, Integer> {

    //一覧表示(売れ筋順)
    @Query("SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id)" + 
            "FROM OrderItem oi INNER JOIN  oi.item i" + 
            "INNER JOIN i.category c " + 
            "GROUP BY i.id,i.name,i.image, i.price, c.name " + 
            "ORDER BY count(i.id) DESC ")
    public List<Item> sortByOrderCount();

}
package jp.co.sss.shop.entity;

import java.sql.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

/**
 * 商品情報のエンティティクラス
 *
 * @author SystemShared
 */
@Entity
@Table(name = "items")
@NamedQuery(name="OrderById", query="SELECT i FROM Item i INNER JOIN i.category c ORDER BY i.id DESC")
public class Item {
    /**
     * 商品ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_items_gen")
    @SequenceGenerator(name = "seq_items_gen", sequenceName = "seq_items", allocationSize = 1)
    private Integer            id;

    /**
     * 商品名
     */
    @Column
    private String            name;

    /**
     * 価格
     */
    @Column
    private Integer            price;

    /**
     * 商品説明
     */
    @Column
    private String            description;

    /**
     * 在庫数
     */
    @Column
    private Integer            stock;

    /**
     * 商品画像ファイル名
     */
    @Column
    private String            image;

    /**
     * 削除フラグ
     */
    @Column(insertable = false)
    private Integer            deleteFlag;

    /**
     * 登録日付
     */
    @Column(insertable = false, updatable = false)
    private Date            insertDate;

    /**
     * カテゴリ情報
     */
    @ManyToOne
    @JoinColumn(name = "category_id", referencedColumnName = "id")
    private Category        category;

    /**
     * 注文商品情報
     */
    @OneToMany(mappedBy = "item")
    private List<OrderItem>    orderItemList;

    /**
     * コンストラクタ
     */
    public Item() {
    }

    /**
     * コンストラクタ
     * @param id 商品ID
     * @param name 商品名
     * @param description 商品説明
     * @param image 画像ファイル名
     * @param category_name カテゴリ名
     */
    public Item(Integer id, String name, String description, String image, String category_name) {
        this.id = id;
        this.name = name;
        this.description = description;
        this.image = image;
        this.category = new Category();
        this.category.setName(category_name);
    }

    /**
     * コンストラクタ
     * @param id 商品ID
     * @param name 商品名
     * @param price 価格
     * @param description 商品説明
     * @param image 画像ファイル名
     * @param category_name カテゴリ名
     */
    public Item(Integer id, String name, Integer price, String description, String image, String category_name) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.description = description;
        this.image = image;
        this.category = new Category();
        this.category.setName(category_name);
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getPrice() {
        return price;
    }

    public void setPrice(Integer price) {
        this.price = price;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStock() {
        return stock;
    }

    public void setStock(Integer stock) {
        this.stock = stock;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public Integer getDeleteFlag() {
        return deleteFlag;
    }

    public void setDeleteFlag(Integer deleteFlag) {
        this.deleteFlag = deleteFlag;
    }

    public Date getInsertDate() {
        return insertDate;
    }

    public void setInsertDate(Date insertDate) {
        this.insertDate = insertDate;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }

    public List<OrderItem> getOrderItemsList() {
        return orderItemList;
    }

    public void setOrderItemsList(List<OrderItem> orderItemList) {
        this.orderItemList = orderItemList;
    }

}
package jp.co.sss.shop.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQuery;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

/**
 * 注文商品情報のエンティティクラス
 *
 * @author SystemShared
 */
@Entity
@Table(name = "order_items")
//@NamedQuery(name="findByOrderQuantity", query="SELECT o FROM order_items WHERE o.quantity >= 1 ORDER BY o.quantity DESC")
public class OrderItem {

    /**
     * 注文商品ID
     */
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_order_items_gen")
    @SequenceGenerator(name = "seq_order_items_gen", sequenceName = "seq_order_items", allocationSize = 1)
    private Integer    id;

    /**
     * 注文個数
     */
    @Column
    private Integer    quantity;

    /**
     * 注文情報
     */
    @ManyToOne
    @JoinColumn(name = "order_id", referencedColumnName = "id")
    private Order    order;

    /**
     * 商品情報
     */
    @ManyToOne
    @JoinColumn(name = "item_id", referencedColumnName = "id")
    private Item    item;


    /**
     * 注文時点商品単価
     */
    @Column
    private int price;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getQuantity() {
        return quantity;
    }

    public void setQuantity(Integer quantity) {
        this.quantity = quantity;
    }

    public Order getOrder() {
        return order;
    }

    public void setOrder(Order order) {
        this.order = order;
    }

    public Item getItem() {
        return item;
    }

    public void setItem(Item item) {
        this.item = item;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }


}
package jp.co.sss.shop.entity;

import java.sql.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

/**
 * カテゴリ情報のエンティティクラス
 *
 * @author SystemShared
 */
@Entity
@Table(name = "categories")
public class Category {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_categories_gen")
    @SequenceGenerator(name = "seq_categories_gen", sequenceName = "seq_categories", allocationSize = 1)
    /**
     * カテゴリID
     */
    private Integer        id;

    /**
     * カテゴリ名
     */
    @Column
    private String        name;

    /**
     * カテゴリ説明
     */
    @Column
    private String        description;

    /**
     * 削除フラグ 0:未削除、1:削除済み
     */
    @Column(insertable = false)
    private Integer        deleteFlag;

    /**
     * 登録日付
     */
    @Column(insertable = false)
    private Date        insertDate;

    /**
     * 商品リスト
     */
    @OneToMany(mappedBy = "category")
    private List<Item>    itemList;

    /**
     * コンストラクタ
     */
    public Category() {
    }

    /**
     * コンストラクタ
     * @param name カテゴリ名
     */
    public Category(String name) {
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getDeleteFlag() {
        return deleteFlag;
    }

    public void setDeleteFlag(Integer deleteFlag) {
        this.deleteFlag = deleteFlag;
    }

    public Date getInsertDate() {
        return insertDate;
    }

    public void setInsertDate(Date insertDate) {
        this.insertDate = insertDate;
    }

    public List<Item> getItemList() {
        return itemList;
    }

    public void setItemList(List<Item> itemList) {
        this.itemList = itemList;
    }
}
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • m.ts10806

    2019/12/16 09:30

    SQLはDBに対して直接実行して想定通りの結果を得られたものを利用しているわけではないのでしょうか。

    キャンセル

  • k7023

    2019/12/16 09:41 編集

    jpqlでは文法が違い、結合キーを指定する句がjpqlではなく困っています。

    (注文商品テーブル、商品)の結合テーブルから注文件数の多い順に表示

    SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id)
    FROM items i INNER JOIN order_items oi ON i.id=oi.item_id
    INNER JOIN categories c ON c.id = i.category_id
    GROUP BY i.id,i.name,i.image, i.price, c.name
    ORDER BY count(i.id) DESC;

    ちなみに元のSQLは上記です。

    キャンセル

  • m.ts10806

    2019/12/16 09:47

    タグにOracleとあったものでてっきりそれかと。

    キャンセル

  • k7023

    2019/12/16 09:54

    oraleからのデータ取得になりますので

    キャンセル

回答 2

0

JPA JOIN ON と ON を省略しないで記述したらどうなりますか?

SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id)
                                                 ↓
SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id) AS COUNTS


などときちんと関数を使った列の別名を設定しておいた方がいい場合もあります。

質問にOracle, JPA?, JPQL? のバージョンは明記した方が良いです。タグを追加するとコメントが付き易くなる場合もあります。必要ならteratail側に タグの追加 を依頼してください。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

エラーの原因

エラーメッセージに記載されているクエリを見ると、半角スペースが足りない個所があることがわかります。

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'i.category' [SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id)FROM jp.co.sss.shop.entity.OrderItem oi INNER JOIN  oi.item iINNER JOIN i.category c GROUP BY i.id,i.name,i.image, i.price, c.name ORDER BY count(i.id) DESC ]

SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id)FROM jp.co.sss.shop.entity.OrderItem oi INNER JOIN  oi.item iINNER JOIN i.category c GROUP BY i.id,i.name,i.image, i.price, c.name ORDER BY count(i.id) DESC

ですが、半角スペースを追加しても別のエラーが発生します。
このクエリでは、Itemクラスにマッピングできないプロパティ(name や count)があるので、このままではクエリを実行することはできません。

//一覧表示(売れ筋順)
@Query("SELECT i.id,i.name,i.image,i.price, c.name, COUNT(i.id)" + 
        "FROM OrderItem oi INNER JOIN  oi.item i" + 
        "INNER JOIN i.category c " + 
        "GROUP BY i.id,i.name,i.image, i.price, c.name " + 
        "ORDER BY count(i.id) DESC ")
public List<Item> sortByOrderCount();

解決方法

なので、クエリの結果を受け取るためのPOJOの実装とクエリの発行方法を修正する必要があります。

POJOの実装

下記のItemDtoクラスを実装したという前提で説明を続けます。
※アクセサメソッドは省略しています。

package com.example.demo.dto;

public class ItemDto {
    private Integer id;
    private String name;
    private String image;
    private Integer price;
    private String categoryName;
    private Integer total;
}

クエリの発行

次にクエリの発行方法ですが下記3点が考えられます。個人的に最適と考えるのは3番目のJdbcTemplateを使う方法ですが、チームで開発している場合はどの方法を取るかチーム内のコンセンサスが必要だと思います。
※下記のコードはあくまでも実装例であり重要でないコードは省略していることにご留意ください。

1) repository + JPQL を使う

この実装であればNative Queryを書かなくて済みますが、リソースの消費や性能面で問題がありますのでお勧めしません。

この方法のポイントはJPQL内で " SELECT new com.example.demo.dto.ItemDto( ... ) " としている点です。この記述でクエリの結果をItemDtoクラスへマッピングしています。
なお、エンティティクラス上でエンティティ間のリレーションが定義されているので、JPQLでJOINする必要はありません。

public interface ItemRepository extends JpaRepository<Item, Integer> {

    /* JPQL */
    public static final String ORDER_SUMMARY = "SELECT new com.example.demo.dto.ItemDto(i.id, i.name, i.image, i.price, i.category.name, size(i.orderItemList)) " +
                                                      "FROM Item AS i " +
                                                     "GROUP BY i.id, i.name, i.image, i.price, i.category.name " +
                                                     "ORDER BY size(i.orderItemList) DESC";

    @Query(value = ORDER_SUMMARY)
    public List<ItemDto> findAllOrderSummary();

}
2) repository + Native Query を使う

この方法は、コードの記述量が増えるのとJPAを採用するメリットを損なう点がデメリットですが、JPQLに比べると性能面での問題は少なく、問題が起きてもチューニングしやすいというメリットがあります。

下記のItemクラスで、クエリの結果をItemDtoへマッピングする定義を行い、Native Queryに"nativeItemOrderSummary"という名前付けとマッピング方法を紐づけています。

@Entity
@Table(name = "items")
@SqlResultSetMappings({
    @SqlResultSetMapping(
        name = "itemDtoMapping",
        classes = {
            @ConstructorResult(
                targetClass = com.example.demo.dto.ItemDto.class,
                columns = {
                     @ColumnResult(name = "id", type = Integer.class),
                     @ColumnResult(name = "name"),
                     @ColumnResult(name = "image"),
                     @ColumnResult(name = "price", type = Integer.class),
                     @ColumnResult(name = "categoryName"),
                     @ColumnResult(name = "total", type = Integer.class)
                }
            )
        }
    )
})
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "nativeItemOrderSummary",
        query = Item.ITEM_ORDER_SUMMARY,
        resultSetMapping = "itemDtoMapping"
    )
})
public class Item {

    /* Native Query */
    public static final String ITEM_ORDER_SUMMARY =
            "SELECT i.id AS id, i.name AS name, i.image AS image, i.price AS price, c.name AS categoryName, COUNT(i.id) AS total " +
              "FROM items i INNER JOIN order_items oi ON i.id = oi.item_id " +
                            "INNER JOIN categories c ON c.id = i.category_id " +
             "GROUP BY i.id, i.name, i.image, i.price, c.name " +
             "ORDER BY COUNT(i.id) DESC";

}

ItemRepositoryの実装では、上記で定義したNative Queryを使用する設定を行います。ItemRepositoryの使い方は1番目のJPQLと同じです。

public interface ItemRepository extends JpaRepository<Item, Integer> {

    @Query(name = "nativeItemOrderSummary", nativeQuery = true)
    public List<ItemDto> findAllOrderSummary();

}

※この方法のバリエーションとしてEntityManagerを使う方法もありますので、念のため補足しておきます。

@Autowired
private EntityManager entityManager;

public List<ItemDto> getSummary() {
    TypedQuery<ItemDto> query = entityManager.createNamedQuery("nativeItemOrderSummary", ItemDto.class);
    return query.getResultList();
}

3) JdbcTemplate + Native Query を使う

上記2つの方法に比べると、JdbcTemplateを使う方法がもっとも簡単です。JPAが使えるのであればJdbcTemplateも使えると思います。
下記のようにクエリの結果をItemDtoへマッピングするRowMapperを実装したマッピングクラスが必要ですが、実装内容はシンプルです。

public class ItemDtoMapper implements RowMapper<ItemDto> {

    public static final String ITEM_ORDER_SUMMARY =
            "SELECT i.id AS id, i.name AS name, i.image AS image, i.price AS price, c.name AS categoryName, COUNT(i.id) AS total " +
              "FROM items i INNER JOIN order_items oi ON i.id = oi.item_id " +
                            "INNER JOIN categories c ON c.id = i.category_id " +
             "GROUP BY i.id, i.name, i.image, i.price, c.name " +
             "ORDER BY COUNT(i.id) DESC";

    @Override
    public ItemDto mapRow(ResultSet rs, int rowNum) throws SQLException {
        ItemDto dto = new ItemDto();
        dto.setId(rs.getInt("id"));
        dto.setName(rs.getString("name"));
        dto.setImage(rs.getString("image"));
        dto.setPrice(rs.getInt("price"));
        dto.setCategoryName(rs.getString("categoryName"));
        dto.setTotal(rs.getInt("total"));
        return dto;
    }
}

この例ではコントローラクラスでJdbcTemplateを使っていますが、インジェクションできるクラスであればサービスクラスでもコンポーネントでも構いません。

@Controller
public class ItemController {

    private final JdbcTemplate jdbcTemplate;

    public ItemController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @GetMapping(value = "all")
    public String all(Model model) {
        List<ItemDto> list = jdbcTemplate.query(ItemDtoMapper.ITEM_ORDER_SUMMARY, new ItemDtoMapper());
        model.addAttribute("list", list);
        return "item";
    }

}

上記のコードの動作確認は

  • OpenJDK 11.0
  • Spring Boot 2.2.1
  • MySQL 8.0.17

で行いました。

補足

4番目を書き忘れたので補足します。

4) ビューを使う

目的のクエリでビューを作成し、JPA側でそれをエンティティクラスとして定義するという方法もあります。

SELECT i.id, i.name, i.image,i.price, c.name, COUNT(i.id)
  FROM items i INNER JOIN order_items oi ON i.id = oi.item_id
               INNER JOIN categories c ON c.id = i.category_id
 GROUP BY i.id, i.name, i.image, i.price, c.name
 ORDER BY count(i.id) DESC;

この場合、クエリの複雑さをビューに寄せることができるのでJava側の実装は簡単になります。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

  • ただいまの回答率 87.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る