如何保持引用以进行正确的SQL查询

当我运行以下程序时

package com.util;
import java.util.ArrayList;
public class Test {
    public static void main(String[] args) throws Exception {
        ArrayList list_of_symbols = new ArrayList();
        list_of_symbols.add("ABB");
        list_of_symbols.add("ACC");
        list_of_symbols.add("SBIN");
        StringBuilder sb_builder = new StringBuilder();
        for (int i = 0; i < list_of_symbols.size(); i++) {
            sb_builder.append(list_of_symbols.get(i) + ",");
        }
        String sql = "Select * from data where symbol_name IN ("
                + sb_builder.deleteCharAt(sb_builder.length() - 1).toString()
                + ")";
        System.out.println(sql);
    }
}

SQL IS的结果

Select * from data where symbol_name IN (ABB,ACC,SBIN)

应该在哪里作为预期结果

Select * from data where symbol_name IN ('ABB','ACC','SBIN')

能告诉我如何保持报价,使其成为有效的SQL

不要使用字符串连接来填充SQL参数.这很容易出错.相反,?根据需要构建SQL ,然后使用a PreparedStatement和尽可能多的setString(x, theString)填写?.

在你的情况下,它看起来大致如下:

package com.util;
import java.util.ArrayList;
public class Test {
    public static void main(String[] args) throws Exception {
        ArrayList list_of_symbols = new ArrayList();
        list_of_symbols.add("ABB");
        list_of_symbols.add("ACC");
        list_of_symbols.add("SBIN");

        // Build the statement
        StringBuilder sql = new StringBuilder(200);
        sql.append("Select * from data where symbol_name IN (");
        for (int i = 0; i < list_of_symbols.size(); i++) {
            sql.append(i == 0 ? "?" : ", ?");
        }
        sql.append(')');

        // Build the PreparedStatement and fill in the parameters
        PreparedStatement ps = someConnection.prepareStatement(sql.toString());
        for (int i = 0; i < list_of_symbols.size(); i++) {
            ps.setString(i + 1, list_of_symbols.get(i));
        }

        // Do it
        ResultSet rs = ps.executeQuery();
    }
}

(这是未经优化的,并且已删除.可能需要进行一些编辑.)

这个网站很好地解释了为什么使用字符串concat参数是一个坏主意,以及如何在包括Java在内的多种语言中正确执行操作的实际示例.