Spring-Data-Jpa Repository - Underscore on Entity Column Name

@Query(value="select * from Rdhs_Hospital_Current_Stock h where h.sr_no = :sr_no",nativeQuery=true)
 List<Rdhs_Hospital_Current_Stock> findBySr_no(@Param("sr_no")String sr_no);

Spring Data JPA repository methods don't recognize property names with underscores

Avoid using underscores in the entity property names if you have control over the property naming. This will resolve your repository woes, and will result in a cleaner code-base. Developers dealing with the code after you will thank you.

Note, it's not just my opinion: Spring specifically discourages using underscores.

As we treat underscore as a reserved character we strongly advise to
follow standard Java naming conventions (i.e. not using underscores in
property names but camel case instead).

this JIRA issue shows why the documentation was updated with this reccomendation, and the part describing the double underscore option were removed.

I suspect your root problem is that Spring/Hibernate is not mapping camel case property names to the snake case names you have for your columns in the database. What you really need is for your property name to be interpreted in the SQL that hiberate generates as S_NAME.

Is that why underscores in your property name are "required"? If so, there are a few solutions:

Option 1: @Column annotation

To get JPA/Hibernate to map to the correct column names you can tell it the names explicitly. Use the annotation @Column(name="...") to tell it what column names to use in SQL. Then the field names are not constrained by the column names.

@Entity
public class Student {
     @Id
     @Column(name="s_id")
     private String sId;
     @Column(name="s_name")
     private String sName;

     //...getters and setters...
}

Option 2: Improved Naming Strategy

Or if your application has a large number of entities, rather than adding @Column to every property, change the default naming strategy in your configuration file to the hibernate improved naming strategy.

<prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>

This naming strategy will convert camelCase to SNAKE_CASE. Then your class could look as simple as this:

@Entity
public class Student {
     @Id
     private String sId;
     private String sName;

     //...getters and setters...
}

Using either of those options, when it creates the SQL it will resolve the column names to:

 S_ID
 S_NAME

Note: If you are using, or can use Spring Boot, the auto-configuration default will use SpringNamingStrategy, which is a slightly modified version of the hibernate improved strategy. You won't have to do anything to get this improved naming strategy.

The finish line:

Using camel case in your property names you can write your repository method name using camel case, and you can stop trying to wrangle the double underscore:

@Repository
@Transactional
public interface StudentRepository extends CrudRepository<Student, String> {  
       List<Student> findBySName(String name);   
}

JPA column with incorrect underscore

As described in spring-boot-jpa-column-name-annotation-ignored, your column name is being converted to snake case.

Possible solutions:

• Setup a Naming Strategy
• Use lowercase column names in your annotations

Spring data query not working properly, because the field is having Or in the name. Field name is - approvedOrRejectedBy

The query by method-name will interpret certain keywords from the name:

• Subject Keywords like here findAllBy
• Predicate Keywords like here And, Or, Between together with the property names

Since your field/property seems named as approvedOrRejectedBy you could work around by naming the object property differently and annotate it with the mapped DB-column name as given, e.g. @Column(name "ApprovedOrRejectedBy").

@Column(name "approvedOrRejectedBy")
String reviewedBy;

Then your query-method can be rewritten as

List<Object> findAllByTimeStampBetweenAndCameraSlugInAndReviewedBy(long startTime, long endTime, List<String> cameraSlugs, String id);

Alternatively, since the method name becomes hard to read, you could shorten the name and specify the SELECT on @Query annotation like this:

@Query("SELECT * FROM entityOrTable x WHERE x.timeStamp BETWEEN ?1 AND ?2 AND x.cameraSlug IN ?3 AND x.approvedOrRejectedBy = ?4")
List<Object> findByIntervalCameraSlugInAndReviewedBy(long startTime, long endTime, List<String> cameraSlugs, String id);

See also:

• Spring-Data-Jpa Repository - Underscore on Entity Column Name
• Spring Data JPA repository methods don't recognize property names with underscores

JPA '.'(dot) in column name

Fixed this by add the following to application.properties

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Referenced from this answer

https://stackoverflow.com/a/48442812/8277643

How to map class attributes with underscores _ in their names in Spring-data JPA

Yes Spring Data will have problem with underscores in Entity attribute names. Reason being JpaRepository simply expects attributes with proper Java Standard naming conventions eg property names should be in lower case. (if you can add multiple nouns to make it more meaning full then better make first letter of nouns in upper case except the first one)

String aMyTableId;

Above property would create tell JpaRepository to create a method like

List<MyClass> findByAMyTableId(String aMyTableId);

This would not give compilation error.

In case you want to write custom queries then you can use @Query API. Here you can write Object oriented query.

@Query("Select myclass from MyClass myclass where myclass.aMyTableId=?1 and myclass.activeFlag='1'")
List<MyClass> findByAMyTableIdWithActiveFlagOn(String aMyTableId);

You can find many tutorials and sites explaining how to write custom queries.

---

Related Topics