I discovered something strange when running jpql queries with an entityManager object.
I fetch the id numbers of any objects whose boolean field is true for either the columnname plantClimbs frostresistant or spicy
The method i've made makes a query string that looks like this when plantClimbs is true
select u.id from SeedRecord u WHERE u.climbingPlant = 'true'
When i run this query i'm seeing some unexpected results. Instead of retrieving all the SeedRecord id's whose climbingPlant field is true, it fetches all the id's of the seedrecords whose climbingPlant field is false and whose frostResistant or Spicy field happens to be true.
When i remove the ' characters around true it starts to fetch the right id's again. My question is: Why is this happening? Why does putting ' around true seem to negate everything so that the seedrecords whose climbingPLant field is false actually gets fetched instead?
Heres my full method:
private Set<Long> findAllSeedRecordsByKeywordsOrBooleans(Set<String> checkKeywords, boolean plantClimbs, boolean teaPlant, boolean spicy){
if (checkKeywords.isEmpty()
&& !plantClimbs
&& !spicy
&& !teaPlant) return Collections.EMPTY_SET;
Set<String> availablePlantTypes = new HashSet<>();
Set<String> availableProduceTypes = new HashSet<>();
log.info("fetching entitymanager");
EntityManager entityManager = seedRecordDao.getEntityManager();
Map<String,Set<String>> containsProduceNameKeyword = new HashMap<>();
Map<String,Set<PlantType>> containsPlantTypeKeyword = new HashMap<>();
Map<String,Set<ProduceType>> containsProduceTypeKeyword = new HashMap<>();
Set<String> searchProduceNames = new HashSet<>();
Set<PlantType> searchPlantTypes = new HashSet<>();
Set<ProduceType> searchProduceTypes = new HashSet<>();
boolean hasPriorCondition = false;
boolean produceNamesConditionApplied = false;
boolean produceTypesConditionApplied = false;
boolean plantTypesConditionApplied = false;
StringBuilder filterQuery = new StringBuilder("select u.id from SeedRecord u WHERE ");
if(!checkKeywords.isEmpty()) {
log.info("getting the producenames");
Set<String> availableProduceNames = seedRecordDao.getProduceNames()
.stream().map(ProduceName::getProduceName).collect(Collectors.toSet());
for (PlantType plantType : PlantType.values())
availablePlantTypes.add(plantType.toString());
for (ProduceType produceType : ProduceType.values())
availableProduceTypes.add(produceType.toString());
for (String keyword : checkKeywords) {
if (availableProduceNames.contains(keyword)) {
searchProduceNames.add(keyword);
if (!produceNamesConditionApplied) {
if (!hasPriorCondition) {
filterQuery.append("u.produceName IN :produceNames ");
hasPriorCondition = true;
} else
filterQuery.append("OR u.produceName IN :produceNames ");
produceNamesConditionApplied = true;
}
} else if (availablePlantTypes.contains(keyword)) {
searchPlantTypes.add(PlantType.valueOf(keyword));
if (!plantTypesConditionApplied) {
if (!hasPriorCondition) {
filterQuery.append("u.plantType IN :plantTypes ");
hasPriorCondition = true;
} else
filterQuery.append("OR u.plantType IN :plantTypes ");
plantTypesConditionApplied = true;
}
} else if (availableProduceTypes.contains(keyword)) {
searchProduceTypes.add(ProduceType.valueOf(keyword));
if (!produceTypesConditionApplied) {
if (!hasPriorCondition) {
filterQuery.append("u.produceType IN :produceTypes ");
hasPriorCondition = true;
} else
filterQuery.append("OR u.produceType IN :produceTypes ");
produceTypesConditionApplied = true;
}
}
}
if (!searchProduceNames.isEmpty())
containsProduceNameKeyword.put("produceNames", searchProduceNames);
if (!searchProduceTypes.isEmpty())
containsProduceTypeKeyword.put("produceTypes", searchProduceTypes);
if (!searchPlantTypes.isEmpty())
containsPlantTypeKeyword.put("plantTypes", searchPlantTypes);
}
if(plantClimbs)
if (!hasPriorCondition) {
filterQuery.append("u.climbingPlant = 'true' ");
hasPriorCondition = true;
}
else
filterQuery.append("OR u.climbingPlant = 'true' ");
if(teaPlant)
if (!hasPriorCondition) {
filterQuery.append("u.teaPlant = 'true' ");
hasPriorCondition = true;
}
else
filterQuery.append("OR u.teaPlant = 'true' ");
if(spicy)
if (!hasPriorCondition) {
filterQuery.append("u.spicy = 'true' ");
}
else
filterQuery.append("OR u.spicy = 'true' ");
try {
log.info("preparing query");
log.info(filterQuery.toString());
TypedQuery<Long> query = entityManager.createQuery(filterQuery.toString(), Long.class);
if (!containsProduceNameKeyword.isEmpty())
containsProduceNameKeyword.forEach((parameter, input) -> query.setParameter(parameter, input));
if (!containsPlantTypeKeyword.isEmpty())
containsPlantTypeKeyword.forEach((parameter, input) -> query.setParameter(parameter, input));
if (!containsProduceTypeKeyword.isEmpty())
containsProduceTypeKeyword.forEach((parameter, input) -> query.setParameter(parameter, input));
log.info("sending to database NOW");
return new HashSet<>(query.getResultList());
}
catch(Exception e){
throw e;
}
}
It's because your climbingPlant column is boolean, so MySQL has to cast 'true' to boolean to compare with it, which is (and is for any string) false. So when you have true in quotes, it matches climbingPlant when climbingPlant is false. The values of the other columns are irrelevant. For a demo, try this:
select 'true' = true, 'true' = false, 'anything' = false
Output:
'true' = true 'true' = false 'anything' = false
0 1 1