pubfunction.java

来自「这个是转换为把数据库中数据转换为正常的xml」· Java 代码 · 共 354 行

package com;

import java.io.*;
import java.sql.*;

import org.dom4j.Document;

import com.sun.rowset.WebRowSetImpl;

public class PubFunction {
	private Connection conn = null;

	private Statement stmt = null;
	
	public byte[] PubCom(String sql){
		byte[] temp=null;
		try{
			ResultSet rs = getQueryResult();
			temp = RsToXML(rs);
			return temp;
		}catch(Exception ex)
		{
			return new byte[-1];
		}
	}
	
	public byte[] PubExec(String xml){
		byte[] temp=null;
		try{
			ResultSet rs = getQueryResult();
			temp = RsToXML(rs);
			return temp;
		}catch(Exception ex)
		{
			return new byte[-1];
		}
		finally{
			try{
				if (conn!=null){
 				 stmt.close(); // 关闭statement
				 conn.close(); // 关闭连接
				}
			}catch(Exception ex)
			{
				
			}
		}
	}
	/**	
	 * 通用的读取结果集某一列的值并转化为String表达
	 * @param ResultSet rs 输入的纪录集
	 * @param int colNum 第几列
	 * @param int type 数据类型
	 */
	private String getValue(final ResultSet rs, int colNum, int type)
			throws SQLException {
		switch (type) {
		case Types.ARRAY:
		case Types.BLOB:
		case Types.CLOB:
		case Types.DISTINCT:
		case Types.LONGVARBINARY:
		case Types.VARBINARY:
		case Types.BINARY:
		case Types.REF:
		case Types.STRUCT:
			return "undefined";
		default: {
			Object value = rs.getObject(colNum);
			if (rs.wasNull() || (value == null))
				return ("null");
			else
				return (value.toString());
		}
	}
	}
	
	private String GetType(int type)
	 {
	 	String temp="";
	 	switch(type){
	     case Types.FLOAT:
	     case Types.NUMERIC:
	     	temp = "float";
	     	break;
	     case Types.CHAR:
	     	temp = "bin.hex";
	     	break;
	     case Types.BIGINT:
	     	temp = "string";
	     	break;
	     default:
	     	temp = "string";
	 	}
	 	return temp;
	 }
	
	private byte[] RsToXML(ResultSet rs) throws IOException {
		StringBuffer buffer = new StringBuffer(1024 * 1024);
		try{		
			if (rs == null)
				return null;
			if (!rs.next())
				return null;
			String colName,colnull,writeunknown,type;
			String Temp="";
			int curType, colCount,isnull,maxLength;
			buffer.append("<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'\n"); //XML的头部信息
			buffer.append("\txmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'\n");
			buffer.append("\txmlns:rs='urn:schemas-microsoft-com:rowset'\n");
			buffer.append("\txmlns:z='#RowsetSchema'>\n");
			
			buffer.append("<s:Schema id='RowsetSchema'>\n");		
			buffer.append("\t<s:ElementType name='row' content='eltOnly' rs:updatable='true'>\n");		
			ResultSetMetaData rsmd = rs.getMetaData(); 		//得到结果集的定义结构
			colCount = rsmd.getColumnCount(); 			    //得到列的总数
			//tablename= "bbs_post";                        //rsmd.getTableName(1);
			for (int i = 1; i <= colCount; i++) {		
				curType = rsmd.getColumnType(i);	        //获取字段类型
				colName = rsmd.getColumnName(i);
				isnull  = rsmd.isNullable(i);
				type    = GetType(rsmd.getColumnType(i));
				maxLength = rsmd.getColumnDisplaySize(i);
				
				if (rsmd.isReadOnly(i))
				{
					writeunknown="false";
				}
				else
					writeunknown="true";
				if (isnull==1)
					colnull="true";
				else
					colnull="false";
				
				if (type.equals("float")){
					Temp= " rs:nullable='"+colnull+"'";
				}
				
				buffer.append("\t\t<s:AttributeType name='"+colName+
						"' rs:number='"+i+"'"+Temp+					
						" rs:writeunknown='"+writeunknown+"'>\n");
				buffer.append("\t\t\t<s:datatype");
				buffer.append(" dt:type='"+type+"'");
				if (type.equals("string")){
					buffer.append(" rs:dbtype='str'");
					buffer.append(" dt:maxLength='"+String.valueOf(maxLength)+"'");
					if (colnull.equals("false")){
						buffer.append(" rs:maybenull='"+colnull+"'");
					}
				}
				else if (type.equals("float")){
					buffer.append(" dt:maxLength='"+String.valueOf(maxLength)+"'");
					buffer.append(" rs:precision='"+String.valueOf(rsmd.getPrecision(i)+15)+"'");
					buffer.append(" rs:fixedlength='"+Boolean.toString(rsmd.isSigned(i))+"'");
				}	
				else if (type.equals("bin.hex")){
					buffer.append(" dt:maxLength='"+String.valueOf(maxLength)+"'");
				}	
				
				buffer.append("/>\n\t\t</s:AttributeType>\n");
			}
			buffer.append("\t\t<s:extends type='rs:rowbase'/>\n");
			buffer.append("\t</s:ElementType>\n");
			buffer.append("</s:Schema>\n");
			
			System.out.println("hiteker1:"+buffer.toString());
			
			buffer.append("<rs:data>\n");
			while(rs.next()) { 			
				// 对放回的全部数据逐一处理,格式为row id , col name, col context
				buffer.append("\t<z:row");
				for (int i = 1; i <= colCount; i++) {
					curType = rsmd.getColumnType(i);
					if (getValue(rs, i, curType)!=null&&!getValue(rs, i, curType).equals("null")){
						buffer.append(" "+rsmd.getColumnName(i)+ "='");
						buffer.append(getValue(rs, i, curType)+"'");
					}
				}
				buffer.append("/>\n");
			}
			buffer.append("</rs:data>\n");
			buffer.append("</xml>");
			rs.close();
			System.out.println("hiteker:"+buffer.toString());
	}catch(Exception ex)
	{
		return new byte[-1];
	}
		return buffer.toString().getBytes();
	}
	
