mysql - SQL variables in queries in Java -

i want use several times same values.

if use in dbforge mysql next query,

set @v1 = 123, @v2='2014-04-11', @v3 = 'user1', @v4='title1'; insert test_table (tid, created, owner, title) values (@v1,@v2,@v3,@v4) on duplicate key update created=@v2, owner=@v3, title=@v4 

it correctly executes, in java, when use code

final string dbquerry = "set @v1 = %s, @v2='%s', @v3 = '%s', @v4='%s';\n"+                           "insert test_table (tid, created, owner, title)\n" +                            "values (@v1,@v2,@v3,@v4)\n" +                           "on duplicate key update\n" +                            "created=@v2, owner=@v3, title=@v4";  string currentquerry = string.format(dbquerry, t.getparam("id"),                                                 t.getparam("date"),                                                t.getparam("user"),                                                t.getparam("title")); mdbstatement.execute(currentquerry); 

i have exception

sql exception: have error in sql syntax; check manual corresponds mysql server version right syntax use near 'insert test_table (tid, created, owner, title) values (@v1,@v2,@v3,@v4) on ' @ line 2

i can use this

final string dbquerry = "insert test_table (tid, created, owner, title)\n" +                          "values (?,?,?,?)\n" +                         "on duplicate key update\n" +                          "created=?, owner=?, title=?";    preparedstatement st = mdbconnection.preparestatement(dbquerry);   st.setint(1, integer.valueof(t.getparam("id")));   st.setstring(2, t.getparam("date"));   st.setstring(5, t.getparam("date"));   st.setstring(3, t.getparam("user"));   st.setstring(6, t.getparam("user"));   st.setstring(4, t.getparam("title"));   st.setstring(7, t.getparam("title")); 

but looks ugly.

is there way solve problem?

one option use special values() function reference value have been inserted column, if insert had succeeded, this:

... on duplicate key update created = values(created)      , owner   = values(onwer)      , title   = values(title) 

---

the latter form in example preferred, using placeholders bind variables. what's ugly having supply same value twice.

i'd recommend this:

final string dbquerry = "insert test_table (tid,created,owner,title)\n" +          " values (?,?,?, ?)\n" +         " on duplicate key update\n" +          " created=values(created), owner=values(owner), title=values(title)";  preparedstatement st = mdbconnection.preparestatement(dbquerry); st.setint(1, integer.valueof(t.getparam("id"))); st.setstring(2, t.getparam("date")); st.setstring(3, t.getparam("user")); st.setstring(4, t.getparam("title")); 

and that's not ugly. that's normative pattern.

---

using special values() function useful if we're upserting more 1 row, either values clause e.g.

insert fee (fi, fo, fum) values (1,'doo','dah'),(2,'menom','menah'),(3,'buhdeep','uhdeepee') on duplicate key update fo  = values(fo)      , fum = values(fum) 

or, insert ... select form:

insert fee (fi, fo, fum) select t.ay, t.bee, t.cee sometable t on duplicate key update fo  = values(fo)      , fum = values(fum) 

---

btw... error being returned first form type of error we'd expect if allowmultiqueries=true not included in connect string. note enabling multiple queries per execution disables security feature.

consider sql text generated , sent database crafted values:

val = "foo'; drop table students; --" 

using prepared statement (with static sql text placeholder bind variables, in example above) prevents mode of sql injection. , disallowing multiple statements in single execution way thwart sql injection attacks.