[JDBC] 2 table Join, group by 사용하는 방법 및 적용예제

-오라클 테이블 생성

sys계정에서 

JDBC JIn 2 table

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

public class workshop {

       public static void main(String[] args) throws ClassNotFoundException {

              String driver = "oracle.jdbc.driver.OracleDriver";

              String url = "jdbc:oracle:thin:@localhost:1521:orcl";

              String userid = "test";

              String passwd="test";

              Connection con= null;

              ResultSet rs= null;

              PreparedStatement pstmt= null;

              try {

                     Class.forName(driver);                   

                     con = DriverManager.getConnection(url,userid,passwd);

                     String sql ="";

                     Scanner scan = new Scanner(System.in);

                     System.out.println("매출 조회 메뉴 = [ 매출 일자 순 :1, 상품별 매출 순 :2 ] :");

                     int menu = scan.nextInt();

                     if(menu ==1) {

                           sql=" SELECT TO_CHAR(ODATE, 'YYYY-MM-DD') 영업일, SUM(O.QUANTITY*P.PRICE) 매출 "

                                         + "FROM D7_ORDER O JOIN D7_PRODUCT P USING(pid) GROUP BY ODATE ORDER BY 1";

                           

                     }else if(menu ==2) {

                           

                           sql="SELECT PNAME 상품명, SUM(O.QUANTITY*P.PRICE) 매출 "

                                         + "FROM D7_ORDER O JOIN D7_PRODUCT P USING(pid) GROUP BY PNAME ORDER BY 2 DESC";

                     }

                     

                     pstmt= con.prepareStatement(sql);

                     rs = pstmt.executeQuery();

                     System.out.println("--------------");

                     System.out.println(((menu==1)?"영업일":"상품명") + "\t\t\t매출");

                     System.out.println("--------------");

                     

                     while(rs.next()) {

                      System.out.println(rs.getString(1)+"\t\t"+rs.getString(2));

                           

                     }

                     System.out.println("------------------");

              } catch (SQLException e) {

                     

                     e.printStackTrace();

              }catch(ClassNotFoundException e) {

                     e.printStackTrace();

              }

              finally {

                     try {

                           if( rs != null) rs.close();

                           if( pstmt != null) pstmt.close();

                           if( con != null) con.close();

                     } catch (Exception e) {

                           e.printStackTrace();

                     }

              }

              

              }}

DB서버 클래스 분리

-Dept

package com.dto;

//  Dept테이블의 하나의 레코드 저장용도

public class Dept {

       private int deptno;

       private String dname;

       private String loc;

       

       public Dept() {

              super();

              // TODO Auto-generated constructor stub

       }

       public Dept(int deptno, String dname, String loc) {

              super();

              this.deptno = deptno;

              this.dname = dname;

              this.loc = loc;

       }

       public int getDeptno() {

              return deptno;

       }

       public void setDeptno(int deptno) {

              this.deptno = deptno;

       }

       public String getDname() {

              return dname;

       }

       public void setDname(String dname) {

              this.dname = dname;

       }

       public String getLoc() {

              return loc;

       }

       public void setLoc(String loc) {

              this.loc = loc;

       }

       @Override

       public String toString() {

              return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]"+"\n";

       }

       

       

       

       

}

=--

-OracleTxDAO

package com.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.print.attribute.standard.PresentationDirection;

import com.dto.Dept;

import com.exception.RecordNotFoundException;

import com.service.OracleTxService;

public class OracleTxDAO {

    public void update(Connection con,Dept xx)throws RecordNotFoundException {        

        PreparedStatement pstmt = null;

//        ResultSet rs = null;

//        Dept dept = new Dept();

        try {            

            String sql = "update dept set dname =?, loc=? where deptno = ?";

            pstmt = con.prepareStatement(sql);

            pstmt.setString(1, xx.getDname());

            pstmt.setString(2, xx.getLoc());

            pstmt.setInt(3, xx.getDeptno());

            int result = pstmt.executeUpdate();

            System.out.println("실행된 레코드 갯수: "+ result);

            

            

        }catch (SQLException e) {

            e.printStackTrace();

        }finally {

            try {

//                if( rs != null)rs.close();

                if( pstmt != null)pstmt.close();

//                if( con != null)con.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

            }

        

    }

    public void delete(Connection con,int i) {

        int result = 0;

        PreparedStatement pstmt = null;

//        ResultSet rs = null;

//        Dept dept = new Dept();

        try {

            

            String sql = "delete from dept where deptno=? ";

            pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, i);

            result = pstmt.executeUpdate();

            System.out.println("실행된 레코드 갯수: "+ result);

            

            

        }catch (SQLException e) {

            e.printStackTrace();

        }finally {

            try {

//                if( rs != null)rs.close();

                if( pstmt != null)pstmt.close();

                

            } catch (SQLException e) {

                e.printStackTrace();

            }

    }

    

    }

