MSSQL User Defined Data Types in Hibernate (missing column exception)

Question

I'm working with an (old) existing MSSQL database that uses a set of user-defined data types. You can recreate my situation using by applying these statements to an MSSQL database:

CREATE TYPE [dbo].[T_MyCustomType] FROM [varchar](10) NOT NULL
GO

CREATE TABLE [dbo].[MyTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[T_MyCustomType] NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([ID] ASC))
GO

In order access this database, I'm using hibernate in a JBoss EAP 6.4.7 server. The following entity class can be used for this:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "MyTable")
public class CustomTypeTest {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int ID;

    @Column(name = "Name")
    private String name;


    public int getID() {
        return ID;
    }

    public void setID(int iD) {
        ID = iD;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

I realize that the @Column(name = "Name) annotation is not necessary, but I added it just to ensure that the correct column is used, and the same problems appear without this annotation.

For completeness, this is the persistence.xml I'm using:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Persistence deployment descriptor for dev profile -->
<persistence xmlns="http://java.sun.com/xml/ns/persistence" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" 
             version="1.0">
    <persistence-unit name="MyDatabase-MSSQL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>java:/mssql/MyDatabase</jta-data-source>       
        <class>com.example.CustomTypeTest</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>
        <properties>
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.format_sql" value="false" />
            <property name="hibernate.hbm2ddl.auto" value="validate" />
            <property name="hibernate.cache.use_second_level_cache" value="true"/>
            <property name="hibernate.cache.region.factory_class" value="org.jboss.as.jpa.hibernate4.infinispan.InfinispanRegionFactory"/>
            <property name="hibernate.cache.infinispan.cachemanager" value="java:jboss/infinispan/container/hibernate"/>     
            <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.JBossTransactionManagerLookup"/>
            <property name="hibernate.cache.use_query_cache" value="true"/>
            <property name="hibernate.jdbc.batch_size" value="100"/>
        </properties>       
    </persistence-unit>
</persistence>

When I start my JBoss server, I get the following error:

javax.persistence.PersistenceException: [PersistenceUnit: main-db-MSSql] Unable to build EntityManagerFactory
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:103)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_65]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_65]
    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_65]
    at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.1.2.Final-redhat-1.jar:2.1.2.Final-redhat-1]
Caused by: javax.persistence.PersistenceException: [PersistenceUnit:main-db-MSSql]] Unable to build EntityManagerFactory
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:925)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:900)
    at org.hibernate.ejb.HibernatePersistence.createContainerEntityManagerFactory(HibernatePersistence.java:76)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl.createContainerEntityManagerFactory(PersistenceUnitServiceImpl.java:200)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl.access$600(PersistenceUnitServiceImpl.java:57)
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:99)
    ... 4 more
Caused by: org.hibernate.HibernateException: Missing column: Name in MyDatabase.dbo.MyTable
    at org.hibernate.mapping.Table.validateColumns(Table.java:366)
    at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1307)
    at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:175)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:514)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1854)
    at org.hibernate.ejb.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:96)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:915)
    ... 9 more

If I were to change the data type of the Name column in MyTable to a standard varchar(10), it works. However, this is not possible for the existing database.

Is there any way to map MSSQL user defined data types in hibernate, so that the presented setup will work?

Answer 1

It's possible that you just need a @Column#columnDefinition annotation.

Or you need to GRANT VIEW DEFINITION to your user/role in the database for those custom types (or both).