前几天进行了一些功能的Oracle转Mysql的常用SQL处理,在这里记录一下。
Oracle:
1."||"字符串拼接函数的使用(eg:CODE||' '||NAME)
2.START WITH 函数的使用,一般用于将查询编码表的结果集作为IN的查询条件
eg:
result.append("ZJXZ_DM IN ( SELECT ID FROM BDA_SZ_D_ZJXZ WHERE ND = ? START WITH ID IN ( ");
result.append(sortValueList((String) param.get("ZJXZ_DM")));
result.append(" ) CONNECT BY PRIOR ID = P_ID ) ");
3.ROWNUM 分页的实现
4.默认转换为的最后结果字段为大写字母字段
MySQL解决办法
1.使用CONCAT进行多个字符的连接。
eg:CONCAT(name,class,dept)
2.自己封装对应的SQL拼接转换函数。
eg:
/** * 替代Oracle 的 START WITH * * @param ad_code * @return * @throws Exception */ private String getSqlParmasAdCode(String ad_code,String tableName) throws Exception { String sql = "SELECT CODE ID,P_ID FROM '".concat(tableName); List delList = bgdDataSource.findBySql(sql); TreeToolUtil treeUtil = new TreeToolUtil(delList); treeUtil.setParentId("P_ID"); treeUtil.setId("ID"); List<Map> sortList = treeUtil.getSortList(ad_code, ","); // 将查询出的节点值转换为集合 StringBuffer whereSb = new StringBuffer(); for (Map map : sortList) { whereSb.append("'"); whereSb.append(map.get("ID").toString()); whereSb.append("',"); } // 去掉最后一个逗号 whereSb.deleteCharAt(whereSb.length() - 1); return whereSb.toString(); }
//使用逗号拆分字符串并返回
public static String splitStringJustComma(String str) throws Exception {
if (str != null && !"".equals(str) && !"undefined".equals(str)) {
String[] strArrays = str.split(",");
String sznew = "";
for (int i = 0; i < strArrays.length; i++) {
if (strArrays.length == 1) {
sznew += strArrays[i] ;
} else {
if (i < strArrays.length - 1) {
sznew += strArrays[i] + ",";
} else {
sznew += strArrays[i] ;
}
}
}
str = sznew;
}
return str;
}
具体SQL上这样拼接:
sql.append(" AND GNFL_DM IN ( '")
.append(getSqlParmasAdCode(splitStringJustComma(params.get("zckm").toString()), "BDA_SZ_D_GNFL"))
.append("') ");
3.LIMIT实现MYSQL分页
dataSql.append(" SELECT * ");
dataSql.append(sql);
dataSql.append(" LIMIT ?,? ");
4.小写转大写
字段后起别名eg:SELECT NAME \"NAME\" FROM TABLE