기록해! 정리해!

JSP - 학사관리프로그램(2) 학번/include/서치(검색창)/조회수 본문

JAVA/JSP

JSP - 학사관리프로그램(2) 학번/include/서치(검색창)/조회수

zsuling 2022. 7. 21. 12:31

07/21

 

+ 마지막 학번 추가하기

1. 메소드 추가하기

ExamtblService 랑 ExamtblDao에 int sno(); 추가하기

 

2. Impl에 메소드 추가하기

1)Service 

@Override
public int sno() {

return dao.sno();
}

2)Dao

private static final String SNO_SQL = "select max(sno)+1 as sno from Examtbl ";

.

.

.

@Override
public int sno() {
int sno =0;
 try {
   
  conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(SNO_SQL);
rs = pstmt.executeQuery();
rs.next();

   sno = Integer.parseInt(rs.getString("sno"));


} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
return sno;

 

3.TotalController에 추가하기

}else if(sw.equals("sno")) {
int sno = service.sno();

 request.setAttribute("sno", sno);
 str = "form.jsp";
}

 

4. 각jsp파일에 form 경로 수정해주기

<a href=TotalController?sw=sno>성적입력</a>

 

5. form.jsp 에 학번 가져오기

<%
 int sno = (int)request.getAttribute("sno");
%>

.

.

<tr><td width=80>학번</td><td><input type=text name=sno value=<%=sno %>></td></tr>

include 하기

1. top.jsp / bottom.jsp 생성

2. <!DOCTYPE html> 부터 </nav> 까지 - top.jsp 

    <footer>부터 </html>까지 - bottom.jsp 

3. <%@include file="top.jsp" %>   

    <%@include file="bottom.jsp" %>

 

서치하기 검색창...

1. Service랑 Dao에 selectAll이 매개변수를 받아야함.

List<ExamtblVo> selectAll(String ch1, String ch2);

 

2.  Impl에 메소드 추가하기

1)

 @Override
   public List<ExamtblVo> selectAll(String ch1, String ch2) {
      return dao.selectAll( ch1,  ch2);
2) String ch1, String ch2      

 

3. TotalController (파라미터 2개 넘기기) 

if(sw.equals("select")) {

 String ch1 = request.getParameter("ch1");
 String ch2 = request.getParameter("ch2");
 
 List<ExamtblVo> li = service.selectAll( ch1,  ch2);
 request.setAttribute("li", li);
 str = "list.jsp";

 

4. list.jsp에 form 추가하기

<form  action=TotalController>
<input  type=hidden name=sw value=select>
 <select name=ch1 >
   <option value = "sno" >학번</option>
   <option value = "sname" >성명</option>
 </select>
   <input type=text name=ch2>
   <input type=submit value="검색하기">
</form>  

 

5. ExamtblDaoImpl 쿼리문 추가하기

private static final String SELECT_SQL = "select * from Examtbl ";
private static final String SELECT_SQL_SNO = "select * from Examtbl where sno like ? ";
private static final String SELECT_SQL_SNAME = "select * from Examtbl where sname like ? ";

.

.

.

 try {
    li = new ArrayList<ExamtblVo>();
conn = DBConnection.getConnection();


 if(ch1 == null) {
pstmt=conn.prepareStatement(SELECT_SQL);
 }else if(ch1.equals("sno")) {
pstmt=conn.prepareStatement(SELECT_SQL_SNO);
pstmt.setString(1,"%"+ch2+"%");
 }else if(ch1.equals("sname")) {
 pstmt=conn.prepareStatement(SELECT_SQL_SNAME);
pstmt.setString(1,"%"+ch2+"%");
 }
 
rs = pstmt.executeQuery();

 

 

º Cnt

1. 오라클에 추가하기

alter table examtbl add cnt int default '1';

 

2.list.jsp에 

<th>조회수</th>

 int cnt = m.getCnt();

 <td><%=cnt %></td>

 

3. ExamtblVo 에 추가하기 (게터세터)

private int cnt;

 

4. ExamtblDaoImplntroller selectAll 에 

m.setCnt(rs.getInt("cnt")); 추가

 

- 학생을 클릭하면 조회수가 1씩 증가하기

 

1. 새로운 메소드 추가하기

void cnt(String sno);

2.   Impl에 메소드 추가하기

1)

@Override
public void cnt(String sno) {
       
dao.cnt(sno);
}

2) ExamtblDaoImpl

private static final String UPDATE_SQL_CNT = " update examtbl set cnt=cnt+1 where sno=? ";

.

.

.

