• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem Insert where not exists

 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
    Posts: 42
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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 Vashko
    Sheriff
    Posts: 3837
    66
    Netbeans IDE Oracle Firefox Browser
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    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
    Posts: 42
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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 Vashko
    Sheriff
    Posts: 3837
    66
    Netbeans IDE Oracle Firefox Browser
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 42
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 42
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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 Vashko
    Sheriff
    Posts: 3837
    66
    Netbeans IDE Oracle Firefox Browser
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 42
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    de you mean i write the query of sql in the code java in code of statement?


     
    wafa khan
    Ranch Hand
    Posts: 42
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    YES it works tha line contains 20 dosent inserted SO it's write why the query dosent work
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic