1：%放在占位符中
parameters.add("%"+familyMemberQueryBean.getFullName()+"%");
sql+=" and t.full_name like ?";
2：使用concat函数
parameters.add(familyMemberQueryBean.getFullName());
sql+=" and t.full_name like concat('%',?,'%')";
3：使用转义字符\，百分号直接写在sql语句中
parameters.add(familyMemberQueryBean.getFullName());
sql+=" and t.full_name like \"%\"?\"%\"";
直接查询的SQL语句如下：SELECT id,full_name,email,phone,remark FROM family_member t WHERE 1 = 1
AND t.full_name LIKE "%"'李'"%" AND t.email LIKE "%"'qq'"%"
4：直接拼接SQL
sql+=" and t.full_name like '%"+familyMemberQueryBean.getFullName()+"%'";
例如：
package org.pine.dao.impl; import org.pine.dao.FamilyMemberDao;
import org.pine.dto.FamilyMember;
import org.pine.util.DbcpUtils;
import org.pine.vo.FamilyMemberQueryBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List; /**
* FamilyMemberDao实现类
*
* @Auther: zts
* @Date: 2019/5/28 下午 03:21
* @version: 1.0
*/
public class FamilyMemberDaoImpl implements FamilyMemberDao {
/*
public List<FamilyMember> queryFamilyMembers(FamilyMemberQueryBean familyMemberQueryBean) {
Connection connection =null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection = DbcpUtils.getConnection();
String sql ="SELECT id,full_name,email,phone,remark FROM family_member t WHERE 1 = 1 ";
//拼接SQL
List<Object> parameters = new ArrayList<Object>();
if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){
parameters.add("%"+familyMemberQueryBean.getFullName()+"%");
sql+=" and t.full_name like ?";
}
if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){
parameters.add("%"+familyMemberQueryBean.getEmail()+"%");
sql+=" and t.email like ?";
}
if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){
parameters.add("%"+familyMemberQueryBean.getPhone()+"%");
sql+=" and t.phone like ?";
}
if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){
parameters.add("%"+familyMemberQueryBean.getRemark()+"%");
sql+=" and t.remark like ?";
}
sql+="limit ?,?";
parameters.add(familyMemberQueryBean.getStart());
parameters.add(familyMemberQueryBean.getLimit());
DbcpUtils.printlnSQL(sql);
DbcpUtils.printlnParameters(parameters);
preparedStatement = connection.prepareStatement(sql);
//设置参数
for(int i=0;i<parameters.size();i++){
preparedStatement.setObject(i+1,parameters.get(i));
}
resultSet = preparedStatement.executeQuery();
List<FamilyMember> familyMembers = new ArrayList<FamilyMember>();
while (resultSet.next()){
FamilyMember familyMember = new FamilyMember();
int id = resultSet.getInt("id");
String fullName = resultSet.getString("full_name");
String email = resultSet.getString("email");
String phone = resultSet.getString("phone");
String remark = resultSet.getString("remark");
familyMember.setId(id);
familyMember.setFullName(fullName);
familyMember.setEmail(email);
familyMember.setPhone(phone);
familyMember.setRemark(remark);
familyMembers.add(familyMember);
}
return familyMembers;
}catch (Exception e){
throw new RuntimeException(e);
}finally {
DbcpUtils.closeConnection(connection,preparedStatement,resultSet);
}
}*/ /*
@Override
public int queryFamilyMemberCount(FamilyMemberQueryBean familyMemberQueryBean) {
Connection connection =null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection = DbcpUtils.getConnection();
String sql ="SELECT count(*) FROM family_member t WHERE 1 = 1 ";
//拼接SQL
List<String> parameters = new ArrayList<String>();
if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){
parameters.add(familyMemberQueryBean.getFullName());
sql+=" and t.full_name like concat('%',?,'%')";
}
if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){
parameters.add(familyMemberQueryBean.getEmail());
sql+=" and t.email like concat('%',?,'%')";
}
if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){
parameters.add(familyMemberQueryBean.getPhone());
sql+=" and t.phone like concat('%',?,'%')";
}
if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){
parameters.add(familyMemberQueryBean.getRemark());
sql+=" and t.remark like concat('%',?,'%')";
}
DbcpUtils.printlnSQL(sql);
DbcpUtils.printlnParameters(parameters);
preparedStatement = connection.prepareStatement(sql);
//设置参数
for(int i=0;i<parameters.size();i++){
preparedStatement.setString(i+1,parameters.get(i));
}
resultSet = preparedStatement.executeQuery();
resultSet.next();
int count = resultSet.getInt(1);
return count;
}catch (Exception e){
throw new RuntimeException(e);
}finally {
DbcpUtils.closeConnection(connection,preparedStatement,resultSet);
}
}*/ public List<FamilyMember> queryFamilyMembers(FamilyMemberQueryBean familyMemberQueryBean) {
Connection connection =null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection = DbcpUtils.getConnection();
String sql ="SELECT id,full_name,email,phone,remark FROM family_member t WHERE 1 = 1 ";
//拼接SQL
List<Object> parameters = new ArrayList<Object>();
if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){
parameters.add(familyMemberQueryBean.getFullName());
sql+=" and t.full_name like \"%\"?\"%\"";
}
if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){
parameters.add(familyMemberQueryBean.getEmail());
sql+=" and t.email like \"%\"?\"%\"";
}
if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){
parameters.add(familyMemberQueryBean.getPhone());
sql+=" and t.phone like \"%\"?\"%\"";
}
if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){
parameters.add(familyMemberQueryBean.getRemark());
sql+=" and t.remark like \"%\"?\"%\"";
}
sql+=" limit ?,?";
parameters.add(familyMemberQueryBean.getStart());
parameters.add(familyMemberQueryBean.getLimit());
DbcpUtils.printlnSQL(sql);
DbcpUtils.printlnParameters(parameters);
preparedStatement = connection.prepareStatement(sql);
//设置参数
for(int i=0;i<parameters.size();i++){
preparedStatement.setObject(i+1,parameters.get(i));
}
resultSet = preparedStatement.executeQuery();
List<FamilyMember> familyMembers = new ArrayList<FamilyMember>();
while (resultSet.next()){
FamilyMember familyMember = new FamilyMember();
int id = resultSet.getInt("id");
String fullName = resultSet.getString("full_name");
String email = resultSet.getString("email");
String phone = resultSet.getString("phone");
String remark = resultSet.getString("remark");
familyMember.setId(id);
familyMember.setFullName(fullName);
familyMember.setEmail(email);
familyMember.setPhone(phone);
familyMember.setRemark(remark);
familyMembers.add(familyMember);
}
return familyMembers;
}catch (Exception e){
throw new RuntimeException(e);
}finally {
DbcpUtils.closeConnection(connection,preparedStatement,resultSet);
}
} @Override
public int queryFamilyMemberCount(FamilyMemberQueryBean familyMemberQueryBean) {
Connection connection =null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection = DbcpUtils.getConnection();
String sql ="SELECT count(*) FROM family_member t WHERE 1 = 1 ";
//拼接SQL
List<String> parameters = new ArrayList<String>();
if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){
sql+=" and t.full_name like '%"+familyMemberQueryBean.getFullName()+"%'";
}
if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){
sql+=" and t.email like '%"+familyMemberQueryBean.getEmail()+"%'";
}
if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){
sql+=" and t.phone like '%"+familyMemberQueryBean.getPhone()+"%'";
}
if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){
sql+=" and t.remark like '"+familyMemberQueryBean.getRemark()+"'";
}
DbcpUtils.printlnSQL(sql);
DbcpUtils.printlnParameters(parameters);
preparedStatement = connection.prepareStatement(sql);
//设置参数
for(int i=0;i<parameters.size();i++){
preparedStatement.setString(i+1,parameters.get(i));
}
resultSet = preparedStatement.executeQuery();
resultSet.next();
int count = resultSet.getInt(1);
return count;
}catch (Exception e){
throw new RuntimeException(e);
}finally {
DbcpUtils.closeConnection(connection,preparedStatement,resultSet);
}
}
}