package getandset;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class oracleServlet extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public oracleServlet() {
		super();
	}

	
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
		
	}

	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String[] index=request.getParameter("checkIndex").split(",");
		String orcName=request.getParameter("orcName");
		String orcPwd=request.getParameter("orcPwd");
		String orcDb=request.getParameter("orcDb");
		String orcIp=request.getParameter("orcIp");
		String orcPort=request.getParameter("orcPort");
		ArrayList al=(ArrayList)(request.getSession().getAttribute("tables"));//获取数据库中的所有表名
		//获得一个sql server数据库连接。
		SqldbAccess sdba=(SqldbAccess)(request.getSession().getAttribute("sqlcon"));
		//获取一个oracle数据库连接
		OrcdbAccess odba=OrcdbAccess.getDbAccess();
		odba.setValue(orcIp, orcPort, orcDb, orcName, orcPwd);
		for(int i=0;i<index.length;i++){
			String sql="select   a.name FROM syscolumns a join sysobjects b on a.id=b.id and b.xtype='U' and b.name<>'dtproperties' where exists"
							+" (SELECT 1 FROM sysobjects where xtype='PK' and name in "
							+" (SELECT name FROM sysindexes WHERE indid in( "
							+" SELECT indid FROM sysindexkeys WHERE id=a.id AND colid=a.colid))) and b.name='"+al.get(Integer.parseInt(index[i]))+"' ";
			String primaryKey=sdba.getFirstUnit(sql);//得到本次操作的表的主键字符串。
			String newTableSql="create table "+al.get(Integer.parseInt(index[i]))+"(";
			//获取以前的表的字段名及类型和长度。
            ResultSet rSet=sdba.executeResultSet("select c.name,c.length,t.name from  SysColumns c left join  systypes t on c.xtype=t.xtype "
		        +" where ID=OBJECT_ID('"+al.get(Integer.parseInt(index[i]))+"') Order By ColID ");
			String colName;//字段名字
			String colType;//字段类型
			int typeLength;//类型长度
			
			try {//开始取得建表语句。
				while(rSet.next()){
					colName=rSet.getString(1);//获取字段的名字
					typeLength=rSet.getInt(2);//获取字段的 长度
					colType=rSet.getString(3);//获取类型的名字
					//更新建表语句
					if(colType.contains("char")||colType.equals("datetime")){//如果是字符串类型或日期型，则需要指定长度。
						newTableSql+=colName+" varchar2("+typeLength+")";
					}else{//如果不是字符串类型，则不需要指定长度。
						newTableSql+=colName+" "+colType;
					}
					if(colName.equals(primaryKey)){//如果该字段是主键
						newTableSql+=" primary key,";
					}else {
						newTableSql+=" ,";
					}
				}
				rSet.close();
				//把所有的字段都添加进后，完善建表语句。
				newTableSql=newTableSql.substring(0,newTableSql.length()-1)+")";
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println(e.getMessage());
				e.printStackTrace();
			}
			//以以前的表表名在oracle中建一个相同名字的表
			try{
			odba.getInt(newTableSql);//建表
			}catch (Exception e) {
				System.out.println("oracle建表失败，请确认是否已打开oracle服务!");
				// TODO: handle exception
			}
			//从sql server取出数据并导入oracle.
			String orlInsert;
			ResultSet rs=sdba.executeResultSet("select * from "+al.get(Integer.parseInt(index[i])));//查询表的所有内容。
			try {
				while(rs.next()){
					orlInsert="insert into "+al.get(Integer.parseInt(index[i]))+" values(";
					for(int j=1;j<rs.getMetaData().getColumnCount()+1;j++){
						String typeCheck=rs.getMetaData().getColumnTypeName(j);
						if(typeCheck.contains("char")||typeCheck.equals("datetime")){
							orlInsert+="'"+rs.getString(j)+"',";
						}else {
							orlInsert+=rs.getString(j)+",";
						}
					}
				//完善insert语句：
					orlInsert=orlInsert.substring(0,orlInsert.length()-1)+")";
					System.out.println(orlInsert+"|");
					odba.getInt(orlInsert);
				}
				System.out.println(al.get(Integer.parseInt(index[i]))+"转移成功！");
				rs.close();	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		sdba.close();
		odba.close();
		response.sendRedirect("../Success.jsp");
	}

	public void init() throws ServletException {
		// Put your code here
	}

}