dbconnector.java

数据库课程lab。通过java下的xmlAPI实现xml文件数据库到oracle数据的迁移。

package logic;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.linuxense.javadbf.DBFField;

public class DBConnector {
	
	private String url = "jdbc:oracle:thin:@10.132.140.125:1521/test1.lab";

	private String userid = "system";

	private String pwd = "aaaaaa";

	private Connection conn;

	private Statement stmt;

	public DBConnector() {
		// TODO Auto-generated constructor stub

	}

	public DBConnector(String url, String userid, String pwd) throws Exception {
		this.url = "jdbc:oracle:thin:@"+url;
		this.userid = userid;
		this.pwd = pwd;
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		conn = DriverManager.getConnection(this.url,this.userid, this.pwd);
		stmt = conn.createStatement();

	}

	public boolean execute(String sql) throws SQLException {

		stmt.executeUpdate(sql);
		return true;

	}

	public ResultSet select(String sql) throws SQLException {

		ResultSet rset = stmt.executeQuery(sql);
		return rset;

	}

	public boolean close() throws SQLException {

		stmt.close();
		conn.close();
		return true;

	}
	
	public String[] getXls(File f){
		String [] sqls =null;
		ReadExcel re = new ReadExcel();
		String[][] result = re.readExcel(f.getAbsolutePath());
		//System.out.println(f.getName().substring(0, f.getName().lastIndexOf(".")));
		String tableName = f.getName().substring(0, f.getName().lastIndexOf("."));
		String tableAttributes = "";
		for (int i = 0;i < result[0].length;i++ ){
			if(i != result[0].length-1){
				tableAttributes = tableAttributes+result[0][i]+"  varchar(30),\n";
			}else{
				tableAttributes = tableAttributes+result[0][i]+"  varchar(30)";
			}
		}
		//System.out.print(tableAttributes);
		String createString= "create table " + tableName + "("+tableAttributes + ")";
		//System.out.print(createString);
		sqls = new String[result.length];
		sqls[0]= createString;
		String values = "";
		for(int i = 1;i < result.length;i++ ){
			for(int j = 0;j <result[0].length; j++){
				if(j != result[0].length-1){
					values = values + "'"+result[i][j]+"',";
				}else{
					values = values + "'"+result[i][j]+"'";
				}
			}
			sqls[i] = "insert into "+tableName + " values("+values+")";
			values = "";
			//System.out.println(sqls[i]);
		}
		
		return sqls;
		
	}
	public String[] getDbf(File f){
		String[] sqls = null;
		ReadDbf rd = new ReadDbf();
		Object[][] result = rd.readDbf(f.getAbsolutePath());
		String tableName = f.getName().substring(0, f.getName().lastIndexOf("."));
		//String x = (char)a.getDataType()+"\n"+a.getFieldLength()+"\n"+a.getName()+"\n";
		//System.out.print(x);
		String tableAttributes = "";
		for (int i = 0;i < result[0].length;i++ ){
			if(i != result[0].length-1){
				tableAttributes = tableAttributes+((DBFField)result[0][i]).getName()+"  varchar(30),\n";
			}else{
				tableAttributes = tableAttributes+((DBFField)result[0][i]).getName()+"  varchar(30)";
			}
		}
		String createString= "create table " + tableName + "("+tableAttributes + ")";
		sqls = new String[result.length];
		sqls[0]= createString;
		String values = "";
		for(int i = 1;i < result.length;i++ ){
			for(int j = 0;j <result[0].length; j++){
				if(j != result[0].length-1){
					values = values + "'"+(""+result[i][j]).replaceAll("'", "''")+"',";
				}else{
					values = values + "'"+(""+result[i][j]).replaceAll("'", "''")+"'";
				}
			}
			sqls[i] = "insert into "+tableName + " values("+values+")";
			values = "";
		}
		return sqls;
	}
}