java - JPA:如何使用 SHA1 加密保留列？

Question

我对 JPA(以及扩展的 JPQL)相当陌生。希望有人能够启发我解决这个问题。

我尝试执行的查询...

String query = "select u from user u where u.email = '" + userEmail + "' and u.password = sha1('"+ userPassword + "')";
List resultList = emf.createEntityManager().createQuery(query).getResultList();

我收到以下异常...

Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [select u from Utilisateur u where u.email = 'myuser' and u.mot_Passe = sha1('mypassword')], line 1, column 73: unexpected token [(].
Internal Exception: NoViableAltException(83@[()* loopback of 383:9: (d= DOT right= attribute )*])
    at org.eclipse.persistence.exceptions.JPQLException.unexpectedToken(JPQLException.java:372)
...

显然，我在这里遗漏了一些东西，我应该以某种方式转义这个角色吗？以另一种方式指定它？我将非常感谢对此的任何帮助。

Answer 1

我认为JPA没有sha1函数。所以你有两个选择:

1. 您可以在 Java 代码上实现 sha1 函数，并在加载查询中的参数之前使用它们。

   String query = "select u from user u where u.email = :userEmail" +
      " and u.password = :userPassword";
   Query jpqlQuery = em.createQuery(query)
       .setParameter("userEmail", userEmail)
       .setParameter("userPassword",sha1(userPassword));
   

2. 如果您的数据库具有 sha1 函数，您可以编写 native 查询。检查此链接:http://www.oracle.com/technetwork/articles/vasiliev-jpql-087123.html

   List<Customer> customers = (List<Customer>)em.createNativeQuery
           ("SELECT * FROM customers", jpqlexample.entities.Customer.class)
                         .getResultList(); 
   Iterator i = customers.iterator();
   Customer cust;
   while (i.hasNext()) {
       cust = (Customer) i.next();
       //do something
   }