I have posted this request before but no solution. The respective JIRA log is
http://jira.nhibernate.org/browse/NH-455
I just finished the modifications and attached the modified files with that JIRA log. This solution is a temporary one before migrating Hibernate 3.x.
I really hope NHibernate development team can include my contribute to the next release, which is before implementing Hibernate 3.x. I have not found an easy and clean way to provide same ability by an outsider wrapper. But this ability is quite necessary for our applications, and I think it will be quite usefull for many other people.
Please do let me know if you will or will not include this in next release, or there is anything you want me to correct or improve about my approach.
Thanks
------------------------------------------------------------------------------------
The modified files are SessionImpl.cs, SqlLoader.cs, and StandardQueryCache.cs. All the modified place are commented with "// scalar query"
Let me explan my approach here:
1. Modify SessionImpl.cs FindBySQL(...) to allow specifing primitive types when calling session.CreateSQLQuery(...)
First check if the user want a scalar query
Code:
// scalar query
bool scalarReturn = false;
for( int i = 0; i < classes.Length; i++ )
{
System.Type theType = classes[ i ];
if ( theType.IsValueType || theType.Equals(typeof(string)) )
{
scalarReturn = true;
break;
}
persisters[ i ] = GetSqlLoadable( theType );
}
Next, make sure scalar query and object query cannot mix with each other, then call a new SqlLoader constructor to pass in the primitive types array.
Code:
// scalar query
if ( scalarReturn )
{
foreach(System.Type type in classes)
{
if ( ! type.IsValueType && ! type.Equals(typeof(string)) )
{
throw new QueryException("Cannot allow scalar query with type: "+type.FullName);
}
}
persisters = new ISqlLoadable[0];
loader = new SqlLoader( aliases, classes, factory, sqlQuery, querySpaces );
}
else
{
loader = new SqlLoader( aliases, persisters, factory, sqlQuery, querySpaces );
}
Currently, only support all the ValueTypes and string, for Blob, raw columns, I am not quite familiar with them yet, and we don't need them yet. So, I will leave them for now.
2. Add private field scalarTypes to class SqlLoader, to hold the return types info:
Code:
// scalar query, used to store scalar types
private System.Type[] scalarTypes;
3. Create the new SqlLoader constructor:
Code:
// scalar query
public SqlLoader( string[ ] aliases, System.Type[ ] classes, ISessionFactoryImplementor factory, string sqlQuery, ICollection additionalQuerySpaces )
: base( factory.Dialect )
{
this.sqlQuery = sqlQuery;
this.aliases = aliases;
// Remember the factory for the PopulateSqlString call.
this.factory = factory;
alias2Persister = new Hashtable();
ArrayList resultTypeList = new ArrayList();
scalarTypes = classes;
RenderStatement( new ILoadable[]{} );
PostInstantiate();
}
4. Modify method GetResultColumnOrRow(...) of class SqlLoader to allow returning of scalar values.
Code:
protected override object GetResultColumnOrRow( object[ ] row, IDataReader rs, ISessionImplementor session )
{
if( Persisters.Length == 1 )
{
return row[ row.Length - 1 ];
}
else
{
// scalar query, convert type
if ( scalarTypes != null )
{
row = new object[rs.FieldCount];
for( int i = 0; i < row.Length; i++ )
{
object obj = rs[i];
if (obj is DBNull)
{
obj = null;
}
else
{
obj = Convert.ChangeType(obj,scalarTypes[i]);
}
row[i] = obj;
}
}
return row;
}
}
5. Modify method SubstituteBrackets() of class SqlLoader to deal with alias name for scalar types:
Code:
// scalar query
ISqlLoadable currentPersister = null;
int currentPersisterIndex = 0;
if ( Persisters.Length == 0 && firstDot != -1)
{
throw new QueryException( "Scalar query cannot contains dot (.) in alias." );
}
else if ( Persisters != null && Persisters.Length > 0)
{
currentPersister = GetPersisterByResultAlias( aliasName );
......
So far, we finished the modification to allow scalar values return by sql-query. One of my example of using it, in our application is :
Code:
sqlStr =
"SELECT " +
" SUM(ID) KEEP (DENSE_RANK LAST ORDER BY valid_date) AS {MVID}, " +
" MAX(valid_date) AS {ValidDate}, " +
" SUM(value_numeric) KEEP (DENSE_RANK LAST ORDER BY valid_date) AS {Value} " +
"FROM MEASURED_VALUES mv " +
"WHERE " +
" mv.SEC_ID = :paramSECID " +
" AND ( " +
" currency_id IS NULL AND NOT EXISTS (SELECT id FROM currencies WHERE iso_code = :paramCurrIsoCode) " +
" OR " +
" currency_id = (SELECT id FROM currencies WHERE iso_code = :paramCurrIsoCode) " +
" ) " +
" AND mv.MVT_ID = (SELECT id FROM measured_value_types WHERE name = :paramMvtName) " +
"GROUP BY TRUNC(valid_date, '" + dateTruncPeriod + "') " +
"ORDER BY MAX(valid_date) ";
query = p_session
.CreateSQLQuery(
sqlStr
,new string[] {"MVID", "ValidDate", "Value"}
,new System.Type[] {typeof(long), typeof(DateTime), typeof(double)}
);
6. What I like is the ability to cache the query result and let the user manipulate the cache same way as existing query cache. So I have to modify StandardQueryCache.cs, change the Put(...) and Get(...) methods.
Code:
public void Put( QueryKey key, IType[ ] returnTypes, IList result, ISessionImplementor session )
{
......
{
// scalar query cache, cached the result as a single object
cacheable = new ArrayList( 2 );
cacheable.Add( session.Timestamp );
cacheable.Add(result);
}
queryCache.Put( key, cacheable );
}
public IList Get( QueryKey key, IType[ ] returnTypes, ISet spaces, ISessionImplementor session )
{
......
{
// scalar query cached, return as a single object.
result = (IList) cacheable[1];
}
return result;
}
The purpose of this cache is not cache each of the object value of return, but cache the whole return results as a single object. Because usually we want to make use of only query necessary scalar values and need to cache the result is because the result contains too many records, say 100K +, we just want several columns which we need, we don't want the whole object, and if we cache it, we just need to put and get the result as a whole set, as long as the query parameter are same, the scalar value from cache is good enough.
The limitation is very obvious: when NHibernate Session update some objects which has values those scalar query readed out already, NHibernate cannot automatically expire the cache. But because the value are scalar, not link with any objects, this cannot be solved. User has to control the refresh manually.
Fortuanately, query.SetForceCacheRefresh() can fulfill the work easily enough.
I don't support HQL scalar query cache yet, cause I feel its not very necessary, cause scalar query and cache usually means very extrem performance tunning, and usually, we need the native sql already anyways. Only if I have extra time I will see if I can may HQL scalar query cached.
Login
FAQ
Search
