JSP 5일 (23.03.29)

Jane·2023년 3월 29일
Javahtmljspsql
0

IT 수업 정리

목록 보기
92/124

1. 예외 페이지 처리

1-1. 예외 페이지

  • 500 : 프로그램 내부 오류 (40/0 (ArithmeticException) 등과 같이 페이지 안의 오류가 있을 때)
  • 404 : 페이지가 없다

1-2. 내장 객체를 이용한 예외 처리

  • 컴퓨터 안의 내장 객체 : out, request, response, session, exception

info.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page errorPage="errorPage.jsp" %>    
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>welcome</title>
</head>
<body>

<% 
	int i = 40/0; // error code
%>
</body>
</html>

errorPage.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page isErrorPage="true" %>
<% response.setStatus(200); %> 
<%-- default: 500 --%>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>welcome</title>
</head>
<body>
다음과 같은 오류가 발생했습니다.<br>
<%= exception.getMessage() %>
</body>
</html>

1-3. web.xml 수정하기

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" id="WebApp_ID" version="4.0">
  <display-name>servet_example</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  
    <error-page>
     <error-code>404</error-code>
     <location>/error404.jsp</location>
  </error-page>
  <error-page>
     <error-code>500</error-code>
     <location>/error500.jsp</location>
  </error-page>
 
</web-app>
  • xml에 error page를 처리하는 구문을 넣는다.
  • info.jsp의 errorPage.jsp를 주석 처리하고, error500.jsp와 error404.jsp를 작업한다.

2. 데이터베이스 다루기

2-1. dept.jsp 만들기


<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>DEPT</title>
</head>
<body>
<% 
String url = "jdbc:oracle:thin:@localhost:1521/xe";
Class.forName("oracle.jdbc.driver.OracleDriver");

String sql = "SELECT * FROM dept";

Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);

while (rs.next()) { 
    out.print(rs.getString("deptno")); 
    
}

rs.close();
st.close();
con.close();

%>
</body>
</html>

  • sql 객체 페이지 (Connection, Statement, ResultSet, DriverManager) import한다.

    • Connection : DriverManager를 이용하여 SQL과 연결한다.
    • Statement : 작업할 수 있도록 하는 커넥션 객체를 생성한다.
    • ResultSet : 스테이트먼트의 쿼리 결과를 실행하여 보여준다,

  • 드라이버 매니저가 있고, 각 드라이버는 제조사가 만든 것을 사용한다.

  • C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib 안에 있는 jar 삽입

2-2. dept 테이블 안의 컬럼 내용 출력하기


<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>DEPT</title>
</head>
<body>
<% 
String url = "jdbc:oracle:thin:@localhost:1521/xe";
Class.forName("oracle.jdbc.driver.OracleDriver");
// OracleDriver 객체 생성 후 메모리에 올림

String sql = "SELECT * FROM dept";

Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);

while (rs.next()) { 
	//out.print("부서번호 : " + rs.getString("deptno") + "<br>");
	
    out.print(rs.getString("deptno")+"<br />"); 
    out.print(rs.getString("dname") + "<br />");
    out.print(rs.getString("loc") + "<br />");
    out.println("<hr />");
    
}

rs.close();
st.close();
con.close();

%>
</body>
</html>

2-3. emp 출력 (emp.jsp)

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>EMP</title>
</head>
<body>
<% 
String url = "jdbc:oracle:thin:@localhost:1521/xe";
Class.forName("oracle.jdbc.driver.OracleDriver");

String sql = "SELECT * FROM emp";

Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);

while (rs.next()) { 
	//out.print("부서번호 : " + rs.getString("deptno") + "<br>");
	
/*     out.print(rs.getString("empno") + "  "); 
    out.print(rs.getString("ename") + "  ");
    out.print(rs.getString("job")+ "  ");
    out.print(rs.getString("mgr")+ "  ");
    out.print(rs.getString("hiredate")+ "  ");
    out.print(rs.getString("sal")+ "  ");
    out.print(rs.getString("comm")+ "  ");
    out.print(rs.getString("deptno")+ "  ");
    out.println("<br /><hr />"); */
        
}

rs.close();
st.close();
con.close();

%>
</body>
</html>

2-4. emp 사원들의 총 연봉 구하기 (totalsal.jsp)

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>EMP_totalsal</title>
</head>
<body>
<% 
String url = "jdbc:oracle:thin:@localhost:1521/xe";
Class.forName("oracle.jdbc.driver.OracleDriver");

String sql = "SELECT ENAME, SAL, NVL(COMM, 0) as commission, SAL*12+NVL(COMM, 0) as yearsal FROM EMP";

Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);

while (rs.next()) { 

    out.println("이름 : " + rs.getString("ename") + "<br>");
    out.println("급여 : " + rs.getString("sal")+ "<br>");
    out.println("커미션 : " + rs.getString("commission")+ "<br>");
    out.println("연봉 : " + rs.getString("yearsal")+ "<br>");
   
    out.println("<hr />");
        
}

rs.close();
st.close();
con.close();

%>
</body>
</html>

2-5. 사원의 급여와 등급 출력 (salgrade.jsp)

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>SALGRADE</title>
</head>
<body>
<% 
String url = "jdbc:oracle:thin:@localhost:1521/xe";
Class.forName("oracle.jdbc.driver.OracleDriver");

String sql = "select * from emp, salgrade where sal BETWEEN losal AND hisal";

Connection con = DriverManager.getConnection(url, "scott", "tiger");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);

while (rs.next()) { 
	out.println("이름 : " + rs.getString("ename") + "<br>");
    out.println("급여 : " + rs.getString("sal") + "<br>");
    out.println(rs.getString("grade") + "등급<br>");
    out.println("<hr />");
}

rs.close();
st.close();
con.close();

%>
</body>
</html>
profile
Jane
velog, GitHub, Notion 등에 작업물을 정리하고 있습니다.
이전 포스트

DB 6일 (23.03.28)

다음 포스트

DB 7일 (23.03.29)

0개의 댓글