aspose file tools*
The moose likes JDBC and the fly likes Using Sequences With JDBC in Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using Sequences With JDBC in Oracle" Watch "Using Sequences With JDBC in Oracle" New topic
Author

Using Sequences With JDBC in Oracle

Sid Singh
Greenhorn

Joined: Jun 14, 2010
Posts: 20

Hi,

My question is related to usage of sequences in insert queries.
Following are the two methods that i have used for sequences

1. To get the sequence in a seperate query and then pass it to the insert query using PreparedStatement
2. Directly pass the SequenceName.NEXTVAL in the insert query.

I have seen in the projects that i have worked, Method 1 is used mostly. As far as i visualize getting sequence in a seperate query will increase
a round of I/O which would be 1 in second method.

Please help in identifying the pro's and cons of both the methods.

Awaiting your reply.

OCPJP 6, SCWCD In Progress
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
If you need to use the generated key from sequence then you should either select seq.nextval before executing insert or use insert into returning clause.

Regards,
Fatih.
Sid Singh
Greenhorn

Joined: Jun 14, 2010
Posts: 20

Fatih Keles wrote:If you need to use the generated key from sequence then you should either select seq.nextval before executing insert or use insert into returning clause.

Regards,
Fatih.


Hi Fatih,

No i dont need to use that key. Since it is a Primary Key for the table therefore i need to fetch the sequence while inserting the record.

One more point i would like to add in my above mentioned question.
I work in a tightly integrated system where several applications fire queries on a common database,
so what effect would each of the methods have on performance and execution time of the query.

Regards
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Executing a query from dual to fetch a value from a sequence is almost costless for Oracle database, but for the senario you have execute 2 queries consequently and total time is effected from many things like network, AS utilization etc. I would prefer to execute one query where possible.
Sid Singh
Greenhorn

Joined: Jun 14, 2010
Posts: 20

Fatih Keles wrote:Executing a query from dual to fetch a value from a sequence is almost costless for Oracle database, but for the senario you have execute 2 queries consequently and total time is effected from many things like network, AS utilization etc. I would prefer to execute one query where possible.


Thanks Fatih,
That was really helpful.

It would have been great if some other people could give their comments on it.
Thanks in Advance
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
I prefer to execute SQL as less as possible (without compromising the functionality!) no matter whether its via JDBC or PLSQL as long as that single SQL becomes too complex and convoluted (in that case probably breaking the single SQL into parts is beneficial). Every call to a SQL in PLSQL routine results in a context switch from PLSQL engine to SQL engine in the DB. In case of JDBC, every SQL call would result in a round network trip. My personal preference would be to reduce number of SQL calls, and make SQL call only when its absolutely necessary.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using Sequences With JDBC in Oracle
 
Similar Threads
Recursion
TableGenerator
testing my java DAO insert method using GregorianCalendar instead of java.sql.Date?
Problem faced due to skipping sequence numbers when generated from inside JMS Queues during message
Table-level locking, how?