CODEWITHSUNDEEP
javasqldatabasejdbch2
Mathomatic
Mathomatic

Reputation: 929

Insert into table if certain values don't exist

I have a H2 database named PRODUCTS, with 5000 rows and 55 columns per row. I'm currently using a PreparedStatement to add rows of values to the database, but it does not check whether the row exists already. Instead, I need to only insert the row if the column name "id" (type VARCHAR) does not contain a certain alpha-numeric string, and if the column name "id2" (type VARCHAR) does not a certain alpha-numeric string, and if the column name "raw_yn" (type BOOLEAN) contains false. The values I enter into the prepared statement are provided via a method.

The question here is very close to what I'm asking, with the difference being the fact that it's solution is based on adding rows to an empty DB, and ensures the database is empty. The creator of H2 commented saying:

Then the "where not exists" ensures this rows are only inserted if the table [TABLE] is empty.

How do I adapt this code so that it only performs the INSERT query if my above 3 requirements are met (whether or not DB is empty)?

Currently I have:

 import java.sql. *;
       static final String JDBC_DRIVER = "org.h2.Driver";
       static final String DB_URL = "jdbc:h2:~/myDB";
       static final String USER = "test";
       static final String PASS = "test";
       static final Connection conn = null;
       static final Statement stmt = null;

       public class DataBaseManager {

           public void insertIntoDB(String id1val, String id2val, Boolean raw_yn_val, ...,...) {

               try {
                   Class.forName(JDBC_DRIVER);
                   conn = DriverManager.getConnection(DB_URL, USER, PASS);
                   myStatement = "INSERT INTO PRODUCTS VALUES(?,?,?,..,...)";
                   stmt = conn.prepareStatement(myStatement);
                   stmt.setString(1, id1val);
                   stmt.setString(2, id2val);
                   stmt.setBoolean(3, raw_yn_val);
                   stmt.setString(4,....);
                // Continue up to 55
                   stmt.executeUpdate();
               }

                 [catch&finally blocks]
           }
       }

What should myStatement be changed to? I'm confused because if I use select 0, 'id1' union like below, how does that fit into my PreparedStatement of stmt.setString(1, id1val); ??. Thanks for your help.

INSERT INTO PRODUCTS SELECT * FROM(
select 0, 'id1' union                         // <--- How does this fit into Prepared Statement?
select 1, 'id2' union
select 2, 'raw_yn' union
) x where not exists(SELECT * FROM PRODUCTS);  // <--- Ensures only works when empty

UPDATE:

Following Gord's suggestion, I've put together the following code. If the database is empty, n will return 0. This takes about 1 minute to add 5000 rows to the empty database. However, if there IS a match, it's taking nearly 5x longer, even though I'm merely using return as opposed to using additional INSERT code. So shouldn't it be faster? 

try {
   Class.forName(JDBC_DRIVER);
   conn = DriverManager.getConnection(DB_URL, USER, PASS);
   statement = conn.createStatement();

   String sql = "SELECT COUNT(*) AS n FROM PROPERTIES WHERE id1='" + id1 + "' AND id2='" + id2 + "' AND raw_yn='true'";
   rs = statement.executeQuery(sql);
   rs.next();
   if (rs.getInt("n") == 0) {
    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    myStatement = "INSERT INTO PROPERTIES VALUES(?,?,?,...)";
    stmt = conn.prepareStatement(myStatement);
    stmt.setString(1, id1);
    stmt.setString(2, id2);
    stmt.setBoolean(3, raw_yn);
    stmt.executeUpdate();
   } else {
    return; // <-- Takes 5x longer to go through ???
   }

  }

    [catch & finally blocks]

Upvotes: 0

Views: 1716

Answers (1)

Parfait
Parfait

Reputation: 107687

Consider using a staging temp table where you append all data as is into a similar structured table, ProductsTemp, and then migrate to final table, Products, filtered for the unique rows. Below are the SQL statements to incorporate in your Java code in this order:

Staging Append (two statements)

DELETE FROM ProductsTemp;

INSERT INTO ProductsTemp VALUES (?,?,?,..,...);

Final Migration

INSERT INTO Products (id, id2, raw_yn, ...)
SELECT id, id2, raw_yn, ...
FROM ProductsTemp temp
WHERE NOT EXISTS (SELECT 1 FROM Products sub
                   WHERE sub.id = temp.id
                     AND sub.id2 = temp.id2
                     AND sub.raw_yn = temp.raw_yn);

Upvotes: 1

Related Questions