swtface.java

导库程序

/*
 *@作者：吴玮杰    学号：0461013
 *@创建日期：2006-9-17
 *复旦大学04软件工程
 */
package oracleConn;

import org.eclipse.swt.SWT;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.FileDialog;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Text;
import com.swtdesigner.SWTResourceManager;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import jxl.*;
import jxl.read.biff.BiffException;


public class SWTface extends Shell
{

	private Text text;
	private Text filePath;
	private Text pwd;
	private Text user;
	private Text hostname_1;
	private Text serviceName;
	private Text hostName;
	private String fileName;
	final static String sDBDriver = "oracle.jdbc.driver.OracleDriver";
	/**
	 * Launch the application
	 * @param args
	 */
	public static void main(String args[])
	{
		try
		{
			Display display = Display.getDefault();
			SWTface shell = new SWTface(display, SWT.SHELL_TRIM);
			shell.open();
			shell.layout();
			while (!shell.isDisposed())
			{
				if (!display.readAndDispatch())
					display.sleep();
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * Create the shell
	 * @param display
	 * @param style
	 */
	public SWTface(Display display, int style)
	{
		super(display, style);
		createContents();
	}

	/**
	 * Create contents of the window
	 */
	protected void createContents()
	{
		setText("SWT Application");
		setSize(500, 375);
		setBackground(SWTResourceManager.getColor(192, 192, 192));

		final Label hostNameLabel = new Label(this, SWT.HORIZONTAL);
		hostNameLabel.setBackground(SWTResourceManager.getColor(192, 192, 192));
		hostNameLabel.setFont(SWTResourceManager.getFont("Arial", 11, SWT.BOLD));
		hostNameLabel.setText("Host Name");
		hostNameLabel.setBounds(10, 10, 87, 21);

		hostName = new Text(this, SWT.BORDER);
		hostName.setFont(SWTResourceManager.getFont("", 12, SWT.NONE));
		hostName.setBounds(103, 10, 136, 21);

		final Label portLabel = new Label(this, SWT.HORIZONTAL);
		portLabel.setBackground(SWTResourceManager.getColor(192, 192, 192));
		portLabel.setFont(SWTResourceManager.getFont("Arial", 11, SWT.BOLD));
		portLabel.setText("Service Name");
		portLabel.setBounds(250, 10, 111, 21);

		serviceName = new Text(this, SWT.BORDER);
		serviceName.setFont(SWTResourceManager.getFont("", 12, SWT.NONE));
		serviceName.setBounds(357, 10, 125, 21);

		final Button brouseButton = new Button(this, SWT.BORDER);
		brouseButton.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(final SelectionEvent e)
			{
				FileDialog i = new FileDialog(SWTface.this, 0);
				fileName = i.open();
				filePath.setText(fileName);

			}
		});
		brouseButton.setBounds(356, 72, 60, 30);
		brouseButton.setText("Brouse...");

		final Button executeButton = new Button(this, SWT.NONE);
		executeButton.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(final SelectionEvent e)
			{
				String path = filePath.getText();
				if(path.substring(path.lastIndexOf(".") + 1).equals("xls"))
				{
					InputStream is;
					int sheets=0;
					try
					{
						is = new FileInputStream(path);
						Workbook workbook = Workbook.getWorkbook(is);
						
						for(int i = 0;i <workbook.getNumberOfSheets();i++)
						{
							Sheet s = workbook.getSheet(i);
							Cell c = s.getCell(1,1);
							String s2 = c.getContents().trim();
							if(s2.equals(""))
							{
								break;
							}
							sheets++;
						}
						
					}
					catch (FileNotFoundException e1)
					{
						// TODO 自动生成 catch 块
						//e1.printStackTrace();
						text.append(e1.getMessage());
					}
					catch (BiffException e1)
					{
						// TODO 自动生成 catch 块
						//e1.printStackTrace();
						text.append(e1.getMessage());
					}
					catch (IOException e1)
					{
						// TODO 自动生成 catch 块
						//e1.printStackTrace();
						text.append(e1.getMessage());
					}
					catch (Exception e1)
					{
						// TODO 自动生成 catch 块
						//e1.printStackTrace();
						text.append(e1.getMessage());
						
					}
					finally
					{
						for (int i = 0;i<sheets;i++)
							parseExcel(filePath.getText(),i);
					}
				}
				else if(path.substring(path.lastIndexOf(".") + 1).equals("mdb"))
					parseAccess(filePath.getText());
			}
		});
		executeButton.setBounds(422, 72, 60, 30);
		executeButton.setText("Execute");

