Hibernate @Formula doesn't include Schema

Question

I have an entity with a property @Formula like this:

@Entity
@Table(name = "areasAuxiliar")
public final class AreaAuxiliar implements Serializable {

    @Id
    @Column(name = "idArea")
    private Integer idArea;

    @Formula("RUTAAREA(idArea)")
    private String ruta;

when I configure my hibernate to point to an Oracle DB I have no problem, BUT, when I switch to an SQLServer, hibernate is not including the shema and the query fails,

the query generated for hibernate looks like this:

select
    areaauxili4_.idArea as idArea1_6_4_,
    rutaArea(areaauxili4_.idArea) as formula2_4_
from
    SIGAP.areasAuxiliar areaauxili4_ 

the param hibernate.default_schema=SIGAP is being read and included in the table but not in the function,

is there an option/annotation to force the shema in in that function?

I have tried hibernate 5.1 and 5.2 with the same result :(

Answer 1

You can use a mysql-orm.xml file to override your formula then configure your build to tale into account the file when the database is mysql.

Overriding here the formula :

<entity-mappings
    xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm orm_2_1.xsd"
    version="2.1">
    <package>com.acme.persistence</package>
    <entity class="AreaAuxiliar" access="FIELD">
        <attributes>
            <property name="ruta" formula="schemaName.RUTAAREA(idarea)"/>
        </attributes>
    </entity>
</entity-mappings>

Then add the reference in a specific persistence.xml. You then override your default persistence.xml with this one in your build or at runtime (cf. links below).

<persistence
xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
version="2.1">

<persistence-unit name="persistenceUnit">

    <provider>
        org.hibernate.jpa.HibernatePersistenceProvider
    </provider>

    <mapping-file>
        mappings/identifier/global/mysql-orm.xml
    </mapping-file>

    <class>
        com.acme.persistence.AreaAuxiliar 
    </class>

</persistence-unit>

Note : heavlily inspired from How to change Hibernate GenerationType identifier depending on the underlying database

Note (2) : In the blog post and here, the author generate the PersistenceUnitInfo at run-time.

Answer 2

Not sure if this helps apply to the function, but have you tried adding the property 'schema' to the @Table annotation:

@Entity
@Table(name = "areasAuxiliar", schema="mySchemaName")
public final class AreaAuxiliar implements Serializable {

    @Id
    @Column(name = "idArea")
    private Integer idArea;

    @Formula("RUTAAREA(idArea)")
    private String ruta;

Another solution, which was converted to a comment, is to use a place holder in the @Formula annotation

@Entity
@Table(name = "areasAuxiliar")
public final class AreaAuxiliar implements Serializable {

    @Id
    @Column(name = "idArea")
    private Integer idArea;

    @Formula("{SCHEMA_AND_FUNCTION}")
    private String ruta;

Then Add an Interceptor to populate the formula's value. The link to the solution is Hibernate @Formula set value at runtime

Lastly, please see my comment about creating a global function in SQLSERVER. The details can be found here Can I create create a global function in SQL Server?