hibernate的方言问题

今天遇到这样的问题：

The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead

在网上搜集了一下资料，原来是方言的问题。遂转载过来，方便以后继续学习。

Hibernate 不同数据库的连接及SQL方言

<!–MySql 驱动程序 eg. mysql-connector-java-5.0.4-bin.jar–>

<property name=”dialect”>org.hibernate.dialect.MySQLDialect</property>

<property name=”connection.driver_class”>com.mysql.jdbc.Driver</property>

<!– JDBC URL –>

<property name=”connection.url”>jdbc:mysql://localhost/dbname?characterEncoding=gb2312</property>

<!– 数据库用户名–>

<!– 数据库密码–>

<!–Sql Server 驱动程序 eg. jtds-1.2.jar–>

<property name=”dialect”>org.hibernate.dialect.SQLServerDialect</property>

<property name=”connection.driver_class”>net.sourceforge.jtds.jdbc.Driver</property>

<!– JDBC URL –>

<property name=”connection.url”>jdbc:jtds:sqlserver://localhost:1433;DatabaseName=dbname</property>

<!– 数据库用户名–>

<!– 数据库密码–>

<!–Oracle 驱动程序 ojdbc14.jar–>

<property name=”dialect”>org.hibernate.dialect.OracleDialect</property>

<property name=”connection.driver_class”>oracle.jdbc.driver.OracleDriver</property>

<!– JDBC URL –>

<property name=”connection.url”>jdbc:oracle:thin:@localhost:1521:dbname</property>

<!– 数据库用户名–>

<!– 数据库密码–>

原文网址：http://www.fengfly.com/plus/view-168177-1.html

如果出现如下错误，则可能是Hibernate SQL方言 (hibernate.dialect)设置不正确。

Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]’last_insert_id’ 不是可以识别的函数名。

RDBMS 方言

DB2 org.hibernate.dialect.DB2Dialect

DB2 AS/400 org.hibernate.dialect.DB2400Dialect

DB2 OS390 org.hibernate.dialect.DB2390Dialect

PostgreSQL org.hibernate.dialect.PostgreSQLDialect

MySQL org.hibernate.dialect.MySQLDialect

MySQL with InnoDB org.hibernate.dialect.MySQLInnoDBDialect

MySQL with MyISAM org.hibernate.dialect.MySQLMyISAMDialect

Oracle (any version) org.hibernate.dialect.OracleDialect

Oracle 9i/10g org.hibernate.dialect.Oracle9Dialect

Sybase org.hibernate.dialect.SybaseDialect

Sybase Anywhere org.hibernate.dialect.SybaseAnywhereDialect

Microsoft SQL Server org.hibernate.dialect.SQLServerDialect

SAP DB org.hibernate.dialect.SAPDBDialect

Informix org.hibernate.dialect.InformixDialect

HypersonicSQL org.hibernate.dialect.HSQLDialect

Ingres org.hibernate.dialect.IngresDialect

Progress org.hibernate.dialect.ProgressDialect

Mckoi SQL org.hibernate.dialect.MckoiDialect

Interbase org.hibernate.dialect.InterbaseDialect

Pointbase org.hibernate.dialect.PointbaseDialect

FrontBase org.hibernate.dialect.FrontbaseDialect

Firebird org.hibernate.dialect.FirebirdDialect

原文网址：http://www.fengfly.com/plus/view-168177-1.html

有时候，hibernate用原生SQL 查询，native SQL 的时候，会出现

org.hibernate.MappingException: No Dialect mapping for JDBC type: -1 ，-16，等等的错误

type 类型代码可以看 java.sql.Types 中的定义，看源码就可以了

那是因为 Dialect 未定义，重写类，把未定义的Dialect 注册一下即可，

并且在 hibernate.cfg.xml中加入

Xml代码

org.hibernate.dialect.OracleCustomDialect

</property>

org.hibernate.dialect.OracleCustomDialect

</property>

Java代码

import java.sql.Types;

import org.hibernate.Hibernate;

