Spring JdbcTemplate “insert into.. select from..” not working

63
September 13, 2017, at 10:05 PM

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.

Answer 1

The that you have to specify the Type of each column in your case it is not possible because you Insert with a Select (subquery).

I would like to separate this into two steps :

1) Use the select to find the values you want to select

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

2) Then Insert them with a query like :

INSERT INTO japan_wht.PIVOT_20427002(doc_header_text, value_date, total_amt, is_refund) 
VALUES (?, ?, ?, ?)
Answer 2

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!

READ ALSO
@RolesAllowed has no effect on my RESTful Service

@RolesAllowed has no effect on my RESTful Service

I'm having some trouble with setting up my secure REST-ServiceI wanted to create a simple login/logout service and mess around with it

55
How can I examine external source code for incidence of dead code? [on hold]

How can I examine external source code for incidence of dead code? [on hold]

Recently I received JAVA application to write for my university classesThis is the task:

75
Add object to ArrayList at specified index

Add object to ArrayList at specified index

I think it's a fairly simple question, but I can't figure out how to do this properly

67
Merging two TreeMaps while skipping the first entry in Java

Merging two TreeMaps while skipping the first entry in Java

I have two TreeMaps which I want to merge:

65