could not execute statement; SQL [n/a] Caused by - Unknown column 'postback_data' in 'field list'

Question

I am Currently working on one of the requirements where I wanted to POST some data in my database.

In mys database named template_data I have three columns called id,json_data & timestamp. The json_data column will hold the json where the json values will be coming from the user such as:

        "suggestions": [
            {
                "reply": {
                    "text": "**Tobe given by user**",
                    "postbackData": "**Tobe given by user**"
                }
            }

I have to construct this JSON through my code what I am doing here in the below code

package com.airkommtemplate.springboot.RCMTemplate.controller;


import com.airkommtemplate.springboot.RCMTemplate.entity.TemplateData;
import com.airkommtemplate.springboot.RCMTemplate.service.TemplateServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import java.time.LocalDateTime;
import java.util.List;

@Controller
@RequestMapping("/templates")
public class TemplateController {

    private TemplateServiceImpl templateService;

    @Autowired
    public  TemplateController(TemplateServiceImpl theTemplateService){
        templateService = theTemplateService;
    }

    @GetMapping("/form")
    public String showForm(Model model) {
        model.addAttribute("template", new TemplateData());
        return "templates/template-form"; // Thymeleaf template file name (e.g., template-form.html)
    }

    @GetMapping("/list")
    public String listTemplateData(Model theModel) {

        List<TemplateData> theTemplateList =templateService.findAll();

        // add to the spring model
        theModel.addAttribute("templates", theTemplateList);

        return "templates/list-templates";
    }

    @PostMapping("/save")
    public String saveData(@ModelAttribute("template") TemplateData template) {
        // Construct the complete JSON structure using user input
        String jsonData = "{\"suggestions\": [{" +
                "\"reply\": {" +
                "\"text\": \"" + template.getText() + "\"," +
                "\"postbackData\": \"" + template.getPostbackData() + "\"" +
                "}}]}";

        // Set timestamp
        template.setTimestamp(LocalDateTime.now());
        // Save the complete JSON data to the database
        template.setJsonData(jsonData);

        templateService.saveData(template);

        return "redirect:/templates/list";

        //return "redirect:/success"; // Redirect to a success page
    }
}

The service

package com.airkommtemplate.springboot.RCMTemplate.service;

import com.airkommtemplate.springboot.RCMTemplate.dao.TemplateDataRepository;
import com.airkommtemplate.springboot.RCMTemplate.entity.TemplateData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;

@Service
public class TemplateServiceImpl implements TemplateService{

    private final TemplateDataRepository templateDataRepository;

    @Autowired
    public TemplateServiceImpl(TemplateDataRepository templateDataRepository) {
        this.templateDataRepository = templateDataRepository;
    }
    @Override
    public List<TemplateData> findAll() {
        return templateDataRepository.findAll();
    }

    @Override
    public void saveData(TemplateData templateData) {
        //TemplateData templateData = new TemplateData();
        templateData.setTimestamp(LocalDateTime.now());
        templateDataRepository.save(templateData);

    }



}

The Repository (DAO)

package com.airkommtemplate.springboot.RCMTemplate.dao;

import com.airkommtemplate.springboot.RCMTemplate.entity.TemplateData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface TemplateDataRepository extends JpaRepository<TemplateData, Integer> {
}

My entity class holds below parameters

@Entity
@Table(name="template_data")
public class TemplateData {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private int id;

    @Column(name="json_data")
    private String jsonData;

    @Column(name="timestamp")
    private LocalDateTime timestamp;

    private String text;
    private String postbackData;

When I am trying to save this data using /templates/save api it is throwing the error below:

There was an unexpected error (type=Internal Server Error, status=500).
could not execute statement; SQL [n/a]
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:256)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:232)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:243)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:164)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244)
    at jdk.proxy4/jdk.proxy4.$Proxy111.save(Unknown Source)
    at com.airkommtemplate.springboot.RCMTemplate.service.TemplateServiceImpl.saveData(TemplateServiceImpl.java:29)
    at com.airkommtemplate.springboot.RCMTemplate.controller.TemplateController.saveData(TemplateController.java:59).......


Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'postback_data' in 'field list'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122......

I have tried changing the parameters of template type which I am passing in my controllers to service call, but nothing is working out. Nothing is working out here, Help would be really appreciated.

Thanks.

Answer 1

Please keep in mind that

Java's transient keyword is used to denote that a field is not to be serialized, whereas JPA's @Transient annotation is used to indicate that a field is not to be persisted in the database, i.e. their semantics are different.

So please make the following changes

    @Transient
    private String text;

    @Transient
    private String postbackData;

Please accept the answer if it works, Thanks!