最近有用到HSQL内存数据库,感觉轻巧,方便,只可惜有一定的局限性,毕竟是内存级的,这不,就遇到一个问题,上周五也去论坛上提了问,但是回答者寥寥无几(其实就两个),也许是我提的问题水平太低了,不能吸引大家眼球,呵呵…
先看问题描叙,下面的sql在Oracle中很显然是支持的:
from table1 a,table2 b,table3 c,table4 d
where a.emp = '100'
and a.type = b.type_id
and a.id = c.id(+)
and a.area(+) = d.area_id
and d.code = 10
但是在HSQL中不被支持,因为这个是Oracle语法,不是标准的SQL语法,标准的应该用左右连接来替代,如下:
from TABLE1 A RIGHT JOIN TABLE3 C ON A.ID = C.ID LEFT JOIN TABLE4 D ON A.AREA = D.AREA_ID,TABLE2 B
where A.EMP = '100'
AND A.TYPE = B.TYPE_ID
AND D.CODE = 10
问题就是这样。
今天总算是吧问题解决了,总体思路也不罗嗦了,直接上代码,理解有问题,或者认为程序有问题的,欢迎大家留言讨论,交流。
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
public class AnalysSQL {
public static void main(String args[]){
String fromString = "table1 a,table2 b,table3 c,table4 d";
String whereString = "a.emp = '100' and a.type = b.type_id and a.id = c.id(+) and a.area(+) = d.area_id and d.code = 10";
AnalysSQL test = new AnalysSQL();
String fromWhere = test.transCondition(fromString, whereString);
System.out.println(fromWhere);
}
/**
* 解析SQL中from和where后面的串
* @param fromString from 串
* @param whereCondition where 串
* @return from 后面的整串,含where关键字
*/
private String transCondition(String fromString,String whereCondition) {
fromString = fromString.toUpperCase().trim(); //from串转大写去空格
whereCondition = whereCondition.toUpperCase().trim();//where串转大写去空格
StringBuffer whereString = new StringBuffer(); //存放where条件
List<String> tableRelaList= new ArrayList<String>();//存放表的连接关系
String[] srcTables = fromString.split(","); //源表数组
List<String> srcTableList = new ArrayList<String>(Arrays.asList(srcTables));//源表数组转List集合
String[] whereTmp = whereCondition.split("AND");
String lastTable = null;
for (int i = 0; i < whereTmp.length; i++) { //where条件处理
whereTmp[i] = whereTmp[i].trim();
int index = whereTmp[i].indexOf("(+)"); //确定(+)位置
if (index == -1) {
if (whereString.length()==0) {
whereString.append(whereTmp[i]);
}else {
whereString.append(" AND "+whereTmp[i]);
}
}else {
int index_equ = whereTmp[i].indexOf("=");// 确定等号位置
String[] tmp1 = whereTmp[i].split("="); // 按 = 拆分
String left = tmp1[0].trim(); // = 左边
String right = tmp1[1].trim(); // = 右边
String table1 = left.split("\\.")[0];// = 左边部分按 . 拆分后,第一个位置为表1
String table2 = right.split("\\.")[0];// = 右边部分按 . 拆分后,第一个位置为表2
for (int j = 0; j < srcTables.length; j++) {//源表处理,源表集合中移除有连接关系表
srcTables[j] = srcTables[j].trim();
if (srcTables[j].lastIndexOf(table1) != -1) {
table1 = srcTables[j];
srcTableList.remove(srcTables[j]);
continue;
}
if (srcTables[j].lastIndexOf(table2) != -1) {
table2 = srcTables[j];
srcTableList.remove(srcTables[j]);
}
}
if (index<index_equ) { // (+) 在 = 左边,左关联
if (lastTable!=null&&lastTable.equals(table1)) {
tableRelaList.add(" LEFT JOIN "+table2+" ON "+whereTmp[i].replace("(+)", ""));
}else {
tableRelaList.add(table1+" LEFT JOIN "+table2+" ON "+whereTmp[i].replace("(+)", ""));
}
}else { // (+) 在 = 右边,右关联
if (lastTable!=null&&lastTable.equals(table1)) {
tableRelaList.add(" RIGHT JOIN "+table2+" ON "+ whereTmp[i].replace("(+)", ""));
}else {
tableRelaList.add(table1+" RIGHT JOIN "+table2+" ON "+ whereTmp[i].replace("(+)", ""));
}
}
lastTable = table1;
}
}
if (srcTableList!=null&&srcTableList.size()!=0) {
fromString =this.listToString(tableRelaList) + "," +this.listToString(srcTableList); //拼接from串
}else {
fromString =this.listToString(tableRelaList);
}
whereString = whereString.length()==0?whereString.append(" 1=1 "):whereString;
return fromString+" where "+whereString;
}
/**
* list转字符串
* @param list
* @return 转换后的字符串
*/
private String listToString(List<String> list) {
StringBuffer temp = new StringBuffer() ;
Iterator<String> iterator = list.iterator() ;
boolean flag = true; //开关控制,用于第一次
while (iterator.hasNext()) {
String tmp = iterator.next().trim();
if (flag) {
temp.append(tmp);
flag = false;
continue;
}
if (tmp.startsWith("LEFT")||tmp.startsWith("RIGHT")) {
temp.append(" "+tmp);
}else {
temp.append(", "+tmp);
}
}
return temp.toString();
}
}
很希望能和大家一起交流,大家可以随意发表自己的看法。个人觉得这段程序太冗余,不够精简。。。。