拼装getTotalCount的sql

本文介绍了如何在v1.3中拼装用于获取总数的SQL查询。通过结合Java代码,展示了如何构建动态SQL来统计特定数据集的数量。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

v1.3

package testsql;

/**
* Created by dinghw on 2015/5/4.
*
* @desc 优化了第一版包含group by查询效率低的问题
* 此类只支持特定的场景使用,
* 并且存在写法上的要求:group by 后的参数必须不能存在空格,并且最后一个参数后需要保留一个空格,
* 例:group by t.app_id,t.channel_id (channel_id后必须得有空格)
* <p/>
* v1.3 修复group by 后面存在order by时sql拼接错误
* 修复两层select查询 sql拼接错误
*/
public class SqlUtil {
private static final String SELECT = "SELECT ";
private static final String FROM = " FROM ";
private static final String WHERE = " WHERE ";
private static final String GROUP = " GROUP BY ";
private static final String ORDER = " ORDER BY ";
private static final String AS = "\\) AS ";

public static StringBuffer getCountSql(String sql) throws Exception {
StringBuffer countSql = new StringBuffer();
sql = sql.replaceAll(FROM, " from ").replaceAll(GROUP, " group by ").replaceAll(WHERE, " where ").replaceAll(ORDER, " order by ").replaceAll(AS, ") as ");
int indexFrom = sql.indexOf(" from ");
if (indexFrom > 0) {
//判断有几层from,并获取内层的sql
sql = getInnerFrom(sql);
indexFrom = sql.indexOf(" from ");
int indexGroupBy = sql.indexOf(" group by ");
if (indexGroupBy > 0) {
countSql.append("select count(distinct ");
countSql.append(getDistinctParameter(sql));
countSql.append(") num");
countSql.append(" from ").append(sql.substring(indexFrom + 6, indexGroupBy));
} else {
countSql.append("select count(*) num ");
countSql.append(sql.substring(sql.indexOf(" from ")));
}
} else {
throw new Exception("error sql");
}
System.out.println("getTotalCount sql:" + countSql.toString());
return countSql;
}

private static String getInnerFrom(String sql) {
int indexFrom = sql.indexOf(" from ");
int lastFrom = sql.lastIndexOf(" from ");
if (indexFrom < lastFrom) {
sql = sql.substring(lastFrom, sql.indexOf(") as "));
}
return sql;
}

private static String getDistinctParameter(String sql) {
int indexGroupBy = sql.indexOf(" group by ");
int indexIterator = sql.indexOf(" ", indexGroupBy + 10);
return sql.substring(indexGroupBy + 10, indexIterator);
}

}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值