How to solve the error report of parameter transmission of mybatis group by substr function

This article mainly introduces the relevant knowledge of "how to solve the error report of mybatis group by substr function". The editor shows you the operation process through the actual case, the operation method is simple and fast, and it is practical. I hope this article "how to solve the error report of mybatis group by substr function" can help you solve the problem.

Abnormal misreport of mybatis group by substr parameter transmission

# Cause: java.sql.SQLSyntaxErrorException: ORA-00979: not an GROUP BY expression

SELECT SUBSTR (region_code,1,# {queryMap.groupCodeLength,jdbcType = INTEGER}) AS "region_code", count (CASE WHEN TYPE = 1 THEN 0 END) AS "like", count (CASE WHEN TYPE = 2 THEN 0 END) AS "roast" FROM t_pub_sentiment WHERE 1 = 1 GROUP BY SUBSTR (region_code,1,# {queryMap.groupCodeLength,jdbcType = INTEGER})

After change:

SELECT SUBSTR (region_code, 1, ${queryMap.groupCodeLength}) AS "region_code", count (CASE WHEN TYPE = 1 THEN 0 END) AS "like", count (CASE WHEN TYPE = 2 THEN 0 END) AS "roast" FROM t_pub_sentiment WHERE 1 = 1 GROUP BY SUBSTR (region_code, 1, ${queryMap.groupCodeLength}) reason

# {} and ${} are treated differently in precompilation. # {} will the parameter part be preprocessed with a placeholder? Instead. And ${} is just a simple string substitution.

${} has the risk of sql injection and should be used with caution.

Using a group by grouping query to return as null

When I use mybatis for grouping queries, the database has data, but mybatis returns null, using mybatis version 3.4.1

Solution method

Add the property attribute to the result tag of resultMap

As follows:

Select id,dept_name,count (1) from tbl_dept where dept_id=# {id} group by id

The fact that I didn't add property the first time I used it caused mybatis to return null, which can be returned normally after adding it.

Dao layer code

Public List getDeptByIdStep (Integer id); this is the end of the introduction of "how to solve the parameter error of mybatis group by substr function". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.