자바 - Bean 학생목록조회 list.jsp

정보처리산업기사_2차 평가문제(공개용) (1).pdf

0.43MB

 

 

 

1. DBPKG 패키지 생성

2. DBConn 클래스 생성

3. 연결

package DBPKG;

import java.sql.Connection; 
import java.sql.DriverManager;
//Connection, DriverManager 는 클래스 *로 대체할수있다 import java.sql.*;

public class DBConn {
   public static Connection getConnection() throws Exception{
	   
	   String url = "jdbc:oracle:thin:@//localhost:1521/xe";
	   String id = "system";
	   String pwd = "1234";
	   
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection
       (url,id,pwd);
      return con;
   }
}

4. 새 클래스 jungboVo 생성 -getter~

package DBPKG;

public class jungboVo {
private String sno;
private String sname;
private int kor;
private int eng;
private int math;
private int hist;

5. 인터페이스 jungbo 생성

package DBPKG;

import java.util.List;

public interface jungbo {
 List<jungboVo> selectAll();
}

 

 

*  1) 클래스를 상속 받는 경우 (일반적)

    2) 인터페이스를 상속 받는 경우 : 메소드의 정의를 받아오는 것

상속은 다중상속이 안됨

 

6. 임플리먼트 jungboImpl 생성 - 메소드 불러오기

package DBPKG;

import java.util.List;

public class jungboImpl implements jungbo {

	@Override
	public List<jungboVo> selectAll() {
		// TODO Auto-generated method stub
		return null;
	}

}

-- DBConn 불러오기

    Connection dbconn = DBConn.getConnection(); 하면 import 받고 try-catch 받아준다

 

이 이후로는 정리 할 수 없었다 ...

 

 

* count 만들기

 

---

<정리>

DBConn

package DBPKG;

import java.sql.*;

public class DBConn {
	
	public static Connection getConnection() throws Exception{
		String url="jdbc:oracle:thin:@//localhost:1521/xe";
		String id = "system";  
		String pwd = "1234";		   
		Class.forName("oracle.jdbc.OracleDriver");		
		Connection con = DriverManager.getConnection(url,id,pwd);
		return con;
	}
	
}

Jungbo

package DBPKG;

import java.util.List;

public interface Jungbo {
 List<JungboVo> selectAll();
  int count();
}

JungboImpl

package DBPKG;

import java.sql.*;
import java.util.*;


public class JungboImpl implements Jungbo {
	
	Connection	 conn =null;
	String SQL ="";
	PreparedStatement pstmt =null;
	ResultSet rs =null;
	
	@Override
	public List<JungboVo> selectAll()  {
		
		List<JungboVo> li = null;
		
		try {
			 conn = DBConn.getConnection();
			 SQL = "select *  from examtbl";
			 pstmt = conn.prepareStatement(SQL);
			 rs = pstmt.executeQuery();
			
			 li = new ArrayList<JungboVo>();
			 while(rs.next()) {
			  JungboVo	m= new JungboVo();
			  m.setSno(rs.getString("sno"));	
			  m.setSname(rs.getString("sname"));
			  m.setKor(rs.getInt("kor"));
			  m.setEng(rs.getInt("eng"));
			  m.setMath(rs.getInt("math"));
			  m.setHist(rs.getInt("hist"));
			  li.add(m)	 ;
			 }			 
			 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return li;
	}

	@Override
	public int count() {
List<JungboVo> li = null;
         int tc =0;
		try {
			 conn = DBConn.getConnection();
			 SQL = "select count(*)tc  from examtbl";
			 pstmt = conn.prepareStatement(SQL);
			 rs = pstmt.executeQuery();
			 rs.next();
			  tc = rs.getInt("tc");
	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return tc;
	}

}

JungboVo

package DBPKG;

public class JungboVo {
  private  String sno;
  private  String  sname;
  private  int  kor;
  private  int  eng;
  private  int  math;
  private  int  hist;
  
public String getSno() {
	return sno;
}
public void setSno(String sno) {
	this.sno = sno;
}
public String getSname() {
	return sname;
}
public void setSname(String sname) {
	this.sname = sname;
}
public int getKor() {
	return kor;
}
public void setKor(int kor) {
	this.kor = kor;
}
public int getEng() {
	return eng;
}
public void setEng(int eng) {
	this.eng = eng;
}
public int getMath() {
	return math;
}
public void setMath(int math) {
	this.math = math;
}
public int getHist() {
	return hist;
}
public void setHist(int hist) {
	this.hist = hist;
}  
}

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="DBPKG.*" %>


<%
Jungbo jungbo = new  JungboImpl();
List<JungboVo>  li = jungbo.selectAll();
 int tc = jungbo.count();
%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>학생성적 목록보기</title>
</head>


<body>
<div align="center">
<h2> 학생 수 : <%= tc %> </h2> 
<table border=1>
<tr>
    <td>학번</td><td>이름</td>
    <td>국어</td><td>영어</td><td>수학</td><td>역사</td>
    <td>합계</td><td>평균</td>
</tr>

<%  
    for(int i=0 ; i < li.size(); i++){
    	JungboVo	m= li.get(i);
    int sumK = m.getKor() + m.getEng() + m.getMath() + m.getHist();
    double avgK =sumK/4.0;
%>
<tr>
    <td><%= m.getSno() %></td>
    <td><%= m.getSname() %></td>
    <td><%= m.getKor() %></td>
    <td><%= m.getEng() %></td>
    <td><%= m.getMath() %></td>
    <td><%= m.getHist() %></td>
    <td><%=sumK %></td>
    <td><%=avgK %></td>
</tr>

<% } %>

</table>
</div>
</body>
</html>