错误模糊查询:
<select id="selectAllClerkInfo" parameterType="ClerkInfo" resultType="ClerkInfo"
SELECT c1.*,c2.clerk_contract_term AS contract_term, c2.clerk_contract_desc AS contract_desc
FROM clerk c1
LEFT JOIN
contract c2
ON c1.id = c2.`clerk_id`
<if test=" clerkInfo.getName() != null and clerkInfo.getName()!='' ">
having c1.name LIKE CONCAT(CONCAT('%',#{ clerkInfo.name},'%'))
</if>
<if test=" clerkInfo.getPhone() != null and clerkInfo.getPhone()!='' ">
or c1.phone LIKE CONCAT(CONCAT('%',#{clerkInfo.phone},'%'))
</if>
</select>
提醒的错误信息:不认识'c1.name'
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'c1.name' in 'having clause' ### The error may exist in class path resource [mapper/ClerkMapper.xml] ### The error may involve com.chinasoft.mybatis.dao.ClerkMapper.selectAllClerkInfo-Inline ### The error occurred while setting parameters ### SQL: SELECT count(0) FROM clerk c1 LEFT JOIN contract c2 ON c1.id = c2.`clerk_id` HAVING c1.name LIKE CONCAT(CONCAT('%', ?, '%')) OR c1.phone LIKE CONCAT(CONCAT('%', ?, '%')) ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'c1.name' in 'having clause' ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'c1.name' in 'having clause'
很纳闷为什么不认识呢,尝试去掉c1仍然不认识,然后考虑到了having的头上,这时候才知道自己对having的用法不是很熟悉,having:
看到这里,我明明没有分组函数,我写个having干嘛呢,傻逼吧,然后考虑到之前用了左连接,我on之后得有个连接符和模糊查询语句分割开吧,(不好意思:中间我还试过不用连接符,直接在模糊语句里用and,还试过两个都是or,不好意思,查出来的东西你看的都恶心)是什么呢?:
另一位网友的回答:
on and 是指这前后两个表连接的条件 不止一个条件,两个表会按所有条件进行前后连接,这on和and左右出现的是列与列的关系。他是A和B全部数据按要求的连接。
on where 是指这前后两个表的连接条件只有on后面的一个,然后对连接好的结果,去执行where条件查询,where后面的列可以不是连接相关的列,where后面出现的是某一个列的条件,是对行的筛选条件,不是列与列的连接关系。where是对A或B筛选之后的数据再去按照ON的条件去连接
比如select * from employee A join Salary B on A.name=B.name and A.ID=B.ID
select * from employee A join Salary B on A.name=B.name where A.ID='111'
到了这里,不用多说,必须是where:红的是需要注意的,还需要注意,我查询语句本来是不需要参数的,但是我要做模糊查询,所以参数还是得加的,把搜索时候的输入值封装到name属性通过参数传递过来,用@Param注解好取到name和phone的值,
<select id="selectAllClerkInfo" parameterType="ClerkInfo" resultType="ClerkInfo">
SELECT c1.*,c2.clerk_contract_term AS contract_term, c2.clerk_contract_desc AS contract_desc
FROM clerk c1
LEFT JOIN
contract c2
ON c1.id = c2.`clerk_id`
where 1=1
<if test=" clerkInfo.getName() != null and clerkInfo.getName()!='' ">
and c1.name LIKE CONCAT(CONCAT('%',#{ clerkInfo.name},'%'))
</if>
<if test=" clerkInfo.getPhone() != null and clerkInfo.getPhone()!='' ">
or c1.phone LIKE CONCAT(CONCAT('%',#{clerkInfo.phone},'%'))
</if>
</select>
对了,contrller有两句别忘了:这就是传递输入的搜索字段绑定到name。
成功了: