public String getSelectStr(String kind) {
try {
String strSelect = "select " + "\n";
Node xmlNode = xmlCon.selectSingleNode(xmlDoc, "//db_table");
NodeList nodelist = null;
Node tempNode = null;
if (kind.equals("all")) {
nodelist =
xmlCon.selectNodes(
xmlNode, "//db_table/logical_model/select_clause/logical_column[@dispflg='true']");
} else if (kind.equals("use_flg")) {
nodelist =
xmlCon.selectNodes(
xmlNode,
"//db_table/logical_model/select_clause/logical_column[@dispflg='true' and @use_flg='1']");
}
// System.out.println(nodelist.getLength());
for (int i = 0; i < nodelist.getLength(); i++) {
// Sortが効かないので、番号がついている属性を指定して、SelectSingleNodeする。
if (kind.equals("all")) {
tempNode = nodelist.item(i);
} else if (kind.equals("use_flg")) {
tempNode =
xmlCon.selectSingleNode(
xmlDoc,
"//db_table/logical_model/select_clause/logical_column[@use_order='"
+ i
+ "' and @dispflg='true' and @use_flg='1']");
}
// System.out.println(tempNode);
if (tempNode != null) { // 同じ名前が重なる場合はNullとなる。
if (strSelect.equals("select " + "\n")) {
strSelect +=
" "
+ xmlCon.selectSingleNode(tempNode, ".//sql").getFirstChild().getNodeValue();
strSelect +=
" as "
+ xmlCon.selectSingleNode(tempNode, ".//name").getFirstChild().getNodeValue()
+ "\n";
} else {
strSelect +=
" ,"
+ xmlCon.selectSingleNode(tempNode, ".//sql").getFirstChild().getNodeValue();
strSelect +=
" as "
+ xmlCon.selectSingleNode(tempNode, ".//name").getFirstChild().getNodeValue()
+ "\n";
}
}
}
return strSelect;
} catch (Exception e) {
log.error("exception in getSelectStr()：\n", e);
e.printStackTrace();
}
return null;
}
public Document readFile(String filepath) {
try {
xmlDoc = xmlCon.readFile(filepath);
return xmlDoc;
} catch (Exception e) {
log.error("exception in readFile()：\n", e);
e.printStackTrace();
}
return null;
}
public String getFromStr() {
try {
String strFrom = "from " + "\n";
Node xmlNode = xmlCon.selectSingleNode(xmlDoc, "//db_table");
NodeList nodelist = xmlCon.selectNodes(xmlNode, "//db_table");
for (int i = 0; i < nodelist.getLength(); i++) {
if (i == 0) {
strFrom += " " + ((Element) nodelist.item(i)).getAttribute("name") + "\n";
} else {
strFrom += " ," + ((Element) nodelist.item(i)).getAttribute("name") + "\n";
}
}
return strFrom;
} catch (Exception e) {
log.error("exception in getFromStr()：\n", e);
e.printStackTrace();
}
return null;
}
public String getJoinTableStr(Node node) {
try {
// 複数カラムJoin用のLoop
String strJoin = "";
String tableName1 = "";
String tableName2 = "";
Node tableNode1 = xmlCon.selectSingleNode(node, ".//table1");
tableName1 = ((Element) tableNode1).getAttribute("tablename");
Node tableNode2 = xmlCon.selectSingleNode(node, ".//table2");
tableName2 = ((Element) tableNode2).getAttribute("tablename");
NodeList columList1 = xmlCon.selectNodes(tableNode1, ".//join_column");
NodeList columList2 = xmlCon.selectNodes(tableNode2, ".//join_column");
for (int i = 0; i < columList1.getLength(); i++) { // 複数Table用のループ
if (i == 0) {
strJoin += "";
} else {
strJoin += "\n" + " and ";
}
strJoin +=
tableName1
+ "."
+ xmlCon.selectSingleNode(columList1.item(i), ".").getFirstChild().getNodeValue();
strJoin +=
" = "
+ tableName2
+ "."
+ xmlCon.selectSingleNode(columList2.item(i), ".").getFirstChild().getNodeValue();
}
return strJoin;
} catch (Exception e) {
log.error("exception in getJoinTableStr()：\n", e);
e.printStackTrace();
}
return null;
}
public String getJoinStr() {
try {
// String strJoin = "where 'mode'!='viewer' "+"\n";
String strJoin = "where 1=1 " + "\n";
Node xmlNode = xmlCon.selectSingleNode(xmlDoc, "//RDBModel");
NodeList nodelist = xmlCon.selectNodes(xmlNode, "//RDBModel/joins/join");
for (int i = 0; i < nodelist.getLength(); i++) { // 複数Table用のループ
// xmlCon.selectSingleNode(nodelist.item(i),"//table1");
if (i == 0) {
strJoin += " and " + getJoinTableStr(nodelist.item(i)) + "\n";
} else {
strJoin += " and " + getJoinTableStr(nodelist.item(i)) + "\n";
}
}
return strJoin;
} catch (Exception e) {
log.error("exception in getJoinStr()：\n", e);
e.printStackTrace();
}
return null;
}
public String getTableSQL(String xmlString) {
try {
xmlDoc = xmlCon.toXMLDocument(xmlString);
String sqlString = "";
sqlString += getSelectStr("all");
sqlString += getFromStr();
sqlString += getWhereStr("", "all");
sqlString += getGroupByStr("all");
sqlString += getOrderByStr("all");
sqlString += getLimitStr();
return sqlString;
} catch (Exception e) {
log.error("exception in getTableSQL()：\n", e);
e.printStackTrace();
}
return null;
}
public String getOrderByStr(String kind) {
try {
String strOrderBy = "order by " + "\n";
Node xmlNode = xmlCon.selectSingleNode(xmlDoc, "//db_table");
NodeList nodelist = null;
Node tempNode = null;
if (kind.equals("all")) {
nodelist =
xmlCon.selectNodes(
xmlNode,
"//db_table/logical_model/select_clause/logical_column[@groupbyflg='true']"); //
} else if (kind.equals("use_flg")) {
// nodelist =
// xmlCon.selectNodes(xmlNode,"//db_table/logical_model/select_clause/logical_column[@use_flg='1']");//@groupbyflg='true' and
nodelist =
xmlCon.selectNodes(
xmlNode,
"//db_table/logical_model/select_clause/logical_column[@groupbyflg='true' and @use_flg='1']"); //
}
if (nodelist.getLength() == 0) { // 無い場合は、何も返さない（FactTable用）
return "";
}
boolean first_flg = true;
int plusNum = 0; // Sort番号が飛び順になる場合。
for (int i = 0; i < nodelist.getLength(); i++) {
if (kind.equals("all")) {
tempNode = nodelist.item(i);
// Sortが効かないので、番号がついている属性を指定して、SelectSingleNodeする。
} else if (kind.equals("use_flg")) {
tempNode =
xmlCon.selectSingleNode(
xmlDoc,
"//db_table/logical_model/select_clause/logical_column[@use_order='"
+ (i + plusNum)
+ "' and @groupbyflg='true' and @use_flg='1']");
if (tempNode == null) {
plusNum++;
tempNode =
xmlCon.selectSingleNode(
xmlDoc,
"//db_table/logical_model/select_clause/logical_column[@use_order='"
+ (i + plusNum)
+ "' and @groupbyflg='true' and @use_flg='1']");
for (; plusNum < 1000 && tempNode == null; plusNum++) {
tempNode =
xmlCon.selectSingleNode(
xmlDoc,
"//db_table/logical_model/select_clause/logical_column[@use_order='"
+ (i + plusNum)
+ "' and @groupbyflg='true' and @use_flg='1']");
}
}
}
if (tempNode != null) {
if (first_flg == true) {
strOrderBy +=
" "
+ xmlCon.selectSingleNode(tempNode, ".//sql").getFirstChild().getNodeValue()
+ "\n";
first_flg = false;
} else {
strOrderBy +=
" ,"
+ xmlCon.selectSingleNode(tempNode, ".//sql").getFirstChild().getNodeValue()
+ "\n";
}
}
}
return strOrderBy;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public String getGroupByStr(String kind) {
try {
String strGroupBy = "group by " + "\n";
Node xmlNode = xmlCon.selectSingleNode(xmlDoc, "//db_table");
NodeList nodelist = null;
Node tempNode = null;
// Selectに属するものをまとめて取得（絞り込みは、下のSort処理部分で。）
if (kind.equals("all")) {
nodelist =
xmlCon.selectNodes(
xmlNode,
"//db_table/logical_model/select_clause/logical_column[@groupbyflg='true']"); //
} else if (kind.equals("use_flg")) {
// 修正
// nodelist =
// xmlCon.selectNodes(xmlNode,"//db_table/logical_model/select_clause/logical_column[@use_flg='1']");//@groupbyflg='true' and
nodelist =
xmlCon.selectNodes(
xmlNode,
"//db_table/logical_model/select_clause/logical_column[@groupbyflg='true' and @use_flg='1']"); //
}
// System.out.println(nodelist.getLength());
if (nodelist.getLength() == 0) { // 無い場合は、何も返さない（FactTable用）
return "";
}
boolean first_flg = true;
int plusNum = 0; // Sort番号が飛び順になる場合。
// System.out.println(nodelist.getLength());
for (int i = 0; i < nodelist.getLength(); i++) {
// System.out.println(xmlCon.toXMLText(nodelist.item(i)));
// Sortが効かないので、番号がついている属性を指定して、SelectSingleNodeする。
if (kind.equals("all")) {
tempNode = nodelist.item(i);
} else if (kind.equals("use_flg")) {
// System.out.println("Null"+i);
// System.out.println("Plus"+plusNum);
tempNode =
xmlCon.selectSingleNode(
xmlDoc,
"//db_table/logical_model/select_clause/logical_column[@use_order='"
+ (i + plusNum)
+ "' and @groupbyflg='true' and @use_flg='1']");
if (tempNode == null) {
plusNum++;
tempNode =
xmlCon.selectSingleNode(
xmlDoc,
"//db_table/logical_model/select_clause/logical_column[@use_order='"
+ (i + plusNum)
+ "' and @groupbyflg='true' and @use_flg='1']");
for (; plusNum < 1000 && tempNode == null; plusNum++) {
tempNode =
xmlCon.selectSingleNode(
xmlDoc,
"//db_table/logical_model/select_clause/logical_column[@use_order='"
+ (i + plusNum)
+ "' and @groupbyflg='true' and @use_flg='1']");
}
}
// System.out.println(plusNum+i);
// System.out.println(xmlCon.toXMLText(tempNode));
// System.out.println("後Null"+i);
// System.out.println("後Plus"+plusNum);
}
if (tempNode != null) {
if (first_flg == true) {
strGroupBy +=
" "
+ xmlCon.selectSingleNode(tempNode, ".//sql").getFirstChild().getNodeValue()
+ "\n";
first_flg = false;
} else {
strGroupBy +=
" ,"
+ xmlCon.selectSingleNode(tempNode, ".//sql").getFirstChild().getNodeValue()
+ "\n";
}
// System.out.println(strGroupBy);
}
}
return strGroupBy;
} catch (Exception e) {
log.error("exception in getOrderByStr()：\n", e);
e.printStackTrace();
}
return null;
}
public String getWhereStr(String whereStr, String kind) {
try {
String strWhere = "";
if (whereStr == "") {
// strWhere = "where 'mode'!='viewer' "+"\n";
strWhere = "where 1=1 " + "\n";
} else {
strWhere = whereStr;
}
Node xmlNode = xmlCon.selectSingleNode(xmlDoc, "//db_table");
NodeList nodelist = null;
if (kind.equals("all")) {
nodelist =
xmlCon.selectNodes(xmlNode, "//db_table/logical_model/where_clause/logical_condition");
} else if (kind.equals("use_flg")) {
nodelist =
xmlCon.selectNodes(
xmlNode, "//db_table/logical_model/where_clause/logical_condition[@use_flg='1']");
}
// 条件が１つもなかったら、Whereを削除して、Return
if (nodelist.getLength() == 0) {
return whereStr;
}
// @が入っている場合は、サンプルSQLでは、コメントにする。
// 実際に発行する場合は、ここを置き換えて条件を入れる。
String sqlStr = "";
int whereCnt = 0;
for (int i = 0; i < nodelist.getLength(); i++) {
if (i == 0) {
if (whereStr == "") {
sqlStr =
" and "
+ xmlCon
.selectSingleNode(nodelist.item(i), ".//sql")
.getFirstChild()
.getNodeValue()
+ "\n";
} else {
sqlStr =
" and "
+ xmlCon
.selectSingleNode(nodelist.item(i), ".//sql")
.getFirstChild()
.getNodeValue()
+ "\n";
}
if (sqlStr.indexOf("@") == -1) {
strWhere += sqlStr;
whereCnt++;
} else {
strWhere += "--" + sqlStr;
}
} else {
sqlStr =
" and "
+ xmlCon
.selectSingleNode(nodelist.item(i), ".//sql")
.getFirstChild()
.getNodeValue()
+ "\n";
if (sqlStr.indexOf("@") == -1) {
strWhere += sqlStr;
whereCnt++;
} else {
strWhere += "--" + sqlStr;
}
}
}
// 全部@が入っている場合は、Whereもコメントにする。
// if (whereCnt==0){
// strWhere = "-- " + strWhere;
// }
return strWhere;
} catch (Exception e) {
log.error("exception in getWhereStr()：\n", e);
e.printStackTrace();
}
return null;
}