save file and his data at the same time with the foreign key of the file

42
April 07, 2021, at 11:00 AM

i'm new here and i have a problem for saving a file and his data at the same time with the foreign key.

so i have to table, the first is bordereau and the second is bordDetails.

the first is for saving file such as his name and his type and his date.

and the second for saving file details and it's contain the foreign key for Borderau the first table.

this is the code for the first entity :

@Entity
@Data
@NoArgsConstructor
@EqualsAndHashCode
@Builder
@AllArgsConstructor
@Table(name="Bordereau")
public class Bordereau extends Auditable<String> implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    
    @Column(name="Nom")
    private String nom;
    
    @Column(name="DateRemise")
    private Date DateRemise;
    
    @Column(name="Type")
    private String Type;
    
    @Column(name="versionAc")
    private String VersionAc;
    
    
    
    @ManyToOne
    @JoinColumn(name="Code_affaire", referencedColumnName = "code")
    private Affaire affaire;
    
    
    
    @OneToMany(targetEntity = BordDetail.class,fetch = FetchType.LAZY ,mappedBy = "bordereau")
    private List<BordDetail> ListInfFile;
    
    
    public List<BordDetail> getListInfFile() {
        return ListInfFile;
    }
    public void setListInfFile(List<BordDetail> listInfFile) {
        ListInfFile = listInfFile;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getNom() {
        return nom;
    }
    public void setNom(String nom) {
        this.nom = nom;
    }
    public Date getDateRemise() {
        return DateRemise;
    }
    public void setDateRemise(Date dateRemise) {
        DateRemise = dateRemise;
    }
    public String getType() {
        return Type;
    }
    public void setType(String type) {
        Type = type;
    }
    public String getVersionAc() {
        return VersionAc;
    }
    public void setVersionAc(String versionAc) {
        VersionAc = versionAc;
    }
    
    public Affaire getAffaire() {
        return affaire;
    }
    public void setAffaire(Affaire affaire) {
        this.affaire = affaire;
    }
    
    
}

the second entity :

@Entity
@Data
@AllArgsConstructor
@EqualsAndHashCode
@Builder
@NoArgsConstructor
@Table(name = "BordereauDetail")
public class BordDetail implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    
    @Column(name = "Type_ligne")
    private String Typeligne;
    
    
    @Column(name = "Num_Prix")
    private int NumPrix;
    
    @Column(name = "version")
    private int version;
    
    @Column(name = "Designation")
    private String Designation;
    
    @Column(name = "Quantite")
    private int Quantite;
    
    @Column(name = "Prix_Unitaire")
    private double PU;
    
    @Column(name = "Niveau")
    private int Niveau;
    
    @Column(name = "EtatLigne")
    private String EtatLigne;
    
    @Column(name = "Num_Ligne")
    private int NLigne;
    
    @Column(name = "Chiffrer_Par")
    private String ChiffrerPar;
    
    @Column(name = "Unité")
    private String Unité;
    
    @ManyToOne(targetEntity = Bordereau.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "id_bordreau")
    private Bordereau bordereau;
    
    
    public BordDetail(int id,String typeligne, int numPrix,String unité, String designation, int quantite, double pU, int niveau,
            int nLigne, Bordereau bordereau) {
        super();
        this.id = id;
        Typeligne = typeligne;
        NumPrix = numPrix;
        Unité = unité;
        Designation = designation;
        Quantite = quantite;
        PU = pU;
        Niveau = niveau;
        NLigne = nLigne;
        this.bordereau = bordereau;
    }
    public BordDetail() {}
    
    
    
    public String getUnité() {
        return Unité;
    }
    public void setUnité(String unité) {
        Unité = unité;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getTypeligne() {
        return Typeligne;
    }
    public void setTypeligne(String typeligne) {
        Typeligne = typeligne;
    }
    public int getNumPrix() {
        return NumPrix;
    }
    public void setNumPrix(int numPrix) {
        NumPrix = numPrix;
    }
    public int getVersion() {
        return version;
    }
    public void setVersion(int version) {
        this.version = version;
    }
    public String getDesignation() {
        return Designation;
    }
    public void setDesignation(String designation) {
        Designation = designation;
    }
    public int getQuantite() {
        return Quantite;
    }
    public void setQuantite(int quantite) {
        Quantite = quantite;
    }
    public double getPU() {
        return PU;
    }
    public void setPU(double pU) {
        PU = pU;
    }
    public int getNiveau() {
        return Niveau;
    }
    public void setNiveau(int niveau) {
        Niveau = niveau;
    }
    public String getEtatLigne() {
        return EtatLigne;
    }
    public void setEtatLigne(String etatLigne) {
        EtatLigne = etatLigne;
    }
    public int getNLigne() {
        return NLigne;
    }
    public void setNLigne(int nLigne) {
        NLigne = nLigne;
    }
    public String getChiffrerPar() {
        return ChiffrerPar;
    }
    public void setChiffrerPar(String chiffrerPar) {
        ChiffrerPar = chiffrerPar;
    }
    public Bordereau getBordereau() {
        return bordereau;
    }
    public void setBordereau(Bordereau bordereau) {
        this.bordereau = bordereau;
    }
    
}

