Extend JPA's support for array fields (postgresql database)

Extend JPA's support for postgresql database array fields

The code of this content has not been completely posted, the code cloud address: https://gitee.com/daifylearn/postgresql

Although postgresql supports array fields, Hibernate does not. Special treatment is required here

Direct use of third-party dependencies

Directly introduce the open source library written by Hibernate boss Vlad Mihalcea, easy and happy

Introduce dependencies

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

Do it yourself

It is suitable for situations where there are special business requirements or it is not convenient to introduce other dependencies. The main code still relies on Vlad Mihalcea's open source library. The big guy has completed the code implementation, we just need to copy it.
Of course, we don’t need to copy everything on the side, just copy the parts we need to mainly involve three parts of the code:

Code that needs to be implemented by yourself

The code of this piece is mainly in the com.hi.config.arraypackage

The role of the main interface or class

JavaTypeDescriptor: JAVA description of data, this interface provides the description content of java direction value mapping. You can directly rely on abstract classes when extending AbstractTypeDescriptor.
SqlTypeDescriptor: JDBC provides some functions such as value binding and folding for data processing.
AbstractSingleColumnStandardBasicType: Basic data types defined both sides of the bridge, which is a set of interfaces includes a number of interfaces SingleColumnType, BasicType,
StringRepresentableType, ProcedureParameterExtractionAware, ProcedureParameterNamedBinder. It mainly provides operations such as value binding to basic data types.
Of course, most of the content AbstractSingleColumnStandardBasicTypehas already been partially implemented in this, saving a lot of actions that you have to implement yourself.

Use demo

Here is hibernate-types-52the operation method used, of course, the same content is also used when implementing it yourself

Add dependency

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

New data model

java data model

@Data
@Entity
@TypeDefs({
        @TypeDef(name = "string-array",typeClass = StringArrayType.class)
})
public class ArrayBean {

    @Id
    @Column(name = "id")
    private String id;

    @Column(name = "name")
    private String name;

    @Type(type = "string-array")
    @Column(columnDefinition = "text[]")
    private String[] authorName;
}

On the entity object class, you need to declare the name of the special type used and the corresponding class @TypeDef, use the @Typedeclared type name on the corresponding attribute , and specify the element type in the arraycolumnDefinition

Generated table statement

CREATE TABLE public.array_bean (
	id varchar(255) NOT NULL,
	author_name text[] NULL,
	"name" varchar(255) NULL,
	CONSTRAINT array_bean_pkey PRIMARY KEY (id)
)
WITH (
	OIDS=FALSE
) ;

data query

JPA format

This is to use JPA naming rules to implement related queries. However, it should be noted that JPA can not well complete the screening and judgment of array fields.

public interface ArrayBeanRepository extends JpaRepository<ArrayBean,String> {

    List<ArrayBean> findByAuthorName(String[] authorName);
}

The conversion of the above method into SQL is equivalent to the following content, and only simple equality and inequality judgments can be made.

SELECT * FROM public.array_bean x
where array['a','b'] = x.author_name

The need for strict matching here not only strictly matches the element values ​​of the array object, but also needs to be consistent in the order. But in many cases, for the data in the array, postgresql has a richer syntax for filtering array parameters,
so if we want to have a more flexible way to match. Here you need to use native SQL to achieve the relevant content.

Use @Query to use native SQL in methods

    /**
     * @param authorName
     * @return
     */
    @Query(value = "SELECT * FROM public.array_bean x where CAST(string_to_array(?1,',') as text[]) && x.author_name",nativeQuery = true)
    List<ArrayBean> findByAuthorNameIn2(String authorName);

Use EntityManager for native SQL queries

@Service
public class ArrayBeanServiceImpl {

    @Autowired
    private EntityManager em;

