Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

group concat - JPA Criteria API group_concat usage

I am currently working on a report which needs a group_concat for one of the fields.

CriteriaQuery<GameDetailsDto> criteriaQuery = criteriaBuilder
                .createQuery(GameDetailsDto.class);
Root<BetDetails> betDetails = criteriaQuery.from(BetDetails.class);
Expression<String> betSelection = betDetails.get("winningOutcome");
criteriaQuery.multiselect(
    // other fields to select
    criteriaBuilder.function("group_concat", String.class, betSelection),
    // other fields to select
    );
//predicate, where clause and other filters

TypedQuery<GameDetailsDto> typedQuery = entityManager.createQuery(criteriaQuery);

this throws a null pointer exception on the line:

TypedQuery<GameDetailsDto> typedQuery = entityManager.createQuery(criteriaQuery);

did i incorrectly use the function method of the criteriaBuilder?
the documentations says:

function(String name, Class<T> type, Expression<?>... args);
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I figured out how to do this with Hibernate-jpa-mysql:

1.) created a GroupConcatFunction class extending org.hibernate.dialect.function.SQLFunction (this is for single column group_concat for now)

public class GroupConcatFunction implements SQLFunction {

@Override
public boolean hasArguments() {
    return true;
}

@Override
public boolean hasParenthesesIfNoArguments() {
    return true;
}

@Override
public Type getReturnType(Type firstArgumentType, Mapping mapping)
        throws QueryException {
    return StandardBasicTypes.STRING;
}

@Override
public String render(Type firstArgumentType, List arguments,
        SessionFactoryImplementor factory) throws QueryException {
    if (arguments.size() != 1) {
        throw new QueryException(new IllegalArgumentException(
                "group_concat shoudl have one arg"));
    }
    return "group_concat(" + arguments.get(0) + ")";
}

}


2.) i created the CustomMySql5Dialect class extending org.hibernate.dialect.MySQL5Dialect and registered the group_concat class created in step 1
3.) On the app context, i updated the jpaVendorAdapter to use the CustomMySql5Dialect as the databasePlatform
4.) Finally to use it

criteriaBuilder.function("group_concat", String.class,
        sampleRoot.get("sampleColumnName"))

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...