Oracle char类型问题在Hibernate中HQL查询 [英] Oracle char type issue in Hibernate HQL query
问题描述
我有Oracle表，其中包含char类型列。在我的Entity类中，我将oracle char类型映射到java字符串类型。
这是我的Entity类的代码。
@Entity
@Table（name =ORG）
public class Organization {
private String serviceName;
private String orgAcct;
//这里有一些其他属性...
@Column（name =ORG_ACCT，nullable = false，length = 16）
public String getOrgAcct （）{
return this.orgAcct;
}
public void setOrgAcct（String orgAcct）{
this.orgAcct = orgAcct;
}
@Column（name =SERVICE_NAME，nullable = true，length = 16）
public String getServiceName（）{
return this .serviceName;
}
public void setServiceName（String serviceName）{
this.serviceName = serviceName;
}
}
这里serviceName和orgAcct都是char类型变量在Oracle中
在我的DAO类中，我写了一个HQL查询来使用serviceName和orgAcct属性来获取Oranization实体对象。
@Repository
@Scope（singleton）//默认范围是单例
public class OrganizationDAOImpl implementsOrganizationDAO {
public OrganizationDAOImpl（）{
}
public组织findOrganizationByOrgAcctAndServiceName（String orgAcct，String serviceName）{
String hqlQuery =SELECT org FROM Organization org WHERE org.serviceName =：serName AND org.orgAcct =：orgAct;
Query query = getCurrentSession（）。createQuery（hqlQuery）
.setString（serName，serviceName）
.setString（orgAct，orgAcct）;
组织org = findObject（query）;
return org;
}
}
但是当我调用findOrganizationByOrgAcctAndServiceName（）获取组织对象为空（即HQL查询不检索Char类型数据）。
请帮助我解决这个问题。这里我不能更改Oracle类型char到Varchar2。我需要使用oracle char类型的变量。
@EngineerDollery我们修改了我的Entity类与columnDefinition， @Column 注释属性。
@Column（name =SERVICE_NAME，nullable = true，length = columnDefinition =CHAR）
public String getServiceName（）{
return this.serviceName;
}
但是我无法检索相应列的数据。 p>
我在columnDefinition属性中添加了列大小。
@Column SERVICE_NAME，nullable = true，length = 16，columnDefinition =CHAR（16）
我使用OraclePreparedStatement和Hibernate UserType接口解决了这个问题。
通过扩展org.hibernate.usertype.UserType接口创建了一个新的UserType类，并为nullSafeSet（），nullSafeGet（）方法提供了实现。
nullSafeSet（）方法，我们有第一个参数作为PreparedStatement，在方法内部我使用setFixedCHAR（）方法将PreparedStatement转换为OraclePreparedStatement对象并传递String值。
$ b b
这里是UserType impl类的完整代码。
package nc3.jws.persistence.userType;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.apache.commons.lang.StringUtils;
import org.hibernate.type.StringType;
import org.hibernate.usertype.UserType;
/ **
*
*基于www.hibernate.org/388.html
* /
public class OracleFixedLengthCharType implements UserType {
public OracleFixedLengthCharType（）{
System.out.println（OracleFixedLengthCharType constructor）;
}
public int [] sqlTypes（）{
return new int [] {Types.CHAR};
}
public Class< String> returnedClass（）{
return String.class;
}
public boolean equals（Object x，Object y）{
return（x == y）|| （x！= null& y！= null&&（x.equals（y）））;
}
@SuppressWarnings（deprecation）
public Object nullSafeGet（ResultSet inResultSet，String [] names，Object o）throws SQLException {
// String val =（String）Hibernate.STRING.nullSafeGet（inResultSet，names [0]）;
String val = StringType.INSTANCE.nullSafeGet（inResultSet，names [0]）;
//System.out.println(\"From nullSafeGet method value is+ val）;
return val == null？ null：StringUtils.trim（val）;
}
public void nullSafeSet（PreparedStatement inPreparedStatement，Object o，
int i）
throws SQLException {
String val =（String）o;
//从DBCP连接池PreparedStatement对象获取delegatingStmt对象。
org.apache.commons.dbcp.DelegatingStatement delgatingStmt =（org.apache.commons.dbcp.DelegatingStatement）inPreparedStatement;
//使用deletatingStatement对象获取OraclePreparedStatement对象。
oracle.jdbc.driver.OraclePreparedStatement oraclePreparedStmpt =（oracle.jdbc.driver.OraclePreparedStatement）delgatingStmt.getInnhestDelegate（）;
//通过传递字符串类型值调用setFixedCHAR方法。
oraclePreparedStmpt.setFixedCHAR（i，val）;
}
public Object deepCopy（Object o）{
if（o == null）{
return null;
}
return new String（（（String）o））;
}
public boolean isMutable（）{
return false;
}
public Object assemble（Serializable cached，Object owner）{
return cached;
}
public可串行化反汇编（对象值）{
return（Serializable）value;
}
public Object replace（Object original，Object target，Object owner）{
return original;
}
public int hashCode（Object obj）{
return obj.hashCode（）;
}
}
使用@TypeDefs注释配置此类in Entity class。
@TypeDefs（{
@TypeDef（name =fixedLengthChar，typeClass = nc3.jws .persistence.userType.OracleFixedLengthCharType.class）
}）
将此类型添加到CHAR类型列中
@Type（type =fixedLengthChar）
@ column（name =SERVICE_NAME，nullable = true，length = 16）
public String getServiceName（）{
return this.serviceName;
}
I have Oracle table, which contains char type columns. In my Entity class i mapped oracle char type to java string type.
Here is the code for my Entity class.
@Entity
@Table(name="ORG")
public class Organization {
private String serviceName;
private String orgAcct;
//Some other properties goes here...
@Column(name="ORG_ACCT", nullable=false, length=16)
public String getOrgAcct() {
return this.orgAcct;
}
public void setOrgAcct(String orgAcct) {
this.orgAcct = orgAcct;
}
@Column(name="SERVICE_NAME",nullable=true, length=16)
public String getServiceName() {
return this.serviceName;
}
public void setServiceName(String serviceName) {
this.serviceName = serviceName;
}
}
Here both serviceName and orgAcct are char type variables in Oracle
In my DAO class I wrote a HQL query to fetch Oranization entity object using serviceName and orgAcct properties.
@Repository
@Scope("singleton") //By default scope is singleton
public class OrganizationDAOImpl implementsOrganizationDAO {
public OrganizationDAOImpl(){
}
public Organization findOrganizationByOrgAcctAndServiceName(String orgAcct,String serviceName){
String hqlQuery = "SELECT org FROM Organization org WHERE org.serviceName = :serName AND org.orgAcct = :orgAct";
Query query = getCurrentSession().createQuery(hqlQuery)
.setString("serName", serviceName)
.setString("orgAct", orgAcct);
Organization org = findObject(query);
return org;
}
}
But when I call findOrganizationByOrgAcctAndServiceName() method , I am getting Organization object as null(i.e. HQL query is not retrieving Char type data ).
Please help me to fix this issue. Here I can't change Oracle type char to Varchar2. I need to work with oracle char type variables.
@EngineerDollery After going throw above post, I modified my Entity class with columnDefinition , @Column annotation attribute.
@Column(name="SERVICE_NAME",nullable=true,length=16,columnDefinition="CHAR")
public String getServiceName() {
return this.serviceName;
}
But still I am not able to retrieve the data for corresponding columns.
and I added column size as well in columnDefinition attribute.
@Column(name="SERVICE_NAME",nullable=true,length=16,columnDefinition="CHAR(16)
But still same issue I am facing. Any thing Am I doing wrong. Please help me.
I resolved this problem using OraclePreparedStatement and Hibernate UserType interface.
Crated a new UserType class by extending org.hibernate.usertype.UserType interface and provided implementation for nullSafeSet(), nullSafeGet() methods .
nullSafeSet() method, we have first parameter as PreparedStatement, inside the method I casted PreparedStatement into OraclePreparedStatement object and pass String value using setFixedCHAR() method.
Here is the complete code of UserType impl class.
package nc3.jws.persistence.userType;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.apache.commons.lang.StringUtils;
import org.hibernate.type.StringType;
import org.hibernate.usertype.UserType;
/**
*
* based on www.hibernate.org/388.html
*/
public class OracleFixedLengthCharType implements UserType {
public OracleFixedLengthCharType() {
System.out.println("OracleFixedLengthCharType constructor");
}
public int[] sqlTypes() {
return new int[] { Types.CHAR };
}
public Class<String> returnedClass() {
return String.class;
}
public boolean equals(Object x, Object y) {
return (x == y) || (x != null && y != null && (x.equals(y)));
}
@SuppressWarnings("deprecation")
public Object nullSafeGet(ResultSet inResultSet, String[] names, Object o) throws SQLException {
//String val = (String) Hibernate.STRING.nullSafeGet(inResultSet, names[0]);
String val = StringType.INSTANCE.nullSafeGet(inResultSet, names[0]);
//System.out.println("From nullSafeGet method valu is "+val);
return val == null ? null : StringUtils.trim(val);
}
public void nullSafeSet(PreparedStatement inPreparedStatement, Object o,
int i)
throws SQLException {
String val = (String) o;
//Get the delegatingStmt object from DBCP connection pool PreparedStatement object.
org.apache.commons.dbcp.DelegatingStatement delgatingStmt = (org.apache.commons.dbcp.DelegatingStatement)inPreparedStatement;
//Get OraclePreparedStatement object using deletatingStatement object.
oracle.jdbc.driver.OraclePreparedStatement oraclePreparedStmpt = (oracle.jdbc.driver.OraclePreparedStatement)delgatingStmt.getInnermostDelegate();
//Call setFixedCHAR method, by passing string type value .
oraclePreparedStmpt.setFixedCHAR(i, val);
}
public Object deepCopy(Object o) {
if (o == null) {
return null;
}
return new String(((String) o));
}
public boolean isMutable() {
return false;
}
public Object assemble(Serializable cached, Object owner) {
return cached;
}
public Serializable disassemble(Object value) {
return (Serializable) value;
}
public Object replace(Object original, Object target, Object owner) {
return original;
}
public int hashCode(Object obj) {
return obj.hashCode();
}
}
Configured this class with @TypeDefs annotation in Entity class.
@TypeDefs({
@TypeDef(name = "fixedLengthChar", typeClass = nc3.jws.persistence.userType.OracleFixedLengthCharType.class)
})
Added this type to CHAR type columns
@Type(type="fixedLengthChar")
@Column(name="SERVICE_NAME",nullable=true,length=16)
public String getServiceName() {
return this.serviceName;
}
这篇关于Oracle char类型问题在Hibernate中HQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
