JDBC实现模糊、动态与分页查询的详解

本章来讲一下，JDBC的模糊查询、动态查询以及分页查询的三种查询方式
旨在帮助大家更好的理解和掌握这三种查询方式，希望能够帮助到大家

一. 模糊查询

1. Mysql的写法

这里先回忆一下sql中模糊查询的写法

1. like: 模糊查询关键字
2. %: 模糊查询通配符
3. _: 模糊查询占位符

例如查询学生姓名首字母为A的同学
select * from students where studentName like ‘A%’;

2. JDBC的实现

那么接下来就使用JDBC来实现模糊查询

1. 获取数据库连接

conn = JdbcUtils.getConnection();

2. 创建PreparedStatement对象

ps = conn.prepareStatement("select * from users where username like ?");

3. 参数绑定

ps.setString(1,username);

4. 执行sql语句并且保存到集合中

rs = ps.executeQuery();
while(rs.next()){Users user = new Users();user.setUserid(rs.getInt("userid"));user.setUsername(rs.getString("username"));user.setUserage(rs.getInt("userage"));list.add(user);}

5. 释放资源

JdbcUtils.closeResource(rs, ps, connection);

完整代码

public class FuzzyQueryTest {/*** 根据用户名称模糊查找用户信息*/public List<Users> fuzzyQuery(String username){// 用于保存查询的数据List<Users> list= new ArrayList<>();Connection conn =null;PreparedStatement ps = null;ResultSet rs = null;try{ // 获取数据库连接conn = JdbcUtils.getConnection();// 创建PreparedStatement对象ps = conn.prepareStatement("select * from users where username like ?");// 参数绑定ps.setString(1,username);// 执行sql语句rs = ps.executeQuery();while(rs.next()){Users user = new Users();user.setUserid(rs.getInt("userid"));user.setUsername(rs.getString("username"));user.setUserage(rs.getInt("userage"));list.add(user);}}catch(Exception e){e.printStackTrace();}finally{JdbcUtils.closeResource(rs,ps,conn);}// 返回数据集合 return list;}// 测试代码public static void main(String[] args) {FuzzyQueryTest ft = new FuzzyQueryTest();// 相当于查询// select * from users where username like %Y%// 并且保存数据集合List<Users> users = ft.fuzzyQuery("%Y%");for(Users user1 : users){System.out.println(user1.getUserid()+" "+user1.getUsername()+" "+user1.getUserage());}}
}

这里的测试代码实现的是，在users表中查询名字有Y这个字母的所有数据
原表数据为：

运行后得到结果为

二. 动态条件查询

动态条件查询就是根据用户给定的条件，来执行对应的查询
例如：要对用户的姓名和年龄对应的数据
这就提升了灵活性，弹性

先复习一下SQL，也就是先使用SQL语句来写一下查询方法
问题：在users表中，查询名字为LuoYi，年龄为18岁的用户信息
那么SQL语句应该是
select * from users where username = ‘LuoYi’ and userage = 18
这样就可以实现查询对应条件的数据了
接下来使用JDBC来实现

1. 创建生成动态条件查询sql的方法

这是在动态条件查询类中创建的一个方法

private String generateSql(Users users){// 初始化SQL语句，使用1=1确保后续条件可以直接用and连接 	  StringBuffer sb = new StringBuffer("select * from users where 1=1 ");// 如果有userid要修改的值// 那么就将这个userid添加到这个查询条件中  if(users.getUserid() > 0){sb.append(" and userid = ").append(users.getUserid());}// 这个也是，如果username有要修改的值// 那么就对应的添加修改条件  if(users.getUsername() !=null &&users.getUsername().length() > 0){// 因为这里的username是字符串，所以要添加单引号  sb.append(" and username = '").append(users.getUsername()).append("'");}// 同理  if(users.getUserage() > 0){sb.append(" and userage = ").append(users.getUserage());}// 最后再将其转换成字符串  return sb.toString();}

2. 完整的动态条件查询类以及测试类

public class DynamicConditionQueryTest {/*** 动态条件查询Users*/public List<Users> queryUsers(Users users){List<Users> list= new ArrayList<>();Connection conn =null;PreparedStatement ps = null;ResultSet rs = null;try{//获取数据库连接conn = JdbcUtils.getConnection();//拼接查询SQL语句String sql = this.generateSql(users);System.out.println(sql);//创建PreparedStatement对象ps = conn.prepareStatement(sql);//执行sql语句rs = ps.executeQuery();while(rs.next()){Users user = new Users();user.setUserid(rs.getInt("userid"));user.setUsername(rs.getString("username"));user.setUserage(rs.getInt("userage"));list.add(user);}}catch(Exception e){e.printStackTrace();}finally{JdbcUtils.closeResource(rs,ps,conn);}return list;}/*** 生成动态条件查询sql*/private String generateSql(Users users){// 初始化SQL语句，使用1=1确保后续条件可以直接用and连接 	  StringBuffer sb = new StringBuffer("select * from users where 1=1 ");// 如果有userid要修改的值// 那么就将这个userid添加到这个查询条件中  if(users.getUserid() > 0){sb.append(" and userid = ").append(users.getUserid());}// 这个也是，如果username有要修改的值// 那么就对应的添加修改条件  if(users.getUsername() !=null &&users.getUsername().length() > 0){// 因为这里的username是字符串，所以要添加单引号  sb.append(" and username = '").append(users.getUsername()).append("'");}// 同理  if(users.getUserage() > 0){sb.append(" and userage = ").append(users.getUserage());}// 最后再将其转换成字符串  return sb.toString();}// 测试类
public static void main(String[] args) {DynamicConditionQueryTest dt = new DynamicConditionQueryTest();Users users = new Users();// 存放的值就是要修改的条件users.setUsername("LuoYi");users.setUserage(18);List<Users> list = dt.queryUsers(users);for(Users user1 : list){System.out.println(user1.getUserid()+" "+user1.getUsername()+" "+user1.getUserage());}}
}

三. 分页查询

1. 什么是分页查询？

当要显示的数据过多时，就会导致内存溢出
那么分页查询就是来避免内存溢出的，降低内存消耗
也就是，我想要一页显示多少，就显示多少

2. 分页查询的分类

1. 物理分页：
   a. 在数据库执行查询时（实现分页查询），查询需要的数据—依赖数据库的SQL语句
   b. 在SQL查询时，从数据库只检索分页需要的数据
   c. 通常不同的数据库有着不同的物理分页语句
   d. MySql物理分页采用limit关键字
2. 逻辑分页：
   在sql查询时，先从数据库检索出所有数据的结果集，在程序内，通过逻辑语句获得分页需要的数据
   简单来说，逻辑分页，就是在java程序中，通过循环语句来实现一页显示多少数据

3. MySQL的实现

先复习一下MySQL中的分页查询使用SQL如何实现

select * from tableName limit m,n

其中
m 代表从哪开始(以0为起始)
n 代表需要获取多少行的数据项

例如我们想从users表中先获取前两条数据SQL为：
select * from users limit 0, 2;
那么如果要获取下一页显示2页数据，怎么来实现呢
select * from users limit 2, 2
以此类推，就能够看出规律，n是不变的，然后就看m
m 的公式为： （当前页 - 1) × 每页大小

4. JDBC实现

4.1. 创建page页

这个是分页的实体类

public class Page<T> {// 当前页private int currentPage;// 每页显示的条数private int pageSize;// 总条数private int totalCount;// 总页数private int totalPage;// 结果集private List<T> result;public int getCurrentPage() {return currentPage;}public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public int getTotalCount() {return totalCount;}public void setTotalCount(int totalCount) {this.totalCount = totalCount;}public int getTotalPage() {return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public List<T> getResult() {return result;}public void setResult(List<T> result) {this.result = result;}
}

4.2. 分页的实现

public class PageTest {/*** 分页查询Users*/public Page<Users> selectPage(Page page){Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;List<Users> list = new ArrayList<>();try{// 获取连接对象conn = JdbcUtils.getConnection();// 创建PrepareStatement对象ps = conn.prepareStatement("select * from users limit ?,?");// 绑定m参数  m的值 = ( 当前页 - 1 ) * 每页显示的条数ps.setInt(1,(page.getCurrentPage()-1)*page.getPageSize());// 绑定n参数 n的值为每页显示的条数ps.setInt(2,page.getPageSize());// 执行查询rs = ps.executeQuery();// 处理结果集while(rs.next()){// 完成ORM映射Users users = new Users();users.setUserid(rs.getInt("userid"));users.setUsername(rs.getString("username"));users.setUserage(rs.getInt("userage"));list.add(users);}// 将结果集存放到Page对象中。page.setResult(list);// 查询总条数ps = conn.prepareStatement("select count(*) from users");// 执行查询rs = ps.executeQuery();while(rs.next()){// 总条数int count = rs.getInt(1);// 保存总条数page.setTotalCount(count);// 换算总页数 = 总条数 / 每页显示的条数 向上取整int totalPage = (int)Math.ceil(1.0*count/page.getPageSize());// 保存总页数page.setTotalPage(totalPage);}}catch(Exception e){e.printStackTrace();}finally{JdbcUtils.closeResource(rs,ps,conn);}return page;}// 测试类public static void main(String[] args) {PageTest pt = new PageTest();Page page = new Page();page.setCurrentPage(2);page.setPageSize(2);Page page1 = pt.selectPage(page);System.out.println("总条数："+page1.getTotalCount());System.out.println("总页数："+page1.getTotalPage());System.out.println("当前页："+page1.getCurrentPage());System.out.println("每页显示的条数："+page1.getPageSize());List<Users> list = page1.getResult();for(Users user:list){System.out.println(user.getUserid()+ " "+user.getUsername()+" "+user.getUserage());}}
}

原数据为

输出结果

现在讲完了，三种查询方式，希望能够帮助到大家
感谢~😊