Java Criteria API: Select single column from one-to-many relationship table

Question

I am trying to select a single column from a related table. I have a table (Item) with many Values. I would like to select Value.valueString.

Basically, the query is supposed to pass in a bunch of values and pull any ValueFields that contain those values. The SQL might look something like this:

select ItemValues.valueString from ItemEntity
    join StockItem on ItemEntity.stockItemId = StockItem.id
    join ItemValues on ItemEntity.id = ItemValues.itemId
where StockItem.vendor = vendorId 
AND (ItemValues.valueString like '%test%' OR ItemValues.valueString like '%test2%'...);

Here is my code:

        final CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();
        final CriteriaQuery<String> query = builder.createQuery(String.class);

        final Root<ItemEntity> root = query.from(ItemEntity.class);

        query.select(root.join("ItemValues").<String>get("ValueString"));

        final List<Predicate> filters = new LinkedList<Predicate>();

        filters.add(builder.equal(root.join("StockItem").get("id"), vendorNumber));
        final List<Predicate> filterNamesCriteria = new LinkedList<Predicate>();

        if (filenames.length > 0) {

            for (String fileName : filenames) {
                filterNamesCriteria.add(builder.like(root.join("ItemValues").<String>get("ValueString"), fileName));
            }
            filters.add(builder.or(filterNamesCriteria.toArray(new Predicate[0])));
        }

        query.where(filters.toArray(new Predicate[0]));

        final TypedQuery<String> resolvedQuery = this.entityManager.createQuery(query);

        return resolvedQuery.getResultList();

I want the result to return a List of Strings (valueString column), but it's not returning anything.

Am I doing something wrong? When I say "builder.createQuery(String.class)", is that correct?

You need a Tuple: http://stackoverflow.com/questions/3842122/complex-queries-with-jpa-criteria-builder — a.drew.b, Sep 10 '12 at 23:41
But I'm only selecting one value. Isn't a tuple for multiselects? — dmoss18, Sep 11 '12 at 12:46
@a.drew.b The OP doesn't need a tuple, the OP can achieve this using an IN operation. `select .. from ... where value IN :collection`. I could make an example but I don't feel like figuring out how the entities are mapped. OP please show how the entities are related (classes). — siebz0r, Sep 11 '12 at 14:02
Item has many ItemValues Item has one StockItem I can't do an "IN" operation because ItemValues.valueString could contain multiple concatenated values (ex: value1;value2;value3 stored as a semi-colon-delimited string). So I must do a (LIKE 'foo' OR LIKE 'bar' OR LIKE 'fubar') — dmoss18, Sep 11 '12 at 14:16

score 0 · Accepted Answer · answered Sep 13 '12 at 18:51

I found the problem:

filters.add(builder.equal(root.join("StockItem").get("id"), vendorNumber));

I was joining based on the StockItem id and not the StockItem.itemNumber

I used two queries to solve the issue of joining the Itemvalues map (it was returning 32,000+ results)

Java Criteria API: Select single column from one-to-many relationship table

1 Answers1