Spring JPA - underscore on column name, failed to create query, property not found

I have an Employee class with a company_id field. I'm using spring-data-jpa and I want to create a query with JPA to find a class

@Entity
public class Employee {
  ...
  @Column(name = "company_id")
  private Long company_id;
  ...
}

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    Employee findByCompany_Id(Long companyId);
}

During startup, the application crashes because of this error:

org.springframework.data.repository.query.QueryCreationException: Could not create 
query for public abstract com.example.jpa.Employee com.example.jpa.EmployeeRepository.findByCompany_Id(java.lang.Long)

Caused by: java.lang.IllegalArgumentException: Failed to create query for method 
public abstract com.example.jpa.Employee com.example.employee.jpa.EmployeeRepository.findByCompany_Id(java.lang.Long); 
No property 'company' found for type 'Employee'

I tried to change the method name to findByCompany_id but id didn't help. This seems to happen only for parameters that have an underscore in the name. Why is this happening and how can I fix it?

Solution

In Spring Data JPA, the underscore is a special character, it is used to search into nested fields. So by defining the query findByCompany_Id, JPA expects to find a Company field with an Id in it. For example, this query would work fine if your Employee entity was defined like this:

@Entity
public class Employee {
  ...
  @JoinColumn
  private Company company;
  ...
}

In any case, to fix it I highly suggest changing all your entity parameters and the method query definition to camelCase, as that is what works best with jpa. Spring also automatically converts the field names to columns converting camelCase to snake_case out of the box, so you don't need to delcare all the column names

@Entity
public class Employee {
  ...
  private Long companyId;
  ...
}

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    Employee findByCompanyId(Long companyId);
}

This is also mentioned in spring documentation:

Because we treat underscores (_) as a reserved character, we strongly advise to follow standard Java naming conventions (that is, not using underscores in property names but applying camel case instead).

If you don't want to change to camel case, then you will have to write down your queries as you cannot rely on JPA:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @Query("Select e from Employee e where e.company_id = :companyId")
    Employee findByCompany_Id(Long companyId);
}

Alternative #1

I've encountered this exact issue in production applications, and sometimes the problem isn't just the underscore - it's also about naming conventions and JPA's property resolution logic.

One approach I've found effective is using explicit column mapping with @Column annotations:

@Entity
public class Employee {
    @Id
    private Long id;
    
    @Column(name = "company_id")  // Explicit column mapping
    private Long companyId;       // Camel case property name
    
    // Getters and setters
}

Then your repository method becomes:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    Employee findByCompanyId(Long companyId);  // Clean method name
}

This approach:

Keeps your database column names as they are
Uses proper Java naming conventions
Avoids JPA's underscore interpretation issues
Makes your code more maintainable

I've also seen this happen when legacy databases have inconsistent naming patterns. This solution works well for those scenarios.

Alternative #2

Another common scenario I've encountered is when you're working with legacy databases that you can't change, or when you need to maintain backward compatibility.

In these cases, you can use custom query methods with @Query and native SQL:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    
    @Query(value = "SELECT * FROM employee WHERE company_id = :companyId", nativeQuery = true)
    Employee findByCompanyIdNative(@Param("companyId") Long companyId);
    
    // Or using JPQL with explicit property mapping
    @Query("SELECT e FROM Employee e WHERE e.companyId = :companyId")
    Employee findByCompanyIdJpql(@Param("companyId") Long companyId);
}

You can also use Specification for more complex queries:

public class EmployeeSpecifications {
    public static Specification<Employee> hasCompanyId(Long companyId) {
        return (root, query, criteriaBuilder) -> 
            criteriaBuilder.equal(root.get("companyId"), companyId);
    }
}

// Usage
List<Employee> employees = employeeRepository.findAll(EmployeeSpecifications.hasCompanyId(companyId));

This approach gives you full control over the query logic while maintaining type safety.

Alternative #3

If you're dealing with complex entity relationships or multiple naming conventions, you might want to consider using JPA's @AttributeOverride or custom converters.

Here's an approach I've used for entities with multiple naming patterns:

@Entity
@Table(name = "employee")
public class Employee {
    @Id
    private Long id;
    
    @Column(name = "company_id")
    private Long companyId;
    
    @Column(name = "department_id")
    private Long departmentId;
    
    // Use @AttributeOverride for inheritance scenarios
    @AttributeOverride(name = "companyId", column = @Column(name = "company_id"))
    private CompanyInfo companyInfo;
}

// Or use custom converters for complex mappings
@Converter
public class CompanyIdConverter implements AttributeConverter<Long, String> {
    @Override
    public String convertToDatabaseColumn(Long companyId) {
        return companyId != null ? companyId.toString() : null;
    }
    
    @Override
    public Long convertToEntityAttribute(String dbData) {
        return dbData != null ? Long.valueOf(dbData) : null;
    }
}

Then your repository methods become very clean:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    List<Employee> findByCompanyIdAndDepartmentId(Long companyId, Long departmentId);
    Employee findByCompanyId(Long companyId);
}

This approach is especially useful when you have mixed naming conventions in your database schema.