DAO: diffrence between InMemory implementation and Database Implementation

Question

I'm confusing with implementation of CRUD methods for DAODatabase (for Oracle 11 xe). The problem is that the "U"-method (update) in case of storing in generally to a Map collection inserts a new element or renews it (key-value data like ID:AbstractBusinessObject) in a Map collection. And you don't care about it, when you write something like myHashMap.add(element). This method (update) is widely used in project's business logic.

Obviously, in case of using Oracle I must care about both inserting and renewing of existing elements. But I'm stucked to choose the way how to implement it:

There is no intrinsic function for so-called UPSERT in Oracle (at least in xe11g r2 version). However, I can emulate necessary function by SQL-query like this:

INSERT INTO mytable (id1, t1) 
  SELECT 11, 'x1' FROM DUAL 
  WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11); 

UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;
(src:http://stackoverflow.com/a/21310345/2938167)

By using this kind of query (first - insert, second - update) I presume that the data mostly will be inserted not updated (at least it will be rather rare). (May it be not optimal for concurrency?).

Ok, it is possible. But at this point I'm confusing to decide:

-- should I write an SQL function (with approriate arguments of course) for this and call it via Java

-- or should I simply handle a serie of queries for preparedStatements and do them via .executeUpdate/.executeQuery? Should I handle the whole UPSERT SQL code for one preparedStatment or split it into several SQL-queries and prepared statements inside one method's body? (I'm using Tomcat's pool of connections and I pass a connection instance via static method getConnection() to each method implementation in DAODatabase) ?

Is there another possibility to solve the UPSERT quest?

MT0 · Accepted Answer · 2016-03-04T15:28:04.137

The equivalent to your UPSERT statement would seem to be to use MERGE:

MERGE INTO mytable d
USING ( SELECT 11 AS id, 'x1' AS t1 FROM DUAL ) s
      ON ( d.id = s.id )
WHEN NOT MATCHED THEN
  INSERT ( d.id, d.t1 ) VALUES ( s.id, s.t1 )
WHEN MATCHED THEN
  UPDATE SET d.t1 = s.t1;

You could also use (or wrap in a procedure):

DECLARE
  p_id MYTABLE.ID%TYPE := 11;
  p_t1 MYTABLE.T1%TYPE := 'x1';
BEGIN
  UPDATE mytable
  SET    t1 = p_t1
  WHERE  id = p_id;

  IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO mytable ( id, t1 ) VALUES ( p_id, p_t1 );
  END IF;
END;
/

However, when you are handling a CRUD request - if you are doing a Create action then it should be represented by an INSERT (and if something already exists then you ought to throw the equivalent of the HTTP status code 400 Bad Request or 409 Conflict, as appropriate) and if you are doing an Update action it should be represented by an UPDATE (and if nothing is there to update then return the equivalent error to 404 Not Found.

So, while MERGE fits your description I don't think it is representative of a RESTful action as you ought to be separating the actions to their appropriate end-points rather than combining then into a joint action.

DAO: diffrence between InMemory implementation and Database Implementation

1 Answers1