LoginSignup
6
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@rubytomato@github(渡邉 真)

JPQLでJSON型カラムを持つテーブルを検索する

Posted at

概要

MySQL 5.7よりサポートされたJSON型カラムを持つテーブルをJPQLを使って検索するサンプルコードです。
この記事で説明している検索方法はデータベース固有の関数(MySQLのJSON_EXTRACT)を使っています。理想としてはJPA標準機能だけで実現できればいいのですが、いまのところ方法が見つけられなかったので、データベース依存の実装になっています。

環境

  • Windows10 Professional
  • Java 1.8.0_144
  • Spring Boot 1.5.6
    • Spring Data JPA
  • MySQL 5.7.19

参考

サンプルコード

テーブル

サンプルコードで使用するテーブルです。notesというカラムをJSON型にしています。notesに格納するJSONオブジェクトはスキーマレスではなくPOJOとの変換を考えて、下記のcolor, shape, statusという最大3つのフィールドを持ちます。

{"color": "***", "shape": "***", "status": "***"}

CREATE TABLE IF NOT EXISTS stock (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  stocks INT NOT NULL DEFAULT 0,
  notes JSON,
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  del_flag TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
)
DEFAULT CHARSET = UTF8MB4;

テストデータ

サンプルコードで使用するテストデータです。

