In a connection query, there is no problem with the query in the MySQL query tool. When it is stuck in the Mapper.xml
file in the project, it reports MybatisPlusException: Error: Method queryTotal execution error of sql
:
SELECT
ANY_VALUE(e.id) AS "id",
ANY_VALUE(e.project_no) AS "projectNo",
ANY_VALUE(sampleIn.sample_id) AS "fieldNumber",
ANY_VALUE(e.subject_no) AS "laboratoryNumber",
ANY_VALUE(e.type) AS "type",
ANY_VALUE(d.sampling_location) AS "location",
ANY_VALUE(sampleIn.in_quantity) AS "inquatity",
ANY_VALUE(sampleIn.create_date) AS "createDate",
ANY_VALUE(sys_user.name) AS "createUser",
ANY_VALUE(s. project_no) AS "projectNoName",
ANY_VALUE(s.subject_name) AS "subjectName",
ANY_VALUE(sd.name) AS "saveCondition",
ANY_VALUE(sa.location) AS "location",
ANY_VALUE(DATE_FORMAT(sa.shelf_life,'%Y-%m-%d'))AS"shelfLife"
FROM labcode e
LEFT JOIN labprojects s ON e.project_no = s.id
LEFT JOIN labprojectplan n ON s.id = n.project_no
LEFT JOIN labprojectplan_field d ON n.id = d. lab_project_plan_id AND e.serial_number = d.serial_number AND e.letter_code =d.letter_code
LEFT JOIN labsubjectsample sa ON e.id =sa.laboratory_number
LEFT JOIN laboratorysamplein sampleIn ON sampleIn.code_id = e.id
LEFT JOIN sys_user ON sampleIn.create_user=sys_user.user_id
LEFT JOIN sys_dict sd ON sd.code = sa.save_condition
WHERE 1 = 1
AND s.is_delete = 0
AND e.is_delete=0
AND e.subject_no LIKE '%B'
AND e.type=3
GROUP BY e.id
ORDER BY ANY_VALUE(sampleIn.create_date) DESC
After searching online, it was confirmed that it was the problem of the where
condition, so they were eliminated one by one, and finally locked on several is_delete
fields, which are of type char
in the database. The database query tool supports direct use of 0
, 1
to query, and MyBatis
needs to be consistent with the database. Finally, change the where
part to
WHERE 1 = 1
AND s.is_delete = '0'
AND e.is_delete='0'
AND e.subject_no LIKE '%B'
AND e.type='3'
This exception is basically a problem of where conditions, and the type mismatch is just one of them, and there are other problems such as conditional null values, keywords and frame conflicts. As long as you write code carefully and consider avoiding exceptions, be patient when problems occur, investigate one by one, and be good at summing up after problems are solved, you can accumulate experience and grow gradually on the way of writing code.