java.sql.SQLSyntaxErrorException: Unknown column in 'field list'

Question

I am trying to work on a OneToMany relation using Hibernate. I am using @Temporal annotation to tell hibernate about the Data field. I am not sure why am I getting this error here. Looks like there is a problem with the Date format. Please let me know how to solve it.

Customer

package regular;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;

@Entity
public class Customers {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int customer_id;
    private String customerName;
    private String contactName;
    private String address;
    private String city;
    private String postalCode;
    private String country;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true)
    @JoinColumn(name = "customer_id")
    private List<Orders> order;

    public Customers() {
    }

    public List<Orders> getOrder() {
        return order;
    }

    public void setOrder(List<Orders> order) {
        this.order = order;
    }

    public Customers(String customerName, String contactName, String address, String city, String postalCode,
            String country, List<Orders> order) {
        this.customerName = customerName;
        this.contactName = contactName;
        this.address = address;
        this.city = city;
        this.postalCode = postalCode;
        this.country = country;
        this.order = order;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public String getContactName() {
        return contactName;
    }

    public void setContactName(String contactName) {
        this.contactName = contactName;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getPostalCode() {
        return postalCode;
    }

    public void setPostalCode(String postalCode) {
        this.postalCode = postalCode;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public int getCustomer_id() {
        return customer_id;
    }

    @Override
    public String toString() {
        return "Customers [customer_id=" + customer_id + ", customerName=" + customerName + ", contactName="
                + contactName + ", address=" + address + ", city=" + city + ", postalCode=" + postalCode + ", country="
                + country + ", order=" + order + "]";
    }

}

Orders

package regular;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
public class Orders {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int orderId;

    @Temporal(value = TemporalType.TIMESTAMP)
    private Date orderDate;

    private String productName;
    private int quantity;

    public Orders(String productName, int quantity) {
        this.orderDate = new Date();
        this.productName = productName;
        this.quantity = quantity;
    }

    public Orders() {
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public int getQuantity() {
        return quantity;
    }

    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    public int getOrderId() {
        return orderId;
    }

    @Override
    public String toString() {
        return "Orders [orderId=" + orderId + ", orderDate=" + orderDate + ", productName=" + productName
                + ", quantity=" + quantity + "]";
    }

}

Runner

package regular;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class Runner {

    public static void main(String[] args) {

        SessionFactory sessionFactory = new Configuration().configure("/regular/hibernate.cfg.xml")
                .addAnnotatedClass(Customers.class).addAnnotatedClass(Orders.class).buildSessionFactory();
        Session session = sessionFactory.openSession();
        session.beginTransaction();

        Customers customer = new Customers();

        customer.setCustomerName("Robert Bosch");
        customer.setAddress("404 California Ave");
        customer.setCity("California");
        customer.setPostalCode("60466");
        customer.setCountry("USA");

        List<Orders> orders = new ArrayList<>();

        orders.add(new Orders("Car", 4));
        orders.add(new Orders("Headphones", 6));

        customer.setOrder(orders);

        session.save(customer);

        session.close();
    }

}

Error

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'orderDate' in 'field list'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:686)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
    at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2041)
    at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1827)
    at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2041)
    at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:1977)
    at com.mysql.cj.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:4963)
    at com.mysql.cj.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1962)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 41 more

Well, does your Orders table have a column named orderDate? — Andreas
– Andreas, Commented Aug 15, 2016 at 2:05
have you tried : @Temporal(value = TemporalType.Date) since the field is a date ? — Ramachandran.A.G
– Ramachandran.A.G, Commented Aug 15, 2016 at 2:33

Hadrian Blackwater · Accepted Answer · 2016-08-15 03:21:40Z

11

Looks like there is a problem in my table names and database. I have changed the table names and it worked. Thanks everyone!

answered Aug 15, 2016 at 3:21

Hadrian Blackwater

5072 gold badges5 silver badges16 bronze badges

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

1 Comment

Hi computer Over a year ago

yeah, same problem here, just check your table names and schemas config

Saddam Hussain · Accepted Answer · 2021-02-06 09:52:19Z

6

hibernate converts the camel case lettering to underscores by default. so you either change your columns in your table to reflect that or change hibernate naming strategy.

answered Feb 6, 2021 at 9:52

Saddam Hussain

1561 silver badge6 bronze badges

Comments

Keegs · Accepted Answer · 2020-08-25 07:35:29Z

5

The below link may be helpful to some users:

Hibernate field naming issue with Spring Boot (naming strategy)

Add the below in the application.properties file:

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

answered Aug 25, 2020 at 7:35

Keegs

5107 silver badges12 bronze badges

Comments

Vikash · Accepted Answer · 2020-02-23 12:37:10Z

2

I also faced a similar issue. I had a field named as

@Column(name = "CountryCode") 
 private String CountryCode

Apparently, it was being as country_code and not as countrycode (which is the real column in the table). I changed it to and it worked:

 @Column(name = "countrycode") 
     private String CountryCode

So make sure you only start with a capital letter and not include it anywhere else in column name annotation, as it will break it up using _ and will use the 2nd caps as a delimiter to insert that _ .

answered Feb 23, 2020 at 12:37

Vikash

7311 gold badge11 silver badges19 bronze badges

Comments

Tomas Lukac · Accepted Answer · 2019-09-27 08:54:42Z

0

I have encountered the similar issue and thanks to this post I looked at the column names. In my case, I had a typo in

@ManyToOne
@JoinColumn(name = "client", referencedColumnName = "id")
private Client client;

Which should be

@ManyToOne
@JoinColumn(name = "client_id", referencedColumnName = "id")
private Client client;

answered Sep 27, 2019 at 8:54

Tomas Lukac

2,3053 gold badges26 silver badges45 bronze badges

Comments

Neeraj Kumar · Accepted Answer · 2020-02-10 09:39:28Z

0

Sometimes while inserting data into table , you are inserting into different table but in query you are writing different table's name.

More or less this answer matches with Question writer's answer.

answered Feb 10, 2020 at 9:39

Neeraj Kumar

914 bronze badges

Comments

olatno · Accepted Answer · 2021-04-25 01:17:59Z

0

If the java model class is updated with a new variable and you are not auto-generating the database table, make sure you update the column in your database table accordingly.

answered Apr 25, 2021 at 1:17

olatno

2895 silver badges8 bronze badges

Comments

Andronicus · Accepted Answer · 2022-04-06 06:33:21Z

0

Add this in your application.properties:

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

edited Apr 6, 2022 at 6:33

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

answered Apr 6, 2022 at 6:32

Suraj

1

Comments

Neili · Accepted Answer · 2021-12-21 00:25:01Z

-1

The problem is in your database field's name. You can fix it by using @Column(name = "Field's name") Ps: "Field's name" should match the column name in your database

edited Dec 21, 2021 at 0:25

answered Dec 17, 2021 at 13:32

Neili

176 bronze badges

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException: Unknown column in 'field list'

9 Answers 9

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related