java - 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 接口(interface)来进行重构，一种方法用于您正在执行的每个查询。我会将这些查询字符串设置为 DAO 实现类中的静态最终常量。我会单独测试一下。当它完美工作时，我会给检查经验级别的对象提供对 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);
        }
    }
}