hibernate native query inserting records in a loop with many-to-one relationship

Question

I'm working on a spring boot project, there I have two tables that are related to each other with OneToMany relationship

public class PackGroupEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String description;
    private Double qty;
    private Integer packingNr;
    @Temporal(TemporalType.TIMESTAMP)
    private Date deliveredTime;
    @OneToMany(mappedBy = "packGroup", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    private List<PackArticlesEntity> packArticles= new ArrayList<>();
}

And

public class PackArticlesEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Double qty;
    private Double confirmedName;
    @Enumerated(EnumType.STRING)
    private ArticleStatus status;
    private Double weight;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "pack_group_id")
    private PackGroupEntity packGroup;
}

And I insert data on these two tables in a loop, first I group the articles by packing number for which I will create a PackGroup that has a list of articles:

Map<Integer, List<RecivedArticlesDTO >> groupedArticles =
                    recivedArticlesListDTO.stream().collect(Collectors.groupingBy(RecivedArticlesDTO::getPackingNr));
    for (Map.Entry<Integer, List<RecivedArticlesDTO>> entry : groupedArticles.entrySet()) {

        List<RecivedArticlesDTO > groups = entry.getValue();
        PackGroupEntity packGroup = new PackGroupEntity();
        packGroup.setPackingNr(entry.getKey())
        //some manipulations and setting data for each field 

       List<PackArticlesEntity> listWithArticles = new ArrayList<>();
       groups.forEach(pack -> {
            PackArticlesEntity packArticle= new PackArticlesEntity();
            packArticles.setQty(pack.getQty);
            //some manipulations and setting data for each field 
            listWithArticles.add(packArticles);
      }
      packGroup.setPackArticles(listWithArticles);

      // here I have to save the data using native query 
     packGroupService.savePackGroupData(packGroup);
}

In this way, it is so slow so I wanted to do it on the native query. The problem is that I have many packGroups a many packArticles that needs to be saved. I was thinking to somehow do only one connection with DB to send the list of pack groups and pack articles to save, but I don't know how to do this on native queries. This here is just for one pack_group but yet I don't know how to pass packArticles on a native query since it's a list

@Query(value = "insert  into pack_group " +
        "        (id,packing_nr, description, qty, deliveredTime, packArticles) " +
        "       values (1?, 2?, 3?, 4?, 5?, 6?)", nativeQuery = true)
void savePackGroupData(id, packing_nr, description, qty, packArticles);

Can someone help with this, please?

EDIT: I want to return the id from insert on

    String query = String.format("insert into pack_group(group, remark, description ) " +
 "values ( %s, %s, %s)", "x","y","z"  );
    Query q = entityManager.createNativeQuery(query );
    BigInteger biid = (BigInteger) q.getSingleResult();
    long id = biid.longValue();

And I get this error com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

Answer 1

To speed things up consider using batch updates. It can be a bit tricky with spring data to get it to work, but it do speed things up considerably when working.

See How to do bulk (multi row) inserts with JpaRepository?

If you wish more control over your insert statements then perhaps spring jdbc is a better option :How to do multiple inserts in database using spring JDBC Template batch?

Answer 2

This is an answer for 'I have a list of articles in which I have to use the reference id of the previous inserted pack group. Can you get that on SQL?' in comment.

You have Main and Detail table. And you want to insert Main first, and Detail second with Id of Main just inserted.

You can use output inserted of SQL Server to get only currently inserted rows. And you can join it with string_split result to insert into Detail table.

Here's example.

create table Main (
    Id int identity(1, 1),
    Name nvarchar(50)
);
create table Detail (
    MainId int,
    Name nvarchar(50)
);
insert into Main (Name) values ('X'); -- To make new inserted Id starts with 2

declare @MainList nvarchar(1000) = 'A,B,C';
declare @DetailList nvarchar(1000) = 'A2,B2,C2';

declare @IdList table (
    Seq int identity(1, 1),
    Id int
);

-- Insert 3 rows, get all 3 Id using inserted, insert 3 Id to @IdList table
insert into Main (Name)
output inserted.Id into @IdList
select value from string_split(@MainList, ',');

-- Join @IdList table with string_split returned table with Seq
-- Seq of @IdList is auto generated by identity(1, 1)
-- Seq of string_split returned table generated by row_number()
insert into Detail (MainId, Name)
select m.Id MainId, d.value Name
from   @IdList m
       inner join
       (select row_number() over (order by (select 1)) Seq, value
       from    string_split(@DetailList, ',')
       ) d
       on m.Seq = d.Seq;

Result:

select * from Main;
select * from Detail;

Id  Name
--------
1   X
2   A
3   B
4   C

MainId  Name
------------
2   A2
3   B2
4   C2

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=30213ab0cd59fcb7f541c18c738d4dad