英文:
Spring JPA find by method is getting SQL statement error
问题
我正在为餐厅评论创建一个API。我正在使用Spring MVC方法,并尝试使用POST方法将用户添加到我的数据库,URL为http://localhost:8080/user/add。我能够连接到我的Spring应用程序,并且我看到一切都按预期工作,直到我的应用程序尝试运行我在repo中创建的findByUsername方法。
以下是我的模型类
package com.dining.model;
import org.springframework.stereotype.Component;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Component
@NoArgsConstructor
@AllArgsConstructor
@Data
@Entity(name = "User")
@Table(name = "User")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String username;
private String city;
private String state;
private String zip;
private boolean peanutAll;
private boolean eggAll;
private boolean dairyAll;
}
控制器
package com.dining.controller;
import java.util.Optional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.dining.exceptions.UserAlreadyExistsException;
import com.dining.model.User;
import com.dining.service.UserService;
import jakarta.validation.Valid;
@RestController
@RequestMapping(path = "/user")
public class UserController {
@Autowired UserService userService;
@Autowired User returnUser;
@PostMapping("/add")
public ResponseEntity<User> addUser(@Valid @RequestBody User user) {
System.out.print(user.getUsername());
Optional<User> tempUser = userService.getUser(user.getUsername());
if (tempUser.isEmpty()) {
returnUser = userService.addUser(user);
return ResponseEntity.ok(returnUser);
} else {
throw new UserAlreadyExistsException(String.format("User %s already exists", user.getUsername()));
}
}
}
服务层
package com.dining.service;
import java.util.Optional;
import java.util.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.dining.data.UserRepo;
import com.dining.exceptions.ChangeUsernameException;
import com.dining.exceptions.UserNotFoundException;
import com.dining.model.User;
@Service
public class UserService {
@Autowired UserRepo userRepo;
@Autowired User tempUser;
public User addUser(User user) {
try {
if (userRepo.findByUsername(user.getUsername()) != null) {
throw new SecurityException();
} else {
tempUser = userRepo.save(user);
//未来的实现是添加安全性(密码)
return tempUser;
}
} catch (SecurityException ex) {
ex.printStackTrace();
System.out.printf("Username %s", user.getUsername());
} catch (Exception ex) {
ex.printStackTrace();
}
}
public Optional<User> getUser(String username) {
Optional<User> tempUser = userRepo.findByUsername(username);
if (tempUser.isEmpty()) {
throw new UserNotFoundException(String.format("Unable to find user: %s", username));
} else {
return tempUser;
}
}
}
package com.dining.data;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.dining.model.User;
@Repository
public interface UserRepo extends JpaRepository<User, Integer> {
Optional<User> findByUsername(String username);
}
我尝试将此JSON对象发送作为请求的主体。我认为SQL错误来自于未读取用户名值。它在SQL语句中显示为username=?
。
{
"id": 1,
"username": "Jb",
"city": "toledo",
"state": "NC",
"zip": "43706",
"peanutAll": "false",
"eggAll": "false",
"dairyAll": "false"
}
我注意到,如果我在对象中拼错了用户名键,它不会引发错误。它仍然尝试运行SQL语句,但显示username=null
。以下是我得到的错误堆栈跟踪。
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement [Syntax error in SQL statement "select u1_0.id,u1_0.city,u1_0.dairy_all,u1_0.egg_all,u1_0.peanut_all,u1_0.state,u1_0.username,u1_0.zip from [*]user u1_0 where u1_0.username=?"; expected "identifier"; SQL statement:
select u1_0.id,u1_0.city,u1_0.dairy_all,u1_0.egg_all,u1_0.peanut_all,u1_0.state,u1_0.username,u1_0.zip from user u1_0 where u1_0.username=? [42001-214]] [select u1_0.id,u1_0.city,u1_0.dairy_all,u1_0.egg_all,u1_0.peanut_all,u1_0.state,u1_0.username,u1_0.zip from user u1_0 where u1_0.username=?]
英文:
I am creating a API for a dining review. I am using spring MVC method and trying to add a user to my database using a post method at the url of http://localhost:8080/user/add . I am able to connect to my spring app and I see that everything is working as expected until my application attempts to run the findByUsername method I've created in my repo.
Here is my model class
package com.dining.model;
import org.springframework.stereotype.Component;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Component
@NoArgsConstructor
@AllArgsConstructor
@Data
@Entity(name = "User")
@Table(name = "User")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String username;
private String city;
private String state;
private String zip;
private boolean peanutAll;
private boolean eggAll;
private boolean dairyAll;
}
Controller
package com.dining.controller;
import java.util.Optional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.dining.exceptions.UserAlreadyExistsException;
import com.dining.model.User;
import com.dining.service.UserService;
import jakarta.validation.Valid;
@RestController
@RequestMapping(path="/user")
public class UserController {
@Autowired UserService userv;
@Autowired User returnUser;
@PostMapping("/add")
public ResponseEntity<User> addUser(@Valid @RequestBody User user){
System.out.print(user.getUsername());
Optional<User> tempUser = userv.getUser(user.getUsername());
if(tempUser.isEmpty()) {
returnUser = userv.addUser(user);
return ResponseEntity.ok(returnUser);
}
else {
throw new UserAlreadyExistsException(String.format("User %s already exists", user.getUsername()));
}
}
}
Service layer
package com.dining.service;
import java.util.Optional;
import java.util.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.dining.data.UserRepo;
import com.dining.exceptions.ChangeUsernameException;
import com.dining.exceptions.UserNotFoundException;
import com.dining.model.User;
@Service
public class UserService {
@Autowired UserRepo userRepo;
@Autowired User tempUser;
public User addUser(User user) {
try {
if(userRepo.findByUsername(user.getUsername()) != null) {
throw new SecurityException();
}
else {
tempUser = userRepo.save(user);
//future implementation is to add security (password)
return tempUser;
}
}
catch(SecurityException ex) {
ex.printStackTrace();
System.out.printf("Username %s", user.getUsername())
}
catch(Exception ex) {
ex.printStackTrace();
}
}
public Optional<User> getUser(String username) {
Optional <User> tempUser = userRepo.findByUsername(username);
if(tempUser.isEmpty()){
throw new UserNotFoundException(String.format("Unable to find user: %s", username));
}
else {
return tempUser;
}
}
}
package com.dining.data;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.dining.model.User;
@Repository
public interface UserRepo extends JpaRepository<User, Integer>{
Optional<User> findByUsername(String username);
}
I have tried sending this JSON object as my body of my request. I believe the sql error comes from username value not being read. It shows username=? in the sql statement
{
"id": 1,
"username":"Jb",
"city": "toledo",
"state": "NC",
"zip": "43706",
"peanutAll":"false",
"eggAll":"false",
"dairyAll":"false"
}
I am noticing that if I mispell the username key in my object it isn't throwing an error. It still attempts to run the sql statement but shows username=null . Here is the error stack tracing im getting
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement [Syntax error in SQL statement "select u1_0.id,u1_0.city,u1_0.dairy_all,u1_0.egg_all,u1_0.peanut_all,u1_0.state,u1_0.username,u1_0.zip from [*]user u1_0 where u1_0.username=?"; expected "identifier"; SQL statement:\nselect u1_0.id,u1_0.city,u1_0.dairy_all,u1_0.egg_all,u1_0.peanut_all,u1_0.state,u1_0.username,u1_0.zip from user u1_0 where u1_0.username=? [42001-214]] [select u1_0.id,u1_0.city,u1_0.dairy_all,u1_0.egg_all,u1_0.peanut_all,u1_0.state,u1_0.username,u1_0.zip from user u1_0 where u1_0.username=?]; SQL [select u1_0.id,u1_0.city,u1_0.dairy_all,u1_0.egg_all,u1_0.peanut_all,u1_0.state,u1_0.username,u1_0.zip from user u1_0 where u1_0.username=?]\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:256)\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:229)\r\n\tat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)\r\n\tat org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)\r\n\tat org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)\r\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\r\n\tat org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\r\n\tat org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)\r\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244)\r\n\tat jdk.proxy5/jdk.proxy5.$Proxy120.findByUsername(Unknown Source)\r\n\tat com.dining.service.UserService.getUser(UserService.java:48)
答案1
得分: 1
你现在使用的数据库是H2还是任何将USER作为保留关键字的数据库?根据错误堆栈跟踪,我怀疑是这个问题。尝试将你的表重命名为自定义名称,例如UserSummary,看看是否仍然可以复现这个问题。
或者你可以在application.properties中使用以下配置:
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
关于这个问题的更多信息可以在这里找到。
英文:
is the database you use H2 or any database that has USER as a reserved keyword? From the error stack trace this is what I am suspecting. Try to rename your table to something custom, like UserSummary, and see if this is still reproducible.
Or you can use this in application.properties:
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
More about this here.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论