The best way to use SQL functions in JPQL or Criteria API queries with JPA and Hibernate

Introduction

When executing an entity query (e.g. JPQL, HQL or Criteria API), you can use any SQL function without having to register it as long as the function is passed directly to the WHERE clause of the underlying SQL statement.

However, if the SQL function is used in the SELECT clause, and Hibernate has not registered the SQL function (be it a database-specific or user-defined function), you will have to register the function prior to using it in an entity query.

In this article, you are going to learn various ways to register SQL functions with JPA and Hibernate.

Continue reading “The best way to use SQL functions in JPQL or Criteria API queries with JPA and Hibernate”

How to call MySQL stored procedures and functions with JPA and Hibernate

Introduction

This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational database.

Continue reading “How to call MySQL stored procedures and functions with JPA and Hibernate”

How to call SQL Server stored procedures and functions with JPA and Hibernate

Introduction

This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational database.

Continue reading “How to call SQL Server stored procedures and functions with JPA and Hibernate”

JOOQ Facts: SQL functions made easy

Introduction

The JDBC API has always been cumbersome and error-prone and I’ve never been too fond of using it. The first major improvement was brought by the Spring JDBC framework which simply revitalized the JDBC usage with its JdbcTemplate or the SqlFunction classes, to name a few. But Spring JDBC doesn’t address the shortcoming of using string function or input parameters names and this opened the door for type-safe SQL wrappers such as jOOQ.

JOOQ is the next major step towards a better JDBC API and ever since I started using it I knew there was no turning back. JOOQ became my number one choice for building dynamic queries and recently it became my standard SQL function wrapper.

Continue reading “JOOQ Facts: SQL functions made easy”