Hibernate Criteria Query to get only specific columns

Question

I have two tables: ReportRows and DataRows

Table ReportRows:

@Id
@Column(name = "ID")
private Long id;

@ManyToOne
@JoinColumn(name = "T_REPORT_CODE")
  private Report report;

@OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumn(name = "T_DATA_ROWS_ID")
  private DataRow dataRow;

additionally getters and setters;

Table DataRows:

@Id
@Column(name = "ID")
    private Long id;

@Column(name = "VALUE_0")
    private String value0;

@Column(name = "VALUE_1")
    private String value1;

@Column(name = "VALUE_2")
    private String value2;

...

@Column(name = "VALUE_30")
    private String value30;

additionally getters and setters

Select from ReportRows and DataRows gets all columns from both tables when listing data. I need only a few columns from DataRows (i specify it).

Select which I want to get looks like this:

SELECT 
dataRows.value0,
dataRows.value1
from
t_report_rows this_
inner join
t_reports report_ on report_.id = this_.report_id
inner join
t_data_rows dataRows_ on dataRows_.id = this_t_data_rows_id
where report_.id = 1

It is only example which I wrote. The point is I need different columns each query. One time it will be value0, value1, second time it would be value0, value2.

When I do query I know which columns I need. I create criteria like this (Columns is class from which I retrive names of columns):

public List<ReportRow> list(Criteria criteria, Long reportId) {
    criteria.add(Restrictions.eq("report.id", reportId));
    ProjectionList projectionList = Projections.projectionList();
    for (Column col : columnList) {
        String columnName = getColumnId(col);
        projectionList.add(Projections.property(columnName));
        //I also tried like this:
        // projectionList.add(Projections.property(columnName), columnName);
        //or like this:
        //projectionList.add(Projections.property(columnName), getColumnId2(col));
        //also added this line with each of above:
        //criteria.setResultTransformer(Transformers.aliasToBean(ReportRow.class));
    }
    criteria.setProjection(projectionList);

    return list(criteria);
}

Methods getColumnId and getColumnId2:

protected String getColumnId(Column col) {
    StringBuilder b = new StringBuilder();
    b.append("dataRow.value");
    b.append(col.getRowIndex());
    return b.toString();
  }

  protected String getColumnId2(Column col) {
    StringBuilder b = new StringBuilder();
    b.append("value");
    b.append(col.getRowIndex());
    return b.toString();
  }

I get error: org.hibernate.QueryException: could not resolve property: dataRow.value0

Is it even possible to do what I want? Get only specific columns from table dataRows based on Projection and table Column which store columns indexes?

Update 1:
I added custom transformer. Everything works fine. It was done to increase speed of sql. Now it is doing query with small amount of columns. It's really good. I turned on show sql in hibernate. After custom transformer it is doing to database for each row new query with:

where reportRowId = ?

Do you know why?

Custom Transformer transformTuple method looks like this for now:

@Override
  public Object transformTuple(Object[] tuple, String[] aliases) {
    ReportRow row = new ReportRow();
    DataRow dataRow = new DataRow();
    row.setGridRow(dataRow);

    for (int i = 0 ; i < aliases.length; i++) {
      if ("id".equals(aliases[i])) {
        row.setId((Long) tuple[i]);
      } else if ("entityVersion".equals(aliases[i])) {
        row.setEntityVersion((Long) tuple[i]);
      } else if ("dataRowId".equals(aliases[i])) {
        dataRow.setId((Long) tuple[i]);
      } else if ("rowEntityVersion".equals(aliases[i])) {
        dataRow.setEntityVersion((Long) tuple[i]);
      } else if ("value0".equals(aliases[i])) {
        dataRow.setValue0((String) tuple[i]);
      } else if ("value1".equals(aliases[i])) {
        dataRow.setValue1((String) tuple[i]);
      }
    }

    return row;
  }

Precursory

How is your Criteria created? Unrelated, but your getColumnId() method could be reduced to return "dataRow.value" + col.getRowIndex();, which would be much more readable and as, if not more efficient. – Bassett

Criteria criteria = getSession().createCriteria(getKind()); where getKind returns class. In this example: ReportRow.class – Precursory

So, just as in your SQL query, you need a join: criteria.createAlias("dataRow", "dataRow"); – Bassett

Yes, it worked. Thanks. I have one more question. If I want return also in this query entity Report and columns value0, value1 what I have to do? – Precursory

AFAIK, that is not possible. Either you return an entity, or columns, but not both. – Bassett

Thanks. That was really helpful. – Precursory

I have problem because when I setResultTransformer it returns objects with null variables. Example. getId() is null (I added projectionList.add(Projections.property("id"));) – Precursory

Answer 1

I have problem because when I setResultTransformer it returns objects with null variables. Example. getId() is null (I added projectionList.add(Projections.property("id"));)

You need to specify an alias of the projection

projectionList.add(Projections.property("id").as("id"));

Yes, it worked. Thanks. I have one more question. If I want return also in this query entity Report and columns value0, value1 what I have to do?

You can get a part of Report by specifying projections for columns of it. You need to get ReportRows as root and properties of Report and DataRow using nested projections.

For nested projections you can use a custom transformer

How to transform a flat result set using Hibernate

Consultant

Answer 2

session = sessionFactory.openSession();
Criteria criteria = session.createCriteria(DlrStatus.class);
criteria.add(Restrictions.eq("userId",userId)).add(Restrictions.eq("jobId",jobId))
        .add(Restrictions.eq("status", status)).setFirstResult(start).setMaxResults(max).addOrder(Order.desc("id"));
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.property("mobile").as("mobile"));
projectionList.add(Projections.property("Sender").as("sender"));
projectionList.add(Projections.property("message").as("message"));
projectionList.add(Projections.property("loggedAt").as("loggedAt"));
projectionList.add(Projections.property("dlrTime").as("dlrTime"));
criteria.setProjection(projectionList);
criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List<DlrStatus> list = criteria.list();

Duncan

Can you please add some explanation to this code - especially for future readers to see what the problem was. – Nurture