In a connection query, there is no problem with the query in the MySQL query tool. It is pasted into the mapper.xml file in the project, but the runtime 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 going online to search, it was confirmed that it was the problem of where conditions, so they were eliminated one by one, and finally locked on several is_delete fields, which are of char type in the database. The database query tool supports direct use of 0 and 1 to query, and MyBatis needs to contact the database Keep consistent in the middle, and 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'
Problem-solving
This exception is basically the problem of where conditions, and type mismatch is only one of them. Other problems include condition null value, keyword conflict with framework and so on. As long as you are careful when writing code, think more about avoiding exceptions, be patient when problems occur, check them one by one, and be good at summarizing after problems are solved, you can accumulate experience and grow gradually on the way of writing code.
