读书人
栏目▼

hibernate的方言有关问题

发布时间： 2012-11-04 10:42:42 作者： rapoo

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>

?

??<!-- 数据库用户名-->

??<property name="connection.username">root</property>

?

??<!-- 数据库密码-->

??<property name="connection.password">root</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>

?

??<!-- 数据库用户名-->

??<property name="connection.username">sa</property>

??<!-- 数据库密码-->

??<property name="connection.password"></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>

??<!-- 数据库用户名-->

??<property name="connection.username">test</property>

??<!-- 数据库密码-->

??<property name="connection.password">test</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代码

<property name="hibernate.dialect"> ??

?? ?org.hibernate.dialect.OracleCustomDialect ? ?

</property> ??

<property name="hibernate.dialect">

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() ); ??

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() );?

如果有特殊类型确实需要，只能自定义。

所以oracle的类型定义最好遵循以上的 java.sql.Types，

在hibernate.cfg.xml中使用 org.hibernate.dialect.OracleCustomDialect?

以防出现No Dialect mapping for JDBC type错误

其他的 数据库类型，SQL SERVER ?DB2 MYSQL 等等，都可以查看 hibernate的相关源码找出个所以然来

?

?

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

?

读书人网 >软件架构设计

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net