Hibernate could not fetch the SequenceInformation from the database
I solved the problem as follows. Created an extension for Oracle12cDialect. Limited the maximum/minimum value of columns to SQL
package ru.mvawork.hibernate;
import org.hibernate.dialect.Oracle12cDialect;
@SuppressWarnings("unused")
public class CustomOracleDialect extends Oracle12cDialect {
@Override
public String getQuerySequencesString() {
return "select SEQUENCE_OWNER, SEQUENCE_NAME, greatest(MIN_VALUE, -9223372036854775807) MIN_VALUE,\n"+
"Least(MAX_VALUE, 9223372036854775808) MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, ORDER_FLAG, CACHE_SIZE,\n"+
"Least(greatest(LAST_NUMBER, -9223372036854775807), 9223372036854775808) LAST_NUMBER,\n"+
"PARTITION_COUNT, SESSION_FLAG, KEEP_VALUE\n"+
"from all_sequences";
}
}
In the application.properties file referred to a dialect implementation
spring.jpa.properties.hibernate.dialect=ru.mvawork.hibernate.CustomOracleDialect
You can recreate sequences by limiting the minimum and maximum values. In my case, I can 't do it. The primary keys that I use have the dimension Number (12), which falls within the range limit from -9223372036854775807 to 9223372036854775808 with a large margin
In the end, I came up to the following solution:
- Create a sequence information extractor that extends
SequenceInformationExtractorOracleDatabaseImpl
:
public class AppSequenceInformationExtractor extends SequenceInformationExtractorOracleDatabaseImpl
{
/**
* Singleton access
*/
public static final AppSequenceInformationExtractor INSTANCE = new AppSequenceInformationExtractor();
@Override
protected Long resultSetMinValue(ResultSet resultSet) throws SQLException {
return resultSet.getBigDecimal(super.sequenceMinValueColumn()).longValue();
}
}
Yes, I understand that we can lose information about the overall magnitude and precision of this BigDecimal
value as well as return a result with the opposite sign. But this is not important due to this Steve Ebersole's comment about the Long getMinValue()
and Long getMaxValue()
methods from the SequenceInformation
interface:
I'm actually tempted to just drop these 2 methods from
SequenceInformation
. We never use them in any meaningful way. Or change the return type for these 2 methods fromLong
toBigInteger
- it could beBigDecimal
instead, but the value is implicitly an integer (in the whole number sense).I guess at this point it is too late in the game to do either of these, so something like your change is fine - like I said, we never use these values anyway. We should definitely deprecate these 2 methods IMO.
So, this trick just allows to avoid the exception with minimal awkward extra coding.
- Create a hibernate dialect that extends
Oracle12cDialect
:
public class AppOracleDialect extends Oracle12cDialect
{
@Override
public SequenceInformationExtractor getSequenceInformationExtractor() {
return AppSequenceInformationExtractor.INSTANCE;
}
@Override
public String getQuerySequencesString() {
return "select * from user_sequences";
}
}
- And then use this dialect in the
persistence.xml
:
<property name="hibernate.dialect" value="com.my.app.AppOracleDialect" />
As for the method getQuerySequencesString()
overriding and usage USER_SEQUENCES
instead of ALL_SEQUENCES
it's debatable (See HHH-13322 and HHH-14022). But, in my case, the USER_SEQUENCES
usage is preferable.