英文:
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)@RequiredArgsConstructorpublic 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)@RequiredArgsConstructorpublic 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论