		final Label hostNameLabel_1 = new Label(this, SWT.HORIZONTAL);
		hostNameLabel_1.setBounds(10, 37, 87, 21);
		hostNameLabel_1.setFont(SWTResourceManager.getFont("Arial", 11, SWT.BOLD));
		hostNameLabel_1.setBackground(SWTResourceManager.getColor(192, 192, 192));
		hostNameLabel_1.setText("User");

		hostname_1 = new Text(this, SWT.BORDER);
		hostname_1.setBounds(180, 20, 0, 35);
		hostname_1.setFont(SWTResourceManager.getFont("", 12, SWT.NONE));

		user = new Text(this, SWT.BORDER);
		user.setBounds(103, 37, 136, 21);
		user.setFont(SWTResourceManager.getFont("", 12, SWT.NONE));

		final Label portLabel_1 = new Label(this, SWT.HORIZONTAL);
		portLabel_1.setBounds(250, 37, 111, 21);
		portLabel_1.setFont(SWTResourceManager.getFont("Arial", 11, SWT.BOLD));
		portLabel_1.setBackground(SWTResourceManager.getColor(192, 192, 192));
		portLabel_1.setText("Password");

		pwd = new Text(this, SWT.PASSWORD | SWT.BORDER);
		pwd.setBounds(357, 37, 125, 21);
		pwd.setFont(SWTResourceManager.getFont("", 12, SWT.NONE));

		final Label hostNameLabel_1_1 = new Label(this, SWT.HORIZONTAL);
		hostNameLabel_1_1.setBounds(10, 78, 87, 21);
		hostNameLabel_1_1.setFont(SWTResourceManager.getFont("Arial", 11, SWT.BOLD));
		hostNameLabel_1_1.setBackground(SWTResourceManager.getColor(192, 192, 192));
		hostNameLabel_1_1.setText("Excel Path");

		filePath = new Text(this, SWT.BORDER);
		filePath.setBounds(103, 75, 247, 27);
		filePath.setFont(SWTResourceManager.getFont("", 12, SWT.NONE));

