PreparedStatement，分页查询

半世晨晓0719

于 2022-09-01 17:14:04 发布

阅读量794

点赞数

分类专栏： JDBC 文章标签： java

本文链接：https://blog.youkuaiyun.com/qq_45939736/article/details/126647540

版权

JDBC 专栏收录该内容

19 篇文章

订阅专栏

该代码示例展示了如何在Java中利用PreparedStatement进行分页查询。通过构建动态SQL，结合Condition对象中的条件（姓名、性别、积分区间），并利用参数占位符防止SQL注入，实现了灵活的数据库查询。最后，遍历ResultSet将查询结果转化为Card对象列表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PreparedStatement，分页查询

package com.javakc.test2;

import com.javakc.db.Utils;
import com.javakc.vo.Card;
import com.javakc.vo.Condition;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class testQueryLimit {
    public static List<Card> queryLimit(Condition condition, int offset, int rows) throws SQLException {
        List<Card> list = new ArrayList<>();
        Connection connection = null;
        try {
            //获取连接
            connection = Utils.getConnection();
            //拼接带条件的sql语句
            String sql = "select id,name,sex,credit from card where 1=1 ";
            //封装带有查询条件的List
            List<Object> paramList = new ArrayList<>();

            //拼接条件
            //判断用户是否输入条件
            if (condition != null) {
                //判断用户是否需要查询姓名
                if (condition.getName() != null && condition.getName().trim().length() > 0) {
                    //姓名,模糊查询
                    sql += "and name like ? ";
                    paramList.add("%" + condition.getName() + "%");
                }
                //判断用户是否需要查询性别
                if (condition.getSex() != null && condition.getSex().trim().length() > 0) {
                    //性别,精确查询
                    sql += "and sex=?";
                    paramList.add(condition.getSex());
                }
                //判断用户是否需要查询积分
                if (condition.getCredit1() != null) {
                    //积分,区间查询
                    sql += "and credit >=? ";
                    paramList.add(condition.getCredit1());
                }
                if (condition.getCredit2() != null) {
                    sql += "and credit<=? ";
                    paramList.add(condition.getCredit2());
                }
            }
            sql += "limit ?,?";
            paramList.add(offset);
            paramList.add(rows);

            System.out.println(sql);
            System.out.println(paramList);
            //创建能处理预编译的sql语句的对象PreparedStatement
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //替换占位符
            for (int i = 0; i < paramList.size(); i++) {
                preparedStatement.setObject(i + 1, paramList.get(i));
            }
            //执行sql语句
            ResultSet resultSet = preparedStatement.executeQuery();
            //处理数据库返回的结果
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                int credit = resultSet.getInt("credit");

                Card c = new Card();
                c.setId(id);
                c.setName(name);
                c.setSex(sex);
                c.setCredit(credit);

                list.add(c);
            }
            preparedStatement.close();
            resultSet.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            if (connection != null) {
                connection.close();
            }
        }
        return list;
    }

    public static void main(String[] args) throws SQLException {
        Condition condition = new Condition();
        condition.setName("a");
        condition.setSex("男");
        condition.setCredit1(1);
        condition.setCredit2(5);

        List<Card> list = queryLimit(condition, 0, 3);
        for (Card card : list) {
            System.out.println(card);
        }
    }
}