Spring JPA的findBy方法出现SQL语句错误。

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

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 = &quot;User&quot;)
@Table(name = &quot;User&quot;)
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=&quot;/user&quot;)
public class UserController {
	@Autowired UserService userv;
	@Autowired User returnUser;
	@PostMapping(&quot;/add&quot;)
	public ResponseEntity&lt;User&gt; addUser(@Valid @RequestBody User user){
		System.out.print(user.getUsername());
	Optional&lt;User&gt; tempUser =	userv.getUser(user.getUsername());
	if(tempUser.isEmpty()) {
		returnUser = userv.addUser(user);
		return ResponseEntity.ok(returnUser);
	}
	else {
		throw new UserAlreadyExistsException(String.format(&quot;User %s already exists&quot;, 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(&quot;Username %s&quot;, user.getUsername())
			
		}
		catch(Exception ex) {
			ex.printStackTrace();
		}
		
		
	}
	
	public Optional&lt;User&gt; getUser(String username) {
		
		Optional &lt;User&gt; tempUser = userRepo.findByUsername(username);
		if(tempUser.isEmpty()){
			throw new UserNotFoundException(String.format(&quot;Unable to find user: %s&quot;, 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&lt;User, Integer&gt;{
	


	Optional&lt;User&gt; 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

{
    &quot;id&quot;: 1,
    &quot;username&quot;:&quot;Jb&quot;,
    &quot;city&quot;: &quot;toledo&quot;,
    &quot;state&quot;: &quot;NC&quot;,
    &quot;zip&quot;: &quot;43706&quot;,
    &quot;peanutAll&quot;:&quot;false&quot;,
    &quot;eggAll&quot;:&quot;false&quot;,
    &quot;dairyAll&quot;:&quot;false&quot;
}

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 &quot;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=?&quot;; expected &quot;identifier&quot;; 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.

huangapple
  • 本文由 发表于 2023年7月10日 20:08:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76653589.html
匿名

发表评论

匿名网友

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

确定