    public List findArrayBeanByAuthorNameIn(List<String> authorName) {
        StringBuffer sql = new StringBuffer("SELECT * FROM public.array_bean x");
        String ids = authorName.stream().map(item -> "'" + item + "'").collect(Collectors.joining(","));
        sql.append(" where CAST(array[").append(ids).append("] as text[]) && x.author_name");
        Query query = em.createNativeQuery(sql.toString(),ArrayBean.class);
        List<ArrayBean> resultList = query.getResultList();
        ArrayBean arrayBean = resultList.get(0);
        System.out.println(arrayBean.getAuthorName());
        return resultList;
    }
}

Similar queries can be achieved in the above two ways

SELECT * FROM public.array_bean x where CAST(string_to_array('a,b,c',',') as text[]) && x.author_name

However, when using the @Query method to perform value substitution in some SQL, you need to convert the array type to a string type for processing.

Postgresql array type field support

Judgment between arrays

symboleffectexamplereturn valueDetailed introduction
=equalARRAY[1,2,3]::int[] = ARRAY[1,2,3]trueEqual to judgment requires that the two arrays are strictly the same in content and order
<>not equal toARRAY[1,2,3] <> ARRAY[1,2,4]trueAnd = judge the opposite
<Less thanARRAY[1,2,3] < ARRAY[1,2,4]trueDetermine the size of each element in turn according to the array elements
>more than theARRAY[1,4,3] > ARRAY[1,2,4]trueDetermine the size of each element in turn according to the array elements
<=less than or equal toARRAY[1,2,3] <= ARRAY[1,2,3]trueDetermine the size of each element in turn according to the array elements
>=greater than or equal toARRAY[1,4,3] >= ARRAY[1,4,3]trueDetermine the size of each element in turn according to the array elements
@>containARRAY[1,4,3] @> ARRAY[3,1]trueAll the elements on the right should appear on the left, and the number of repeated elements on the right is not required to be the same, such as: ARRAY[1,4,3] @> ARRAY[3,1,1] will also return true
<@Be contained inARRAY[2,7] <@ ARRAY[1,7,4,2,6]trueContrary to @>, all elements on the left are required to appear on the right
&&Overlap (have common elements)ARRAY[1,4,3] && ARRAY[2,1]trueThe same elements exist on the left and right sides

Array operations

Add elements to the array to use ||. There will be three results according to the different data types of both parties

Array value merge

When two arrays are used ||, their contents will be merged:

ARRAY[1,2,3] || ARRAY[4,5,6] -> {1,2,3,4,5,6}

Add as a child element of an array

If one side is a two-dimensional array, the array will be added to the head node or tail node of the two-dimensional array according to the positions of both sides

ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] -> {{1,2,3},{4,5,6},{7,8,9}}

Value is added to the array

Depending on whether the value is on the left or right, a single value will be added as the head or tail node of the array

ARRAY[4,5,6] || 7 -> {4,5,6,7}

functionparametereffectgrammarCorresponding operatorresult
array_appendarray,elementAdd elements to the end of the arrayarray_append(ARRAY[1,2], 3)||{1,2,3}
array_prependelement,arrayAdd elements to the head of the arrayarray_append(3,ARRAY[1,2])||{3,1,2}
array_catarray,arrayCombine two array elementsarray_cat(ARRAY[1,2,3], ARRAY[4,5])||{1,2,3,4,5}
array_dimsarrayUsed to return the previous and next of the array. When it is a multi-dimensional array, its range will be displayed in turn (the lower bound starts at 1 by default)array_dims(ARRAY[[1,2,3], [4,5,6]])[1:2][1:3]
array_lowerarray, int (the number of dimensions of the array)Returns the lower bound of the specified dimensionarray_lower(ARRAY[[1,2,3], [4,5,6]],2)1
array_upperarray, int (the number of dimensions of the array)Returns the upper bound of the specified dimensionarray_upper(ARRAY[1,2,3,4], 1)4
array_to_stringarray,textUse the specified delimiter to convert the array to a stringarray_to_string(ARRAY[1, 2, 3], ‘,’)1,2,3
string_to_arraytext…Split the specified field into an arrayselect string_to_array(‘a-b-c’,’-’)ARRAY[‘a’,‘b’,‘c’]{a,b,c}