Problem
I am developing a spring-boot application that needs to read data from an oracle database.
The database itself is outside of my control and I cannot change the way it's structured.
I'm using spring-data-jpa in combination with hibernate to interact with the database.
I have the following entity:
@Entity
@Table(name = "mytable")
public class MyEntity {
@Id
@Column
private String entityId;
// This column might have a value like "abc,xyz,a1b" in the db
@Column
private String associatedIds;
}
The column associatedIds contains comma separated values (eg. abc,xyz,a1b).
Users of my application only have permission to read rows with atleast 1 associatedId they have access to. The Ids they have access to are represented as a List<String> allowedIds.
In order to ensure that users only receive the data they are allowed to, I need to restrict my queries. I'm having trouble constructing a query that meets my requirements.
If users only had access to a single Id I could simply use the following query using a simple LIKE operator:
@Query(value = "SELECT e FROM MyEntity e WHERE e.associatedIds LIKE '%:allowedId%'")
Page<MyEntity> findAllByAllowedId(@Param("allowedId") String allowedId, Pageable pageable);
That query isn't sufficient for my usecase because users have access to more than one Id.
I came up with a few ways to approach this problem, but didn't manage to fully implement any of them.
Approach 1: "IN LIKE"
The first approach would be a query like this:
@Query(value = "SELECT e FROM MyEntity e WHERE e.associatedIds IN (LIKE '%:allowedIds%')")
Page<MyEntity> findAllByAllowedIds(@Param("allowedIds") List<String> allowedIds, Pageable pageable);
This doesn't work because as far as I know there is no query combining IN and LIKE.
Approach 2: "AttributeConverter"
The next thing I tried was to convert the comma separated string to a List<String> using the following converter:
@Converter
public class StringArrayToStringConverter implements AttributeConverter<List<String>, String> {
@Override
public String convertToDatabaseColumn(List<String> strings) {
return strings == null ? null : StringUtils.join(strings, ',');
}
@Override
public List<String> convertToEntityAttribute(String s) {
if(StringUtils.isBlank(s)) {
return Collections.emptyList();
}
return Arrays.asList(s.split(","));
}
}
This conversion works, but doesn't allow me to construct queries as if the column were a list, because the database remains unchanged.
Approach 3: "@Formula"
Another possible solution might be hibernate's @Formula annotation. There might be a way to create a virtual column that can be searched according to my requirements. I have no clue where to start with that though, as I've never used that annotation before and don't quite understand how list types work in oracle. Another thing to consider is whether you can actually perform hql queries on formula columns.
Approach 4: "Multiple OR's"
The last possible solution I could come up with would involve adding a separate where clause for each allowed Id, this could end up looking like:
SELECT e FROM MyEntity e WHERE e.allowedIds LIKE'%allowedIds.get(0)%' OR e.allowedIds LIKE'%allowedIds.get(1)%' etc..
With this approach I also don't know how I could implement that in spring-data-jpa.
I would greatly appreciate some guidance on how to implement these solutions or suggestions I didn't think of.
