lp_convertdata.java

简介： 今天为网友提供的是JAVA源码

package com.power.pipeengine;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
import java.sql.*;
import java.net.*;

/**
 * Convert between CSV and SQLDB
 * Creation date: 2002/01/15
 * @author: vicksong
 */
public class LP_ConvertData extends HttpServlet {

  private int BATCH_BLOCK = 100;
  private String UserName = "";
  private String Password = "";

  private final String ERRMsg = "**Error";
  private PrintWriter out = null;
  private Connection conn = null;
  private Statement stmt = null;
  private PreparedStatement pstmt = null;
  private ResultSet rs = null;
  private URL url = null;
  private HttpURLConnection urlconn = null;
  private PrintStream writer = null;

  private BufferedInputStream in = null;
  private BufferedReader bd = null;

    /**
     * Process incoming HTTP GET requests
     *
     * @param request Object that encapsulates the request to the servlet
     * @param response Object that encapsulates the response from the servlet
     */
    public synchronized void doGet(
        javax.servlet.http.HttpServletRequest request,
        javax.servlet.http.HttpServletResponse response)
        throws ServletException, IOException {

        response.setContentType("text/html;charset=gb2312");
        out = response.getWriter();
        response.setStatus(HttpServletResponse.SC_OK);

        String cmd = request.getParameter("cmd");
        String FileName = request.getParameter("FileName");
        String Folder = request.getParameter("Folder");
//        String UserName = request.getParameter("UserName");
//        String Password = request.getParameter("Password");

        /**** check Parameters ****/
        if (cmd == null || FileName == null || Folder == null) {
	       	out.println("**Error: Reading Missing parameters.");
	    	System.out.println("**Error: Reading Missing parameters.");
	       	return;
        }

        /**** check Legal "cmd" ****/
        if (!cmd.equals("DB-CSV")) {
            out.println("**Error: Invalid 'cmd' parameter -->" + cmd);
            System.out.println("**Error: Invalid 'cmd' parameter -->" + cmd);
            return;
        }

        /**** Converting Files ****/
        out.println("<<<<Start Convert DB-CSV>>>>");
        System.out.println("<<<<Start Convert DB-CSV>>>>");

        try {
          getDBConnection();

          BufferedReader br = new BufferedReader(new FileReader(FileName));
          String str = "";
          while ((str=br.readLine()) != null) {
            if (str.length() == 0 ||
                str.length() >= 2 &&
                str.substring(0,2).equals("//")) continue;

            String _FileName = "";
            String _SQL = "";
            int _ColCount = 0;
            StringTokenizer st = new StringTokenizer(str, "&");
            _FileName = st.nextElement().toString();
            _ColCount = Integer.parseInt(st.nextElement().toString());
            _SQL = st.nextElement().toString();

            DBtoCSV(Folder+"\\"+_FileName, _SQL, _ColCount);
          }

          br.close();
        } catch (Exception e) {
            System.out.println("**Error: " + e.getMessage());
            out.println("**Error: " + e.getMessage());
        } finally {
          releaseDBConnection();
          out.println("<<<<End Convert DB-CSV>>>>");
          System.out.println("<<<<End Convert DB-CSV>>>>");
          out.close();
        }
    }
    /**
     * Process incoming HTTP POST requests
     *
     * @param request Object that encapsulates the request to the servlet
     * @param response Object that encapsulates the response from the servlet
     */
    public synchronized void doPost(
        javax.servlet.http.HttpServletRequest request,
        javax.servlet.http.HttpServletResponse response)
        throws ServletException, IOException {

        response.setContentType("text/html");
        out = response.getWriter();
        response.setStatus(HttpServletResponse.SC_OK);

        String cmd = request.getParameter("cmd");
        String FileName = request.getParameter("FileName");
        String TableName = request.getParameter("TableName");

        /**** check Parameters ****/
        if (cmd == null || FileName == null || TableName == null) {
          out.println("**Error: Reading Missing parameters.");
          System.out.println("**Error: Reading Missing parameters.");
          return;
        }
        /**** check Legal "cmd" ****/
        if (!cmd.equals("CSV-DB")) {
            out.println("**Error: Invalid 'cmd' parameter -->" + cmd);
            System.out.println("**Error: Invalid 'cmd' parameter -->" + cmd);
            return;
        }

        /**** Converting Files ****/
        out.println("<<<<Start Deconvert CSV-DB>>>>");
        System.out.println("<<<<Start Deconvert CSV-DB>>>>");

        try {
          getDBConnection();
          CSVtoDB(FileName, TableName);

        } catch (Exception e) {
            System.out.println("**Error: " + e.getMessage());
            out.println("**Error: " + e.getMessage());
        } finally {
          releaseDBConnection();
          out.println("<<<<End Deconvert DB-CSV>>>>");
          System.out.println("<<<<End Deconvert DB-CSV>>>>");
          out.close();
        }
    }

/**** CSV to DB ****/
    public void CSVtoDB(String FileName, String TableName)
        throws Exception
    {
      System.out.println("##Start Deconverting.");
      out.println("##Start Deconverting file--> "+FileName+" into "+TableName);

      int count = 0;
      try {
        getFileReadConnection(FileName);
        String str = "";
        boolean isInited = false;
        while ((str=bd.readLine()) != null) {
                if (str.length() == 0) continue;
                if (str.length() >= ERRMsg.length() &&
                    str.substring(0,ERRMsg.length()).equals(ERRMsg)) {
                  throw new Exception("File reading Failed.");
                }

                //get TokenCount
                StringTokenizer st = new StringTokenizer(str, ",");
                int ColCount = st.countTokens();
                if (ColCount == 0) continue;

                //construct PrepareStatement
                String _SQL = "insert into " + TableName + " values(";
                if (!isInited) {
                  for (int i=0; i<ColCount; i++) _SQL += "?,";
                  _SQL = _SQL.substring(0, _SQL.length()-1) + ")";
                  pstmt = conn.prepareStatement(_SQL);
                  isInited = true;
                }

                //set Values
                for (int i=1; i<=ColCount; i++) {
                  byte b[] = st.nextToken().toString().getBytes("gb2312");
                  String newValue = new String(b,"8859_1");
                  pstmt.setString(i, newValue.toLowerCase().equals("null")?null:newValue);
                }
                count += pstmt.executeUpdate();
        }
      } catch (Exception e) {
        throw e;
      }
      finally {
        out.println("Total " + count + " rows Inserted.");
        System.out.println("Total " + count + " rows Inserted.");
        releaseReturnMessage();
        releaseFileReaderConnection();
      }
       out.println("-------Deconverting OK-------");
       System.out.println("-------Deconverting OK-------");
    }

/**** DB to CSV ****/
    public void DBtoCSV(String FileName, String SQLstr, int ColCount)
        throws Exception
    {
      System.out.println("##Start Converting.");
      System.out.println("SQL '" + SQLstr + "'");
      out.println("##Start Converting file--> " + FileName);
      out.println("SQL '" + SQLstr + "'");

      try {
        if (!stmt.execute(SQLstr)) throw new Exception("No Results be returned. ");
        getFileWriteConnection(FileName);
        while (true) {
          rs = stmt.getResultSet();
          if (ColCount == 0)
            ColCount = rs.getMetaData().getColumnCount();
          int RowCount = 1;
          while (rs.next()) {
            String s = "";
            for (int i=1; i<=ColCount; i++) {
              if (rs.getString(i) == null)
                throw new Exception("Row " + RowCount + ",Column " + i +
                                    " found Null value, CANCEL RUNNING.");
              s += (i>1?",":"") + rs.getString(i).replace(',','.');
            }
            RowCount++;
/*            byte b[] = s.getBytes("8859_1");
            s = new String(b,"gb2312");
*/            writer.println(s);
          }
          if (!stmt.getMoreResults()) break;
        }
        getReturnMessage();
      } catch (Exception e) {
        throw e;
      }
      finally {
        releaseReturnMessage();
        releaseFileWriterConnection();
      }

       System.out.println("-------Converting OK-------");
       out.println("-------Converting OK-------");
    }

