Problem with relational tables MySQL. Spring Boot + Hibernate

Question

I'm trying to make relational tables with MySQL and Hibernate.

And here is the code in IntelliJ:

DeviceEntity

    import org.springframework.format.annotation.DateTimeFormat;
    
    import java.io.Serializable;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    import javax.persistence.*;
    
    @Entity
    @Table(name = "device")
    public class DeviceEntity implements Serializable {
    
        private static final long serialVersionUID = 1L;
    
        // -- Attributes -- //
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        private Integer id;
    
        @Column(name = "name")
        private String name;
    
        @Column(name = "type")
        private String type;
    
        @Column(name = "model")
        private String model;
    
        @Column(name = "enabled")
        private Boolean enabled;
    
        @Temporal(TemporalType.TIMESTAMP)
        @DateTimeFormat(pattern = "dd/MM/yyyy hh:mm:ss")
        @Column(name = "created_at")
        private Date createdAt;
    
        @OneToMany(fetch = FetchType.LAZY)
        private List<PetitionEntity> listPetitionEntity;
    
        // -- Contructors -- //
    
        public DeviceEntity(){
            listPetitionEntity = new ArrayList<>();
        }
    
        // -- Methods -- //
    
        @PrePersist
        public void prePersist() {
            createdAt = new Date();
        }
    
        // -- Getters & Setters -- //
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getType() {
            return type;
        }
    
        public void setType(String type) {
            this.type = type;
        }
    
        public String getModel() {
            return model;
        }
    
        public void setModel(String model) {
            this.model = model;
        }
    
        public Boolean getEnabled() {
            return enabled;
        }
    
        public void setEnabled(Boolean enabled) {
            this.enabled = enabled;
        }
    
        public Date getCreatedAt() {
            return createdAt;
        }
    
        public void setCreatedAt(Date createdAt) {
            this.createdAt = createdAt;
        }
    
        public List<PetitionEntity> getListPetitionEntity() {
            return listPetitionEntity;
        }
    
        public void setListPetitionEntity(List<PetitionEntity> listPetitionEntity) {
            this.listPetitionEntity = listPetitionEntity;
        }
    }

UserEntity

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.PrePersist;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.springframework.format.annotation.DateTimeFormat;

@Entity
@Table(name = "user")
public class UserEntity implements Serializable {

    private static final long serialVersionUID = 1L;
    
    // -- Attributes -- //
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "uername")
    private String user;

    @Column(name = "password")
    private String pass;

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

    @Column(name = "surname")
    private String surname;

    @Column(name = "enabled")
    private Boolean enabled;

    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(pattern = "dd/MM/yyyy hh:mm:ss")
    @Column(name = "created_at")
    private Date createdAt;

    @OneToMany(fetch = FetchType.LAZY)
    private List<PetitionEntity> listPetitionEntity;
    
    // -- Contructors -- //
    
    public UserEntity() {
        listPetitionEntity = new ArrayList<>();
    }
    
    // -- Methods -- //
    
    @PrePersist
    public void prePersist() {
        createdAt = new Date();
    }

    // -- Getters & Setters -- //

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPass() {
        return pass;
    }

    public void setPass(String pass) {
        this.pass = pass;
    }

    public String getName() {
        return name;
    }

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

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    public Boolean getEnabled() {
        return enabled;
    }

    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public List<PetitionEntity> getListPetitionEntity() {
        return listPetitionEntity;
    }

    public void setListPetitionEntity(List<PetitionEntity> listPetitionEntity) {
        this.listPetitionEntity = listPetitionEntity;
    }
}

PetitionEntity

    import org.springframework.format.annotation.DateTimeFormat;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@Entity
@Table(name = "petition")
public class PetitionEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    // -- Attributes -- //

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "device_id")
    private DeviceEntity device;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private UserEntity user;

    @Column(name = "switch_on")
    private Boolean switchOn;

    @Column(name = "temp_ref")
    private Float tempRef;

    @Column(name = "mode")
    private Integer mode;

    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(pattern = "dd/MM/yyyy hh:mm:ss")
    @Column(name = "time_to_off")
    private Date timeToOff;

    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(pattern = "dd/MM/yyyy hh:mm:ss")
    @Column(name = "created_at")
    private Date createdAt;

    // -- Methods -- //

    @PrePersist
    public void prePersist() {
        createdAt = new Date();
    }

    // -- Getters & Setters -- //

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public DeviceEntity getDevice() {
        return device;
    }

    public void setDevice(DeviceEntity device) {
        this.device = device;
    }

    public UserEntity getUser() {
        return user;
    }

    public void setUser(UserEntity user) {
        this.user = user;
    }

    public Boolean getSwitchOn() {
        return switchOn;
    }

    public void setSwitchOn(Boolean switchOn) {
        this.switchOn = switchOn;
    }

    public Float getTempRef() {
        return tempRef;
    }

    public void setTempRef(Float tempRef) {
        this.tempRef = tempRef;
    }

    public Integer getMode() {
        return mode;
    }

    public void setMode(Integer mode) {
        this.mode = mode;
    }

    public Date getTimeToOff() {
        return timeToOff;
    }

    public void setTimeToOff(Date timeToOff) {
        this.timeToOff = timeToOff;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }
}

