QueryDSL Null Pointer exception left join unrelated tables-CodePudding

I have got two tables which are unrelated . Below are the entity classes .

UsersStaging

@Entity
@Table(name = "users_staging")
@Data
public class Usersstaging implements Serializable{
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native", strategy = "native")
    @Column(name = "id", updatable = false, nullable = false)
    private Integer id;
    
    @Column(name = "employee_id", length = 10)
    private String employeeId;
    
    @Column(name="user_name")
    private String userName;
    
    @Column(name = "email")
    private String email;

Roles table

@Entity
@Table(name = "user_roles")
@Data
@TypeDefs({ @TypeDef(name = "json", typeClass = JsonType.class) })
public class UserRoles implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Column(name = "employeeId", length = 10)
    private String employeeId;

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

    @Type(type = "json")
    @Column(name = "roles", columnDefinition = "json")
    private List<String> roles;

}

I am trying to write the below query by using left join on username column. Query works fine as long as there are predicates(exp) for UsersStaging table . But if I add a predicate for userRoles table (roleExp) , the query fails .

JPAQuery<UsersStaging> query = new JPAQuery<>(entityManager);
        QUsersStaging usersStagingPath = QUsersStaging.usersStaging;
        QUserRoles userRolesPath = QUserRoles.userRoles;
        BooleanExpression exp = usersStagingPath.legalFirstName
                        .containsIgnoreCase(criteria.getValue());
        BooleanExpression roleExp = userRolesPath.roles.any().eq("SUPERADMIN");
        return query
                .select(Projections.constructor(UsersStagingDTO.class, usersStagingPath.employeeId,
                        usersStagingPath.userName, usersStagingPath.email, usersStagingPath.isActive,
                        usersStagingPath.legalFirstName, usersStagingPath.legalLastName, usersStagingPath.legalFullName,
                        userRolesPath.roles))
                .from(usersStagingPath).leftJoin(userRolesPath).on(usersStagingPath.userName.eq(userRolesPath.username))
                .where(exp.and(roleExp)).limit(10).fetch();
    }

Getting the below exception

java.lang.NullPointerException: null
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:432) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:4007) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3793) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3671) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:746) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:602) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.collectionFunctionOrSubselect(HqlSqlBaseWalker.java:5020) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4713) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2180) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2108) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:841) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]

CodePudding user response：

I was able to resolve it . The issue was that roles field is a json type column and JSON_CONTAINS function is not supported by Hibernate yet .

To fix it , I implemented the MetadataBuilderContributor interface .

public class SQLFunctionContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("json_contains_key",
                new SQLFunctionTemplate(BooleanType.INSTANCE, "JSON_CONTAINS(?1, JSON_QUOTE(?2))"));
    }

}

Add the below line in application.properties so that Hibernate can register the contrib SQL function .

spring.jpa.properties.hibernate.metadata_builder_contributor=com.xxx.xxx.utils.SQLFunctionContributor

I modified my query in below way and no issues.

BooleanExpression roleExp = Expressions.booleanTemplate("json_contains_key({0}, {1})", userRolesPath.roles,
                        Expressions.constant(criteria.getValue())).isTrue();