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

Question

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?

You can see the exact query JPA is using by enabling debug level log. — aatwork
– aatwork, Commented Nov 20, 2022 at 5:01
... to enable sql query traces use show_sql=true in the hibernate parameters. The error seems to be the end of a sql update. — Pierre Demeestere
– Pierre Demeestere, Commented Nov 20, 2022 at 9:51
User is a sql keyword (stackoverflow.com/questions/6082412/…). Try to change the name of your user table. — Pierre Demeestere
– Pierre Demeestere, Commented Nov 20, 2022 at 9:59
@PierreDemeestere yep, that was dumb. This ended up being the issue in the end, because JPA doesnt add quotations on user like what did -> 'user'. Thank youuu — Oirampok
– Oirampok, Commented Nov 21, 2022 at 14:20
I am glad I could help you. If you have enough permission to up vote my remark that would help me. — Pierre Demeestere
– Pierre Demeestere, Commented Nov 21, 2022 at 15:49

Pierre Demeestere · Accepted Answer · 2022-11-22 07:37:19Z

1

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.

answered Nov 22, 2022 at 7:37

Pierre Demeestere

4063 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Subha Chandra · Accepted Answer · 2022-11-20 14:50:41Z

0

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.

answered Nov 20, 2022 at 14:50

Subha Chandra

7819 silver badges24 bronze badges

1 Comment

Subha Chandra Over a year ago

Please refer this working example, github.com/jsubhachandra/demo-mysql

Xianghua · Accepted Answer · 2022-11-22 09:47:05Z

0

@Entity
@Data
@Table(name = "USER")
public class  User{

    @Id
    @Column(name = "NAME", columnDefinition = "varchar(32) comment 'id:name'")
    private String name;

    @Column(name = "AGE", columnDefinition = "int(3) comment 'age'")
    private Integer age;

    @Column(name = "DETAILS", columnDefinition = "text comment 'details'")
    private String details;
}


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

public UserService {
    @Autowired
    UserRepository userRepository;

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

answered Nov 22, 2022 at 9:47

Xianghua

921 silver badge3 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related