我正在嘗試使用外部應用從具有本機 SQL 的兩個表/物體中獲取結果。在 SQL Server 中，我得到了正確的答案，但是，使用相同的查詢，我在 JPA 中得到了不同的結果。在 SQL Server 中，我只從第二個表中獲取最后一個 ID（應該如此），而在 JPA 中我從第二個表中獲取所有 ID。如何僅從第二個表中獲取最后一個 ID？
public List<Client> getClients()
{
List results = entityManager.createNativeQuery("SELECT top 17 c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Credit, p.Debit\n"
"FROM tblCustomer c OUTER APPLY\n"
" (SELECT TOP 1 p.*\n"
" FROM tblPayments p\n"
" WHERE c.CustomerNumber = p.CustomerNumber\n"
" ORDER BY p.id DESC\n"
" ) p;",
Client.class)
.getResultList();
return results;
}
SQL Server 結果
{
"id": 1023,
"customerNumber": "000001",
"name": "Pajazit",
"surname": "Neziri",
"area": "1",
"city": "?egran",
"address": "Bake",
"phoneNumber": "",
"customerType": 2,
"enabled": false,
"dateCreated": "2016-10-25",
"payments": [
{
"id": 16238,
"customerNumber": "000001",
"invoiceNumber": "9000001112018",
"monthOfPayment": "11-2018",
"dateOfPayment": "2018-11-18",
"credit": 1200.0,
"debit": 0.0,
"paymentPeriod": "M",
"operator": 0,
"printed": "T",
"paied": "T",
"isInvoice": "T"
},
{
"id": 20459,
"customerNumber": "000001",
"invoiceNumber": "900000112019",
"monthOfPayment": "1-2019",
"dateOfPayment": "2019-01-09",
"credit": 0.0,
"debit": 600.0,
"paymentPeriod": "M",
"operator": 0,
"printed": "T",
"paied": "T",
"isInvoice": "T"
},
{
"id": 20538,
"customerNumber": "000001",
"invoiceNumber": "2000001112018",
"monthOfPayment": "11-2018",
"dateOfPayment": "2019-01-09",
"credit": 0.0,
"debit": 600.0,
"paymentPeriod": "M",
"operator": 1,
"printed": "T",
"paied": "T",
"isInvoice": "T"
},
{
"id": 20557,
"customerNumber": "000001",
"invoiceNumber": "2000001122018",
"monthOfPayment": "12-2018",
"dateOfPayment": "2019-01-09",
"credit": 0.0,
"debit": 600.0,
"paymentPeriod": "M",
"operator": 1,
"printed": "T",
"paied": "T",
"isInvoice": "T"
},
{
"id": 20576,
"customerNumber": "000001",
"invoiceNumber": "2000001012019",
"monthOfPayment": "01-2019",
"dateOfPayment": "2019-01-09",
"credit": 0.0,
"debit": 600.0,
"paymentPeriod": "M",
"operator": 1,
"printed": "T",
"paied": "T",
"isInvoice": "T"
},....
uj5u.com熱心網友回復：
您可以使用與您的查詢非常相似的 JPQL 查詢。
select t from tblPayments t order by t.id desc
如果要添加基于搜索最后一條記錄的條件，也可以在上面的查詢中輸入。
建立 Query 物件后，您可以呼叫
query.getSingleResult() or call query.setMaxResults(1)
uj5u.com熱心網友回復：
更改List results為List<Client>因為您需要多個資料。
下面是修改后的代碼：
public List<Client> getClients()
{
List<Client> results = entityManager.createNativeQuery("SELECT top 17 c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Credit, p.Debit FROM tblCustomer c OUTER APPLY (SELECT TOP 1 p.* FROM tblPayments p WHERE c.CustomerNumber = p.CustomerNumber ORDER BY p.id DESC) p", Client.class).getResultList();
return results;
}
uj5u.com熱心網友回復：
出于某種原因，使用 EntityManager 時，回應永遠不會與來自 SQL Server 的回應相同。然后我嘗試使用 JPQL，答案再次與 SQL Server 的答案不同。做了更多的研究，讓我找到了另一種方法，使用 JDBC RowMapper 并最終使用它，我獲得了與 SQL Server 相同的結果，如下所示：
@GetMapping("/testi2")
public List<CustomerResponse> getCCP2() {
String sql = "SELECT top 1 c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit\n"
"FROM TblCustomer c OUTER APPLY\n"
" (SELECT TOP 1 p.*\n"
" FROM TblPayments p\n"
" WHERE c.CustomerNumber = p.CustomerNumber\n"
" ORDER BY p.id DESC \n"
" ) p;";
List<CustomerResponse> result = jdbcTemplate.query(
sql,
new BeanPropertyRowMapper(CustomerResponse.class));
return result;
}
}
但是，謝謝@Sathiyaraj 和@Faeemazaz Bhanej。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/363269.html