Spring JPA cannot save entity with mysql json column using native save method-CodePudding

I have the following entity:

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "USER")
public class User implements Serializable {

    @Id
    @Column(name = "NAME", nullable = false)
    private String name;

    @Column(name = "AGE", nullable = false)
    private int age;

    @Column(name = "DETAILS", nullable = false, columnDefinition = "json" )
    private String details;
}

When I receive a new user object I will try to persist it in the database.

{
    "age": 5,
    "name": "MARIO",
    "details": "{\"country\":\"Indonesia\"}"
}

For some reason I cant save with with the normal JpaRepository save,

@Autowired
UserRepository userRepository;

public saveNewUser(User newUser){
    userRepository.save(newUser);
}

Running the save user function throws me this error:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'age=5 where name='MARIO'' at line 1

But if i define my own custom save method in the repository it saves just fine with no issues:

@Repository
public interface UserRepository extends JpaRepository<User, String> {

@Modifying
@Transactional
@Query(value = "insert into USER (NAME, AGE, DETAILS) values (:name, :age, :details)", nativeQuery = true)
void saveWithJson(String name, String details, int age);

}

and I call it like so:

@Autowired
UserRepository userRepository;

public saveNewUser(User newUser){
    userRepository.saveWithJson(newUser.getName, newUser.getDetails, newUser.getAge);
}

Any idea why this is happening? I tested with the exact same JSON being received. I dont mind using my own save query, I just assumed that underneath the layer of abstraction JPA should be calling the same method as my native query?

CodePudding user response：

Add below property in application.properties file, to view the JPA generated query in console/log

spring.jpa.show-sql: true

Also verify, is the name already inserted in DB.

CodePudding user response：

The problem comes from the name of table chosen for the entity User entity :

@Table(name = "USER")

user is a sql reserved keyword.

The solution is to choose another name for that table.