0

I'm working on a Spring non-boot (6 version) application where I'm trying to retrieve training records for a specific trainee. I'm using JPA with Hibernate and I'm using java.util.Date class date handling. However, I'm encountering a SQLGrammarException when executing my JPQL query.

While i'm trying to send request http://localhost:8082/api/v1/trainee/yevhenii.kashtalian/trainings?dateFrom=2020-02-20 this error occurs: SQLGrammarException: JDBC exception executing SQL [...] [ERROR: could not determine data type of parameter $2]

Here’s the relevant part of my method:

@Override
  public List<Training> getTrainingsByTraineeUsername(
      String username, Date fromDate, Date toDate, String trainerName, TrainingType trainingType) {
    var trainings =
        entityManager
            .createQuery(
                """
                        SELECT t FROM Training t
                        JOIN t.trainee trainee
                        WHERE trainee.user.username = :username
                        AND (
                        :fromDate IS NULL OR t.date >= :fromDate
                        )
                        AND (
                        :toDate IS NULL OR t.date <= :toDate
                        )
                        AND (
                        :trainerName IS NULL OR t.trainer.user.firstName = :trainerName
                        )
                        AND (
                        :trainingType IS NULL OR t.type = :trainingType
                        )
                                                                         """,
                Training.class)
            .setParameter("username", username)
            .setParameter("fromDate", fromDate)
            .setParameter("toDate", toDate)
            .setParameter("trainerName", trainerName)
            .setParameter("trainingType", trainingType)
            .getResultList();
    return trainings;
  }

My controller method:

@GetMapping("/trainee/{username}/trainings")
public ResponseEntity<List<TraineeTrainingDto>> getTraineeTrainings(
      @PathVariable("username")
          @Parameter(description = "Username of the trainee", example = "john.doe", required = true)
          String username,
      @RequestParam(value = "dateFrom", required = false) @DateTimeFormat(pattern = "yyyy-MM-dd")
          Date dateFrom,
      @RequestParam(value = "dateTo", required = false) @DateTimeFormat(pattern = "yyyy-MM-dd")
          Date dateTo,
      @RequestParam(value = "trainerName", required = false)
          @Parameter(description = "Trainer name of the training", example = "john.doe")
          String trainerName,
      @RequestParam(value = "trainingType", required = false)
          @Parameter(description = "Training name", example = "john.doe")
          String trainingType) {
    var traineeTrainings =
        trainingService.getTrainingsByTraineeUsername(
            username, dateFrom, dateTo, trainerName, trainingType);

    return ResponseEntity.ok(traineeTrainings);
  }

and my service method:

@Transactional(readOnly = true)
  @Override
  public List<TraineeTrainingDto> getTrainingsByTraineeUsername(
      String username, Date fromDate, Date toDate, String trainerName, String trainingTypeName) {
    getTraineeByUsername(username);
    var trainingType = parseTrainingType(trainingTypeName);
    var trainings =
        trainingDao.getTrainingsByTraineeUsername(
            username, fromDate, fromDate, trainerName, trainingType);

    var traineeTrainingsDto =
        trainings.stream().map(trainingMapper::toTraineeTrainingsDto).toList();

    logger.info("Successfully fetched trainings for trainee: {}", username);

    return traineeTrainingsDto;
  }

  private TrainingType parseTrainingType(String trainingTypeName) {
    TrainingType trainingType = null;
    if (trainingTypeName != null && !trainingTypeName.isBlank()) {
      trainingType =
          trainingTypeDao
              .findByName(TrainingType.Type.valueOf(trainingTypeName.toUpperCase()))
              .orElseThrow(() -> new TrainingServiceException("Training type not found"));
    }
    return trainingType;
  }

I tried implementing a method to retrieve training sessions based on various filters, including dates, trainer name, and training type. I expected the method to return a list of training sessions without errors. However, I encountered an SQL exception indicating that the database could not determine the data type for one of the parameters, which prevented the query from executing successfully.

4
  • what is SELECT t FROM Training t JOIN t.trainee trainee? Commented Feb 25 at 11:26
  • This statement is used to retrieve Training entities and their associated Trainee entities in a db. Commented Feb 25 at 11:45
  • In query INNER or LEFT JOIN needs ON clause. SELECT t FROM Training t JOIN t.trainee trainee ON <some condition> WHERE ... Commented Feb 25 at 11:52
  • 1
    I would suggest using a Criteria based query to conditionally expand the WHERE clause. This will make it easier to add/remove conditions. What is the actual type of the date field in your entity? That being said you really shouldn't use java.util.Date (or even worse java.sql.Date) in your classes, rather use the new java.time classes like LocalDate etc. Finally add the full stacktrace to your question not just a snippet. Commented Feb 25 at 14:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.