국비지원학원/HTML,JavaScript,jQ

91일차-JavaEE_DBCP

초코맛 2019. 3. 14. 02:07
반응형
*DBCP(DataBase Connection Pool)
  • 사용할 Project의 <Context> 하위에 기술
  • <Context       >
  •    <Resource name="jdbc/DBCP명"/>
  • </Context>
  • 주의)
  • 사용전 카타리나 홈에 ojdbc6.jar를 넣어 준다
 



<<<<DBCP 설정>>>
 Servers>Tomcat~>server.xml페이지중 제일 하단에 아래의 코드와 일치하는 부분에 같은 구성으로 수정해준다.
<Resource name="jdbc/myoracle" --java에서 JNDI를 사용하여 찾을 이름
    auth="Container"
            type="javax.sql.DataSource" --DBCP에서 DB와 연결할 객체
    driverClassName="oracle.jdbc.OracleDriver" --연동에 사용할 Driver Class
            url="jdbc:oracle:thin:@127.0.0.1:1521:mysid" --DB와 연동할 DB의 URL
            username="scott" --DBO(owner)의 ID
    password="tiger" --DBO의 Password
    maxTotal="20"  --maxIdle이 모두 사용 중 일때 Connnection의 갯수를 설정한 갯수까지 늘려서 서비스 하기위해 사용하는 속성
                                                                                    --(넘어가면 죽는다)->Close가 중요!
    maxIdle="10"  --Connection을 연결할(유지할) 갯수
            maxWaitMillis="-1" --Connection을 요청했을 때 대기할 시간 설정 (-1 : 즉시)
/>
 server.xml을 수정하면 web.xml 에 resource-ref 추가 안해도 된다.(?)
 
 
<<사용 예>>
package day0306;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@SuppressWarnings("serial")
public class UseDBCP extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out=response.getWriter();
        
          out.write("<!DOCTYPE html>\r\n");
          out.write("<html>\r\n");
          out.write("<head>\r\n");
          out.write("<meta charset=\"UTF-8\">\r\n");
          out.write("<title>Insert title here</title>\r\n");
          out.write("<link rel=\"stylesheet\" type=\"text/css\" href=\"http://localhost:8080/servlet_prj/common/css/main_v190130.css\"/>\r\n");
          out.write("<style type=\"text/css\">\r\n");
          out.write("#wrap{ margin:0px auto; width:800px; height: 860px;  }\r\n");
          out.write("#header{  width:800px; height: 140px; background: #FFFFFF url(http://localhost:8080/servlet_prj/common/images/header_bg.png) repeat-x;\r\n");
          out.write("\t\t\tposition: relative; }\r\n");
          out.write("#headerTitle{ font-family: HY견고딕, 고딕; font-size: 30px; font-weight: bold;text-align: center;\r\n");
          out.write("\t\t\t\t\tposition: absolute; top:30px; left:290px}\r\n");
          out.write("#container{  width:800px; height: 600px; }\r\n");
          out.write("#footer{  width:800px; height: 120px; }\r\n");
          out.write("#footerTitle{ float:right; font-size: 15px; padding-top:20px; padding-right: 20px }\r\n");
          out.write("</style>\r\n");
          out.write("</head>\r\n");
          out.write("<body>\r\n");
          out.write("<div\tid=\"wrap\">\r\n");
          out.write("\t<div id=\"header\">\r\n");
          out.write("\t\t<div id=\"headerTitle\">SIST Class4</div>\r\n");
          out.write("\t</div>\r\n");
          out.write("\t<div id=\"container\">\r\n");
          
          //DBCP사용
          //1.JDNI 사용 객체 생성
          try {
            Context ctx=new InitialContext();
            //2.이름으로 객체 찾기
            DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/dbcp");
            //3.Connection 얻기
            Connection con=ds.getConnection();
            out.print("DB연동 성공"+con);
            
            con.close();//끊어진 커녁션은 다시 Pool로 들어가서 다음번에 재 사용 된다.
            
            } catch (NamingException ne) {
                out.println("죄송합니다.<br/>");
                out.println("<img src='common/images/img.png'/>");
            ne.printStackTrace();
          } catch (SQLException e) {
              out.println("죄송합니다.<br/>");
              out.println("<img src='common/images/img2.png'/>");
            e.printStackTrace();
          }//end catch
          
          out.write("\t</div>\r\n");
          out.write("\t<div id=\"footer\">\r\n");
          out.write("\t\t<div id=\"footerTitle\">copyright&copy; all right reserved. class 4 </div>\r\n");
          out.write("\t</div>\r\n");
          out.write("</div>\r\n");
          out.write("\r\n");
          out.write("</body>\r\n");
          out.write("</html>\r\n");
    }//doGet
}//class

