PreparedStatement，根据条件查询总数

半世晨晓0719

已于 2022-09-01 16:51:00 修改

阅读量944

点赞数

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

于 2022-09-01 16:50:59 首次发布

本文链接：https://blog.csdn.net/qq_45939736/article/details/126646840

版权

JDBC 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

PreparedStatement，条件查询总数

package com.javakc.test2;

import com.javakc.db.Utils;
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 testQueryCount {
    public static long selectCount(Condition condition) throws SQLException {
        Connection connection = null;
        long count = 0;
        try {
            //获取连接
            connection = Utils.getConnection();
            //拼接带条件的sql语句
            String sql = "select count(*)c 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());
                }
                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();
            //处理数据库返回的结果
            if (resultSet.next()) {
                count = resultSet.getLong("c");
            }
            preparedStatement.close();
            resultSet.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                //关闭连接
                connection.close();
            }
        }
        return count;
    }

    public static void main(String[] args) throws SQLException {
        Condition c = new Condition();
        c.setName("a");
        c.setSex("男");
        c.setCredit1(1);
        c.setCredit2(5);
        long l = selectCount(c);
        System.out.println(l);
    }
}