CODEWITHSUNDEEP
javamysqlspringspring-bootjdbctemplate
Akshay Lokur
Akshay Lokur

Reputation: 7506

Spring JdbcTemplate "insert into.. select from.." not working

I am trying to execute following SQL with Spring JdbcTemplate:

INSERT INTO japan_wht.PIVOT_20427002(doc_header_text, value_date, total_amt, is_refund)                    
 (SELECT 
    doc_header_text, DATE(value_date), SUM(LOCAL_CCY_AMT), is_refund
 FROM
    (SELECT 
        *
    FROM
        japan_wht.DATA_20427002
    WHERE IS_REFUND in ('N')
    ) t 
GROUP BY DATE(value_date) , doc_header_text, is_refund)

However, it does not insert anything into database table and no error is thrown.

When I tried to execute following SQL with JdbcTemplate, it works and inserts data in DB table:

INSERT INTO japan_wht.PIVOT_20427002(id, doc_header_text, value_date, total_amt, is_refund) values('1', '1', '2017-12-31', 3000, 'Y');

Below is my call to execute above SQLs:

jdbcTemplate.update(sqlString);

Not sure what is going wrong here.

Upvotes: 2

Views: 6861

Answers (3)

mrNormal Kolos
mrNormal Kolos

Reputation: 11

If you run it in IDEA, check temporally files if you open DB from resource file. I store my DB file exampleDB.db in src/main/resorces/db folder, so i found my temporally db file in build/resources/main/db/exampleDB.db. Try to open it temporally file to check it contain.

Upvotes: 0

Dherik
Dherik

Reputation: 19050

You can use the execute method. Like:

jdbcTemplate.execute("INSERT INTO table1 (some_id, some_key, some_text) " +
      "SELECT id, key, text from table2 t2 " +
      "LIMIT ?", (PreparedStatementCallback<Boolean>) ps -> {
                      ps.setInt(1, 400);
                      return ps.execute();
                  });

Upvotes: 0

Akshay Lokur
Akshay Lokur

Reputation: 7506

I had to resort to plain JDBC and it worked:

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mySchema?autoReconnect=true&useSSL=false&rewriteBatchedStatements=true",
    "root", "root");
Statement stmt = conn.createStatement();
int flag = stmt.executeUpdate(sqlString);
LOGGER.info("Flag = {}", flag);

Not sure why Spring JdbcTemplate can not handle such thing!

Upvotes: 1

Related Questions