public class OracleCustomDialect extends org.hibernate.dialect.Oracle10gDialect {

public OracleCustomDialect() {

super();

registerHibernateType(Types.FLOAT, Hibernate.FLOAT.getName());

}

import java.sql.Types;

import org.hibernate.Hibernate;

public class OracleCustomDialect extends org.hibernate.dialect.Oracle10gDialect {

public OracleCustomDialect() {

super();

registerHibernateType(Types.FLOAT, Hibernate.FLOAT.getName());

}

这是自己解决的折中办法，最好还是遵循标准类型

在hibernate中，Oracle9Dialect 方言定义有以下代码：

Java代码

public class Oracle9Dialect extends Dialect {

private static final Logger log = LoggerFactory.getLogger( Oracle9Dialect.class );

public Oracle9Dialect() {

super();

log.warn( “The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead” );

registerColumnType( Types.BIT, “number(1,0)” );

registerColumnType( Types.BIGINT, “number(19,0)” );

registerColumnType( Types.SMALLINT, “number(5,0)” );

registerColumnType( Types.TINYINT, “number(3,0)” );

registerColumnType( Types.INTEGER, “number(10,0)” );

registerColumnType( Types.CHAR, “char(1 char)” );

registerColumnType( Types.VARCHAR, 4000, “varchar2($l char)” );

registerColumnType( Types.VARCHAR, “long” );

registerColumnType( Types.FLOAT, “float” );

registerColumnType( Types.DOUBLE, “double precision” );

registerColumnType( Types.DATE, “date” );

registerColumnType( Types.TIME, “date” );

registerColumnType( Types.TIMESTAMP, “timestamp” );

registerColumnType( Types.VARBINARY, 2000, “raw($l)” );

registerColumnType( Types.VARBINARY, “long raw” );

registerColumnType( Types.NUMERIC, “number($p,$s)” );

registerColumnType( Types.DECIMAL, “number($p,$s)” );

registerColumnType( Types.BLOB, “blob” );

registerColumnType( Types.CLOB, “clob” );

……

public class Oracle9Dialect extends Dialect {

private static final Logger log = LoggerFactory.getLogger( Oracle9Dialect.class );

public Oracle9Dialect() {

super();

log.warn( “The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead” );

registerColumnType( Types.BIT, “number(1,0)” );

registerColumnType( Types.BIGINT, “number(19,0)” );

registerColumnType( Types.SMALLINT, “number(5,0)” );

registerColumnType( Types.TINYINT, “number(3,0)” );

registerColumnType( Types.INTEGER, “number(10,0)” );

registerColumnType( Types.CHAR, “char(1 char)” );

registerColumnType( Types.VARCHAR, 4000, “varchar2($l char)” );

registerColumnType( Types.VARCHAR, “long” );

registerColumnType( Types.FLOAT, “float” );

registerColumnType( Types.DOUBLE, “double precision” );

registerColumnType( Types.DATE, “date” );

registerColumnType( Types.TIME, “date” );

registerColumnType( Types.TIMESTAMP, “timestamp” );

registerColumnType( Types.VARBINARY, 2000, “raw($l)” );

registerColumnType( Types.VARBINARY, “long raw” );

registerColumnType( Types.NUMERIC, “number($p,$s)” );

registerColumnType( Types.DECIMAL, “number($p,$s)” );

registerColumnType( Types.BLOB, “blob” );

registerColumnType( Types.CLOB, “clob” );

……

而hibernate 3.3.2中带的Oracle10gDialect方言代码如下，是继承了 Oracle9Dialect

Java代码

public class Oracle10gDialect extends Oracle9iDialect {

public Oracle10gDialect() {

super();

}

public JoinFragment createOuterJoinFragment() {

return new ANSIJoinFragment();

}

public class Oracle10gDialect extends Oracle9iDialect {

public Oracle10gDialect() {

super();

}

public JoinFragment createOuterJoinFragment() {

return new ANSIJoinFragment();

}

同时，在public abstract class Dialect 类中，定义了以下registerHibernateType，所有的数据库类型的Dialect都是 extends Dialect

Java代码

protected Dialect() {

log.info( “Using dialect: ” + this );

sqlFunctions.putAll( STANDARD_AGGREGATE_FUNCTIONS );

// standard sql92 functions (can be overridden by subclasses)

registerFunction( “substring”, new SQLFunctionTemplate( Hibernate.STRING, “substring(?1, ?2, ?3)” ) );

registerFunction( “locate”, new SQLFunctionTemplate( Hibernate.INTEGER, “locate(?1, ?2, ?3)” ) );

registerFunction( “trim”, new SQLFunctionTemplate( Hibernate.STRING, “trim(?1 ?2 ?3 ?4)” ) );

registerFunction( “length”, new StandardSQLFunction( “length”, Hibernate.INTEGER ) );

registerFunction( “bit_length”, new StandardSQLFunction( “bit_length”, Hibernate.INTEGER ) );

registerFunction( “coalesce”, new StandardSQLFunction( “coalesce” ) );

registerFunction( “nullif”, new StandardSQLFunction( “nullif” ) );

registerFunction( “abs”, new StandardSQLFunction( “abs” ) );

registerFunction( “mod”, new StandardSQLFunction( “mod”, Hibernate.INTEGER) );

registerFunction( “sqrt”, new StandardSQLFunction( “sqrt”, Hibernate.DOUBLE) );

registerFunction( “upper”, new StandardSQLFunction(“upper”) );

registerFunction( “lower”, new StandardSQLFunction(“lower”) );

registerFunction( “cast”, new CastFunction() );

registerFunction( “extract”, new SQLFunctionTemplate(Hibernate.INTEGER, “extract(?1 ?2 ?3)”) );

//map second/minute/hour/day/month/year to ANSI extract(), override on subclasses

registerFunction( “second”, new SQLFunctionTemplate(Hibernate.INTEGER, “extract(second from ?1)”) );

registerFunction( “minute”, new SQLFunctionTemplate(Hibernate.INTEGER, “extract(minute from ?1)”) );

registerFunction( “hour”, new SQLFunctionTemplate(Hibernate.INTEGER, “extract(hour from ?1)”) );

registerFunction( “day”, new SQLFunctionTemplate(Hibernate.INTEGER, “extract(day from ?1)”) );

registerFunction( “month”, new SQLFunctionTemplate(Hibernate.INTEGER, “extract(month from ?1)”) );

registerFunction( “year”, new SQLFunctionTemplate(Hibernate.INTEGER, “extract(year from ?1)”) );

registerFunction( “str”, new SQLFunctionTemplate(Hibernate.STRING, “cast(?1 as char)”) );

// register hibernate types for default use in scalar sqlquery type auto detection

registerHibernateType( Types.BIGINT, Hibernate.BIG_INTEGER.getName() );

registerHibernateType( Types.BINARY, Hibernate.BINARY.getName() );

registerHibernateType( Types.BIT, Hibernate.BOOLEAN.getName() );

registerHibernateType( Types.CHAR, Hibernate.CHARACTER.getName() );

registerHibernateType( Types.DATE, Hibernate.DATE.getName() );

registerHibernateType( Types.DOUBLE, Hibernate.DOUBLE.getName() );

registerHibernateType( Types.FLOAT, Hibernate.FLOAT.getName() );

registerHibernateType( Types.INTEGER, Hibernate.INTEGER.getName() );

registerHibernateType( Types.SMALLINT, Hibernate.SHORT.getName() );

registerHibernateType( Types.TINYINT, Hibernate.BYTE.getName() );

registerHibernateType( Types.TIME, Hibernate.TIME.getName() );

registerHibernateType( Types.TIMESTAMP, Hibernate.TIMESTAMP.getName() );

registerHibernateType( Types.VARCHAR, Hibernate.STRING.getName() );

registerHibernateType( Types.VARBINARY, Hibernate.BINARY.getName() );

registerHibernateType( Types.NUMERIC, Hibernate.BIG_DECIMAL.getName() );

registerHibernateType( Types.DECIMAL, Hibernate.BIG_DECIMAL.getName() );

registerHibernateType( Types.BLOB, Hibernate.BLOB.getName() );

registerHibernateType( Types.CLOB, Hibernate.CLOB.getName() );

registerHibernateType( Types.REAL, Hibernate.FLOAT.getName() );