Use JDBC to modify the Oracle sequence step in batches

Use JDBC to modify the Oracle sequence step in batches

Scenario

After the data migration is complete, you need to reset the sequence in the database ,.

Method

Three Common Methods

Delete-recreate

In this way, Simple Sequence reconstruction will affect the business logic processing and cannot adapt to the inconsistency of original sequence Parameter rules. However, if the sequence parameters are consistent, this method is simple and efficient.

Temporarily modify the sequence step and then restore (PL/SQL adopts this method)

In this way, first modify the sequence step and then use the nextval auto-incrementing sequence to obtain the target value, and then restore the sequence step to the original value, as shown in the following SQL:

ALTER SEQUENCE SEQ_ACCOUNT_BALANCE INCREMENT BY 1000;SELECT SEQ_ACCOUNT_BALANCE.NEXTVAL FROM DUAL;ALTER SEQUENCE SEQ_ACCOUNT_BALANCE INCREMENT BY 1;

This method is often used when you modify a specific sequence. However, if you operate a sequence in batches, the operation step is one more step than the previous step.

Cyclic incremental Sequence Value

This method is safe but inefficient, as shown in the following SQL:

FOR I IN 1 .. 1000 LOOPSELECT SEQ_ACC_ID.NEXTVAL INTO SAI FROM DUAL;END LOOP;

 

Encapsulate batch operation sequences using JDBC

Package Org. ybygjy. example; import Java. SQL. connection; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. SQL. statement; import Java. util. arraylist; import Java. util. list; import Org. ybygjy. util. dbutils;/*** sequence auto-increment processing, which is responsible for auto-incrementing the given sequence value to a given value * <p> is completed by deleting the sequence and then recreating it. This is suitable for the case where the sequence step size is consistent. * @ Author wangyancheng * @ version 2012-5-7 */public class autoincrementseq {private string qrysql = "select sequence_name from user_sequences"; private string drostmq = "drop sequence @ seq "; private string createseq = "create sequence @ seq start with @ SW increment by 1 nocycle nomaxvalue"; private int strides = 300000; private connection conn; /*** initialize the constructor * @ Param conn {@ link connection} */pub LIC autoincrementseq (connection conn) {This. conn = conn;}/*** logical processing entry */Public void dowork () {string [] seqnamearray = getsequence4dbuser (); // string [] seqnamearray = fileutils. fetchfilename (new file ("D: \ Dev \ works \ prod \ @ Users \ SQL \ fbcm \ oracle \ SQL \ new \ seq ")); list <string> seqddlarray = new arraylist <string> (); For (string seqname: seqnamearray) {rebuildseq (seqddlarray, seqname, strides) ;} Innerexecddlsql (seqddlarray);}/*** obtain the sequence in the user scope * @ return rtnarray sequence name set/null */Public String [] getsequence4dbuser () {statement stmt = NULL; resultset rs = NULL; string [] rtnarray = NULL; try {stmt = Conn. createstatement (resultset. type_forward_only, resultset. concur_read_only); RS = stmt.exe cutequery (qrysql); List <string> tmparray = new arraylist <string> (); While (RS. next () {tmparray. add (RS. getstring (1);} If (tmparray. size ()> 0) {rtnarray = new string [tmparray. size ()]; tmparray. toarray (rtnarray) ;}} catch (exception e) {e. printstacktrace ();} finally {If (null! = RS) {try {Rs. Close () ;}catch (exception e) {e. printstacktrace () ;}} if (null! = Stmt) {try {stmt. close ();} catch (exception e) {e. printstacktrace () ;}}return rtnarray ;} /*** re-build sequence * @ Param seqname sequence name * @ Param startwith start value */Public void rebuildseq (list <string> rtnsqlarr, string seqname, int startwith) {rtnsqlarr. add (this. drostmq. replace ("@ seq", seqname); rtnsqlarr. add (this. createseq. replace ("@ seq", seqname ). replacefirst ("@ SW", String. valueof (startwith )));}/* ** Run the SQL statement * @ Param sqlarray */private void innerexecddlsql (list <string> sqlarray) {statement stmt = NULL; try {stmt = This. conn. createstatement (resultset. type_forward_only, resultset. concur_read_only); For (string tmpsql: sqlarray) {stmt. addbatch (tmpsql);} int [] rtnarr = stmt.exe cutebatch (); int Index = 0; For (string seqsql: sqlarray) {system. out. println ("SQL:" + seqsql + "\ t Result :". concat (String. valueof (rtnarr [Index]) ;}} catch (exception e) {e. printstacktrace () ;}finally {If (null! = Stmt) {try {stmt. close ();} catch (sqlexception e) {e. printstacktrace () ;}}}/*** execution entry * @ Param ARGs parameter list */public static void main (string [] ARGs) {string connurl = "JDBC: ORACLE: thin: nstcfss516/6316380@192.1.116.204: 1521/nsdev "; connection conn = NULL; try {conn = dbutils. createconn4oracle (connurl); autoincrementseq aiseqinst = new autoincrementseq (conn); aiseqinst. dowork ();} catch (E Xception e) {e. printstacktrace ();} finally {If (null! = Conn) {try {conn. Close ();} catch (exception e) {e. printstacktrace ();}}}}}