45

I am using spring-data-jpa on a spring webmvc project. I am facing an issue using query creation on a Repository of one of my Entities. Below you can see my Entity, my Repository and the Exception.

My Entity: 

@Entity
@Table(schema = "mainschema")
@XmlRootElement
public class Municipalperson implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(nullable = false)
    private Integer id;

    @Basic(optional = false)
    @Column(name = "municipal_id", nullable = false)
    private Integer municipal_id;

    @Basic(optional = false)
    @Column(nullable = false, length = 60)
    private String firstname;

    public Municipalperson(Integer id, Integer municipal_id, String firstname) {
        this.id = id;
        this.municipal_id = municipal_id;
        this.firstname = firstname;
    }


    public Integer getId() {
        return id;
    }

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

    public Integer getMunicipal_id() {
        return municipal_id;
    }

    public void setMunicipal_id(Integer municipal_id) {
        this.municipal_id = municipal_id;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }
}

my Repository: 

@Repository
public interface MunicipalpersonRepository extends JpaRepository<Municipalperson, Integer> {

    List<Municipalperson> findByMunicipal_idOrderByLastnameDesc(int municipal_id);
}

and the exception, 

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'municipalpersonRepository': Invocation of init method failed; nested exception is org.springframework.data.mapping.PropertyReferenceException: No property municipal found for type Municipalperson!

I tried to set municipal_id as int, then as Integer and the same for the parameter municipal_id on my Repository, but none worked. Also, I renamed the Repository to findByMunicipalidOrderByLastnameDesc and findByMunicipalIdOrderByLastnameDesc but it didn't work either.

Finally I renamed the municipal_id to municipalId (underscore removed) and also renamed getters/setters and the Repository (findByMunicipalIdOrderByLastnameDesc) and the issue was resolved.

My question is why this is happening?

BeUndead
  • 3,463
  • 2
  • 17
  • 21
Georgios Syngouroglou
  • 18,813
  • 9
  • 90
  • 92
  • 2
    Spring Data JPA heavily relies on Java naming conventions. You chose not to respect them by having an underscore in your field and accessor names. That's why you got bitten. Respect the Java naming conventions, and Spring Data JPA will be happy, and your code will be more readable as a bonus. Not that the column can be named as you want, including with underscores. What matters is your mapped field names. – JB Nizet May 04 '14 at 12:31
  • Thank you very much for your comment. I didn't know about the java naming conventions. I found, also, this [stackoverflow question](http://stackoverflow.com/questions/150192/using-underscores-in-java-variables-and-method-names) that helped me too. – Georgios Syngouroglou May 04 '14 at 12:38

6 Answers6

62

I solved this error by renaming field to the name without underscore.

@Column(name = "municipal_id", nullable = false)
private Integer municipalId; // <-- field was renamed
Valentyn Kolesnikov
  • 2,029
  • 1
  • 24
  • 31
  • 2
    This really is the best way to handle this scenario and should be the accepted answer. I realize this answer came a few years after the question and first answer, but am putting this comment to draw attention of future visitors to this answer. Solving in this manner allows you to work with databases that name fields in a way you cannot control, without deviating from the coding standards which are generally accepted across the entire field of java developers, e.g. keeps underscores entirely out of your method and variable names. – Thomas Carlisle Jun 28 '17 at 18:41
  • Best way to remove error without changing database and all the mapping stuff. – Chinthaka Dinadasa Jan 24 '18 at 09:50
  • Answering the first comment, the question was not "how to map a db column name with underscore to a Java Entity" but how to use an entity property with an underscore with spring-data. That's why this answer is not the accepted. – Georgios Syngouroglou Feb 14 '18 at 11:34
  • 2
    this answer should be marked as correctly one, a very small effort to overcome problem. – Khang Tran Jun 02 '20 at 23:40
45

The underscore _ is a reserved character in Spring Data query derivation (see the reference docs for details) to potentially allow manual property path description. So there are two options you have:

  1. Stick to the Java naming conventions of using camel-case for member variable names and everything will work as expected.
  2. Escape the _ by using an additional underscore, i.e. rename your query method to findByMunicipal__idOrderByLastnameDesc(…).

I'd recommend the former as you're not going to alienate fellow Java developers :).

Oliver Drotbohm
  • 80,157
  • 18
  • 225
  • 211
  • 28
    This does not work. Double underscore does not change anything. And fields in Java JPA object just follow the name of the fields in database that obviously does not follow Java convention. So that must be a pretty common use case. – Stan Sokolov Apr 10 '16 at 01:47
  • 3
    The double underscore solution did not worked for me too and I am not able to undo my upvote... – Zomono Dec 01 '17 at 11:38
  • The double underscore did not work for me, but I also see many upvotes. Perhaps the answer can be improved by listing the conditions required for this to work, so those for whom it doesn't apply don't attempt this solution? – user1445967 Aug 21 '19 at 18:36
  • The answer is fine. Just use option 1. People spent way too much time working on conventions and providing support for it so it becomes useful. The only problem with the answer is that option 2 is mentioned, it shouldn't. – negative Oct 16 '20 at 18:23
  • 1
    Option 1 is sometimes not possible, especially when you work with code that is supposed to follow format guidelines and which verification is done as part of the build, some code conventions in some companies require you to use prefix instance variables with "_" to differentiate them from method local variables and to avoid idioms like this.myVar = myVar which can become smelly. Jagannath's answer covers those cases. – raspacorp Feb 25 '21 at 22:54
  • The link to the reference docs is no longer valid. This is the correct one: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-property-expressions – Wim Deblauwe Oct 26 '21 at 06:32
5

Please add the following properties to application.properties file:

spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
Lotus91
  • 1,127
  • 4
  • 18
  • 31
Jagannath Nanda
  • 51
  • 1
  • 2
  • This works well and requires no other changes. From: https://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/cfg/ImprovedNamingStrategy.html: org.hibernate.cfg.ImprovedNamingStrategy is an improved naming strategy that prefers embedded underscores to mixed case names. – bhlowe Dec 26 '18 at 21:49
  • spring.jpa.hibernate.naming-strategy is an unknown property error – Sayan Dasgupta Dec 04 '21 at 19:05
2

I know the question was answered a long time ago, but it can help others in the future.

According to the Docs, underscore is a special character used by spring to separate properties names. If you really want to stick with snake case notation, you can set nativeQuery to true and solve this problem:

@Query(value = "SELECT * FROM municipalperson WHERE municipal_id=?1 ORDER BY last_name DESC", nativeQuery = true)
List<Municipalperson> findByMunicipal_idOrderByLastnameDesc(int municipal_id);
Cássio Morales
  • 121
  • 7
2

One other approach that worked for me is to use @JsonProperty to differentiate between field name used in REST request/response and that used for database. For example:

@JsonProperty("municipalId")
private Integer municipal_id;
Gabriel Dimech
  • 699
  • 5
  • 10
0

Refer to the official documentation here

  • Because we treat the underscore character as a reserved character, we strongly advise following standard Java naming conventions (that is, not using underscores in property names but using camel case instead).
Ravendra Singh
  • 21
  • 6