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"); }