关于Oracle的date类型,使用hibernate,经常会出现不走索引的情况,根本原因就是Oracle是Timestamp类型的
类似下面的代码:
// execute_at is of type DATE and there's an index
PreparedStatement stmt = connection.prepareStatement(
"SELECT * " +
"FROM rentals " +
"WHERE rental_date > ? AND rental_date < ?");
使用java.sql.timestamp绑定:
stmt.setTimestamp(1, start);
stmt.setTimestamp(2, end);
产生下面的查询计划:
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
|* 2 | TABLE ACCESS FULL| RENTAL |
-------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:1<=:2)
2 - filter((INTERNAL_FUNCTION("RENTAL_DATE")>=:1 AND
INTERNAL_FUNCTION("RENTAL_DATE")<=:2))
通过INTERNAL_FUNCTION方法来处理Oracel Date和Oracle Timestamp的转换
可以使用hibernate 的userType来解决这个问题
定义一个OracleDate的转换:
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Objects;
import oracle.sql.DATE;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;
public class OracleDate implements UserType {
@Override
public int[] sqlTypes() {
return new int[] { Types.TIMESTAMP };
}
@Override
public Class<?> returnedClass() {
return Timestamp.class;
}
@Override
public Object nullSafeGet(
ResultSet rs,
String[] names,
SessionImplementor session,
Object owner
)
throws SQLException {
return rs.getTimestamp(names[0]);
}
@Override
public void nullSafeSet(
PreparedStatement st,
Object value,
int index,
SessionImplementor session
)
throws SQLException {
// The magic is here: oracle.sql.DATE!
st.setObject(index, new DATE(value));
}
// The other method implementations are omitted
}
entity中使用:
@Entity
@TypeDefs(
value = @TypeDef(
name = "oracle_date",
typeClass = OracleDate.class
)
)
public class Rental {
@Id
@Column(name = "rental_id")
public Long rentalId;
@Column(name = "rental_date")
@Type(type = "oracle_date")
public Timestamp rentalDate;
}
现在的执行计划会变成:
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS BY INDEX ROWID| RENTAL |
|* 3 | INDEX RANGE SCAN | IDX_RENTAL_UQ |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:1<=:2)
3 - access("RENTAL0_"."RENTAL_DATE">=:1
AND "RENTAL0_"."RENTAL_DATE"<=:2)
对于JPA目前没有很好的办法(JPA2.1举例)
JPA中能够使用convertor来处理类型的转换:
import java.sql.Timestamp;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import oracle.sql.DATE;
@Converter
public class OracleDateConverter
implements AttributeConverter<Timestamp, DATE>{
@Override
public DATE convertToDatabaseColumn(Timestamp attribute) {
return attribute == null ? null : new DATE(attribute);
}
@Override
public Timestamp convertToEntityAttribute(DATE dbData) {
return dbData == null ? null : dbData.timestampValue();
}
}
使用:
import java.sql.Timestamp;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class Rental {
@Id
@Column(name = "rental_id")
public Long rentalId;
@Column(name = "rental_date")
@Convert(converter = OracleDateConverter.class)
public Timestamp rentalDate;
}
但是在hibernate4.3.7中,会认为视图使用类型VARBINARY来绑定变量
源码:
// From org.hibernate.type.descriptor.sql.SqlTypeDescriptorRegistry
public <X> ValueBinder<X> getBinder(JavaTypeDescriptor<X> javaTypeDescriptor) {
if ( Serializable.class.isAssignableFrom( javaTypeDescriptor.getJavaTypeClass() ) ) {
return VarbinaryTypeDescriptor.INSTANCE.getBinder( javaTypeDescriptor );
}
return new BasicBinder<X>( javaTypeDescriptor, this ) {
@Override
protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options)
throws SQLException {
st.setObject( index, value, jdbcTypeCode );
}
};
}
这个问题在这里有描述:https://hibernate.atlassian.net/browse/HHH-9553