[JSP] JDBC + Bean (+ Vector) : 데이터 조회(SELECT)하기
25 Nov 2021 -
2 minute read
!! 컬렉션 프레임워크 - Vector를 사용해 클래스(Bean)의 객체를 배열로 만들어 사용 !!
DB
create database silsp;
use silsp;
create table member(
no int primary key auto_increment,
name char(10),
age int,
addr char(20)
);
insert into member
values
(1, '이마이', 15, '강동구 천호동'),
(2, '김사라', 36, '광진구 자양동'),
(3, '박다효', 24, '송파구 가락동');
pack_Sample.DataBean.java
package pack_Sample;
public class DataBean {
private int no;
private String name;
private int age;
private String addr;
public int getNo() {
return no;
}
public void setNo(int no) {
this.no = no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
}
pack_Sample.Test.java
package pack_Sample;
import java.sql.*;
import java.util.Vector;
public class Test {
public Vector mtdDBCP() {
// 반환타입 > VECTOR(컬렉션프레임워크, 배열로처리)
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Vector vector = new Vector();
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/silsp?";
url += "useSSL=false&";
url += "serverTimezone=Asia/Seoul&";
url += "useUnicode=true&";
url += "characterEncoding=UTF-8&";
url += "allowPublicKeyRetrieval=true";
String user = "root";
String password = "1234";
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
String sql = "select * from member order by no desc";
rs = stmt.executeQuery(sql);
if (rs != null) {
while (rs.next()) {
DataBean dataBean = new DataBean();
dataBean.setNo(rs.getInt("no"));
dataBean.setName(rs.getString("name"));
dataBean.setAge(rs.getInt("age"));
dataBean.setAddr(rs.getString("addr"));
vector.add(dataBean);
}
}
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("DB 연동 Class 이슈 : " + e.getMessage());
} catch (SQLException e) {
System.out.println("SQL 이슈 : " + e.getMessage());
}
return vector;
}
}
UsingJDBC_JSP.jsp
<%@page import="java.util.Vector"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*, pack_Sample.DataBean" %>
<jsp:useBean class="pack_Sample.Test" id="testObj" scope="page"></jsp:useBean>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>JDBC에서 DB 연동, 출력하기 - SELECT</title>
<link rel="stylesheet" href="">
<style>
table td{
border: 1px solid #000;
font-size: 18px;
padding: 10px;
}
</style>
</head>
<body>
<div id="wrap">
<h1>JSP 스크립트릿에서 DBCP+Bean 연동하기</h1>
<h2>회원정보</h2>
<table>
<tbody>
<tr>
<td>번호(no)</td>
<td>이름(name)</td>
<td>나이(age)</td>
<td>주소(addr)</td>
</tr>
<%
Vector vecList = testObj.mtdDBCP();
int cnt = vecList.size();
for(int i = 0; i < cnt; i++){
DataBean dataBean = (DataBean)vecList.get(i);
%>
<tr>
<td><%=dataBean.getNo() %></td>
<td><%=dataBean.getName() %></td>
<td><%=dataBean.getAge() %></td>
<td><%=dataBean.getAddr() %></td>
</tr>
<%
}
%>
</tbody>
</table>
<br><br>
<h2>총 회원수는 <%=cnt %>명 입니다.</h2>
</div>
</body>
</html>
