[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle Insert/update in Java

Posted on 2009-02-13
16
Medium Priority
?
880 Views
Last Modified: 2013-11-16
I have a USER table in Oracle with the following columns

FIRST_NAME char
LAST_NAME char
ID number
DEPT number
DATE_CREATED date
DATE_MODIFIED date

The database can have these records and I am given a set of IDS and names to update the DEPT field to a new number. Is there a single command I can user to insert/or update the record given an ID? In other words:

if the record exist,
then update DEPT and set DATE_MODIFIED

else
INSERT with all values.

Is there a simple way to do it other than doing a select for it and then deciding to insert or not?

Thanks!
0
Comment
Question by:Maarvaadi
  • 5
  • 4
  • 4
  • +1
16 Comments
 
LVL 92

Expert Comment

by:objects
ID: 23638823
not that I am aware of unless there is an oracle specific command.

instead of doing a select you could attempt the update and check if the record was updated (executeUpdate() returns the number od rows updated). If not updated then its safe to assume it needs to be inserted.

0
 

Author Comment

by:Maarvaadi
ID: 23638838
The problem with that is then I cant batch them. I will have to update them one by one. Otherwise I can batch a 1000 updates and or inserts and then execute them at one shot increasing performance.
0
 
LVL 92

Expert Comment

by:objects
ID: 23638892
you should still be able to use a batch, the batch update returns an array of ints indicating the results of the statements

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Maarvaadi
ID: 23638964
True, but then on failure you will have to backtrack and then start doing the inserts, but I get the idea. Thanks.
0
 
LVL 92

Expert Comment

by:objects
ID: 23638979
save you doing the select, unless oracle provides something then I don't know of a better way.

0
 
LVL 92

Expert Comment

by:objects
ID: 23638980
actually another way to look at it would be to try select for update, but you're going to  lose batching ability that way.

0
 
LVL 15

Expert Comment

by:fsze88
ID: 23639289
actually, this question is related to oracle more than java
create a procedure/function from oracle and run it by java command
procedure/function do the jobs what you needs (select/update/insert)
It could saving cpu usage on both of java client application and oracle database server..
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23639778
Use a PreparedStatement, the sql of which should be something like the below:
MERGE INTO user
USING (
	SELECT id, dept, date_modified
	FROM user
	WHERE id = ? )
WHEN MATCHED THEN
	UPDATE SET dept = ?, date_modified = ?
WHEN NOT MATCHED THEN	
	INSERT (first_name, last_name, id, dept, date_created, date_modified) values (?,?,?,?,?,?)

Open in new window

0
 

Author Comment

by:Maarvaadi
ID: 23641207
CEHJ: Thanks for that pointer, but apparantly that does not work. I did a search for the syntax and it is like this:

MERGE INTO bonuses b
USING (
  SELECT employee_id, salary, dept_no
  FROM employee
  WHERE dept_no =20) e
ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE SET b.bonus = e.salary * 0.1
  DELETE WHERE (e.salary < 40000)
WHEN NOT MATCHED THEN
  INSERT (b.employee_id, b.bonus)
  VALUES (e.employee_id, e.salary * 0.05)
  WHERE (e.salary > 40000);

Which means that there is 2 tables involved?
0
 
LVL 15

Expert Comment

by:fsze88
ID: 23641285
something like code snippet
reference http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1225807,00.html
Merge into destination_table d
 using source_table s
 on (d.unique_id = s.unique_id)
 when matched then
 Update
 set d.full_name = s.full_name,
   d.phone = s.phone,
   d.email = s. email
when not matched then
 insert (d.contact_id,
    d.creation_date,
    d.full_name,
    d.phone,
    d.email,
    d.unique_id)
 values (v_next_val,
    s.creation_date,
    s.full_name,
    s.phone,
    s.email,
    s.unique_id);
commit;

Open in new window

0
 

Author Comment

by:Maarvaadi
ID: 23641293
fsze88: This has 2 tables. I have only one table which I am operating on.
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 900 total points
ID: 23641309
0
 
LVL 15

Assisted Solution

by:fsze88
fsze88 earned 100 total points
ID: 23641330
Okey,
please have a look http://www.psoug.org/reference/merge.html
;)
0
 
