DetachedCriteria: get sql alias used for sql projection or sql restriction
Description
Activity

Lynne Brown August 27, 2013 at 1:39 PM
Thank you very much for the response. I think you're rught to focus on JPA
now.

Brett Meyer August 20, 2013 at 3:08 PM
As mentioned on the PR:
Actually, we'll probably switch directions on this one:
We discussed it extensively on IRC this morning. As mentioned on http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch12.html, the org.hibernate.Criteria API is legacy and should be considered mostly deprecated. For the most part, we had decided not to accept new features for it. Instead, we're starting to focus on the JPA Criteria API which is much more powerful and concise. Yes, I did go against that and merged #554, but that was a really narrowly scoped issue that won't require much support. However, we feel that this new feature is too far-reaching and may require additional work/support in the future (ensuring other areas are "alias-aware", etc.).
Further, your specific use case (and others) can already be accomplished by JPA Criteria. For example:
09:55:06 AM) sebersole: so in your example...
(09:55:48 AM) sebersole: you'd have references to "c" and "e" which would be some type of javax.persistence.criteria.From
(09:55:56 AM) sebersole: a Root, or Join, or Fetch
(09:56:09 AM) sebersole: and you'd get a path like:
(09:56:30 AM) sebersole: Root<Person> personRoot = ...;
(09:57:31 AM) sebersole: Path<Person,String> personCodePath = personRoot.get( Person_.code );
(09:57:44 AM) sebersole: / same for "e"
(09:57:53 AM) sebersole: and then...
(09:59:02 AM) sebersole: criteria.where( builder.eq( builder.lower( personCodePath ), otherCodePath ) )
If other use cases are found to be missing, we'd rather focus on creating extensions there, rather than in the legacy Criteria. Sorry for stringing you along. Let me know if you have any comments.

Jonathan Card May 13, 2013 at 7:29 PM
Pull Request opened for this:
https://github.com/hibernate/hibernate-orm/pull/533

HarpreetA June 8, 2012 at 11:54 AM
I have created one class to resolve this issue. This class is reflection of SQLProjection. The whole functionality is same as
that of SQLProjection except that it provides one additional functionality.
According to current scenario we can only replace the root alias with its SQL equivalent alias but with this you can provide
the alias which you want to replace with the SQL alias and its necessary to provide both alias name and criteria both together.
public class MySQLProjection implements Projection {
/** The Constant serialVersionUID. */
private static final long serialVersionUID = 1L;
/** The sql query string to retrieve columns. */
private final String sql;
/** The sql string to specify group by query. */
private final String groupBy;
/** The types of the columns retrieved in sql query. */
private final Type[] types;
/** The aliases of the columns retrieved in sql query. */
private String[] aliases;
/** The aliases of the columns retrieved in sql query. */
private String[] columnAliases;
/** True if group by property is there means it is sql group projection. */
private boolean grouped;
/**
The set of aliases to be replaced with their SQL aliases. Here set must
* contain values as POJOAlias.property from which we get sql alias for
* POJOAlias. One POJOAlias with one dot operator and property is must.
*/
private Set<String> aliasSetToReplace;
@Override
public String toSqlString(final Criteria criteria, final int loc,
final CriteriaQuery criteriaQuery) throws HibernateException {
return replaceAliasWithSQLAlias(sql, criteria, criteriaQuery);
}
@Override
public String toGroupSqlString(final Criteria criteria, final CriteriaQuery criteriaQuery)
throws HibernateException {
return replaceAliasWithSQLAlias(groupBy, criteria, criteriaQuery);
}
/**
Replace alias in the given query string with their sql aliases from
* criteria query.
*
* @param queryString
* the query string
* @param criteria
* the criteria
* @param criteriaQuery
* the criteria query
* @return the string
*/
private String replaceAliasWithSQLAlias(final String queryString, final Criteria criteria,
final CriteriaQuery criteriaQuery) {
String sqlString = StringHelper.replace(queryString, "{alias}",
criteriaQuery.getSQLAlias(criteria));
Iterator<String> iterator = aliasSetToReplace.iterator();
String aliasPropString;
String aliasString;
int index;
while (iterator.hasNext()) {
aliasPropString = iterator.next();
index = aliasPropString.indexOf('.');
if (index > 0) {
aliasString = aliasPropString.substring(0, index);
sqlString = StringHelper.replace(sqlString, "{" + aliasString + "}",
criteriaQuery.getSQLAlias(criteria, aliasPropString));
}
}
return sqlString;
}
@Override
public Type[] getTypes(final Criteria crit, final CriteriaQuery criteriaQuery)
throws HibernateException {
return types;
}
@Override
public String toString() {
return sql;
}
/**
Instantiates a new my sql projection.
*
* @param sqlQuery
* the sql
* @param columnAliasArray
* the column aliases
* @param typeArray
* the types
*/
public MySQLProjection(final String sqlQuery, final String[] columnAliasArray,
final Type[] typeArray) {
this(sqlQuery, null, columnAliasArray, typeArray);
}
/**
Instantiates a new my sql projection.
*
* @param sqlQuery
* the sql
* @param groupBySQL
* the group by
* @param columnAliasArray
* the column aliases
* @param typeArray
* the types
*/
public MySQLProjection(final String sqlQuery, final String groupBySQL,
final String[] columnAliasArray, final Type[] typeArray) {
this(sqlQuery, groupBySQL, columnAliasArray, typeArray, null);
}
/**
Instantiates a new my sql projection.
*
* @param sqlQuery
* the sql
* @param groupBySQL
* the group by
* @param columnAliasArray
* the column aliases
* @param typeArray
* the types
* @param aliasSet
* the aliases map to replace
*/
public MySQLProjection(final String sqlQuery, final String groupBySQL,
final String[] columnAliasArray, final Type[] typeArray,
final Set<String> aliasSet) {
this.sql = sqlQuery;
this.types = typeArray;
this.aliases = columnAliasArray;
this.columnAliases = columnAliasArray;
this.grouped = groupBySQL != null;
this.groupBy = groupBySQL;
if (aliasSet == null) {
this.aliasSetToReplace = new HashSet<String>();
} else {
this.aliasSetToReplace = aliasSet;
}
}
@Override
public String[] getAliases() {
return aliases;
}
@Override
public String[] getColumnAliases(final int loc) {
return columnAliases;
}
@Override
public boolean isGrouped() {
return grouped;
}
@Override
public Type[] getTypes(final String alias, final Criteria crit,
final CriteriaQuery criteriaQuery) {
return null; // unsupported
}
@Override
public String[] getColumnAliases(final String alias, final int loc) {
return null; // unsupported
}
}

Matthieu Maquevice November 7, 2011 at 3:19 PM
The method is also needed for the Criteria class.
Details
Details
Assignee

Reporter

A method to get sql alias is needed from DetachedCriteria in order to use
non-root entity alias in sql restriction or sql projection.
DetachedCriteria:
String getSqlAlias(String alias) // alias has been created
detachedCriteria.createAlias("group","g");
String sqlAlias = detachedCriteria.getSqlAlias("g");
detachedCriteria.add(Restrictions.sqlRestriction(sqlAlias + ".name='John'"));
detachedCriteria.setProjection(Restrictions.sqlProjection(sqlAlias + ".name"));