UI sends month parameter as int

@GetMapping("/{Monthnumber}")
Public List<FunctionList> getByMonth(@pathvariable("MonthNumber") int month)

Using the above API

For example, if my month is Feb the parameter value would be 2 but in my oracle db the column contains timestamp data 22-FEB-22 11.00.00.00000000 AM

Tried below IN REPO

@QUERY("SELECT * FROM Table_name WHERE month(timestamp_date) = ?1)
List<Functionlist> getByMonth(int month number)

How to correctly query this

CodePudding user response：

To correctly query the database using the month parameter in your API, you can use the EXTRACT function in your SQL query. The EXTRACT function extracts a specific part of a date or timestamp value, such as the month.

Here's an example of how you can use the EXTRACT function in your SQL query:

SELECT * FROM Table_name WHERE EXTRACT(MONTH FROM timestamp_date) = ?1

This query will select all rows from the Table_name table where the month part of the timestamp_date column is equal to the value of the month parameter passed to the API.

You can also use the TO_CHAR function to convert the timestamp_date column to a string with a specific format before extracting the month part. This can be useful if the format of the timestamp in the database is different from the format of the month parameter passed to the API.

Here's an example of how you can use the TO_CHAR function in your SQL query:

SELECT * FROM Table_name WHERE EXTRACT(MONTH FROM TO_CHAR(timestamp_date, 'DD-MON-YY HH.MI.SS.FF AM')) = ?1

This query will select all rows from the Table_name table where the month part of the timestamp_date column, after being converted to the specified format using TO_CHAR, is equal to the value of the month parameter passed to the API.

You can then use this SQL query in your repository method like this:

@Query("SELECT * FROM Table_name WHERE EXTRACT(MONTH FROM timestamp_date) = ?1")
List<FunctionList> getByMonth(int monthNumber);

This repository method will execute the SQL query and return a list of FunctionList objects that match the specified month.

I hope this helps...

CodePudding user response：

To fetch data from a database using Spring Data JPA based on a timestamp column and a month parameter in a native query, you can use the following steps:

1. Define a repository interface that extends JpaRepository and specifies the entity type and ID type. For example:

public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
}

2. Add a method to the repository interface that defines a native query to fetch data based on the timestamp column and the month parameter. You can use the @Query annotation to specify the native query, and use a named parameter (prefixed with :) to pass in the month parameter. For example:

@Query(value = "SELECT * FROM my_entity WHERE MONTH(timestamp_column) = :month", nativeQuery = true)
List<MyEntity> findByTimestampColumnMonth(@Param("month") int month);

3. In your service or controller, inject an instance of the repository and call the method you defined to fetch the data. For example:

@Autowired
MyEntityRepository repository;

List<MyEntity> entities = repository.findByTimestampColumnMonth(3);

This will execute the native query and return a list of MyEntity objects where the month of the timestamp_column is 3.