Registering a SQL function with JPA and Hibernate

问题:

I would like to know what's the best way to register a custom SQL function with JPA/Hibernate.

Do I have to go through extending the MysqlInnodb dialect or is there a better way?

Can anyone please provide code samples and pointers to relevant documentation?

回答1:

Yes extending the dialect is a good way of registering custom SQL function.

Add something like this in your Dialect classes constructor.

registerFunction("current_timestamp", new NoArgSQLFunction(Hibernate.TIMESTAMP) );
registerFunction("date", new StandardSQLFunction(Hibernate.DATE) );

Look at the source code of one of the existing dialect classes. http://www.koders.com/java/fid0E7F787E2EC52F1DA8DFD264EDFBD2DE904A0927.aspx

回答2:

As I explained in this article, since Hibernate ORM 5.2.18 and 5.3.1, the best way to register a SQL function is to supply a MetadataBuilderContributor like this:

public class SqlFunctionsMetadataBuilderContributor 
        implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "group_concat",
            new StandardSQLFunction(
                "group_concat", 
                StandardBasicTypes.STRING
            )
        );
    }
}

Which you can pass to Hibernate via the hibernate.metadata_builder_contributor configuration property:

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

Or, if you bootstrap Hibernate natively, you can just apply the SQL function to the MetadataBuilder during bootstrap.

You might read articles telling you to register the SQL function by extending the Hibernate Dialect, but that's a naive solution. For more details, read this article.

回答3:

Register SQL Method every version

        //Add Hibernate Properties
        properties.put("hibernate.dialect",
                        "com.sparkslink.web.config.sql.RegisterSqlFunction");

        //Create A Class 
        public class RegisterSqlFunction extends MySQLDialect {

            public RegisterSqlFunction() {
                super();
                registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
            }
        }

        //Dao Method

        public List<Client> getTest() {
                Query query = getSession()
                        .createQuery("SELECT sl.name as name ,group_concat(sl.domain) as domain FROM SlClient sl GROUP BY sl.name");
                query.setResultTransformer(Transformers.aliasToBean(Client.class));
                return query.list();
            }
//DTO Class
    public class Client {
        private String name;
        private String domain;
    //Getter 
    //Setter
    }