为hibernate添加数据库方言函数

在sessionFactory中配置hibernate.dialect属性，以mysql为例，class设置为自定义的类，

<bean id="chartSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="chartDataSource" />
		<property name="mappingLocations">
			<list>
	            <value>classpath*:com/landray/kmss/hrt/chart/model/*.hbm.xml</value>
				<value>classpath:com/landray/kmss/component/dbop/model/CompDbcp.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigPersonalInfo.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigTrainServer.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigTrainMain.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigTechnology.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigPolitics.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigFunction.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigEducation.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/config/model/HrtConfigNation.hbm.xml</value>
				<value>classpath:com/landray/kmss/sys/organization/model/SysOrgPost.hbm.xml</value>
				<value>classpath:com/landray/kmss/sys/organization/model/SysOrgGroupCate.hbm.xml</value>
				<value>classpath:com/landray/kmss/sys/organization/model/SysOrgGroup.hbm.xml</value>
				<value>classpath:com/landray/kmss/sys/organization/model/SysOrgElement.hbm.xml</value>
				<value>classpath:com/landray/kmss/sys/organization/model/SysOrgPerson.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/course/scorm/model/HrtStudyCourse.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/course/scorm/model/HrtStudyCourseType.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/course/scorm/model/HrtCourseProperties.hbm.xml</value>
				<value>classpath:com/landray/kmss/hrt/course/scorm/model/HrtStudyStudentCourse.hbm.xml</value>
			</list>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">com.landray.kmss.util.MySQLLocalDialect</prop>
				<prop key="hibernate.show_sql">${chartDB.hibernate.show_sql}</prop>
				<prop key="hibernate.format_sql">${chartDB.hibernate.format_sql}</prop>
				<prop key="hibernate.jdbc.fetch_size">${chartDB.hibernate.jdbc.fetch_size}</prop>
				<prop key="hibernate.jdbc.batch_size">${chartDB.hibernate.jdbc.batch_size}</prop>
				<prop key="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</prop>
			</props>
		</property>
		<property name="lobHandler" ref="defaultLobHandler2"></property>
	</bean>

package com.landray.kmss.util;


import org.hibernate.dialect.function.SQLFunctionTemplate;

/**
 * @author Zhangyanan
 *	使mysql支持汉字排序
 */
public class MySQLLocalDialect extends org.hibernate.dialect.MySQL5Dialect {
	
	public MySQLLocalDialect(){ 
		super(); 
		registerFunction("convert", new SQLFunctionTemplate(org.hibernate.Hibernate.STRING, "convert(?1 using ?2)") );
		registerFunction("convert_gbk", new SQLFunctionTemplate(org.hibernate.Hibernate.STRING, "convert(?1 using gbk)") ); 
	} 

}

在hql中就可以使用 select u from User order by convert(personName,gbk)或者 select u from User order by convert_gbk(userName) 来实现按汉字拼音顺序排序了

评论

3 楼 javatozhang 2013-06-29  

zyn010101 写道

javatozhang 写道

能说说使用hibernate数据库方言的原理吗？

,这两天我忙完了去扒下源码把原理也贴上来

期待lz的持续更新，希望与lz交流学习！

2 楼 zyn010101 2013-06-29  

javatozhang 写道

能说说使用hibernate数据库方言的原理吗？

,这两天我忙完了去扒下源码把原理也贴上来

1 楼 javatozhang 2013-06-28  

能说说使用hibernate数据库方言的原理吗？