wood burning stoves 2.0*
The moose likes JDBC and the fly likes Problem Insert where not exists Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem Insert where not exists" Watch "Problem Insert where not exists" New topic
Author

Problem Insert where not exists

wafa khan
Ranch Hand

Joined: Apr 29, 2013
Posts: 42
Hi all of you
I have code to insert into table sql so it works but when i execute many times it will be inserted the same data then i have repetition
So i want do an query for Insert into table where not exists
i use it in many queries it works but here i have the insert from arraylist<>
i work with java

this is the code :


VLAN= it's the first column in table but not primary key
and table has 5 columns
VLAN,desc,jr,vrf,address
Thank you for help
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3455
    
  47

What exactly is your problem? I didn't try, but I believe this could work. However, there are few possible modifications:

1) Pass in the srv.getvlan() as a parameter too (you need to pass it twice, of course). As it apparently changes in every iteration, you're not actually using the performance improvement of the PreparedStatement; as SQL of every executed statement differs. And, of course, your code is possibly prone to SQL injection this way.

2) Use MERGE statement. It will probably work very similarly to your current construct, but is (in my opinion) more understandable.

3) If you're really concerned about performance and insert a lot of rows this way you could do this:
  • create a global temporary table in your schema to keep the data you'll be inserting (columns VLAN,desc,jr,vrf,address).
  • insert all data into the temporary table using JDBC batching and fully parametrized (see the first point) PreparedStatement. Make sure you're using Oracle JDBC driver which comes with Oracle 11g, since the performance of JDBC batching was improved there (before that version you'd have to use Oracle's specific statement batching to get the best performance - it's easier to use, in my opinion, but not supported by the JDBC standard).
  • merge all of these data into the target table at once using the MERGE command mentioned above.
  • wafa khan
    Ranch Hand

    Joined: Apr 29, 2013
    Posts: 42
    Hi thank you for replayin but i don't understand what you mean
    i change code at this query but they give me this error:

    java.sql.SQLException: Column count doesn't match value count at row 1

    And this is the code:


    I execute program many times so every time the same data will be inserted i have repetition for that i want use Insert where not exists

    Martin Vajsar
    Sheriff

    Joined: Aug 22, 2010
    Posts: 3455
        
      47

    wafa khan wrote:Hi thank you for replayin but i don't understand what you mean
    i change code at this query but they give me this error:

    java.sql.SQLException: Column count doesn't match value count at row 1

    Did the code in your first post produce the same error?

    In any case, please post the stack trace (use printStackTrace() method of the SQLException to obtain it). Make sure the code you posted here contains the line where the error occurred, and let us know which line it was.

    I execute program many times so every time the same data will be inserted i have repetition for that i want use Insert where not exists

    This is what the MERGE statement is for. Have you read the documentation I've provided?

    The way you're doing it should work as well, though (just tried it in principle in sql*plus). It's just less usual, since MERGE serves the same purpose.

    We can try to solve the error first and return to the possible use of MERGE later. Your first post didn't indicate where the problem was, so I've answered it in perhaps a bit too much broad way
    wafa khan
    Ranch Hand

    Joined: Apr 29, 2013
    Posts: 42
    i try with many queries but any result

    and i don't know that MERGE

    this is code with preparedstatement



    ANd this ERROR:
    java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    Martin Vajsar
    Sheriff

    Joined: Aug 22, 2010
    Posts: 3455
        
      47

    Let's forget about MERGE for now.

    The error in your last post is caused by the fact that you're trying to set parameters, but you haven't defined any parameter in your query (no question marks in the SQL query).

    I suggest that you return to the code you posted at the beginning (in the first post) and post here the error you're getting with that. Please post full stack trace, not just the error message (you can print out the stack trace by invoking printStackTrace() method on the exception you catch).

    Don't change your code haphazardly. Let's try to understand the cause of the error first and then to fix it.
    wafa khan
    Ranch Hand

    Joined: Apr 29, 2013
    Posts: 42
    I have test the first query in SQL and it works but in code NO

    this is the query :

    And this is the code test in SQL

    wafa khan
    Ranch Hand

    Joined: Apr 29, 2013
    Posts: 42
    And this Error about the query
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '','','','172.16.2.159 '
    FROM dual
    WHERE NOT EXISTS (SELECT VLAN FROM tout WHERE' at line 2

    this an line in my tables sql but i don't know what they say that
    Martin Vajsar
    Sheriff

    Joined: Aug 22, 2010
    Posts: 3455
        
      47

    1) Remove newlines ('\n') from the SQL in your program; replace them by spaces. They are certainly not needed, they do not improve readability, and they might be responsible for the error.

    2) Assign the SQL text of the query to a variable and print it out before executing it. Then verify that it is the same as the query you've tested outside Java.
    wafa khan
    Ranch Hand

    Joined: Apr 29, 2013
    Posts: 42
    de you mean i write the query of sql in the code java in code of statement?


    wafa khan
    Ranch Hand

    Joined: Apr 29, 2013
    Posts: 42
    YES it works tha line contains 20 dosent inserted SO it's write why the query dosent work
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Problem Insert where not exists
     
    Similar Threads
    how to retrieve value from autogenerated(oracle sequence) column in a servlet.
    SQL Exception:General Error
    Insert SQL syntax error
    Multiple insert into "MS Access" database
    how can we use where clause with insert query using preparedStatement?