I have this tables created in MySQL workbench. Here is the code:

Device table

    CREATE TABLE `device` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(80) DEFAULT NULL,
      `type` varchar(80) DEFAULT NULL,
      `model` varchar(80) DEFAULT NULL,
      `enabled` tinyint DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

User table

    CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `username` varchar(80) DEFAULT NULL,
      `password` varchar(80) DEFAULT NULL,
      `name` varchar(80) DEFAULT NULL,
      `surname` varchar(80) DEFAULT NULL,
      `enabled` tinyint DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      `uername` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Petition table

    CREATE TABLE `petition` (
      `id` int NOT NULL AUTO_INCREMENT,
      `user_id` int DEFAULT NULL,
      `device_id` int DEFAULT NULL,
      `switch_on` tinyint DEFAULT NULL,
      `temp_ref` float DEFAULT NULL,
      `mode` int DEFAULT NULL,
      `time_to_off` datetime DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_user_table_id_idx` (`user_id`),
      KEY `fk_device_table_id_idx` (`device_id`),
      CONSTRAINT `fk_device_table_id` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`),
      CONSTRAINT `fk_user_table_id` FOREIGN KEY (`user_id`) REFERENCES `db_domotic_house`.`user` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

This is my application.properties:

    spring.datasource.url=jdbc:mysql://localhost/domotic_house?serverTimezone=Europe/Madrid
    spring.datasource.username=root
    spring.datasource.password=sasa
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
    spring.jpa.generate-ddl=true
    spring.jpa.hibernate.ddl-auto=none
    logging.level.org.hibernate.SQL=debug

When I run the application in IntelliJ I can see in MySQL workbench that hibernate has created some new tables that I don't want.

This are the code of that tables:

device_list_petition_entity

    CREATE TABLE `device_list_petition_entity` (
      `device_entity_id` int NOT NULL,
      `list_petition_entity_id` int NOT NULL,
      UNIQUE KEY `UK_j196d5ebc0t1v3ak5xik9cfoc` (`list_petition_entity_id`),
      KEY `FKl9kjtuj8w6t07u9et7u8573hg` (`device_entity_id`),
      CONSTRAINT `FK5lupuetde9paakuy8b5g86vmb` FOREIGN KEY (`list_petition_entity_id`) REFERENCES `petition` (`id`),
      CONSTRAINT `FKl9kjtuj8w6t07u9et7u8573hg` FOREIGN KEY (`device_entity_id`) REFERENCES `device` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

user_list_petition_entity

    CREATE TABLE `user_list_petition_entity` (
      `user_entity_id` int NOT NULL,
      `list_petition_entity_id` int NOT NULL,
      UNIQUE KEY `UK_9ce6ulb7rn1mjpqesb0set963` (`list_petition_entity_id`),
      KEY `FKfk2tckk9hrc0krq0yka2bspnf` (`user_entity_id`),
      CONSTRAINT `FK5sturgl5us0uw35douhvpkug9` FOREIGN KEY (`list_petition_entity_id`) REFERENCES `petition` (`id`),
      CONSTRAINT `FKfk2tckk9hrc0krq0yka2bspnf` FOREIGN KEY (`user_entity_id`) REFERENCES `user` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I don't know what I'm doing wrong. Can someone help? Thanks!

I would set spring.jpa.generate-ddl to false since you don't want tables to be automatically generated. Hibernate seems to be generating join tables for oneToMany collections. I think those annotations need a mappedBy attribute to create a proper bi-directional mapping between the entities. See [this answer](https://stackoverflow.com/a/11938290/424903). — Gimby, Dec 08 '20 at 13:21
@Gimby I put mappedBy in one side and in another side I put JoinColumn and seems that it's working. Thanks for ur help! — ProRiderZ115, Dec 09 '20 at 19:49

Problem with relational tables MySQL. Spring Boot + Hibernate

0 Answers0