如何解决Java连接Snowflake时出现“Bind variable ? not set”报错

根因分析 #

• SQL语法错误：每段SELECT查询的最后一个字段末尾多写了多余的逗号，会导致数据库解析SQL失败；同时UNION ALL的各子查询字段数量不匹配，前三个查询返回3个字段，最后一段HISTORY表的查询返回4个字段，不符合UNION语法要求。
• 占位符绑定不完整：你的SQL语句中一共定义了4个?占位符，但调用jdbcTemplate.query方法时仅传入了1个userId参数，JDBC无法完成剩余3个占位符的赋值，直接触发你遇到的Bind variable ? not set报错。

修复代码示例 #

public void EPI_GAME_REP_QUERY(String id) {
    long userId = Long.parseLong(id);
    // 修正SQL语法：删除多余逗号、统一UNION字段数量
    String REP_QUERY = "SELECT \n" +
            "                \"ProductName\",\n" +
            "                \"UserID\",\n" +
            "                \"UserName\",\n" +
            "                NULL AS \"StartTime\"\n" + 
            "            FROM \"DATABSE4\".\"RAW_DATA\".\"BETA\"\n" +
            "            WHERE \"UserID\" = ? \n" +
            "            UNION ALL\n" +
            "            SELECT \n" +
            "                \"ProductName\",\n" +
            "                \"UserID\",\n" +
            "                \"UserName\",\n" +
            "                NULL AS \"StartTime\"\n" +
            "            FROM \"DATABASE3\".\"RAW_DATA\".\"BETAC\"\n" +
            "            WHERE \"UserID\" = ? \n" +
            "            UNION ALL\n" +
            "            SELECT  \n" +
            "                \"ProductName\",\n" +
            "                \"UserID\",\n" +
            "                \"UserName\",\n" +
            "                NULL AS \"StartTime\"\n" +
            "            FROM \"DATABASE1\".\"RAW_DATA\".\"ACTIVITY\"\n" +
            "            WHERE \"UserID\" = ? \n" +
            "            UNION ALL\n" +
            "            SELECT  \n" +
            "                \"ProductName\",\n" +
            "                \"StartTime\",\n" +
            "                \"UserID\",\n" +
            "                \"UserName\"\n" +
            "            FROM \"DATABASE1\".\"RAW_DATA\".\"HISTORY\"\n" +
            "            WHERE \"UserID\" = ? \n" +
            "            ORDER BY \"StartTime\" ASC;";

    log.info("Executing query...");
    // 给4个占位符依次传入userId参数
    jdbcTemplate.query(REP_QUERY, rs -> {
        // 处理结果集逻辑
    }, userId, userId, userId, userId);
}

如果你不需要返回StartTime字段，也可以直接删除最后一段SELECT中的"StartTime",行，不用给前面三个查询补充NULL字段，同样可以满足UNION的字段数量要求。

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