public class BoardDAO {
	Connection conn;
	private DataSource dataFactory;
	PreparedStatement pstmt;
	
	public BoardDAO() {
		try {
			Context ctx = new InitialContext();
			Context envctx = (Context)ctx.lookup("java:/comp/env");
			dataFactory = (DataSource) envctx.lookup("jdbc/oracle");
		} catch (NamingException e) {
			System.out.println("-----------------------------------");
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void mkdirdfs(int articleNO) {
		try {
			Class.forName("org.apache.hive.jdbc.HiveDriver");
			Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.30.120:10000/default", "hive", "hive");
			Statement stmt = conn.createStatement();
			String hdfs_dir="/user/hive/warehouse/"+articleNO;
			String sql = "dfs -mkdir "+hdfs_dir;
			
			System.out.println("----------sql(mkdirdfs) = "+sql);
			stmt.executeQuery(sql);
			System.out.println("");
			stmt.close();
			conn.close();
		
		} catch (ClassNotFoundException e) {
			System.out.println("class.forname");
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("connection");
		}
	}
	public void putdfs(String dir_name2,int articleNO,String FileName) {
		try {
			Class.forName("org.apache.hive.jdbc.HiveDriver");
			Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.30.120:10000/default", "hive", "hive");
			Statement stmt = conn.createStatement();
			String hdfs_dir="/user/hive/warehouse/"+articleNO;
			
			String dir_name = "/var/server_share/"+articleNO;
			String local_file=dir_name+"/"+FileName;
			String sql= "dfs -put "+local_file+" "+hdfs_dir;
			//String sql = "LOAD DATA LOCAL INPATH "+local_file+" OVERWRITE INTO TABLE "+articleNO;
			System.out.println("--------------sql(putdfs) = "+sql);
			
			stmt.executeQuery(sql);
			System.out.println("333333333333333333333333333333333333333333333333333");
			System.out.println("hdfs ϳֱ ");
		
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			System.out.println("class.forname");
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("connection");
		}
	}
	public void getdfs(String articleNO,String FileName) {
		try {
			Class.forName("org.apache.hive.jdbc.HiveDriver");
			Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.30.120:10000/default", "hive", "hive");
			Statement stmt = conn.createStatement();
			
			String hdfs_dir="/user/hive/warehouse/"+articleNO;
			String local_dir="/var/server_share";//Integer.toString(articleNO);
			
			String sql = "dfs -get "+hdfs_dir+" "+local_dir;
			stmt.executeQuery(sql);
			System.out.println("server ߰ Ϸ");
			
		} catch (ClassNotFoundException e) {
			System.out.println("class.forname");
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("connection");
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void rmrdfs(int articleNO) {
		try {
			Class.forName("org.apache.hive.jdbc.HiveDriver");
			Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.30.120:10000/default", "hive", "hive");
			Statement stmt = conn.createStatement();
			
			String hdfs_dir="/user/hive/warehouse/"+articleNO;
			
			String sql = "dfs -rmr "+hdfs_dir;
			stmt.executeQuery(sql);
			System.out.println("hadoop ϻ Ϸ");
			
		} catch (ClassNotFoundException e) {
			System.out.println("class.forname");
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("connection");
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void rmrfiledfs(int articleNO,String filename) {
		try {
			Class.forName("org.apache.hive.jdbc.HiveDriver");
			Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.30.120:10000/default", "hive", "hive");
			Statement stmt = conn.createStatement();
			
			String hdfs_file="/user/hive/warehouse/"+articleNO+"/"+filename;
			
			String sql = "dfs -rmr "+hdfs_file;
			stmt.executeQuery(sql);
			System.out.println("hadoop ϻ Ϸ");
			
		} catch (ClassNotFoundException e) {
			System.out.println("class.forname");
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("connection");
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public List selectAllArticles() {
		List articlesList = new ArrayList();
		try {
			conn = dataFactory.getConnection();
			String query = "select LEVEL,articleNO,parentNO,title,content,id,writeDate"+" from t_board2"
							+" START WITH parentNO=0 CONNECT BY PRIOR articleNO=parentNO";
							//+" ORDER SIBLINGS BY articleNO DESC";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				int level = rs.getInt("level");
				int articleNO = rs.getInt("articleNO");
				int parentNO = rs.getInt("parentNO");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String id = rs.getString("id");
				Date writeDate = rs.getDate("writeDate");
				ArticleVO article = new ArticleVO();
				article.setLevel(level);
				article.setArticleNO(articleNO);
				article.setParentNO(parentNO);
				article.setTitle(title);
				article.setContent(content);
				article.setId(id);
				article.setWriteDate(writeDate);
				articlesList.add(article);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return articlesList;
	}
	public List selectmemArticles(String id) {
		List<ArticleVO> articlesList = new ArrayList<ArticleVO>();
		
		try {
			conn = dataFactory.getConnection();
			String query = "select distinct * from t_title2 where id=?";
			//String query = "select * from t_title2 where id=?";
			System.out.println("--------------------------");
			System.out.println(query);
			System.out.println(id);
			System.out.println("--------------------------");
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				int articleNO = rs.getInt("articleNO");
				ArticleVO articleVO = new ArticleVO();
				articleVO.setArticleNO(articleNO);
				articlesList.add(articleVO);
				System.out.println("articleNO ==== "+articleNO);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return articlesList;
	}
	/*
	public List selectAllArticles() {
		List articlesList = new ArrayList();
		
		try {
			conn = dataFactory.getConnection();
			String query = "select LEVEL,articleNO,parentNO,title,content,id,writeDate"+" from t_board2"
							+" START WITH parentNO=0 CONNECT BY PRIOR articleNO=parentNO"
							+" ORDER SIBLINGS BY articleNO DESC";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				int level = rs.getInt("level");
				int articleNO = rs.getInt("articleNO");
				int parentNO = rs.getInt("parentNO");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String id = rs.getString("id");
				Date writeDate = rs.getDate("writeDate");
				ArticleVO article = new ArticleVO();
				article.setLevel(level);
				article.setArticleNO(articleNO);
				article.setParentNO(parentNO);
				article.setTitle(title);
				article.setContent(content);
				article.setId(id);
				article.setWriteDate(writeDate);
				articlesList.add(article);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return articlesList;
	}
	*/
	
	private int getNewArticleNo() {
		try {
			conn = dataFactory.getConnection();
			String query = "select max(articleNO) from t_board2";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery(query);
			if(rs.next())
				return (rs.getInt(1)+1);
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	public int insertNewArticle(ArticleVO article) {
		int articleNO = getNewArticleNo();
		try {
			conn = dataFactory.getConnection();
			int parentNO = article.getParentNO();
			String title = article.getTitle();
			String content = article.getContent();
			String id = article.getId();
			System.out.println("filename ========="+article.getFileName());
			String FileName = article.getFileName();
			String query = "INSERT INTO t_board2 (articleNO, parentNO, title, content, filename, id)"
						+" values (?,?,?,?,?,?)";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, articleNO);
			pstmt.setInt(2, parentNO);
			pstmt.setString(3, title);
			pstmt.setString(4, content);
			pstmt.setString(5, FileName);
			pstmt.setString(6, id);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return articleNO;
	}
	
	public ArticleVO selectArticle(int articleNO) {
		ArticleVO article = new ArticleVO();
		try {
			System.out.println("1 filename ========="+article.getFileName());
			conn = dataFactory.getConnection();
			String query = "select articleNO,parentNO,title,content,filename,id,writeDate"
					+" from t_board2"
					+" where articleNO=?";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, articleNO);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			int _articleNO = rs.getInt("articleNO");
			int parentNO = rs.getInt("parentNO");
			String title = rs.getString("title");
			String content = rs.getString("content");
			String FileName = rs.getString("FileName");
			String id = rs.getString("id");
			Date writeDate = rs.getDate("writeDate");
			
			article.setArticleNO(_articleNO);
			article.setParentNO (parentNO);
			article.setTitle(title);
			article.setContent(content);
			article.setFileName(FileName);
			article.setId(id);
			article.setWriteDate(writeDate);
			System.out.println("2 filename ========="+article.getFileName());
			
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return article;
	}

	public void updateArticle(ArticleVO article) {
		int articleNO = article.getArticleNO();
		String title = article.getTitle();
		String content = article.getContent();
		String FileName = article.getFileName();
		
		try {
			conn=dataFactory.getConnection();
			String query = "update t_board2 set title=?,content=?";
			if(FileName != null && FileName.length()!=0) {
				query +=",FileName=?";
			}
			query += " where articleNO=?";
			
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, title);
			pstmt.setString(2, content);
			if(FileName != null && FileName.length()!=0) {
				pstmt.setString(3, FileName);
				pstmt.setInt(4, articleNO);
			}else {
				pstmt.setInt(3, articleNO);
			}
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void deleteArticle(int articleNO) {
		try {
			conn = dataFactory.getConnection();
			String query = "DELETE FROM t_board2";
			query += " WHERE articleNO in (";
			query += " select articleNO from t_board2";
			query += " START WITH articleNO = ?";
			query += " CONNECT BY PRIOR articleNO = parentNO )";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, articleNO);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	public List<Integer> selectRemovedArticles(int articleNO){
		List<Integer> articleNOList = new ArrayList<Integer>();
		try {
			conn = dataFactory.getConnection();
			String query = "select articleNO from t_board2";
			query += " START WITH articleNO = ?";
			query += " CONNECT BY PRIOR articleNO=parentNO";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, articleNO);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				articleNO = rs.getInt("articleNO");
				articleNOList.add(articleNO);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return articleNOList;
	}
}