[14] 성적테이블 DAO

 

[성적테이블 DAO]

 

파일명 : SungjukDAO.java

-------------------------------------------------------------

package www.sungjuk;

 

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

 

import www.utility.DBClose;
import www.utility.DBConnect;

 

public class SungjukDAO {
    DBConnect dbconnect=null;

    public SungjukDAO() {
        dbconnect=new DBConnect();
    }
   
    //목록
    public ArrayList list(){
        Connection con=dbconnect.getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        StringBuffer sql=null;
        ArrayList list=null;
       
        sql=new StringBuffer();
        sql.append(" SELECT sno,uname,kor,eng,mat,aver,address ");
        sql.append(" FROM tb_sungjuk ");
        sql.append(" ORDER BY sno DESC ");
       
        list=new ArrayList();
        try{
            pstmt=con.prepareStatement(sql.toString());
            rs=pstmt.executeQuery();
            while(rs.next()){
                SungjukDTO dto=new SungjukDTO();
                dto.setSno(rs.getInt("sno"));
                dto.setUname(rs.getString("uname"));
                dto.setKor(rs.getInt("kor"));
                dto.setEng(rs.getInt("eng"));
                dto.setMat(rs.getInt("mat"));
                dto.setAver(rs.getInt("aver"));
                dto.setAddress(rs.getString("address"));
                list.add(dto);
            }
        }catch(Exception e){
            System.out.print(e.toString());
           
        }finally{
            DBClose.close(con,pstmt,rs);
        }
       
        return list;
    }
   
    //추가
    public int create(SungjukDTO dto){
        Connection con=dbconnect.getConnection();
        PreparedStatement pstmt=null;
        StringBuffer sql=null;
        int cnt=0;
       
        sql=new StringBuffer();
        sql.append(" INSERT INTO tb_sungjuk(uname,kor,eng,mat,aver,address) ");
        sql.append(" VALUES(?,?,?,?,?,?) ");
       
        try{
            pstmt=con.prepareStatement(sql.toString());
            pstmt.setString(1,dto.getUname());
            pstmt.setInt(2,dto.getKor());
            pstmt.setInt(3,dto.getEng());
            pstmt.setInt(4,dto.getMat());
            pstmt.setInt(5,dto.getAver());
            pstmt.setString(6,dto.getAddress());
            cnt=pstmt.executeUpdate();
               
        }catch(Exception e){
            System.out.print(e.toString());
        }finally{
            DBClose.close(con,pstmt);
        }
        return cnt;
    }

  

    //상세보기

    public SungjukDTO read(int sno){
        Connection con = dbconnect.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        SungjukDTO dto = null;

        sql = new StringBuffer();

        // 한건의 레코드를 읽어옴.
        sql.append(" SELECT sno,uname,kor,eng,mat,aver,address ");
        sql.append(" FROM tb_sungjuk ");
        sql.append(" WHERE sno = ? ");
                   
        try{
            pstmt = con.prepareStatement(sql.toString());
            pstmt.setInt(1, sno);
           
            rs = pstmt.executeQuery();
           
            if (rs.next() == true){
                dto = new SungjukDTO();               
                dto.setSno(rs.getInt("sno"));
                dto.setUname(rs.getString("uname"));
                dto.setKor(rs.getInt("kor"));
                dto.setEng(rs.getInt("eng"));
                dto.setMat(rs.getInt("mat"));
                dto.setAver(rs.getInt("aver"));
                dto.setAddress(rs.getString("address"));
            }
        }catch(Exception e){
            System.out.println(e.toString());
        }finally{
            DBClose.close(con, pstmt, rs);
        }
        //----------------------------------------------------------------
       
        return dto;
    }

 

    //수정하기

    public int update(SungjukDTO dto){
        Connection con = dbconnect.getConnection();
        PreparedStatement pstmt = null;
        StringBuffer sql = null;
        int cnt = 0; //
       
        sql = new StringBuffer();
       
        sql.append(" UPDATE tb_sungjuk ");
        sql.append(" SET uname = ? ");
        sql.append(" ,kor = ? ");
        sql.append(" ,eng = ? ");
        sql.append(" ,mat = ? ");
        sql.append(" ,aver = ? ");
        sql.append(" ,address = ? ");
        sql.append(" WHERE sno = ?");

        try{
            pstmt = con.prepareStatement(sql.toString());

            //폼의 값을 가져옵니다.
            pstmt.setString(1,dto.getUname());
            pstmt.setInt(2,dto.getKor());
            pstmt.setInt(3,dto.getEng());
            pstmt.setInt(4,dto.getMat());
            pstmt.setInt(5,dto.getAver());
            pstmt.setString(6,dto.getAddress());
            pstmt.setInt(7, dto.getSno());
            cnt = pstmt.executeUpdate();
           
        }catch(Exception e){
            System.out.println(e.toString());
        }finally{
            DBClose.close(con, pstmt);
        }
 
        return cnt;
    }

 

 

    //삭제하기

    public int delete(int sno){
        Connection con = dbconnect.getConnection();
        PreparedStatement pstmt = null;
        StringBuffer sql = null;
        int cnt = 0;

        sql = new StringBuffer();
       
        sql.append(" DELETE FROM tb_sungjuk ");
        sql.append(" WHERE sno = ?");
       
        try{
            pstmt = con.prepareStatement(sql.toString());

            //폼의 값을 가져온다.
            pstmt.setInt(1, sno);

            cnt = pstmt.executeUpdate();
        }catch(Exception e){
            System.out.println(e.toString());
        }finally{
            DBClose.close(con, pstmt);
        }

        return cnt;
    }   

} 

-------------------------------------------------------------