JPA Expression concatenate more than two columns

Question

I have the following statement to concatenate two columns which works well

Expression<String> stringConcat = 
            cb.concat(cb.concat(root.get(Employee_.userId), " # "), 
                                   joinDept.get(Employee_.empName));

and SQL is

select emp.user_id|| ' # '|| dept.emp_name from ..       

I would like to concatenate one more column and SQL is

select emp.user_id|| ' # '|| dept.emp_name|| ' # '|| hist.user_name from ..       

Not sure how add other columns in JPA API using CriteriaBuilder and Expression

Edit 1

I am looking for concatenation using multiple columns and answer which is marked as duplicate doesn't help to resolve the problem and most importantly this question is tagged and seeking solution to resolve concatenation issue pertains to JPA Criteria API and certainly not JPQL.

Spacetime

@TobiasLiefke Could you please explain how to use multiple columns concatenation with Expression? – Spacetime

@TobiasLiefke I suggest please remove duplicate mark from the question. – Spacetime

Not a duplicate, since the linked answer refers to JPQL and not the Criteria API approach. – Commines

Answer 1

You can basically wrap the concat(...) into each other, or use a method like the following (assuming you want to use the same delimiter string between columns):

private CriteriaBuilder criteriaBuilder = /* ... */

// notice the three dots before "expressions", they are no decoration ;-)
private Expression<String> concat(String delimiter, Expression<String> ... expressions) {
    Expression<String> result = null;
    for (int i = 0; i < expressions.length; i++) {
        final boolean first = i == 0, last = i == (expressions.length - 1);
        final Expression<String> expression = expressions[i];
        if (first && last) {
            result = expression;
        } else if (first) {
            result = criteriaBuilder.concat(expression, delimiter);
        } else {
            result = criteriaBuilder.concat(result, expression);
            if (!last) {
                result = criteriaBuilder.concat(result, delimiter);
            }
        }
    }
    return result;
}

Expression<String> userId = root.get(Employee_.userId);
Expression<String> empName = joinDept.get(Employee_.empName);
Expression<String> userName = hist.get(User_.name); // or whatever

Expression<String> stringConcat = concat(" # ", userId, empName, userName);

Commines

Yes delimiter remain same. I will try this approach. – Spacetime

I am getting compile error for expressions.length – Spacetime

What error? Did you forget the three dots "..."? Are you using Java < 1.5.x? – Commines

Excellent, this resolved the issue. Much appreciated. – Spacetime

The fact that they didn't write this functionality in themselves for concat, a function that can have as many parameters as is needed, is pretty pathetic. – Overdose

Please note it does not work as desired when one of the expressions points to a null column – Euglena

Answer 2

Here is a simpler implementation of a multi-concat function than provided by jabu.10245.

public static Expression<String> concat(CriteriaBuilder cb, String separator,
        Expression<String>... expressions) {
    // Returns an empty string if no expression is provided
    if (expressions.length == 0) {
        return cb.literal("");
    }
    // Start with the first expression
    Expression<String> result = expressions[0];
    // Then concat subsequent expressions (starting from the second one)
    for (int i = 1; i < expressions.length; i++) {
        result = cb.concat(result, cb.concat(separator, expressions[i]));
    }
    return result;
}

Bonus: it returns an empty string expression instead of plain null when called without any expression in parameters. It could arguably return cb.nullLiteral(String.class) instead in that case.

Dividend