Stored Procedure call with JPA Native Query in Firebird and spring boot

December 28, 2025

#1

Firebird supports stored procedures to encapsulate complex logic. In Spring Boot, you can call them with JPA native queries for direct execution.

Add Dependencies

In pom.xml:

<dependency>
  <groupId>org.firebirdsql.jdbc</groupId>
  <artifactId>jaybird-jdk18</artifactId>
  <version>5.0.3.java18</version>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Configure Firebird

spring.datasource.url=jdbc:firebirdsql://localhost:3050/yourdb
spring.datasource.username=sysdba
spring.datasource.password=masterkey
spring.datasource.driver-class-name=org.firebirdsql.jdbc.FBDriver
spring.jpa.hibernate.ddl-auto=none
spring.jpa.database-platform=org.hibernate.dialect.FirebirdDialect

Create a Stored Procedure in Firebird

SET TERM !! ;
CREATE PROCEDURE GET_USER_BY_ID (
    USER_ID BIGINT)
RETURNS (
    USERNAME VARCHAR(50),
    EMAIL VARCHAR(100))
AS
BEGIN
  SELECT u.username, u.email
  FROM users u
  WHERE u.id = :USER_ID
  INTO :USERNAME, :EMAIL;
END !!
SET TERM ; !!

Repository with Stored Procedure Call

public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT * FROM GET_USER_BY_ID(?1)", nativeQuery = true)
    List<Object[]> callGetUserById(Long id);
}

Service Layer

@Service
public class UserService {
    @Autowired
    private UserRepository repo;

    public Map<String, String> getUserDetails(Long id) {
        List<Object[]> result = repo.callGetUserById(id);
        if (result.isEmpty()) return Map.of();
        Object[] row = result.get(0);
        return Map.of("username", row[0].toString(),
                      "email", row[1].toString());
    }
}

Controller

@RestController
@RequestMapping("/users")
public class UserController {
    @Autowired
    private UserService service;

    @GetMapping("/{id}")
    public Map<String, String> getUserById(@PathVariable Long id) {
        return service.getUserDetails(id);
    }
}