如何动态拼接affiliate_earnings_breakdown_201010表中的最后一个字符串，即"201010“

Question

如何动态拼接affiliate_breakdown_201010表中的最后一个字符串，即"201010“。

因为有许多类似的表需要动态检索，因为我不能硬编码表名，因为只有最后一个字符串不同，表字符串的其余部分对于所有表都是通用的。

我想检索过去90天的记录计数，如下所示：

String queryString = "select count(*) from ("+

" (select a.keyword_id  from affiliate_breakdown_201010 a where a.keyword_id = "+keywordId+" and a.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())"+ 

" union all"+ 

" (select i.keyword_id  from affiliate_breakdown_201009 i where i.keyword_id = "+keywordId+" and i.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())"+

" union all"+ 

"(select i.keyword_id  from affiliate_breakdown_201008 i where i.keyword_id = "+keywordId+" and i.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())"+

" ) as s";

Answer 1

第一:不要像那样把你的keywordId注入到你的SQL语句中，因为这很容易导致SQL Injection vulnerability。请改用PreparedStatement。

不幸的是，PreparedStatement不适用于表名(主要是因为数据库通常不包含动态表名，像您这样做是相当不寻常的)。在这种情况下，您需要使用“正常”字符串连接。

Answer 2

java字符串的连接是使用运算符+完成的。您已经在您的语句中多次这样做了。

因此，实现获取日期(月)并创建查询的循环。类似于以下内容：

int year = 2010;
StringBuilder query = new StringBuilder("select count(*) from (");
for (int i = 1;  i <=12;  i++) {
    String suffix = "" + year + (month < 10 ? "0" : "") +  month;
    String tableName = "affiliate_earnings_breakdown_" + suffix;
    String alias = "a" + i;
    query.append("(select a.keyword_id  from " + tableName + " " + alias + " where a.keyword_id = "+keywordId+" and a.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())");
    if (i < 12) {
        query.append(" union all");
    }
}

query.append(") as s");

请注意，我没有验证代码是否真的可以工作并创建正确的SQL语句。我把这个任务留给你了。我相信这个例子是一个很好的开始，会对你有所帮助。

Answer 3

注意：你需要3个联盟4个月(目前在获得90天之前+3个联盟)。

这可能是可以优化的！

// import java.util.Calendar;
Calendar cal = Calendar.getInstance();
int cur = cal.get(Calendar.YEAR) * 100 + cal.get(Calendar.MONTH) + 1;
cal = cal.add(Calendar.MONTH,-1);
int cur_minus_1 = cal.get(Calendar.YEAR) * 100 + cal.get(Calendar.MONTH) + 1;
cal = cal.add(Calendar.MONTH,-1);
int cur_minus_2 = cal.get(Calendar.YEAR) * 100 + cal.get(Calendar.MONTH) + 1;
cal = cal.add(Calendar.MONTH,-1);
int cur_minus_3 = cal.get(Calendar.YEAR) * 100 + cal.get(Calendar.MONTH) + 1;

String queryString = "select count(*) from ("+

" (select a.keyword_id  from affiliate_breakdown_" + cur + " a where a.keyword_id = "+keywordId+" and a.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())"+ 

" union all"+ 

" (select i.keyword_id  from affiliate_breakdown_" + cur_minus_1 + " i where i.keyword_id = "+keywordId+" and i.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())"+

" union all"+ 

"(select i.keyword_id  from affiliate_breakdown_" + cur_minus_2 + " i where i.keyword_id = "+keywordId+" and i.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())"+

" union all"+ 

"(select i.keyword_id  from affiliate_breakdown_" + cur_minus_3 + " i where i.keyword_id = "+keywordId+" and i.summary_date BETWEEN DATE_SUB(CURDATE(),INTERVAL "+days+" DAY) and CURDATE())"+

" ) as s";