进行sql拼接,setString这个方法一直都无法将问号设置为传进来的参数,打印sql也可以看到?。求各位大牛指点下小弟,万分感谢!!!
//入库卡条件查询
@Override
public int getCoCardCount(String quryInputDate, String quryInputDateTwo, String quryCardNo, String quryCardNoTwo,
String cardstate) {
int key =0;
List<String> params = new ArrayList<String>();
String sql = "SELECT count(*) FROM ( " +
"SELECT X.*, ROWNUM RN " +
" FROM (SELECT * FROM( " +
"select a.card_id,a.card_number,b.storage_time,c.dictionary_name" +
"from t_card a " +
"inner join t_storage b on a.card_incomeid=b.storage_id "; if (quryInputDate != null && quryInputDateTwo != null && quryInputDate != "" && quryInputDateTwo != "") { sql += " and storage_time between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') ";
params.add(quryInputDate);
params.add(quryInputDateTwo);
} sql+="inner join t_dictionary c on a.card_state=c.dictionary_desc and c.dictionary_type='卡状态' ";
if (cardstate != null && cardstate != "") { sql += " and c.dictionary_name=? ";
params.add(cardstate); }
if (quryCardNo != null && quryCardNoTwo != null && quryCardNo != "" && quryCardNoTwo != "") { sql += " inner join t_card d on a.card_id=d.card_id where a.card_number between ? and ? ";
params.add(quryCardNo);
params.add(quryCardNoTwo); }
sql+=") order by storage_time desc)X) ";
Connection conn = DbUtil.getDbUtil().getConn();
PreparedStatement pstmt = null;
ResultSet res = null;
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.size(); i++) { pstmt.setString(i + 1, params.get(i)); }
res = pstmt.executeQuery();
if (res.next()) { key=res.getInt("count(*)"); } } catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtil.release(res, pstmt, conn);
} return key; }
//入库卡条件查询
@Override
public int getCoCardCount(String quryInputDate, String quryInputDateTwo, String quryCardNo, String quryCardNoTwo,
String cardstate) {
int key =0;
List<String> params = new ArrayList<String>();
String sql = "SELECT count(*) FROM ( " +
"SELECT X.*, ROWNUM RN " +
" FROM (SELECT * FROM( " +
"select a.card_id,a.card_number,b.storage_time,c.dictionary_name" +
"from t_card a " +
"inner join t_storage b on a.card_incomeid=b.storage_id "; if (quryInputDate != null && quryInputDateTwo != null && quryInputDate != "" && quryInputDateTwo != "") { sql += " and storage_time between to_date(?,'yyyy-mm-dd') and to_date(?,'yyyy-mm-dd') ";
params.add(quryInputDate);
params.add(quryInputDateTwo);
} sql+="inner join t_dictionary c on a.card_state=c.dictionary_desc and c.dictionary_type='卡状态' ";
if (cardstate != null && cardstate != "") { sql += " and c.dictionary_name=? ";
params.add(cardstate); }
if (quryCardNo != null && quryCardNoTwo != null && quryCardNo != "" && quryCardNoTwo != "") { sql += " inner join t_card d on a.card_id=d.card_id where a.card_number between ? and ? ";
params.add(quryCardNo);
params.add(quryCardNoTwo); }
sql+=") order by storage_time desc)X) ";
Connection conn = DbUtil.getDbUtil().getConn();
PreparedStatement pstmt = null;
ResultSet res = null;
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < params.size(); i++) { pstmt.setString(i + 1, params.get(i)); }
res = pstmt.executeQuery();
if (res.next()) { key=res.getInt("count(*)"); } } catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtil.release(res, pstmt, conn);
} return key; }
报错如图所示
"SELECT X.*, ROWNUM RN " +
" FROM (
SELECT * FROM( " +
"select a.card_id,a.card_number,b.storage_time,c.dictionary_name" +
"from t_card a " +
"inner join t_storage b on a.card_incomeid=b.storage_id ";
改成:+
String sql = "SELECT count(*) FROM ( " +
"SELECT * FROM( " +
"select a.card_id,a.card_number,b.storage_time,c.dictionary_name" +
"from t_card a " +
"inner join t_storage b on a.card_incomeid=b.storage_id) ";试试看
"SELECT X.*, ROWNUM RN " +
" FROM (
SELECT * FROM( " +
"select a.card_id,a.card_number,b.storage_time,c.dictionary_name" +
"from t_card a " +
"inner join t_storage b on a.card_incomeid=b.storage_id ";
改成:
String sql = "SELECT count(*) FROM ( " +
"select a.card_id,a.card_number,b.storage_time,c.dictionary_name" +
"from t_card a " +
"inner join t_storage b on a.card_incomeid=b.storage_id) ";试试看