c# - 在执行 ISQLQuery.UniqueResult() 时，如何让 SQLite 返回正确的 DateTime？

Question

TLDR:

The following code is run in the different databases, Oracle: select sysdate from dual SQLite select datetime('now')

When doing Session.CreateSQLQuery(cmd).UniqueResult<DateTime>() the result is a DateTime when working against Oracle but a string when working against SQLite.

It feels like a bug in the SQLite driver and a hack to check the returned type and do a DateTime.Parse() if it is a string. I could do that but are there any ways to have NHibernate return the correct type?

我正在尝试从数据库中获取当前数据库时间。它在使用 Oracle 时工作正常，但当我尝试针对 SQLite 执行此操作时(在我的单元测试中)它会中断，因为返回的日期不是 DateTime 而是一个字符串。

我见过使用自定义 IUserType 的解决方案，但我看不出在这种情况下应该如何使用它。有什么建议吗？

using System;
using System.Collections.Generic;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Dialect.Function;

namespace My.Common.Types {

    public class MyNHibernateDialectException : Exception {
        public MyNHibernateDialectException(string message) : base(message) { }
    } 

    /// <summary>
    /// Define all custom functions here by name. It is important that when adding a new custom sql function, that function will work
    /// in all dialects supported.
    /// </summary>
    public static class MyDatabaseDialects {

        public enum Query {
            SysDate
        }

        /// <summary>
        ///  Dialect implementations will use this function to verify that they all implement the same functions.
        /// </summary>
        /// <param name="dialect"></param>
        public static void VerifyRegistrations(this NHibernate.Dialect.Dialect dialect) {
            // Verify that the required function are there
            foreach (var func in Enum.GetValues(typeof(Function))) {
                var enumName = func.ToString();
                if (!dialect.Functions.ContainsKey(enumName)) {
                    throw new MyNHibernateDialectException(
                        string.Format("The custom function '{0}' is not defined. Did you forget it in factory '{1}'?", enumName, dialect));
                }
            }
        }

    }

    /// <summary>
    /// An interface to reveal more advanced functionality that is database specific
    /// </summary>
    public interface IDialectExtensions {

        /// <summary>
        /// Fetch a query specfic for the current database.
        /// </summary>
        ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query);

        /// <summary>
        /// Fetch a parameterized query specfic for the current database.
        /// </summary>
        ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams);
    }

    /// <summary>
    /// Class to store database specific objects except functions (which are supported by NHibernate).
    /// </summary>
    class DialectExtension {

        private readonly Dictionary<MyDatabaseDialects.Query, string> queryDictionary = new Dictionary<MyDatabaseDialects.Query, string>();

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return this.GetQuery(session, query, null);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            var cmd = (queryParams == null) ? queryDictionary[query] : string.Format(queryDictionary[query], queryParams);
            return session.Session.CreateSQLQuery(cmd);
        }

        public void RegisterQuery(MyDatabaseDialects.Query query, string hqlString) {
            queryDictionary.Add(query, hqlString);
        }

        public void VerifyQueryRegistrations() {

            foreach (var query in Enum.GetValues(typeof(MyDatabaseDialects.Query))) {
                if (!queryDictionary.ContainsKey((MyDatabaseDialects.Query)query)) {
                    throw new MyNHibernateDialectException(string.Format("The custom query '{0}' is not defined.", query.ToString()));
                }
            }
        }
    }

    public class MyOracle10gDialect : NHibernate.Dialect.Oracle10gDialect, IDialectExtensions {

        private readonly DialectExtension dialectExtension = new DialectExtension();

        public MyOracle10gDialect() {

            #region Dialect extensions

            dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select sysdate from dual");

            dialectExtension.VerifyQueryRegistrations();

            #endregion Dialect extensions

        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return dialectExtension.GetQuery(session, query);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            return dialectExtension.GetQuery(session, query, queryParams);
        }
    }

    public class MySqliteDialect : NHibernate.Dialect.SQLiteDialect, IDialectExtensions {

        private readonly DialectExtension dialectExtension = new DialectExtension();

        public MySqliteDialect() {

            #region Dialect extensions

            dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select datetime('now')");

            dialectExtension.VerifyQueryRegistrations();

            #endregion Dialect extensions
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
            return dialectExtension.GetQuery(session, query);
        }

        public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
            return dialectExtension.GetQuery(session, query, queryParams);
        }

    }

}

我像这样使用上面的代码:

