JDBC模糊查询的4种方式
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()+"%'";
例如：
1 package org.pine.dao.impl; 2 3 import org.pine.dao.FamilyMemberDao; 4 import org.pine.dto.FamilyMember; 5 import org.pine.util.DbcpUtils; 6 import org.pine.vo.FamilyMemberQueryBean; 7 import java.sql.Connection; 8 import java.sql.PreparedStatement; 9 import java.sql.ResultSet; 10 import java.util.ArrayList; 11 import java.util.List; 12 13 /** 14 * FamilyMemberDao实现类 15 * 16 * @Auther: zts 17 * @Date: 2019/5/28 下午 03:21 18 * @version: 1.0 19 */ 20 public class FamilyMemberDaoImpl implements FamilyMemberDao { 21 /* 22 public List<FamilyMember> queryFamilyMembers(FamilyMemberQueryBean familyMemberQueryBean) { 23 Connection connection =null; 24 PreparedStatement preparedStatement=null; 25 ResultSet resultSet=null; 26 try { 27 connection = DbcpUtils.getConnection(); 28 String sql ="SELECT id,full_name,email,phone,remark FROM family_member t WHERE 1 = 1 "; 29 //拼接SQL 30 List<Object> parameters = new ArrayList<Object>(); 31 if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){ 32 parameters.add("%"+familyMemberQueryBean.getFullName()+"%"); 33 sql+=" and t.full_name like ?"; 34 } 35 if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){ 36 parameters.add("%"+familyMemberQueryBean.getEmail()+"%"); 37 sql+=" and t.email like ?"; 38 } 39 if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){ 40 parameters.add("%"+familyMemberQueryBean.getPhone()+"%"); 41 sql+=" and t.phone like ?"; 42 } 43 if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){ 44 parameters.add("%"+familyMemberQueryBean.getRemark()+"%"); 45 sql+=" and t.remark like ?"; 46 } 47 sql+="limit ?,?"; 48 parameters.add(familyMemberQueryBean.getStart()); 49 parameters.add(familyMemberQueryBean.getLimit()); 50 DbcpUtils.printlnSQL(sql); 51 DbcpUtils.printlnParameters(parameters); 52 preparedStatement = connection.prepareStatement(sql); 53 //设置参数 54 for(int i=0;i<parameters.size();i++){ 55 preparedStatement.setObject(i+1,parameters.get(i)); 56 } 57 resultSet = preparedStatement.executeQuery(); 58 List<FamilyMember> familyMembers = new ArrayList<FamilyMember>(); 59 while (resultSet.next()){ 60 FamilyMember familyMember = new FamilyMember(); 61 int id = resultSet.getInt("id"); 62 String fullName = resultSet.getString("full_name"); 63 String email = resultSet.getString("email"); 64 String phone = resultSet.getString("phone"); 65 String remark = resultSet.getString("remark"); 66 familyMember.setId(id); 67 familyMember.setFullName(fullName); 68 familyMember.setEmail(email); 69 familyMember.setPhone(phone); 70 familyMember.setRemark(remark); 71 familyMembers.add(familyMember); 72 } 73 return familyMembers; 74 }catch (Exception e){ 75 throw new RuntimeException(e); 76 }finally { 77 DbcpUtils.closeConnection(connection,preparedStatement,resultSet); 78 } 79 }*/ 80 81 /* 82 @Override 83 public int queryFamilyMemberCount(FamilyMemberQueryBean familyMemberQueryBean) { 84 Connection connection =null; 85 PreparedStatement preparedStatement=null; 86 ResultSet resultSet=null; 87 try { 88 connection = DbcpUtils.getConnection(); 89 String sql ="SELECT count(*) FROM family_member t WHERE 1 = 1 "; 90 //拼接SQL 91 List<String> parameters = new ArrayList<String>(); 92 if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){ 93 parameters.add(familyMemberQueryBean.getFullName()); 94 sql+=" and t.full_name like concat('%',?,'%')"; 95 } 96 if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){ 97 parameters.add(familyMemberQueryBean.getEmail()); 98 sql+=" and t.email like concat('%',?,'%')"; 99 } 100 if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){ 101 parameters.add(familyMemberQueryBean.getPhone()); 102 sql+=" and t.phone like concat('%',?,'%')"; 103 } 104 if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){ 105 parameters.add(familyMemberQueryBean.getRemark()); 106 sql+=" and t.remark like concat('%',?,'%')"; 107 } 108 DbcpUtils.printlnSQL(sql); 109 DbcpUtils.printlnParameters(parameters); 110 preparedStatement = connection.prepareStatement(sql); 111 //设置参数 112 for(int i=0;i<parameters.size();i++){ 113 preparedStatement.setString(i+1,parameters.get(i)); 114 } 115 resultSet = preparedStatement.executeQuery(); 116 resultSet.next(); 117 int count = resultSet.getInt(1); 118 return count; 119 }catch (Exception e){ 120 throw new RuntimeException(e); 121 }finally { 122 DbcpUtils.closeConnection(connection,preparedStatement,resultSet); 123 } 124 }*/ 125 126 public List<FamilyMember> queryFamilyMembers(FamilyMemberQueryBean familyMemberQueryBean) { 127 Connection connection =null; 128 PreparedStatement preparedStatement=null; 129 ResultSet resultSet=null; 130 try { 131 connection = DbcpUtils.getConnection(); 132 String sql ="SELECT id,full_name,email,phone,remark FROM family_member t WHERE 1 = 1 "; 133 //拼接SQL 134 List<Object> parameters = new ArrayList<Object>(); 135 if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){ 136 parameters.add(familyMemberQueryBean.getFullName()); 137 sql+=" and t.full_name like \"%\"?\"%\""; 138 } 139 if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){ 140 parameters.add(familyMemberQueryBean.getEmail()); 141 sql+=" and t.email like \"%\"?\"%\""; 142 } 143 if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){ 144 parameters.add(familyMemberQueryBean.getPhone()); 145 sql+=" and t.phone like \"%\"?\"%\""; 146 } 147 if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){ 148 parameters.add(familyMemberQueryBean.getRemark()); 149 sql+=" and t.remark like \"%\"?\"%\""; 150 } 151 sql+=" limit ?,?"; 152 parameters.add(familyMemberQueryBean.getStart()); 153 parameters.add(familyMemberQueryBean.getLimit()); 154 DbcpUtils.printlnSQL(sql); 155 DbcpUtils.printlnParameters(parameters); 156 preparedStatement = connection.prepareStatement(sql); 157 //设置参数 158 for(int i=0;i<parameters.size();i++){ 159 preparedStatement.setObject(i+1,parameters.get(i)); 160 } 161 resultSet = preparedStatement.executeQuery(); 162 List<FamilyMember> familyMembers = new ArrayList<FamilyMember>(); 163 while (resultSet.next()){ 164 FamilyMember familyMember = new FamilyMember(); 165 int id = resultSet.getInt("id"); 166 String fullName = resultSet.getString("full_name"); 167 String email = resultSet.getString("email"); 168 String phone = resultSet.getString("phone"); 169 String remark = resultSet.getString("remark"); 170 familyMember.setId(id); 171 familyMember.setFullName(fullName); 172 familyMember.setEmail(email); 173 familyMember.setPhone(phone); 174 familyMember.setRemark(remark); 175 familyMembers.add(familyMember); 176 } 177 return familyMembers; 178 }catch (Exception e){ 179 throw new RuntimeException(e); 180 }finally { 181 DbcpUtils.closeConnection(connection,preparedStatement,resultSet); 182 } 183 } 184 185 @Override 186 public int queryFamilyMemberCount(FamilyMemberQueryBean familyMemberQueryBean) { 187 Connection connection =null; 188 PreparedStatement preparedStatement=null; 189 ResultSet resultSet=null; 190 try { 191 connection = DbcpUtils.getConnection(); 192 String sql ="SELECT count(*) FROM family_member t WHERE 1 = 1 "; 193 //拼接SQL 194 List<String> parameters = new ArrayList<String>(); 195 if(familyMemberQueryBean.getFullName()!=null&&familyMemberQueryBean.getFullName().length()>0){ 196 sql+=" and t.full_name like '%"+familyMemberQueryBean.getFullName()+"%'"; 197 } 198 if(familyMemberQueryBean.getEmail()!=null&&familyMemberQueryBean.getEmail().length()>0){ 199 sql+=" and t.email like '%"+familyMemberQueryBean.getEmail()+"%'"; 200 } 201 if(familyMemberQueryBean.getPhone()!=null&&familyMemberQueryBean.getPhone().length()>0){ 202 sql+=" and t.phone like '%"+familyMemberQueryBean.getPhone()+"%'"; 203 } 204 if(familyMemberQueryBean.getRemark()!=null&&familyMemberQueryBean.getRemark().length()>0){ 205 sql+=" and t.remark like '"+familyMemberQueryBean.getRemark()+"'"; 206 } 207 DbcpUtils.printlnSQL(sql); 208 DbcpUtils.printlnParameters(parameters); 209 preparedStatement = connection.prepareStatement(sql); 210 //设置参数 211 for(int i=0;i<parameters.size();i++){ 212 preparedStatement.setString(i+1,parameters.get(i)); 213 } 214 resultSet = preparedStatement.executeQuery(); 215 resultSet.next(); 216 int count = resultSet.getInt(1); 217 return count; 218 }catch (Exception e){ 219 throw new RuntimeException(e); 220 }finally { 221 DbcpUtils.closeConnection(connection,preparedStatement,resultSet); 222 } 223 } 224 }
