改善sql字符串的可读性(3) - 多个可选Join 拼接优化

 

报表众多的系统，不仅仅是where子句存在拼接，join子句也可能需要拼接。还是看代码：

String sql=" select * from bill  ";

if (queryParams.pTypeId != null)

{

  dbHelper.AddParameter("@ptypeid", ptypeid);

  sqlWhere += " and  p.typeid like @ptypeid ";

sqlJoin += "  inner join ptype p on  a.ptypeid = p.id ";

}

if (queryParams.eTypeId != null)

{

  dbHelper.AddParameter("@etypeid", etypeid);

  sqlWhere += " and e.typeid like @etypeid ";

sqlJoin += "   inner join employee e on a.etypeid = e.id";

}

if (queryParams.kTypeId != null)

{

  dbHelper.AddParameter("@ktypeid", ktypeid);

   sqlWhere += " and k.typeid like @ktypeid ";

sqlJoin += "     left join stock k on a.ktypeid = k.id ";

}

新代码：

String sql=@"

select * from bill 

  inner join ptype p on  @enablePtypeJoin and  a.ptypeid = p.id and a.profileid = p.profileid

  inner join employee e on @enableEtypeJoin and a.etypeid = e.id and a.profileid = e.profileid

  left join stock k on @enableKtypeJoin and  a.ktypeid = k.id

  where

  and (@enablePtypeJoin and p.typeid like @ptypeid )

  and  (@enableEtypeJoin and e.typeid like @etypeid )

  and  (@enableKtypeJoin and p.typeid like @ktypeid )

";

  dbHelper.AddParameter("@ptypeid", ptypeid );

  dbHelper.AddParameter("@etypeid", etypeid);

  dbHelper.AddParameter("@ktypeid", ktypeid);

  dbHelper.AddParameter("@enablePtypeJoin",ptypeid != null);

  dbHelper.AddParameter("@enableKtypeJoin",ktypeid != null);

  dbHelper.AddParameter("@enableEtypeJoin",etypeid != null);

 

新代码的特点是采用了@EnableXXX型的bool值插入了join子句和Where子句内。

比如 (@enablePtypeJoin and p.typeid like @ptypeid )，当enablePtypeJoin 为false 的时候，p.typeid like @ptypeid就等于没有拼接进来一样，当enablePtypeJoin 为 true 的时候,@enablePtypeJoin=true也是没用的。从而通过这个@EnableXXX达到和拼接一样的效果。

 

通过 mysql 的explain可以看到：采用@enableXXX的方法可能会降低效能。不过需要实际测试才知道。因为，即使 @enablePtypeJoin=false,连接也还是发生了。同样的语句，在在sqlserver中，从执行计划上可以看出， @enablePtypeJoin=false的时候，join不会发生，故而不会降低效能。

 

新代码其实是伪代码，因为在sqlserver内，并没有bool类型。通常会用1=1 和1=0表达true/false,因此，

  dbHelper.AddParameter("@enablePtypeJoin",ptypeid != null);

必须做出修改，适应这个情况：

  dbHelper.AddParameter("@enablePtypeJoin",ptypeid != null?1:0);

对应的：

      and (@enablePtypeJoin and p.typeid like @ptypeid )

需要改成：

     and (1=@enablePtypeJoin and p.typeid like @ptypeid )

阅读效果稍微差了一点，带上比起原来的代码好要的多了。