GET Available Vehicles: API & Repository Implementation

by Alex Johnson 56 views

In this guide, we'll walk through the process of implementing a GET /relatorios/veiculos-disponiveis API endpoint. This endpoint will be responsible for retrieving a list of available vehicles from our system. We'll cover the necessary steps, including creating a method in the VeiculoRepository and an endpoint in the RelatorioController. We will also delve into the specifics of the SQL query required to accurately identify available vehicles.

Understanding the Requirements

Before diving into the implementation, it's crucial to fully understand the requirements for identifying available vehicles. According to the specifications, a vehicle is considered available if it meets the following criteria:

  1. veiculo.status = 'disponivel' (Vehicle status is 'available')
  2. veiculo.ativo = true (Vehicle is active)
  3. NOT EXISTS in viagens with data_retorno IS NULL (Vehicle is not currently on a trip without a return date)

These conditions ensure that we only retrieve vehicles that are explicitly marked as available, are active in the system, and are not currently assigned to an ongoing trip. This meticulous approach guarantees accurate reporting and prevents the inclusion of vehicles that are, in reality, unavailable.

Step 1: Creating the Method in VeiculoRepository

The first step is to create a method in the VeiculoRepository that will execute the SQL query to fetch the available vehicles. This repository method will serve as the data access layer, abstracting the database interactions from the controller. Here’s how we can approach this:

First, let's consider the SQL query. It needs to select vehicles that meet our criteria. A well-structured query will not only ensure accuracy but also optimize performance. The basic structure of the query should look something like this:

SELECT * FROM veiculo
WHERE status = 'disponivel'
AND ativo = true
AND NOT EXISTS (
 SELECT 1 FROM viagens
 WHERE viagens.veiculo_id = veiculo.id
 AND data_retorno IS NULL
);

This query selects all columns from the veiculo table where the status is 'disponivel', the ativo flag is true, and there are no corresponding records in the viagens table with a NULL return date. The NOT EXISTS clause is particularly important as it efficiently checks for the absence of ongoing trips.

Now, let’s translate this into a repository method. Assuming we are using a framework like Spring Data JPA, the method might look like this:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface VeiculoRepository extends JpaRepository<Veiculo, Long> {

 @Query(value = "SELECT v FROM Veiculo v WHERE v.status = 'disponivel' AND v.ativo = true AND NOT EXISTS (SELECT 1 FROM Viagem vg WHERE vg.veiculo.id = v.id AND vg.dataRetorno IS NULL)", nativeQuery = false)
 List<Veiculo> findAvailableVehicles();
}

In this example, we define an interface VeiculoRepository that extends JpaRepository, providing us with basic CRUD operations. The @Query annotation allows us to define a custom JPQL (Java Persistence Query Language) query. This query mirrors the SQL query we discussed earlier but operates on the entity level.

Key components of this method include:

  • @Repository: Marks the interface as a repository, making it a Spring-managed component.
  • @Query: Specifies the query to be executed. We use a JPQL query here, which is database-agnostic and operates on entities rather than tables.
  • findAvailableVehicles(): The method name clearly indicates its purpose.
  • nativeQuery = false: Indicates that the query is a JPQL query, not a native SQL query.

This method encapsulates the logic for retrieving available vehicles, making it reusable and testable.

Step 2: Creating the Endpoint in RelatorioController

With the repository method in place, the next step is to create the API endpoint in the RelatorioController. This controller endpoint will handle incoming requests, call the repository method, and return the results. Here’s how we can implement this:

First, we need to define the controller class and the endpoint method. Let's assume we are using a framework like Spring MVC. The controller class might look like this:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
@RequestMapping("/relatorios")
public class RelatorioController {

 @Autowired
 private VeiculoRepository veiculoRepository;

 @GetMapping("/veiculos-disponiveis")
 public List<Veiculo> getAvailableVehicles() {
 return veiculoRepository.findAvailableVehicles();
 }
}

In this example, we define a RelatorioController class that handles requests under the /relatorios path. The @RestController annotation combines @Controller and @ResponseBody, indicating that this class handles incoming web requests and returns data directly in the response body.

Key components of this controller include:

  • @RestController: Marks the class as a REST controller.
  • @RequestMapping("/relatorios"): Maps requests with the /relatorios path to this controller.
  • @Autowired: Injects the VeiculoRepository dependency.
  • @GetMapping("/veiculos-disponiveis"): Maps GET requests with the /veiculos-disponiveis path to the getAvailableVehicles() method.
  • getAvailableVehicles(): The method that handles the request and returns the list of available vehicles.

Inside the getAvailableVehicles() method, we simply call the findAvailableVehicles() method from the VeiculoRepository. The repository handles the database interaction, and the controller method returns the result. This separation of concerns makes the code more maintainable and testable.

Step 3: Testing the Endpoint

After implementing the repository method and the controller endpoint, it’s essential to test the functionality thoroughly. Testing ensures that our implementation meets the requirements and that the API behaves as expected.

