Reputation: 487

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

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

Upvotes: 7

Answers (9)

Suraj

Reputation: 1

Add this in your application.properties:

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

Upvotes: 0

Neili

Reputation: 17

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

Upvotes: -1

olatno

Reputation: 269

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.

Upvotes: 0

Saddam Hussain

Reputation: 156

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.

Upvotes: 6

Keegs

Reputation: 490

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

Upvotes: 5

Vikash

Reputation: 711

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 _ .

Upvotes: 1

Neeraj Kumar

Reputation: 91

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.

Upvotes: 0

Tomas Lukac

Reputation: 2225

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;

Upvotes: 0

Hadrian Blackwater

Reputation: 487

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

Upvotes: 11

java.sql.SQLSyntaxErrorException: Unknown column in &#39;field list&#39;

Answers (9)

Related Questions

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