在 JSP 代码中获取错误"error:java.sql.SQLException: Invalid column index"

Question

我收到错误"错误：java.sql.SQLException：无效的列索引"，其中包含以下代码段。你能帮忙我在这里做错的地方吗？

String SEARCH_SQL_COUNT = "SELECT COUNT(*) FROM (
SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
FROM PERSONAL_INFO UNION ALL 
SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
FROM ELEC_AUTO_MERC UNION ALL 
SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
FROM MISSING_PERSON_INFO UNION ALL 
SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
FROM PETS_INFO UNION ALL 
SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
FROM REAL_ESTATE UNION ALL 
SELECT AD_TITLE,DESCRIPTION,STATE_NAME,CITY_NAME,CATEGORY,SUBCATEGORY 
FROM SERVICES_INFO) 
WHERE UPPER(AD_TITLE) LIKE UPPER('%?%') 
OR UPPER(DESCRIPTION) LIKE UPPER('%?%') 
OR UPPER(STATE_NAME) LIKE UPPER('%?%') 
OR UPPER(CITY_NAME) LIKE UPPER('%?%') 
OR UPPER(CATEGORY) LIKE UPPER('%?%') 
OR UPPER(SUBCATEGORY) LIKE UPPER('%?%')";
int ad_count=0;
PreparedStatement st_fetch_product_count_1 = conn.prepareStatement(SEARCH_SQL_COUNT);
st_fetch_product_count_1.setString(1,qs_keyword);
st_fetch_product_count_1.setString(2,qs_keyword);
st_fetch_product_count_1.setString(3,qs_keyword);
st_fetch_product_count_1.setString(4,qs_keyword);
st_fetch_product_count_1.setString(5,qs_keyword);
st_fetch_product_count_1.setString(6,qs_keyword);
ResultSet rs_fetch_product_count_1 = st_fetch_product_count_1.executeQuery();

Answer 1

您正在执行SELECT COUNT(*)语句。 此类语句仅生成包含单列(计数(的一行。 您的setString()调用需要 6 列，因此索引 2 已经超出范围。

此外，您正在尝试对只读结果集使用setString()，但这是一个不同的问题。