public void SimpleSelectStringSqlTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor) factory;
SqlSimpleSelectBuilder select = new SqlSimpleSelectBuilder(factoryImpl.Dialect, factoryImpl);
select.SetTableName("test_simple_select_builder");
select.AddColumn("column_no_alias");
select.AddColumn("aliased_column", "aliased_column_alias");
select.AddColumns(new string[] {"column1_no_alias", "column2_no_alias"});
select.AddColumns(new string[] {"column1_with_alias", "column2_with_alias"}, new string[] {"c1_alias", "c2_alias"});
select.SetIdentityColumn(new string[] {"identity_column"}, NHibernateUtil.Int64);
select.SetVersionColumn(new string[] {"version_column"}, (IVersionType) NHibernateUtil.Int32);
select.AddWhereFragment(new string[] {"where_frag_column"}, NHibernateUtil.Int32, " = ");
SqlString sqlString = select.ToSqlString();
Parameter[] actualParams = new Parameter[3];
string expectedSql = new StringBuilder().Append("SELECT ")
.Append("column_no_alias, ")
.Append("aliased_column AS aliased_column_alias, ")
.Append("column1_no_alias, ")
.Append("column2_no_alias, ")
.Append("column1_with_alias AS c1_alias, ")
.Append("column2_with_alias AS c2_alias ")
.Append("FROM test_simple_select_builder ")
.Append("WHERE identity_column = ? AND version_column = ?")
.Append(" AND where_frag_column = ?")
.ToString();
Assert.AreEqual(expectedSql, sqlString.ToString(), "SQL String");
Assert.AreEqual(3, sqlString.GetParameterCount(), "3 parameters");
}
/// <summary>
/// Generates an SqlString that selects a row by id
/// </summary>
/// <param name="forUpdateFragment">SQL containing <c>FOR UPDATE</c> clauses
/// to append at the end of the query (optional)</param>
/// <returns></returns>
protected virtual SqlString GenerateSelectString(string forUpdateFragment)
{
SqlSimpleSelectBuilder builder = new SqlSimpleSelectBuilder(Factory);
// set the table name and add the columns to select
builder.SetTableName(TableName)
.AddColumns(IdentifierColumnNames)
.AddColumns(SubclassColumnClosure, SubclassColumnAliasClosure)
.AddColumns(SubclassFormulaClosure, SubclassFormulaAliasClosure);
if (HasSubclasses)
{
builder.AddColumn(DiscriminatorColumnName, DiscriminatorAlias);
}
// add the parameters to use in the WHERE clause
builder.SetIdentityColumn(IdentifierColumnNames, IdentifierType);
// Ok, render the SELECT statement
SqlString selectSqlString = builder.ToSqlString();
// add any special text that is contained in the forUpdateFragment
if (forUpdateFragment != null && forUpdateFragment.Length > 0)
{
selectSqlString = selectSqlString.Append(forUpdateFragment);
}
return selectSqlString;
}
/// <summary>
/// Generate the SQL that pessimistic locks a row by id (and version)
/// </summary>
/// <param name="sqlString">An existing SqlString to copy for then new SqlString.</param>
/// <param name="forUpdateFragment"></param>
/// <returns>A new SqlString</returns>
/// <remarks>
/// The parameter <c>sqlString</c> does not get modified. It is Cloned to make a new SqlString.
/// If the parameter<c>sqlString</c> is null a new one will be created.
/// </remarks>
protected override SqlString GenerateLockString( SqlString sqlString, string forUpdateFragment )
{
SqlStringBuilder sqlBuilder = null;
if( sqlString == null )
{
SqlSimpleSelectBuilder builder = new SqlSimpleSelectBuilder( factory );
// set the table name and add the columns to select
builder.SetTableName( qualifiedTableName )
.AddColumn( base.IdentifierColumnNames[ 0 ] );
// add the parameters to use in the WHERE clause
builder.SetIdentityColumn( base.IdentifierColumnNames, IdentifierType );
if( IsVersioned )
{
builder.SetVersionColumn( new string[ ] {VersionColumnName}, VersionType );
}
sqlBuilder = new SqlStringBuilder( builder.ToSqlString() );
}
else
{
sqlBuilder = new SqlStringBuilder( sqlString );
}
// add any special text that is contained in the forUpdateFragment
if( forUpdateFragment != null )
{
sqlBuilder.Add( forUpdateFragment );
}
return sqlBuilder.ToSqlString();
}
public void SimpleSelectStringSqlTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory( );
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlSimpleSelectBuilder select = new SqlSimpleSelectBuilder(factoryImpl);
select.SetTableName("test_simple_select_builder");
select.AddColumn("column_no_alias");
select.AddColumn("aliased_column", "aliased_column_alias");
select.AddColumns(new string[]{"column1_no_alias", "column2_no_alias"});
select.AddColumns(new string[]{"column1_with_alias", "column2_with_alias"}, new string[] {"c1_alias", "c2_alias"});
select.SetIdentityColumn(new string[]{"identity_column"}, NHibernateUtil.Int64);
select.SetVersionColumn(new string[]{"version_column"}, (IVersionType)NHibernateUtil.Int32);
select.AddWhereFragment(new string[]{"where_frag_column"}, NHibernateUtil.Int32, " = ");
SqlString sqlString = select.ToSqlString();
Parameter[] actualParams = new Parameter[3];
int numOfParameters = 0;
string expectedSql = new StringBuilder().Append("SELECT ")
.Append("column_no_alias, ")
.Append("aliased_column AS aliased_column_alias, ")
.Append("column1_no_alias, ")
.Append("column2_no_alias, ")
.Append("column1_with_alias AS c1_alias, ")
.Append("column2_with_alias AS c2_alias ")
.Append("FROM test_simple_select_builder ")
.Append("WHERE identity_column = :identity_column AND version_column = :version_column")
.Append(" AND where_frag_column = :where_frag_column")
.ToString();
Assert.AreEqual(expectedSql , sqlString.ToString(), "SQL String");
foreach(object part in sqlString.SqlParts)
{
if(part is Parameter)
{
actualParams[numOfParameters] = (Parameter)part;
numOfParameters++;
}
}
Assert.AreEqual(3, numOfParameters, "3 parameters");
Parameter firstParam = new Parameter( "identity_column", new SqlTypes.Int64SqlType() );
Parameter secondParam = new Parameter( "version_column", new SqlTypes.Int32SqlType() );
Parameter thirdParam = new Parameter( "where_frag_column", new SqlTypes.Int32SqlType() );
Assert.AreEqual(firstParam.SqlType.DbType, actualParams[0].SqlType.DbType, "First Parameter Type");
Assert.AreEqual(firstParam.Name, actualParams[0].Name, "First Parameter Name");
Assert.AreEqual(secondParam.SqlType.DbType, actualParams[1].SqlType.DbType, "Second Parameter Type");
Assert.AreEqual(secondParam.Name, actualParams[1].Name, "Second Parameter Name");
Assert.AreEqual(thirdParam.SqlType.DbType, actualParams[2].SqlType.DbType, "Third Parameter Type");
Assert.AreEqual(thirdParam.Name, actualParams[2].Name, "Third Parameter Name");
}