Unable to identify which record is causing oracle exception Spring DATA JPA

I have a method which is annotated with @Transactional and we are making a bulk update using save(Iterable entities) method of SimpleJPARepository class of spring-data-jpa library. I have a scenario where i have nearly 20000 records are being processed by this method. There seems to be few entities which are not well formed in terms of datatypes as oracle column expects. For example : an attribute first name of entity has its value as "1234" ( which is not accepted by oracle DB as datatype in DB is VARCHAR ).

Because few records of 20000 records have this issue, save(Iterable entities) method throws "java.sql.SQLSyntaxErrorException: ORA-01722: invalid number" exception and doesnt save any of 20000 records. Is there a way i can identify which records are causing this exception and exclude them to update in the database?

I see there are couple of options like validation in DOMAIN classes( which is not very feasible for me as i have lot of Domain classes and changing them is going to have a lot of impact for my application). One more option i see is using "JPA batch inserts with Hibernate". Again , even with this batch inserts approach i am not sure if i can get information on records which are causing the exception.

Any suggestions on this issue will be of great help.

asked Nov 13 '18 at 19:32

poorna chandra

have you tried using save(S entity); in a try block and log the objects which cause the exception in the catch block?

– Robert Niestroj
Nov 13 '18 at 20:13

thanks for the reply . I didnot try using that method save(S entity); as number of records my application processes is very high. Generally my application processes 20k to 30k records. I felt it is not good in terms of performance to hit DB that number of times individually. Correct me if i am wrong

– poorna chandra
Nov 13 '18 at 20:24

saveAll does the looping on the iterable internally, so it is the same performance wise or even worse because it returns a List of the saved elements, whereas doing save (S entity) you dont need to store the saved entities in a List. Look at the source code: github.com/spring-projects/spring-data-jpa/blob/master/src/main/… If you care about performance you should look at batch operations.

– Robert Niestroj
Nov 14 '18 at 8:09

add a comment |

Any suggestions on this issue will be of great help.

asked Nov 13 '18 at 19:32

poorna chandra

have you tried using save(S entity); in a try block and log the objects which cause the exception in the catch block?

– Robert Niestroj
Nov 13 '18 at 20:13

thanks for the reply . I didnot try using that method save(S entity); as number of records my application processes is very high. Generally my application processes 20k to 30k records. I felt it is not good in terms of performance to hit DB that number of times individually. Correct me if i am wrong

– poorna chandra
Nov 13 '18 at 20:24

saveAll does the looping on the iterable internally, so it is the same performance wise or even worse because it returns a List of the saved elements, whereas doing save (S entity) you dont need to store the saved entities in a List. Look at the source code: github.com/spring-projects/spring-data-jpa/blob/master/src/main/… If you care about performance you should look at batch operations.

– Robert Niestroj
Nov 14 '18 at 8:09

add a comment |

Any suggestions on this issue will be of great help.

asked Nov 13 '18 at 19:32

poorna chandra

Any suggestions on this issue will be of great help.

oracle spring-data-jpa

asked Nov 13 '18 at 19:32

poorna chandra

asked Nov 13 '18 at 19:32

poorna chandra

asked Nov 13 '18 at 19:32

poorna chandra

asked Nov 13 '18 at 19:32

poorna chandra

asked Nov 13 '18 at 19:32

poorna chandra

have you tried using save(S entity); in a try block and log the objects which cause the exception in the catch block?

– Robert Niestroj
Nov 13 '18 at 20:13

thanks for the reply . I didnot try using that method save(S entity); as number of records my application processes is very high. Generally my application processes 20k to 30k records. I felt it is not good in terms of performance to hit DB that number of times individually. Correct me if i am wrong

– poorna chandra
Nov 13 '18 at 20:24

saveAll does the looping on the iterable internally, so it is the same performance wise or even worse because it returns a List of the saved elements, whereas doing save (S entity) you dont need to store the saved entities in a List. Look at the source code: github.com/spring-projects/spring-data-jpa/blob/master/src/main/… If you care about performance you should look at batch operations.

– Robert Niestroj
Nov 14 '18 at 8:09

add a comment |

have you tried using save(S entity); in a try block and log the objects which cause the exception in the catch block?

– Robert Niestroj
Nov 13 '18 at 20:13

thanks for the reply . I didnot try using that method save(S entity); as number of records my application processes is very high. Generally my application processes 20k to 30k records. I felt it is not good in terms of performance to hit DB that number of times individually. Correct me if i am wrong

– poorna chandra
Nov 13 '18 at 20:24

saveAll does the looping on the iterable internally, so it is the same performance wise or even worse because it returns a List of the saved elements, whereas doing save (S entity) you dont need to store the saved entities in a List. Look at the source code: github.com/spring-projects/spring-data-jpa/blob/master/src/main/… If you care about performance you should look at batch operations.

– Robert Niestroj
Nov 14 '18 at 8:09

have you tried using save(S entity); in a try block and log the objects which cause the exception in the catch block?

– Robert Niestroj
Nov 13 '18 at 20:13

