spring-data-jpa 如何使用spring data jpa查询jsonb列?

yvfmudvl  于 2022-11-10  发布在  Spring
关注(0)|答案(7)|浏览(443)

我在postgres 9.4示例上获取这个原生查询时遇到问题。
我的存储库有一个方法:

@Query(value = "SELECT t.* " +
            "FROM my_table t " +
            "WHERE t.field_1 = ?1 " +
            "AND t.field_2 = 1 " +
            "AND t.field_3 IN ?2 " +
            "AND t.jsonb_field #>> '{key,subkey}' = ?3",
            nativeQuery = true)
    List<Entity> getEntities(String field1Value,
                                   Collection<Integer> field3Values,
                                   String jsonbFieldValue);

但日志显示:

SELECT t.* FROM my_table t 
WHERE t.field_1 = ?1 
  AND t.field_2 = 1 
  AND t.field_3 IN ?2 
  AND t.jsonb_field ? '{key,subkey}' = ?3

我得到了这个异常:
内部异常错误:org.postgresql.util.PSQLException:未指定参数2的值。
我在方法调用之前直接记录了参数,并且它们都被提供了。
我不知道为什么#>>在日志中显示?。我需要转义#>>吗?我需要格式化IN的集合吗?我需要转义json路径吗?
当我直接对数据库执行查询时,它就可以工作了。例如:

SELECT *
FROM my_table t
WHERE t.field_1 = 'xxxx'
  AND t.field_2 = 1
  AND t.field_3 IN (13)
  AND t.jsonb_field #>> '{key,subkey}' = 'value'
cfh9epnr

cfh9epnr1#

我发现Spring Data 中的Specification api非常有用。
假设我们有一个名为Product的实体和一个名为title的JSON(B)类型的属性。
我假设此属性包含不同语言的产品标题。例如:{"EN":"Multicolor LED light", "EL":"Πολύχρωμο LED φώς"} .
下面的源代码通过作为参数传递的标题和区域设置查找一个(如果不是唯一字段,则查找多个)产品。

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
}

public class ProductSpecification implements Specification<Product> {

    private String locale;
    private String titleToSearch;

    public ProductSpecification(String locale, String titleToSearch) {
        this.locale = locale;
        this.titleToSearch = titleToSearch;
    }

    @Override
    public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(this.locale)), this.titleToSearch);
    }
}

@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public List<Product> findByTitle(String locale, String titleToSearch) {
        ProductSpecification cs = new ProductSpecification(locale, titleToSearch);
        return productRepository.find(cs);
        // Or using lambda expression - without the need of ProductSpecification class.
//      return productRepository.find((Root<ProductCategory> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
//          return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(locale)), titleToSearch);
//      });
    }
}

您可以在这里找到关于如何使用Spring Data 的另一个答案。
希望能有所帮助。

63lcw9qa

63lcw9qa2#

如果由于某种原因,运算符被转换成问号,那么您应该尝试使用函数来代替。您可以在psql控制台中使用\doS+ #>>找到相应的函数。它告诉我们调用的函数是jsonb_extract_path_text。这将使您的查询:

@Query(value = "SELECT t.* " +
        "FROM my_table t " +
        "WHERE t.field_1 = ?1 " +
        "AND t.field_2 = 1 " +
        "AND t.field_3 IN ?2 " +
        "AND jsonb_extract_path_text(t.jsonb_field, '{key,subkey}') = ?3",
        nativeQuery = true)
pdkcd3nj

pdkcd3nj3#

也许这是一个老主题,但我在这里把jsonb中的搜索按字段使用spring规范。
如果要使用“LIKE”进行搜索,则需要使用以下代码创建like析取:

final Predicate likeSearch = cb.disjunction();

然后,假设你的对象中有一个jsonb字段是address,address有5个字段,要在所有这些字段中搜索,你需要为所有字段添加“LIKE”表达式:

for (String field : ADDRESS_SEARCH_FIELDS) {
                likeSearch.getExpressions().add(cb.like(cb.lower(cb.function("json_extract_path_text", String.class,
                        root.get("address"), cb.literal(field))), %searchKey%));
            }

其中cb是相同的criteriaBuilder。%searchKey%是您要在地址字段中搜索的内容。
希望这对你有帮助。

wyyhbhjk

wyyhbhjk4#

您还可以使用FUNC JPQL关键字来调用自定义函数,而不使用本地查询。
像这样的东西,

@Query(value = "SELECT t FROM my_table t "
        + "WHERE t.field_1=:field_1 AND t.field_2=1 AND t.field_3 IN :field_3 "
        + "AND FUNC('jsonb_extract_path_text', 'key', 'subkey')=:value")
List<Entity> getEntities(@Param("field_1") String field_1, @Param("field_3") Collection<Integer> field_3, @Param("value") String value);
tjvv9vkg

tjvv9vkg5#

我建议不要采用这种方式,我更喜欢采用通用的CRUD方式(也在为Spring Data Rest maven插件以StrongLoop Loopback的方式处理高级自动生成的DAO方法,但它目前只是实验性的)。但是对于这个JSON,现在该怎么做...我正在通过@Document注解寻找类似于Spring Data中MongoDB JSON处理的东西,然而,这还不是可用的。但是还有其他的方法:-)
一般来说,它是关于实现JSON用户类型(UserType接口):

