Hibernate Criteria Query to get only specific columns
<p>I have two tables: ReportRows and DataRows</p>

<p>Table ReportRows:</p>

<pre><code>@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;
</code></pre>

<p>additionally getters and setters;</p>

<p>Table DataRows:</p>

<pre><code>@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;
</code></pre>

<p>additionally getters and setters</p>

<p>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).</p>

<p>Select which I want to get looks like this:</p>

<pre><code>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
</code></pre>

<p>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.</p>

<p>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):</p>

<pre><code>public List&lt;ReportRow&gt; 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);
}
</code></pre>

<p>Methods getColumnId and getColumnId2:</p>

<pre><code>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();
  }
</code></pre>

<p>I get error:
org.hibernate.QueryException: could not resolve property: dataRow.value0</p>

<p>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?</p>

<p>Update 1:<br/>
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:</p>

<pre><code>where reportRowId = ?
</code></pre>

<p>Do you know why?</p>

<p>Custom Transformer transformTuple method looks like this for now:</p>

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

    for (int i = 0 ; i &lt; 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;
  }
</code></pre>