RequestBody doesn't get body JSON and jdbc.BadSqlGrammarException

Question

I'm trying to make a fast CRUD app usins java spring boot with jdbc as database, I'm having some troubles with adding something new to the database (DisputeLog in this case), at first I'm having an error 500 for the jdbc.BadSqlGrammarException but i still don't notice something bad at all with the query, and also the params that i sent in the body JSON are not being taken at the moment with of the @ResponseBody. I'll be grateful if someone could help me notice my mistake.

Exception:

2023-07-07 19:45:13.425 ERROR 12115 --- [nio-8096-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO ENGAGE_AR_DISPUTES.DISPUTE_LOGS(FROM_VALUES, TO_VALUES,SOURCE, DISPUTE_ID, CREATED_BY) VALUES('null','null','null','null','null');]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10145][10844][4.28.11] Invalid parameter 1: Parameter index is out of range. ERRORCODE=-4461, SQLSTATE=42815] with root cause

Controller:

@PostMapping("/create-dispute-log")
    public ResponseEntity<DisputeLog> createDispute(@RequestBody DisputeLog disputeLog){
        System.out.println("dispute log id: "+disputeLog.getDisputeLogID());
        return new ResponseEntity<DisputeLog>(disputeLogService.addNewDisputeLog(disputeLog), HttpStatus.CREATED);
    }

Service:

  public DisputeLog addNewDisputeLog(DisputeLog disputeLog){
        disputeLog.setCreatedAt(new Timestamp(System.currentTimeMillis()));
        String query="INSERT INTO ENGAGE_AR_DISPUTES.DISPUTE_LOGS(FROM_VALUES, " +
                "TO_VALUES,SOURCE, DISPUTE_ID, CREATED_BY) VALUES('"
                +disputeLog.getFromValues()+"','"+disputeLog.getToValues()+"','"+disputeLog.getSource()+"','"+
                disputeLog.getDisputeID()+"','"+disputeLog.getCreatedBy()+"');";
        jdbcTemplate.update(query);
        DisputeLog newDisputeLog=disputeLog;
        return newDisputeLog;
    }

JSON sent in the body at Insomnia:

{
    "FROM_VALUES": "Y",
    "TO_VALUES":"Z",
    "SOURCE": "ES",
    "DISPUTE_ID":"5567",
    "CREATED_BY":"AB5W1"    
}

I tried to change the statement but still doesn't work.

Answer 1

Without seeing the code of your Java class DisputeLog I'm assuming it is just a simple POJO with fields and getters/setters written according to Java conventions. Like this:

public class DisputeLog {
    private String fromValues;
    public String getFromValues() {
        return fromValues;
    }
    public void setFromValues(String fromValues) {
        this.fromValues = fromValues;
    }
}

In this case when you have a JSON object like in your post the mapping will not work because your JSON property has a different name: FROM_VALUES That's why you are getting null from the getter. To get it work you can either rename JSON property or add an annotation to the Java field. Like this:

either:
{
    "fromValues": "Y"
}
or:
    @JsonProperty("FROM_VALUES")
    private String fromValues;

Another problem causing SQL exception is that the SQL string ends with semicolon. It is not necessary and not valid when using JDBC. Just remove semicolon and it should fly.

Hope it helps.