Is it possible to sort two columns in the same API as two different parameters?-CodePudding

I'm trying to do this SQL sentence with SpringBoot and JPArepository:

SELECT serie, nombre, activo FROM proveedor ORDER BY activo desc, nombre asc;

This sentence works perfectly to sort the columns in PGADMIN, however, in springBoot I'm only able to sort one of those columns passing it as a param in my endpoint API, my question is how can I pass this SQL sentence to springBoot and get to sort those two columns in the same request?

Here is my code:
Entity:

 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: I don't need to use pagination in this functionality so I'm avoiding to go by that option

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>{

 }

SERVICE:

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));
}

}

CONTROLLER:

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);
}

}

I would appreciate any assistance on this or maybe if is there a better way to do it, I'm open to try it. Thank you!

CodePudding user response：

In the interface class, please write one more method:

 @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();
 }

and then you can call it . I hope this answer helps you

CodePudding user response：

If the columns for sorting are always the same, you can not transfer them from the controller:

public List<Provider> getSortingList() {
   return providerRepository.findAll(Sort.by(Sort.Order.desc("activo"), Sort.Order.asc("nombre")));
}

If you need the api client to set the sort order itself, you can do this:

//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