Domain association among 3 entities

Question

I was working on a Grails application. I created 3 entities Customer,Product and Order. A customer can have many orders but each order can contain only a single product. So a single order can be associated to 1 customer and 1 product. Here are my domains:

Customer:

class Customer {
    String name;
    String address;
    Integer phoneNumber;
    Date dateCreated

    static hasMany = [orders:Order]
    static constraints = {
        name nullable: false
        address nullable: false
    }
}

Order:

class Order {
    Customer customer
    Product product
    Date orderDate

    static hasOne = [customer:Customer,product:Product]

    static constraints = {

    }
}

Product:

class Product {
    String name;
    String description;
    Date dateCreated

    static hasMany = [orders:Order]

    static constraints = {
    }
}

I generated the controller,views using generate-all and when I run the application I get this error:

| Running Grails application
| Error 2015-04-19 17:22:35,289 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: create table order (id bigint not null auto_increment, version bigint not null, customer_id bigint not null, order_date datetime not null, product_id bigint not null, primary key (id)) ENGINE=InnoDB
| Error 2015-04-19 17:22:35,290 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id bigint not null auto_increment, version bigint not null, customer_id b' at line 1
| Error 2015-04-19 17:22:35,373 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: alter table order add index FK651874E2B2D0780 (product_id), add constraint FK651874E2B2D0780 foreign key (product_id) references product (id)
| Error 2015-04-19 17:22:35,373 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order add index FK651874E2B2D0780 (product_id), add constraint FK651874E2B2D0780' at line 1
| Error 2015-04-19 17:22:35,373 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: alter table order add index FK651874E89AD9194 (customer_id), add constraint FK651874E89AD9194 foreign key (customer_id) references customer (id)
| Error 2015-04-19 17:22:35,373 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order add index FK651874E89AD9194 (customer_id), add constraint FK651874E89AD919' at line 1

Also I am not able to create a customer. Is there anything wrong in the domain mapping ?

My datasource config is as follows:

dataSource {
    driverClassName="com.mysql.jdbc.Driver"
    dialect="org.hibernate.dialect.MySQL5InnoDBDialect"
    url="jdbc:mysql://localhost:3306/aprilapp?useUnicode=true&characterEncoding=UTF-8"
    username="root"
    password="root"
    dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
}

Answer 1

You named your table order, and this is a reserved SQL keyword. Choose another table name.