Filter by array using spring repository

Question

I've got the following setup :

Pojo :

@Data
@EqualsAndHashCode
@ToString
@NoArgsConstructor
@Entity
@Immutable
public class ExchangeView {

    @Id
    private Long id;

    private Integer number;

    @Type(type = "com.myapp.domain.custom.StringArrayType")
    private String[] exchangeObjects, environments, bus;

    private String name, technology, type;

}

Custom Type :

public class StringArrayType implements UserType{

private final int[] arrayTypes = new int[] { Types.ARRAY };

@Override
public int[] sqlTypes() {
    return arrayTypes;
}

@Override
public Class<String[]> returnedClass() {
    return String[].class;
}

@Override
public boolean equals(Object x, Object y) throws HibernateException {
    return x == null ? y == null : x.equals(y);
}

@Override
public int hashCode(Object x) throws HibernateException {
    return x == null ? 0 : x.hashCode();
}

@Override
public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
    // get the first column names
    if (names != null && names.length > 0 && rs != null && rs.getArray(names[0]) != null) {
        String[] results = (String[]) rs.getArray(names[0]).getArray();
        return results;
    }
    return null;
}

@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException,
        SQLException {
    // setting the column with string array
    if (value != null && st != null) {
        String[] castObject = (String[]) value;
        Array array = session.connection().createArrayOf("varchar", castObject);
        st.setArray(index, array);
    } else {
        st.setNull(index, arrayTypes[0]);
    }
}

@Override
public Object deepCopy(Object value) throws HibernateException {
    return value == null ? null : ((String[]) value).clone();
}

@Override
public boolean isMutable() {
    return false;
}

@Override
public Serializable disassemble(Object value) throws HibernateException {
    return (Serializable) value;
}

@Override
public Object assemble(Serializable cached, Object owner) throws HibernateException {
    return cached;
}

@Override
public Object replace(Object original, Object target, Object owner) throws HibernateException {
    return original;
}

public StringArrayType toStringArrayType(List<String> strings){


    return null;
}

}

And Spring repository :

public interface ExchangeViewRepository extends Repository<ExchangeView, Long> {

    List<ExchangeView> findAll();

    List<ExchangeView> findByEnvironments(String[] environments);


    List<ExchangeView> findByBus(String[] bus);
}

In my database I got some datas like this :

id      name        type    technology      environments                   bus

33      REGROUP     batch   ESB webMethods  {PREPRODUCTION,PRODUCTION,RECETTE}  {ASFR,BCIT}
34      CONDPAI     batch   ESB webMethods  {PREPRODUCTION,PRODUCTION,RECETTE}  {ASFR}

And in my controller i'm trying to use this method

List<ExchangeView> findByBus(String[] bus);

But it filters my table with the lines which have the exact array of bus that i'm passing on my method. I would like to have all the lines with the following bus (include the ones with other bus AND this list of bus).

Any ideas ?

Answer 1

You can use a native query in your repository to find all ExchangeViews that contain a given bus. I tested this against hsqldb and it works as expected.

@Query(nativeQuery = true, value = "select * from EXCHANGE_VIEW where POSITION_ARRAY(:bus IN bus) > 0")
List<ExchangeView> findByBus(@Param("bus") String bus);

The query will vary depending on your dbms.

Update

To get all rows that have any one of the given buses you can use this query:

@Query(nativeQuery = true, value = "select * from EXCHANGE_VIEW where id in ( select id from EXCHANGE_VIEW, unnest(bus) as bus(b) where b in(:bus_names))")
List<ExchangeView> findByBus(@Param("bus_names") List<String> buses);