CrudRepository带参数的查询

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

CrudRepository query with parameter

问题

我想知道是否有任何方法可以在传递给仓库的函数中使用参数，然后在 @Query 中使用该参数。

我想要按游戏平台对用户进行排序，所以我在我的 UserRepository 中添加了以下函数：

  1. @Repository
  2. public interface UserRepository extends CrudRepository<DbUser, Integer> {
  4.     @Query("SELECT * from users WHERE platform = *****在这里放入参数***** ")
  5.     public List<DbUser> findAllByPlatform(String platform);
  6. }

有人知道这是否可能？如果可能，应该怎么做？如果不行，是否有干净的解决方法？提前感谢。

编辑：我的 DbUser 类：

  1. @Entity
  2. @Table(name = "users")
  3. public class DbUser {
  4.     @Id
  5.     @GeneratedValue(strategy = GenerationType.AUTO)
  6.     @Column(name="user_id")
  7.     private int UserId;
  9.     @Column(name="user_name")
  10.     private String UserName;
  12.     @Column(name="email_address")
  13.     private String EmailAddress;
  15.     @Column(name="password_hash")
  16.     private int PasswordHash;
  18.     @Column(name="platform")
  19.     private String Platform;
  21.     @Column(name="platformid")
  22.     private String PlatformID;
  24.     @Convert(converter = StringListConvertor.class)
  25.     private ArrayList<String> Wishlist;
  27.     public DbUser(String userName, String emailAddress, int passwordHash, String platform, String platformID, String newWishlistItem){
  28.         UserName = userName;
  29.         EmailAddress = emailAddress;
  30.         PasswordHash = passwordHash;
  31.         Platform = platform;
  32.         PlatformID = platformID;
  33.         Wishlist.add(newWishlistItem);
  34.     }
  36.     public DbUser() {
  38.     }
  40.     public int getUserId() {
  41.         return UserId;
  42.     }
  44.     public void setUserId(int userId) {
  45.         UserId = userId;
  46.     }
  48.     public String getUserName() {
  49.         return UserName;
  50.     }
  52.     public void setUserName(String userName) {
  53.         UserName = userName;
  54.     }
  56.     public String getEmailAddress() {
  57.         return EmailAddress;
  58.     }
  60.     public void setEmailAddress(String emailAddress) {
  61.         EmailAddress = emailAddress;
  62.     }
  64.     public int getPasswordHash() {
  65.         return PasswordHash;
  66.     }
  68.     public void setPasswordHash(int passwordHash) {
  69.         PasswordHash = passwordHash;
  70.     }
  72.     public String getPlatform() {
  73.         return Platform;
  74.     }
  76.     public void setPlatform(String platform) {
  77.         Platform = platform;
  78.     }
  80.     public String getPlatformID() {
  81.         return PlatformID;
  82.     }
  84.     public void setPlatformID(String platformID) {
  85.         PlatformID = platformID;
  86.     }
  88.     public ArrayList<String> getWishlist() {
  89.         return Wishlist;
  90.     }
  92.     public void setWishlist(ArrayList<String> wishlist) {
  93.         Wishlist = wishlist;
  94.     }
  95. }
英文:

I would like to know if there is any way to use a parameter from a function I pass to a repository can be used in the @Query.

I would like to sort users by gaming platform so I added the following function to my UserRepository:

  1. @Repository
  2. public interface UserRepository extends CrudRepository&lt;DbUser, Integer&gt; {
  3. @Query(&quot;SELECT * from users WHERE platform = *****parameter here***** &quot;)
  4. public List&lt;DbUser&gt; findAllByPlatform(String platform);
  5. }

Does anybody know if this is possible? If so, how? If not, is there a clean workaround? Thanks in advance.

EDIT: My DbUser class:

  1. @Entity
  2. @Table(name = &quot;users&quot;)
  3. public class DbUser {
  4. @Id
  5. @GeneratedValue(strategy = GenerationType.AUTO)
  6. @Column(name=&quot;user_id&quot;)
  7. private int UserId;
  8. @Column(name=&quot;user_name&quot;)
  9. private String UserName;
  10. @Column(name=&quot;email_address&quot;)
  11. private String EmailAddress;
  12. @Column(name=&quot;password_hash&quot;)
  13. private int PasswordHash;
  14. @Column(name=&quot;platform&quot;)
  15. private String Platform;
  16. @Column(name=&quot;platformid&quot;)
  17. private String PlatformID;
  18. @Convert(converter = StringListConvertor.class)
  19. private ArrayList&lt;String&gt; Wishlist;
  20. public DbUser(String userName, String emailAddress, int passwordHash, String platform, String platformID, String newWishlistItem){
  21. UserName = userName;
  22. EmailAddress = emailAddress;
  23. PasswordHash = passwordHash;
  24. Platform = platform;
  25. PlatformID = platformID;
  26. Wishlist.add(newWishlistItem);
  27. }
  28. public DbUser() {
  29. }
  30. public int getUserId() {
  31. return UserId;
  32. }
  33. public void setUserId(int userId) {
  34. UserId = userId;
  35. }
  36. public String getUserName() {
  37. return UserName;
  38. }
  39. public void setUserName(String userName) {
  40. UserName = userName;
  41. }
  42. public String getEmailAddress() {
  43. return EmailAddress;
  44. }
  45. public void setEmailAddress(String emailAddress) {
  46. EmailAddress = emailAddress;
  47. }
  48. public int getPasswordHash() {
  49. return PasswordHash;
  50. }
  51. public void setPasswordHash(int passwordHash) {
  52. PasswordHash = passwordHash;
  53. }
  54. public String getPlatform() {
  55. return Platform;
  56. }
  57. public void setPlatform(String platform) {
  58. Platform = platform;
  59. }
  60. public String getPlatformID() {
  61. return PlatformID;
  62. }
  63. public void setPlatformID(String platformID) {
  64. PlatformID = platformID;
  65. }
  66. public ArrayList&lt;String&gt; getWishlist() {
  67. return Wishlist;
  68. }
  69. public void setWishlist(ArrayList&lt;String&gt; wishlist) {
  70. Wishlist = wishlist;
  71. }
  72. }

答案1

得分: 1

如果您正在使用Spring Data，请使用@Param注解参数，并提供要在查询中使用的变量名称：

  1. @Query("SELECT * FROM users WHERE platform = :pltfrm")
  2. public List<DbUser> findAllByPlatform(@Param("pltfrm") String platform);
英文:

If you're using Spring data, annotate parameter with @Param and supply variable name to be used in query:

  1. @Query(&quot;SELECT * from users WHERE platform = :pltfrm&quot;)
  2. public List&lt;DbUser&gt; findAllByPlatform(@Param(&quot;pltfrm&quot;) String platform);

答案2

得分: 0

你可以像这样做：

  1. @Query("SELECT * from users WHERE platform = %?1")

spring data jpa 文档

英文:

You can do something like that

  1. @Query(&quot;SELECT * from users WHERE platform = %?1&quot;)

spring data jpa documentation

huangapple
  • 本文由 发表于 2020年9月21日 20:48:17
  • 转载请务必保留本文链接：https://go.coder-hub.com/63992600.html
  • intellij-idea
  • java
  • jpa
  • spring
  • sql
匿名

发表评论

匿名网友

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

确定