    /**** varify Writing Message ****/
    public void getReturnMessage()
        throws Exception
    {
      in = new BufferedInputStream(urlconn.getInputStream());
      bd = new BufferedReader(new InputStreamReader(in));
      String str = "";
      while ((str=bd.readLine()) != null) {
        out.println(str);
        if (str.length() >= ERRMsg.length() &&
            str.substring(0,ERRMsg.length()).equals(ERRMsg)) {
          throw new Exception("File writing Failed.");
        }
      }
    }

    /**** Open Reading Stream ****/
    public void getFileReadConnection(String FileName)
        throws Exception
    {
      url = new URL("http://127.0.0.1/ChangRong/servlet/com.power.pipeengine.LP_ReadWriteFile?"+
                    "FileName="+FileName+"&cmd=ReadModel");
      urlconn = (HttpURLConnection)(url.openConnection());
      urlconn.setRequestMethod("GET");
      urlconn.setDoOutput(true);
      urlconn.setDoInput(true);
      in = new BufferedInputStream(urlconn.getInputStream());
      bd = new BufferedReader(new InputStreamReader(in));
    }

    /**** Open Writing Stream ****/
    public void getFileWriteConnection(String FileName)
        throws Exception
    {
      url = new URL("http://127.0.0.1/ChangRong/servlet/com.power.pipeengine.LP_ReadWriteFile?"+
                    "FileName="+FileName+"&cmd=WriteModel");
      urlconn = (HttpURLConnection)(url.openConnection());
      urlconn.setRequestMethod("POST");
      urlconn.setDoOutput(true);
      urlconn.setDoInput(true);
      writer = new PrintStream(urlconn.getOutputStream());
    }

    /**** Initialize DB Connection ****/
    public void getDBConnection()
        throws Exception
    {
          Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
          conn = DriverManager.getConnection
          ("jdbc:microsoft:sqlserver://127.0.0.1:1433","ChangRong3","paraster");
          stmt = conn.createStatement();
    }

    /****　Release Stream Reader ****/
    public void releaseReturnMessage()
    {
      try {
        if (in != null) in.close();
        if (bd != null) bd.close();
      }
      catch(Exception e) {}
    }

    /**** Release Writer Stream ****/
    public void releaseFileWriterConnection()
    {
      try {
        if (writer != null) {
          writer.flush();
          writer.close();
        }
        urlconn.disconnect();
      }
      catch(Exception e) {}
    }

    /**** Release Writer Stream ****/
    public void releaseFileReaderConnection()
    {
      try {
        urlconn.disconnect();
      }
      catch(Exception e) {}
    }

    /**** Release DB Connection ****/
    public void releaseDBConnection()
    {
      try {
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
      if (stmt != null) stmt.close();
      if (conn != null) conn.close();
      }
      catch(Exception e) {}
    }
}