Inserire record solo se non presenti tramite un solo prepared statement

In un bellissimo articolo su IT Jungle ho trovato un interessante trucco. Il problema Ŕ quello di inserire i dati in una tabella, solo nel caso non esista giÓ una chiave uguale, utilizzando un solo prepared statement. Ecco la soluzione ;-)

 
INSERT INTO myTable
(KEY, code, name)
SELECT Cast(? AS Int),
       Cast(? AS VarChar(30)),
       Cast(? AS Char(10))
  FROM SYSIBM.SysDummy1
 WHERE NOT EXISTS
    (SELECT 1
       FROM myTable
      WHERE KEY=?)

con il relativo codice Java che ne fa uso:

 
Connection conn=DriverManager.getConnection("jdbc:as400://myas400;naming=sql","user","pwd");
String sql="Insert Into myLib.myTable (key, code, name) " +
  "Select Cast(? As Int)," +
  "       Cast(? As VarChar(30))," +
  "       Cast(? As Char(10))" +
  "  From SysIBM.SysDummy1 " +
  " Where Not Exists " +
  " (Select * " +
  "    From myLib.myTable " +
  "   Where key=?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, 3);                // Column 1
ps.setString(2, "Test It");   // Column 2
ps.setFloat(3,"Fun");         // Column 3
ps.setInt(4, 3);               // Where Clause
ps.execute();                  // Run the statement
ps.close();
conn.close();

Lascia un commento

You must be logged in to post a comment.