public static void main(String[] args){

UseDBCP ud=new UseDBCP();

ud.test();

}//main으로 테스트. 

 

//test();를 doget으로 열땐 consol창에 잘 출력 되었는데,
//application으로 java를 실행했을땐 JVM에 instance가 올라가는것이고
//Tomcat을 돌리는것은 JVM->Tomcat ->DBCP가 실행되는것으로 연동이 성공되는것!
//Tomcat은 inconteinerTest : 이 환경에서만 실행되!!

DAO : Data Access Object
VO : Value Object( getter )
DTO : Data Trance Object( getter/setter )//VO와 비슷, 밖에서 바꿀 필요가 있을때




package day0306;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@SuppressWarnings("serial")
public class SelectEmp extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out=response.getWriter();
        
          out.write("<!DOCTYPE html>\r\n");
          out.write("<html>\r\n");
          out.write("<head>\r\n");
          out.write("<meta charset=\"UTF-8\">\r\n");
          out.write("<title>Insert title here</title>\r\n");
          out.write("<link rel=\"stylesheet\" type=\"text/css\" href=\"http://localhost:8080/servlet_prj/common/css/main_v190130.css\"/>\r\n");
          out.write("<style type=\"text/css\">\r\n");
          out.write("#wrap{ margin:0px auto; width:800px; height: 860px;  }\r\n");
          out.write("#header{  width:800px; height: 140px; background: #FFFFFF url(http://localhost:8080/servlet_prj/common/images/header_bg.png) repeat-x;\r\n");
          out.write("\t\t\tposition: relative; }\r\n");
          out.write("#headerTitle{ font-family: HY견고딕, 고딕; font-size: 30px; font-weight: bold;text-align: center;\r\n");
          out.write("\t\t\t\t\tposition: absolute; top:30px; left:290px}\r\n");
          out.write("#container{  width:800px; height: 600px; }\r\n");
          out.write("#footer{  width:800px; height: 120px; }\r\n");
          out.write("#footerTitle{ float:right; font-size: 15px; padding-top:20px; padding-right: 20px }\r\n");
          
          out.println("th{background-color:#141650; }");
          out.println("span{color:#FFFFFF; font-weight:bold; font-size:14px;}");
          out.println("td{border:1px solid #141650; text-align:center }");
          out.println("table{border-spacing :0px}");
          
          out.write("</style>\r\n");
          out.write("</head>\r\n");
          out.write("<body>\r\n");
          out.write("<div\tid=\"wrap\">\r\n");
          out.write("\t<div id=\"header\">\r\n");
          out.write("\t\t<div id=\"headerTitle\">SIST Class4</div>\r\n");
          out.write("\t</div>\r\n");
          out.write("\t<div id=\"container\">\r\n");

          out.println("<table>");
          out.println("<tr>");
          out.println("<th width='80'><span>번호</span></th>");
          out.println("<th width='120'><span>사원번호</span></th>");
          out.println("<th width='150'><span>사원명</span></th>");
          out.println("<th width='80'><span>매니저번호</span></th>");
          out.println("<th width='100'><span>직무</span></th>");
          out.println("<th width='100'><span>연봉</span></th>");
          out.println("<th width='150'><span>입사일</span></th>");
          out.println("</tr>");
          
          List<EmpVO> list=searchAllEmp();
          if(list == null) { //DB에서 문제가 발생한 경우
              out.println("<tr><td colspan='7'>뎨둉합니다..문제가 발생하였습니다.</td></tr>");
          }else {
              EmpVO ev=null;
              for(int i=0; i<list.size(); i++) {
                  ev=list.get(i);
                  
                  out.println("<tr>");
                  
                  out.print("<td>");
                  out.print(i+1);
                  out.print("</td>");
                  
                  out.print("<td>");
                  out.print(ev.getEmpno());
                  out.print("</td>");
                  
                  out.print("<td>");
                  out.print(ev.getEname());
                  out.print("</td>");
                  
                  out.print("<td>");
                  out.print(ev.getMgr());
                  out.print("</td>");
                  
                  out.print("<td>");
                  out.print(ev.getJob());
                  out.print("</td>");
                  
                  out.print("<td>");
                  out.print(ev.getSal());
                  out.print("</td>");
                  
                  out.print("<td>");
                  out.print(ev.getHiredate());
                  out.print("</td>");
                  
                  out.println("</tr>");
              }//end for
              
              if(list.isEmpty()) {
                  out.print("<tr><td colspan='7'>사원정보가 존재하지 않습니다.<br/>");
                  out.print("<img src='common/images/img2.jpg' title='#일안하냐? #사원관리'/>");
                  out.println("</td></tr>");
              }//end if
              
          }//end else
          
          out.println("</table>");
          
          out.write("\t</div>\r\n");
          out.write("\t<div id=\"footer\">\r\n");
          out.write("\t\t<div id=\"footerTitle\">copyright&copy; all right reserved. class 4 </div>\r\n");
          out.write("\t</div>\r\n");
          out.write("</div>\r\n");
          out.write("\r\n");
          out.write("</body>\r\n");
          out.write("</html>\r\n");
    }//doGet
    
    public List<EmpVO> searchAllEmp(){
        List<EmpVO> list=null;
        
        EmpDAO ed=new EmpDAO();
        try {
            list=ed.selectAllEmp();
        } catch (SQLException e) {
            
            e.printStackTrace();
        }//end catch

        return list;
    }//searchAllEmp

}//class

