Warning in SQL statement when creating table using Spring Data JPA and error when inserting into the table

huangapple go评论110阅读模式
英文:

Warning in SQL statement when creating table using Spring Data JPA and error when inserting into the table

问题

I created a table using Spring Data JPA, so that means I did not type any SQL code. Now I am getting a warning about a Syntax error in my SQL statement like this:

Syntax error in SQL statement "create table [*]user (id bigint not null, city varchar(255), fullname varchar(255), password varchar(255), phone_number varchar(255), state varchar(255), street varchar(255), username varchar(255), zip varchar(255), primary key (id))"; expected "identifier";

This is a section of my "User" domain for getting user information:

@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PRIVATE, force = true)
@RequiredArgsConstructor
public class User implements UserDetails{

	private static final long serialVersionUID = 1L;
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	
	private final String username;
	private final String password;
	private final String fullname;
	private final String street;
	private final String city;
	private final String state;
	private final String zip;
	private final String phoneNumber;

Other methods are irrelevant because they just override the UserDetails interface boolean methods to return just "true."

Now I get the Syntax error exception Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:. And when I run and navigate to my /register page on the web, I fill in the user details and submit for it to be inserted into the UserRepository:

import org.springframework.data.repository.CrudRepository;
import tacos1.User;

public interface UserRepository extends CrudRepository<User, Long> {

	User findByUsername(String username);
}

I get this error on the web:

There was an unexpected error (type=Internal Server Error, status=500).
could not prepare statement; SQL [insert into user (city, fullname, password, phone_number, state, street, username, zip, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)];

Caused by:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "insert into [*]user (city, fullname, password, phone_number, state, street, username, zip, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)"; expected "identifier"; 
英文:

I created a table using Spring Data JPA, so that means I did not type any SQL code. Now I am getting a warning about a Syntax error in my SQL statement like this:

Syntax error in SQL statement "create table [*]user (id bigint not null, city varchar(255), fullname varchar(255), password varchar(255), phone_number varchar(255), state varchar(255), street varchar(255), username varchar(255), zip varchar(255), primary key (id))"; expected "identifier";

This is a section of my "User" domain for getting user information

@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PRIVATE, force = true)
@RequiredArgsConstructor
public class User implements UserDetails{

	private static final long serialVersionUID = 1L;
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	
	private final String username;
	private final String password;
	private final String fullname;
	private final String street;
	private final String city;
	private final String state;
	private final String zip;
	private final String phoneNumber;
	

Other methods are irrelevant because they just override the UserDetails interface boolean methods to return just "true"

Now I get the Syntax error exception Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:. And when I run and navigate to my /register page on the web, I fill in the user details and submit for it to be inserted into the UserRepository:

import org.springframework.data.repository.CrudRepository;
import tacos1.User;

public interface UserRepository extends CrudRepository<User, Long> {

	User findByUsername(String username);
}

I get this error on the web:

There was an unexpected error (type=Internal Server Error, status=500).
could not prepare statement; SQL [insert into user (city, fullname, password, phone_number, state, street, username, zip, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)];

caused by:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "insert into [*]user (city, fullname, password, phone_number, state, street, username, zip, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)"; expected "identifier"; 

答案1

得分: 0

如果您的应用程序使用H2数据库,"User"是一个保留关键字,可以在文档中看到。

一个快速的解决办法是将您的实体名称重命名为"Users"或者您可以在JDBC URL中添加NON_KEYWORDS=user

spring.datasource.url=jdbc:h2:mem:mydb;NON_KEYWORDS=user

英文:

If you are using H2 database for your application, "User" is a reserved keyword as can be seen in the documentation.

A quick fix would be to rename your entity name to something like "Users" or you can add NON_KEYWORDS=user to your JDBC URL.

spring.datasource.url=jdbc:h2:mem:mydb;NON_KEYWORDS=user

huangapple
  • 本文由 发表于 2023年8月5日 16:14:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/76840714.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定