1、背景
由于做的一个小项目里需要联动查询，一想16种情况，因为我是SSM框架写的，这样我就要写16个接口，16个实现，16条sql语句，想想就大头。既然数据库本身接收的就是String类型，那我就直接在implement中写喽，拼接sql语句。
image.png
2、编码内容
1）、mapper.xml中，只需要写一条数据库查询语句即可：
<select id="MapperCommonSelect" resultMap="UserBaseResultMap" parameterType="java.lang.String">
select
u.*,p.*
from cetc50_user_info u,cetc50_project_info p
where ${_parameter} u.ProjectId = p.ProjectId
order by UserId
</select>
${_parameter}就是我要拼接的内容（注意下划线不能少不能少）。
2）、我们再看*impl.java中的文件
public List<User> selectByProjectIdAndInuseAndApproval(String userName,Integer projectId,Integer inuse,Integer approval){
List<User> userList = new ArrayList<User>();
String strsql = "";
if(userName.equals("")){
if(projectId > 0){
if(inuse < 0){
if(approval < 0){
strsql = "u.ProjectId = "+projectId+" and";
}else{
strsql = "u.ProjectId = "+projectId+" and"+" approval = "+approval+" and";
}
}else{
if(approval < 0){
strsql = "u.ProjectId = "+projectId+" and"+" inuse = "+inuse+" and";
}else{
strsql = "u.ProjectId = "+projectId+" and"+" approval = "+approval+" and"+" inuse = "+inuse+" and";
}
}
}else{
if(inuse < 0){
if(approval < 0){
strsql = "";
}else{
strsql = " approval = "+approval+" and";
}
}else{
if(approval < 0){
strsql = " inuse = "+inuse+" and";
}else{
strsql = " approval = "+approval+" and"+" inuse = "+inuse+" and";
}
}
}
}else{
if(projectId > 0){
if(inuse < 0){
if(approval < 0){
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" u.ProjectId = "+projectId+" and";
}else{
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" u.ProjectId = "+projectId+" and"+" approval = "+approval+" and";
}
}else{
if(approval < 0){
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" u.ProjectId = "+projectId+" and"+" inuse = "+inuse+" and";
}else{
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" u.ProjectId = "+projectId+" and"+" approval = "+approval+" and"+" inuse = "+inuse+" and";
}
}
}else{
if(inuse < 0){
if(approval < 0){
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and";
}else{
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" approval = "+approval+" and";
}
}else{
if(approval < 0){
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" inuse = "+inuse+" and";
}else{
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" approval = "+approval+" and"+" inuse = "+inuse+" and";
}
}
}
}
userList = this.userDao.MapperCommonSelect(strsql);
return userList;
}
好吧十六种情况，写出来却是有点长。
3、重点问题
说下我遇到的问题吧，这个才是学习的重点。
问题1、但凡写过sql语句的人估计都曾经碰到过类似于Unknown column ‘xxx’ in ‘where clause’的问题。单从字面理解，我们很容易得出列名不存在的结论，但是，很多时候起始并不是由于列名出错造成的。
而是由于拼凑sql语句时对字符类型数据没有用引号引起来造成的。
例如：
strsql = "UserName like CONCAT('%','"+userName+"','%')"+" and"+" inuse = "+inuse+" and";
由于我的userName前面定义的String类型，我一开始没注意，没有增加引号，结果报Unknown column ‘xxx’ in ‘where clause’。
问题2、 报There is no getter for property named 'strsql' in 'class java.lang.String']错误。
where ${strsql} u.ProjectId = p.ProjectId
我一开始是这样写的，因为strsql是我mapper.java里的传值，请教度娘后，将strsql换成了_parameter，
where ${_parameter} u.ProjectId = p.ProjectId
最后就正确啦。数据也就惊奇的出现在我的前端。开心，哈哈哈