    public void insert(Connection con,Dept xx) {

        int result = 0;

        PreparedStatement pstmt = null;

//        ResultSet rs = null;

//        Dept dept = new Dept();

        try {            

            String sql = "insert into dept(deptno,dname, loc ) "+"values(?,?,?)";

            pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, xx.getDeptno());

            pstmt.setString(2, xx.getDname());

            pstmt.setString(3, xx.getLoc());        

            result = pstmt.executeUpdate();

            System.out.println("실행된 레코드 갯수: "+result);

            

        } catch (SQLException e) {

            e.printStackTrace();

        }finally {

            try {

//                if( rs != null)rs.close();

                if( pstmt != null)pstmt.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }            

            }

        

    }//end insert

    

    

    

public ArrayList<Dept> select(Connection con)throws SQLException {//con빠지고 service에서 받아서 만들어줌.

    //con사용

    //psts, rs만 close

    

    ArrayList<Dept> list = new ArrayList<Dept>();

    PreparedStatement pstmt = null;    

    ResultSet rs = null;

    String sql = "select deptno, dname, loc from dept";

    pstmt = con.prepareStatement(sql);

        rs = pstmt.executeQuery();

        while(rs.next()){

            Dept dept = new Dept();

            dept.setDeptno(rs.getInt(1));

            dept.setDname(rs.getString(2));

            dept.setLoc(rs.getString(3));

            list.add(dept);    

    }

        

            if( rs != null) rs.close();

            if( pstmt != null) pstmt.close();

        

            

    return list;

    

    }//end select

}

-OracleTxService

package com.service;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import com.dao.OracleTxDAO;

import com.dto.Dept;

import com.exception.RecordNotFoundException;

public class OracleTxService {

    String driver = "oracle.jdbc.driver.OracleDriver";

    String url = "jdbc:oracle:thin:@localhost:1521:orcl";

    String userid = "scott";

    String passwd = "tiger";

    OracleTxDAO dao;

    public OracleTxService() throws Exception {

        //드라이버 로딩

        //dao생성    

        dao = new OracleTxDAO();

        try {

            Class.forName(driver);

                    

        } catch (Exception e) {        

            e.printStackTrace();

        }

        

    }// end OracleDAO

    public ArrayList<Dept> select() throws SQLException {

        //db연결

        //selec호출 - con을 인자로 넘김.

        //connection 끊기

        //메인으로 결과 리턴

        Connection con = null;

//        PreparedStatement pstmt = null;

        ArrayList<Dept> list = null;

        try {

            con = DriverManager.getConnection(url, userid, passwd);        

            list = dao.select(con);    

            

        } finally {

            if(con != null)con.close();

        }

        

        return list;

    }// end select

    public void insert(Dept xx) throws Exception {

        Connection con = null;

        try {

            con = DriverManager.getConnection(url, userid, passwd);

            dao.insert(con, xx);

        } catch (Exception e) {

            System.out.println(e.getMessage());

        }finally {

            if(con != null)con.close();

        }         

    }// end insert

    

    public void delete(int xx) throws Exception {        

        Connection con = null;

        try {

            con = DriverManager.getConnection(url, userid, passwd);

            dao.delete(con, xx);

        } catch (Exception e) {

            System.out.println(e.getMessage());

        }finally {

            if(con != null)con.close();

        }

        

    }

    public void update(Dept xx2)throws Exception {

        Connection con = null;

        try {

            con = DriverManager.getConnection(url, userid, passwd);

            dao.update(con, xx2);

        } catch (Exception e) {

            System.out.println(e.getMessage());

        }finally {

            if(con != null)con.close();

        }

    

    }//end deletes

}

-OracleTxMain

import java.sql.SQLException;

import java.util.ArrayList;

import com.dto.Dept;

import com.exception.RecordNotFoundException;

import com.service.OracleTxService;

public class OracleTxMain {

    public static void main(String[] args) throws Exception  {

        // TODO Auto-generated method stub

OracleTxService service = new OracleTxService();

ArrayList<Dept> list= service.select();

System.out.println(list);

//

//service.insert(new Dept(91,"제조","경기"));

//service.update(new Dept(50,"제조","강원"));

//service.delete(91);

    }

}

-RecordNotFoundException

package com.exception;

public class RecordNotFoundException extends Exception {

       public RecordNotFoundException(String mesg) {

              super(mesg);

                     

       }

}

-TRANSACTION

public void insertDelete(Dept dept,int i) throws Exception {

              

              Connection con = null;

              try {

                     con = DriverManager.getConnection(url, userid, passwd);

                     

                     //기본적으로 자동 commit됨  con.setAutocommit(false)//con.setAutoCommet(true) true가 default됨.

                     //insert와 delete를 하나의 tx으로 묶음 tx의 시작

                     con.setAutoCommit(false);

                     dao.insert(con, dept);

                     dao.delete(con, i);

                     con.commit();//tx완료시                   

              }catch(SQLException e) {

                     con.rollback();//문제 발생 시

                     System.out.println(e.getMessage());

              }finally{

                     try {

                     if(con != null)con.close();

              }catch(SQLException e) {

       

                     System.out.println(e.getMessage());

              }

       

              }}

}