• SchoolDAO.java

package school.dao;

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

import school.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
	
	
	// 검 색
	public void search(SchoolDTO dto) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		
		try {
			String sql = null;
			if(dto == null) {
				sql = "select * from school";
			} else if ( dto.getName() != null) {
				sql = "select * from school where name like ?";
			} else {
				sql = "select * from school where code=?";
			}
			con = DriverManager.getConnection(url,id,pw);
			pstmt = con.prepareStatement(sql);
			if (dto != null) {
				if(dto.getName() != null) {
					pstmt.setString(1, "%" + dto.getName()+"%");;
				} else {
					pstmt.setInt(1, dto.getCode());
				}
			}
			res = pstmt.executeQuery();
			while(res.next()) {
				String name = res.getString("name");
				String value = res.getString("value");
				int code = res.getInt("code");
				if(code == 1) {
					System.out.print("학번 : " + value);
				} else if (code == 2) {
					System.out.print("과목 : " + value);
				} else {
					System.out.print("부서 : " + value);
				}
			}
		} 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();
			}
		}
		
	}
	// search() end
}