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.
SELECT t FROM Training t JOIN t.trainee trainee?SELECT t FROM Training t JOIN t.trainee trainee ON <some condition> WHERE ...Criteriabased query to conditionally expand theWHEREclause. This will make it easier to add/remove conditions. What is the actual type of thedatefield in your entity? That being said you really shouldn't usejava.util.Date(or even worsejava.sql.Date) in your classes, rather use the newjava.timeclasses likeLocalDateetc. Finally add the full stacktrace to your question not just a snippet.