[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-817) using projections is causing SQL query error on oracle (ORA-00904 error)

[Chris Federowicz (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-817?page=comments#action_26375 ] 

Chris Federowicz commented on HHH-817:
--------------------------------------

I have a workaround that has worked for me in basic examples.  

Basically i'm using projections and the alias to bean transformer fo only load selected properties of an entity.  

	// Set up selected columns to load of the entity
	ProjectionList projectionList = Projections.projectionList();
	projectionList.add(new CustomPropertyAliasProjection("property1", "property1"));
	projectionList.add(new CustomPropertyAliasProjection("property2", "property2"));
	criteria.setProjection(projectionList);
	criteria.setResultTransformer(new PrefixAliasToBeanResultTransformer(entityClass));

	// Now I can use this without getting the ORA-00904 error
	criteria.add(Restrictions.eq("property1", new Integer(1)));


Here's the class I wrote to override AliasedProjection.  I can't guarantee it works in all cases.  It would be great if someone from the Hibernate team can take a quick look and see if there are any potential problems that I've overlooked.  

	/**
	 * Customized projection to map a property with an alias that is intended to work-around HHH-817.
	 * This works in a basic situation that I've tried, but I can't guarantee it works in complex queries.
	 * 
	 * This causes the alias to be bypassed in the where clause so the original column name is used.
	 * Some DB's don't support aliases in the where clause.
	 * 
	 * http://opensource.atlassian.com/projects/hibernate/browse/HHH-817
	 * 
	 * @author Chris Federowicz
	 */
	public class CustomPropertyAliasProjection extends AliasedProjection {
		/** Stores the property name being aliased. */
		private String propertyName;
		/** Stores the column name for the property in the PropertyProjection. */
		private String propertyColumn;
		
		/**
		 * @param propertyName the property name
		 * @param alias alias of the property
		 */
		public CustomPropertyAliasProjection(String propertyName, String alias) {
			super(Projections.property(propertyName), alias);
			this.propertyName = propertyName;
		}

		/** Default serialization ID. */
		private static final long serialVersionUID = 1L;

		/**
		 * @see org.hibernate.criterion.AliasedProjection#toSqlString(org.hibernate.Criteria, int, org.hibernate.criterion.CriteriaQuery)
		 */
		public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
			String ret = super.toSqlString(criteria, position, criteriaQuery);
			
			// Store the property's real column for use in getColumnAliases
			propertyColumn = criteriaQuery.getColumn(criteria, propertyName);
			
			return ret;
		}

		/**
		 * Override to use the property's column if we have it (which we should).  
		 * Hack alert: This assumes that toSqlString gets called beforehand, which seems to be the case.
		 * 
		 * @see org.hibernate.criterion.AliasedProjection#getColumnAliases(java.lang.String, int)
		 */
		public String[] getColumnAliases(String alias, int loc) {
			return propertyColumn != null ? new String[] {propertyColumn} : super.getColumnAliases(alias, loc);
		}
		
	}


> using projections is causing SQL query error on oracle (ORA-00904 error)
> ------------------------------------------------------------------------
>
>          Key: HHH-817
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-817
>      Project: Hibernate3
>         Type: Bug

>     Versions: 3.0.5
>  Environment: Oracle 9.2.0.6, Hibernate 3.0.5, Spring Framework 1.2.2 based application working on Jakarta Tomcat 5.0.28
>     Reporter: Michal Jastak
>     Priority: Minor

>
>
> following java code:
> protected Entity loadEntityLightweight(Serializable entityId) throws DataAccessException {
>     Criteria criteria = getSession().createCriteria(Entity.class);    
>     ProjectionList projectionList = Projections.projectionList();
>     projectionList.add(Property.forName(BaseEntity.PROP_ID), BaseEntity.PROP_ID);
>     projectionList.add(Property.forName(BaseEntity.PROP_TYPE), BaseEntity.PROP_TYPE);
>     criteria.setProjection(projectionList);
>     criteria.add(Restrictions.eq(BaseEntity.PROP_ID, entityId));
>     criteria.setResultTransformer(new AliasToBeanResultTransformer(Entity.class));
>     return (Entity) criteria.uniqueResult();
>   }
> generates following SQL query:
> select this_.id as y0_, this_.type as y1_ from entities this_ left outer join facilities this_1_ on this_.id=this_1_.id left outer join users this_2_ on this_.id=this_2_.id left outer join addresses address2_ on this_.address_id=address2_.id left outer join entities entity3_ on this_2_.employer_id=entity3_.id left outer join facilities entity3_1_ on entity3_.id=entity3_1_.id left outer join users entity3_2_ on entity3_.id=entity3_2_.id where y0_=?
> y0_ = ? expression in where clause is causing a 904 error on Oracle 9:
> ORA-00904: "Y0_": invalid identifier
> hibernate dialect: org.hibernate.dialect.Oracle9Dialect
> mapping for Entity class:
> <?xml version="1.0"?>
> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
>                                    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
> <hibernate-mapping default-lazy="false" default-cascade="save-update">
>  
>   <class name="Entity" table="entities" mutable="true">
>     <id name="id" type="java.lang.Long" unsaved-value="null">
>       <generator class="sequence">
>         <param name="sequence">entities_id_seq</param>
>       </generator>
>     </id>
>     <many-to-one  name="address"   class="Address" column="address_id" />
>     ...
>     <!--
>       -  Facilities
>       -->
>     <joined-subclass name="Facility" table="facilities">
>       <key column="id" />
>       ...
>       <set name="users" inverse="true" lazy="true">
>         <key column="facility_id" />
>         <one-to-many class="User" />
>       </set>
>     </joined-subclass>
>     <!--
>       -  Users
>       -->
>     <joined-subclass name="User" table="users" dynamic-insert="true" dynamic-update="true">
>       <key column="id" />
>       <many-to-one name="employer"   class="Entity"  column="employer_id" cascade="none" />
>       ...
>       <set name="userAuthorities" inverse="true" cascade="all-delete-orphan">
>         <key          column="user_id" />
>         <one-to-many  class="Authority" />
>       </set>
>     </joined-subclass>
>   </class>
> </hibernate-mapping>

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira