JDBC11Servlet
- 그냥 반복
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<>();
// 3. business logic
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();
}
// 4. add attribute
request.setAttribute("employees", list);
// 5. forward
String path = "/WEB-INF/view/jdbc02/v11.jsp";
request.getRequestDispatcher(path).forward(request, response);
}+ Employee, v11.jsp
jdbc03
JDBC12Servlet
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
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();
}
// 4. add attribute
request.setAttribute("customer", cus);
// 5. forward
String path = "/WEB-INF/view/jdbc03/v12.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
주소창에 쿼리 스트링으로 몇번 ID 의 정보를 얻어올지 직접 정할 수 있다
String id = request.getParameter("customerID")
쿼리스트링 ?customerID=1, 2, 3, .. 등 작성
나머지는 뭐 동일
jdbc03
v12.jsp
<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>
</body>
JDBC13Servlet
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
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;
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();
}
// 4. add attribute
request.setAttribute("customer", cus);
// 5. forward
String path = "/WEB-INF/view/jdbc03/v12.jsp";
request.getRequestDispatcher(path).forward(request, response);
}String id = request.getParameter("customerID")
String sql = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country FROM Customers WHERE CustomerID = ?"
→ ? 로 남겨두면 어떻게 받아서 값을 넣어주지?
PerparedStatement stmt = con.prepareStatement(sql)
stmt.setInt(1, Integer.parseInt(id))
문자열 id 를 숫자로 형변환하여 ? 자리에 넣는다는거같은데...
↓ 설명
PreparedStatement 객체
객체명.setInt(int parameterIndex, int x)
혹은 string 이라면 객체명.setString(int parameterIndex, String x) 와 같이 작성하는데, 파라미터 첫번째는 인덱스 (위치) 이고 두번째는 그 변수에 들어갈 값이다 변수가 들어갈 자리는 ? 로 작성
Customer 객체 cus 를 customer 로 전달, v12.jsp 로 포워딩
v12.jsp
<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>
</body>JDBC14Servlet
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);
}리스트 list 생성
country 로 값 어디선가 받아오려고 준비
sql 에서 country 값만 빼고 코드 작성
여긴 그냥 statement (아래 15번에서 바꿀 예정)
Customer 객체 cus 생성
값 모두 받아서 리스트에 추가
리스트를 customers 로 전달, v08.jsp 로 포워딩
- 볼거없음 -
쿼리스트링으로 country 값 작성 시 따옴표 없이!
ex. ?country=UK
+ Customer, v08.jsp
JDBC15Servlet
위 14의 statement 를 preparedStatement 로 바꿔보기
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.setString(1, country);
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);
}String country = request.getParameter("country") 는 동일하게 작성
sql 코드의 country 값 받을 자리에 ? 작성
PreparedStatement 객체 생성 후 setString 으로 받아온 country 값을 ? 자리에 넣음
나머지 동일
쿼리스트링으로 country 값을 줘서 실행시킬 수 있음
JDBC16Servlet
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
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);
try (ResultSet rs = stmt.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();
}
// 4. add attribute
request.setAttribute("suppliers", list);
// 5. forward
String path = "/WEB-INF/view/jdbc03/v16.jsp";
request.getRequestDispatcher(path).forward(request, response);
}15 와 동일하고 테이블만 바뀜
보통 쿼리스트링으로 값을 전달하지 않으니까, 다른 방법을 알아보자
input 으로 받아서 출력해보기 ↓
v16.jsp
<body>
<div class="container">
<div class="row">
<div class="col">
<form action="">
<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>
<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>
↓ uk 입력하고 검색
JDBC17Servlet
이번에는 input 말고 select, option 으로 나라를 선택하도록 하자
나라 목록을 jsp 파일로 넘겨주기 위해서, Country 컬럼을 중복을 제거하고 오름차순 정렬한다
그리고 리스트로 넘겨준다
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
// 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);
try (ResultSet rs = stmt.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();
}
// 4. add attribute
request.setAttribute("countryList", countryList);
request.setAttribute("suppliers", list);
// 5. forward
String path = "/WEB-INF/view/jdbc03/v17.jsp";
request.getRequestDispatcher(path).forward(request, response);
}리스트 두개
List<Supplier> list = new ArrayList<>(); - 원래 있던 거
List<String> countryList = new ArrayList<>(); - 나라 목록을 저장할 리스트
sql2 = SELECT DISTINCT Country FROM Suppliers ORDER BY Country
→ Suppliers 테이블에서, Country 컬럼을 중복을 제거, 오름차순 정렬하여 조회
try 두번 실행함
먼저 sql2 실행하여 나라들을 리스트에 추가
다음으로 sql 실행하여 선택된 나라의 정보들을 리스트에 추가 (여기에서는 행을 저장해야 하므로 bean 사용)
request.setAttribute 로 리스트 두 개 모두 전달
v17.jsp
<body>
<div class="container">
<div class="row">
<div class="col">
<form action="">
<div class="form-group">
<label for="select1">국가 선택</label>
<select name="country" id="select1" class="form-control">
<c:forEach items="${countryList }" var="country">
<option value="${country }" ${country eq param.country ? 'selected' : '' }>${country }</option>
</c:forEach>
</select>
</div>
<input class="btn btn-primary" type="submit" value="검색">
</form>
</div>
</div>
</div>
<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>
↓
JDBC18Servlet
17 과 동일, 테이블만 변경해서 연습
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
List<String> countryList = new ArrayList<>();
List<Customer> customerList = new ArrayList<>();
// 2. request 파라미터 분석/가공
String country = request.getParameter("country");
// 3. business logic
// 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. add attribute
request.setAttribute("countryList", countryList);
request.setAttribute("customerList", customerList);
// 5. forward
String path = "/WEB-INF/view/jdbc03/v18.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
v18.jsp
<body>
<div class="container">
<div class="row">
<div class="col-5">
<form action="">
<div class="input-group">
<select name="country" id="" class="custom-select">
<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>
<div class="container">
<div class="row">
<div class="col">
<h1>고객들</h1>
<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>
</body>DAO: Data Access Object
서블릿에서 반복적으로 작성되는 부분 - executeQuery, resultSet - 을 따로 빼내어 작성하기
jdbc04.servlet
JDBC19Servlet
- 18 번 코드 복사하여 작성
doGet 메소드
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<String> countryList = new ArrayList<>();
List<Customer> customerList = new ArrayList<>();
// 2. request 파라미터 분석/가공
String country = request.getParameter("country");
try (Connection con = ds.getConnection();) {
// 3. business logic
// 3.1 country 리스트 조회
countryList = dao.getCountryList(con);
// 3.2 customer 리스트 조회
customerList = dao.getCustomerListByCountry(con, country);
} catch (Exception e) {
e.printStackTrace();
}
// 4. add attribute
request.setAttribute("countryList", countryList);
request.setAttribute("customerList", customerList);
// 5. forward
String path = "/WEB-INF/view/jdbc03/v18.jsp";
request.getRequestDispatcher(path).forward(request, response);
}JDBC18Servlet 과 비교
2. request 파라미터 분석/가공 , 4. add attribute , 5. forward - 동일
0. 사전작업
CustomerDAO 객체생성 추가됨
CustomerDAO dao = new CustomerDAO();
3. business logic
sql 코드 없어짐
String sql1 = “SELECT DISTINCT country FROM Customers ORDER BY country”;
String sql2 = “SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country FROM Customers WHERE Country = ?”;
try - catch 코드가 두번에서 한번으로 줄었음
try() 안의 statement, resultset 없어짐
try(){} 안의 while 로 리스트에 값을 추가하는 코드 없어짐
새로운 try 안에는 다음 두 줄만 작성됨
countryList = dao.getCountryList(con);
customerList = dao.getCustomerListByCountry(con, country);
→ 아마도 dao 에서 sql 코드, statement, resultset, while 로 리스트에 값 추가까지 다 해서
서블릿에서는 dao 객체생성, 이를 받아와 리스트에 저장만 하는 것 같다
jdbc04.dao
CustomerDAO
DAO 는 파일을 서블릿으로 만들지 않고 그냥 클래스로 생성
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;
}
}public List<String> getCountryList(Connection con) { }
- 메소드명 getCountryList, 매개변수 Connection 타입 con
- 리스트 생성, sql 작성, try 안에 statement, resulrset 작성, while로 리스트에 값 추가, 리스트 반환
- 나라 목록 리스트로 반환하는 메소드!
public List<Customer> getCustomerListByCountry(Connection con, String country) { }
- 메소드명 getCustomerListByCountry, 매개변수 Connection 타입 con, String 타입 country
- 리스트 생성, sql 작성, try 안에 preparedStatement, resultset 작성, while 로 리스트에 값 추가 (bean 사용), 리스트 반환
- 받은 country 의 Customer 정보를 리스트로 반환하는 메소드!
v18.jsp
<body>
<div class="container">
<div class="row">
<div class="col-5">
<form action="">
<div class="input-group">
<select name="country" id="" class="custom-select">
<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>
<div class="container">
<div class="row">
<div class="col">
<h1>고객들</h1>
<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>
</body>JDBC20Servlet
- 이번에는 JDBC17Servlet 을 dao 를 써서 다시 작성해보기 (연습)
doGet 메소드
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> list = new ArrayList<>();
List<String> countryList = new ArrayList<>();
// 2. request 분석/가공
String country = request.getParameter("country");
try (Connection con = ds.getConnection()) {
// 3. business logic
// 3.1 - 공급자 국가 조회
countryList = dao.getCountryList(con);
// 3.2 - 공급자들 조회
list = dao.getSupplierListByCountry(con, country);
} catch (Exception e) {
e.printStackTrace();
}
// 4. add attribute
request.setAttribute("countryList", countryList);
request.setAttribute("suppliers", list);
// 5. forward
String path = "/WEB-INF/view/jdbc03/v17.jsp";
request.getRequestDispatcher(path).forward(request, response);
}SupplierDAO dao = new SupplierDAO();
try 안에
countryList = dao.getCountryList(con);
list = dao.getSupplierListByCountry(con, country);
SupplierDAO
public class SupplierDAO {
public List<String> getCountryList(Connection con) {
List<String> list = new ArrayList<>();
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> getSupplierListByCountry(Connection con, String country) {
List<Supplier> list = new ArrayList<>();
String sql = "SELECT SupplierID, SupplierName, ContactName, Address, City," + " PostalCode, Country, Phone "
+ "FROM Suppliers WHERE Country = ?";
try (PreparedStatement stmt = con.prepareStatement(sql);) {
stmt.setString(1, country);
try (ResultSet rs = stmt.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 복사
<body>
<div class="container">
<div class="row">
<div class="col">
<form action="">
<div class="form-group">
<label for="select1">국가 선택</label>
<select name="country" id="select1" class="form-control">
<c:forEach items="${countryList }" var="country">
<option value="${country }" ${country eq param.country ? 'selected' : '' }>${country }</option>
</c:forEach>
</select>
</div>
<input class="btn btn-primary" type="submit" value="검색">
</form>
</div>
</div>
</div>
<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' 카테고리의 다른 글
| DB05 - 25일(jdbc27~30, sql08~11) - delete, create (0) | 2021.12.08 |
|---|---|
| DB04 - 24일(sql06~07, jdbc21~26) - insert, update (0) | 2021.12.07 |
| DB02 - 22일(jdbc03~10) (0) | 2021.12.05 |
| DB01 - 19일(sql01~05, jdbc01~02) (0) | 2021.12.04 |
| listener (0) | 2021.12.04 |