public class YourJSONBType implements UserType {

最后,您需要使用实现的用户类型的规范来增强JPA类:

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@TypeDef(name = "JsonbType", typeClass = YourJSONBType.class)
public class Person {
    @Id
    @GeneratedValue
    private Long id;

    @Column(columnDefinition = "jsonb")
    @Type(type = "JsonbType")
    private Map<String,Object> info;
}

看另一个相关的文章在这里:Mapping PostgreSQL JSON column to Hibernate value type
完整的实施示例可从以下网址获得:

类似但略有不同的示例如下:http://www.wisely.top/2017/06/27/spring-data-jpa-postgresql-jsonb/?d=1

dhxwm5r4

dhxwm5r46#

分享我自己的例子,因为我努力分解提供的答案,以满足我的特定需求。希望这能帮助其他人。我的例子是在groovy中,我正在与postgres SQL数据库集成。这是一个简单的例子,说明如何在名为“name”的字段上搜索JSON列,并使用分页。
JSON支持类

@TypeDefs([@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)])
@MappedSuperclass
class JSONSupport {}

实体类:

@Entity
@Table(name = "my_table")
class MyEntity extends JSONSupport {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long pk

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    String jsonData
}

规范类

class NameSpecification implements Specification<MyEntity> {

    private final String name

    PhoneNumberSpecification(String name) {
        this.name = name
    }

    @Override
    Predicate toPredicate(Root<ContactEntity> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.equals(
                   builder.function(
                       "jsonb_extract_path_text",
                       String.class,
                       root.<String>get("jsonData"),
                       builder.literal("name")
                   ),
                   this.name
              )
    }
}

存储库

interface MyEntityRepository extends PagingAndSortingRepository<MyEntity, Long>, JpaSpecificationExecutor<MyEntity> {}

用法

@Service
class MyEntityService {

    private final MyEntityRepository repo

    MyEntityService(MyEntityRepository repo) {
         this.repo = repo
    }    

    Page<MyEntity> getEntitiesByNameAndPage(String name, Integer page, Integer pageSize) {
        PageRequest pageRequest = PageRequest.of(page, pageSize, Sort.by("pk"))

        NameSpecification spec = new NameSpecification(name)
        return repo.findAll(spec, pageRequest)
    }
}
zkure5ic

zkure5ic7#

在 postgres DB 中 创建 表

CREATE TABLE shared.my_data (
id serial PRIMARY KEY,
my_config jsonb
);

中 的 每 一 个
将 数据 插入 表格

INSERT into shared.my_data (id, my_config) VALUES( 1,
'{"useTime": true,
"manualUnassign": false,
"require":true,
"blockTime":10,
"additionalHours":1,
"availablegroups":[10,20,30]
}')

格式
检查 表 中 的 数据 :

select * from shared.tenant_data

格式
Spring 启动 Java 项目 Java 版本 :11 Spring 版 :2.7.1
Maven 依赖 于 POM.xml 文件 。 对于 postgres JOSNB , 我们 需要 特定 的
vladmihalcea 依赖 项 2.14.0 版

<dependency>
        <groupId>com.vladmihalcea</groupId>
        <artifactId>hibernate-types-52</artifactId>
        <version>2.14.0</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        <version>2.7.1</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
        <version>2.7.1</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>2.7.1</version>
    </dependency>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>

格式

    • JSON 对象 类 * *
import com.fasterxml.jackson.annotation.JsonProperty;

import java.util.List;

public class MyConfig {
@JsonProperty("useTime")
private boolean useTime;
@JsonProperty("manualUnassign")
private boolean manualUnassign;
@JsonProperty("require")
private boolean require;
@JsonProperty("additionalHours")
private int additionalHours;
@JsonProperty("blockTime")
private int blockTime;
@JsonProperty("availableGroup")
private List<Integer> availableGroup;
}

格式

    • [ 实体 ] 用于 封装 表 行 中 列 的 根 对象 * *
import com.vladmihalcea.hibernate.type.json.JsonBinaryType;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import javax.persistence.*;

@Data
@Entity
@Table(name = "my_data", schema = "shared")
@Builder
@NoArgsConstructor
@AllArgsConstructor
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class MyData {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
private Long id;

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private MyConfig myConfig;
}

格式

    • 存储 库 层 * *
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MyDataRepo extends JpaRepository<MyData, Long> {
}

格式

    • 服务 层 * *
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class MyDataService {

@Autowired
private MyDataRepo myDataRepo;

public List<MyData> getAllMyspecificData(){
    List<MyData> allMyData = myDataRepo.findAll();
    return allMyData;
}
 }

格式

    • REST 结束 点 * *
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping(path = "/my")
public class MyResouce {
@Autowired
MyDataService myDataService;

@GetMapping("/data")
public ResponseEntity<Object> getAllMyData() {
     List<MyData> myDataList = 
     myDataService.getAllMyspecificData();

    return new ResponseEntity<>(myDataList, HttpStatus.OK);
}
}

格式

相关问题