11. Java Beans StudentDataBase 를 이용한 테이블 STUDENT 조회 프로그램 :: 테스토리

11. Java Beans StudentDataBase 를 이용한 테이블 STUDENT 조회 프로그램

2017. 2. 23. 13:20

1.

create table student(

id varchar2(20) not null,

passwd varchar2(20) not null,

name varchar2(20) not null,

year number(4) null,

snum varchar2(10) null,

depart varchar2(30) null,

mobile1 varchar2(4) null,

mobile2 varchar2(10) null,

address varchar2(65) null,

email varchar2(30) null,

PRIMARY KEY(id) );

insert into student (id, passwd, name, year, snum, depart, mobile1, mobile2, address, email)

values ('javajsp', 'java8394', '수선화', 2010, '1077818', '컴퓨터공학과', '011', '7649-9875', '서울시', 'java2@gmail.com');

insert into student (id, passwd, name, year, snum, depart, mobile1, mobile2, address, email)

values ('gonji', 'young', '백일홍', 2009, '2065787', '인터넷비즈니스과', '016', '2975-9854', '인천시', 'gong@hotmail.com');

insert into student (id, passwd, name, year, snum, depart, mobile1, mobile2, address, email)

values ('water', 'javayoung', '수국', 2010, '1176432', '기술경영과', '011', '5531-6677', '제주도', 'singer@gmail.com');

insert into student (id, passwd, name, year, snum, depart, mobile1, mobile2, address, email)

values ('novel', 'elephant', '백합', 2011, '2056485', '컴퓨터공학과', '016', '3487-9919', '서울시', 'novel@hanmail.com');

insert into student (id, passwd, name, year, snum, depart, mobile1, mobile2, address, email)

values ('korea', '9943inner', '해당화', 2010, '1987372', '기술경영과', '017', '2670-4593', '인천시', 'wing@gmail.com');

select * from student;

<student_1.sql 저장>

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>Student Table select 조회</title>

</head>

<body>

<%@ page import="java.sql.*" %>

<h2> 데이타베이스 ORACLE STUDENT 테이블 조회 프로그램 </h2>

<hr size="5" color="red">

<center>

<h2>학생 정보 조회</h2>

<%

Connection conn = null;

Statement stmt = null;

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

String dbURL = "jdbc:oracle:thin:@localhost:1521:xe";

try{

Class.forName(driverName);

conn = DriverManager.getConnection(dbURL,"scott", "tiger");

stmt = conn.createStatement();

ResultSet result = stmt.executeQuery("select * from student");

%>

<table width="100%" border="2" cellpadding="1">

<tr>

<td align="center"><b>아이디</b></td>

<td align="center"><b>암호</b></td>

<td align="center"><b>이름</b></td>

<td align="center"><b>입학년도</b></td>

<td align="center"><b>학번</b></td>

<td align="center"><b>학과</b></td>

<td align="center"><b>휴대폰1</b></td>

<td align="center"><b>휴대폰2</b></td>

<td align="center"><b>주소</b></td>

<td align="center"><b>이메일</b></td>

</tr>

<%

while(result.next()){

%>

<tr>

<td align="center"><%=result.getString(1) %></td>

<td align="center"><%=result.getString(2) %></td>

<td align="center"><%=result.getString(3) %></td>

<td align="center"><%=result.getInt(4) %></td>

<td align="center"><%=result.getString(5) %></td>

<td align="center"><%=result.getString(6) %></td>

<td align="center"><%=result.getString(7) %></td>

<td align="center"><%=result.getString(8) %></td>

<td align="center"><%=result.getString(9) %></td>

<td align="center"><%=result.getString(10) %></td>

</tr>

<%

}

result.close();

}catch(Exception e){

out.println("ORACLE 데이타베이스 STUDENT 테이블 조회에 문제가 있습니다.");

out.println(e.toString());

e.printStackTrace();

}

finally{

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

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

}

%>

</table>

</center>

<hr size="5" color="green">

</body>

</html>

<studentSelectDB.jsp로 저장>

2. 테이블 STUDENT에서 이름으로 조회

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>SELECT TABLE name 조회 결과</title>

</head>

<body>

<%@ page import="java.sql.*" %>

<%request.setCharacterEncoding("utf-8");%>

<h2>테이블 STUDENT 에서 이름으로 조회하는 프로그램</h2>

<hr size="5" color="green">

