writetodb.java

这是一个实施监控程序, 这是一个实施监控程序,

package com.util.task;


import java.text.ParseException;
import java.util.Date;
import java.util.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;

public class WriteToDB {
	private static WriteToDB instance = null;

	private Connection conn = null;

	private Statement stm = null;

	private PreparedStatement pstm = null;

	private final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";

	private final String ORACLE_URL = "jdbc:oracle:thin:@192.168.0.201:1521:ORCL";

	private final String USR = "pec_admin", PWD = "pec_admin";

	// private ResultSet rs=null;
	/** 是否连接 */
	private boolean connected = false;

	private WriteToDB() {
		CreateConn(ORACLE_DRIVER, ORACLE_URL, USR, PWD);
		//createStatement();
		System.out.println("----------" + conn + "\n" + stm);

	}

	public static WriteToDB getInstance() {
		if (instance == null)
			instance = new WriteToDB();
		return instance;
	}

	public int executeProcToDB(Record record) {
		String sql = "{ call InsertDB(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
		//boolean succ=false;
		int res=0;
		try {
			CallableStatement proc = conn.prepareCall(sql);
			
			proc.setString(1, record.getFDLX().toString());
			 proc.setString(2,record.getWZH());
			 proc.setString(3,record.getJZZL());
			 proc.setString(4,record.getDABH());
			 proc.setString(5,record.getJSZH());
			 proc.setString(6,record.getJYBH());
			 proc.setString(7,record.getSF());
			 proc.setString(8,record.getCPH());
			 proc.setString(9,record.getHPZL());
			 proc.setString(10,record.getCFSJ());
			
			 proc.setString(11,record.getWZDD());
			 //proc.setString(12,record.getFKJE());
			 proc.setLong(12, Long.parseLong(record.getFKJE()));
			 // proc.setString(13,record.getWZJF());
			 proc.setLong(13, Long.parseLong(record.getWZJF()));
			 proc.setString(14,record.getWZDM1());
			 proc.setString(15,record.getWZDM2() );
			 proc.setString(16,record.getXM());
			 proc.setString(17,record.getXZQH());
			 proc.setString(18,record.getFZJG());
			 proc.executeUpdate();
			 conn.commit();
			 res=1;
			System.out.println("返回:---"+res);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.out.println("------"+e.getMessage()+"----");
			e.printStackTrace();
			return -1;
		}finally{
			
		}
		return res;
	}

	public  int execSQLToDB(Record rec) {
		//String sql = "insert into peccancy(FDLX,WZH,JZZL,DABH,JSZH,JYBH,SF,CPH,HPZL,CFSJ," +
				//"WZDD,FKJE,WZJF,WZDM1,WZDM2,XM,XZQH,FZJG) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			StringBuffer sbSql=new StringBuffer();
			sbSql.append("insert into peccancy(FDLX,WZH,JZZL,DABH,JSZH,JYBH,SF,CPH,HPZL,CFSJ,");
			sbSql.append("WZDD,FKJE,WZJF,WZDM1,WZDM2,XM,XZQH,FZJG) values(");
			sbSql.append("'"+rec.getFDLX()+"',");
			sbSql.append("'"+rec.getWZH()+"',");
			sbSql.append("'"+rec.getJZZL()+"',");
			sbSql.append("'"+rec.getDABH()+"',");
			sbSql.append("'"+rec.getJSZH()+"',");
			sbSql.append("'"+rec.getJYBH()+"',");
			sbSql.append("'"+rec.getSF()+"',");
			sbSql.append("'"+rec.getCPH()+"',");
			sbSql.append("'"+rec.getHPZL()+"',");
			sbSql.append(""+toOracleDateString(rec.getCFSJ())+",");
			sbSql.append("'"+rec.getWZDD()+"',");
			sbSql.append(rec.getFKJE()+",");
			sbSql.append(rec.getWZJF()+",");
			sbSql.append("'"+rec.getWZDM1()+"',");
			sbSql.append("'"+rec.getWZDM2().toString().trim()+"',");
			sbSql.append("'"+rec.getXM()+"',");
			sbSql.append("'"+rec.getXZQH().toString().trim()+"',");
			sbSql.append("'"+rec.getFZJG()+"')");
			System.out.println("----------"+sbSql.toString());
		int result=0;
		try {

			//result=pstm.executeUpdate();
			stm=conn.createStatement();
			result=stm.executeUpdate(sbSql.toString());
			conn.commit();
			System.out.println("------------------"+result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.err.println(e.getMessage());
			e.printStackTrace();
		}finally{
			try{
			if(pstm!=null){
				pstm.close();
				}
			}catch(SQLException e){
				System.err.println("发生异常"+e.getMessage());
			}
		}
		return result;
	}
	
	
	
	 public static String toOracleDateString(String date)
	    {
	        String v=date;
	        try{
	      //  int pos=v.indexOf(".");
	        //v=v.substring(0,pos);
	        v="to_date('"+v+"','yyyy-mm-dd HH24:mi:ss')";}
	        catch(Exception e){
	        	v=null;
	        }
	        return v;
	    }

	public void CreateConn(String drv, String url, String usr, String pwd) {
		try {

			if (connected) {
				throw new Exception("数据库已连接，无须重连！");
			}
			Class.forName(drv).newInstance();
			conn = DriverManager.getConnection(url, usr, pwd);
			connected = true;

		} catch (ClassNotFoundException ec) {
			System.out.println("建立数据库连接时出错;\r\n错误为:" + ec);
		} catch (SQLException e) {
			System.out.println("建立数据库连接时出错;\r\n错误为:" + e);
		} catch (Exception et) {
			System.out.println("从自身建立数据库连接时出错;\r\n错误为:" + et);
		}

	}

	public void createStatement() {
		try {
			stm = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("create statement faild" + e);
		}
	}

	/**
	 * 得到是否自动提交
	 */
	public boolean getAutoCommit() {
		try {
			return conn.getAutoCommit();
		} catch (SQLException e) {
		}
		return true;
	}

	/**
	 * 关闭自动提交
	 */
	public void closeAutoCommit() {
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
		}
	}

	/**
	 * 提交操作
	 */
	public void commit() {
		try {
			conn.commit();
		} catch (SQLException e) {
		}
	}

	public void releaseConn() {
		try {
			if (!connected) {
				throw new SQLException("the database is not connect");
			}
			if (conn != null)
				conn.close();
			connected = false;
		} catch (SQLException e) {
			System.err.println("关闭数据库连接时报错!" + e);
		}
	}

	public static void main(String args[]) {
		
		JDOMParser parser=new JDOMParser();
		List list=parser.parser("c:\\gyt\\6000D_6.xml");
		if(list!=null&&list.size()>0)
			for(int i=0;i<list.size();i++){
				Record rec=(Record)list.get(i);
				WriteToDB.getInstance().executeProcToDB(rec);
			}
		
	}

}