java - Hibernate + SQLite 使用 hibernate.hbm2ddl.auto = auto 更新表

Question

我想在我的项目中支持 hibernate.hbm2ddl.auto = auto 。我使用 Hibernate + SQLite 和 SQLiteDialect:

package app.sqlite;

import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.JDBCException;
import org.hibernate.ScrollMode;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.AbstractAnsiTrimEmulationFunction;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.LimitHelper;
import org.hibernate.dialect.unique.DefaultUniqueDelegate;
import org.hibernate.dialect.unique.UniqueDelegate;
import org.hibernate.engine.spi.RowSelection;
import org.hibernate.exception.DataException;
import org.hibernate.exception.JDBCConnectionException;
import org.hibernate.exception.LockAcquisitionException;
import org.hibernate.exception.spi.SQLExceptionConversionDelegate;
import org.hibernate.exception.spi.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.spi.ViolatedConstraintNameExtracter;
import org.hibernate.internal.util.JdbcExceptionHelper;
import org.hibernate.mapping.Column;
import org.hibernate.type.StandardBasicTypes;

public class SQLiteDialect extends Dialect {
  private final UniqueDelegate uniqueDelegate;

  public SQLiteDialect() {
    registerColumnType(Types.BIT, "boolean");
    //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($p, $s)");
    registerColumnType(Types.DECIMAL, "decimal");
    registerColumnType(Types.CHAR, "char");
    registerColumnType(Types.VARCHAR, "varchar($l)");
    registerColumnType(Types.LONGVARCHAR, "longvarchar");
    //registerColumnType(Types.DATE, "date");
    //registerColumnType(Types.TIME, "time");
    registerColumnType(Types.TIMESTAMP, "datetime");
    registerColumnType(Types.BINARY, "blob");
    registerColumnType(Types.VARBINARY, "blob");
    registerColumnType(Types.LONGVARBINARY, "blob");
    //registerColumnType(Types.BLOB, "blob");
    //registerColumnType(Types.CLOB, "clob");
    //registerColumnType(Types.BOOLEAN, "boolean");

    registerFunction( "concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", "") );
    registerFunction( "mod", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 % ?2" ) );
    registerFunction( "quote", new StandardSQLFunction("quote", StandardBasicTypes.STRING) );
    registerFunction( "random", new NoArgSQLFunction("random", StandardBasicTypes.INTEGER) );
    registerFunction( "round", new StandardSQLFunction("round") );
    registerFunction( "substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING) );
    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)");
        }
    } );
    uniqueDelegate = new SQLiteUniqueDelegate(this);
  }

  // database type mapping support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  @Override
  public String getCastTypeName(int code) {
    return super.getCastTypeName(code); // FIXME
  }

  // IDENTITY support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

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

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

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

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

  @Override
  public String getIdentitySelectString(String table, String column, int type) {
    return "select last_insert_rowid()";
  }

  // limit/offset support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  private static final AbstractLimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
    @Override
    public String processSql(String sql, RowSelection selection) {
      final boolean hasOffset = LimitHelper.hasFirstRow( selection );
      return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
    }

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

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

  @Override
  public LimitHandler getLimitHandler() {
    return LIMIT_HANDLER;
  }

  // lock acquisition support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  @Override
  public boolean supportsLockTimeouts() {
    return false; // may be http://sqlite.org/c3ref/db_mutex.html ?
  }

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

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

  /*
  @Override
  public boolean dropTemporaryTableAfterUse() {
    return true; // temporary tables are only dropped when the connection is closed. If the connection is pooled...
  }
  */

  // current timestamp support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

  public boolean isCurrentTimestampSelectStringCallable() {
    return false;
  }

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

  // SQLException support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  private static final int SQLITE_BUSY = 5;
  private static final int SQLITE_LOCKED = 6;
  private static final int SQLITE_IOERR = 10;
  private static final int SQLITE_CORRUPT = 11;
  private static final int SQLITE_NOTFOUND = 12;
  private static final int SQLITE_FULL = 13;
  private static final int SQLITE_CANTOPEN = 14;
  private static final int SQLITE_PROTOCOL = 15;
  private static final int SQLITE_TOOBIG = 18;
  private static final int SQLITE_CONSTRAINT = 19;
  private static final int SQLITE_MISMATCH = 20;
  private static final int SQLITE_NOTADB = 26;

  @Override
  public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
    return new SQLExceptionConversionDelegate() {
      @Override
      public JDBCException convert(SQLException sqlException, String message, String sql) {
        final int errorCode = JdbcExceptionHelper.extractErrorCode(sqlException);
        if (errorCode == SQLITE_TOOBIG || errorCode == SQLITE_MISMATCH) {
          return new DataException(message, sqlException, sql);
        } else if (errorCode == SQLITE_BUSY || errorCode == SQLITE_LOCKED) {
          return new LockAcquisitionException(message, sqlException, sql);
        } else if ((errorCode >= SQLITE_IOERR && errorCode <= SQLITE_PROTOCOL) || errorCode == SQLITE_NOTADB) {
          return new JDBCConnectionException(message, sqlException, sql);
        }

        // returning null allows other delegates to operate
        return null;
      }
    };
  }

  public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
    return EXTRACTER;
  }

  private static final ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {
    @Override
    protected String doExtractConstraintName(SQLException sqle) throws NumberFormatException {
      final int errorCode = JdbcExceptionHelper.extractErrorCode(sqle);
      if (errorCode == SQLITE_CONSTRAINT) {
        return extractUsingTemplate("constraint ", " failed", sqle.getMessage());
      }
      return null;
    }
  };

  // union subclass support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

  // DDL support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

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

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

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

  /*
  public String getAddColumnString() {
    return "add column";
  }
  */

  @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 supportsCommentOn() {
    return true;
  }

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

  /* not case insensitive for unicode characters by default (ICU extension needed)
  public boolean supportsCaseInsensitiveLike() {
    return true;
  }
  */

  @Override
  public boolean doesReadCommittedCauseWritersToBlockReaders() {
    return true; // TODO Validate (WAL mode...)
  }

  public boolean doesRepeatableReadCauseReadersToBlockWriters() {
    return true;
  }

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

  public int getInExpressionCountLimit() {
    return 1000; // Compile/runtime time option: http://sqlite.org/limits.html#max_variable_number
  }

  @Override
  public UniqueDelegate getUniqueDelegate() {
    return uniqueDelegate;
  }
  private static class SQLiteUniqueDelegate extends DefaultUniqueDelegate {
    public SQLiteUniqueDelegate(Dialect dialect) {
      super(dialect);
    }
    @Override
    public String getColumnDefinitionUniquenessFragment(Column column) {
      return " unique";
    }
  }

  @Override
  public String getSelectGUIDString() {
    return "select hex(randomblob(16))";
  }

  @Override
  public ScrollMode defaultScrollMode() {
    return ScrollMode.FORWARD_ONLY;
  }
}

