CODEWITHSUNDEEP
javaoracle-databasejdbcspring-batchjdbctemplate
Aindriu
Aindriu

Reputation: 63

Does jdbcTemplate.update support a sub select in insert queries?

I am trying to carry out an insert using jdbcTemplate

I have my insert statement string

String myInsert = "INSERT INTO myTable(col1, col2, col3,) values( val1, (SELECT thisVal from mySecondTable where myColumn = val2), val3)"

and then my jdbcTemplate.Update code looks like

for(MyObject myobject: objects) {
  this.jdbcTemplate.update(myInsert,
    myObject.getVal1,
    myObject.getVal2,
    myObject.getVal3
    );
}

the error occurs on val2 and says I am trying to insert a null value into the table,

I have logging enabled and the log shows the objects before the loop and I can see values for each field. so I am wondering is there something going awry with how the mapping is done in the prepared statement that jdbcTemplate creates?

Upvotes: -1

Views: 46

Answers (1)

AndrewL
AndrewL

Reputation: 3455

Does jdbcTemplate.update support a sub select in insert queries?

JDBC Template doesn't care what SQL you use... this is just passed to the database to process - so if your database supports the syntax (which you should try outside java in your database's shell)

What the problem, as @talex points out, is that you are not defining any bind variables in your SQL statement

Change your statement from:

String myInsert = "INSERT INTO myTable(col1, col2, col3,) values( val1, (SELECT thisVal from mySecondTable where myColumn = val2), val3)"

to

String myInsert = "INSERT INTO myTable(col1, col2, col3,) values( ?, (SELECT thisVal from mySecondTable where myColumn = ?), ?)"

This your program should work. See this tutorial: https://www.baeldung.com/spring-jdbc-jdbctemplate#1-basic-queries (many others exist)

Upvotes: 1

Related Questions