JAVA中SQL动态拼接（笔记、（11））

最新推荐文章于 2024-04-26 11:55:15 发布

zhangjiapengdg

最新推荐文章于 2024-04-26 11:55:15 发布

阅读量1.5k

点赞数 1

分类专栏： oracle 文章标签： sql java

本文链接：https://blog.csdn.net/weixin_58033123/article/details/126468241

版权

oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、StringBuilder拼接和SQL拼接

1、StringBuilder拼接


public static void main(String[] args) {
   String dateStr = "123";
   StringBuilder sb = new StringBuilder();
   sb.append(" SELECT DISTINCT SUBSTR( VALUE, INSTR( VALUE, '-', - 1, 1 ) + 1 ) +1 AS sno ");
   sb.append(" FROM ERM_RECORD_DATA ");
   sb.append(" JOIN META_ATTRIBUTE ON META_ATTRIBUTE.ID=ERM_RECORD_DATA.ATTRIBUTE_ID ");
   sb.append(" WHERE META_ATTRIBUTE.CODE = 'DADH' AND VALUE IS NOT NULL");
   if(dateStr!=null){
      sb.append(" AND VALUE LIKE '%"+dateStr+"%' ");
   }
   StringBuilder sb2 = new StringBuilder();
   sb2.append("SELECT MAX(sno) FROM ( "+sb +")");
   System.out.println("sb2::" + sb2.toString());

   //sb2::SELECT MAX(sno) FROM (  SELECT DISTINCT SUBSTR( VALUE, INSTR( VALUE, '-', - 1, 1 ) + 1 ) +1 AS sno  FROM ERM_RECORD_DATA  JOIN META_ATTRIBUTE ON META_ATTRIBUTE.ID=ERM_RECORD_DATA.ATTRIBUTE_ID  WHERE META_ATTRIBUTE.CODE = 'DADH' AND VALUE IS NOT NULL AND VALUE LIKE '%123%' )
}

2、SQL拼接


public static void main(String[] args) {

 SQL sql1 = new SQL();
		sql1.SELECT("BID")
		    .FROM("RECORD_BUSINESS")
		    .WHERE("RID=#{rid}");
		SQL sql = new SQL();
		sql.SELECT("REMARK")
		   .FROM("BIZ_PROCESS")
		   .WHERE("id in (" + sql1 + ") AND to_char(MODIFIED_DATE,'yyyyMMddhh24mi')  LIKE CONCAT(#{time}, '%') AND REMARK is NOT NULL AND ROWNUM=1");

   //sql::SELECT REMARK FROM BIZ_PROCESS WHERE (id in (SELECT BID FROM RECORD_BUSINESS
    WHERE (RID=#{rid})) AND to_char(MODIFIED_DATE,'yyyyMMddhh24mi')  LIKE CONCAT(#{time},         '%') AND REMARK is NOT NULL AND ROWNUM=1)

}