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();
String sql = "select count(*)c from card where 1=1 ";
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);
}
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < paramList.size(); i++) {
preparedStatement.setObject(i + 1, paramList.get(i));
}
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);
}
}