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>
/// Generate the SQL that selects the version number by id
/// </summary>
protected SqlString GenerateSelectVersionString()
{
SqlSimpleSelectBuilder builder = new SqlSimpleSelectBuilder(Factory.Dialect, factory)
.SetTableName(VersionedTableName);
if (IsVersioned)
builder.AddColumn(versionColumnName);
else
builder.AddColumns(rootTableKeyColumnNames);
if (Factory.Settings.IsCommentsEnabled)
{
builder.SetComment("get version " + EntityName);
}
return builder.AddWhereFragment(rootTableKeyColumnNames, IdentifierType, " = ").ToSqlString();
}
/// <summary> Generate the SQL that selects a row by id</summary>
protected internal virtual SqlString GenerateSelectString(LockMode lockMode)
{
SqlSimpleSelectBuilder select = new SqlSimpleSelectBuilder(Factory.Dialect, Factory)
.SetLockMode(lockMode)
.SetTableName(TableName)
.AddColumns(IdentifierColumnNames)
.AddColumns(SubclassColumnClosure, SubclassColumnAliasClosure, SubclassColumnLazyiness)
.AddColumns(SubclassFormulaClosure, SubclassFormulaAliasClosure, SubclassFormulaLazyiness);
//TODO: include the rowids!!!!
if (HasSubclasses)
{
if (IsDiscriminatorFormula)
{
select.AddColumn(DiscriminatorFormula, DiscriminatorAlias);
}
else
{
select.AddColumn(DiscriminatorColumnName, DiscriminatorAlias);
}
}
if (Factory.Settings.IsCommentsEnabled)
{
select.SetComment("load " + EntityName);
}
return select.AddWhereFragment(IdentifierColumnNames, IdentifierType, "=").ToSqlString();
}
/// <summary>
/// Generate the SQL that selects the version number by id
/// </summary>
/// <returns></returns>
protected SqlString GenerateSelectVersionString()
{
SqlSimpleSelectBuilder builder = new SqlSimpleSelectBuilder(factory);
builder.SetTableName(VersionedTableName);
if (IsVersioned)
{
builder.AddColumn(versionColumnName);
}
else
{
builder.AddColumns(rootTableKeyColumnNames);
}
builder.AddWhereFragment(rootTableKeyColumnNames, IdentifierType, " = ");
return builder.ToSqlString();
}
/// <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;
}
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");
}