		text = new Text(this, SWT.V_SCROLL | SWT.MULTI | SWT.BORDER | SWT.H_SCROLL);
		text.setFont(SWTResourceManager.getFont("", 10, SWT.NONE));
		text.setEditable(false);
		text.setBounds(10, 122, 472, 197);
		//
	}

	@Override
	protected void checkSubclass()
	{
		// Disable the check that prevents subclassing of SWT components
	}
	
	protected String parseExcel(String filePath,int sheetNum)
	{
		int columnNum = 0;
		int rowNum = 0;
		String buildText = "";
		String insertText = "";
		
		try
		{
			InputStream is = new FileInputStream(fileName);
			Workbook workbook = Workbook.getWorkbook(is);
			
			Sheet sheet = workbook.getSheet(sheetNum);
			buildText += "create table "
				+ fileName.substring(fileName.lastIndexOf("\\") + 1, fileName.lastIndexOf("."))
				+ sheet.getName()
				+ "(";
			
			Cell[] column = new Cell[sheet.getColumns()];
			//get column number
			for( int i = 0; i < sheet.getColumns(); i++)
			{
				if (sheet.getCell(i,0).getContents().trim().equals(""))
					break;
				column[i] = sheet.getCell(i,0);
				columnNum++;
			}
			//get Row number
			for( int j = 0; j < sheet.getRows(); j++)
			{
				if (sheet.getCell(0,j).getContents().trim().equals(""))
					break;
				rowNum++;
			}
			
			
			for( int i = 0; i < columnNum; i++)
			{
				if (sheet.getCell(i,0).getContents().trim().equals(""))
					break;
				column[i] = sheet.getCell(i,0);
				//last time
				if (i == columnNum - 1)
				{
					buildText += column[i].getContents() + " varchar(512))" ;
					break;
				}
				//common time
				buildText += column[i].getContents() + " varchar(512)" + ",";
			}
			executeQuery(buildText);
			
			for( int j = 1; j < rowNum;j++)
			{
				insertText = "insert into "
					+ fileName.substring(fileName.lastIndexOf("\\") + 1, fileName.lastIndexOf("."))
					+ sheet.getName()
					+ " values(";
				for( int i = 0; i < columnNum; i++)
				{
					if( i == columnNum-1)
					{
						insertText += "'" + sheet.getCell(i,j).getContents() + "')";
						break;
					}
					insertText += "'" + sheet.getCell(i,j).getContents() + "',";
				}
				executeQuery(insertText);
			}
			workbook.close();
			return buildText +""+ insertText;
		} 
		catch (BiffException e) {
			//   TODO   Auto-generated   catch   block   
			//e.printStackTrace();
			text.append(e.getMessage());
		} 
		catch (IOException e) {
			//   TODO   Auto-generated   catch   block   
			//e.printStackTrace();
			text.append(e.getMessage());
		}
		return "";
	}
	
	protected ResultSet executeQuery(String command)
	{
		ResultSet rs = null;
		DbConnection dbConnection;
		Connection con = null;
		Statement st = null;
		if(hostName.getText().trim().equals("") || serviceName.getText().trim().equals(""))
			dbConnection = new DbConnection("127.0.0.1","oradb");
		else
			dbConnection = new DbConnection(hostName.getText().trim(),serviceName.getText().trim());
		
		if(user.getText().trim().equals("") || pwd.getText().trim().equals(""))
			con = dbConnection.connectByJdbc("system","manager");
		else
			con = dbConnection.connectByJdbc(user.getText().trim(),pwd.getText().trim());
		
		try
		{
			st =con.createStatement();
			text.append(command +"\n");
			rs = st.executeQuery(command);
			
		}
		catch (SQLException e)
		{
			// TODO 自动生成 catch 块
			//e.printStackTrace();
			text.append(e.getMessage());
		}
		catch (Exception e)
		{
			// TODO 自动生成 catch 块
			//e.printStackTrace();
			return null;
		}
		try
		{
			con.close();
		}
		catch (SQLException e)
		{
			// TODO 自动生成 catch 块
			//e.printStackTrace();
			text.append(e.getMessage());
		}
		return rs;
	}
	
	
	public void parseAccess(String filePath)
	{   
		String buildText = "";
		String insertText = "";
		String columnName = "";
		String data = "";
		//String columnType = "";
		Statement st = null;
		Connection conn = null;
		AccessConnection acon = new AccessConnection(filePath);
		conn = acon.connectAccess();
		try
		{
			
			DatabaseMetaData m_DBMetaData = conn.getMetaData();
			ResultSet tableRet = m_DBMetaData.getTables(null, null,"%",new String[]{"TABLE"});
			while(tableRet.next())
			{
				String tableName = tableRet.getString("TABLE_NAME");
				//System.out.println( "\n" + tableName);
				ResultSet colRet = m_DBMetaData.getColumns(null,null, tableName,"%");
				buildText = "create table " + tableName + "(";
				while(true) {
					if (colRet.next())
					{
						columnName = colRet.getString("COLUMN_NAME");
						buildText += columnName + " varchar(512)" + ",";
						//columnType = colRet.getString("TYPE_NAME");
			
						//int datasize = colRet.getInt("COLUMN_SIZE");
			
						//int digits = colRet.getInt("DECIMAL_DIGITS");
			
						//int nullable = colRet.getInt("NULLABLE");
						
					}
					else
					{
						buildText = buildText.substring(0, buildText.length()-1);
						buildText += ")";
						System.out.println(buildText);
						executeQuery(buildText);
						break;
					}

				}
				
			
			}
			
			st = conn.createStatement();
			ResultSet rs = null;
			tableRet = m_DBMetaData.getTables(null, null,"%",new String[]{"TABLE"});
			while(tableRet.next())
			{
				String tableName = tableRet.getString("TABLE_NAME");
				//System.out.println( "\n" + tableName);
				ResultSet colRet = m_DBMetaData.getColumns(null,null, tableName,"%");
				
				rs = st.executeQuery("select * from " + tableName);
					while(rs.next()){
						//insertText = "insert into " + tableName + " values(";
						insertText = "insert into " + tableName + " values(";  
						colRet = m_DBMetaData.getColumns(null,null, tableName,"%");
						for(int i = 1;;i++)
						{
							if(!colRet.next())
							{
								insertText = insertText.substring(0, insertText.length()-1);
								insertText += ")";
								System.out.println(insertText);
								executeQuery(insertText);
								break;
							}
							data = rs.getString(i);
							insertText += "'" + data + "',";					
							
							
							//System.out.println(columnName);
						}
				
				}
				
			
			}
			st.close();
		}
		catch (SQLException e)
		{
			// TODO 自动生成 catch 块
			//e.printStackTrace();
			text.append(e.getMessage());
		}
		
		try
		{
			conn.close();
		}
		catch (SQLException e)
		{
			// TODO 自动生成 catch 块
			//e.printStackTrace();
			text.append(e.getMessage());
		}
        
	}      
		        
}