Peguei um dialeto padrão do Hibernate e fui modificando o que precisava, daí ficou fácil. Talvez ele pudesse ser mais simples, mas não domino o suficiente pra modificar mais.
No Hibernate 3.2 dá um monte de warnings, mas funciona. Na versão 3.3 em diante, não dá warnings, mas de vez em quando os campos string dão problemas se o SO for 64 bits.
Outra coisa, não pode habilitar os comentários no hibernate.cfg, senão não funciona, ele se confunde na hora de executar o SQL.
Tirando isso, dá pra trabalhar.
package br.com.viena.dao;
import java.sql.Types;
import org.hibernate.Hibernate;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.StandardSQLFunction;
//import org.hibernate.sql.JoinFragment;
import org.hibernate.tool.hbm2x.StringUtils;
public class MSAccessDialect extends Dialect {
public MSAccessDialect() {
super();
registerColumnType(Types.BIT, "BIT");
registerColumnType(Types.BIGINT, "INTEGER");
registerColumnType(Types.SMALLINT, "SMALLINT");
registerColumnType(Types.TINYINT, "BYTE");
registerColumnType(Types.INTEGER, "INTEGER");
registerColumnType(Types.CHAR, "VARCHAR(1)");
registerColumnType(Types.VARCHAR, "VARCHAR($l)");
registerColumnType(Types.FLOAT, "DOUBLE");
registerColumnType(Types.DOUBLE, "DOUBLE");
registerColumnType(Types.DATE, "DATE");
registerColumnType(Types.TIME, "TIME");
registerColumnType(Types.TIMESTAMP, "DATETIME");
registerColumnType(Types.VARBINARY, "VARBINARY($l)");
registerColumnType(Types.NUMERIC, "NUMERIC");
registerColumnType(Types.BLOB, "memo"); // so that's 65,535 characters in Access
registerColumnType(Types.CLOB, "memo"); // so that's 2.14 GB in Access
registerFunction("asc", new StandardSQLFunction("asc", Hibernate.INTEGER)); // SQL Server: ascii(x)
registerFunction("char", new StandardSQLFunction("char", Hibernate.CHARACTER)); // SQL Server: chr(x)
registerFunction("len", new StandardSQLFunction("len", Hibernate.LONG)); // SQL Server: datalength(x)
registerFunction("lcase$", new StandardSQLFunction("lcase$")); // SQL Server: lower(x)
registerFunction("ucase$", new StandardSQLFunction("ucase$")); // SQL Server: upper(x)
registerFunction("str$", new StandardSQLFunction("str$", Hibernate.STRING)); // SQL Server: str(x)
registerFunction("ltrim$", new StandardSQLFunction("ltrim$")); // SQL Server: ltrim(x)
registerFunction("rtrim$", new StandardSQLFunction("rtrim$")); // SQL Server: rtrim(x)
registerFunction("right$", new StandardSQLFunction("right$")); // SQL Server: right(x,y)
registerFunction("mid$", new StandardSQLFunction("mid$")); // SQL Server: substring(x,y,z)
registerFunction("getdate", new NoArgSQLFunction("getdate", Hibernate.TIMESTAMP));
registerFunction("getutcdate", new NoArgSQLFunction("getutcdate", Hibernate.TIMESTAMP));
registerFunction("day", new StandardSQLFunction("day", Hibernate.INTEGER));
registerFunction("month", new StandardSQLFunction("month", Hibernate.INTEGER));
registerFunction("year", new StandardSQLFunction("year", Hibernate.INTEGER));
registerFunction("datename", new StandardSQLFunction("datename", Hibernate.STRING));
registerFunction("abs", new StandardSQLFunction("abs"));
registerFunction("sign", new StandardSQLFunction("sign", Hibernate.INTEGER));
registerFunction("acos", new StandardSQLFunction("acos", Hibernate.DOUBLE));
registerFunction("asin", new StandardSQLFunction("asin", Hibernate.DOUBLE));
registerFunction("atan", new StandardSQLFunction("atan", Hibernate.DOUBLE));
registerFunction("cos", new StandardSQLFunction("cos", Hibernate.DOUBLE));
registerFunction("cot", new StandardSQLFunction("cot", Hibernate.DOUBLE));
registerFunction("exp", new StandardSQLFunction("exp", Hibernate.DOUBLE));
registerFunction("log", new StandardSQLFunction("log", Hibernate.DOUBLE));
registerFunction("log10", new StandardSQLFunction("log10", Hibernate.DOUBLE));
registerFunction("sin", new StandardSQLFunction("sin", Hibernate.DOUBLE));
registerFunction("sqrt", new StandardSQLFunction("sqrt", Hibernate.DOUBLE));
registerFunction("tan", new StandardSQLFunction("tan", Hibernate.DOUBLE));
registerFunction("pi", new NoArgSQLFunction("pi", Hibernate.DOUBLE));
registerFunction("square", new StandardSQLFunction("square"));
registerFunction("rand", new StandardSQLFunction("rand", Hibernate.FLOAT));
registerFunction("radians", new StandardSQLFunction("radians", Hibernate.DOUBLE));
registerFunction("degrees", new StandardSQLFunction("degrees", Hibernate.DOUBLE));
registerFunction("round", new StandardSQLFunction("round"));
registerFunction("ceiling", new StandardSQLFunction("ceiling"));
registerFunction("floor", new StandardSQLFunction("floor"));
getDefaultProperties().setProperty(Environment.USE_GET_GENERATED_KEYS, "false");
getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE,
"0");
}
public boolean supportsForUpdate() {
return false;
}
@Override
public boolean supportsIdentityColumns() {
return true;
}
@Override
public boolean supportsInsertSelectIdentity() {
return false;
}
@Override
public boolean supportsParametersInInsertSelect() {
return false;
}
@Override
public String getIdentitySelectString() {
return "select @@IDENTITY";
}
// appendIdentitySelectToInsert() -- can't do this as can only have one statement at a time, I think
@Override
public String getIdentityColumnString() {
//return "counter";
return "not null auto_number";
}
@Override
public boolean hasDataTypeInIdentityColumn() {
return false;
}
@Override
public boolean supportsIfExistsBeforeTableName() {
return false;
}
@Override
public String getLowercaseFunction() {
return "lcase$";
}
@Override
public boolean hasAlterTable() {
return true;
}
@Override
public boolean dropConstraints() {
return false;
}
@Override
public String getAddPrimaryKeyConstraintString(String constraintName) {
return " constraint " + constraintName + " primary key ";
}
@Override
public String getAddForeignKeyConstraintString(
String constraintName,
String[] foreignKey,
String referencedTable,
String[] primaryKey,
boolean referencesPrimaryKey) {
return super.getAddForeignKeyConstraintString(
constraintName, foreignKey, referencedTable, primaryKey, true);
}
@Override
public boolean supportsCascadeDelete() {
return false;
}
@Override
public String transformSelectString(String select) {
// O Join do Access é diferente, exige uma série de parênteses
int matches = StringUtils.countMatches(select, " outer join ")
+ StringUtils.countMatches(select, " inner join ");
if (matches > 0) {
select = StringUtils.replace(select, " left outer join ", ") left join ");
select = StringUtils.replace(select, " right outer join ", ") right join ");
select = StringUtils.replace(select, " inner join ", ") inner join ");
select = StringUtils.replace(select, "from ", "from " + StringUtils.repeat("(", matches));
}
return select;
}