[FIXED] How to properly find a row in SQL based on Date field from spring boot

Issue

I have created a entity which has two fields date and rate (exchange rating currency). I want to create a method in my service class so that i can find rate exchanges, based on date time, which will be in the format of yyyy/dd/mm no other additional time fields.

Here is what i done so far:

i created a model:

@Entity
@Getter
@Setter
@Table(name = "ExchangeRate")
public class EmployeeExchangeRate extends BaseEntity {


@Basic
@Temporal(TemporalType.DATE)
@Column(name = "date", nullable = false)
private Date date;

@Column(name = "rate", nullable = false)
private double rate;

}

then i created a repository:

public interface EmployeeExchangeRateRepository extends JpaRepository<EmployeeExchangeRate, Long> {
EmployeeExchangeRate findByDate(Date date);
}

in my service i have written the following method:

private void findRate(int day, int month, int year){

    String s = day + "-" + month + "-" + year;

    Optional<EmployeeExchangeRate> foundExchangeRate =
            exchangeRateRepository.findByDate(new SimpleDateFormat("yyyy/dd/MM").parse(s));

    System.out.println(foundExchangeRate.get());
}

Note: that this code does not work, i have tried several variants but i could not parse the string correctly to a date time. In the method i want to pass day, month, and year and based on that arguments i want to find a exchange rate. The question is What is the most correct way for finding a row based on date? How can i convert parameters: day, month, year to date, so that i can find a row based on this date time? Note: that date in mySql is represented as yyyy/dd/mm

Solution

You are doing this:

String s = day + "-" + month + "-" + year;

but when parsing, you are expecting yyyy/mm/dd. Set the data correctly to the SimpleDateFormat.

public static void main(String[] args) throws ParseException {
    String s = "10-10-1989";
    Date date = new SimpleDateFormat("yyyy/dd/MM").parse(s);
    System.out.println(date);
}

The above example throws Unparseable date: "10-10-1989"

Besides that, in the comments I saw you are not getting an unique result, which makes sense since the query won't have any limit. There could be multiple rows for one date.

Modify the name of the method to return one value or change the return value to a List<EmployeeExchangeRate>

Answered By - Leonardo Emmanuel de Azevedo
Answer Checked By - Katrina (JavaFixing Volunteer)