<center>

<h2>학생 정보 조회</h2>

<%

Connection conn = null;

PreparedStatement pstmt = null;

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

String dbURL = "jdbc:oracle:thin:@localhost:1521:xe";

int rowCount = 0;

try{

Class.forName(driverName);

conn = DriverManager.getConnection(dbURL,"scott","tiger");

if(request.getParameter("ssearch").equals("name"))

{

String name = request.getParameter("restest") + "%";

String sql = "select * from student where name like ?";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, name);

}

else if(request.getParameter("ssearch").equals("year"))

{

int name = Integer.parseInt(request.getParameter("restest"));

String sql = "select * from student where year = ?";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, name);

}

ResultSet result = pstmt.executeQuery();

%>

<table width="90%" border="2" celpadding="1">

<tr>

<td align="center"><b>아이디</b></td>

<td align="center"><b>암호</b></td>

<td align="center"><b>이름</b></td>

<td align="center"><b>입학년도</b></td>

<td align="center"><b>학번</b></td>

<td align="center"><b>학과</b></td>

<td align="center"><b>휴대폰1</b></td>

<td align="center"><b>휴대폰2</b></td>

<td align="center"><b>주소</b></td>

<td align="center"><b>이메일</b></td>

</tr>

<%

while(result.next()){

%>

<tr>

<td align="center"><%=result.getString(1) %></td>

<td align="center"><%=result.getString(2) %></td>

<td align="center"><%=result.getString(3) %></td>

<td align="center"><%=result.getString(4) %></td>

<td align="center"><%=result.getString(5) %></td>

<td align="center"><%=result.getString(6) %></td>

<td align="center"><%=result.getString(7) %></td>

<td align="center"><%=result.getString(8) %></td>

<td align="center"><%=result.getString(9) %></td>

<td align="center"><%=result.getString(10) %></td>

</tr>

<%

rowCount ++;

}

result.close();

}catch(Exception e)

{

out.println("ORACLE 데이타베이스 STUDENT 테이블 조회에 문제가 있습니다.");

out.println(e.toString());

e.printStackTrace();

}

finally{

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

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

}

%>

</table>

</center>

<p>

<hr size="5" color="yellow">

<font color="green">

<%

if(rowCount == 0) out.println("조회된 결과가 없습니다.");

else out.println("조회된 레코드가 " + rowCount+" 건 입니다.");

%>

</font>

<hr size="5" color="red">

</body>

</html>

selectName.jsp로 저장

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>테이블 STUDENT에서 이름으로 조회</title>

</head>

<body>

<h2> STUDENT TABLE 에서 이름으로 조회하는 프로그램 </h2>

<hr size="5" color="red">

<h3> 조회할 이름을 입력하세요. </h3>

<form action="selectName.jsp" method="post" name="test">

검색 조건 : <input type="radio" name="ssearch" value="name">이름

<input type="radio" name="ssearch" value="year">년도<br>

검색 내용 : <input type="text" name="restest"><br>

<input type="submit" value="보내기">

</form>

</body>

</html>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>테이블 STUDENT에서 이름으로 조회</title>

</head>

<body>

<h2> STUDENT TABLE 에서 이름으로 조회하는 프로그램 </h2>

<hr size="5" color="red">

<h3> 조회할 이름을 입력하세요. </h3>

<form action="selectName.jsp" method="post" name="test">

검색 조건 : <select name="ssearch">

<option value="name">이름</option>

<option value="year">년도</option>

</select><p>

검색 내용 : <input type="text" name="restest"><br>

<input type="submit" value="보내기">

</form>

</body>

</html>

3. insertStudent

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>DBCP 이용 STUDENT 테이블에 레코드 삽입</title>

</head>

<body>

<%@ page import="java.sql.*, javax.sql.*, javax.naming.*" %>

<h2> Oracle Database STUDENT 테이블에 학생 레코드 삽입 </h2>

<hr size="5" color="red">

<h2> 학생 삽입 </h2>

<%

Connection conn = null;

PreparedStatement pstmt = null;

Statement stmt = null;

StringBuffer SQL = new StringBuffer("INSERT INTO student ");

SQL.append("VALUES(?,?,?,?,?,?,?,?,?,?)");

String name="개나리2";

