Hibernate, mysql Unsigned smallint映射错误

Question

hibernate版本: hibernate-core-5.3.21.Final
遇到的问题是这样的:
Spring Data JPA集成hibernate, 定义的JpaRepository接口方法:

@Query(value = "select * from xxx", nativeQuery=true)
List<Map<String, Object>> queryXXXX();

数据库是mysql, 其中一个字段类型为smallint unsigned.
该字段可以存储0--65535, 而hibernate执行查询, 对结果进行转化时, 自动进行类型判断, 将该字段中的值存入了Short里, 导致装不下, 最终报错:

Caused by: org.hibernate.exception.DataException: could not execute query
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.loader.Loader.doList(Loader.java:2790)
    at org.hibernate.loader.Loader.doList(Loader.java:2770)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2604)
    at org.hibernate.loader.Loader.list(Loader.java:2599)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2243)
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069)
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:173)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1566)
    at org.hibernate.query.Query.getResultList(Query.java:132)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    ... 128 more
Caused by: java.sql.SQLDataException: Value '40131' is outside of valid range for type java.lang.Short
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:114)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:92)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1423)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getShort(ResultSetImpl.java:859)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getShort(ResultSetImpl.java:865)
    at com.alibaba.druid.pool.DruidPooledResultSet.getShort(DruidPooledResultSet.java:283)
    at org.hibernate.type.descriptor.sql.SmallIntTypeDescriptor$2.doExtract(SmallIntTypeDescriptor.java:62)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)
    at org.hibernate.loader.custom.ScalarResultColumnProcessor.extract(ScalarResultColumnProcessor.java:54)
    at org.hibernate.loader.custom.ResultRowProcessor.buildResultRow(ResultRowProcessor.java:83)
    at org.hibernate.loader.custom.ResultRowProcessor.buildResultRow(ResultRowProcessor.java:60)
    at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:412)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:771)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:1002)
    at org.hibernate.loader.Loader.doQuery(Loader.java:960)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:351)
    at org.hibernate.loader.Loader.doList(Loader.java:2787)
    ... 151 more
Caused by: com.mysql.cj.exceptions.NumberOutOfRange: Value '40131' is outside of valid range for type java.lang.Short
    at com.mysql.cj.result.ShortValueFactory.createFromLong(ShortValueFactory.java:62)
    at com.mysql.cj.result.ShortValueFactory.createFromLong(ShortValueFactory.java:44)
    at com.mysql.cj.protocol.a.MysqlBinaryValueDecoder.decodeUInt2(MysqlBinaryValueDecoder.java:190)
    at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:104)
    at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:243)
    at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1311)
    ... 168 more

请问, 应该加上什么配置, 才能让smallint unsigned映射到Integer类型上去? 谢谢!

Answer 1

smallint 改为 int

Answer 2

找到了一种不改变数据库表结构, 通过自定义SqlTypeDescriptor来重新进行映射, 将所有数据库中的short类型映射到integer上, 部分代码如下(以下代码未经严格测试):

import org.hibernate.boot.model.TypeContributions;
import org.hibernate.boot.model.TypeContributor;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.type.AbstractSingleColumnStandardBasicType;
import org.hibernate.type.descriptor.ValueBinder;
import org.hibernate.type.descriptor.ValueExtractor;
import org.hibernate.type.descriptor.WrapperOptions;
import org.hibernate.type.descriptor.java.IntegerTypeDescriptor;
import org.hibernate.type.descriptor.java.JavaTypeDescriptor;
import org.hibernate.type.descriptor.sql.BasicBinder;
import org.hibernate.type.descriptor.sql.BasicExtractor;
import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class CustomTypeContributor implements TypeContributor {

    private static final SmallintToIntegerBasicType INSTANCE = new SmallintToIntegerBasicType();

    @Override
    public void contribute(TypeContributions typeContributions, ServiceRegistry serviceRegistry) {
        typeContributions.getTypeConfiguration().getBasicTypeRegistry().register(INSTANCE);
    }

    public static class SmallintToIntegerBasicType extends AbstractSingleColumnStandardBasicType<Integer>{

        public SmallintToIntegerBasicType() {
            super(new SmallintToIntegerTypeDescriptor(), new IntegerTypeDescriptor());
        }

        @Override
        public String getName() {
            return "short";
        }
    }

    public static class SmallintToIntegerTypeDescriptor implements SqlTypeDescriptor {

        @Override
        public int getSqlType() {
            return Types.SMALLINT;
        }

        @Override
        public boolean canBeRemapped() {
            return true;
        }

        @Override
        public <X> ValueBinder<X> getBinder(JavaTypeDescriptor<X> javaTypeDescriptor) {
            return new BasicBinder<X>( javaTypeDescriptor, this ) {
                @Override
                protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
                    st.setInt( index, javaTypeDescriptor.unwrap( value, Integer.class, options ) );
                }

                @Override
                protected void doBind(CallableStatement st, X value, String name, WrapperOptions options)
                        throws SQLException {
                    st.setInt( name, javaTypeDescriptor.unwrap( value, Integer.class, options ) );
                }
            };
        }

        @Override
        public <X> ValueExtractor<X> getExtractor(JavaTypeDescriptor<X> javaTypeDescriptor) {
            return new BasicExtractor<X>( javaTypeDescriptor, this ) {
                @Override
                protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
                    return javaTypeDescriptor.wrap( rs.getInt( name ), options );
                }

                @Override
                protected X doExtract(CallableStatement statement, int index, WrapperOptions options) throws SQLException {
                    return javaTypeDescriptor.wrap( statement.getInt( index ), options );
                }

                @Override
                protected X doExtract(CallableStatement statement, String name, WrapperOptions options) throws SQLException {
                    return javaTypeDescriptor.wrap( statement.getInt( name ), options );
                }
            };
        }
    }
}

配置类:

    @Primary
    @Bean(name = "userEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean userEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        Map<String, Object> properties = getVendorProperties(getDataSource());
        innerHibernateConfiguration(properties);
        return builder.dataSource(getDataSource())
                .properties(properties)
                .packages("xxx")
                .persistenceUnit("conf")
                .build();
    }
    
    public void innerHibernateConfiguration(Map<String, Object> properties){
        properties.put(EntityManagerFactoryBuilderImpl.TYPE_CONTRIBUTORS, new TypeContributorList(){
            @Override
            public List<TypeContributor> getTypeContributors() {
                ArrayList<TypeContributor> contributors = new ArrayList<>(1);
                contributors.add(new CustomTypeContributor());
                return contributors;
            }
        });
    }