sqlselectblh.java

以前做的一个j2ee的项目

package gov.gdlt.ssgly.taxcore.taxblh.gzda.service;

import java.sql.*;
import java.io.*;
import org.jdom.*;
import org.jdom.input.*;
import gov.gdlt.ssgly.taxcore.taxblh.gzda.service.xmlinterface.*;
import gov.gdlt.ssgly.taxcore.taxblh.gzda.service.xmlinterface.impl.*;
import gov.gdlt.ssgly.taxcore.comm.blh.BaseBizLogicHandler;
import gov.gdlt.ssgly.taxcore.comm.event.RequestEvent;
import gov.gdlt.ssgly.taxcore.comm.event.ResponseEvent;
import gov.gdlt.ssgly.taxcore.comm.exception.TaxBaseBizException;
import gov.gdlt.ssgly.taxcore.comm.exception.TaxBaseSystemException;

import gov.gdlt.ssgly.taxcore.comm.dao.*;
import gov.gdlt.ssgly.taxcore.comm.servicelocator.SqlMapLocator;
import gov.gdlt.ssgly.taxcore.comm.config.ApplicationContext;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.common.util.PaginatedList;
import gov.gdlt.ssgly.taxcore.comm.servicelocator.JDBCLocator;

public class SqlSelectBLH extends BaseBizLogicHandler implements Command {
    public SqlSelectBLH() {
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public String execute(RequestHelper helper) {
        Document document = null;
        String xml = helper.getParaXml();
        StringReader SR = new StringReader(xml);
        SAXBuilder builder = new SAXBuilder();
        try {
            document = builder.build(SR);
        } catch (JDOMException je) {
            System.out.println(je.toString());
        }
        // Get the root element
        Element root = document.getRootElement();
        Element params = root.getChild("select");
        String select = params.getText();

        return getSelectXml(select);
    }

    private String getSelectXml(String sql) {
        StringBuffer buff = new StringBuffer();
        Connection connection = null;
        Statement stmt = null;
        ResultSet res = null;
        ResultSetMetaData data = null;
        //SqlMapClient sqlMap=null;
        buff.append("<?xml version=\"1.0\" encoding=\"gb2312\"?>\n<root>\n");
        try {
            //connection = ConFactor.getConnectFromT3();
            //connection = ConFactor.getConnJdbc();
            //sqlMap=SqlMapLocator.getInstance().getSqlMapClient();
            //sqlMap.startTransaction();
            //connection = sqlMap.getCurrentConnection();
             connection=JDBCLocator.getInstance().getJDBCConnection();

            if (connection == null) {
                System.out.println("通过JNDI连接数据库不存功!");
            }
            stmt = connection.createStatement();
            res = stmt.executeQuery(sql);

            data = res.getMetaData();
            int col = data.getColumnCount();

            buff.append("<params>\n");
            while (res.next()) {
                buff.append("<item>\n");
                for (int i = 1; i <= col; i++) {
                    int type = data.getColumnType(i);
                    String colname = data.getColumnName(i);
                    String value = getRes(type, i, res) == null ? "" :
                                   getRes(type, i, res);
                    //System.out.println(value);
                    //try{
                    //  value = new String(value.getBytes("CP850"), "GBK");
                    //}catch(UnsupportedEncodingException ue){
                    //  System.out.println(ue.toString());
                    //}
                    value=replace(value,"<","(");
                    value=replace(value,">",")");
                    buff.append("<" + colname + ">\n");
                    buff.append(value + "\n");
                    buff.append("</" + colname + ">\n");
                }
                buff.append("</item>\n");
            }
           //sqlMap.commitTransaction();
        } catch (SQLException sqlexception) {} finally {
            try {
                //sqlMap.endTransaction();
                connection.close();
            } catch (SQLException sqlexception1) {}
        }
        buff.append("</params>\n");
        buff.append("</root>\n");


        return buff.toString();
    }

    private String replace(String str, String oldStr, String newStr)
      {
        int len = oldStr.length();
        int idx;
        int len2;
        String str2;
        while((idx = str.indexOf(oldStr)) != -1)
        {
          String str1 = str.substring(0, idx);
          len2=idx + len ;
          if(len2>=str.length())
            str2="";
          else
            str2 = str.substring(len2);
          str = String.valueOf(String.valueOf((new StringBuffer(String.valueOf(String.valueOf(str1)))).append(newStr).append(str2)));
        }
        return str;
  }

    private String getRes(int coltype, int colindex, ResultSet myres) throws
            SQLException {
        String res = "";
        switch (coltype) {

        case Types.BIT:
            res = Integer.toString(myres.getInt(colindex));
            break;
        case Types.TINYINT:
            break;
        case Types.BIGINT:
            break;
        case Types.LONGVARBINARY:
            break;
        case Types.VARBINARY:
            break;
        case Types.BINARY:
            break;
        case Types.LONGVARCHAR:
            break;
        case Types.NULL:
            res = "";
            break;
        case Types.CHAR:
            res = myres.getString(colindex);
            break;
        case Types.NUMERIC:
            res = Double.toString(myres.getDouble(colindex));
            res = deciToInte(res);
            break;
        case Types.DECIMAL:
            res = Double.toString(myres.getDouble(colindex));
            res = deciToInte(res);
            break;
        case Types.INTEGER:
            res = Integer.toString(myres.getInt(colindex));
            break;
        case Types.SMALLINT:
            res = Integer.toString(myres.getInt(colindex));
            break;

        case Types.FLOAT:
            res = Float.toString(myres.getFloat(colindex));
            res = deciToInte(res);
            break;

        case Types.REAL:
            res = Double.toString(myres.getDouble(colindex));
            res = deciToInte(res);
            break;

        case Types.DOUBLE:
            res = Double.toString(myres.getDouble(colindex));
            res = deciToInte(res);
            break;
        case Types.VARCHAR:
            res = myres.getString(colindex);
            break;

        case Types.DATE:
            if (myres.getDate(colindex) == null) {
                res = "";
            } else {
                res = myres.getDate(colindex).toString();
            }
            if (res.length() > 8) {
                res = res.substring(0, 10);
            }

            break;

        case Types.TIME:
            break;
        case Types.TIMESTAMP:
            Timestamp sdate = myres.getTimestamp(colindex);
            if (sdate == null) {
                //sdate = Timestamp.valueOf("1900-01-01 00:00:00");
                res = "";
            }
            res = sdate.toString();
            res = res.substring(0, 10);
            break;

        default:
            res = myres.getString(colindex);
            break;
        }
        return res;
    }

    private String deciToInte(String res) {
        String ret = "";
        if (res.indexOf(".") > 0) {
            String ve = res.substring(res.indexOf(".") + 1, res.length());
            int dec;
            if (ve.indexOf("E") > 0) {
                dec = 1;
            } else {
                dec = Integer.parseInt(ve);
            }
            if (dec > 0) {
                ret = res;
            } else {
                ret = res.substring(0, res.indexOf("."));
            }
        } else {
            ret = res;
        }
        return ret;
    }

    public static void main(String[] args) {

    }

    private void jbInit() throws Exception {
    }
    protected ResponseEvent validateData(RequestEvent req) throws
            Exception {
        return null;
    }

    protected ResponseEvent performTask(RequestEvent req) throws
        TaxBaseBizException, TaxBaseSystemException {

    String dealMethod = req.getDealMethod();
    ResponseEvent resEvn = new ResponseEvent();
     return resEvn;
   }
}