SpringBoot - MySQL - Unknown column field in list error

Question

The following is my Spring boot Rest API Application.

Vendor.Java

package hello;

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

@Entity(name="vendor")
public class Vendor {

    @Id
    @Column(name="vendorId")
    private int vendorId;
    @Column(name="vendorName")
    private String vendorName;
    @Column(name="vendorPhone")
    private int vendorPhone;
    @Column(name="vendorBalance")
    private int vendorBalance;
    @Column(name="vendorChequeAmount")
    private int vendorChequeAmount;


    public int getVendorId() {
        return vendorId;
    }
    public void setVendorId(int vendorId) {
        this.vendorId = vendorId;
    }
    public String getVendorName() {
        return vendorName;
    }
    public void setVendorName(String vendorName) {
        this.vendorName = vendorName;
    }
    public int getVendorPhone() {
        return vendorPhone;
    }
    public void setVendorPhone(int vendorPhone) {
        this.vendorPhone = vendorPhone;
    }
    public int getVendorBalance() {
        return vendorBalance;
    }
    public void setVendorBalance(int vendorBalance) {
        this.vendorBalance = vendorBalance;
    }
    public int getVendorChequeAmount() {
        return vendorChequeAmount;
    }
    public void setVendorChequeAmount(int vendorChequeAmount) {
        this.vendorChequeAmount = vendorChequeAmount;
    }

}

VendorRepository.Java

package hello;

import org.springframework.data.repository.CrudRepository;

import hello.Vendor;

// This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
// CRUD refers Create, Read, Update, Delete

public interface VendorRepository extends CrudRepository<Vendor, Integer> {

}

MainController.Java

package hello;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import hello.Vendor;
import hello.VendorRepository;

@Controller    // This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
    @Autowired // This means to get the bean called userRepository
               // Which is auto-generated by Spring, we will use it to handle the data
    private VendorRepository vendorRepository;

    @GetMapping(path="/all")
    public @ResponseBody Iterable<Vendor> getAllVendors() {
        // This returns a JSON or XML with the users
        return vendorRepository.findAll();
    }
    @GetMapping(path="/msg")
    public @ResponseBody String getMsg(){
        return "Hi";
    }
}

Application.Java

package hello;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

When I try to access @ http://localhost:8089/demo/all I get the following errors

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Sat Feb 10 14:59:39 GST 2018
There was an unexpected error (type=Internal Server Error, status=500).
could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Please see the errors :-

Eclipse error log

Table details

Application.properties

server.port=8089
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.type=trace 
spring.jpa.hibernate.naming.strategy=org.hibernate.cfg.EJB3NamingStrategy
spring.datasource.url=jdbc:mysql://localhost:3306/web_customer_tracker
spring.datasource.username=springstudent
spring.datasource.password=springstudent

Still, I am getting the error:

eclipse log

What is this error? How to resolve it?

gmc · Accepted Answer · 2018-02-10 11:38:09Z

2

From the error message, it seems that the column names are being generated wrong in the SQL query. I recommend when developing to set spring.jpa.show-sql=true in application.properties to see the generated query in console.

The error is weird, because it complaints about column vendor_id when you are scpecifically setting its name to vendorId (werid database column naming policy, btw). Researching a little bit, I came across this question, where according to its first answer, it seems to be some kind of bug with column names. As said there, try adding this in your application.preferences:

spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy

answered Feb 10, 2018 at 11:38

gmc

4,0303 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ShefZee Over a year ago

Hi, Please see my application.properties. Also, I am not able to see the SQL query generated in the console. I am running the app as a Java application.

ShefZee Over a year ago

I ran as Spring boot App and I could see the SQL generated in the log. Yes, it is wrong..Hibernate: /* select generatedAlias0 from vendor as generatedAlias0 */ select vendor0_.vendor_id as vendor_i1_0_, vendor0_.vendor_balance as vendor_b2_0_, vendor0_.vendor_cheque_amount as vendor_c3_0_, vendor0_.vendor_name as vendor_n4_0_, vendor0_.vendor_phone as vendor_p5_0_ from vendor vendor0_

Sergey Sargsyan · Accepted Answer · 2018-02-10 14:18:40Z

2

If you are using Hibernate v5 use the following line in application.properties

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

answered Feb 10, 2018 at 14:18

Sergey Sargsyan

4526 silver badges11 bronze badges

Comments

ShefZee · Accepted Answer · 2018-02-10 17:01:00Z

1

I just tried with column names in lowercase and it worked !

@Id
@Column(name="vendorid")
private int vendorId;
@Column(name="vendorname")
private String vendorName;
@Column(name="vendorphone")
private int vendorPhone;
@Column(name="vendorbalance")
private int vendorBalance;
@Column(name="vendorchequeamount")
private int vendorChequeAmount;

answered Feb 10, 2018 at 17:01

ShefZee

712 silver badges9 bronze badges

Collectives™ on Stack Overflow

SpringBoot - MySQL - Unknown column field in list error

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related