Java – PL/SQL – insert record only if record does not exist

javajdbcoracleplsql

I got no good answers to my question, found out how to do it. This is how you would insert a record to a table only if the record does not already exist:

1) Create or Replace function on your schema (this is checking 2 parameters, you can set it to check as many as you wish) PL/SQL is very specific , copying and pasting as I have written should compile successfully. It took many tries to get the syntax just right. This function checks the Table to be written to, and the corresponding column names to be checked if they already exist together. 

create or replace function Found(
   var1 type, 
   var2 type)
 return number 
 is             
   numberOfSelectedRows number := 0;
 begin 
   select count(*) 
     into numberOfSelectedRows 
     from TABLE 
    where COLUMN_NAME = var1 
      and COLUMN_NAME = var2;

   return numberOfSelectedRows;
 end Found;

2) Write the java to execute the pl/sql function: this is done with NetBeans. When the button is clicked, it takes FORM data- loaded from other tables, and determines whether or not the record already exists in the table to be inserted into. 

  try {             
       DriverManager.registerDriver (new oracle.jdbc.OracleDriver());   
       con = DriverManager.getConnection(""+LOGIN.url+"");

       String str1 = jTextField_VariableName.getText();
       String str2 = jTextField_VariableName.getText();

       String q = "insert into TABLE (var1 type, var2 type) VALUES ('"+str1+"', '"+str2+"')" ; 

       cs = con.prepareCall("{?=call Found(?, ?)}");              // cs = CallableStatement - defined in class  CallableStatement cs = null; 

       cs.setString(2, str1);
       cs.setString(3, str2);
       cs.registerOutParameter(1, Types.INTEGER);
       cs.execute();

       if(cs.getInt(1)>= 1)
            {
            JOptionPane.showMessageDialog(null, " this record already exists");
            }
       else 
           {
            try{
                DriverManager.registerDriver (new oracle.jdbc.OracleDriver());  
                con = DriverManager.getConnection(""+LOGIN.url+"");

                pst = con.prepareStatement(q);

                pst.execute();

                }catch(SQLException ex)  {Logger.getLogger(REGISTER_STUDENT.class.getName()).log(Level.SEVERE, null, ex);}
           }

      } catch (SQLException ex) {Logger.getLogger(REGISTER_STUDENT.class.getName()).log(Level.SEVERE, null, ex);}

Best Answer

Just do:

String q = 
"insert into TABLE (var1, var2) " +
"SELECT ?, ? FROM dual "+
"WHERE NOT EXISTS(
"   SELECT 1 FROM table
"   WHERE var1 = ? AND var2 = ? )";
pst = con.prepareStatement(q);
pst.setString( 1, str1 );
pst.setString( 2, str2 );
pst.setString( 3, str1 );
pst.setString( 4, str2 );
int status = pst.execute();
if( status > 0 )
    System.out.println("New row inserted");
else
    System.out.println("Row already exists, insert skipped");



--- EDIT ---

INSERT INTO .... SELECT .... syntax is suported on most DBMS, see this link for more details: http://www.w3schools.com/sql/sql_insert_into_select.asp
It simply selects rows from one table and inserts a result into another table.

dual is a "dummy" table on Oracle databases, it has one column and one row, here is an example: http://www.sqlfiddle.com/#!4/d41d8/11
Dual table is helpful in many cases where we need to create a row with some values. In other databases - esspecially in MySql and PostGreSQL - this can be done with simple SELECT x, y, unfortunately Oracle doesn't support this syntax, and we must use SELECT x, y FROM dual.

In our case it is used to create a row of values that we want to insert into the table:

INSERT INTO table
SELECT x, y FROM dual;

However, the row must be inserted only when some condition is meet - this condition is described in the WHERE clause:

INSERT INTO table
SELECT x, y FROM dual
WHERE NOT EXISTS( 
   SELECT 1 FROM table
   WHERE var1 = X and var2 = Y
)

if the subquery SELECT 1 FROM ... returns a row (a row exists) - then the condition NOT EXISTS ... is false, and SELECT x, y returns nothing (empty resultset), and nothing is inserted into the table. On the other hand, when the subquery doesn't find anything, the whole condition is true, and the SELECT x,y statement creates a row with two columns: X+Y, and this row is inserted into the table.

In the PreparedStatement (in java code) we are using ? instead of X,Y variables - ? is a placeholder (a substitution variable). This link: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html describes in details how PreparedStatement works and how to use substitution variables.

Saying it simply - our query has 4 placeholders ?:

 insert into TABLE (var1, var2)  
 SELECT ?, ? FROM dual  
 WHERE NOT EXISTS(
    SELECT 1 FROM table
    WHERE var1 = ? AND var2 = ? ) ;

and setXXX statements binds values to consecutive placeholders:

pst.setString( 1, str1 );
pst.setString( 2, str2 );
pst.setString( 3, str1 );
pst.setString( 4, str2 );

thus after binding values to substitution marks, the query (which is finally executed) looks like:

 INSERT INTO TABLE (var1, var2)  
 SELECT 'str1', 'str2' FROM dual  
 WHERE NOT EXISTS(
    SELECT 1 FROM table
    WHERE var1 = 'str1' AND var2 = 'str2' ) ;
Related Topic