java sql insertion without duplication

Question

I am new to write sql using java. I can create the tables like this:

String queryCreateBookTable = "create table Book (" + 
    "isbn char(13) not null," + 
    "author char(30)," + 
    "title char(30)," +
    "subject char(30)," +
    "primary key(isbn))";

And I want to insert some rows from a given array which contains many rows. So I tried this code and it isn't working. How can I fix it without duplicate of primary key and null situation?

public int insertBook(Book[] books)
{
    int result = 0;
    int art=0;
    String query = null;
    String[] depo=null;
    while(books[art].getIsbn().length() == 13 )
    {
        if(art==0)
        {
            depo[0]=books[0].getIsbn();
        }
        for(int i=0;i<art;i++)
        {
            if(books[art].getIsbn() ==depo[i])
                result++;
            else
                result=0;
        }
        if(result==0)
        {
            query= "insert into Book values ('" + 
            books[art].getIsbn()+ "','" + 
            books[art].getAuthor() + "','" + 
            books[art].getTitle() + "','" + 
            books[art].getSubject() +   "')";
        }
    }
}

isn't it how it works in the first place? when you insert a duplicate isbn, it just shouldn't insert — vlatkozelka, Dec 23 '15 at 16:57
Do you mean to construct a query where you specify only certain fields for the insert, not all of them? — Adam Evans, Dec 23 '15 at 16:58
I dont want to insert a row whose isbn is already exist in the query — , Dec 23 '15 at 17:00
@YavuzSelimKartal isbn is primary, the insertion would just fail. — vlatkozelka, Dec 23 '15 at 17:02

score 1 · Answer 1 · answered Dec 23 '15 at 17:00

1

you want unique ISBNs, so you can:

perform the insert, let it fail, catch the SQLException (ignore the duplicate) and keep going (probably expensive)
pre-filter the collection. For example use a java.util.Set to remove duplicate ISBNs (brittle approach)
check before hand if the ISBN already exists in the db. Basically do a select first and do the insert only if you get an empty ResultSet. (probably best)

answered Dec 23 '15 at 17:00

user2560528

105
6

exactly what I meant in my comment, just let the insertion fail , it's primary key anyways – vlatkozelka Dec 23 '15 at 17:01

score 0 · Answer 2 · edited May 23 '17 at 12:23

Your requirement is insert if not exists. You can use insert ignore.

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

If primary exists and you want to update if it exists you can use insert on duplicate key update as described in the above link.

For checking null condition, you have to put null checks using if condition and if it is null then skip the current record and continue to the next record

java sql insertion without duplication

2 Answers2