try{

Context initCtx = new InitialContext();

Context env = (Context)initCtx.lookup("java:comp/env/");

DataSource ds = (DataSource)env.lookup("jdbc/OracleDB");

conn = ds.getConnection();

pstmt = conn.prepareStatement(SQL.toString());

// 삽입할 학생 레코드 입력

pstmt.setString(1, "dall");

pstmt.setString(2, "commons");

pstmt.setString(3, name);

pstmt.setInt(4, 2017);

pstmt.setString(5, "1039653");

pstmt.setString(6, "정보처리과");

pstmt.setString(7, "010");

pstmt.setString(8, "2345-3456");

pstmt.setString(9, "제주도");

pstmt.setString(10, "dbcp6@daum.net");

int rowCount = pstmt.executeUpdate();

if(rowCount == 1)

{

out.println("<hr color='yellow' size='3'> 학생 ["+name+"] 레코드가 생공적으로 삽입되었습니다.<hr color='yellow' size='3'>");

}

else{

out.println("학생 레코드 삽입에 문제가 발생하였습니다.");

}

// 이후 다시 학생 조회

stmt = conn.createStatement();

ResultSet result = stmt.executeQuery("SELECT * FROM student");

%>

<table width="90%" border="2" cellpadding="1">

<tr>

<td align="center"><b>아이디</b></td>

<td align="center"><b>암호</b></td>

<td align="center"><b>이름</b></td>

<td align="center"><b>입학년도</b></td>

<td align="center"><b>학번</b></td>

<td align="center"><b>학과</b></td>

<td align="center"><b>휴대폰1</b></td>

<td align="center"><b>휴대폰2</b></td>

<td align="center"><b>주소</b></td>

<td align="center"><b>이메일</b></td>

</tr>

<%

while(result.next()){

%>

<tr>

<td align="center"><%=result.getString(1) %></td>

<td align="center"><%=result.getString(2) %></td>

<td align="center"><%=result.getString(3) %></td>

<td align="center"><%=result.getString(4) %></td>

<td align="center"><%=result.getString(5) %></td>

<td align="center"><%=result.getString(6) %></td>

<td align="center"><%=result.getString(7) %></td>

<td align="center"><%=result.getString(8) %></td>

<td align="center"><%=result.getString(9) %></td>

<td align="center"><%=result.getString(10) %></td>

</tr>

<%

}

result.close();

}catch(Exception e)

{

out.println("Oracle Database STUDENT 테이블에 레코드 삽입에 문제가 발생하였습니다.<hr>");

out.println(e.toString());

e.printStackTrace();

}

finally{

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

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

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

}

%>

</table>

<hr size="5" color="green">

</body>

</html>

4.

<%@ page language="java" contentType="text/html; charset=UTF-8"

pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>Java Beans 사용 Oracle 테이블 STUDENT 조회 프로그램</title>

</head>

<body>

<%@ page import="java.util.ArrayList, univ.StudentEntity" %>

<h2> Java Beans StudentDataBase 를 이용한 테이블 STUDENT 조희 프로그램 </h2>

<hr size="5" color="green">

<CENTER>

<h2> 학생 정보 조회 </h2>

<jsp:useBean id="stdtdb" class="univ.StudentDatabase" scope="page" />

<%

ArrayList<StudentEntity> list = stdtdb.getStudentList();

int counter = list.size();

if(counter > 0){

%>

<table width="90%" border="2" cellpadding="1">

<tr>

<td align="center"><b>아이디</b></td>

<td align="center"><b>암호</b></td>

<td align="center"><b>이름</b></td>

<td align="center"><b>입학년도</b></td>

<td align="center"><b>학번</b></td>

<td align="center"><b>학과</b></td>

<td align="center"><b>휴대폰1</b></td>

<td align="center"><b>휴대폰2</b></td>

<td align="center"><b>주소</b></td>

<td align="center"><b>이메일</b></td>

<td align="center"><b>성별</b></td>

<td align="center"><b>성별2</b></td>

</tr>

<%

for(StudentEntity stdt : list){

%>

<tr>

<td align="center"><%=stdt.getId() %></td>

<td align="center"><%=stdt.getPasswd() %></td>

<td align="center"><%=stdt.getName() %></td>

<td align="center"><%=stdt.getYear() %></td>

<td align="center"><%=stdt.getSnum() %></td>

<td align="center"><%=stdt.getDepart() %></td>

<td align="center"><%=stdt.getMobile1() %></td>

<td align="center"><%=stdt.getMobile2() %></td>

<td align="center"><%=stdt.getAddress() %></td>

<td align="center"><%=stdt.getEmail() %></td>

<td align="center"><%=stdt.getSex() %></td>

<td align="center"><%

if(stdt.getSnum().indexOf("1")==0)

{ out.print("남자.");

} else if(stdt.getSnum().indexOf("2")==0)

{ out.print("여자."); }%></td>

</tr>

</tr>

<%

}

%>

</table>

<%

}

