[Solved] SQL Error: Method queryTotal execution error of sql

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.

Read More: