intercomimporter.java

jdbc oracle jdbc oraclejdbc oraclejdbc oraclejdbc oraclejdbc oraclejdbc oracle

package tlz.imp;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.*;
import java.net.ConnectException;

import tlz.Debug;

import java.sql.*;

import javax.swing.*;
import javax.swing.table.*;

public class InterComImporter extends DataImporter {
	private String [][] data;
	private int oldMaxID = 0;
	private int comType = -1;
	private Connection accressConn;
	private Connection oracleConn;
	private String[] fieldTitles;
	private ResultSet accessRs;
	
	public void setComType(int type){
		this.comType = type;
	}
	
	public InterComImporter(JDesktopPane showPanel, File dataFile){
		super(showPanel, dataFile);
	}
	
	private void connectAccess() throws Exception{
		accressConn = null;
		String strurl="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=" + dataFile;
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        accressConn=DriverManager.getConnection(strurl) ;
	}
	
	private void deConn() throws Exception{
		if(null != accessRs){
			accessRs.close();
			accessRs = null;
		}
		if(null != accressConn){
			if(!accressConn.isClosed()){
				accressConn.close();
				accressConn = null;
			}
		}
		if(null != oracleConn){
			oracleConn.close();
			oracleConn = null;
		}
	}
	
	public void run(){
		try{
			doImport();
		}catch(Exception e){
			e.printStackTrace();
			//this.writeLog(e.getMessage());
		}
	}
	
	private void readAccess()throws Exception{
		try{
			data = null;
			fieldTitles = null;
			boolean fieldTitlesFalg = true;
			connectAccess();
			if(null == accressConn) return;
			Statement statement = accressConn.createStatement(); 
			String sql="SELECT * FROM [" + Worker.tableName[comType] + "]"; 
			Debug.print("sql = " + sql);
			accessRs = statement.executeQuery(sql); 
			ResultSetMetaData meta  = accessRs.getMetaData();
			int column  = meta.getColumnCount();
			fieldTitles = new String[column];
			data = new String[5][column];
			for(int i = 0; i < 5; i ++){
				if(!accessRs.next()) break;
				for(int j  = 0,m  = 1; j < column; j ++,m++){
					String value  = accessRs.getString(m);
					if(fieldTitlesFalg){
						fieldTitles[j] = meta.getColumnName(m);
						//Debug.print(j + "fieldTitles[j] = " + fieldTitles[j]);
					}
					data[i][j] = value;
				}
				fieldTitlesFalg = false;
				
			}
		}catch(Exception e){
			e.printStackTrace();
			throw e; 
		}
	} 
	
	public void showSample() throws Exception{
	   
	    try{
	    	readAccess();
	    } catch(Exception ex){ //报错
			System.out.println(ex); 
			Worker.appWindow.showHomePage();
			Worker.showError("文件错误",ex.getMessage());
			return;
		} finally{
			deConn();
		}
		SampleDataTableModel sdtm = new SampleDataTableModel(data, fieldTitles);
		JSortTable dataTable = new JSortTable(sdtm);
		
		//dataTable.setModel(sdtm);
		dataTable.setColumnModel(createColumnModel());
		
		if(samplePanel == null) samplePanel = new JPanel(new BorderLayout());
		JScrollPane jScrollPanel = new JScrollPane(dataTable);
		jScrollPanel.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED); 
		samplePanel.add(jScrollPanel, BorderLayout.CENTER);
		JLabel lb = new JLabel("数据预览：");
		samplePanel.add(lb, BorderLayout.NORTH);
		DoImportButton dib = new DoImportButton();
		ReturnButton rb = new ReturnButton("上一步");
		JPanel jp = new JPanel();
		FlowLayout fl = new FlowLayout();
		fl.setAlignment(FlowLayout.RIGHT);
		jp.setLayout(fl);
		jp.add(rb);
		jp.add(dib);
		samplePanel.add(jp, BorderLayout.SOUTH);
		this.showPanel.add(samplePanel);
	}
	
	public int doImport() throws Exception{
		this.isRunning = true;
		int records = 0;
		
		PreparedStatement preStmt = null;
		int maxSeq = 0;
		String field = null;//取最大ID
		try{
	    	connectAccess();
	    	Statement statement = accressConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 
			String sql="SELECT * FROM [" + Worker.tableName[comType] + "]"; 
			Debug.print("sql = " + sql);
			accessRs = statement.executeQuery(sql); 
			try{
				oracleConn = Worker.getDBConnection();
			}catch(Exception e){
				throw new Exception("oracle数据库连接失败");
			}
			if(Worker.types[comType].equals("馆藏珍本")){
				field = "BOOK_ID";
			}else{
				field = "id";
			}
			String maxSql = "select "+ field + " from " + Worker.oracleTableName[comType] + " where " + field + "=(select max("+ field + ")"+ " from " + Worker.oracleTableName[comType] + ")";
			Statement maxStatement  = oracleConn.createStatement();
			Debug.print("maxSql = " + maxSql);
			ResultSet maxSet  = maxStatement.executeQuery(maxSql);
			if(maxSet.next()){
				maxSeq  = maxSet.getInt(field);
			}else{
				maxSeq = -1;
			}
			Debug.print("maxSeq = " + maxSeq);
			maxStatement.close();
			maxStatement = null;
			
			//sql
			StringBuffer sqlBuf = new StringBuffer();
			sqlBuf.append("INSERT INTO ");
			sqlBuf.append(Worker.oracleTableName[comType]);
			sqlBuf.append("(");
			boolean gczb = false;
			
			sqlBuf.append(field + ",");//加序列ID
			
			if(Worker.types[comType].equals("馆藏珍本")){
				gczb = true;
			}
			//sql =  +  + "ID,art_title_e,art_title,author,com_year,attach_file,keyword,com_type,introduce) values(?,?,?,?,?,?,?,'" + comType + "',?) "
			for(int i  = 0; i < Worker.oracleField[comType].length; i ++){
				sqlBuf.append(Worker.oracleField[comType][i]);
				if(i != Worker.oracleField[comType].length - 1){
					sqlBuf.append(",");
				}
			}
			sqlBuf.append(") values(");//value
			for(int i  = 0; i < Worker.oracleField[comType].length + 1; i ++){
				sqlBuf.append("?");
				if(i != Worker.oracleField[comType].length){//加一个ID字段
					sqlBuf.append(",");
				}
			}
			sqlBuf.append(")");
			sql = sqlBuf.toString();
			Debug.print("oracle sql = " + sql);
			preStmt = oracleConn.prepareStatement(sql);
			accessRs.last();
			int rowSize  = accessRs.getRow();
			int percent = 0;
			Debug.print("rowSize = " + rowSize);
			int indexadd = maxSeq;
			for(int i = 0; i < rowSize; i ++){
				accessRs.absolute(i + 1);
				if(jobCanceled) break;
				if((i % 50 == 0) || i  == rowSize - 1){
					int t = (int)(i * 1.0 / rowSize * 100);
			        if(t != percent) {
			        	if(i  == rowSize - 1)
			        		t  = 100;
			        	this.showPercent(t);
			        	percent = t;
			        }
				}
				
			//	sleep(3000);
				
				preStmt.setInt(1,++indexadd);
				for(int j = 0; j < Worker.oracleField[comType].length;j ++){
					if(gczb){//馆藏珍本单独处理
						if(Worker.oracleField[comType][j].equals("SORT_ID")){
							Debug.print("SORT_ID index = " + (j + 2));
							preStmt.setInt(j + 2,Worker.getBook_ID());
							continue;
						}else if(Worker.oracleField[comType][j].equals("ATTACH_FILE")){
							preStmt.setString(j + 2,"/" + Worker.getBook_Folder() + "/" + accessRs.getString(Worker.accessField[comType][j]));
							continue;
						}
					}
					switch(Worker.oracleTypes[comType][j]){
						case Worker.TYPE_VARCHAR2:
							preStmt.setString(j + 2,accessRs.getString(Worker.accessField[comType][j]));
							break;
						case Worker.TYPE_NUMBER:
							preStmt.setInt(j + 2,accessRs.getInt(Worker.accessField[comType][j]));
							break;
						case Worker.TYPE_DATE:
							preStmt.setDate(j + 2,accessRs.getDate(Worker.accessField[comType][j]));
							break;
						case Worker.TYPE_CLOB:
							String content = accessRs.getString(Worker.accessField[comType][j]);
							if(content != null){
				        		java.io.StringReader sr = new java.io.StringReader(content);
				        		preStmt.setCharacterStream(j + 2, sr, content.length());
			        		}else{
			        			preStmt.setString(j + 2, "");//introduce
			        		}
					}
				}
				try{
					Debug.print("1111 = " + preStmt.toString());
        			preStmt.executeUpdate();
        			this.writeLog("行" + i + "导入完成.");
        			records ++;
        		}catch(SQLException sqle){
        			sqle.printStackTrace();
        			this.writeLog("行" + i + ":" + sqle.getMessage());
        		}
			}

	    }catch(Exception ex){ //报错
			ex.printStackTrace();
			jobCanceled = true;
			Worker.showError("导入错误",ex.getMessage());
		} finally{
			this.isRunning = false;
			cancelButton.setEnabled(false);
			if(!jobCanceled){
		    	Worker.showMessage("完成", "共导入" + records + "条记录！");
		    }else{
		    	rollback(field,maxSeq);  //回滚
		    	Worker.showMessage("操作已取消", "操作已取消！");
		    }
			Worker.appWindow.showHomePage();
			if(null != preStmt){
				preStmt.close();
				preStmt = null;
			}
			deConn();
		}
		return records;
	}
	
	private void rollback(String field,int begin){
		Debug.print("rollback");
		Statement delment = null;
		try{
			String slq = "delete " + Worker.oracleTableName[comType] + " where " + field + ">" + begin;
			delment = oracleConn.createStatement();
			Debug.print("delete sql = " + slq);
			delment.executeUpdate(slq);
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(null != delment){
				try {
					delment.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				delment = null;
			}
		}
	}
	
	public boolean excuteSqlByTransaction(Connection conn,String[] sqlStr){
        if(null == sqlStr || sqlStr.length == 0)
            return false;
        boolean flag = true;
        Statement stmt = null;    
        try{
            stmt = conn.createStatement();
            stmt.executeUpdate("SET AUTOCOMMIT=0;");  //设置为不自动提交
            stmt.executeUpdate("START TRANSACTION;");
            for(int i = 0;i < sqlStr.length;i++){
                stmt.addBatch(sqlStr[i]);
            }
            int[] res = stmt.executeBatch();
            for(int i = 0;i < res.length;i++){
                if(res[i] <= 0){
                    stmt.executeUpdate("ROLLBACK;");  //回滚
                    return false;
                }
            }
            if(flag)        
                stmt.executeUpdate("COMMIT;"); //提交
        }catch(Exception ex){
            flag = false;
            try{                
                stmt.executeUpdate("ROLLBACK;");   //事务回滚                
            }catch(Exception ex1){
            }
            
            Worker.showError("excuteSqlByTransaction Failed:",ex.getMessage());
        }finally{      
            try{
                stmt.executeUpdate("SET AUTOCOMMIT=1;");   //恢复为自动提交
            }catch(Exception ex){}
            if(null != stmt)
				try {
					stmt.close();
				} catch (SQLException e) {}
            if(null != conn)
				try {
					conn.close();
				} catch (SQLException e) {}
        }
        return flag;
    }
	
	private DefaultTableColumnModel createColumnModel() {
	    TableCellRenderer defaultRenderer = new DefaultTableCellRenderer();
	    DefaultTableColumnModel  columnModel = new DefaultTableColumnModel();
	    for(int i = 0; i < fieldTitles.length; i ++){
	    	TableColumn column = new TableColumn(i,70,defaultRenderer,null);
	    	column.setHeaderValue(fieldTitles[i]);
	    	columnModel.addColumn(column);
	    }
	     return columnModel;
	  }
	protected void doCancel() throws Exception{
		Connection conn = null;
		PreparedStatement preStmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try{
			conn = Worker.getDBConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM db_inter_com_files WHERE ID > " + this.oldMaxID);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{stmt.close();}catch(Exception e){}
			try{conn.close();}catch(Exception e){}
		}
		//this.showSample();
	}
}