package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import dto.SchoolDTO;

public class SchoolDAO {
	private String url = "jdbc:oracle:thin:@localhost:1521:xe";
	private String id = "DBTEST";
	private String pw = "1234";
	
	public SchoolDAO() {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			System.out.println("로딩 성공");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public boolean insert(SchoolDTO dto) {
		Connection con = null;
		PreparedStatement pstmt = null;
		boolean check = false;
		
		try {
			String sql = "INSERT INTO school VALUES(?,?,?)";
			con = DriverManager.getConnection(url,id,pw);
			pstmt= con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getValue());
			pstmt.setInt(3, dto.getCode());
			int su=pstmt.executeUpdate();
			if (su > 0 ) {
				check = true;
				return check;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt!=null) pstmt.close();
				if(con!=null) con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return check;
	}
	
	// 목 록
	public ArrayList<SchoolDTO> getList() {
		
		ArrayList<SchoolDTO> list = new ArrayList<>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		
		try {
			String sql = "SELECT * FROM school";
			con=DriverManager.getConnection(url,id,pw);
			pstmt=con.prepareStatement(sql);
			res=pstmt.executeQuery();
			
			while(res.next()) {
				String name = res.getString("name");
				String value = res.getString("value");
				int code = res.getInt("code");
				
				list.add(new SchoolDTO(name,value,code));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(res!=null) res.close();
				if(con!=null) con.close();
				if(pstmt!=null) pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(list.isEmpty()) {
			list = null;
		}
		return list;
	}
	// getList() end
	
}