Java子查询语法错误问题：PostgreSQL可执行但Eclipse编译失败

这个错误的核心原因是字符串拼接时缺少必要的空格，导致最终生成的SQL语句语法失效。虽然你的SQL在PostgreSQL客户端中单独执行没问题，但Java字符串拼接时的空格缺失让数据库解析器无法识别合法的语法结构。

具体错误点梳理 #

我帮你找到了几处关键的空格缺失：

1. 第一个子查询的末尾：

   "AND v.master_user_id = a.master_user_id" + "AND v.master_role_id = a.master_role_id ) as in_progress, "
   

   拼接后变成：AND v.master_user_id = a.master_user_idAND v.master_role_id = a.master_role_id，数据库会把a.master_user_idAND当成一个无效的标识符，进而在遇到v时抛出语法错误。

2. 第四个子查询的开头：

   "(SELECT count (y.id) FROM project_member_dtl y" + "LEFT JOIN project_timeline s ON y.project_timeline_id = s.id "
   

   拼接后变成：project_member_dtl yLEFT JOIN...，表别名y和LEFT关键字之间没有空格，破坏了JOIN语句的语法结构。

3. 第五个子查询的中间：

   "LEFT JOIN project_timeline t ON z.project_timeline_id = t.id" + "LEFT JOIN master_project_dtl n ON t.master_project_dtl_rid = n.rid"
   

   拼接后变成：t.idLEFT JOIN...，字段id和LEFT关键字之间缺少空格，同样导致语法错误。

修正后的代码 #

我已经给所有缺失空格的地方补上了空格，你可以直接替换使用：

@Override
protected StringBuffer buildQuery(String sql, Object... objects) {
    StringBuffer sbSQL = new StringBuffer("SELECT a.master_user_id, b.name, d.group_name, a.role, "
            + "(SELECT count (v.id) FROM project_member_dtl v "
            + "LEFT JOIN project_timeline p ON v.project_timeline_id = p.id "
            + "LEFT JOIN master_project_dtl j ON p.master_project_dtl_rid = j.rid "
            + "LEFT JOIN master_project aa ON j.master_project_sid = aa.sid "
            + "where ((v.member_plan_date > now()) AND (v.member_real_date IS NULL)) "
            + "AND j.is_freeze = 'n' "
            + "AND p.is_active = 'y' "
            + "AND aa.is_deleted = 'n' "
            + "AND v.master_user_id = a.master_user_id "  // 补上缺失的空格
            + "AND v.master_role_id = a.master_role_id ) as in_progress, "
            + "(SELECT count (w.id) FROM project_member_dtl w "
            + "LEFT JOIN project_timeline q ON w.project_timeline_id = q.id "
            + "LEFT JOIN master_project_dtl k ON q.master_project_dtl_rid = k.rid "
            + "LEFT JOIN master_project bb ON k.master_project_sid = bb.sid "
            + "where ((w.member_plan_date < now()) "
            + "AND (w.member_real_date IS NULL)) "
            + "AND k.is_freeze = 'n' "
            + "AND q.is_active = 'y' "
            + "AND bb.is_deleted = 'n' "
            + "AND w.master_user_id = a.master_user_id "
            + "AND w.master_role_id = a.master_role_id ) as in_progress_late, "
            + "(SELECT count (x.id) FROM project_member_dtl x "
            + "LEFT JOIN project_timeline r ON x.project_timeline_id = r.id "
            + "LEFT JOIN master_project_dtl l ON r.master_project_dtl_rid = l.rid "
            + "LEFT JOIN master_project cc ON l.master_project_sid = cc.sid "
            + "where (x.member_plan_date IS NULL) "
            + "AND l.is_freeze = 'n' AND r.is_active = 'y' "
            + "AND cc.is_deleted = 'n' "
            + "AND x.master_user_id = a.master_user_id "
            + "AND x.master_role_id = a.master_role_id ) as waiting_list, "
            + "(SELECT count (y.id) FROM project_member_dtl y "  // 补上缺失的空格
            + "LEFT JOIN project_timeline s ON y.project_timeline_id = s.id "
            + "LEFT JOIN master_project_dtl m ON s.master_project_dtl_rid = m.rid "
            + "LEFT JOIN master_project dd ON m.master_project_sid = dd.sid "
            + "where (y.member_plan_date > y.member_real_date) "
            + "AND m.is_freeze = 'n' AND s.is_active = 'y' "
            + "AND dd.is_deleted = 'n' "
            + "AND y.master_user_id = a.master_user_id "
            + "AND y.master_role_id = a.master_role_id ) as finish_on_time, "
            + "(SELECT count (z.id) FROM project_member_dtl z "
            + "LEFT JOIN project_timeline t ON z.project_timeline_id = t.id "  // 补上缺失的空格
            + "LEFT JOIN master_project_dtl n ON t.master_project_dtl_rid = n.rid "
            + "LEFT JOIN master_project ee ON n.master_project_sid = ee.sid "
            + "where (z.member_plan_date < z.member_real_date) "
            + "AND n.is_freeze = 'n' AND t.is_active = 'y' "
            + "AND ee.is_deleted = 'n' "
            + "AND z.master_user_id = a.master_user_id "
            + "AND z.master_role_id = a.master_role_id ) as finish_over_time "  // 补上缺失的空格
            + "FROM project_member_dtl a");
    sbSQL.append("LEFT JOIN master_user b ON a.master_user_id = b.id ");
    sbSQL.append("LEFT JOIN user_group c ON a.master_user_id = c.master_user_id ");
    sbSQL.append("LEFT JOIN master_group d ON c.group_id = d.id ");
    sbSQL.append(sql);
    return sbSQL;
}

额外建议 #

为了避免以后再出现类似问题，你可以：

• 养成每个字符串片段末尾保留一个空格的习惯，这样拼接时自然会分隔开不同的SQL关键字和标识符；
• 考虑使用PreparedStatement构建动态SQL，不仅能避免这类语法错误，还能有效防止SQL注入风险；
• 拼接完成后，先打印出生成的SQL语句，复制到PostgreSQL客户端中验证语法是否正确，这样能快速定位问题。

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