	private ResultSet getQueryResult() throws Exception {
		try {
			//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
		} catch (Exception ex) {
			System.out.println("hiteker");
		}
		//String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName="+DateBase;
		String url = "jdbc:oracle:thin:@192.168.6.40:1521:ORA9M8";
		// 载入jdbcodbc的驱动程序
		String user = "m8connect";
		String password = "m8connect";
		ResultSet rs=null;
		try {
			conn = DriverManager.getConnection(url, user, password); // 连接数据库
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			// 创建一个statement
			String sql = "select * from (select a.*, rownum rn from tgl_items a) where rn<=7120";
			//String sql = "select * from (select a.*, rownum rn from TAM_ASSETBOOK0003401 a) where rn<=100";// 定义查询的SQL语句
			rs = stmt.executeQuery(sql); // 执行查询
		} catch (Exception ex) {
			System.out.println("hiteker");
		} finally {
			try {
				// stmt.close(); // 关闭statement
				// conn.close(); // 关闭连接
			} catch (Exception ex) {
			}
		}
		return rs;
	}

	private Boolean ExecSql(String sql) throws Exception {
		try {
			//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
		} catch (Exception ex) {
			System.out.println("hiteker");
		}
		//String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName="+DateBase;
		String url = "jdbc:oracle:thin:@192.168.6.40:1521:ORA9M8";
		// 载入jdbcodbc的驱动程序
		String user = "m8connect";
		String password = "m8connect";
		ResultSet rs=null;
		try {
			conn = DriverManager.getConnection(url, user, password); // 连接数据库
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			// 创建一个statement
			sql = "select * from (select a.*, rownum rn from tgl_items a) where rn<=7120";
			//String sql = "select * from (select a.*, rownum rn from TAM_ASSETBOOK0003401 a) where rn<=100";// 定义查询的SQL语句
			return stmt.execute(sql); // 执行查询
		} catch (Exception ex) {
			System.out.println("hiteker");
			return false;
		} finally {
			try {
				// stmt.close(); // 关闭statement
				// conn.close(); // 关闭连接
			} catch (Exception ex) {
			}
		}
		
	}

	private static byte[] toString(Document document) throws Exception {
		document.setXMLEncoding("GB2312");
		StringWriter sb = new StringWriter();
		BufferedWriter bw = new BufferedWriter(sb);
		document.write(bw);
		bw.close();
		sb.close();
		return sb.toString().getBytes();
	}

	private static void writeFile(byte[] data, String filename)
			throws IOException {
		File file = new File(filename);
		file.getParentFile().mkdirs();
		BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(
				new FileOutputStream(file));
		bufferedOutputStream.write(data);
		bufferedOutputStream.close();
	}
	/**
	 * 把结果集转换成xml文件
	 * **/
	private void changeXML(final ResultSet rs) throws Exception {
		try {
			WebRowSetImpl wrs = new WebRowSetImpl();
			wrs.populate(rs);
			FileOutputStream out = new FileOutputStream("c:/catalog.xml");
			wrs.writeXml(out);
			wrs.close();		
		} catch(IOException e){
			System.out.println("Andrew: IOException!");//$NON-NLS-1$
			e.printStackTrace();
		}
	}
	private void main(){ 
		try{ 
		BufferedReader br=new BufferedReader(new FileReader("BufferedReaderTest.java")); 
		String line; 
		while((line = br.readLine())!= null){ 
		System.out.println(line); 
		} 
		} 
		catch(Exception e){ 
		System.out.println(e.getLocalizedMessage()); 
		e.printStackTrace(); 
		} 
		} 

	private  byte[] ReadFile() throws Exception {
        // 此文件为utf-8编码文件
        String filename = "c:\\";
        FileInputStream fin = new FileInputStream(filename);
        // ChangeCharset.class.getResourceAsStream("/a.txt")
        // 通知虚拟机以utf-8的编码格式读入
        //BufferedReader in = new BufferedReader(new InputStreamReader(fin,"UTF-8"));
        
        BufferedReader in = new BufferedReader(new InputStreamReader(fin,
        "ISO8859_1"));        
        
        // 输出文件
        // File file = new File("D:/eclipse/workspace/MY Test/bin/a.txt");
        // 写入文件的代码
        // BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new
        // FileOutputStream(file), "GBK"));
        // String str ;
        // while((str=in.readLine()) != null) {
        // System.out.println(str);
        // out.write(str,0,str.length());
        // out.newLine();
        // out.flush();
        // }
        // 写入byte数组的代码
        ByteArrayOutputStream byteAout = new ByteArrayOutputStream();
        // 可以制定任意编码输出
        BufferedWriter out = new BufferedWriter(new OutputStreamWriter(
                byteAout, "GBK"));
        String str;
        while ((str = in.readLine()) != null) {
            // 自动转换为系统编码输出
            System.out.println(str);
            // 写入输出流
            out.write(str, 0, str.length());
            out.newLine();
            out.flush();
        }
        //将gbk的byte数组编码成String输出
        System.out.println(new String(byteAout.toByteArray(), "GBK"));
        out.close();
        in.close();
        fin.close();
        return byteAout.toByteArray();
    }

}