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);

         }

     }

 }

最新文章

  1. JavaScript 面向对象继承的实现
  2. prototype数组方法的实现
  3. P1-概率论基础（Primer on Probability Theory）
  4. Maven最佳实践：划分模块
  5. bzoj 1054 移动玩具
  6. 通过Curator操作Zookeeper的简单例子代码
  7. eclipse实现JavaWeb应用增量打包
  8. JavaWeb_数据传输_原
  9. C++动态分配内存
  10. ☀【组件 - 工具】Parallax 视差
  11. poj 1979 Red and Black（dfs水题）
  12. ”Metro UI之磁贴（二）
  13. css所有属性参考
  14. 【译】如何高效的使用 Git
  15. python经典书籍推荐：python编码规范
  16. django的视图函数
  17. 「UVA10766」Organising the Organisation（生成树计数）
  18. [每天解决一问题系列 - 0008] 关于.net framework 路径最大长度的问题
  19. openssl rsa/pkey
  20. laravel 前后端分离 token

热门文章

  1. 使用 atom 将 makedown 编辑并转换成 pdf
  2. Callback must be a function. Received undefined
  3. Spark实战系列目录
  4. Content-Type四种常见取值
  5. 使用pprof调试go程序
  6. Spring Initializr生成的demo测试404错误
  7. shoshana-技术文集
  8. springboot异步线程(二)
  9. （谷歌浏览器）前端以FormData类形成表单（含文件），通过ajax提交，PHP后端$_POST数组为空
  10. libevent源码分析一--io事件响应