java中写长串sql语句的示例

String str1 = "SELECT tb1.AGENCYNAME, tb2.TODAYINSTALL, tb3.TODAYTRANS, trim(nvl(to_char(tb3.TODAYPRICE, 'L999,999,999,990.99'), '￥0.00')) as TODAYPRICE,trim(nvl(to_char(tb3.TODAYPAYMENT, 'L999,999,999,990.99'), '￥0.00')) as TODAYPAYMENT "

+ ", tb1.TOTALINSTALL, tb4.TOTALTRANS,trim(nvl(to_char(tb4.TOTALPRICE, 'L999,999,999,990.99'), '￥0.00')) as TOTALPRICE,trim(nvl(to_char(tb4.TOTALPAYMENT, 'L999,999,999,990.99'), '￥0.00')) as TOTALPAYMENT "

+ "FROM (SELECT t2.me_name AS AGENCYNAME, COUNT(t1.MERCHNO) AS TOTALINSTALL FROM scode_install t1 RIGHT JOIN (SELECT expand_mechanism.id, expand_mechanism.me_name FROM expand_mechanism UNION ALL "

+ "SELECT scode_expand_sub.id, scode_expand_sub.me_name FROM SCODE_EXPAND_SUB UNION ALL SELECT scode_expand.id, scode_expand.me_name FROM SCODE_EXPAND) t2 ON t2.ID = t1.tzjg ";

String str2= " GROUP BY t2.me_name) tb1 INNER JOIN (SELECT t2.me_name AS AGENCYNAME, COUNT(t1.MERCHNO) AS TODAYINSTALL FROM scode_install t1 RIGHT JOIN (SELECT expand_mechanism.id, expand_mechanism.me_name FROM expand_mechanism "

+ "UNION ALL SELECT scode_expand_sub.id, scode_expand_sub.me_name FROM SCODE_EXPAND_SUB UNION ALL SELECT scode_expand.id, scode_expand.me_name FROM SCODE_EXPAND) t2 ON t2.ID = t1.tzjg AND t1.installdate = '"

+ curDate

+ "' "

+ " GROUP BY t2.me_name) tb2 ON tb1.AGENCYNAME = tb2.AGENCYNAME INNER JOIN (SELECT t3.me_name AS AGENCYNAME, COUNT(t2.amt) AS TODAYTRANS, SUM(t2.amt) AS TODAYPRICE, SUM(t2.PAYAMT-(t2.amt*t1.fee/100)) AS TODAYPAYMENT FROM SCODE_INSTALL t1 "

+ "RIGHT JOIN (SELECT LMERCHNO, PAYAMT, amt FROM scp_scode_trans WHERE status = '00' AND settledate = '"

+ curDate

+ "') t2 ON t1.MERCHNO = t2.LMERCHNO RIGHT JOIN (SELECT expand_mechanism.id, expand_mechanism.me_name FROM expand_mechanism "

+ "UNION ALL SELECT scode_expand_sub.id, scode_expand_sub.me_name FROM SCODE_EXPAND_SUB UNION ALL SELECT scode_expand.id, scode_expand.me_name FROM SCODE_EXPAND) t3 ON t3.id = t1.tzjg GROUP BY t3.me_name) tb3 ON tb2.AGENCYNAME = tb3.AGENCYNAME "

+ "INNER JOIN (SELECT t3.me_name AS AGENCYNAME, COUNT(t2.amt) AS TOTALTRANS, SUM(t2.amt) AS TOTALPRICE, SUM(t2.PAYAMT-(t2.amt*t1.fee/100)) AS TOTALPAYMENT FROM SCODE_INSTALL t1 RIGHT JOIN (SELECT LMERCHNO, PAYAMT, amt FROM scp_scode_trans WHERE status = '00' ";

String str3=") t2 ON t1.MERCHNO = t2.LMERCHNO RIGHT JOIN (SELECT expand_mechanism.id, expand_mechanism.me_name FROM expand_mechanism UNION ALL SELECT scode_expand_sub.id, scode_expand_sub.me_name FROM SCODE_EXPAND_SUB UNION ALL SELECT scode_expand.id, scode_expand.me_name "

+ "FROM SCODE_EXPAND) t3 ON t3.id = t1.tzjg GROUP BY t3.me_name) tb4 ON tb3.AGENCYNAME = tb4.AGENCYNAME ";

if (null != lastStart && !"".equals(lastStart)) {

str1+="AND t1.installdate >= '"+lastStart+"' ";

str2+="AND settledate >= '"+lastStart+"' ";

}

if (null != lastEnd && !"".equals(lastEnd)) {

str1+="and t1.installdate <='"+lastEnd+"' ";

str2+="AND settledate <= '"+lastEnd+"' ";

}

if (agency != null && !"".equals(agency)) {

str3 += "where tb1.AGENCYNAME like '%" + agency

+ "%' ORDER BY TODAYINSTALL DESC";

} else {

str3 += "ORDER BY TODAYINSTALL DESC";

}

String str = str1+str2+str3;

在JAVA中书写上面这样的SQL语句时，要注意在每一行的语句末尾加上一个空格或在每一行的首位加上一个空格，这样可以防止拼接出来的字符串出现问题。