PreparedStatement IN子句替代？

对于某些通过JDBC 4规范传递数组的数据库不受支持,此方法可以帮助将慢速= ?转换为更快的IN (?)子句条件,然后可以通过调用该any方法进行扩展.

PostgreSQL的解决方案:

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

要么

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

没有简单的方法AFAIK.如果目标是保持语句高速缓存比率高(即不为每个参数计数创建语句),则可以执行以下操作:

使用一些(例如10个)参数创建一个语句:

......在哪里(？,？,？,？,？,？,？,？,？,？)......

绑定所有实际参数

的SetString(1, "富"); 的SetString(2, "巴");

将其余部分绑定为NULL

setNull(3,Types.VARCHAR)... setNull(10,Types.VARCHAR)

NULL永远不会匹配任何内容,因此SQL计划构建器会对其进行优化.

将List传递给DAO函数时,逻辑很容易自动化:

while( i < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

while( i < MAX_PARAMS ) {
  ps.setNull(i+1,Types.VARCHAR);
  i++;
}

一个令人不快的解决方法,但肯定是可行的是使用嵌套查询.创建一个临时表MYVALUES,其中包含一列.将值列表插入MYVALUES表.然后执行

select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )

丑陋,但如果您的值列表非常大,那么这是一个可行的选择.

这种技术的另一个优点是,如果数据库不缓存预准备语句,优化器可能会提供更好的查询计划(检查页面是否存在多个值,表格只扫描一次,而不是每个值一次,等等)可以节省开销.您的"插入"需要批量完成,并且可能需要调整MYVALUES表以获得最小的锁定或其他高开销保护.

in()运算符的局限性是万恶之源.

它适用于琐碎的情况,您可以通过"自动生成准备好的语句"来扩展它,但它始终有其限制.

如果您正在创建具有可变数量参数的语句,那么将在每次调用时产生sql解析开销

在许多平台上,in()运算符的参数数量是有限的

在所有平台上,总的SQL文本大小是有限的,因此无法向params发送2000个占位符

发送1000-10k的绑定变量是不可能的,因为JDBC驱动程序有其局限性

对于某些情况,in()方法可以很好,但不能防火箭:)

防火解决方案是在单独的调用中传递任意数量的参数(例如,通过传递一些参数),然后使用视图(或任何其他方式)在SQL中表示它们并在您的位置使用标准.

这里有一个蛮力变体http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

但是,如果你可以使用PL/SQL,这个烂摊子会变得非常整洁.

function getCustomers(in_customerIdList clob) return sys_refcursor is 
begin
    aux_in_list.parse(in_customerIdList);
    open res for
        select * 
        from   customer c,
               in_list v
        where  c.customer_id=v.token;
    return res;
end;

然后,您可以在参数中传递任意数量的逗号分隔客户ID,并且:

将不会得到解析延迟,因为select for SQL是稳定的

没有流水线功能复杂性 - 它只是一个查询

SQL使用简单的连接,而不是IN运算符,这非常快

毕竟,这是一个很好的经验规则没有击中任何普通的数据库中选择或DML,因为它是Oracle,它提供了比MySQL或类似的简单的数据库引擎更光年.PL/SQL允许您以有效的方式从应用程序域模型中隐藏存储模型.

这里的诀窍是:

我们需要一个接受长字符串的调用,并存储在db会话可以访问它的地方(例如,简单的包变量或dbms_session.set_context)

那么我们需要一个可以将其解析为行的视图

然后你有一个包含你正在查询的ID的视图,所以你需要的只是一个简单的连接到查询的表.

视图看起来像:

create or replace view in_list
as
select
    trim( substr (txt,
          instr (txt, ',', 1, level  ) + 1,
          instr (txt, ',', 1, level+1)
             - instr (txt, ',', 1, level) -1 ) ) as token
    from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1

其中aux_in_list.getpayload引用原始输入字符串.

一种可能的方法是传递pl/sql数组(仅由Oracle支持),但是您不能在纯SQL中使用它们,因此总是需要转换步骤.转换不能在SQL中完成,因此,毕竟传递带有所有参数的clob并在视图中转换它是最有效的解决方案.

这是我在自己的应用程序中解决它的方式.理想情况下,您应该使用StringBuilder而不是使用+来表示字符串.

    String inParenthesis = "(?";
    for(int i = 1;i < myList.size();i++) {
      inParenthesis += ", ?";
    }
    inParenthesis += ")";

    try(PreparedStatement statement = SQLite.connection.prepareStatement(
        String.format("UPDATE table SET value='WINNER' WHERE startTime=? AND name=? AND traderIdx=? AND someValue IN %s", inParenthesis))) {
      int x = 1;
      statement.setLong(x++, race.startTime);
      statement.setString(x++, race.name);
      statement.setInt(x++, traderIdx);

      for(String str : race.betFair.winners) {
        statement.setString(x++, str);
      }

      int effected = statement.executeUpdate();
    }

如果您决定稍后更改查询,则使用上面的x等变量而不是具体数字会有很大帮助.

我从来没有尝试过,但是.setArray()会做你想要的吗？

更新:显然不是.setArray似乎只适用于来自您从先前查询检索到的ARRAY列的java.sql.Array,或者带有ARRAY列的子查询.

我的解决方法是:

create or replace type split_tbl as table of varchar(32767);
/

create or replace function split
(
  p_list varchar2,
  p_del varchar2 := ','
) return split_tbl pipelined
is
  l_idx    pls_integer;
  l_list    varchar2(32767) := p_list;
  l_value    varchar2(32767);
begin
  loop
    l_idx := instr(l_list,p_del);
    if l_idx > 0 then
      pipe row(substr(l_list,1,l_idx-1));
      l_list := substr(l_list,l_idx+length(p_del));
    else
      pipe row(l_list);
      exit;
    end if;
  end loop;
  return;
end split;
/

现在,您可以使用一个变量来获取表中的某些值:

select * from table(split('one,two,three'))
  one
  two
  three

select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
  value1 AAA
  value2 BBB

因此,准备好的声明可以是:

  "select * from TABLE where COL in (select * from table(split(?)))"

问候,

哈维尔·伊瓦涅斯