select查询中单引号引起的问题

Question

public void getExp(String bool_expression,int groupId,int expLevel){
    List<String> list=new ArrayList<String>();
    List<String> nextExpressionList = new ArrayList<String>();
    try{
        ResultSet resultSet=null;
        String sqlString = null;
        Statement stmt = null;

        if(expLevel==1){
            System.out.println("explevel---"+expLevel+"group Id --"+groupId);
            sqlString ="select bool_expression from LNP_ENG_EXPRESSIONS where fk_group="+groupId+" and expression_level="+expLevel+"";  
            stmt =connection.createStatement();
            resultSet= stmt.executeQuery(sqlString);
            while(resultSet.next()){
                nextExpressionList.add(resultSet.getString(1));
                System.out.println("expression -- "+ resultSet.getString(1));
            }
        }
        if(expLevel > 1 ){
            System.out.println("bool_ expression --"+bool_expression);
            String sql = "select distinct variable_name from LNP_ENG_VARIABLES where id IN "+
            "(select fk_variable_id from LNP_ENG_QUESTIONS where question_code IN "+
            "( select Question_code from LNP_APP_QUESTIONS where id IN "+
            "(select fk_question_id from LNP_ENG_ASC_QUESTION_EXP where FK_EXP_ID IN"+
            "(select id from LNP_ENG_EXPRESSIONS where bool_expression = '"+bool_expression+"'"+"and fk_group="+groupId+" and expression_level="+(expLevel-1)+"))))";
            System.out.println("1");
            stmt =connection.createStatement();
            resultSet=stmt.executeQuery(sql);
            while(resultSet.next()){
                list.add(resultSet.getString(1));
                System.out.println("list --"+resultSet.getString(1));
            }   
            for(int i=0;1<list.size();i++){
                sqlString = "select distinct bool_expression from LNP_ENG_EXPRESSIONS where "+
                "bool_expression like '%"+list.get(i)+"%' and expression_level="+expLevel+" and fk_group="+groupId+"";
                resultSet = stmt.executeQuery(sqlString);
                while(resultSet.next()){
                        nextExpressionList.add(resultSet.getString(1));
                        System.out.println("expression -- "+ nextExpressionList.get(i));
                }                   
            }
        }
    }
    catch (Exception e) {
        // TODO: handle exception
    }
}



public static void main(String args[]){

    ExpressionBuilder builder=new ExpressionBuilder();
    builder.getExp("Industry='NO'", 1, 2);
} 

在将bool_expression作为Industry='NO'传递给sql查询时出现错误。该错误是由单引号'‘引起的。但是我不能解决这个问题。

Answer 1

它是易于使用的PreparedStatement，并让它为您正确地转义字符串。

虽然它不能解释你的问题，但我不太喜欢你的代码。变得太大了。我通过创建一个具有三个方法的单独的DAO接口进行重构，每个方法分别用于您正在执行的每个查询。我会在DAO实现类中使这些查询字符串成为静态的final常量。我会单独测试这个。当它工作正常时，我会给检查经验级别的对象一个对DAO的引用，并让它调用它的方法，而不是将所有数据库逻辑嵌入到一个类中。

这叫做“分解”。当问题变得越来越大时，它会帮助你管理它。

如下所示：

package persistance;

public interface FooDao
{
    List<Foo> find(String name);
}

public class FooDaoImpl implements FooDao
{
    public static final String FIND_BY_NAME_SQL = "SELECT * FROM Foo WHERE name = ?";

    private DataSource dataSource;

    public Foo(DataSource dataSource)
    {   
        this.dataSource = dataSource;
    }

    public List<Foo> find(String name)
    {
        List<Foo> result = new ArrayList<Foo>();

        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {
            ps = this.dataSource.getConnection().prepareStatement(FIND_BY_NAME_SQL);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.hasNext())
            {
                // Map row into Foo and add it to the List
                result.add(foo);
            }
        }
        catch (SQLException e)
        {
           throw new RuntimeException(e);
        }
        finally
        {
            close(rs);
            close(ps);
        }
    }
}

Answer 2

这在另一篇文章中也有涉及，但我想强调的是，“单引号问题”几乎就是"“的同义词。