xml.xsd：
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://xml.netbeans.org/schema/newXMLSchema"
xmlns:tns="http://xml.netbeans.org/schema/newXMLSchema"
elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence>
<!-- 所有要查询的列，按顺序排列-->
<xsd:element name="field" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="table" type="xsd:string" use="required"/>
<xsd:attribute name="column" type="xsd:string" use="required"/>
<xsd:attribute name="to_char" type="xsd:string"/>
<xsd:attribute name="to_date" type="xsd:string"/>
<xsd:attribute name="to_number" type="xsd:string"/>
<xsd:attribute name="lpad" type="xsd:string"/>
<xsd:attribute name="rpad" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
<!-- AND条件，可选-->
<xsd:element name="and" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="left">
<xsd:complexType>
<xsd:all>
<xsd:element name="leftField">
<xsd:complexType>
<xsd:attribute name="table" type="xsd:string" use="required"/>
<xsd:attribute name="column" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
</xsd:element>
<xsd:element name="exp">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:enumeration value="eq" />
<xsd:enumeration value="gt" />
<xsd:enumeration value="lt" />
<xsd:enumeration value="in" />
<xsd:enumeration value="not in" />
<xsd:enumeration value="like" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="right">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rightField">
<xsd:complexType>
<xsd:attribute name="table" type="xsd:string" use="required"/>
<xsd:attribute name="column" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="rightValue"/>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<!-- OR条件，可选-->
<xsd:element name="or" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="left">
<xsd:complexType>
<xsd:all>
<xsd:element name="leftField">
<xsd:complexType>
<xsd:attribute name="table" type="xsd:string" use="required"/>
<xsd:attribute name="column" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
</xsd:element>
<xsd:element name="exp">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:enumeration value="eq" />
<xsd:enumeration value="gt" />
<xsd:enumeration value="lt" />
<xsd:enumeration value="in" />
<xsd:enumeration value="not in" />
<xsd:enumeration value="like" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="right">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rightField">
<xsd:complexType>
<xsd:attribute name="table" type="xsd:string" use="required"/>
<xsd:attribute name="column" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="rightValue"/>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="order" minOccurs="0" maxOccurs="1">
<xsd:complexType>
<xsd:sequence>
<!-- <xsd:choice> -->
<xsd:element name="column" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="order" />
</xsd:sequence>
<xsd:attribute name="table" type="xsd:string" use="required"/>
<xsd:attribute name="column" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
<!-- </xsd:choice> -->
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="update">
<xsd:complexType>
<xsd:attribute name="table" type="xsd:string" use="required" />
<xsd:attribute name="column" type="xsd:string" use="required" />
<xsd:attribute name="value" type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>
<xsd:element name="id">
<xsd:complexType>
<xsd:attribute name="table" type="xsd:string" use="required" />
<xsd:attribute name="column" type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
newXMLDocument.xml：
<?xml version="1.0" encoding="UTF-8"?>
<!--
Document : newXMLDocument.xml
Created on : 2008年6月19日, 下午2:10
Author : Administrator
Description:
Purpose of the document follows.
-->
<root xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xmlns='http://xml.netbeans.org/schema/newXMLSchema'
xsi:schemaLocation='http://xml.netbeans.org/schema/newXMLSchema xml.xsd'>
<field table="DELIVERY" column="DELIVERY_NO" rpad="20"/>
<field table="transaction" column="trans_time" />
<field table="transaction" column="transact_type" lpad="16" />
<field table="transaction" column="card_nbr" lpad="16" />
<field table="DELIVERY" column="merchant_id" rpad="16"/>
<field table="DELIVERY" column="product_id" rpad="16" />
<and>
<left>
<leftField table="transaction" column="trans_no" />
</left>
<exp>eq</exp>
<right>
<rightField table="DELIVERY" column="trans_no" />
</right>
</and>
<and>
<left>
<leftField table="DELIVERY" column="DELIVERY_STATUS" />
</left>
<exp>eq</exp>
<right>
<rightValue>2</rightValue>
</right>
</and>
<order>
<column table="transaction" column="trans_no">
<order>desc</order>
</column>
<column table="transaction" column="trans_no">
<order>asc</order>
</column>
</order>
<update table="DELIVERY" column="DELIVERY_STATUS" value="6" />
<id table="DELIVERY" column="DELIVERY_NO" />
</root>
Main类文件：
package javaapplication1;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
public class Main {
public static void main(String[] args) throws IOException, WriteException {
String fileName="D:/workspace/JavaApplication1/src/javaapplication1/newXMLDocument.xml";
String query=getSql(fileName);
System.out.println(query);
Vector v = getResult(query);
if(v==null){
System.out.println("00000");
}
//outExcel(v);
//outTxt(v);
//outCsv(v);
String ids = getIds(v);
//String updateSql=getUpdate(fileName,ids);
//System.out.println(updateSql);
//int i = executeUpdate(updateSql);
//System.out.println("**********"+i);
//test("/t");
//Vector t = new Vector();
//t.add(" 1");
// t.add(" 12");
//t.add(" 123");
//for(int i=0;i<t.size();i++){
//System.out.println(t.elementAt(i));
//}
// String sql ="select distinct(merchant_id) from delivery t where t.delivery_no in ("+ids+")";
// System.out.println(sql);
// Vector merchants = getResult(sql);
// System.out.println(merchants.elementAt(1).toString());
// for(int i=0;i<merchants.size();i++){
// Vector tmp = (Vector)merchants.elementAt(i);
// System.out.println(tmp.get(0).toString());
// }
String sql2 = "select t1.merchant_id,sum(t2.total_amt) from delivery t1,transaction t2 where t1.trans_no=t2.trans_no and t1.delivery_no in (" +
ids+ ") group by t1.merchant_id";
System.out.println(sql2);
Vector tmp = getResult(sql2);
for(int j=0;j<tmp.size();j++){
Vector t1 = (Vector)tmp.elementAt(j);
for(int k=0;k<t1.size();k++){
System.out.print(t1.get(k).toString());
System.out.print(",");
}
System.out.println();
}
}
private static String getSql(String fileName){
SAXReader reader = new SAXReader();
Document doc = null;// 声明文档对象
try {
doc = reader.read(new File(fileName));// 读取XML文档
} catch (DocumentException e) {
e.printStackTrace();
}
String query = "select ";
Element queryInfo = doc.getRootElement();// 声明跟元素
int j=0;
String tables="";
HashSet hst = new HashSet();
for (Iterator i = queryInfo.elementIterator("field"); i.hasNext();) {
Element field = (Element) i.next();
String table=field.attributeValue("table");
String column=field.attributeValue("column");
String toChar=field.attributeValue("to_char");
String toDate=field.attributeValue("to_date");
String toNumber=field.attributeValue("to_number");
String lpad=field.attributeValue("lpad");
String rpad=field.attributeValue("rpad");
String tmpColumn=table+"."+column;
String tmp=(j==0)?"":",";//如果是第一列，不加逗号；否则多列查询要加逗号
if(toChar!=null){
if(lpad!=null){
query += tmp+"lpad(to_char("+tmpColumn+",'"+toChar+"'),"+lpad+")";
}else if(rpad!=null){
query += tmp+"rpad(to_char("+tmpColumn+",'"+toChar+"'),"+rpad+")";
}else query += tmp+"to_char("+table+"."+column+",'"+toChar+"')";
}else if(toDate!=null){
if(lpad!=null){
query += tmp+"lpad(to_date("+tmpColumn+",'"+toDate+"'),"+lpad+")";
}else if(rpad!=null){
query += tmp+"rpad(to_date("+tmpColumn+",'"+toDate+"'),"+rpad+")";
}else{
query += tmp+"to_date("+table+"."+column+",'"+toDate+"')";
}
}else if(toNumber!=null){
if(lpad!=null){
query += tmp+"lpad(to_number("+tmpColumn+",'"+toNumber+"'),"+lpad+")";
}else if(rpad!=null){
query += tmp+"rpad(to_number("+tmpColumn+",'"+toNumber+"'),"+rpad+")";
}else{
query += tmp+"to_number("+table+"."+column+",'"+toNumber+"')";
}
}else if(toChar==null && toDate==null && toNumber==null){
if(lpad!=null){
query += tmp+"lpad("+tmpColumn+","+lpad+")";
}else if(rpad!=null){
query += tmp+"rpad("+tmpColumn+","+rpad+")";
}else{
query += tmp+tmpColumn;
}
}
hst.add(table.toLowerCase());
j=j+1;
}
int d =0;
for(Iterator tmpI=hst.iterator();tmpI.hasNext();){
String tab = tmpI.next().toString();
if(d==0){
tables += tab;
}else{
tables += ","+tab;
}
d++;
}
query += " from " + tables;
String where=" where 1=1 ";
if(queryInfo.elementIterator("and").hasNext()){
where += getRightExp(queryInfo,"and");
}
if(queryInfo.elementIterator("or").hasNext()){
where += getRightExp(queryInfo,"or");
}
if(queryInfo.elementIterator("order").hasNext()){
where += getOrder(queryInfo);
}
query += where;
return query;
}
private static String getExpString(String exp){
if(exp.equalsIgnoreCase("eq")){
return " = ";
}else if(exp.equalsIgnoreCase("gt")){
return " > ";
}else if(exp.equalsIgnoreCase("lt")){
return " < ";
}else if(exp.equalsIgnoreCase("in")){
return " in ";
}else if(exp.equalsIgnoreCase("not in")){
return " not in ";
}else{
return " like ";
}
}
private static String getOrder(Element queryInfo){
String orders=" order by ";
for(Iterator i=queryInfo.elementIterator("order");i.hasNext();){
Element order =(Element)i.next();
int k=0;
for(Iterator o=order.elementIterator("column");o.hasNext();){
Element tmpOrder=(Element)o.next();
String orderTable=tmpOrder.attributeValue("table");
String orderColumn=tmpOrder.attributeValue("column");
String asc=tmpOrder.element("order").getText();
if(k>0){
orders += ",";
}
orders += orderTable+"."+orderColumn +" " + asc + " ";
k++;
}
}
return orders;
}
private static String getRightExp(Element queryInfo,String condition){
String where=" ";
for (Iterator i = queryInfo.elementIterator(condition); i.hasNext();){
Element and = (Element) i.next();
String leftTable = and.element("left").element("leftField").attributeValue("table");
String leftColumn = and.element("left").element("leftField").attributeValue("column");
String leftExp = leftTable + "." + leftColumn;
String exp = and.element("exp").getText();
String expString = getExpString(exp);
List list = and.element("right").elements();
String childName = "";
String rightExp = "";
for (Iterator test=list.iterator();test.hasNext();){
Element e=(Element)test.next();
childName=e.getName();
}
if(childName.equalsIgnoreCase("rightField")){
String rightTable = and.element("right").element("rightField").attributeValue("table");
String rightColumn = and.element("right").element("rightField").attributeValue("column");
rightExp =rightTable +"."+rightColumn;
}
if(childName.equalsIgnoreCase("rightValue")){
rightExp=and.element("right").elementText("rightValue");
}
where += " "+condition +" "+leftExp+" " + expString + " " + rightExp;
}
return where;
}
private static Vector getResult(String sql){
try {
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);// 强制加载驱动类,隐士的注册驱动
String url = "jdbc:oracle:thin:@128.32.115.1:1521:CISSDEV";
String username = "oracle";
String password = "oracle";
Connection con = DriverManager.getConnection(url, username, password);
Statement stmt = (Statement) con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// if(!rs.next()){
// return null;
// }
ResultSetMetaData rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();
Vector v = new Vector();
int t=0;
while (rs.next()) {
Vector columns = new Vector(numCols);
for (int i = 1; i <= numCols; i++) {
String RR1 = rs.getString(i);
if (RR1 == null) {
RR1 = "";
}
//RR1 = RR1.trim();
columns.addElement(RR1);
}
v.addElement(columns);
}
con.close();
return v;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
private static void outExcel(Vector v) throws IOException, WriteException{
WritableWorkbook wwb = null;
wwb = Workbook.createWorkbook(new File("c:/Test.xls"));
WritableSheet ws = wwb.createSheet("sheet1", 0);
for(int i=0;i<v.size();i++){
Vector tmpVec = (Vector)v.elementAt(i);
for(int j=0;j<tmpVec.size();j++){
//这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
Label labelC = new Label(j, i, tmpVec.elementAt(j).toString());
ws.addCell(labelC);
}
}
wwb.write();
wwb.close();
}
private static void outTxt(Vector v){
FileWriter fw = null;
try {
fw = new FileWriter("C:/FileTest.txt");
for (int i = 0; i < v.size(); i++) {
Vector tmpVec = (Vector)v.elementAt(i);
for(int j=0;j<tmpVec.size();j++){
fw.write(tmpVec.elementAt(j).toString());
fw.write("/t");
}
fw.write("/r/n");
}
fw.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static String getIds(Vector v){
StringBuffer sb = new StringBuffer();
int j=0;
for(int i=0;i<v.size();i++){
Vector tmpV =(Vector)v.elementAt(i);
String s = (String)tmpV.elementAt(0);
if(j>0){
sb.append(",");
}
sb.append(s.trim());
j++;
}
return sb.toString();
}
private static String getUpdate(String fileName,String ids){
SAXReader reader = new SAXReader();
Document doc = null;// 声明文档对象
try {
doc = reader.read(new File(fileName));// 读取XML文档
} catch (DocumentException e) {
e.printStackTrace();
}
Element queryInfo = doc.getRootElement();// 声明跟元素
Element update = queryInfo.element("update");
Element id = queryInfo.element("id");
String table=(String)update.attributeValue("table");
String column=(String)update.attributeValue("column");
String value=(String)update.attributeValue("value");
String whereTable = (String)id.attributeValue("table");
String whereColumn = (String)id.attributeValue("column");
String updateSql="update "+table +" set "+column+"='"+value+"' where "+whereTable+"."+whereColumn+" in ("+ids+")";
return updateSql;
}
private static int executeUpdate(String sql){
try {
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);// 强制加载驱动类,隐士的注册驱动
String url = "jdbc:oracle:thin:@128.32.115.1:1521:CISSDEV";
String username = "oracle";
String password = "oracle";
Connection con = DriverManager.getConnection(url, username, password);
Statement stmt = (Statement) con.createStatement();
int count = stmt.executeUpdate(sql);
con.close();
if(count==0){
return 0;
}else{
return 1;
}
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
}
9862
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