%>

</CENTER>

<hr size="5" color="yellow">

<p>

조회된 학생 수가 <%= counter %> 명 입니다.

<hr size="5" color="red">

</body>

</html>

package univ;

public class StudentEntity {

private String id;

private String passwd;

private String name;

private int year;

private String snum;

private String depart;

private String mobile1;

private String mobile2;

private String address;

private String email;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getPasswd() {

return passwd;

}

public void setPasswd(String passwd) {

this.passwd = passwd;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getYear() {

return year;

}

public void setYear(int year) {

this.year = year;

}

public String getSnum() {

return snum;

}

public void setSnum(String snum) {

this.snum = snum;

}

public String getDepart() {

return depart;

}

public void setDepart(String depart) {

this.depart = depart;

}

public String getMobile1() {

return mobile1;

}

public void setMobile1(String mobile1) {

this.mobile1 = mobile1;

}

public String getMobile2() {

return mobile2;

}

public void setMobile2(String mobile2) {

this.mobile2 = mobile2;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public String getSex()

{

String returnsex="";

if(snum.substring(0,1).equals("1"))

{

returnsex="남자입니다.";

}

else if(snum.substring(0,1).equals("2"))

{

returnsex="여자입니다.";

}

return returnsex;

}

}

package univ;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

// 테이블 STUDENT 데이타베이스 연동 자바빈즈 포로그램

public class StudentDatabase {

private static final String JDBC_DRIVER ="oracle.jdbc.driver.OracleDriver";

private static final String JDBC_URL ="jdbc:oracle:thin:@127.0.0.1:1521:xe";

private static final String JDBC_USER ="scott";

private static final String JDBC_PASSWD ="tiger";

private Connection conn = null;

private Statement stmt = null;

public StudentDatabase(){

try{

Class.forName(JDBC_DRIVER); // 객체 생성시 드라이버 로드

}catch (Exception e) {

// TODO: handle exception

}

}

public void connect(){

try{

conn = DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASSWD);

}catch(Exception e){

e.printStackTrace();

}

}

public void disConnect(){

if(stmt != null){

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

try {

} catch (Exception e) {

e.printStackTrace();

}

}

// 모든 레코드를 반환하는 메소드

public ArrayList<StudentEntity> getStudentList(){

connect();

//질의 결과를 저장 할 ArrayList 객체 생성

//ArrayList 내부에 학생 정보를 저장한 StudentEntity가 삽입

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

String sql = "select * from student";

try {

stmt = conn.createStatement();

ResultSet rs= stmt.executeQuery(sql);

//ResultSet의 모든 행을 각각 StudentEntity에 저장

while(rs.next()){

StudentEntity se = new StudentEntity();

se.setId(rs.getString("id"));

se.setPasswd(rs.getString("passwd"));

se.setName(rs.getString("name"));

se.setYear(rs.getInt("year"));

se.setSnum(rs.getString("snum"));

se.setDepart(rs.getString("depart"));

se.setMobile1(rs.getString("mobile1"));

se.setMobile2(rs.getString("mobile2"));

se.setAddress(rs.getString("address"));

se.setEmail(rs.getString("email"));

list.add(se);

}

rs.close();

} catch (SQLException e) {

e.printStackTrace();

} finally{

disConnect();

}

return list;

}

}

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

'JSP(Java Server Pages)' 카테고리의 다른 글

13. JSTL(JSP Standard Tag Library), EL(Expression Language) (0)	2017.02.27
12. Java Beans, 회원관리 시스템, 로그인 및 관리자 시스템 (0)	2017.02.24
10. Oracle Database 연동 테스트 (0)	2017.02.22
9. <JSP> 데이터베이스 연동, Insert, Select, Update, Delete (0)	2017.02.21
8. 파일업로드폼(FileUploadForm), 썸네일폼(thumbnailform) (0)	2017.02.20

+ Recent posts

Powered by Tistory, Designed by wallel

티스토리툴바