DB(baek)
2021-11-23 preparedStatement ,DAO
Hesitater
2021. 11. 23. 19:06
728x90
2021-11-23
jdbc03 JDBC12Servlet~JDBC18Servlet
preparedStatement
jdbc04 JDBC19Servlet~
DAO
PreparedStatement
DAO
★JDBC11Servelt,Employee, v11.jsp
어제했던거 복습차원 Employee 나머지 추가
SELECT EmployeeID, LastName, FirstName, BirthDate, Photo, Notes FROM Employees;
JDBC11Servelt
@WebServlet("/jdbc02/s11")
package jdbc02.servlet1;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Employee;
/**
* Servlet implementation class JDBC11Servelt
*/
@WebServlet("/jdbc02/s11")
public class JDBC11Servelt extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC11Servelt() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
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 logic( 주로 db작업)
String sql = "SELECT " +
" EmployeeID, LastName, FirstName, BirthDate, Photo, Notes " +
" FROM " +
" Employees ";
try ( Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
while (rs.next()) {
Employee e = new Employee();
int i = 1;
e.setEmployeeID(rs.getInt(i++));
e.setLastName(rs.getString(i++));
e.setFirstName(rs.getString(i++));
e.setBirthDate(rs.getDate(i++));
e.setPhoto(rs.getString(i++));
e.setNotes(rs.getString(i++));
list.add(e);
}
} catch (Exception e) {
e.printStackTrace();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("employee", list);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc02/v11.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
Employee
package jdbc02.bean;
import java.util.Date;
public class Employee {
private int employeeID;
private String lastName;
private String firstName;
private Date birthDate;
private String photo;
private String notes;
public int getEmployeeID() {
return employeeID;
}
public void setEmployeeID(int employeeID) {
this.employeeID = employeeID;
}
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;
}
public Date getBirthDate() {
return birthDate;
}
public void setBirthDate(Date birthDate) {
this.birthDate = birthDate;
}
public String getPhoto() {
return photo;
}
public void setPhoto(String photo) {
this.photo = photo;
}
public String getNotes() {
return notes;
}
public void setNotes(String notes) {
this.notes = notes;
}
}
v11.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%= request.getContextPath() %>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>JDBC11Servlet11의 v11.jsp</title>
</head>
<body>
<!-- .container>.row>.col>h1{직원 목록들} -->
<div class="container">
<div class="row">
<div class="col">
<h1>직원 목록들</h1>
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>LastName</th>
<th>FirstName</th>
<th>BirthDate</th>
<th>Photo</th>
<th>Notes</th>
</tr>
</thead>
<tbody>
<c:forEach items="${employee }" var="emp" >
<tr>
<td>${emp.employeeID }</td>
<td>${emp.lastName }</td>
<td>${emp.firstName }</td>
<td>${emp.birthDate }</td>
<td>${emp.photo }</td>
<td>${emp.notes }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
★JDBC12Servlet,v12.jsp
request를 받아서 특정한 것만 보내줄수있는?
JDBC12Servlet
@WebServlet("/jdbc03/s12")
jdbc03폴더의 v12.jsp
http://localhost:8080/jsp1026/jdbc03/s12?customerID=1,2,3,등 대입
SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country
FROM Customers WHERE CustomerID = 1;
JDBC12Servlet
package jdbc03;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Customer;
import jdbc02.bean.Employee;
/**
* Servlet implementation class JDBC12Servlet
*/
@WebServlet("/jdbc03/s12")
public class JDBC12Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC12Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전 작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
Customer cus = new Customer();
// 2. request 분석, 가공
String id = request.getParameter("customerID");
// 3. business logic( 주로 db작업)
String sql ="SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
+ " FROM Customers WHERE CustomerID= " + id;
try (Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql)
) {
if ( rs.next()) {
int i = 1;
cus.setCustomerID(rs.getInt(i++));
cus.setCustomerName(rs.getString(i++));
cus.setContactName(rs.getString(i++));
cus.setAddress(rs.getString(i++));
cus.setCity(rs.getString(i++));
cus.setPostalCode(rs.getString(i++));
cus.setCountry(rs.getString(i++));
}
} catch (Exception e) {
e.printStackTrace();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("customer", cus);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc03/v12.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
v12.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%= request.getContextPath() %>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>JDBC12Servlet 의 v12.jsp</title>
</head>
<body>
<h1>직원 한명</h1>
<p>id : ${customer.customerID }</p>
<p>custname : ${customer.customerName }</p>
<p>contname : ${customer.contactName }</p>
<p>address : ${customer.address }</p>
<p>city : ${customer.city }</p>
<p>post : ${customer.postalCode }</p>
<p>country : ${customer.country }</p>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
초기화면
- http://localhost:8080/jsp1026/jdbc03/s12?customerID=1 인경우
- http://localhost:8080/jsp1026/jdbc03/s12?customerID=2 인경우
★JDBC13Servlet
PreparedStatement,
stmt.setInt(1, Integer.parseInt(id)); // 파라미터1 : ? 위치, 파라미터2 : 값
String sql ="SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
+ " FROM Customers WHERE CustomerID= ?";
try (Connection con = ds.getConnection();
PreparedStatement stmt = con.prepareStatement(sql);) {
stmt.setInt(1, Integer.parseInt(id)); // 파라미터1 : ? 위치, 파라미터2 : 값
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
int i = 1;
.....
http://localhost:8080/jsp1026/jdbc03/s13?customerID=1
JDBC13Servlet
WebServlet("/jdbc03/s13")
v12.jsp
○결과값
초기값
- http://localhost:8080/jsp1026/jdbc03/s13?customerID=1 인경우
-http://localhost:8080/jsp1026/jdbc03/s13?customerID=2 인 경우
★JDBC14Servlet,
JDBC15Servlet에서 그냥statement 를 preparedStatement 로 바꿀꺼임
JDBC14Servlet
@WebServlet("/jdbc03/s14")
package jdbc03;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Customer;
import jdbc02.bean.Employee;
/**
* Servlet implementation class JDBC14Servlet
*/
@WebServlet("/jdbc03/s14")
public class JDBC14Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC14Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
List<Customer> list = new ArrayList<>();
// 2. request 분석/가공
String country = request.getParameter("country");
// 3. business logic
String sql = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
+ "FROM Customers WHERE Country = '" + country + "'";
try (Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);) {
while (rs.next()) {
Customer cus = new Customer();
int i = 1;
cus.setCustomerID(rs.getInt(i++));
cus.setCustomerName(rs.getString(i++));
cus.setContactName(rs.getString(i++));
cus.setAddress(rs.getString(i++));
cus.setCity(rs.getString(i++));
cus.setPostalCode(rs.getString(i++));
cus.setCountry(rs.getString(i++));
list.add(cus);
}
} catch (Exception e) {
e.printStackTrace();
}
// 4. add attribute
request.setAttribute("customers", list);
// 5. forward
String path = "/WEB-INF/view/jdbc02/v08.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
SELECT * FROM Customers WHERE Country = 'Uk';
SELECT * FROM Customers WHERE Country = 'usa';
등 나라명만 바뀔 계획
v08.jsp 사용
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%= request.getContextPath() %>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>JDBC08Servlet</title>
</head>
<body>
<!-- .container>.row>.col>h1{고객들} -->
<div class="container">
<div class="row">
<div class="col">
<h1>고객들</h1>
<!-- table.table>thead>tr>th*4^^tbody -->
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>고객명</th>
<th>계약명</th>
<th>주소</th>
<th>도시</th>
<th>우편번호</th>
<th>국가</th>
</tr>
</thead>
<tbody>
<c:forEach items="${customers}" var="customer">
<tr>
<td>${customer.customerID}</td>
<td>${customer.customerName }</td>
<td>${customer.contactName }</td>
<td>${customer.address }</td>
<td>${customer.city }</td>
<td>${customer.postalCode }</td>
<td>${customer.country }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
setString 을 따옴표 없이
http://localhost:8080/jsp1026/jdbc03/s14?country=UK
http://localhost:8080/jsp1026/jdbc03/s14?country=usa
○결과값
초기값
- http://localhost:8080/jsp1026/jdbc03/s14?country=UK 인 경우
-http://localhost:8080/jsp1026/jdbc03/s14?country=USA
★JDBC15Servlet, v08.jsp
JDBC14Servlet에서 JDBC15Servlet에서 그냥statement 를 preparedStatement 로 바꿔라
JDBC15Servlet
package jdbc03;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Customer;
/**
* Servlet implementation class JDBC15Servlet
*/
@WebServlet("/jdbc03/s15")
public class JDBC15Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC15Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
List<Customer> list = new ArrayList<>();
// 2. request 분석/가공
String country = request.getParameter("country");
// 3. business logic
String sql = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
+ "FROM Customers WHERE Country = ?";
try (Connection con = ds.getConnection();
PreparedStatement stmt = con.prepareStatement(sql);
) {
stmt.setNString(1, country); // 파라미터1 : ?위치, 파라미터2 : 값
try (ResultSet rs = stmt.executeQuery();) {
while (rs.next()) {
Customer cus = new Customer();
int i = 1;
cus.setCustomerID(rs.getInt(i++));
cus.setCustomerName(rs.getString(i++));
cus.setContactName(rs.getString(i++));
cus.setAddress(rs.getString(i++));
cus.setCity(rs.getString(i++));
cus.setPostalCode(rs.getString(i++));
cus.setCountry(rs.getString(i++));
list.add(cus);
}
}
} catch (Exception e) {
e.printStackTrace();
}
// 4. add attribute
request.setAttribute("customers", list);
// 5. forward
String path = "/WEB-INF/view/jdbc02/v08.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
v08.jsp
http://localhost:8080/jsp1026/jdbc03/s15?country=uk
http://localhost:8080/jsp1026/jdbc03/s15?country=usa
○결과값(JDBC14Servet 랑 같인 결과나옴)
초기값
http://localhost:8080/jsp1026/jdbc03/s15?country=uk 인경우
http://localhost:8080/jsp1026/jdbc03/s15?country=usa
★JDBC16Servlet, v10.jsp
JDBC16Servlet
package jdbc03;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Supplier;
/**
* Servlet implementation class JDBC16Servlet
*/
@WebServlet("/jdbc03/s16")
public class JDBC16Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC16Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
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 분석, 가공
String country = request.getParameter("country");
// 3. business logic( 주로 db작업)
String sql ="SELECT SupplierId, SupplierName, ContactName, Address, City, PostalCode, Country, Phone "
+ " FROM Suppliers WHERE Country = ?";
try (Connection con = ds.getConnection();
PreparedStatement stmt = con.prepareStatement(sql);
) {
//stmt.setString(1, country);
stmt.setString(1, country);
try(ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
Supplier supplier = new Supplier();
int i = 1;
supplier.setSupplierId(rs.getInt(i++));
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();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("suppliers", list);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc02/v10.jsp";
// String path = "/WEB-INF/view/jdbc03/v16.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
v10.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%=request.getContextPath()%>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>
<!-- .contatiner>.row>.col>h1{공급자들} -->
<div class="contatiner">
<div class="row">
<div class="col">
<h1>공급자들</h1>
<!-- table.table>thead>tr>th*8^^tbody -->
<table class="table">
<thead>
<tr>
<th>SupplierId</th>
<th>SupplierName</th>
<th>ContactName</th>
<th>Address</th>
<th>City</th>
<th>PostalCode</th>
<th>Country</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
<c:forEach items="${suppliers }" var="sup">
<tr>
<td>${sup.supplierId }</td>
<td>${sup.supplierName }</td>
<td>${sup.contactName }</td>
<td>${sup.address }</td>
<td>${sup.city }</td>
<td>${sup.postalCode }</td>
<td>${sup.country }</td>
<td>${sup.phone }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
초기화면
http://localhost:8080/jsp1026/jdbc03/s16?country=usa 인 경우
http://localhost:8080/jsp1026/jdbc03/s16?country=uk 인경우
★JDBC16Servlet, v16.jsp
보통 쿼리스트링으로 날리는 경우는 없다
그럼 쿼리스트링으로 말고 다른 방법으로 추가해서 하는 방법을 알아보자
input 엘리멘트로 받아서 출력할 수 있는
v16.jsp ( v10.jsp 복붙해서 추가)
<!-- .container>.row>.col>form -->
form 안에
.input-group.mb-3>input.form-control+.input-group-append>input:s.btn.btn-outline-secondary
JDBC16Servlet
package jdbc03;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Supplier;
/**
* Servlet implementation class JDBC16Servlet
*/
@WebServlet("/jdbc03/s16")
public class JDBC16Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC16Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
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 분석, 가공
String country = request.getParameter("country");
// 3. business logic( 주로 db작업)
String sql ="SELECT SupplierId, SupplierName, ContactName, Address, City, PostalCode, Country, Phone "
+ " FROM Suppliers WHERE Country = ?";
try (Connection con = ds.getConnection();
PreparedStatement stmt = con.prepareStatement(sql);
) {
//stmt.setString(1, country);
stmt.setString(1, country);
try(ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
Supplier supplier = new Supplier();
int i = 1;
supplier.setSupplierId(rs.getInt(i++));
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();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("suppliers", list);
// 5. forward/ redirect
// String path = "/WEB-INF/view/jdbc02/v10.jsp";
String path = "/WEB-INF/view/jdbc03/v16.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
v16.jsp (bootstrap 사용) (v10.jsp+ 추가)
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%= request.getContextPath() %>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>
<!-- .container>.row>.col>form -->
<div class="container">
<div class="row">
<div class="col">
<form action="">
<!-- .input-group.mb-3>input.form-control+.input-group-append>input:s.btn.btn-outline-secondary -->
<div class="input-group mb-3">
<input type="text" class="form-control" name="country" value=${param.country }>
<div class="input-group-append">
<input type="submit" value="검색" class="btn btn-outline-secondary">
</div>
</div>
</form>
</div>
</div>
</div>
<!-- .contatiner>.row>.col>h1{공급자들} -->
<div class="contatiner">
<div class="row">
<div class="col">
<h1>공급자들</h1>
<!-- table.table>thead>tr>th*8^^tbody -->
<table class="table">
<thead>
<tr>
<th>SupplierId</th>
<th>SupplierName</th>
<th>ContactName</th>
<th>Address</th>
<th>City</th>
<th>PostalCode</th>
<th>Country</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
<c:forEach items="${suppliers }" var="sup">
<tr>
<td>${sup.supplierId }</td>
<td>${sup.supplierName }</td>
<td>${sup.contactName }</td>
<td>${sup.address }</td>
<td>${sup.city }</td>
<td>${sup.postalCode }</td>
<td>${sup.country }</td>
<td>${sup.phone }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
초기값
검색창에 uk치고 검색버튼 누름
검색창에 usa치고 검색 버튼 누름
-------------------------------------------오후--------------------------------------
★JDBC17Servlet, v17.jsp
Country를 따로 받아서 거기중 나라를 리스트로 만들고 나라들의 리스트를 v17로 같이넘겨서
<select>,<option> 태그등을 사용하여 나라를 선택하면 그 나라에 대한 Supplier 정보들을 받을수 있게
JDBC17Servlet (JDBC16Servlet doGet 내용 복사후 추가)
@WebServlet("/jdbc03/s17")
package jdbc03;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Supplier;
/**
* Servlet implementation class JDBC17Servlet
*/
@WebServlet("/jdbc03/s17")
public class JDBC17Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC17Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
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<>();
List<String> countryList = new ArrayList<>();
// 2. request 분석, 가공
String country = request.getParameter("country");
// 3. business logic( 주로 db작업)
// 3.1 - 공급자 국가 조회
String sql2 = "SELECT DISTINCT country FROM Suppliers ORDER BY Country";
try (Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql2);
) {
while (rs.next()) {
countryList.add(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
}
// 3.2 - 공급자들 조회
String sql ="SELECT SupplierId, SupplierName, ContactName, Address, City, PostalCode, Country, Phone "
+ " FROM Suppliers WHERE Country = ?";
try (Connection con = ds.getConnection();
PreparedStatement stmt = con.prepareStatement(sql);
) {
//stmt.setString(1, country);
stmt.setString(1, country);
try(ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
Supplier supplier = new Supplier();
int i = 1;
supplier.setSupplierID(rs.getInt(i++));
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();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("countryList", countryList);
request.setAttribute("suppliers", list);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc03/v17.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
v17.jsp (v16.jsp 복붙후 추가)
bootstrap 이용(form)
<!-- .container>.row>.col>form -->
<!-- .form-group>label+select.form-control#select1>option*3 -->
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%=request.getContextPath()%>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>
<!-- .container>.row>.col>form -->
<div class="container">
<div class="row">
<div class="col">
<form action="">
<!-- .form-group>label+select.form-control#select1>option*3 -->
<div class="form-group">
<label for="select1">국가 선택</label>
<select name="country" id="select1" class="form-control">
<%--
<option value="UK">UK</option>
--%>
<c:forEach items="${countryList }" var="country">
<option value="${country }" ${country eq param.country ? 'selected' : '' }>${country }</option>
</c:forEach>
</select>
</div>
<!-- input:s.btn.btn-primary -->
<input class="btn btn-primary" type="submit" value="검색">
</form>
</div>
</div>
</div>
<!-- .container>.row>.col>h1{공급자들} -->
<div class="container">
<div class="row">
<div class="col">
<h1>공급자들</h1>
<!-- table.table>thead>tr>th*8^^tbody -->
<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>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
초기값
국가 선택하고 검색 버튼 누르면 나라별로 정보들이 표시됨
★JDBC18Servlet,v18.jsp
JDBC17Servlet 에서 했던것을 Customers 테이블에서 Country 별로 하기
JDBC18Servlet
package jdbc03;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Customer;
/**
* Servlet implementation class JDBC18Servlet
*/
@WebServlet("/jdbc03/s18")
public class JDBC18Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC18Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전 작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
List<Customer> customerList = new ArrayList<>();
List<String> countryList = new ArrayList<>();
// 2. request 분석, 가공
String country = request.getParameter("country");
// 3. business logic( 주로 db작업)
// 3.1 country 국가 리스트 조회
String sql1 = "SELECT DISTINCT country FROM Customers ORDER BY country";
try (
Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql1);
) {
while (rs.next()) {
countryList.add(rs.getString(1)) ;
}
} catch (Exception e) {
e.printStackTrace();
}
// 3.2 customer들 조회
String sql2 = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
+ " FROM Customers WHERE Country = ?";
try (
Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql2);
) {
pstmt.setString(1, country);
try (ResultSet rs = pstmt.executeQuery();) {
while (rs.next()) {
Customer cus = new Customer();
int i = 1;
cus.setCustomerID(rs.getInt(i++));
cus.setCustomerName(rs.getString(i++));
cus.setContactName(rs.getString(i++));
cus.setAddress(rs.getString(i++));
cus.setCity(rs.getString(i++));
cus.setPostalCode(rs.getString(i++));
cus.setCountry(rs.getString(i));
customerList.add(cus);
}
}
} catch (Exception e) {
e.printStackTrace();
}
// 4. setattribute
request.setAttribute("customerList", customerList);
request.setAttribute("countryList", countryList);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc03/v18.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
v18.jsp (v08.jsp참조)
<!-- .input-group>select.custom-select>option^.input-group-append>button.btn.btn-outline-secondary -->
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%=request.getContextPath()%>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>
<!-- .container>.row>.col>form -->
<div class="container">
<div class="row">
<div class="col-5">
<form action="">
<!-- .input-group>select.custom-select>option^.input-group-append>button.btn.btn-outline-secondary -->
<div class="input-group">
<select name="country" id="" class="custom-select">
<!-- <option value=""></option> -->
<c:forEach items="${countryList }" var="country">
<option value="${country }" ${country eq param.country ? 'selected' : '' }> ${country }</option>
</c:forEach>
</select>
<div class="input-group-append">
<button class="btn btn-outline-secondary" ><i class="fas fa-search"></i></button>
</div>
</div>
</form>
</div>
</div>
</div>
<!-- .contatiner>.row>.col>h1{공급자들} -->
<div class="contatiner">
<div class="row">
<div class="col">
<h1>고객들</h1>
<!-- table.table>thead>tr>th*8^^tbody -->
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>고객명</th>
<th>계약명</th>
<th>주소</th>
<th>도시</th>
<th>우편번호</th>
<th>국가</th>
</tr>
</thead>
<tbody>
<c:forEach items="${customerList}" var="customer">
<tr>
<td>${customer.customerID}</td>
<td>${customer.customerName }</td>
<td>${customer.contactName }</td>
<td>${customer.address }</td>
<td>${customer.city }</td>
<td>${customer.postalCode }</td>
<td>${customer.country }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
초기값
Select 에서 선택후 돋보기(버튼) 을 클릭하면 그 나라에 고객들에 대한 정보들이 나옴
ex) Portugal
ex) UK
★JDBC19Servlet, v18.jsp, CustomerDAO
Data Access Object(dao)
Repository
다오들만 모아두는 패키지 만들기
jdbc04 패키지에
dao, serlvet
CustomerDAO < class임
jdbc04.dao 에 DAO파일들 (class)
jdbc04.servlet 에 Servlet 들 만들어서 확인해보기
Servlet에서 코드가 길어지니깐 내용들(business logic 부분) 을 다른 자바 클래스에 만들고 그 객체를 생성
JDBC19Servlet <- Servlet (JDBC18 doGet 복사
@WebServlet("/jdbc04/s19")
package jdbc04.servlet;
import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Customer;
import jdbc04.dao.CustomerDAO;
/**
* Servlet implementation class JDBC19Servlet
*/
@WebServlet("/jdbc04/s19")
public class JDBC19Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC19Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전 작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
CustomerDAO dao = new CustomerDAO();
List<Customer> customerList = new ArrayList<>();
List<String> countryList = new ArrayList<>();
try ( Connection con = ds.getConnection();) {
// 2. request 분석, 가공
String country = request.getParameter("country");
// 3. business logic( 주로 db작업)
// 3.1 country 국가 리스트 조회
countryList = dao.getCountryList(con);
// 3.2 customer들 조회
customerList = dao.getCustomerListByCountry(con, country);
} catch (Exception e) {
e.printStackTrace();
}
// 4. setattribute
request.setAttribute("customerList", customerList);
request.setAttribute("countryList", countryList);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc03/v18.jsp";
request.getRequestDispatcher(path).forward(request, response);
// 99. 마무리
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
CustomerDAO
package jdbc04.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import jdbc02.bean.Customer;
public class CustomerDAO {
public List<String> getCountryList(Connection con) {
List<String> list = new ArrayList<String>();
String sql = "SELECT DISTINCT country FROM Customers ORDER BY country";
try ( Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);) {
while( rs.next()) {
list.add(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public List<Customer> getCustomerListByCountry(Connection con, String country) {
List<Customer> list = new ArrayList<Customer>();
String sql = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
+ " FROM Customers WHERE Country = ?";
try ( PreparedStatement pstmt = con.prepareStatement(sql) ) {
pstmt.setString(1, country);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
Customer cus = new Customer();
int i = 1;
cus.setCustomerID(rs.getInt(i++));
cus.setCustomerName(rs.getString(i++));
cus.setContactName(rs.getString(i++));
cus.setAddress(rs.getString(i++));
cus.setCity(rs.getString(i++));
cus.setPostalCode(rs.getString(i++));
cus.setCountry(rs.getString(i));
list.add(cus);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
v18.jsp 사용
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%=request.getContextPath()%>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>
<!-- .container>.row>.col>form -->
<div class="container">
<div class="row">
<div class="col-5">
<form action="">
<!-- .input-group>select.custom-select>option^.input-group-append>button.btn.btn-outline-secondary -->
<div class="input-group">
<select name="country" id="" class="custom-select">
<!-- <option value=""></option> -->
<c:forEach items="${countryList }" var="country">
<option value="${country }" ${country eq param.country ? 'selected' : '' }> ${country }</option>
</c:forEach>
</select>
<div class="input-group-append">
<button class="btn btn-outline-secondary" ><i class="fas fa-search"></i></button>
</div>
</div>
</form>
</div>
</div>
</div>
<!-- .contatiner>.row>.col>h1{공급자들} -->
<div class="contatiner">
<div class="row">
<div class="col">
<h1>고객들</h1>
<!-- table.table>thead>tr>th*8^^tbody -->
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>고객명</th>
<th>계약명</th>
<th>주소</th>
<th>도시</th>
<th>우편번호</th>
<th>국가</th>
</tr>
</thead>
<tbody>
<c:forEach items="${customerList}" var="customer">
<tr>
<td>${customer.customerID}</td>
<td>${customer.customerName }</td>
<td>${customer.contactName }</td>
<td>${customer.address }</td>
<td>${customer.city }</td>
<td>${customer.postalCode }</td>
<td>${customer.country }</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
초기화면
나라 클릭후 돋보기(버튼) 클릭 시 정보들 나옴
★JDBC20Servlet, SupplierDAO, v20.jsp
JDBC17Servlet 내용
다오만들어서 구현해보기
JDBC20Servlet
@WebServlet("/jdbc04.s20")
package jdbc04.servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Supplier;
import jdbc04.dao.SupplierDAO;
/**
* Servlet implementation class JDBC20Servlet
*/
@WebServlet("/jdbc04.s20")
public class JDBC20Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC20Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전 작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
SupplierDAO dao = new SupplierDAO();
List<Supplier> supplierlist = new ArrayList<>();
List<String> countryList = new ArrayList<>();
// 2. request 분석, 가공
String country = request.getParameter("country");
// 3. business logic( 주로 db작업)
try (Connection con = ds.getConnection();) {
// 3.1 - 공급자 국가 조회
countryList = dao.getCountryList(con);
// 3.2 - 공급자들 조회
supplierlist = dao.getSupplierList(con, country);
} catch (Exception e) {
e.printStackTrace();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("countryList", countryList);
request.setAttribute("supplierList", supplierlist);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc03/v20.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
SupplierDAO
package jdbc04.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import jdbc02.bean.Supplier;
public class SupplierDAO {
public List<String> getCountryList(Connection con) {
List<String> list = new ArrayList<String>();
String sql = "SELECT DISTINCT Country FROM Suppliers ORDER BY Country";
try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {
while (rs.next()) {
list.add(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public List<Supplier> getSupplierList(Connection con, String country) {
List<Supplier> list = new ArrayList<Supplier>();
String sql = "SELECT SupplierID, SupplierName, ContactName, Address, City," + " PostalCode, Country, Phone "
+ "FROM Suppliers WHERE Country = ?";
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setString(1, country);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
Supplier sup = new Supplier();
int i = 1;
sup.setSupplierID(rs.getInt(i++));
sup.setSupplierName(rs.getString(i++));
sup.setContactName(rs.getString(i++));
sup.setAddress(rs.getString(i++));
sup.setCity(rs.getString(i++));
sup.setPostalCode(rs.getString(i++));
sup.setCountry(rs.getString(i++));
sup.setPhone(rs.getString(i++));
list.add(sup);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
v20.jsp (v17.jsp이용)
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="<%=request.getContextPath()%>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>
<!-- .container>.row>.col>form -->
<div class="container">
<div class="row">
<div class="col">
<form action="">
<!-- .form-group>label+select.form-control#select1>option*3 -->
<div class="form-group">
<label for="select1">국가 선택</label>
<select name="country" id="select1" class="form-control">
<%--
<option value="UK">UK</option>
--%>
<c:forEach items="${countryList }" var="country">
<option value="${country }" ${country eq param.country ? 'selected' : '' }>${country }</option>
</c:forEach>
</select>
</div>
<!-- input:s.btn.btn-primary -->
<input class="btn btn-primary" type="submit" value="검색">
</form>
</div>
</div>
</div>
<!-- .container>.row>.col>h1{공급자들} -->
<div class="container">
<div class="row">
<div class="col">
<h1>공급자들</h1>
<!-- table.table>thead>tr>th*8^^tbody -->
<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="${supplierList }" 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>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>
○결과값
초기화면
나라 선택후 돋보기(버튼) 클릭시 정보들나옴
