java.sql.SQLException: Column 'max_grade' not Found where sql query do not contain 'max_grade'

Question

My problem is I try to get values from 3 table joining. My mysql query is as follows. It works properly without any errors in sql console. But give errors when run using Java:

StringBuilder queryString = new StringBuilder();
queryString.append("SELECT cs.id, MIN( s.grade_level_val ) as minGrade, MAX( s.grade_level_val ) as maxGrade FROM class_section cs ")
           .append("LEFT JOIN enrollment e ON e.class_section_id = cs.id ")
           .append("LEFT JOIN student s ON s.id = e.student_id ")
           .append("WHERE cs.id = :classSectionId");

TypedQuery<ClassSectionVO> query = (TypedQuery<ClassSectionVO>) entityManager.createNativeQuery(queryString.toString(), ClassSectionVO.class);
query.setParameter("classSectionId", classSectionId);

List<ClassSectionVO> result = query.getResultList();

And here is ClassSectionVO:

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class ClassSectionVO implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "minGrade")
    private Short minGrade;

    @Column(name = "maxGrade")
    private Short maxGrade;

    public Integer getId()
    {
        return id;
    }

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

    public Short getMinGrade()
    {
        return minGrade;
    }

    public void setMinGrade(Short minGrade)
    {
        this.minGrade = minGrade;
    }

    public Short getMaxGrade()
    {
        return maxGrade;
    }

    public void setMaxGrade(Short maxGrade)
    {
        this.maxGrade = maxGrade;
    }
}

There is no column max_grade in the query but its gives me the error:

java.sql.SQLException :column 'max_grade' not found.

The query language used in JPA is not the same as used in MySQL. You need to rewrite your query. — Roger Gustavsson, Sep 17 '15 at 12:37
@RogerGustavsson Not when writing a native query as it is done here. — Uwe Allner, Sep 17 '15 at 12:50
@UweAllner Oops. My bad. But some JPA/JPQL stuff seems to have crept through. The change of `maxGrade` to `max_grade`. — Roger Gustavsson, Sep 17 '15 at 12:52
Maybe look at http://stackoverflow.com/questions/2355728/jpql-create-new-object-in-select-statement-avoid-or-embrace and http://www.thoughts-on-java.org/result-set-mapping-basics/. — JimmyB, Sep 17 '15 at 12:56

score 2 · Answer 1 · answered Sep 17 '15 at 12:55

2

If your Configuration object sets an ImprovedNamingStrategy, then camelCase names get converted to snake_case.

If you don't want snake_case, then either don't set the naming strategy or else set it to the DefaultNamingStrategy.

answered Sep 17 '15 at 12:55

eggyal

122,705
18
212
237

Or is this a case where `minGrade` and `maxGrade` should be put inside back-ticks? Since they are aliases. Will they then not be touched by JPA or will that happen anyway? – Roger Gustavsson Sep 17 '15 at 12:57
@RogerGustavsson: I believe it's the other way around—the entity manager is expecting snake_case in the resultset but is finding camelCase, so changing the query (ostensibly to ensure camelCase results when they're already there) won't achieve anything. Of course, one could change the query to use snake_case if so desired. – eggyal Sep 17 '15 at 12:59
@eggyyal: Maybe I'm still missing something, but `maxGrade` is used to name a result column. The query never tries to read from a column `maxGrade` nor `max_grade`, so why would `maxGrade` be translated to `max_grade`? The error message suggests it has been. – Roger Gustavsson Sep 17 '15 at 13:03
@RogerGustavsson: The entity is declared with `@Column(name="maxGrade")`, which `ImprovedNamingStrategy` converts to `max_grade`. The resultset doesn't contain a column named `max_grade`, so the entity manager complains `column 'max_grade' not found`. – eggyal Sep 17 '15 at 13:04
See. I knew I was missing something. I'll be quiet now. – Roger Gustavsson Sep 17 '15 at 13:06
Grade job guys I got the answer, reason was same camelCase Issue. I fixed it it working properly – Lasan Sep 17 '15 at 13:23

score 1 · Accepted Answer · answered Sep 17 '15 at 12:50

If you are using JPA, in your POJOs you use camelCase like maxGrade but JPA translates that to lowercase with underscores for the actual columns in the database so maxGrade would correspond to a DB column called max_grade so the actual query will look for max_grade in the database and not maxGrade. This is convention and you can override it but its better to have database columns as lowercase with underscores if you are joining words and camelCase in code.

score 1 · Answer 3 · answered Feb 26 '17 at 22:14

1

For the future, after hours of searching and searching this saved me : spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

answered Feb 26 '17 at 22:14

ALex

673
1
6
19

java.sql.SQLException: Column 'max_grade' not Found where sql query do not contain 'max_grade'

3 Answers3