DB02 - 22일(jdbc03~10)

course 2021/JDBC\DB

DB02 - 22일(jdbc03~10)

코딩하는토끼 2021. 12. 5. 23:06

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>

테이블