LVL 15

Expert Comment

by:fsze88
ID: 23641380
it works fine on my oracle 10g XE
SQL> CREATE TABLE employee (
  2  employee_id NUMBER(5),
  3  first_name  VARCHAR2(20),
  4  last_name   VARCHAR2(20),
  5  dept_no     NUMBER(2),
  6  salary      NUMBER(10));
 
òúËh<.
 
SQL> INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
 
òúË 1 Ç™.
 
SQL> INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000);
 
òúË 1 Ç™.
 
SQL> INSERT INTO employee VALUES (3, 'Helen', 'Lofstrom', 20, 50000);
 
òúË 1 Ç™.
 
SQL> INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
 
òúË 1 Ç™.
 
SQL> INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);
 
òúË 1 Ç™.
 
SQL> INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);
 
òúË 1 Ç™.
 
SQL> INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);
 
òúË 1 Ç™.
 
SQL>
SQL> CREATE TABLE bonuses (
  2  employee_id NUMBER, bonus NUMBER DEFAULT 100);
 
òúËh<.
 
SQL> INSERT INTO bonuses (employee_id) VALUES (1);
 
òúË 1 Ç™.
 
SQL> INSERT INTO bonuses (employee_id) VALUES (2);
 
òúË 1 Ç™.
 
SQL> INSERT INTO bonuses (employee_id) VALUES (4);
 
òúË 1 Ç™.
 
SQL> INSERT INTO bonuses (employee_id) VALUES (6);
 
òúË 1 Ç™.
 
SQL> INSERT INTO bonuses (employee_id) VALUES (7);
 
òúË 1 Ç™.
 
SQL> COMMIT;
 
ºŒ.
 
SQL>  MERGE INTO bonuses b
  2  USING (
  3    SELECT employee_id, salary, dept_no
  4    FROM employee
  5    WHERE dept_no =20) e
  6  ON (b.employee_id = e.employee_id)
  7  WHEN MATCHED THEN
  8    UPDATE SET b.bonus = e.salary * 0.1
  9    DELETE WHERE (e.salary < 40000)
 10  WHEN NOT MATCHED THEN
 11    INSERT (b.employee_id, b.bonus)
 12    VALUES (e.employee_id, e.salary * 0.05)
 13    WHERE (e.salary > 40000);
 
u 6 Ç™.
 
SQL> SELECT * FROM bonuses;
 
EMPLOYEE_ID      BONUS
----------- ----------
          1        100
          2      10000
          4       4000
          7       9000
          5       3500
          3       2500
 
òxÖ 6 Ç™.
 
SQL>  MERGE INTO bonuses b
  2  USING (
  3    SELECT employee_id, salary, dept_no
  4    FROM employee
  5    WHERE dept_no =20) e
  6  ON (b.employee_id = e.employee_id)
  7  WHEN MATCHED THEN
  8    UPDATE SET b.bonus = e.salary * 0.1
  9    DELETE WHERE (e.salary < 40000)
 10  WHEN NOT MATCHED THEN
 11    INSERT (b.employee_id, b.bonus)
 12    VALUES (e.employee_id, e.salary * 0.05)
 13    WHERE (e.salary > 40000);
 
u 5 Ç™.
 
SQL> SELECT * FROM bonuses;
 
EMPLOYEE_ID      BONUS
----------- ----------
          1        100
          2      10000
          4       4000
          7       9000
          5       7000
          3       5000
 
òxÖ 6 Ç™.
 
SQL>

Open in new window

0
 
LVL 92

Expert Comment

by:objects
ID: 23642896
told you, you would need an oracle specific solution :)
good to know it can be done like that, we'll update our knowledge base accordingly.

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23644069
:-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month18 days, 22 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question