package day0306;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class EmpDAO {
    public List<EmpVO> selectAllEmp() throws SQLException{
        List<EmpVO> list= new ArrayList<>();
        
        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        
        try {
            try {
                //1.JNDI 사용객체 생성
                Context ctx=new InitialContext();
                //2.DataSource를 DBCP에서 꺼내온다.
                DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/dbcp");
                //3.Connection 얻기
                con=ds.getConnection();
            } catch (NamingException ne) {
                ne.printStackTrace();
            }//end catch
        //4.쿼리문 수행 객체 얻기
            String selectEmp="select empno,mgr,sal,ename,job,to_char(hiredate,'yyyy-mm-dd dy') hiredate from emp order by sal";
            pstmt=con.prepareStatement(selectEmp);
            
        //5.쿼리문 수행 후 결과 얻기
            rs=pstmt.executeQuery();
            EmpVO ev=null;
            while(rs.next()) {
                ev=new EmpVO(rs.getInt("empno"),rs.getInt("mgr"),rs.getInt("sal"),rs.getString("ename"),
                        rs.getString("job"),rs.getString("hiredate"));
                list.add(ev);
            }//end while
        }finally {
            //6.연결 끊기
            if(rs !=null) {rs.close();}//end if
            if(pstmt !=null) {pstmt.close();}//end if
            if(con !=null) {con.close();}//end if
        }//end finally
        return list;
    }//selectAllEmp
}//class

package day0306;
public class EmpVO {
     private int empno,mgr,sal;
     private String ename,job,hiredate;
     
     public EmpVO(int empno, int mgr, int sal, String ename,  String job, String hiredate) {
           this.empno = empno;
           this.mgr = mgr;
           this.sal = sal;
           this.ename = ename;
           this.job = job;
           this.hiredate = hiredate;
     }//EmpVO
     public int getEmpno() {
           return empno;
     }
     public int getMgr() {
           return mgr;
     }
     public int getSal() {
           return sal;
     }
     public String getEname() {
           return ename;
     }
     public String getJob() {
           return job;
     }
     public String getHiredate() {
           return hiredate;
     }
     @Override
     public String toString() {
           return "EmpVO [empno=" + empno + ", mgr=" + mgr + ",  sal=" + sal
                     + ", ename=" + ename + ", job=" + job + ",  hiredate=" + hiredate + "]";
     }    
     
}







<<<<<member_join->jQuery로 수정해보기~언젠가~>>>>>>
<<<비연결성 문제를 해결하기 위한 HTTP Servlet classDiagram이랑 흐름도>내일 이어서 >>>


 

반응형
저작자표시 비영리 변경금지

'국비지원학원 > HTML,JavaScript,jQ' 카테고리의 다른 글

92일차-JavaEE_암호화+이어서 비연결성 해결  (0) 2019.03.26
90일차-JavaEE_popup/DBCP  (0) 2019.03.12
89일차-JavaEE_Session/Cookie  (0) 2019.03.09
88일차-JavaEE_parameter처리/페이지 이동(forward,redirect)  (0) 2019.03.09
87일차-JavaEE_HttpServletRequest  (0) 2019.03.08

'국비지원학원/HTML,JavaScript,jQ'의 다른글

  • 현재글91일차-JavaEE_DBCP

관련글

티스토리툴바