1

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.

Improve this question
5
  • Can you add tag DBMS you use? Commented Aug 11, 2021 at 11:32
  • @doctorgu I added it Commented Aug 11, 2021 at 12:12
  • If I were you, I will make List into comma separated string. And insert all string at once. You can use string_split if your SQL Server version is 2016 or later, or you can use custom Split function like here. stackoverflow.com/questions/5493510/… I've got 10 to 30 times faster query after changed all list to comma separated string and insert it as one query. Commented Aug 11, 2021 at 12:53
  • @doctorgu but on the second insert, 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? Commented Aug 11, 2021 at 13:09
  • I answered. If your case is different with my answer, please let me know it. Commented Aug 11, 2021 at 13:44

2 Answers 2

Reset to default
0

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

is this a better option than native queries?
Even if you use native queries you would execute n number of inserts if you loop through your list of entities. Keeping down the number of roundtrips to the database is usually recommended if you're trying to improve your performance. Batch inserts is one way. Since you're using JPA then I think it's easier for you do do that with entities and a saveAll on a repository. If you want more control and dynamically create your inserts then perhaps spring jdbc is a better option stackoverflow.com/questions/9565481/…
I already use Hibernate and jpareository. Can we create a dynamic bulk insert via native query?
0

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

Improve this answer

8 Comments

Can I execute this with entity manager in spring boot?
is there a way to get the id of inserted record from a native query in hibernate?
@xarqt Yes, you can execute it with EntityManager I found example here baeldung.com/hibernate-entitymanager#5-querying-for-entities or with with JdbcTemplate stackoverflow.com/a/40958508/2958717
@xarqt Yes, you can get the id of inserted, just selecting from @IdList like select Id from @IdList will return all Id. Because this SQL is not stored procedure, you can run it dynamically.
can you check please the Edit: I added a new piece of code with the error that I'm getting if you can help me
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.