how can I execute a multi sql line in Java?

Question

These three line of code are have to be execute at the same time.

  SET @num := 0;

        UPDATE your_table SET id = @num := (@num+1);

        ALTER TABLE tableName AUTO_INCREMENT = 1;
    ********************************************************
    Statement s1 = DataCon.getDataCon().createStatement();
                            s1.execute("SET @num := 0; UPDATE tblstaff SET staffid = @num := (@num+1); ALTER TABLE tblstaff AUTO_INCREMENT = 1");
                            s1.close();

Any reason you can't run them separately?

shmosel
– shmosel

2015-12-09 22:53:48 +00:00
Commented Dec 9, 2015 at 22:53 — shmosel
– shmosel, Commented Dec 9, 2015 at 22:53
looks good. what's the question?

Dima
– Dima

2015-12-09 23:00:39 +00:00
Commented Dec 9, 2015 at 23:00 — Dima
– Dima, Commented Dec 9, 2015 at 23:00
how can I execute a multi sql line in Java? is the question

Kiry Meas
– Kiry Meas

2015-12-10 18:57:56 +00:00
Commented Dec 10, 2015 at 18:57 — Kiry Meas
– Kiry Meas, Commented Dec 10, 2015 at 18:57
the reason is I have to reconnect to db twice :)

Kiry Meas
– Kiry Meas

2015-12-10 18:59:11 +00:00
Commented Dec 10, 2015 at 18:59 — Kiry Meas
– Kiry Meas, Commented Dec 10, 2015 at 18:59

Drew · Accepted Answer · 2015-12-10 05:18:41Z

1

Try this! I Found it very useful if you wanna keep data in numerical order

Statement stm = DataCon.getDataCon().createStatement(); 
String stm1 = "SET @num := 0"; 
String stm2 = "UPDATE tblstaff SET staffid = @num := (@num+1)"; 
String stm3 = "ALTER TABLE tblstaff AUTO_INCREMENT = 1"; 
stm.addBatch(stm1); 
stm.addBatch(stm2); 
stm.addBatch(stm3); 
stm.executeBatch(); 
stm.close();

edited Dec 10, 2015 at 5:18

Drew

25k10 gold badges47 silver badges81 bronze badges

answered Dec 9, 2015 at 23:07

Kiry Meas

1,25219 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barmar Over a year ago

Please format your code readably. See the help pages for how to post code in the SO editor.

Gordon Linoff · Accepted Answer · 2015-12-09 23:03:32Z

0

Interestingly, you don't need to. Because you are not using order by, you can use:

UPDATE your_table CROSS JOIN (SELECT @num := 0) params
    SET id = @num := (@num+1);

Having said that, ORDER BY is often desirable in this situation.

answered Dec 9, 2015 at 23:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

how can I execute a multi sql line in Java?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related