How to Solve sql grammar exception and Unknown column 'id' in springBoot

Question

**** customer Class Model*********   
        @Entity
        @Table(name="customer")
        @NamedQuery(name="Customer.findAll", query="SELECT c FROM Customer c")
        public class Customer implements Serializable {
            private static final long serialVersionUID = 1L;

            @Id
            @GeneratedValue(strategy=GenerationType.AUTO)
            @Column(unique=true, nullable=false)
            private int idCustomer;

            @Column(nullable=false, length=45)
            private String name;


            //bi-directional many-to-one association to Login
            @OneToMany(mappedBy="customer")
            private Set<Login> logins;

            //bi-directional many-to-one association to Transaction
            @OneToMany(mappedBy="customer")
            private Set<Transaction> transactions;

            public Customer() {
            }

            public int getIdCustomer() {
                return this.idCustomer;
            }

            public void setIdCustomer(int idCustomer) {
                this.idCustomer = idCustomer;
            }

            public String getName() {
                return this.name;
            }

            public void setName(String name) {
                this.name = name;
            }

            public Set<Login> getLogins() {
                return this.logins;
            }

            public void setLogins(Set<Login> logins) {
                this.logins = logins;
            }

            public Login addLogin(Login login) {
                getLogins().add(login);
                login.setCustomer(this);

                return login;
            }

            public Login removeLogin(Login login) {
                getLogins().remove(login);
                login.setCustomer(null);

                return login;
            }
        }

****** Login Class Model*********

@Entity
@Table(name="login")
@NamedQuery(name="Login.findAll", query="SELECT l FROM Login l")
public class Login implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(unique=true, nullable=false)
    private int idLogin;

    @Column(length=45)
    private String password;

    @Column(length=45)
    private String uName;

    //bi-directional many-to-one association to Customer
    @ManyToOne
    @JoinColumn(name="idCustomer", nullable=false)
    private Customer customer;

    public Login() {
    }

    public int getIdLogin() {
        return this.idLogin;
    }

    public void setIdLogin(int idLogin) {
        this.idLogin = idLogin;
    }

    public String getPassword() {
        return this.password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUName() {
        return this.uName;
    }

    public void setUName(String uName) {
        this.uName = uName;
    }

    public Customer getCustomer() {
        return this.customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

}

MY Controller Method saving login and customer object in the database

@RequestMapping(value="/save",method=RequestMethod.POST)public StringsaveCust(
    @ModelAttribute("cust") 
    @Valid Customer cust,
    BindingResult result,
    @RequestParam("pass") String pass , 
    @RequestParam("user") String user){


                if (result.hasErrors()) {
                    return "redirect:/customer";
                } 

                cust = custRepo.save(cust);//using spring DAO to save the object
                Login log = new Login(); //creaing a new login
                log.setUName(user); //setting the user
                log.setPassword(pass); //setting the pass
                log.setCustomer(cust); //setting the customer
                logRepository.save(log); //doesnt save the login . bt customer is saved 
                return "success";

            }

I have submitted the two model classes which im using in my springBoot application . In my database there are 2 tables customer and login where in login customerID is a foreign key. When i try to save my 2 classes in the database customer is saved and my login is not saved . i get an error saying

Unknown column 'id_customer' in 'field list'

i am using thymeleaf in my prohect and error i am seeing in the brower is

SQLGrammarException:

I think i am doing something wrong in the controller method. Can some one explain me how to solve this.

In your database, what's the name of the column that references `idCustomer` on the `Customer` database? — António Ribeiro, Apr 25 '16 at 19:44
That's the issue. You need to add your column name: `@Column(unique=true, nullable=false, name="idCustomer")`. — António Ribeiro, Apr 25 '16 at 19:55
Does your login table have an id_customer column? Because it should have. By the way, your DB was generated by JPA ou you create it by your self? — Plínio Pantaleão, Apr 25 '16 at 19:55

score 1 · Accepted Answer · answered Apr 25 '16 at 19:46

1

What is the column name for idCustomer? You didn't put name="COLUMN_NAME" in the @Column annotation so Hibernate is defaulting your camelCaseParameter to columns named camel_case_parameter. Here specifically, it's assuming that idCustomer maps to a column named id_customer.

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(unique=true, nullable=false) // missing name="COLUMN_NAME"
private int idCustomer;

answered Apr 25 '16 at 19:46

Dean Clark

3,770
1
11
26

`@Column(unique=true, nullable=false name="idCustomer")` you mean like this – Priyamal Apr 25 '16 at 19:49
It depends what the actual column name is in your database table. If it is "idCustomer", then yes (except add a comma after `nullable = false`). If it is "CUSTOMER_ID", then use `@Column(unique=true, nullable=false, name="CUSTOMER_ID")`. – Dean Clark Apr 25 '16 at 19:54
bt im not allowed to write name inside the `@Column( name ="idCustomer")` . why is that – Priyamal Apr 25 '16 at 19:55
Missing comma. Should be: `@Column(unique=true, nullable=false, name="idCustomer")` – Dean Clark Apr 25 '16 at 19:56
wow thanks , for one moment i thought i wont be able to solve this. – Priyamal Apr 25 '16 at 19:57

How to Solve sql grammar exception and Unknown column 'id' in springBoot

1 Answers1