thanks for the reply . I didnot try using that method save(S entity); as number of records my application processes is very high. Generally my application processes 20k to 30k records. I felt it is not good in terms of performance to hit DB that number of times individually. Correct me if i am wrong

– poorna chandra
Nov 13 '18 at 20:24

saveAll does the looping on the iterable internally, so it is the same performance wise or even worse because it returns a List of the saved elements, whereas doing save (S entity) you dont need to store the saved entities in a List. Look at the source code: github.com/spring-projects/spring-data-jpa/blob/master/src/main/… If you care about performance you should look at batch operations.

– Robert Niestroj
Nov 14 '18 at 8:09

add a comment |

1 Answer
1

active

oldest

votes

What you seemingly trying to do is let the database validate your data.

I consider this a bad idea in general but it doesn't really work with JPA.

Even though you stated that you don't want to fix your broken domain model, this is exactly what you should do:

change the entities so that the types used do match those used in the database. Note that attempting to store a value of "1234" or 1234 in a VARCHAR or VARCHAR2 column won't trigger the exception ORA-01722: invalid number because nothing is trying to convert anything to a number and even if it would, both values are a number or easily parsable into a number.

add validation to ensure those constraints that can't be expressed by types (length of Strings for example).

The alternative would be a process like the following:

Persist a batch of entities.

If it succeeds commit. Done.

If it does not succeed split the batch into smaller batches and repeat from 1.

This is ugly and slow and might help to find all the constraints you need to check but it is not a good way to filter out those records that don't play nice with you database.

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53288273%2funable-to-identify-which-record-is-causing-oracle-exception-spring-data-jpa%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

What you seemingly trying to do is let the database validate your data.

I consider this a bad idea in general but it doesn't really work with JPA.

Even though you stated that you don't want to fix your broken domain model, this is exactly what you should do:

change the entities so that the types used do match those used in the database. Note that attempting to store a value of "1234" or 1234 in a VARCHAR or VARCHAR2 column won't trigger the exception ORA-01722: invalid number because nothing is trying to convert anything to a number and even if it would, both values are a number or easily parsable into a number.

add validation to ensure those constraints that can't be expressed by types (length of Strings for example).

The alternative would be a process like the following:

Persist a batch of entities.

If it succeeds commit. Done.

If it does not succeed split the batch into smaller batches and repeat from 1.

This is ugly and slow and might help to find all the constraints you need to check but it is not a good way to filter out those records that don't play nice with you database.

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

add a comment |

What you seemingly trying to do is let the database validate your data.

I consider this a bad idea in general but it doesn't really work with JPA.

Even though you stated that you don't want to fix your broken domain model, this is exactly what you should do:

change the entities so that the types used do match those used in the database. Note that attempting to store a value of "1234" or 1234 in a VARCHAR or VARCHAR2 column won't trigger the exception ORA-01722: invalid number because nothing is trying to convert anything to a number and even if it would, both values are a number or easily parsable into a number.

add validation to ensure those constraints that can't be expressed by types (length of Strings for example).

The alternative would be a process like the following:

Persist a batch of entities.

If it succeeds commit. Done.

If it does not succeed split the batch into smaller batches and repeat from 1.

This is ugly and slow and might help to find all the constraints you need to check but it is not a good way to filter out those records that don't play nice with you database.

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

add a comment |

What you seemingly trying to do is let the database validate your data.

I consider this a bad idea in general but it doesn't really work with JPA.

Even though you stated that you don't want to fix your broken domain model, this is exactly what you should do:

change the entities so that the types used do match those used in the database. Note that attempting to store a value of "1234" or 1234 in a VARCHAR or VARCHAR2 column won't trigger the exception ORA-01722: invalid number because nothing is trying to convert anything to a number and even if it would, both values are a number or easily parsable into a number.

add validation to ensure those constraints that can't be expressed by types (length of Strings for example).

The alternative would be a process like the following:

Persist a batch of entities.

If it succeeds commit. Done.

If it does not succeed split the batch into smaller batches and repeat from 1.

This is ugly and slow and might help to find all the constraints you need to check but it is not a good way to filter out those records that don't play nice with you database.

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

What you seemingly trying to do is let the database validate your data.

I consider this a bad idea in general but it doesn't really work with JPA.

Even though you stated that you don't want to fix your broken domain model, this is exactly what you should do:

change the entities so that the types used do match those used in the database. Note that attempting to store a value of "1234" or 1234 in a VARCHAR or VARCHAR2 column won't trigger the exception ORA-01722: invalid number because nothing is trying to convert anything to a number and even if it would, both values are a number or easily parsable into a number.

add validation to ensure those constraints that can't be expressed by types (length of Strings for example).

The alternative would be a process like the following:

Persist a batch of entities.

If it succeeds commit. Done.

If it does not succeed split the batch into smaller batches and repeat from 1.

This is ugly and slow and might help to find all the constraints you need to check but it is not a good way to filter out those records that don't play nice with you database.

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

answered Nov 14 '18 at 5:50

Jens Schauder

46.8k17115240

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Pfthb