和错误:

Caused by: java.lang.UnsupportedOperationException: No add column syntax supported by app.sqlite.SQLiteDialect
at org.hibernate.dialect.Dialect.getAddColumnString(Dialect.java:2148)
at org.hibernate.mapping.Table.sqlAlterStrings(Table.java:449)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.migrateTable(SchemaMigratorImpl.java:254)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigrationToTargets(SchemaMigratorImpl.java:170)
at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:60)
at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:133)
at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:101)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:470)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:444)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
at app.sqlite.MailDBConnector.connect(MailDBConnector.java:54)

当我取消注释时:

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

错误是:

org.hibernate.tool.schema.spi.SchemaManagementException: Unable to execute schema management to JDBC target [alter table mailMessage add column flag integer]

编辑:
我仍然解决这个问题，我需要在 org.hibernate.Dialect 中实现 getAddColumnString 方法以使其与 SQLite 数据库一起使用。MySQL 方言的默认实现是:

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

Answer 1

可悲的是，Hibernate 论坛的人告诉我 Hibernate 不支持 SQLite，有一些未定义的计划将来支持它，所以我认为如果不深入挖掘 Hibernate 代码，就没有简单的方法。作为与 hibernate.hbm2ddl.auto=update 一起使用的 SQLite 的替代方案，它可以嵌入 PostgreSQL