Home > OS >  Tables are created in the wrong order. SpringBoot MySql
Tables are created in the wrong order. SpringBoot MySql

Time:08-11

I created all the entities and linked them with primary, foreign keys. In theory, when I run the program, then all these entities should be created in the database, but the problem is that they are created in the wrong order.

For example, first the "orders" label is created, and then the "users" label, and because of this it is impossible to link them using the primary key.

Hence the question, how to make it so that hibernate understands in what order to create tables

Tables are created in the sequence shown below

enter image description here

Error

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table orders (id bigint not null, address varchar(255), create datetime, status varchar(255), sum decimal(19,2), updated datetime, user_id bigint, primary key (id)) engine=MyISAM" via JDBC Statement

Caused by: java.sql.SQLSyntaxErrorException: 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 'create datetime, status varchar(255), sum decimal(19,2), updated datetime, user_' at line 1

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/shop?useSSL=false&serverTimezone=UTC
spring.datasource.username=bestuser
spring.datasource.password=bestuser
spring.flyway.baseline-on-migrate=true
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect

Bucket

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "buckets")
public class Bucket {
    private static final String SEQ_NAME = "bucket_seq";

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQ_NAME)
    @SequenceGenerator(name = SEQ_NAME, sequenceName = SEQ_NAME, allocationSize = 1)
    private Long id;
    @OneToOne
    @JoinColumn(name = "user_id")
    private User user;

    @ManyToMany
    @JoinTable(name = "buckets_products", joinColumns = @JoinColumn(name = "bucket_id"), inverseJoinColumns = @JoinColumn(name = "product_id"))
    private List<Product> products;
}

Category

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "categories")
public class Category {

    private static final String SEQ_NAME = "categories_seq";

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQ_NAME)
    @SequenceGenerator(name = SEQ_NAME, sequenceName = SEQ_NAME, allocationSize = 1)
    private Long id;
    private String title;
}

Order

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "orders")
public class Order {

    private static final String SEQ_NAME = "orders_seq";

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQ_NAME)
    @SequenceGenerator(name = SEQ_NAME, sequenceName = SEQ_NAME, allocationSize = 1)
    private Long id;
    @CreationTimestamp
    private LocalDateTime create;
    @UpdateTimestamp
    private LocalDateTime updated;
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
    private BigDecimal sum;
    private String address;
    @OneToMany(cascade = CascadeType.ALL)
    private List<OrderDetails> details;
    @Enumerated(EnumType.STRING)
    private OrderStatus status;
}

OrderDetails

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "orders_details")
public class OrderDetails {

    private static final String SEQ_NAME = "order_details_seq";

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQ_NAME)
    @SequenceGenerator(name = SEQ_NAME, sequenceName = SEQ_NAME, allocationSize = 1)
    private Long id;
    @ManyToOne
    @JoinColumn(name = "order_id")
    private Order order;
    @ManyToOne
    @JoinColumn(name = "product_id")
    private Product product;
    private BigDecimal amount;
    private BigDecimal price;
}

OrderStatus

public enum OrderStatus {
    NEW, APPROVED, CANCELED, PAID, CLOSED
}

Product

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "products")
public class Product {
    private static final String SEQ_NAME = "product_seq";

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQ_NAME)
    @SequenceGenerator(name = SEQ_NAME, sequenceName = SEQ_NAME, allocationSize = 1)
    private Long id;
    private String title;
    private BigDecimal price;
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "products_categories", joinColumns = @JoinColumn(name = "product_id"), inverseJoinColumns = @JoinColumn(name = "category_id"))
    private List<Category> categories;
}

Role

    public enum Role {
    CLIENT, MANAGER, ADMIN
}

User

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "users")
public class User {
    private static final String SEQ_NAME = "user_seq";

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQ_NAME)
    @SequenceGenerator(name = SEQ_NAME, sequenceName = SEQ_NAME, allocationSize = 1)
    private Long id;

    private String name;
    private String password;
    private String email;
    private boolean active;
    @Enumerated(EnumType.STRING)
    private Role role;
    @OneToOne(cascade = CascadeType.REMOVE)
    private Bucket bucket;
}

CodePudding user response:

You can't achieved table creation sequence by hibernate. You can use migration script tool, like Flyway or Liquibase

And your application.properties (flyway sample) set next properties

spring.flyway.url=
spring.flyway.user=
spring.flyway.password=
spring.flyway.schemas=public
spring.flyway.locations=classpath:/your/path
spring.jpa.hibernate.ddl-auto=validate

CodePudding user response:

you can use @Temporal This annotation must be specified for persistent fields or properties of type java.util.Date and java.util.Calendar

see What is the use of the @Temporal annotation in Hibernate?

Put a annotation on each date or time field as follows :

@Temporal(TemporalType.TIMESTAMP)

or

@Temporal(TemporalType.DATE)

or

@Temporal(TemporalType.TIME)
  • Related