---
title: "Discussion on \"Hibernate 6: custom SQL functions in Spring Data JPA repositories\" | Hashnode"
description: "Discussion on \"Hibernate 6: custom SQL functions in Spring Data JPA repositories\". Hibernate 6 custom functions: how to register custom SQL functions using Hibernate 6 and calling them in Spring Data JPA repositories."
url: "https://hashnode.com/posts/hibernate-6-custom-functions/6528e89bfade2f3801c64e31"
---

[Feed](https://hashnode.com/feed) /

Discussion

# [Hibernate 6: custom SQL functions in Spring Data JPA repositories](https://aregall.tech/hibernate-6-custom-functions?utm_source=hashnode&utm_medium=feed)

Hibernate 6 custom functions: how to register custom SQL functions using Hibernate 6 and calling them in Spring Data JPA repositories.

Oct 13, 2023 · 7 min read · aregall.tech  
Updated Oct 21, 2023  
Read the article

-   [#hibernate](https://hashnode.com/tag/hibernate)
-   [#springboot](https://hashnode.com/tag/springboot)
-   [#java](https://hashnode.com/tag/java)
-   [#sql](https://hashnode.com/tag/sql)
-   [#postgres](https://hashnode.com/tag/postgres)

## Written by

[Arnau Aregall](https://hashnode.com/@ArnauAregall)

@ArnauAregall · Catalonia, Spain

  
[View profile](https://hashnode.com/@ArnauAregall)

Tech Lead ☕️☁️⚙️

I'm Arnau Aregall, a hands-on Tech Lead with 13+ years of experience building software systems with passion, across Banking, Insurance, HR Tech, and enterprise platforms. I work at the intersection of software architecture, engineering culture, and product delivery. Technically I specialize in backend engineering: Java, Spring Framework, microservices, distributed systems, cloud native applications, and scalable data platforms for B2B/B2C use-cases are my speciality, but I'm equally comfortable steering end-to-end web solutions across the full stack with Angular, React, and Next.js. I believe in building systems that last: SOLID principles, DDD, thoughtful application of design patterns, and a strong focus on code quality and testability are part of how I approach technical decisions — spanning cloud architecture and security, Infrastructure as Code, GitOps, CI/CD, observability, and site reliability.

## Responses 4

Markdown supported · ⌘↵ to post

[Oscar Fanchin](https://hashnode.com/@Nexus061)

[Oct 31, 2024](https://hashnode.com/posts/hibernate-6-custom-functions/6528e89bfade2f3801c64e31/comment/672313e1ec30fdacd1172d41)

Hi, I have a custom function that returns a table, I would like to treat the return value as a list of complex objects, do you know if it is possible to register it?

[Poushali Sil](https://hashnode.com/@Silp)

[Nov 9, 2023](https://hashnode.com/posts/hibernate-6-custom-functions/6528e89bfade2f3801c64e31/comment/654d202e648458030f2f3bc8)

Very Useful article. I want to know one thing. If the return type of the custom sql function in Array, then How should one handle return type of the function

[Arnau Aregall](https://hashnode.com/@ArnauAregall)

[Nov 10, 2023](https://hashnode.com/posts/hibernate-6-custom-functions/6528e89bfade2f3801c64e31/comment/654d202e648458030f2f3bc8#reply-654de64f5bb6e20fe50a3150)

Thanks for your comment!

If I got it correctly, you would like to handle that your custom SQL functions returns an array.

Then you could register your function with your aimed return type with a `BasicTypeReference` instance.

```
private static final BasicTypeReference<String[]> RETURN_TYPE = new BasicTypeReference<>("string[]", String[].class, SqlTypes.ARRAY);
```

As an example, a function called `array_first_and_last` that gets the first and last elements of the array would be somehow like this in the post example:

```
select array[p.tags[1], p.tags[array_length(p.tags, 1)]] 
from products p
 where ...
```

After a correct registering and rendering implementation, you should be able to use it in a JPA repository as follows:

```
@Query("select array_first_and_last(p.tags) as array from Product p where p.sku = :sku")
String[] getFirstAndLastTagsBySku(@\Param("sku") String sku);
```

Hope it helps! Cheers 👍

[Toni Uimonen](https://hashnode.com/@tauimonen)

[Oct 13, 2023](https://hashnode.com/posts/hibernate-6-custom-functions/6528e89bfade2f3801c64e31/comment/6529ab4f069d0e037f9a7d58)

Great article that provides real value, thank you.

[Arnau Aregall](https://hashnode.com/@ArnauAregall)

[Oct 15, 2023](https://hashnode.com/posts/hibernate-6-custom-functions/6528e89bfade2f3801c64e31/comment/6529ab4f069d0e037f9a7d58#reply-652b748f900df10a392611b3)

Thank you! Glad you find it useful, this small feedback means a lot to me. Cheers! 👍