/// <summary>
/// Fetches a DialectExtensions object allowing us to have more advanced functionality that is database specific
/// </summary>
public static IDialectExtensions GetDialectExtensions(this IOperationContext operationContext) {
    return Session.GetSessionImplementation().Factory.Dialect as IDialectExtensions;
}

/// <summary>
/// Get the database time by executing a raw SQL statement.
/// </summary>
public static DateTime? GetDatabaseTime() {
    DateTime? result = null;
    try {
        result = GetDialectExtensions()
            .GetQuery(Session, MyDatabaseDialects.Query.SysDate)
            .UniqueResult<DateTime>();
    } catch {
        // SQLite will throw exception here as the result is returned as a string instead of a DateTime
    }
    return result;
}

Answer 1

When doing Session.CreateSQLQuery(cmd).UniqueResult() the result is a DateTime when working against Oracle but a string when working against SQLite.

听起来您可能需要进一步修改 SQLite 的休眠方言以返回相同的结果。确保为 SQLite 设置了默认的列映射类型，以匹配返回的 Oracle。我将时间戳列映射到日期时间。这是我的 SQLiteDialect，请记住这是一个 Java 版本，但应该仍然相似:

public class SQLiteDialect extends Dialect {

    public SQLiteDialect() {
        super();

        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        registerColumnType(Types.BIGINT, "bigint");
        registerColumnType(Types.FLOAT, "float");
        registerColumnType(Types.REAL, "real");
        registerColumnType(Types.DOUBLE, "double");
        registerColumnType(Types.NUMERIC, "numeric");
        registerColumnType(Types.DECIMAL, "decimal");
        registerColumnType(Types.CHAR, "char");
        registerColumnType(Types.VARCHAR, "varchar");
        registerColumnType(Types.LONGVARCHAR, "longvarchar");
        registerColumnType(Types.DATE, "date");
        registerColumnType(Types.TIME, "time");
        registerColumnType(Types.TIMESTAMP, "longvarchar");
        registerColumnType(Types.TIMESTAMP, "datetime");
        registerColumnType(Types.BINARY, "blob");
        registerColumnType(Types.VARBINARY, "blob");
        registerColumnType(Types.LONGVARBINARY, "blob");
        // registerColumnType(Types.NULL, "null");
        registerColumnType(Types.BLOB, "blob");
        registerColumnType(Types.CLOB, "clob");
        registerColumnType(Types.BOOLEAN, "integer");
        registerColumnType(Types.NULL, "null");
        registerHibernateType(Types.NULL, "null");

        registerFunction("concat", new VarArgsSQLFunction(IntegerType.INSTANCE,
            "", "||", ""));
        registerFunction("mod", new SQLFunctionTemplate(IntegerType.INSTANCE,
            "?1 % ?2"));
        registerFunction("substr", new StandardSQLFunction("substr",
            IntegerType.INSTANCE));
        registerFunction("substring", new StandardSQLFunction("substr",
            IntegerType.INSTANCE));

        registerFunction("replace", new StandardSQLFunction("replace",
            IntegerType.INSTANCE));

        registerFunction("current_time", new NoArgSQLFunction(
            "datetime('now')",
            CalendarType.INSTANCE, false));
        registerFunction("current_timestamp", new NoArgSQLFunction(
            "strftime('%s','now')*1000", CalendarType.INSTANCE, false));
        registerFunction("current_date", new NoArgSQLFunction(
            "strftime('%s','now')*1000",
            CalendarType.INSTANCE, false));

        registerFunction(
            "trunc",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%d-%m-%Y\", ?1/1000, 'unixepoch', 'localtime') as string)"));
        registerFunction(
            "second",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%S\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "minute",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%M\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "hour",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%H\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "day",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%d\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "month",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%m\", ?1/1000, 'unixepoch', 'localtime') as integer)"));
        registerFunction(
            "year",
            new SQLFunctionTemplate(IntegerType.INSTANCE,
                "cast (strftime(\"%Y\", ?1/1000, 'unixepoch', 'localtime') as integer)"));

        registerFunction("trim", new AbstractAnsiTrimEmulationFunction() {

            protected SQLFunction resolveBothSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?1)");
            }

            protected SQLFunction resolveBothSpaceTrimFromFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?2)");
            }

            protected SQLFunction resolveLeadingSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "ltrim(?1)");
            }

            protected SQLFunction resolveTrailingSpaceTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "rtrim(?1)");
            }

            protected SQLFunction resolveBothTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "trim(?1, ?2)");
            }

            protected SQLFunction resolveLeadingTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "ltrim(?1, ?2)");
            }

            protected SQLFunction resolveTrailingTrimFunction() {
                return new SQLFunctionTemplate(StandardBasicTypes.STRING,
                    "rtrim(?1, ?2)");
            }
        });
    }

    @Override
    public boolean supportsIdentityColumns() {
        return true;
    }

    /*
     * public boolean supportsInsertSelectIdentity() { return true; // As
     * specify in NHibernate dialect }
     */

    @Override
    public boolean hasDataTypeInIdentityColumn() {
        return false; // As specify in NHibernate dialect
    }

    /*
     * public String appendIdentitySelectToInsert(String insertString) { return
     * new StringBuffer(insertString.length()+30). // As specify in NHibernate
     * dialect append(insertString).
     * append("; ").append(getIdentitySelectString()). toString(); }
     */

    @Override
    public String getIdentityColumnString() {
        // return "integer primary key autoincrement";
        return "integer";
    }

    @Override
    public String getIdentitySelectString() {
        return "select last_insert_rowid()";
    }

    @Override
    public boolean supportsLimit() {
        return true;
    }

    // Added fix from bug,
    // http://code.google.com/p/hibernate-sqlite/issues/detail?id=1
    @Override
    public boolean bindLimitParametersInReverseOrder() {
        return true;
    }

    @Override
    public String getLimitString(String query, boolean hasOffset) {
        return new StringBuffer(query.length() + 20).append(query)
            .append(hasOffset ? " limit ? offset ?" : " limit ?").toString();
    }

    @Override
    public String getLimitString(final String s, final int min, final int max) {
        String limitString = super.getLimitString(s, min, max);

        return limitString;
    }

    @Override
    public boolean supportsTemporaryTables() {
        return true;
    }

    @Override
    public String getCreateTemporaryTableString() {
        return "create temporary table if not exists";
    }

    @Override
    public boolean dropTemporaryTableAfterUse() {
        return false;
    }

    @Override
    public boolean supportsCurrentTimestampSelection() {
        return true;
    }

    @Override
    public boolean isCurrentTimestampSelectStringCallable() {
        return false;
    }

    @Override
    public String getCurrentTimestampSelectString() {
        return "select current_timestamp";
    }

    @Override
    public boolean supportsUnionAll() {
        return true;
    }

    @Override
    public boolean hasAlterTable() {
        return false; // As specify in NHibernate dialect
    }

    @Override
    public boolean dropConstraints() {
        return false;
    }

    @Override
    public String getAddColumnString() {
        return "add column";
    }

    @Override
    public String getForUpdateString() {
        return "";
    }

    @Override
    public boolean supportsOuterJoinForUpdate() {
        return false;
    }

    @Override
    public String getDropForeignKeyString() {
        throw new UnsupportedOperationException(
            "No drop foreign key syntax supported by SQLiteDialect");
    }

    @Override
    public String
                    getAddForeignKeyConstraintString(String constraintName,
                                                     String[] foreignKey,
                                                     String referencedTable,
                                                     String[] primaryKey,
                                                     boolean referencesPrimaryKey) {
        throw new UnsupportedOperationException(
            "No add foreign key syntax supported by SQLiteDialect");
    }

    @Override
    public String getAddPrimaryKeyConstraintString(String constraintName) {
        throw new UnsupportedOperationException(
            "No add primary key syntax supported by SQLiteDialect");
    }

    @Override
    public boolean supportsIfExistsBeforeTableName() {
        return true;
    }

    @Override
    public boolean supportsIfExistsAfterTableName() {
        return false;
    }

    @Override
    public boolean supportsCascadeDelete() {
        return false;
    }
}

确保在构建 session 工厂之前设置“hibernate.dialect”属性:

private SessionFactory buildSessionFactory() throws Exception {
    StandardServiceRegistryBuilder serviceRegistryBuilder =
        new StandardServiceRegistryBuilder();

    org.hibernate.cfg.Configuration config = new Configuration();
    config.setProperty("hibernate.dialect", dialect);
    //Any additional config options, or just set this in your hibernate xml config...


    serviceRegistryBuilder.applySettings(config.getProperties());

    ServiceRegistry serviceRegistry = serviceRegistryBuilder.build();

    sessionFactory = config.buildSessionFactory(serviceRegistry);

    return sessionFactory;
}

这是一个 smaller version I found for .NET

这应该可以解决您的问题。如果没有，您可以通过为 ORM 映射设置设置时间类型来使用其他解决方法:

[Temporal(TemporalType.TIMESTAMP)]

显然，TemporalTypes 在 csharp 中被称为 CustomTypes。 answers for this question 之一提供了一个示例.

此外，您可能需要 account for timezones .