0. 사전작업
ServletContext application = request.getServletContext();
DataSource pool = (DataSource) application.getAttribute("dbpool");
2. request 정보 분석/가공
3. 비즈니스 로직 (주로 db 작업)
String sql = "sql 코드";
try(
3.1. 커넥션 얻기
Connection con = pool.getConnection();
3.2. statement 얻기
Statement stmt = con.createStatement();
) {
3.3. 쿼리 실행 (resultSet 얻기)
ResultSet rs = stmt.excuteQuery(sql);
3.4. resultSet 처리
if(rs.next()) {
System.out.println(rs.getString(1));
}
3.5. 자원 닫기 / exception 처리
} catch(Exception e) {
e.printStackTrace();
}
4. setAttribute
request.setAttribute(" ", );
5. forward / redirect
String view = "경로";
request.getRequestDispatcher(view).forward(request, response);
JDBC03Servlet
doGet 메소드
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
List<String> names = new ArrayList<>();
// 2. request 정보 분석/가공
// 3. 비지니스 로직 (주로 db 작업)
String sql = "SELECT CustomerName FROM Customers";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt= con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
while (rs.next()) {
// System.out.println(rs.getString(1));
names.add(rs.getString(1));
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 3.5 자원 닫기 / exception 처리
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute 추가
request.setAttribute("names", names);
// 5. forward or redirect
String path = "/WEB-INF/view/jdbc01/v03.jsp";
request.getRequestDispatcher(path).forward(request, response);
}이번에는 위에서 미리 다 정의함 (null 로 초기화)
리스트 names 생성
if 대신 while 사용하여 조건 만족하는 동안 반복하여 계속 값들을 리스트에 추가
try - catch - finally 에서 객체 rs, stmt, con 를 모두 닫음 close();
리스트 names 를 names 로 전달하고 v03.jsp 로 포워딩
* rs.next()
rs 는 첫번째 행 이전을 가리키고 있음
next 는 커서를 다음 행으로 이동함
첫번째 행이 있으면 true, 없으면 false 반환한다
next 반복되면 두번째 행, 세번째 행, ...
v03.jsp
<body>
<h1>이름들 </h1>
<ul>
<c:forEach items="${names }" var="name">
<li>${name }</li>
</c:forEach>
</ul>
</body>리스트로 출력
JDBC04Servlet
- 03 반복 연습
03 참고하여 SELECT LastName FROM Employees
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
List<String> names = new ArrayList<>();
// 2. request 정보 분석/가공
// 3. 비지니스 로직 (주로 db 작업)
String sql = "SELECT LastName FROM Employees";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
while (rs.next()) {
// System.out.println(rs.getString(1));
names.add(rs.getString(1));
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 3.5 자원 닫기 / exception 처리
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute 추가
request.setAttribute("names", names);
// 5. forward or redirect
String path = "/WEB-INF/view/jdbc01/v04.jsp";
request.getRequestDispatcher(path).forward(request, response);
}03 에서 sql 코드랑 포워딩 할 jsp 파일명만 달라짐
v04.jsp
<body>
<h1>이름들 </h1>
<ul>
<c:forEach items="${names }" var="name" varStatus="status">
<li>${status.index } : ${name }</li>
</c:forEach>
</ul>
</body>
JDBC05Servlet
이번에는 컬럼 여러개 얻어오기
SELECT CustomerName, ContactName, Address From Customers WHERE CustomerID = 1;
Customers 테이블에서 CustomerID 가 1인 행의 CustomerName, ContactName, Address 얻어오기
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String contactName = "";
String customerName = "";
String address = "";
// 2. request 분석/가공
// 3. business logic
String sql = "SELECT CustomerName, ContactName, Address FROM Customers WHERE CustomerID = 1";
try {
// 1. connection 얻기
con = pool.getConnection();
// 2. statement 얻기
stmt = con.createStatement();
// 3. resultset 얻기
rs = stmt.executeQuery(sql);
if (rs.next()) {
customerName = rs.getString(1);
contactName = rs.getString(2);
address = rs.getString(3);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute
request.setAttribute("contactName", contactName);
request.setAttribute("customerName", customerName);
request.setAttribute("address", address);
// 5. forward / redirect
String path = "/WEB-INF/view/jdbc01/v05.jsp";
request.getRequestDispatcher(path).forward(request, response);
}String contactName = "";
String customerName = "';
String address = "";
받아올 정보를 문자열로 선언하고 null 로 초기화
customerName = rs.getString(1);
contactName = rs.getString(2);
address = rs.getString(3);
→ 1, 2, 3 은 컬럼 위치이고, 여기에서 각 문자열에 정보를 저장
이후 전달하고 포워딩
v05.jsp
<body>
<h1>고객명 : ${customerName }</h1>
<h1>계약명 : ${contactName }</h1>
<h1>주소 : ${address }</h1>
</body>JDBC06Servlet
- 05 반복연습
SELECT LastName, FirstName FROM Employees WHERE EmployeeID = 1;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String lastName = "";
String firstName = "";
// 2. request 분석/가공
// 3. business logic
String sql = "SELECT LastName, FirstName FROM Employees WHERE EmployeeID = 1";
try {
// 1. connection 얻기
con = pool.getConnection();
// 2. statement 얻기
stmt = con.createStatement();
// 3. resultset 얻기
rs = stmt.executeQuery(sql);
if (rs.next()) {
lastName = rs.getString(1);
firstName = rs.getString(2);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute
request.setAttribute("lastName", lastName);
request.setAttribute("firstName", firstName);
// 5. forward / redirect
String path = "/WEB-INF/view/jdbc01/v06.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
v06.jsp
<body>
<h1>1번 직원의 이름</h1>
<h3>LastName : ${lastName }</h3>
<h3>FirstName: ${firstName }</h3>
</body>jdbc02.servlet1
JDBC07Servlet
SELECT CustomerName, ContactName, Address, City
FROM Customers WHERE CustomerID = 1;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
Customer bean = new Customer();
// 2. request 분석/가공
// 3. business logic
String sql = "SELECT CustomerName, ContactName, Address, City FROM Customers WHERE CustomerID = 1";
try {
// 1. connection 얻기
con = pool.getConnection();
// 2. statement 얻기
stmt = con.createStatement();
// 3. resultset 얻기
rs = stmt.executeQuery(sql);
if (rs.next()) {
int i = 1;
bean.setCustomerName(rs.getString(i++));
bean.setContactName(rs.getString(i++));
bean.setAddress(rs.getString(i++));
bean.setCity(rs.getString(i++));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute
request.setAttribute("customer", bean);
// 5. forward / redirect
String path = "/WEB-INF/view/jdbc02/v07.jsp";
request.getRequestDispatcher(path).forward(request, response);
}Customer bean = new Customer(); - Customer 객체 bean 생성
int i = 1;
bean.setCustomerName(rs.getString(i++));
bean.setContactName(rs.getString(i++));
bean.setAddress(rs.getString(i++));
bean.setCity(rs.getString(i++));
↓ 아래와 동일
bean.setCustomerName(rs.getString(1));
bean.setContactName(rs.getString(2));
bean.setAddress(rs.getString(3));
bean.setCity(rs.getString(4));
그리고!! 여기서 bean 객체가 쓰임
bean 은 아래 Customer 클래스 객체명
setCustomerName, setContactName, setAddress, setCity 는 모두 메소드
나중에 getCustomerName, getContactName, getAddress, getCity 로 값을 꺼낼 수 있음
request.setAttribute("customer", bean)
bean 전달하면 그 안의 customerName, contactName, address, city 를 다 전달할 수 있음
jdbc02.bean
Customer
public class Customer {
private int customerID;
private String customerName;
private String contactName;
private String address;
private String city;
private String postalCode;
private String country;
public int getCustomerID() {
return customerID;
}
public void setCustomerID(int customerID) {
this.customerID = customerID;
}
public String getPostalCode() {
return postalCode;
}
public void setPostalCode(String postalCode) {
this.postalCode = postalCode;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public void setCity(String city) {
this.city = city;
}
public String getCity() {
return city;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public String getContactName() {
return contactName;
}
public void setContactName(String contactName) {
this.contactName = contactName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
jdbc02
v07.jsp
<body>
<h1>1번 고객</h1>
<h3>고객명 :${customer.customerName }</h3>
<h3>계약명 :${customer.contactName }</h3>
<h3>주소 :${customer.address }</h3>
<h3>도시 :${customer.city }</h3>
</body>위에서 서블릿에서 이 jsp 파일로 포워딩함
bean 을 customer 로 전달했으니까 여기에선 customer.~ 로
customerName, contactName, address, city 는 property
Customer 클래스에 get 메소드가 있으므로 여기에서 el 로 그냥 꺼낼 수 있음
JDBC08Servlet
SELECT CustomerName, ContactName, Address, City FROM Customers;
Customer 테이블에서 CustomerName, ContactName, Adderss, City 를 얻어오기
hint) 한개의 행이 하나의 bean 이 되어서 list 에 들어가면 된다
doGet 메소드
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
List<Customer> list = new ArrayList<>();
// 2. request 분석/가공
// 3. business logic
String sql = "SELECT CustomerName, ContactName, Address, City FROM Customers";
try {
// 1. connection 얻기
con = pool.getConnection();
// 2. statement 얻기
stmt = con.createStatement();
// 3. resultset 얻기
rs = stmt.executeQuery(sql);
while (rs.next()) {
Customer bean = new Customer();
int i = 1;
bean.setCustomerName(rs.getString(i++));
bean.setContactName(rs.getString(i++));
bean.setAddress(rs.getString(i++));
bean.setCity(rs.getString(i++));
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute
request.setAttribute("customers", list);
// 5. forward / redirect
String path = "/WEB-INF/view/jdbc02/v08.jsp";
request.getRequestDispatcher(path).forward(request, response);
}이번에는 리스트 list 생성
if 가 아닌 while - 여러개의 행을 저장해야 하니까
Customer bean 객체선언도 while 안에서 해야 함 - 하나의 bean 이 아니고 여러개의 bean 이 필요하므로
rs.next() 한 행씩
bean.set~ 각 컬럼 값을 저장하고
list.add(bean) bean 을 리스트에 추가 (즉, 행의 정보를 리스트에 추가)
list 를 customers 로 전달하고, v08.jsp 로 포워딩
v08.jsp
<body>
<div class="container">
<div class="row">
<div class="col">
<h1>고객들</h1>
<table class="table">
<thead>
<tr>
<th>고객명</th>
<th>계약명</th>
<th>주소</th>
<th>도시</th>
</tr>
</thead>
<tbody>
<c:forEach items="${customers }" var="customer">
<tr>
<td>${customer.customerName }</td>
<td>${customer.contactName }</td>
<td>${customer.address }</td>
<td>${customer.city }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
</body>테이블로 출력
el 로 값들(리스트)을 전달받음 items="${customers }"
var="customer" 이기 때문에 아래에서는 customer.customerID 와 같이 작성
v09.jsp
이 jsp 파일을 보고 servlet 작성해보기
<body>
<div class="container">
<div class="row">
<div class="col">
<h1>직원들</h1>
<table class="table">
<thead>
<tr>
<th>Last Name</th>
<th>First Name</th>
</tr>
</thead>
<tbody>
<c:forEach items="${list }" var="emp">
<tr>
<td>${emp.lastName }</td>
<td>${emp.firstName }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
</body>Employees 테이블 / LastName, FirstName 컬럼 / list 로 전달받음
JDBC09Servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
List<Employee> list = new ArrayList<>();
// 2. request 분석
// 3. business login
String sql = "SELECT LastName, FirstName FROM Employees";
try (
Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
while (rs.next()) {
Employee employee = new Employee();
employee.setLastName(rs.getString(1));
employee.setFirstName(rs.getString(2));
list.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
}
// 4. add attribute
request.setAttribute("list", list);
// 5. forward
String path = "/WEB-INF/view/jdbc02/v09.jsp";
request.getRequestDispatcher(path).forward(request, response);
}리스트 list
while 안에서 객체선언 Employee employee
값 받아서 employee 리스트에 추가
리스트 list 로 전달, v09.jsp 로 포워딩
Employee
import java.util.Date;
public class Employee {
private String lastName;
private String firstName;
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
}
JDBC10Servlet
- 그냥 반복
SELECT SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone
FROM Suppliers;
doGet 메소드
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
List<Supplier> list = new ArrayList<>();
// 2. request 분석
// 3. business logic
String sql = "SELECT SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone FROM Suppliers ";
try (Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);) {
while (rs.next()) {
Supplier supplier = new Supplier();
int i = 1;
supplier.setSupplierID(rs.getInt(i++)); // int
supplier.setSupplierName(rs.getString(i++));
supplier.setContactName(rs.getString(i++));
supplier.setAddress(rs.getString(i++));
supplier.setCity(rs.getString(i++));
supplier.setPostalCode(rs.getString(i++));
supplier.setCountry(rs.getString(i++));
supplier.setPhone(rs.getString(i++));
list.add(supplier);
}
} catch (Exception e) {
e.printStackTrace();
}
// 4. add attribute
request.setAttribute("suppliers", list);
// 5. forward
String path = "/WEB-INF/view/jdbc02/v10.jsp";
request.getRequestDispatcher(path).forward(request, response);
}Supplier supplier 객체 생성
supplier 리스트에 추가
리스트 suppliers 로 전달
v10.jsp 로 포워딩
Supplier
public class Supplier {
private int supplierID;
private String supplierName;
private String contactName;
private String address;
private String city;
private String postalCode;
private String country;
private String phone;
public int getSupplierID() {
return supplierID;
}
public void setSupplierID(int supplierID) {
this.supplierID = supplierID;
}
public String getSupplierName() {
return supplierName;
}
public void setSupplierName(String supplierName) {
this.supplierName = supplierName;
}
public String getContactName() {
return contactName;
}
public void setContactName(String contactName) {
this.contactName = contactName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getPostalCode() {
return postalCode;
}
public void setPostalCode(String postalCode) {
this.postalCode = postalCode;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
v10.jsp
body>
<div class="container">
<div class="row">
<div class="col">
<h1>공급자들</h1>
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>SNAME</th>
<th>CNAME</th>
<th>ADDRESS</th>
<th>CITY</th>
<th>POST</th>
<th>COUNTRY</th>
<th>PHONE</th>
</tr>
</thead>
<tbody>
<c:forEach items="${suppliers }" var="supplier">
<tr>
<td>${supplier.supplierID }</td>
<td>${supplier.supplierName }</td>
<td>${supplier.contactName }</td>
<td>${supplier.address }</td>
<td>${supplier.city }</td>
<td>${supplier.postalCode }</td>
<td>${supplier.country }</td>
<td>${supplier.phone }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
</body>테이블
'course 2021 > JDBC\DB' 카테고리의 다른 글
| DB04 - 24일(sql06~07, jdbc21~26) - insert, update (0) | 2021.12.07 |
|---|---|
| DB03 - 23일(jdbc11~20) (0) | 2021.12.06 |
| DB01 - 19일(sql01~05, jdbc01~02) (0) | 2021.12.04 |
| listener (0) | 2021.12.04 |
| filter (0) | 2021.12.02 |