and my Excel code is :

public class ExcelUtils {
    
    @Autowired
    private BordereauRepository bordRepo;
    
    public static List<BordDetail> parseExcelFile ( InputStream is){
        
        try {
            Workbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheet("Bordereau Detail");
            
            Iterator<Row> rows = sheet.iterator();
            
            List<BordDetail> ListInfFile = new ArrayList<BordDetail>();
            
            
            
            int rowNumber = 0;
            while (rows.hasNext()) {
                Row currentRow = rows.next();
                // skip header
                if (rowNumber == 0) {
                    rowNumber++;
                    continue;
                }
                
                Iterator<Cell> cellsInRow = currentRow.iterator();
                
                BordDetail bDetail = new BordDetail();
                
                
                
                int cellIndex = 0;
                
                while (cellsInRow.hasNext()) {
                    Cell currentCell = cellsInRow.next();
                    
                    if (cellIndex == 0) { //Typeligne
                        bDetail.setTypeligne(currentCell.getStringCellValue());
                    } else if (cellIndex == 1) { // NumPrix
                        bDetail.setNumPrix((int)currentCell.getNumericCellValue());
                    } else if (cellIndex == 2) { // Designation
                        bDetail.setDesignation(currentCell.getStringCellValue());
                        
                    } else if (cellIndex == 3) { // Quantite
                        bDetail.setQuantite((int) currentCell.getNumericCellValue());
                    }else if (cellIndex == 4) { // Prix Unitaire
                        bDetail.setPU((double) currentCell.getNumericCellValue());
                    }else if (cellIndex == 5) { // Niveau
                        bDetail.setNiveau((int) currentCell.getNumericCellValue());
                        
                    }else if (cellIndex == 6) { // Num Ligne
                        bDetail.setNLigne((int) currentCell.getNumericCellValue());
                    }else if (cellIndex == 7) { // Unité
                    bDetail.setUnité(currentCell.getStringCellValue());
                    }else {
                        bDetail.setBordereau(null);
                    }
                    cellIndex++;
                }
                
                
                ListInfFile.add(bDetail);
                
            }
            //close workbook
            workbook.close();
            
            
            
            return ListInfFile;
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
}

and my service to save is :

@Service
public class BordereauStorageService {
    @Autowired
    private BordereauRepository bdRepo;
    
    @Autowired
    private BordDetailRepository bDetRepo;
    
    public Bordereau storeFile(String type, String dateremise,MultipartFile file) throws ParseException, IOException {
        
        String filename = StringUtils.cleanPath(file.getOriginalFilename());
        DateFormat sourceFormat = new SimpleDateFormat("yyyy-mm-dd");
        
        
            Bordereau br = new Bordereau();
            
            
            br.setType(type);
            br.setDateRemise(sourceFormat.parse(dateremise));
            br.setNom(filename);
            
            
            
            
            return bdRepo.save(br);
        }
    
    public List<Bordereau> getFiles(){
        return bdRepo.findAll();
    }
    public void store( MultipartFile file) {
        try {
            
            List<BordDetail> bordDetails = ExcelUtils.parseExcelFile(file.getInputStream());
        
            bDetRepo.saveAll(bordDetails);
            
        } catch (IOException e) {e.printStackTrace();}
    }

and this is the tables in database

    create table bordereau_detail (
       id int identity not null,
        chiffrer_par varchar(255),
        designation varchar(255),
        etat_ligne varchar(255),
        num_ligne int,
        niveau int,
        num_prix int,
        prix_unitaire double precision,
        quantite int,
        type_ligne varchar(255),
        unité varchar(255),
        version int,
        id_bordreau int,
        primary key (id)
    )
    create table bordereau (
       id int identity not null,
        creer_par varchar(255),
        creer_le datetime2,
        modifier_par varchar(255),
        modifier_le datetime2,
        date_remise datetime2,
        type varchar(255),
        version_ac varchar(255),
        nom varchar(255),
        code_affaire varchar(255),
        primary key (id)
    )
alter table bordereau_detail 
       add constraint FKy1es51vhwsriwpydineskka6 
       foreign key (id_bordreau) 
       references bordereau

my question is how can i be able to save the foreign key "id_borderau"?

thanks for your help :)

READ ALSO
JavaScript execution works on Chrome and not WordPress

JavaScript execution works on Chrome and not WordPress

I am running this code in Chrome console dev tools and it works:

30
Rows until a number reaches zero [Python]

Rows until a number reaches zero [Python]

I've been trying to find a solution to this problem for days, to no availOur warehouse asked for a way to calculate how many days it takes to process the incoming items, according to our forecast

61