calculate.sql

CREATE OR REPLACE FUNCTION calculate(
    IN x double precision,
    IN y double precision,
    OUT sum double precision)
  RETURNS double precision AS
BEGIN
    sum = x + y;
END;

CallCustomFunctionSelect.java

Object r = em.createQuery("SELECT function('calculate', a.id, 1) FROM Author a WHERE a.id = 1").getSingleResult();

CallCustomFunctionWhere.java

Author a = em.createQuery("SELECT a FROM Author a WHERE a.id = function('calculate', 1, 2)", Author.class).getSingleResult();

HibernateDialect.java

package org.thoughts.on.java.db;

import org.hibernate.dialect.PostgreSQL94Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;

public class MyPostgreSQL9Dialect extends PostgreSQL94Dialect {

	public MyPostgreSQL9Dialect() {
		super();
		registerFunction("calculate", new StandardSQLFunction("calculate"));
	}
}

persistence.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
	<persistence-unit name="my-persistence-unit">
		<description>Custom Database Functions</description>
		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<exclude-unlisted-classes>false</exclude-unlisted-classes>

		<properties>
		  <!-- use custom dialect -->
			<property name="hibernate.dialect" value="org.thoughts.on.java.db.MyPostgreSQL9Dialect" />

      <!-- define database connection -->
			<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
			<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/HPT" />
			<property name="javax.persistence.jdbc.user" value="postgres" />
			<property name="javax.persistence.jdbc.password" value="postgres" />
		</properties>
	</persistence-unit>
</persistence>