concatenated sql statement insert

Question

I have a script that needs to insert multiple things inside one statement, like

sql = "INSERT INTO `table` (something, something) VALUES (smth,smth); INSERT INTO `table` (something, something) VALUES (smth,smth)";
Statement stmt = connection_db.createStatement();  
 boolean update = stmt.execute(sql);

the long sql is concatenated based on conditions and needs to be this long. Using this kind of an sql statement in phpmyadmin is valid and inserts it no problem, however JAVA spits out an error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO `

Is there something I could do to insert this kind of sql, or should I remake my code to launch several times to achieve the same result?

Unrelated, but SQL injection. – Dave Newton Jul 20 '18 at 13:34 — Dave Newton, Jul 20 '18 at 13:34

score 1 · Accepted Answer · answered Jul 20 '18 at 13:27

1

a imple way is using batch SQL insert

  sql = "INSERT INTO `table` (something, something) 
          VALUES (smth,smth), (smth,smth)";

this is a standard sql weay for insert more rows with an single query

answered Jul 20 '18 at 13:27

ScaisEdge

131,976
10
91
107

this worked the best, didn't require me to remake a lot of code, thanks. – PaaPs Jul 20 '18 at 13:57

score 0 · Answer 2 · answered Jul 20 '18 at 13:28

0

Pass only multiple values

See below syntax:

INSERT INTO tbl_name
    (something, something)
VALUES
    (1,2),
    (4,5),
    (7,8);

answered Jul 20 '18 at 13:28

Devsi Odedra

5,244
1
23
37

score -1 · Answer 3 · answered Jul 20 '18 at 13:29

-1

you have to split your sql and iterate over it :

sql = "INSERT INTO `table` (something, something) VALUES (smth,smth); INSERT INTO `table` (something, something) VALUES (smth,smth)";
sqls = sql.split(";");
for(int i=0; i<sqls.length;sqls++) {
  statement = connection.prepareStatement(sqls[i]);
  statement.execute();
}

answered Jul 20 '18 at 13:29

Logan Wlv

3,274
5
32
54

While this may work, it incurs on using more network bandwith since now you're sending 3 different statements to the database rather than 1. – Luiggi Mendoza Jul 20 '18 at 13:30
It's true when you're only doing insert into on the same table like in this case, but isn't it a way to do things if you have a sql string like : "insert into tab1 ... ; update tab2 ...; insert into tab3 ... ; "insert into tab4 ..." – Logan Wlv Jul 20 '18 at 13:36
Yes: add `allowMultiQueries` parameter in your connection string, as explained in https://mariadb.com/kb/en/library/about-mariadb-connector-j/ – Luiggi Mendoza Jul 20 '18 at 13:42
I didn't know about this parameter , thank you for the correction – Logan Wlv Jul 20 '18 at 13:46

concatenated sql statement insert

3 Answers3