SQL exception error: Method queryTotal execution error of sql

descriptioon¶

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

reason and solution¶

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'

summary¶

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.