PostgreSQL boolean语法错误 JSP仅展示单条用户商品问题

问题现象 #

需求为用户登录后，通过用户邮箱匹配productlist表的added_by_user字段，返回该用户新增的全部商品记录在JSP页面展示。实际运行时每个用户仅能展示1条记录：例如某用户新增5件商品，页面仅显示第一条。尝试修改SQL后触发数据库类型错误，相关代码、报错信息如下。

问题代码（DAO层） #

public List<Product> select_products_added_by_user(User user) throws SQLException, ClassNotFoundException
{
    Connection conn = DatabaseConnection.initializeDatabase();
    
    List<Product> listProduct = new ArrayList<>();  

    // 该查询仅返回单条用户记录
    // String query = "select * from productlist where added_by_user = ?" ;    
    // 修改后的查询执行抛出布尔类型输入语法错误
    String query = "select * from productlist where added_by_user in(select added_by_user from productlist group by added_by_user having count(*) > 1 ) = ? ";    

    PreparedStatement prestmt = conn.prepareStatement(query);
    prestmt.setString(1,user.email); 
        
    ResultSet rs = prestmt.executeQuery();
        
    if(rs.next())
    {   
        int id = rs.getInt("id");                            
        String code = rs.getString("code");
        String name = rs.getString("name");
        int price = rs.getInt("price");
        String home_main_category = rs.getString("home_main_category");
        String home_sub_category = rs.getString("home_sub_category");
        String p_avail = rs.getString("p_avail");
        String p_act = rs.getString("p_act");
        String pro_exp_date = rs.getString("pro_exp_date");
        String pro_manufacture_date = rs.getString("pro_manufacture_date");  
        String added_by_user = rs.getString("added_by_user");
        
        listProduct.add(new Product(id,code,name,price,home_main_category,home_sub_category,p_avail,p_act,pro_exp_date,pro_manufacture_date,added_by_user));
    }
    return listProduct;     
}

报错堆栈 #

org.postgresql.util.PSQLException: ERROR: invalid input syntax for type boolean: "sk@gmail.com"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2297)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
    at com.pamposh.java.ProductDAO.select_products_added_by_user(ProductDAO.java:107)
    at com.pamposh.java.ControllerServlet.select_products_added_by_user(ControllerServlet.java:525)
    at com.pamposh.java.ControllerServlet.doGet(ControllerServlet.java:83)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)

根因分析 #

• 最初版本SQLselect * from productlist where added_by_user = ?本身语法和逻辑完全正确，仅返回1条记录的核心原因是结果集遍历逻辑错误：代码用if(rs.next())做判断，只会读取结果集的第一行就终止，不会遍历后续匹配的记录。
• 修改后的SQL存在语法错误：in(...)子查询后直接拼接= ?，数据库会将in(...)的返回结果作为布尔值，和传入的邮箱字符串做等值比较，触发类型不匹配错误，也就是堆栈中提示的invalid input syntax for type boolean。

修复方案 #

1. 恢复使用逻辑正确的简单条件查询SQL，不需要多余的子查询：

   select * from productlist where added_by_user = ?
   

2. 将结果集遍历的if(rs.next())修改为while(rs.next())，循环读取所有匹配的记录，逐行封装为Product对象加入返回列表。
3. 补充资源关闭逻辑，避免数据库连接、语句、结果集泄漏。

修复后的核心代码段：

String query = "select * from productlist where added_by_user = ?" ;    
PreparedStatement prestmt = conn.prepareStatement(query);
prestmt.setString(1,user.email); 
ResultSet rs = prestmt.executeQuery();
// 循环遍历所有结果行
while(rs.next())
{   
    // 原有字段读取和对象封装逻辑不变
    int id = rs.getInt("id");                            
    String code = rs.getString("code");
    String name = rs.getString("name");
    int price = rs.getInt("price");
    String home_main_category = rs.getString("home_main_category");
    String home_sub_category = rs.getString("home_sub_category");
    String p_avail = rs.getString("p_avail");
    String p_act = rs.getString("p_act");
    String pro_exp_date = rs.getString("pro_exp_date");
    String pro_manufacture_date = rs.getString("pro_manufacture_date");  
    String added_by_user = rs.getString("added_by_user");
    listProduct.add(new Product(id,code,name,price,home_main_category,home_sub_category,p_avail,p_act,pro_exp_date,pro_manufacture_date,added_by_user));
}
// 关闭资源
rs.close();
prestmt.close();
conn.close();

内容的提问来源于stack exchange，提问作者kiranaa