INSERT INTO stock (name, stocks, notes) VALUES ('うまいぼう', 210, '{"color": "red", "status": "done"}');
INSERT INTO stock (name, stocks, notes) VALUES ('ぽてとふらい', 300, '{"color": "green", "shape": "rectangle"}');
INSERT INTO stock (name, stocks, notes) VALUES ('きなこぼう',  60, '{"color": "blue", "shape": "triangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('なまいきびーる', 250, '{"color": "red", "status": "in progress"}');
INSERT INTO stock (name, stocks, notes) VALUES ('ふるーつよーぐる', 930, '{"status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('みにこーら',  10, '{"shape": "pentagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('こざくらもち', 330, '{"color": "green", "shape": "rectangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('たまごあいす',  20, '{"color": "red", "shape": "hexagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('もちたろう', 100, '{"color": "blue"}');

mysqlクライアントから検索

ネイティブSQLでのJSON型カラムで検索する方法を確認します。

検索

SELECT s.id, s.name, s.notes
  FROM stock AS s
 WHERE JSON_EXTRACT(s.notes, '$.color') = 'red';
+----+-----------------------+-----------------------------------------------------------+
| id | name                  | notes                                                     |
+----+-----------------------+-----------------------------------------------------------+
|  1 | うまいぼう            | {"color": "red", "status": "done"}                        |
|  4 | なまいきびーる        | {"color": "red", "status": "in progress"}                 |
|  8 | たまごあいす          | {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+-----------------------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

SELECT s.id, s.name, s.notes
  FROM stock AS s
 WHERE JSON_EXTRACT(s.notes, '$.color', '$.shape') = JSON_ARRAY('red', 'hexagon');
+----+--------------------+-----------------------------------------------------------+
| id | name               | notes                                                     |
+----+--------------------+-----------------------------------------------------------+
|  8 | たまごあいす       | {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+--------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)

アプリケーション

エンティティクラス

JSON型のnotesカラムに対応するフィールドにPOJOのNotesクラスを指定します。
ただし、このままではエラーになるのでコンバータークラスを指定して値の変換をさせるようにします。

import com.example.domain.converter.JsonNotesConverter;
import com.example.domain.dto.Notes;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDateTime;

@Entity
@Table(name="stock")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Stock implements Serializable {

    private static final long serialVersionUID = 3766264071895115867L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name="name", nullable = false)
    private String name;
    @Column(name="stocks", nullable = false)
    private Integer stocks;
    @Convert(converter=JsonNotesConverter.class)
    @Column(name="notes")
    private Notes notes;
    @Column(name="create_at", nullable = false)
    private LocalDateTime createAt;
    @Column(name="update_at", nullable = false)
    private LocalDateTime updateAt;
    @Column(name="del_flag", nullable = false)
    private Boolean delFlag;

    @PrePersist
    private void prePersist() {
        createAt = LocalDateTime.now();
        updateAt = LocalDateTime.now();
    }

    @PreUpdate
    private void preUpdate() {
        updateAt = LocalDateTime.now();
    }

}

JSON型にマッピングさせるPOJO

JSON型のnotesカラムにマッピングさせるクラスです。lombokのアノテーションを付けているだけで特に実装はありません。

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Notes {
    private String color;
    private String shape;
    private String status;
}

コンバーター

テーブルに格納するJSONオブジェクトとJavaのPOJOとの変換処理に、JPAのAttributeConverterを実装したコンバータークラスを実装します。この内容は参考にあげたページの内容を参考にしました。

import com.example.domain.dto.Notes;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;

import javax.persistence.AttributeConverter;
import java.io.IOException;

@Slf4j
public class JsonNotesConverter implements AttributeConverter<Notes, String> {

    private final ObjectMapper objectMapper =
            new ObjectMapper().setSerializationInclusion(JsonInclude.Include.NON_NULL);

    @Override
    public String convertToDatabaseColumn(Notes notes) {
        try {
            return objectMapper.writeValueAsString(notes);
        } catch (JsonProcessingException e) {
            log.warn("can't convert to json.", e);
        }
        return null;
    }

    @Override
    public Notes convertToEntityAttribute(String str) {
        try {
            return objectMapper.readValue(str, Notes.class);
        } catch (IOException e) {
            log.warn("can't convert to entity.", e);
        }
        return null;
    }
}

リポジトリクラス

カスタムメソッドを定義しJPQLを記述します。この記事の冒頭にも書きましたが、データベース固有の関数（この例ではMySQLのJSON_EXTRACT）を使用しています。
このため、リポジトリの単体テストでH2などを使用することができません。

import com.example.domain.entity.Stock;
import org.springframework.data.jpa.repository.JpaRepository;

public interface StockRepository extends JpaRepository<Stock, Long>  {
    @Query("SELECT s FROM Stock AS s WHERE FUNCTION('JSON_EXTRACT', s.notes, :key) = :val")
    List<Stock> findByNotes(@Param("key") String key, @Param("val") String val);
}

EntityManagerを使う方法

EntityManagerを使うパターンです。JPQLの記述に変更はありません。

public List<Stock> findByNotes() {
    String sql = "SELECT s " +
                 " FROM Stock AS s " +
                 " WHERE FUNCTION('JSON_EXTRACT', s.notes, :key) = :val";

    TypedQuery<Stock> query = entityManager.createQuery(sql, Stock.class)
        .setParameter("key", "$.color")
        .setParameter("val", "red");

    List<Stock> lists = query.getResultList();
    return lists;
}

ちなみに

DB固有の機能を使わずに下記のようなJPQLが記述できればいいのですが、いまのことろはランタイムエラーになります。

NG
String sql = "SELECT s FROM Stock AS s WHERE s.notes.color = :color";

TypedQuery<Stock> query = entityManager.createQuery(sql, Stock.class)
    .setParameter("color", "red");

検索処理の例

@Autowired
private StockRepository repository;

public void findOne() {
    Stock stock = repository.findOne(1L);
    System.out.println(stock);
    // → Stock(id=1, name=うまいぼう, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
}

public void findAll() {
    List<Stock> lists = repository.findAll();
    lists.forEach(System.out::println);
    // → Stock(id=1, name=うまいぼう, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
    // → Stock(id=2, name=ぽてとふらい, stocks=300, notes=Notes(color=green, shape=rectangle, status=null), createAt=2017-09-15T08:20:20, updateAt=2017-09-15T08:20:20, delFlag=false)
    // → Stock(id=3, name=きなこぼう, stocks=60, notes=Notes(color=blue, shape=triangle, status=not started), createAt=2017-09-15T08:20:23, updateAt=2017-09-15T08:20:23, delFlag=false)
    // → Stock(id=4, name=なまいきびーる, stocks=250, notes=Notes(color=red, shape=null, status=in progress), createAt=2017-09-15T08:20:27, updateAt=2017-09-15T08:20:27, delFlag=false)
    // → Stock(id=5, name=ふるーつよーぐる, stocks=930, notes=Notes(color=null, shape=null, status=waiting), createAt=2017-09-15T08:20:31, updateAt=2017-09-15T08:20:31, delFlag=false)
    // → Stock(id=6, name=みにこーら, stocks=10, notes=Notes(color=null, shape=pentagon, status=waiting), createAt=2017-09-15T08:20:33, updateAt=2017-09-15T08:20:33, delFlag=false)
    // → Stock(id=7, name=こざくらもち, stocks=330, notes=Notes(color=green, shape=rectangle, status=not started), createAt=2017-09-15T08:20:36, updateAt=2017-09-15T08:20:36, delFlag=false)
    // → Stock(id=8, name=たまごあいす, stocks=20, notes=Notes(color=red, shape=hexagon, status=waiting), createAt=2017-09-15T08:20:40, updateAt=2017-09-15T08:20:40, delFlag=false)
    // → Stock(id=9, name=もちたろう, stocks=100, notes=Notes(color=blue, shape=null, status=null), createAt=2017-09-15T08:20:43, updateAt=2017-09-15T08:20:43, delFlag=false)
}

public void findByNotes() {
    List<Stock> lists = repository.findByNotes("$.color", "red");
    lists.forEach(System.out::println);
    // → Stock(id=1, name=うまいぼう, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
    // → Stock(id=4, name=なまいきびーる, stocks=250, notes=Notes(color=red, shape=null, status=in progress), createAt=2017-09-15T08:20:27, updateAt=2017-09-15T08:20:27, delFlag=false)
    // → Stock(id=8, name=たまごあいす, stocks=20, notes=Notes(color=red, shape=hexagon, status=waiting), createAt=2017-09-15T08:20:40, updateAt=2017-09-15T08:20:40, delFlag=false)
}

更新処理の例

public void save() {
    Notes notes = Notes.builder().color("cyan").status("done").build();
    Stock stock = Stock.builder().name("あんずぼー").stocks(1).notes(notes).delFlag(false).build();
    repository.saveAndFlush(stock);
}

mysqlクライアントで確認

> select * from stock where name = 'あんずぼー'\G
*************************** 1. row ***************************
       id: 14
     name: あんずぼー
   stocks: 1
    notes: {"color": "cyan", "status": "done"}
create_at: 2017-09-15 08:31:48
update_at: 2017-09-15 08:31:48
 del_flag: 0
1 row in set (0.00 sec)

注意事項

  • 冒頭でも書きましたが、データベース依存の実装になっています。（この例ではMySQLのJSON_EXTRACT関数の使用。
  • MySQLではJSON型のカラムにインデックスが貼れないので大量データに対する検索ではパフォーマンスに影響がでるかもしれません。
6
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?