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 :(

AFAIK, the content of `@Formula.value` is passed verbatim to the query. Either create a global alias for `RUTAAREA` (not sure if SQLServer supports such aliasing, though) or prepend `RUTAAREA` with the schema name — crizzis, Jul 06 '18 at 13:27
@crizzis i cant add the schema in the code, b'cos it can change i cant build custom deploys for each case, I'm not sure but he alias can be created for tables not functions :( — Alfredo M, Jul 06 '18 at 16:37
@crizzis i added an alias for the function to the dbo but it's the same, the query ended like this: select dbo.RUTAAREA(idarea) from sigap.areasAuxiliar; and it is not recognised without the "dbo." — Alfredo M, Jul 25 '18 at 15:13
I think this is what you're looking for: https://stackoverflow.com/questions/43635745/hibernate-formula-set-value-at-runtime — Dan, Jul 27 '18 at 17:40
Also, to create a global function, try looking here: https://stackoverflow.com/questions/10727925/can-i-create-create-a-global-function-in-sql-server — Dan, Jul 27 '18 at 18:01

score 6 · Answer 1 · answered Jul 25 '18 at 16:15

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.

Dan · Answer 2 · 2018-07-27T18:03:37.553

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?

TacheDeChoco · Answer 3 · 2018-07-26T14:50:14.883

1

1) I know that for native queries, you can use the "{h-schema}" placeholder (which will populated with the value of the "hibernate.default_schema" param):

"SELECT x FROM {h-schema}tableName"

Give a try and see if by chance this is also working in @Formula...

2) If not, you might also try to play with substitutions (i.e hibernate.query.substitutions) in order to tell hibernate to replace literal S with S' - in your case "RUTAAREA" with "schema.RUTAAREA" ?

edited Jul 26 '18 at 14:50

answered Jul 26 '18 at 14:37

TacheDeChoco

3,683
1
14
17

Did you try the 2nd workaround (with substitutions) ? – TacheDeChoco Jul 27 '18 at 06:28

score -1 · Accepted Answer · answered Jul 31 '18 at 23:30

a more simplified solution:

change your @Formula from this:

@Formula("RUTAAREA(idArea)")

to this:

@Formula("{MYAPP_SCHEMA}.RUTAAREA(idArea)")

create a class:

public class HibernateEntityInterceptor extends EmptyInterceptor {

}

register it as an Entity Interceptor in your sessionFactory, in my case:

sessionFactory.setEntityInterceptor(new HibernateEntityInterceptor());

then in that class you override this method:

public String onPrepareStatement(String sql) {

that method recive the sql command before it is executed, so, all you need to do is a simple replace all:

sql = sql.replaceAll("\\{MYAPP_SCHEMA}", default_schema);
return sql;

thanx for the help.

@Dan don't get me wrong, it is pretty much your answer but i try to make it more clear, and only replacing the keyword for the schema, not the schema+function, so this can work with NamedNativeQuery as well — Alfredo M, Aug 01 '18 at 20:26

Hibernate @Formula doesn't include Schema

4 Answers4

Linked