CODEWITHSUNDEEP
javasqldatabaseoracle-databaseerror-handling
jompi
jompi

Reputation: 360

How to check if I'm inserting in db from java

just a guide here, please!

I want to insert some values in my db, from java I have my oracle prepared statement and stuff and it inserts ok, but my other requirement it's to send via email the fields that for some reason werent insert.

So, Im thinking making my method as int and return like 0 if theres an error 1 if its ok, 2 if some fields didn't insert ... etc... BUT

I'm lost figuring how to bind it, I mean if there's an error in the catch from sql in java i can put like 

catch (SQLException e) {// TODO Auto-generated catch block 
            e.printStackTrace();
            return result = 0;

And obviously i can put an error message and stuff where i call it, but how will i know in which item stops and then retrieve the rest of the fields that weren't insert...

any idea? anyone has an example?

Thanks in advance

Upvotes: 1

Views: 951

Answers (3)

Sachin Thapa
Sachin Thapa

Reputation: 3709

First of all try to do as much validations possible in Java and identify the bad records before persisting them in database, you can catch those records before they hit database and send email.

In case you are worried about db level issues e.g. contrainst etc, then you need to choose Performance vs Flexibile Features

Option1 : Dont use batch insert one record at a time, whatever fails you can send email, this is bad from performance perspective but will solve your purpose

Option2 : Divide your records into batches of 50 or 100, whatever batch fails you can report that whole batch failed, this will also report some good records, but you will have information about what was not persisted.

Option3 : Divide your records into batches, if a batch fails try saving one record at a time for that particular batch, that ways you will be able to identify bad record and performance will be optimized, this will require more coding and testing.

So, you can choose what ever suits you, but I would recommend following:

  1. Do maximum validations in Java
  2. Split into smaller batches and use Option 3.

Cheers !!

Upvotes: 1

Omar Mainegra
Omar Mainegra

Reputation: 4184

If the statement failed then none of your field will be inserted, because oracle internally does a rollback to maintain consistency in your data.

Upvotes: 0

Pete B.
Pete B.

Reputation: 3276

When you have an insert you insert all fields included in the statement; or, none. So if throw an exception when running your statement, then you would send an email.

If no exception is thrown then you would assume that it has worked.

Alternatively, after running the insert, you could then attempt to do a select to check to see if the data you inserted was actually there.

Upvotes: 0

Related Questions