JDBC PreparedStatement 批量查询 in 的实现 方案


    我们经常会有这种业务需求,根据一个条件集合去查询一张表的数据,比如:
select * from all_element t where t.task_id in (List <taskids>);
    在java语言中,我们需要用到JDBC来和数据库打交道,那么在JDBC中该如何处理这种需求呢?我们可以有如下几种处理方式

方案一:写一个函数把参数集合转换成一个or 条件 或 in 条件的字符串,最后拼成一个sql

select * from all_element t where t.task_id in (123 ,456, 789);
或者是:
select from all_element t where t.task_id=123 or t.task_id= 456 t.task_id= 789;

    但是这样效率如何呢?我们知道Oracle对传过来的SQL是需要事先编译的,不过是Oracle有个缓存功能可以缓存编译好的SQL,但前提是传过来的SQL必须完全一致,很明显,如果按照以上方式的话,一旦taskid值变化,那么Oracle的缓存便无法利用。

方案二:使用预编译的PrepareStatement

    为了解决Oracle缓存无法利用问题,Jdbc提供了预编译的PrepareStatement,对于变化的参数可以用占位符 <?> 来代替,因此我们可以用占位符来减少Oracle编译次数。
        private static final String QUERY = "select * from all_element where taskId = ?";
          ps = con .prepareStatement(QUERY);
          for(String taskId : taskIds){
             ps.setInt(1, taskId);
             rs = ps .executeQuery();
          }
    这样做虽然可以很好的利用Oracle的缓存,但缺点也很明显,就是每一个Id都需要查询数据库一次,这样效率是极低的。

方案三:动态地创建PrepareStatement

    虽然变化的参数可以用占位符 <?> 来代替,然而遗憾的是Jdbc只提供了单一占位符功能即占位符不能是一个可迭代的集合。因此,对于我们传过来的集合参数,我们可以动态地创建一个PrepareStatement

    拼一个和集合大小相等数量占位符的SQL,再写一个循环来赋值每一个占位符,这样就可以解决taskId的值变化而导致Oracle重新编译SQL问题。
    private static void createQuery(List<String> taskIds) {
          String query = "select * from all_element t where t.task_id in (";
          StringBuilder queryBuilder = new StringBuilder(query);
           for ( int i = 0; i < taskIds.size(); i++) {
              queryBuilder.append( " ?");
               if (i != taskIds.size() - 1)
                   queryBuilder.append( ",");
          }
          queryBuilder.append( ")");
           ps = con .prepareStatement(query);
           for ( int i = 1; i <= taskIds.size(); i++) {
               ps.setInt(i, taskIds.get(i - 1));
          }
           rs = ps .executeQuery();
     }
    但是这么做还是存在一个问题,如果集合的值变化不会导致Oracle重新编译,但是如果集合的大小发生变化,相对应的SQL也就发生了变化,同样也会导致Oracle重新编译,那么该如何解决这个问题呢?

方案四:批量查询(减少查询次数并利用到Oracle缓存)

    批量查询兼顾了第二、第三种方案,其思想是预先定义好几个每次要查询参数的个数,然后把参数集合按这些定义好的值划分成小组。比如我们的前台传过来一个数量为75的taskId的集合,我预定义的批量查询参数的个数分别为:
    SINGLE_BATCH = 1;//注意:为了把参数集合完整划分,这个值为1的批量数是必须的
    SMALL_BATCH = 4;
    MEDIUM_BATCH = 11;
    LARGE_BATCH = 51;

    那么我们第一次会查询51条数据,还剩下24个没有查询,那么第二次批量查询11条数据,还剩下13条未查询,第三次再批量查询11条数据,最后还剩2条未查询,那么我们再分两批次,每批次仅查询一条,这样,最终一个75条的数据分5批次即可查询完成,减少了查询次数,而且还利用到了数据库缓存。附获取批量的算法:
     public static final int SINGLE_BATCH = 1; //注意:为了把参数集合完整划分,这个值为1的批量数是必须的
      public static final int SMALL_BATCH = 4;
      public static final int MEDIUM_BATCH = 11;
      public static final int LARGE_BATCH = 51;
      static int totalNumberOfValuesLeftToBatch=75;
      public static List<Integer>  getBatchSize( int totalNumberOfValuesLeftToBatch){
          List<Integer> batches= new ArrayList<Integer>();
           while ( totalNumberOfValuesLeftToBatch > 0 ) {
               int batchSize = SINGLE_BATCH;
               if ( totalNumberOfValuesLeftToBatch >= LARGE_BATCH ) {
                batchSize = LARGE_BATCH;
              } else if ( totalNumberOfValuesLeftToBatch >= MEDIUM_BATCH ) {
                batchSize = MEDIUM_BATCH;
              } else if ( totalNumberOfValuesLeftToBatch >= SMALL_BATCH ) {
                batchSize = SMALL_BATCH;
              }
              batches.add(batchSize);
              totalNumberOfValuesLeftToBatch -= batchSize;
              System. out.println(batchSize);
          }
           return batches;
     }







  

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/suifeng3051/article/details/24033237
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