@Override
public void cnt(String sno) {
 try {
conn = DBConnection.getConnection();

pstmt = conn.prepareStatement( UPDATE_SQL_CNT);

pstmt.setString(1, sno);
pstmt.executeUpdate();

} catch (Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}

 

3. TotalController edit 에 조회수 증가 찍기

}else if(sw.equals("edit")) {
 String sno = request.getParameter("sno");
 
 service.cnt(sno); // 조회 수 증가
 
 m = service.edit(sno);
 request.setAttribute("m", m);
 str = "edit.jsp";

º DBconn 에서 static void close 사용하기

 public static void close1(PreparedStatement pstmt, Connection conn) {
		          if (pstmt != null) {
		             try {
		                pstmt.close();
		             } catch (SQLException e) {
		                e.printStackTrace();
		             }
		          }
		          if (conn != null) {
		             try {
		                conn.close();
		             } catch (SQLException e) {
		                e.printStackTrace();
		             }
		          }
		       }
		       
		       
		       public static void close2(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		          if (rs != null) {
		             try {
		                rs.close();
		             } catch (SQLException e) {
		                e.printStackTrace();
		             }
		          }
		          
		          if (pstmt != null) {
		             try {
		                pstmt.close();
		             } catch (SQLException e) {
		                e.printStackTrace();
		             }
		          }
		          if (conn != null) {
		             try {
		                conn.close();
		             } catch (SQLException e) {
		                e.printStackTrace();
		             }
		          }

- ExamtblDaoImpl

: insert / update / delete / cnt  = close1

 

} finally {
DBConnection.close1(pstmt, conn);
}

 

: selectAll / edit / sno  =  close 2

} finally {
DBConnection.close2(rs, pstmt, conn);
}

º TotalController 중복 정리

 

 

package DBPKG.controller;

import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import DBPKG.ExamtblVo;
import DBPKG.service.ExamtblService;
import DBPKG.service.ExamtblServiceImpl;

/**
 * Servlet implementation class TotalController
 */
@WebServlet("/TotalController")
public class TotalController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public TotalController() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//response.getWriter().append("Served at: ").append(request.getContextPath());
	
		 String sw = request.getParameter("sw");
		 
		 ExamtblVo m = new ExamtblVo();
		 ExamtblService service = new ExamtblServiceImpl();
		 
		 String str = "";
		 
		 
		 if(sw.equals("insert")||sw.equals("update")) {
				
				String sno= request.getParameter("sno");
				String sname= request.getParameter("sname");
				int kor=Integer.parseInt(request.getParameter("kor"));
				int eng=Integer.parseInt(request.getParameter("eng"));
				int math=Integer.parseInt(request.getParameter("math"));
				int hist=Integer.parseInt(request.getParameter("hist"));
				
				m.setSno(sno);
				m.setSname(sname);
				m.setKor(kor);
				m.setEng(eng);
				m.setMath(math);
				m.setHist(hist);
				}
		 
		if(sw.equals("select")) {
			
			 String ch1 = request.getParameter("ch1");
			 String ch2 = request.getParameter("ch2");
			 
			 List<ExamtblVo> li = service.selectAll( ch1,  ch2);
			 request.setAttribute("li", li);
			 str = "list.jsp";
			
					 
		}else if(sw.equals("insert")) {
			        
			        service.insert(m);
			        
		            str="TotalController?sw=select";
			
		}else if(sw.equals("update")) {
		     
		        service.update(m);
		        
		        str="TotalController?sw=select";
		    
			
		}else if(sw.equals("delete")) {
			
			 String sno = request.getParameter("sno");		
		       
		        service.delete(sno);
		        
		        str="TotalController?sw=select";
		       
		}else if(sw.equals("edit")) {
			 String sno = request.getParameter("sno");
			 
			 service.cnt(sno); // 조회 수 증가
			 
			 m = service.edit(sno);
			 request.setAttribute("m", m);
			 str = "edit.jsp";
			 
		}else if(sw.equals("sno")) {
			int sno = service.sno();
			
			 request.setAttribute("sno", sno);
			 str = "form.jsp";
		}
			
		  RequestDispatcher dispatcher = request.getRequestDispatcher(str);
	        dispatcher.forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}
저작자표시 비영리 변경금지

'JAVA > JSP' 카테고리의 다른 글

JSP 교재  (0) 2022.08.02
JSP - HRD 싱글톤  (0) 2022.07.22
JSP - 학사관리프로그램  (0) 2022.07.20
서블릿(3)  (0) 2022.07.19
서블릿(2)  (0) 2022.07.19
'JAVA/JSP' Related Articles more
Comments