Unable to find column position by name: id

Hibernate ORM
1

I’m building a demo API with spring boot and I’m getting this error when I make the request with path…
“/user/names”

Hibernate: 
    {call db_user..sp_test_nombres()}
2023-09-14T09:43:31.819-04:00  WARN 14928 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S1093
2023-09-14T09:43:31.819-04:00 ERROR 14928 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : El nombre de columna id no es válido.
2023-09-14T09:43:31.836-04:00 ERROR 14928 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.orm.jpa.JpaSystemException: Unable to find column position by name: id [El nombre de columna id no es válido.] [n/a]] with root cause

com.microsoft.sqlserver.jdbc.SQLServerException: El nombre de columna id no es válido.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237) ~[mssql-jdbc-11.2.3.jre17.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:729) ~[mssql-jdbc-11.2.3.jre17.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyResultSet.findColumn(HikariProxyResultSet.java) ~[HikariCP-4.0.3.jar:na]
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.resolveColumnPosition(AbstractResultSetAccess.java:65) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	at org.hibernate.query.results.ResultsHelper.lambda$resolveSqlExpression$0(ResultsHelper.java:64) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	at org.hibernate.query.results.DomainResultCreationStateImpl.resolveSqlExpression(DomainResultCreationStateImpl.java:280) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	at org.hibernate.query.results.ResultsHelper.resolveSqlExpression(ResultsHelper.java:58) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	at org.hibernate.query.results.implicit.ImplicitFetchBuilderBasic.buildFetch(ImplicitFetchBuilderBasic.java:97) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	at org.hibernate.query.results.implicit.ImplicitFetchBuilderBasic.buildFetch(ImplicitFetchBuilderBasic.java:37) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	at org.hibernate.query.results.DomainResultCreationStateImpl.visitIdentifierFetch(DomainResultCreationStateImpl.java:432) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	at org.hibernate.sql.results.graph.entity.AbstractEntityResultGraphNode.afterInitialize(AbstractEntityResultGraphNode.java:48) ~[hibernate-core-6.2.7.Final.jar:6.2.7.Final]
	(...continue)

That path should execute a simple stored procedure that queries for usernames. The stored procedure is the following:


ALTER PROCEDURE sp_test_nombres
AS
BEGIN
    SELECT 
        first_name 
    FROM 
        personas_2
END

This is the model:

package com.apirestdemo.test.models;

import jakarta.persistence.*;

@Entity
@Table(name="personas_2")
public class UserModel {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Long id;

    @Column
    private String firstName;

    @Column
    private String lastName;

    @Column
    private String email;

    public Long getId(){
        return id;
    }

    public void setId(Long id){
        this.id = id;
    }

    public String getFirstName() {return firstName;}

    public void setFirstName(String firstName) {this.firstName = firstName;}

    public String getLastName() {return lastName;}

    public void setLastName(String lastName) {this.lastName = lastName;}

    public String getEmail() {return email;}

    public void setEmail(String email) {this.email = email;}
}

And this is the Repository:

package com.apirestdemo.test.repositories;

import com.apirestdemo.test.models.UserModel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;

@Repository
public interface IUSerRepository extends JpaRepository<UserModel, Long> {
    @Query(value="{call db_user..sp_test_nombres()}", nativeQuery = true)
    ArrayList<UserModel> nombres();
}

In properties I have set the following:

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

What could be wrong?

2

From the error message, looks like the first_name column name might not be valid for the personas_2 table. By default, I would think that the column name is the same as the property name in the entity so firstName, have you checked if the table has this column?

3

yes, the table has that column.

6dW3zQZUY6

It seems to me that the problem is with the id column, but I don’t know why it uses that column.

4

I’m no Spring user, but I think that Spring by default configures a naming strategy that derives from Java style property names like firstName the name first_name (i.e. CamelCaseToUnderscore). You’ll have to figure out what happens there and what you want to happen. Here you can read about naming strategies: Hibernate ORM 6.2.8.Final User Guide

5

I already understood where the problem comes from. In the names() method I am telling it to return an arraylist of usermodel, but that usermodel only has the firstname parameter; id, lastname and email are empty, that’s why it generates a conflict.