我正试图用SpringBoot和JPArepository来完成这句SQL语句：
SELECT serie, nombre, activo FROM proveedor ORDER BY activo desc, nombre asc;
这句话非常适合对PGADMIN中的列进行排序，但是，在springBoot中，我只能对其中一列进行排序(在我的端点API中将其作为参数传递(，我的问题是如何将这句SQL语句传递给springBoot，并在同一请求中对这两列进行排序？
这是我的代码：
实体：
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.Data;
/**
* Class that models the entity Proveedores as table of the database
* @author ssc
*/
@Entity
@Table(name = "proveedor")
@Data
public class Provider {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int serie;
@Column(name = "nombre")
private String name;
@Column(name = "identificacion")
private String identification;
@Column(name = "corte_ultima_factura")
private String dueDateLastBill;
@Column(name = "valor_ultima_factura")
private Double amountLastBill; // null, it needs wrapper to execute as double is primitive and won't accept nullables.
@Column(name = "direccion")
private String address;
@Column(name = "telefono")
private String cellphone;
@Column(name = "ciudad")
private String city;
@Column(name = "pais")
private String country;
@Column(name = "email")
private String emailAddress;
@Column(name = "persona_contacto")
private String contactPerson;
@Column(name = "periodo_facturacion")
private String billingPeriod;
@Column(name = "activo")
private boolean status;
@Column(name = "inicio_operacion")
private String operationStart;
@Column(name = "email2")
private String emailAddress2;
@Column(name = "email3")
private String emailAddress3;
@Column(name = "email4")
private String emailAddress4;
@Column(name = "email5")
private String emailAddress5;
@Column(name = "valor_mora")
private double overdueAmount;
@Column(name = "valor_mora_inicial")
private double initialOverdueAmount;
@Column(name = "fecha_mora_inicial")
private String initialOverdueDate;
}
REPOSITORY：我不需要在这个功能中使用分页，所以我避免使用选项
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.ssc.test.cb3.model.Provider;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
//import org.springframework.data.repository.PagingAndSortingRepository;
/**
* Class that extends to the repository for database management queries with table
proveedor
* @author ssc
*/
@Repository
public interface ProviderRepository extends JpaRepository<Provider, Integer>{
}
服务：
package com.ssc.test.cb3.service;
import com.ssc.test.cb3.model.Provider;
import com.ssc.test.cb3.repository.ProviderRepository;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
//import org.springframework.data.domain.Page;
//import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
/**
* Class to prepare the services to be dispatched upon request regarding Providers.
* @author ssc
*/
@Service
public class ProviderService {
@Autowired
private ProviderRepository providerRepository;
/**
* Function to get all customers
* @return a complete list of customers
*/
public List<Provider> getAllProviders() {
return providerRepository.findAll();
}
/**
* Functionality to create a new provider
* @param provider receives an objet Provider to be saved on the database
* @return the action of saving the provider in the database.
*/
public Provider createProvider(Provider provider){
return providerRepository.save(provider);
}
/**
* Service to sort the list of providers
* @param sortBy represents whether the list will be sort ASCending or DEScending
* @return List sorted in ascending or descending order
*/
public List<Provider> getSortingList(String column){
return providerRepository.findAll(Sort.by(Sort.Direction.DESC, column));
}
}
控制器：
package com.ssc.test.cb3.controller;
import com.ssc.test.cb3.model.Provider;
import com.ssc.test.cb3.service.ProviderService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* Class to handle REST services and APIs for the provider's class
* @author ssc
*/
@RestController
@RequestMapping("/v1/providers")
@CrossOrigin(origins = "http://localhost:3000")
public class ProviderController {
@Autowired
private ProviderService providerService;
@GetMapping("/")
private ResponseEntity<List<Provider>> listProviders(){
return ResponseEntity.ok(providerService.getAllProviders());
}
@GetMapping("/{column}")
private ResponseEntity<List<Provider>> listSortedProviders(@PathVariable String column){
return ResponseEntity.ok(providerService.getSortingList(column));
}
@PostMapping("/provider")
private Provider saveProvider(@RequestBody Provider provider){
return providerService.createProvider(provider);
}
}
我很感激在这方面的任何帮助，或者如果有更好的方法，我愿意尝试。谢谢！
如果用于排序的列总是相同的，则不能从控制器传输它们：
public List<Provider> getSortingList() {
return providerRepository.findAll(Sort.by(Sort.Order.desc("activo"), Sort.Order.asc("nombre")));
}
如果你需要api客户端自己设置排序顺序，你可以这样做：
//don't confuse, need this one
import org.springframework.data.domain.Sort;
//in controller
@GetMapping("/")
private List<Provider> listProviders(Sort sort){
return providerService.getAllProviders(sort);
}
//in service
public List<Provider> getAllProviders(Sort sort) {
if (sort.isUnsorted()) {
sort = Sort.by(Sort.Order.desc("activo"), Sort.Order.asc("nombre"));
}
return providerRepository.findAll(sort);
}
//then call it
curl http://localhost:8080/v1/providers?sort=activo,desc&sort=nombre,asc
在接口类中，请再写一个方法：
@Repository
public interface ProviderRepository extends JpaRepository<Provider, Integer>{
@Query("SELECT p.serie, p.name, p.status FROM Provider p ORDER BY p.status DESC,
p.name ASC")
public List<Object[]> sortByCondition();
}
然后你就可以称之为。我希望这个答案能帮助你