We can use various testing strategies, including unit tests, integration tests, and end-to-end tests. For this scenario, integration tests are particularly useful as they verify the interaction between the controller, the repository, and the database.

Here’s an example of an integration test using Spring Test and JUnit:

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;
import org.springframework.test.web.servlet.result.MockMvcResultMatchers;

@SpringBootTest
@AutoConfigureMockMvc
public class RelatorioControllerIntegrationTest {

 @Autowired
 private MockMvc mockMvc;

 @Test
 public void getAvailableVehicles_ReturnsOk() throws Exception {
 mockMvc.perform(MockMvcRequestBuilders.get("/relatorios/veiculos-disponiveis"))
 .andExpect(MockMvcResultMatchers.status().isOk());
 }

 @Test
 public void getAvailableVehicles_ReturnsListOfVehicles() throws Exception {
 mockMvc.perform(MockMvcRequestBuilders.get("/relatorios/veiculos-disponiveis"))
 .andExpect(MockMvcResultMatchers.status().isOk())
 .andExpect(MockMvcResultMatchers.jsonPath("{{content}}quot;).isArray());
 }
}

In this example, we use Spring Boot’s testing support to create an integration test. Key components of this test include:

  • @SpringBootTest: Indicates that this is a Spring Boot test, loading the application context.
  • @AutoConfigureMockMvc: Configures a MockMvc instance for testing web endpoints.
  • @Autowired: Injects the MockMvc instance.
  • mockMvc.perform(): Performs a mock HTTP request to the specified endpoint.
  • MockMvcResultMatchers.status().isOk(): Asserts that the response status is 200 OK.
  • MockMvcResultMatchers.jsonPath("{{content}}quot;).isArray(): Asserts that the response body is a JSON array.

These tests verify that the endpoint returns a 200 OK status and a JSON array, ensuring that the API is functioning correctly. Additional tests can be added to verify the content of the response, ensuring that the correct vehicles are being returned.

Step 4: Optimizing the Query (If Necessary)

Once the basic functionality is in place, it’s crucial to consider performance. Depending on the size of the database and the complexity of the queries, optimization might be necessary.

In our case, the SQL query includes a NOT EXISTS clause, which can be performance-intensive. However, with proper indexing, the query should perform efficiently. Ensure that the veiculo table has indexes on the status, ativo, and id columns, and that the viagens table has an index on the veiculo_id and data_retorno columns.

If performance issues persist, alternative strategies can be explored, such as:

  1. Using a Subquery with LEFT JOIN: This approach can sometimes be more efficient than NOT EXISTS, depending on the database engine and data distribution.
  2. Denormalization: Adding a column to the veiculo table that indicates whether the vehicle is currently on a trip can simplify the query and improve performance. However, this approach introduces data duplication and requires careful maintenance to ensure consistency.
  3. Caching: Caching the results of the query can reduce database load, especially for frequently accessed data. However, caching introduces complexity and requires a strategy for invalidating the cache when data changes.

The choice of optimization strategy depends on the specific requirements and constraints of the application. It’s essential to measure performance before and after applying optimizations to ensure that the changes are effective.

Best Practices and Considerations

Implementing this API endpoint involves several best practices and considerations to ensure robustness, maintainability, and performance. Here are some key points to keep in mind:

  1. Clear Naming Conventions: Use descriptive names for methods, variables, and endpoints. This makes the code easier to understand and maintain. For example, findAvailableVehicles() clearly indicates the purpose of the method.
  2. Separation of Concerns: Separate the data access logic from the controller logic. The repository should handle database interactions, while the controller should handle request processing and response generation. This makes the code more modular and testable.
  3. Error Handling: Implement proper error handling to gracefully handle exceptions and unexpected situations. This can include logging errors, returning appropriate HTTP status codes, and providing informative error messages to the client.
  4. Input Validation: Validate input parameters to prevent security vulnerabilities and ensure data integrity. This is particularly important for APIs that accept user input.
  5. Security: Secure the API endpoint using appropriate authentication and authorization mechanisms. This can include using API keys, JWT tokens, or other security protocols.
  6. Documentation: Document the API endpoint using a standard format like OpenAPI (Swagger). This makes it easier for developers to understand and use the API.
  7. Monitoring: Monitor the performance and availability of the API endpoint. This can include tracking response times, error rates, and resource utilization.

By following these best practices, you can ensure that your API endpoint is robust, maintainable, and performs efficiently.

Conclusion

Implementing the GET /relatorios/veiculos-disponiveis API endpoint involves creating a method in the VeiculoRepository and an endpoint in the RelatorioController. The SQL query must accurately identify available vehicles based on their status, activity, and trip assignments. Thorough testing and potential query optimization are crucial for ensuring the functionality and performance of the API.

By following the steps and best practices outlined in this guide, you can successfully implement this API endpoint and provide a reliable way to retrieve available vehicles from your system.

For more information on REST API design and best practices, consider exploring resources like the OpenAPI Specification.