[JSP] JDBC + Bean 데이터 조회(SELECT)하기 ②

26 Nov 2021 - 3 minute read

DB

---

create database mydb;
use mydb;

create table memlist(
	uid		char(10)	primary key,
    upw		char(10),
    uname	char(10),
    num1	int,
    num2	int,
	uemail	char(20),
    uphone	char(20),
    zipcode	char(10),
    address	char(20),
    job		char(10)
);

insert into memlist
values
('vidv', '1234', '김준형', 941125, 1234567, 'vjun@naver.com', 
'010-123-4567', 26547, '서울시 은평구', '개발자');

pack_Bean.RegisterBean.java

---

package pack_Bean;

public class RegisterBean {

	private String uid;
	private String upw;
	private String uname;
	private int num1;
	private int num2;
	private String uemail;
	private String uphone;
	private String zipcode;
	private String address;
	private String job;
	
	public String getUid() {
		return uid;
	}
	public void setUid(String uid) {
		this.uid = uid;
	}
	public String getUpw() {
		return upw;
	}
	public void setUpw(String upw) {
		this.upw = upw;
	}
	public String getUname() {
		return uname;
	}
	public void setUname(String uname) {
		this.uname = uname;
	}
	public int getNum1() {
		return num1;
	}
	public void setNum1(int num1) {
		this.num1 = num1;
	}
	public int getNum2() {
		return num2;
	}
	public void setNum2(int num2) {
		this.num2 = num2;
	}
	public String getUemail() {
		return uemail;
	}
	public void setUemail(String uemail) {
		this.uemail = uemail;
	}
	public String getUphone() {
		return uphone;
	}
	public void setUphone(String uphone) {
		this.uphone = uphone;
	}
	public String getZipcode() {
		return zipcode;
	}
	public void setZipcode(String zipcode) {
		this.zipcode = zipcode;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	
}

pack_Bean.RegisterMgr.java

---

package pack_Bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class RegisterMgr {

	private final String JDBC_Driver = "com.mysql.cj.jdbc.Driver";
	private final String JDBC_URL = "jdbc:mysql://localhost:3306/myDB?"
			+ "useSSL=false&serverTimezone=Asia/Seoul&useUnicode=true&"
			+ "characterEncoding=UTF-8&allowPublicKeyRetrieval=true";
	private final String user = "root";
	private final String password = "1234";
	
	public RegisterMgr() {	//디폴트생성자(Constructor)
		try {
			Class.forName(JDBC_Driver);
		} catch (ClassNotFoundException e) {
			System.out.println("Error: JDBC Driver 로딩 실패");
			System.out.println(e.getMessage());
		}
	}
	
	@SuppressWarnings("finally")
	public Vector getMemlist() {
		
		Connection  conn	=	null;
		Statement	stmt	=	null;
		ResultSet	rs		=	null;
		
		Vector vecList = new Vector();
		
		try {
			conn = DriverManager.getConnection(JDBC_URL, user, password);
			
			String sql = "select * from memlist";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {

				RegisterBean registerBean = new RegisterBean();
				
				registerBean.setUid(rs.getString("uid"));
				registerBean.setUpw(rs.getString("upw"));
				registerBean.setUname(rs.getString("uname"));
				registerBean.setNum1(rs.getInt("num1"));
				registerBean.setNum2(rs.getInt("num2"));
				registerBean.setUemail(rs.getString("uemail"));
				registerBean.setUphone(rs.getString("uphone"));
				registerBean.setZipcode(rs.getString("zipcode"));
				registerBean.setAddress(rs.getString("address"));
				registerBean.setJob(rs.getString("job"));
				
				vecList.add(registerBean);
			}
			
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						System.out.println(e.getMessage());
					}
				}
				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException e) {
						System.out.println(e.getMessage());
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
						System.out.println(e.getMessage());
					}
				}
		
			return vecList;
		}
	}
}

View.jsp

---

<%@page import="pack_Bean.RegisterBean"%>
<%@page import="java.util.Vector"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<jsp:useBean id="BeanReg" class="pack_Bean.RegisterMgr" scope="page"></jsp:useBean>
<%
	Vector vList = BeanReg.mtdMemlist();
%>
<!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>JSP JDBC Bean</title>
    <style>
    	div#wrap{
    	border: 1px solid #000;
    	padding: 10px;
    	width: 1000px;
    	text-align: center;
    	}
    	table {
    	border: 1px solid #000;
    	border-collapse: collapse;
    	margin: 0 auto;
    	}
    	th,td {
    	border: 1px solid #000;
    	padding: 12px;
    	}
    </style>
</head>
<body>
	<div id="wrap">
	<h1>회원정보</h1>
	
	<table>
    <tbody>
        <tr>
            <th>아이디</th>
            <th>비밀번호</th>
            <th>이름</th>
            <th>주민번호</th>
            <th>이메일</th>
            <th>연락처</th>
            <th>주소</th>
            <th>직업</th>
        </tr>
        <!-- 데이터 출력 시작 -->
        <%
    	for(int i = 0; i < vList.size(); i ++){
    		RegisterBean registerBean = (RegisterBean)vList.get(i);
        %>
        <tr>
		    <td><%=registerBean.getUid() %></td>
		    <td><%=registerBean.getUpw() %></td>
		    <td><%=registerBean.getUname() %></td>
		    <td><%=registerBean.getNum1() %> - <%=registerBean.getNum2() %></td>
		    <td><%=registerBean.getUemail() %></td>
		    <td><%=registerBean.getUphone() %></td>
		    <td><%="(" + registerBean.getZipcode() + ")" %> <%=registerBean.getAddress() %></td>
		    <td><%=registerBean.getJob() %></td>		    
		</tr>		
		<% } %>
        <!-- 데이터 출력 끝 -->
    </tbody>
</table>
</div>
</body>
</html>