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

Question

@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;
	}
}

Comments

[m.ts10806]

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

[k7023]

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]

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

[k7023]

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

Answer 1

エラーの原因

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

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側の実装は簡単になります。

Answer 2

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側に タグの追加 を依頼してください。