query all row in table that is link from another table using query builder

Question

I have a table that have few rows of variable. One of this row link to another table that have more rows.

To put it clear, 1 table is called Connection. The variable is:

name, groupname, etc.

The groupname should link to a second table called ConnectionGroup. The variable is:

name, id.

My idea is to query to the ConnectionGroup table by name. The abstract class for Connection and ConnectionGroup is like this:

public abstract class Connection_ {

    public static volatile SingularAttribute<Connection, String>name;
    public static volatile SingularAttribute<Connection, String>host;
    public static volatile SetAttribute<Connection, ConnectionGroup>connectionGroups;
}

public abstract class ConnectionGroup_ {

    public static volatile SingularAttribute<ConnectionGroup, String> name;
    public static volatile SingularAttribute<ConnectionGroup, Long> id;

}

To query this, I assume I have to join these 2 table and then only query them. This is the code that I have tried:

@PersistenceContext
private EntityManager em;

public List<Connection> retrieveAll( String groupFilter, int start, int length) {
  ServiceUtil.requireAdmin();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Connection> q = cb.createQuery(Connection.class);
  Root<Connection> c = q.from(Connection.class);
  Join<Connection, ConnectionGroup> join = c.join(Connection_.connectionGroups);
  q.select(c);

  c.fetch(Connection_.connectionGroups).fetch(ConnectionGroup_.id);
  Predicate groupPredicate = cb.equal(
  c.get(Connection_.connectionGroups), "%" + groupFilter + "%");
  q.where(groupPredicate);

  List<Connection> results = em.createQuery(q).setFirstResult(start)
            .setMaxResults(length).getResultList();
  for (Connection conn : results) {
      logger.info( "getconnectionGroups =["+ conn.getConnectionGroups() + "]");
      for (ConnectionGroup conngroup : conn.getConnectionGroups()) {
            logger.info("connectiongroups = [" + conngroup.getName() + "]");
      }
  }
}

Things that I have tried, changing this:

Predicate groupPredicate = cb.equal(
  c.get(Connection_.connectionGroups), "%" + groupFilter + "%");

to this:

Predicate groupPredicate = cb.equal(join.get(ConnectionGroup_.name),
            "%" + groupFilter + "%");

Changing this:

Join<Connection, ConnectionGroup> join = c
            .join(Connection_.connectionGroups);

To this:

Join<Connection, ConnectionGroup> join = c.join("connectionGroups");

When I tried these method, I keep getting an exception Cannot join to attribute of basic type

I also tried changing the code into this:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Connection> q = cb.createQuery(Connection.class);
Root<Connection> c = q.from(Connection.class);
Join<Connection, ConnectionGroup> join = (Join<Connection, ConnectionGroup>) c.fetch(Connection_.connectionGroups);
q.select(c);

Predicate groupPredicate = cb.equal(join.get(ConnectionGroup_.name), "%" + groupFilter + "%");
q.where(groupPredicate);
List<Connection> results = em.createQuery(q).setFirstResult(start)
            .setMaxResults(length).getResultList();

Which return an exception:

query specified join fetching, but the owner of the fetched association was not present in the select list

I'm using these site as reference to write the code:

JPA CriteriaBuilder using fetch joins and result objects

JPA 2 Criteria Fetch Path Navigation

wiki.eclipse.org

developer.com

How can I query the name from ConnectionGroup table? Is my approach wrong?

Answer 1

If you're trying to use filtering by name through string matching, CriteriaBuilder.like() is more appropriate to use than CriteriaBuilder.equal().

To achieve what you're trying to query, you can use the ff. JP QL query:

SELECT DISTINCT conn FROM Connection conn JOIN conn.connectionGroups connGrp
WHERE connGrp.name LIKE :groupFilter

Translating JP QL to CriteriaQuery, you'll have:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Connection> q = cb.createQuery(Connection.class);
Root<Connection> conn = q.from(Connection.class);
Join<Connection, ConnectionGroup> connGrp = conn.join(Connection_.connectionGroups);
q.select(conn).distinct(true);
ParameterExpression<String> param = cb.parameter(String.class, "%"+ groupFilter + "%");
q.where(cb.like(connGrp.get(ConnectionGroup_name), param));