我有以下查詢
package gt.com.anibal.dao;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import gt.com.anibal.model.Order;
@Repository
public interface OrderDao extends JpaRepository<Order, Long>{
//@Query("SELECT o FROM Order o")
@Query(value = "SELECT * FROM Order o WHERE o.price = ?1", nativeQuery = true) //native query
public List<Order> findOrder(double price);
}
這就是錯誤
SELECT
*
FROM
Order o WHERE
o.price = ?
10:13 TRACE 13460 --- [nio-9091-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [DOUBLE] - [25.0]
10:13 WARN 13460 --- [nio-9091-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1064, SQLState: 42000
10:13 ERROR 13460 --- [nio-9091-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper : 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 'Order o WHERE o.price = 25.0' at line 1
10:13 ERROR 13460 --- [nio-9091-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
java.sql.SQLSyntaxErrorException: 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 'Order o WHERE o.price = 25.0' at line 1
如果我在 MySQL 上運行查詢，它作業正常
SELECT
*
FROM
test.Order o WHERE
o.price = 25.0
但我認為 JpaRepository 它在 from 和 where 之間創建了一個空格，當我在另一個物體中使用 JpaRepository 時，它在我的控制臺中的查詢不同
package gt.com.anibal.dao;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import gt.com.anibal.model.Persona;
@Repository
public interface PersonaDao extends JpaRepository<Persona, Long> {
}
這是結果，我沒有看到 from 和下一行之間的空格，我不知道這是問題還是另一個問題。
select
persona0_.id_persona as id_perso1_3_,
persona0_.apellido as apellido2_3_,
persona0_.email as email3_3_,
persona0_.nombre as nombre4_3_,
persona0_.telefono as telefono5_3_
from
persona persona0_
這是我的物體
package gt.com.anibal.model;
import java.sql.Date;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@Entity
@Table( name = "order" )
public class Order {
@Id
@Column(name = "idorder", columnDefinition = "int")
private Long idOrder;
@Column( name = "description")
private String descriptions;
@OneToMany(mappedBy = "order")
private Set<Customer> customer = new HashSet<>();
@Column( name = "date")
private Date date;
@Column( name = "price", precision = 10, scale = 2)
private double price;
public Long getIdOrder() {
return idOrder;
}
public void setIdOrder(Long idOrder) {
this.idOrder = idOrder;
}
public String getDescriptions() {
return descriptions;
}
public void setDescriptions(String descriptions) {
this.descriptions = descriptions;
}
public Set<Customer> getCustomer() {
return customer;
}
public void setCustomer(Set<Customer> customer) {
this.customer = customer;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
}
這是我在 MySQL 上的表
CREATE TABLE `order` (
`idOrder` int NOT NULL,
`description` varchar(45) NOT NULL,
`idCustomer` varchar(45) NOT NULL,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`price` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`idOrder`),
KEY `fk_order_customer_idx` (`idOrder`),
CONSTRAINT `fk_order_customer` FOREIGN KEY (`idOrder`) REFERENCES `customer` (`idCustomer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
uj5u.com熱心網友回復：
我建議對這種簡單的查詢使用JPA 查詢方法。
您的方法應命名如下：
@Repository
public interface OrderDao extends JpaRepository<Order, Long>{
List<Order> findByPrice(double price);
}
這是一個更干凈的代碼。
如果您想在更多情況下使用本機查詢，則應將“？1”替換為方法變數名稱，并以分號為前綴，例如“：price”。
uj5u.com熱心網友回復：
@rortegate 建議的是撰寫簡單查詢的好方法[至少對我而言]。現在，在您的查詢中：
@Query(value = "SELECT * FROM Order o WHERE o.price = ?1", nativeQuery = true)
這*意味著所有列的行/記錄，但您的方法回傳List<Order>不兼容。相反，像這樣更改您的查詢
@Query(value = "SELECT o FROM Order o WHERE o.price = ?1", nativeQuery = true：)
在這里，SELECT o FROM Order o這部分意味著您通過您的別名從查詢中回傳 Order 實體o。
uj5u.com熱心網友回復：
當您使用 nativeQuery = true 時，它??將解釋為您可以直接在 MySQL 中執行的標準 SQL。如果您洗掉 nativeQuery = true，@Query 將解釋為 JPQL。你想用哪一個？
試試這個：
Query(value = "SELECT * FROM Order WHERE price = ?1", nativeQuery = true) //原生查詢
當 nativeQuery 為 true 時，Order 必須是表名，price 必須是列名。
或者，試試這個來使用 jpql：
查詢（價值=“從訂單選擇o WHERE o.price = ?1”）
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/419980.html
標籤：