MySQL 使用 Spring DATA JPA 创建自定义查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42966967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a custom query with Spring DATA JPA?
提问by B378
I'm working on a project with Spring Data JPA. I have a table in the database as my_query.
我正在使用 Spring Data JPA 开发一个项目。我在数据库中有一个表作为 my_query。
I want to create a method which takes a string as a parameter, and then execute it as a query in the database.
我想创建一个将字符串作为参数的方法，然后将其作为数据库中的查询执行。
Method:
方法：
executeMyQuery(queryString)
As example, when I pass
例如，当我通过
queryString= "SELECT * FROM my_query"
then it should run that query in DB level.
那么它应该在数据库级别运行该查询。
The repository class is as follows.
存储库类如下。
public interface MyQueryRepository extends JpaRepository<MyQuery, Long>{
public MyQuery findById(long id);
@Modifying(clearAutomatically = true)
@Transactional
@Query(value = "?1", nativeQuery = true)
public void executeMyQuery(String query);
}
However, it didn't work as I expected. It gives the following error.
然而，它并没有像我预期的那样工作。它给出了以下错误。
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''select * from my_query;'' at line 1
Is there any other way, that I could achieve this goal. Thanks in advance
有没有其他方法，我可以实现这个目标。提前致谢
回答by ilya
The only part of it you can parameterise are values used in WHEREclause. Consider this sample from official doc:
您可以参数化的唯一部分是WHERE子句中使用的值。考虑来自官方文档的这个示例：
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
User findByEmailAddress(String emailAddress);
}
回答by Jens Schauder
There is no special support for this. But what you can do is create a custom method with a Stringparameter and in your implementation get the EntityManagerinjected and execute it.
对此没有特别的支持。但是您可以做的是创建一个带有String参数的自定义方法，并在您的实现中获取EntityManager注入并执行它。
Possibly helpful links:
可能有用的链接：
How to access entity manager with spring boot and spring data
如何使用 spring boot 和 spring 数据访问实体管理器
Note: I would reconsider if what you are trying to do is a good idea because it bleeds implementation details of the repository into the rest of the application.
注意：我会重新考虑您尝试做的是否是一个好主意，因为它会将存储库的实现细节渗透到应用程序的其余部分中。
回答by Md. Sajedul Karim
Using EntityManager you can achieve this .
使用 EntityManager 您可以实现这一点。
Suppose your entity class is like bellow:
假设您的实体类如下所示：
import javax.persistence.*;
import java.math.BigDecimal;
@Entity
@Table(name = "USER_INFO_TEST")
public class UserInfoTest {
private int id;
private String name;
private String rollNo;
public UserInfoTest() {
}
public UserInfoTest(int id, String name) {
this.id = id;
this.name = name;
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", nullable = false, precision = 0)
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Basic
@Column(name = "name", nullable = true)
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Basic
@Column(name = "roll_no", nullable = true)
public String getRollNo() {
return rollNo;
}
public void setRollNo(String rollNo) {
this.rollNo = rollNo;
}
}
And your query is "select id, name from users where roll_no = 1001".
您的查询是“ select id, name from users where roll_no = 1001”。
Here query will return an object with id and a name column. Your Response class is like below:
这里查询将返回一个带有 id 和 name 列的对象。您的 Response 类如下所示：
Your Response class is like:
你的 Response 类是这样的：
public class UserObject{
int id;
String name;
String rollNo;
public UserObject(Object[] columns) {
this.id = (columns[0] != null)?((BigDecimal)columns[0]).intValue():0;
this.name = (String) columns[1];
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRollNo() {
return rollNo;
}
public void setRollNo(String rollNo) {
this.rollNo = rollNo;
}
}
here UserObject constructor will get an Object Array and set data with the object.
这里 UserObject 构造函数将获取一个对象数组并使用该对象设置数据。
public UserObject(Object[] columns) {
this.id = (columns[0] != null)?((BigDecimal)columns[0]).intValue():0;
this.name = (String) columns[1];
}
Your query executing function is like bellow :
您的查询执行功能如下所示：
public UserObject getUserByRoll(EntityManager entityManager,String rollNo) {
String queryStr = "select id,name from users where roll_no = ?1";
try {
Query query = entityManager.createNativeQuery(queryStr);
query.setParameter(1, rollNo);
return new UserObject((Object[]) query.getSingleResult());
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}
Here you have to import bellow packages:
在这里你必须导入波纹管包：
import javax.persistence.Query;
import javax.persistence.EntityManager;
Now your main class, you have to call this function. First get EntityManager and call this getUserByRoll(EntityManager entityManager,String rollNo)function. Calling procedure is given below:
现在你的主类，你必须调用这个函数。首先拿到EntityManager，调用这个getUserByRoll(EntityManager entityManager,String rollNo)函数。调用流程如下：
Here is the Imports
这里是进口
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
get EntityManagerfrom this way:
EntityManager从这种方式得到：
@PersistenceContext
private EntityManager entityManager;
UserObject userObject = getUserByRoll(entityManager,"1001");
Now you have data in this userObject.
现在您在这个 userObject 中有了数据。
Note:
注意：
query.getSingleResult()return a object array. You have to maintain the column position and data type with query column position.
query.getSingleResult()返回一个对象数组。您必须使用查询列位置来维护列位置和数据类型。
select id,name from users where roll_no = 1001
从 roll_no = 1001 的用户中选择 id,name
query return a array and it's [0] --> id and 1-> name.
查询返回一个数组，它是 [0] --> id 和1-> name。
More info visit this thread.
更多信息请访问此线程。
回答by Austine Gwa
Thank you @ilya. Is there an alternative approach to achieve this task using Spring Data JPA? Without @Query annotation?
谢谢@ilya。是否有使用 Spring Data JPA 完成此任务的替代方法？没有@Query 注释？
I just want to act on this part. yes there is a way you can go about it without using the @query annotation. what you need is to define a derived query from your interface that implements the JPA repository instance.
我只想在这部分采取行动。是的，有一种方法可以在不使用 @query 注释的情况下进行。您需要的是从实现 JPA 存储库实例的接口定义派生查询。
then from your repository instance you will be exposed to all the methods that allow CRUD operations on your database such as
然后从您的存储库实例中，您将接触到所有允许对数据库进行 CRUD 操作的方法，例如
interface UserRepository extends CrudRepository<User, Long> {
long deleteByLastname(String lastname);
List<User> removeByLastname(String lastname);
}
with these methods spring data will understand what you are trying to archieve and implement them accordingly.
使用这些方法，spring data 将了解您要归档的内容并相应地实施它们。
Also put in mind that the basic CRUD operations are provided from the base class definition and you do not need to re define them. for instance this is the JPARepository class as defined by spring so extending it gives you all the methods.
还要记住，基本的 CRUD 操作是从基类定义中提供的，您不需要重新定义它们。例如，这是由 spring 定义的 JPARepository 类，因此扩展它可以为您提供所有方法。
public interface CrudRepository<T, ID extends Serializable>
extends Repository<T, ID> {
<S extends T> S save(S entity);
Optional<T> findById(ID primaryKey);
Iterable<T> findAll();
long count();
void delete(T entity);
boolean existsById(ID primaryKey);
}
For more current information check out the documentation at https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
有关更多最新信息，请查看https://docs.spring.io/spring-data/jpa/docs/current/reference/html/ 上的文档
回答by B378
Based on @jelies answer, I am using the following approach
基于@jelies回答，我使用以下方法
You can create another interface for your custom methods (as example MyQueryCustom) and then implement it as follows.
您可以为自定义方法创建另一个接口（例如 MyQueryCustom），然后按如下方式实现它。
public class MyQueryRepositoryImpl implements MyQueryRepositoryCustom {
@PersistenceContext
private EntityManager entityManager;
public int executeQuery(String query) {
return entityManager.createNativeQuery(query).executeUpdate();
}
}
This will execute a custom query.
这将执行自定义查询。
回答by milan zadfiya
if you want to add custom query you should add @Param
如果你想添加自定义查询，你应该添加 @Param
@Query("from employee where name=:name")
employee findByName(@Param("name)String name);
}
this query will select unique record with match name.this will work
此查询将选择匹配的唯一记录。这name将起作用
