rubytomato score 1720
2019/12/17 08:17 投稿
| ### エラーの原因 |
| エラーメッセージに記載されているクエリを見ると、半角スペースが足りない個所があることがわかります。 |
| > 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)があるので、このままではクエリを実行することはできません。 |
| ```Java |
| //一覧表示（売れ筋順） |
| @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クラスを実装したという前提で説明を続けます。 |
| ※アクセサメソッドは省略しています。 |
| ```Java |
| 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する必要はありません。 |
| ```Java |
| 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"という名前付けとマッピング方法を紐づけています。 |
| ```Java |
| @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と同じです。 |
| ```Java |
| public interface ItemRepository extends JpaRepository<Item, Integer> { |
| @Query(name = "nativeItemOrderSummary", nativeQuery = true) |
| public List<ItemDto> findAllOrderSummary(); |
| } |
| ``` |
| ※この方法のバリエーションとしてEntityManagerを使う方法もありますので、念のため補足しておきます。 |
| ```Java |
| @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を実装したマッピングクラスが必要ですが、実装内容はシンプルです。 |
| ```Java |
| 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を使っていますが、インジェクションできるクラスであればサービスクラスでもコンポーネントでも構いません。 |
| ```Java |
| @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側でそれをエンティティクラスとして定義するという方法もあります。 |
| ```sql |
| 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側の実装は簡単になります。 |
