wp/nwyj/.svn/pristine/fb/fb8bae3a7d69373b8278e86b1e3592d8319a28b9.svn-base

package com.cockpit.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import com.cockpit.vo.LineTripOut;
import com.persistence.DBdll.SysOperator;
import com.sinosoft.cm.DBUtils;
import com.sinosoft.cm.common.ArrayList;

public class UtilDao {

	public List<LineTripOut> getLineTripList(){
		Connection conns = DBUtils.getConnection();
		PreparedStatement pss=null;
		ResultSet rs=null;
		
		List<LineTripOut> lineTripList = new ArrayList<LineTripOut>();
		String sql = "SELECT * FROM NWYJ.ECM_OMS_LINE_TRIP_OUT";

		try{
			pss = conns.prepareStatement(sql);
			rs = pss.executeQuery();
			while(rs.next()){
				
				LineTripOut lto = new LineTripOut(
						rs.getString("FD_OBJECTID"), 
						rs.getString("VIPCONSUMER"),
						rs.getString("EQUIPMENTID"), 
						rs.getString("EQUIPMENTNAME"), 
						rs.getString("RECLOSE"),
						rs.getString("STATIONNAME"), 
						rs.getString("VOLLEVEL"), 
						rs.getString("INSTITUTIONS"),
						rs.getString("SUBSTATION"), 
						rs.getString("ONTIME"), 
						rs.getString("MAINTENANCEDEPARTMENT"),
						rs.getString("PHASE"), 
						rs.getString("RECORD"), 
						rs.getString("LOSTLOAD"), 
						rs.getString("AREA"),
						rs.getString("INAREAID"), 
						rs.getDate("TRIPTIME"), 
						rs.getString("CONSUMER"), 
						rs.getString("IS_DEL"),
						rs.getDate("UPDATEDATE"));
				
				lineTripList.add(lto);
			}
			return lineTripList;
		}catch(SQLException e){ e.printStackTrace(); }
		finally{ 
			try {
				if(rs != null)
					rs.close();
				if(pss != null)
					pss.close();
				if(conns != null)
					conns.close();
			} catch (SQLException e) { e.printStackTrace(); }
		}
		return null;
	}
	
	public void addLineTripBatch(List<LineTripOut> ltList){
		Connection conn = DBUtils.getConnection();
		PreparedStatement ps=null;
		String addSql = "insert into NWYJ_SERVICE.OSM_OUTAGETRIP(\"OBJ_ID\", \"VIPCONSUMER\", " + 
						"\"EQUIPMENTNAME\", \"RECLOSE\", \"STATIONNAME\", \"VOLLEVEL\", \"AREA\", " + 
						"\"SUBSTATION\", \"ONTIME\", \"MAINTENANCEDEPARTMENT\", \"PHASE\", " + 
						"\"RECODE\", \"LOSTLOAD\", \"TRIPTIME\", \"CONSUMER\", " + 
						"\"UP_DATE\") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
		try{
			//事务提交方式为非自动
			conn.setAutoCommit(false) ;
			
			ps = conn.prepareStatement(addSql) ;
			for (LineTripOut entity : ltList){
				ps.setObject(1, entity.getFD_OBJECTID());
				ps.setObject(2, entity.getVIPCONSUMER());
				ps.setObject(3, entity.getEQUIPMENTNAME());
				ps.setObject(4, entity.getRECLOSE());
				ps.setObject(5, entity.getSTATIONNAME());
				ps.setObject(6, entity.getVOLLEVEL());
				ps.setObject(7, entity.getAREA());
				ps.setObject(8, entity.getSUBSTATION());
				ps.setObject(9, entity.getONTIME());
				ps.setObject(10, entity.getMAINTENANCEDEPARTMENT());
				ps.setObject(11, entity.getPHASE());
				ps.setObject(12, entity.getRECORD());
				ps.setObject(13, entity.getLOSTLOAD());
				ps.setObject(14, entity.getTRIPTIME());
				ps.setObject(15, entity.getCONSUMER());
				System.out.println(entity.getUPDATEDATE());
				ps.setObject(16, entity.getUPDATEDATE());
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
			System.out.println("ok!!!");
		} catch (SQLException e){ 
			
			try{ conn.rollback(); } 
			catch (SQLException e1){ e1.printStackTrace(); }
			
			e.printStackTrace(); 
		} finally {
			try{ conn.setAutoCommit(true); }
			catch(SQLException e){ e.printStackTrace(); }
			finally{
				try {
					if(ps != null)
						ps.close();
					if(conn != null)
						conn.close();
				} catch (SQLException e) { e.printStackTrace(); }
			}
		}
	}
	
	public static void main(String arrgs[]){
		UtilDao udao = new UtilDao(); 
//		new UtilDao().addLineTripBatch(new UtilDao().getLineTripList());
		udao.addLineTripBatch(udao.getLineTripList());
	}
}