[FIXED] Right way to make insertion of an entity + foreign key on database(Use : Mysql, JPA)

Issue

I have a question about the right way to make insertion of my entity + foreign key on database(MySQL, JPA). Before asking my question I tried solutions that I found, and I spent days to resolve the problem by myself. I believe I have something wrong :

What I need to do is :

1. I have already data (informations) exists in Site table
2. I have to make the insertion of new sequence in Sequence table
3. I have to make the insertion of new sequence in Sequence table, and association of FK site's (site exists and no need to inserting it again)

The main problem is :

1. When I insert in Sequence Table I have Sequence with FK of Site inserted correctly
2. BUT! The problem is I have also Site insertion in database, so redundant insertion of Site evry time I insert Sequence datas

I just need one insertion of Sequence with association (FK of Site).

Below what's happen on my console :

Hibernate: insert into Sites (Current_Sequence, Date_Insert, Date_Update, Enable, Nom_Site, User_Update, applicationService_Id_SA, partner_Id_Partner, Id_Site) values (?, ?, ?, ?, ?, ?, ?, ?, ?)


Hibernate: insert into Sequences (Date_Insert_timestamp, Date_Update_timestamp, Nbr_PDF, Nbr_ZPL, Size_PDF, Size_ZPL, TimestampMinute, User_Update, site_Id_Site, status, Id_Sequence) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)


Hibernate: insert into Sites_Sequences (Site_Id_Site, sequences_Id_Sequence) values (?, ?)

Jpa Repository : I first get Site object by name

    @Override
        public Site findSiteByName(String Name_Site) {
            List sites = entityManager.createQuery("SELECT s FROM Site s").getResultList();
            for (Site item : sites) {
                if (item.getNom_Site().equals(Name_Site)) {
                    return item;
                }
            }
    
            return null;
        }

Jpa method to insert Sequence on data base

@Override
    public Sequence saveSequence(Sequence sequence) {

        return super.save(sequence);
    }

My Rest controller :

@GET
@Path("/addSeq")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.APPLICATION_JSON)
public Sequence addSeq(@Context final UriInfo uriInfo) {
    Site site = siteService.findSiteByName("Name_Site");
    Sequence sequence = sequenceFactory.CreateSequence(new Date(), new Date(), "", "", "", 4, 6, 10, 12);
    sequence.setSite(site);
    site.getSequences().add(sequence);
    sequenceservice.saveSequence(sequence);
    return null;
}

My principal classes are :

@Entity
@Table(name = "Sequences")
public class Sequence {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "Id_Sequence", unique = true, nullable = false)
private long Id_Sequence;
private Date ....;
private Date ....;
private String ....;
private String ....;
private String ....;
private int ....;
private int ....;
private double ....;
private double ....;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<TraceSequence> traceSequences = new HashSet<>();
@ManyToOne(cascade = CascadeType.ALL)
private Site site;

public constructor...

public set..
public get..

   
@Entity
@Table(name = "Sites")
public class Site  {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "Id_Site", unique = true, nullable = false)
private long Id_Site;
private String ...;
private boolean ...;
private long ...; 
private Date ...;
private Date ...;
private String ...;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<Sequence> sequences = new HashSet<>();

@ManyToOne
private ... ...;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<...> ... = new HashSet<>();

@ManyToOne
private ... ...;

public constructor...

public set..
public get..

Solution

Could you try to remove the cascade = CascadeType.ALL in all of the relations? I think the main problem is that CascadeType.PERSIST is cascaded to the child elements. You may have a look here JPA with JTA: Persist entity and merge cascaded child entities

Another thing I would propose is to reduce your table count from 3 to 2. Since you are using a OneToMany-relation, which doesn't need a mapping table like you have now. To achieve this a mappedBy-clause is used within the OneToMany-annotation and an additional JoinColumn-annotation on the ManyToOne site.

@Entity
@Table(name = "Sites")
public class Site {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "Id_Site", unique = true, nullable = false)
    private long Id_Site;
    private String ...;
    private boolean ...;
    private long ...; 
    private Date ...;
    private Date ...;
    private String ...;

    @OneToMany(mappedBy="site", fetch = FetchType.EAGER)
    private Set<Sequence> sequences = new HashSet<>();


@Entity
@Table(name = "Sequences")
public class Sequence {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "Id_Sequence", unique = true, nullable = false)
    private long Id_Sequence;
    private Date ....;
    private Date ....;
    private String ....;
    private String ....;
    private String ....;
    private int ....;
    private int ....;
    private double ....;
    private double ....;

    @OneToMany(fetch = FetchType.EAGER)
    private Set<Sequence> traceSequences = new HashSet<>();

    @ManyToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "site_Id_Site", nullable = false)
    private Site site;

EDITED I tried with:

public interface SequenceRepository extends JpaRepository<Sequence, Long> {

}

and

Site site = siteRepository.findOne(1L);

Sequence seq = new Sequence();
seq.setSite(site);
site.getSequences().add(seq);

sequenceRepository.save(seq);

brings

Hibernate: select site0_.id_site...
Hibernate: insert into sequences (site_id_site) values (?)

In classpath I do have spring-data-rest-webmvc-2.5.6.RELEASE.jar.

Answered By - KLHauser