DB05 - 25일(jdbc27~30, sql08~11) - delete, create

JDBC27Servlet

 

25번 서블릿 + 화면까지 만들기 (jsp)

↑ get 방식 / post 방식

get - jsp (form) 보여주기

post - 실제 db 변경

(update 받아서 하는건 지금은 생략)

update 는 변경 전 정보가 담겨있어야 함, 변경 전 정보를 한번 확인해야 함

 

doGet, doPost

	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();
		Customer customer = null;
		List<String> countryList = null;

		// 2. request parameter 분석/가공
		String idStr = request.getParameter("id");
		int customerID = Integer.parseInt(idStr);

		// 3. business logic
		try (Connection con = ds.getConnection()) {
			customer = dao.selectById(con, customerID);
			countryList = dao.getCountryList(con);
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute
		request.setAttribute("customer", customer);
		request.setAttribute("countryList", countryList);

		// 5. forward
		String path = "/WEB-INF/view/jdbc06/v27.jsp";
		request.getRequestDispatcher(path).forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 0. 사전 작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		CustomerDAO dao = new CustomerDAO();
		boolean ok = false;

		// 2. request 분석/가공
		String customerName = request.getParameter("customerName");
		String contactName = request.getParameter("contactName");
		String city = request.getParameter("city");
		String country = request.getParameter("country");
		String address = request.getParameter("address");
		String postalCode = request.getParameter("postalCode");
		int customerID = Integer.parseInt(request.getParameter("customerID"));
		
		Customer customer = new Customer();
		customer.setCustomerName(customerName);
		customer.setContactName(contactName);
		customer.setCity(city);
		customer.setCountry(country);
		customer.setAddress(address);
		customer.setPostalCode(postalCode);
		customer.setCustomerID(customerID);

		// 3. business logic
		try (Connection con = ds.getConnection()) {
			ok = dao.update(con, customer);
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute

		// 5. forward / redirect
	}

doGet

dao 객체 생성, 리스트 생성

문자열 idStr 에 id 값을 전달받아와 저장, int CustomerID 에 방금 저장한 idStr 을 int 로 형변환하여 저장

try 에서 customer 에 selectById 메소드 반환값을 저장, countryList 에 getCountryList 메소드 반환값을 저장

customer 을 customer 로, countryList 를 countryList 로 전달하고, v27.jsp 파일로 포워딩

doPost

dao 객체 생성, boolean ok = false 

컬럼 값들을 받아와 문자열로 저장, 문자열로 저장한 컬럼 값들을 customer.set~ 로 저장 (bean)

try 에서 dao.update 메소드 실행, 반환값을 ok 에 저장

→

doGet

전달받은 id 를 CustomerID 에 저장, selectById → customer, getCountryList → countryList 저장, jsp로 포워딩

doPost

request.getParameter 로 컬럼 값을 받아옴 (jsp 창에서 직접 입력한 값임), 이를 customer 에 저장하고 메소드로 넘겨서 dao 에서 update 함

 

CustomerDAO

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 boolean update(Connection con, Customer customer) {
		String sql = "UPDATE Customers " + 
				"SET  " + 
				"	CustomerName = ?, " + 
				"    ContactName = ?, " + 
				"    Address = ?, " + 
				"    City = ?, " + 
				"    PostalCode = ?, " + 
				"    Country = ? " + 
				"WHERE " + 
				"    CustomerID = ? ";
		int rowCount = 0;
		
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			int i = 1;
			pstmt.setString(i++, customer.getCustomerName());
			pstmt.setString(i++, customer.getContactName());
			pstmt.setString(i++, customer.getAddress());
			pstmt.setString(i++, customer.getCity());
			pstmt.setString(i++, customer.getPostalCode());
			pstmt.setString(i++, customer.getCountry());
			pstmt.setInt(i++, customer.getCustomerID());
			
			rowCount = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rowCount == 1;
	}

	public Customer selectById(Connection con, int customerID) {
		String sql = "SELECT CustomerName, ContactName, Address, City, "
				+ "          PostalCode, Country "
				+ "FROM Customers "
				+ "WHERE CustomerID = ?";
		
		Customer customer = new Customer();
		
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			pstmt.setInt(1, customerID);
			
			try (ResultSet rs = pstmt.executeQuery()) {
				if (rs.next()) {
					String customerName = rs.getString("CustomerName");
					String contactName = rs.getString("ContactName");
					String address = rs.getString("address");
					String city = rs.getString("City");
					String postalCode = rs.getString("PostalCode");
					String country = rs.getString("Country");
					
					customer.setCustomerID(customerID);
					customer.setCustomerName(customerName);
					customer.setContactName(contactName);
					customer.setAddress(address);
					customer.setCity(city);
					customer.setPostalCode(postalCode);
					customer.setCountry(country);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	
		return customer;
	}

여기서 쓰일 메소드 세 개 (getCountryList, update, selectById) 만 가져옴

public Customer selectById(Connection con, int customerID) 메소드 새로 작성됨

sql = SELECT 컬럼들 FROM Customers WHERE CustomerID = ? (Customers 테이블에서 CustomerID 가 ? 인 행의 컬럼들 정보를 조회)

Customer 객체 customer 생성

try 에서 _ pstmt 로 customerID 를 ? 에 넣기 (쿼리스트링으로 입력한 id 를 서블릿에서 customerID 로 저장했음) 

그 안의 try 에서 _ sql 실행해서 받은 컬럼 값들을 문자열로 저장, 그리고 이를 다시 customer.set~ 으로 저장

customer 반환 (해당 id 의 컬럼값들을 저장한 객체를 반환)

아까랑 달라진건 jsp 파일로 화면 띄워주기 밖에 없잖아

그러면 추가로 필요한건 그냥 컬럼값 받아오기 뿐 아니야? 아까는 직접 코드에 썼다면 이제는 받아오기잖아

근데 왜 이 메소드가 필요하지? 

→ 처음에 쿼리스트링으로 id 주면 입력창에 원래의 컬럼값을 보여주기 위한 거였음

 

jdbc06

v27.jsp

<body>

  <div class="container">
    <div class="row">
      <div class="col">
        <h1>고객 수정</h1>
        
        <form action="" method="post">
            <input type="hidden" name="customerID" value="${customer.customerID }">
        	<div class="form-group">
        		<label for="input1">CustomerName</label>
        		<input value="${customer.customerName }" type="text" class="form-control" id="input1" name="customerName">
        	</div>
        	<div class="form-group">
        		<label for="input2">ContactName</label>
        		<input value="${customer.contactName }" type="text" class="form-control" id="input2" name="contactName">
        	</div>
        	<div class="form-group">
        		<label for="input3">Address</label>
        		<input value="${customer.address }" type="text" class="form-control" id="input3" name="address">
        	</div>
        	<div class="form-group">
        		<label for="input4">City</label>
        		<input value="${customer.city }" type="text" class="form-control" id="input4" name="city">
        	</div>
        	<div class="form-group">
        		<label for="input5">PostalCode</label>
        		<input value="${customer.postalCode }" type="text" class="form-control" id="input5" name="postalCode">
        	</div>
            <div class="form-group">
            	<label for="select1">Country</label>
            	<select name="country" id="select1" class="form-control">
                  <c:forEach items="${countryList }" var="country">
                    <option ${customer.country eq country ? 'selected' : '' } value="${country }">${country }</option>
                  </c:forEach>
                </select>
            </div>
            
            <input type="submit" value="수정" class="btn btn-outline-primary">
        </form>
        
      </div>
    </div>
  </div>

 </body>

쿼리스트링으로 id 입력, 각 컬럼 값은 화면에 입력, country 는 목록에서 선택

customer.컬럼명 으로 전달됨, post 방식

---

JDBC28Servlet

이번에는 Supplier (27 반복)

doGet, doPost

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// get : 화면으로 forward

		// 0. 사전작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		SupplierDAO dao = new SupplierDAO();
		Supplier supplier = null;
		List<String> countryList = null;

		// 2. request 분석
		int supplierID = Integer.parseInt(request.getParameter("id"));

		// 3. business logic
		try (Connection con = ds.getConnection()) {
			supplier = dao.selectById(con, supplierID);
			countryList = dao.getCountryList(con);
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute
		request.setAttribute("supplier", supplier);
		request.setAttribute("countryList", countryList);

		// 5. forward
		String path = "/WEB-INF/view/jdbc06/v28.jsp";
		request.getRequestDispatcher(path).forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// post : update db

		// 0. 사전 작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		SupplierDAO dao = new SupplierDAO();
		boolean ok = false;

		// 2. request 분석/가공
		Supplier supplier = new Supplier();
		supplier.setSupplierName(request.getParameter("supplierName"));
		supplier.setContactName(request.getParameter("contactName"));
		supplier.setCity(request.getParameter("city"));
		supplier.setCountry(request.getParameter("country"));
		supplier.setAddress(request.getParameter("address"));
		supplier.setPostalCode(request.getParameter("postalCode"));
		supplier.setPhone(request.getParameter("phone"));
		supplier.setSupplierID(Integer.parseInt(request.getParameter("supplierID")));

		// 3. business logic
		try (Connection con = ds.getConnection()) {
			ok = dao.update(con, supplier);
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute

		// 5. forward / redirect
	}

 

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 boolean update(Connection con, Supplier supplier) {
		String sql = "UPDATE Suppliers " +
				"SET  " +
				"	SupplierName = ?, " +
				"    ContactName = ?, " +
				"    Address = ?, " +
				"    City = ?, " +
				"    PostalCode = ?, " +
				"    Country = ?, " +
				"    Phone = ? " +
				"WHERE " +
				"    SupplierID = ? ";
		int rowCount = 0;

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			int i = 1;
			pstmt.setString(i++, supplier.getSupplierName());
			pstmt.setString(i++, supplier.getContactName());
			pstmt.setString(i++, supplier.getAddress());
			pstmt.setString(i++, supplier.getCity());
			pstmt.setString(i++, supplier.getPostalCode());
			pstmt.setString(i++, supplier.getCountry());
			pstmt.setString(i++, supplier.getPhone());
			pstmt.setInt(i++, supplier.getSupplierID());

			rowCount = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return rowCount == 1;
	}

	public Supplier selectById(Connection con, int supplierID) {
		String sql = "SELECT SupplierName, "
				+ "          ContactName, "
				+ "          Address, "
				+ "          City, "
				+ "          PostalCode,"
				+ "          Country, "
				+ "          Phone "
				+ "   FROM Suppliers "
				+ "   WHERE SupplierID = ?";
		
		Supplier supplier = new Supplier();
		
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			pstmt.setInt(1, supplierID);
			
			try (ResultSet rs = pstmt.executeQuery()) {
				
				if (rs.next()) {
					supplier.setAddress(rs.getString("Address"));
					supplier.setSupplierName(rs.getString("SupplierName"));
					supplier.setContactName(rs.getString("ContactName"));
					supplier.setCity(rs.getString("City"));
					supplier.setPostalCode(rs.getString("PostalCode"));
					supplier.setCountry(rs.getString("Country"));
					supplier.setPhone(rs.getString("Phone"));
					supplier.setSupplierID(supplierID);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return supplier;
	}

selectById 메소드 새로 작성됨

 

v28.jsp 

<body>
<div class="container">
	<div class="row">
		<div class="col">
			<h1>공급자 수정</h1>
			<form action="" method="post">
                <input type="hidden" name="supplierID" value="${supplier.supplierID }">
				<div class="form-group">
					<label for="input1">Supplier Name</label>
					<input type="text" class="form-control" id="input1" name="supplierName" value="${supplier.supplierName }">
				</div>
				<div class="form-group">
					<label for="input2">Contact Name</label>
					<input type="text" class="form-control" id="input2" name="contactName" value="${supplier.contactName }">
				</div>
				<div class="form-group">
					<label for="input3">Address</label>
					<input type="text" class="form-control" id="input3" name="address" value="${supplier.address }">
				</div>
				<div class="form-group">
					<label for="input4">City</label>
					<input type="text" class="form-control" id="input4" name="city" value="${supplier.city }">
				</div>
				<div class="form-group">
					<label for="input5">Postal Code</label>
					<input type="text" class="form-control" id="input5" name="postalCode" value="${supplier.postalCode }">
				</div>
				<div class="form-group">
					<label for="input6">Phone</label>
					<input type="text" class="form-control" id="input6" name="phone" value="${supplier.phone }">
				</div>
				<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 }" ${supplier.country == country ? 'selected' : '' }>${country }</option>
                      </c:forEach>
                    </select>
				</div>
        
                <button class="btn btn-outline-primary">수정</button>
			</form>
		</div>
	</div>
</div>

</body>

---

*** tomcat 이 실행되지 않는 문제 해결

1) 콘솔 창 확인하기

나의 경우에는 mapping 이 중복되어서 에러가 나는 거였다

web.xml 이든 @WebServlet 이든 경로가 같은 서블릿 파일이 두개이면 안된다

2) tomcat 지우고 재설치하기

preferences - server 검색 - Runtime Environment 에서 기존 톰캣 remove 하기 

다시 add 하여 tomcat 추가

콘솔 창 옆 Servers 에서 다시 설정

마지막으로 Library 확인 (프로젝트 우클릭 - Build Path - Configure Build Path - Java Build Path - Library 탭에서 

Server Runtime 라이브러리 있는지 확인, 없으면 add Library - server runtime 하여 추가)

3) port 문제 

콘솔 창 옆 Servers 에서 톰캣 더블클릭 - Ports 탭에서 HTTP 의 Port Number 를 다른 숫자로 변경하고 저장

---

08delete.sql

♠ DELETE : 삭제하기

1. 테이블 삭제하기

DELETE FROM 테이블명;

하지만 테이블 전체 삭제와 같이 너무 많은 데이터를 지우면 세이프모드로 인해 안 지워질 수 있음

SET SQL_SAFE_UPDATES = 0; --세이프모드 해제
SET SQL_SAFE_UPDATES = 1; --세이프모드 설정

2. 특정 행만 삭제하기

DELETE FROM 테이블명 WHERE 조건;
DELETE FROM 테이블명 WHERE 특정 컬럼 명 = 특정 컬럼 값;

---

jdbc07

JDBC29Servlet 

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();
		boolean ok = false;

		// 2. request 분석
		int customerID = Integer.parseInt(request.getParameter("id"));

		// 3. business logic
		try (Connection con = ds.getConnection()) {
			ok = dao.deleteById(con, customerID);
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute

		// 5. forward / redirect

	}

dao 객체 생성, boolean ok = false

id 받아서 형변환해서 customerID 로 저장 - 쿼리스트링으로 받아오려나봄, 그리고 그 행 삭제하려나봄

try에서 ok = dao.deleteById(con, customerID) 

즉, dao 의 deleteById 메소드 실행, 그런데 con 과 customerID 전달해서, 그리고 값 반환하면 받아서 ok 에 저장함

 

CustomerDAO

	public boolean deleteById(Connection con, int customerID) {
		String sql = "DELETE FROM Customers "
				+ "   WHERE CustomerID = ?";
		
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			
			pstmt.setInt(1, customerID);
			
			int count = pstmt.executeUpdate();
			
			return count == 1;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return false;
	}

새로 작성된 deleteById 메소드만 가져왔음

sql = DELETE FROM Customers WHERE CustomerID = ?

pstmt.setInt 로 메소드의 매개변수로 받아온 customerID 를 ? 에 전달

count 는 executeUpdate 에서 1 을 반환하므로 true, 그런데 왜 마지막에 false 반환하지

---

JDBC30Servlet

이번에는 Supplier (29 반복)

	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();
		boolean ok = false;
		
		// 2. request 분석
		int supplierID = Integer.parseInt(request.getParameter("id"));
		
		// 3. business logic
		try (Connection con = ds.getConnection()) {
			ok = dao.deleteById(con, supplierID);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 4. add attribute
		
		// 5. forward / redirect
		
	}

id 받아서 supplierID 에 저장하고 메소드에서 넘김

 

SupplierDAO

	public boolean deleteById(Connection con, int supplierID) {
		String sql = "DELETE FROM Suppliers "
				+ "WHERE SupplierID = ?";
		
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			pstmt.setInt(1, supplierID);
			
			int cnt = pstmt.executeUpdate();
			
			return cnt == 1;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return false;
	}

새로 작성된 deleteById 메소드만 가져옴

sql 작성, 받아온 supplierID 를 ? 에 넣어줌, 그리고 cnt

---

09create-table.sql, 09create-table-datatype-string.sql

♠ CREATE TABLE : 데이터베이스에 새 테이블 만들기

CREATE TABLE 테이블명 (
	컬럼1이름 컬럼1의datatype,
	컬럼2이름 컬럼2의datatype,
	컬럼3이름 컬럼3의datatype,
	...
);

 

 

♠ datatype 종류 - CHAR(size), VARCHAR(size)

1. CHAR(size) 

고정 길이 문자열 (숫자, 특수문자 포함 가능) 

매개변수 size 는 열 길이를 문자 단위로 지정, 범위: 1~255, 기본값 1

→ 만약, 크기 만큼의 데이터가 들어오지 않으면?

나머지 자리를 공백 (space) 로 채우는 경우와 그렇지 않은 경우가 있다

SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; -- 공백으로 채우도록 하기
SET sql_mode = ''; -- 공백으로 채우지 않도록 하기

2. VARCHAR(size)

가변 길이 문자열 (숫자, 특수문자 포함 가능)

매개변수 size 는 최대 열 길이를 문자 단위로 지정, 범위: 0~65535

CREATE TABLE mytable1 (
	col1 CHAR(1),
	col2 CHAR(2),
	col3 CHAR(3),
	col4 CHAR(4)
);

CREATE TABLE mytable2 (
	col1 VARCHAR(1),
	col2 VARCHAR(2),
	col3 VARCHAR(3),
	col4 VARCHAR(4)
);

INSERT INTO mytable1 (col1) VALUES ('ab'); -- ab 길이는 1을 넘어가므로 불가능
INSERT INTO mytable1 (col2) VALUES ('a'); -- 가능, 남은 한자리는 space로 채워짐

INSERT INTO mytable1 (col1, col2, col3, col4) VALUES ('a', 'ab', 'abc', 'abcd');

INSERT INTO mytable2 (col1) VALUES ('ab'); -- ab 길이는 1을 넘어가므로 불가능
INSERT INTO mytable2 (col2) VALUES ('a'); -- 가능

INSERT INTO mytable2 (col1, col2, col3, col4) VALUES ('a', 'ab', 'abc', 'abcd');

---

10create-table-datatype-numeric.sql

♠ datatype 종류 - INT, DOUBLE, DECIMAL( )

1. INT, DOUBLE

CREATE TABLE mytable3 (
	col1 INT,
	col2 DOUBLE
);

INSERT INTO mytable3 (col1, col2) VALUES (25, 3.14);

2. DECIMAL(size), DECIMAL(size, d)

1) DECIMAL(size) - 자릿수 제한

2) DECIMAL(size, d) - size 는 총 자릿수, d 는 그 중에서 소수점 이하가 차지하는 자릿수 제한

제한된 자릿수를 초과할 경우 원하지 않는 값이 들어갈 수 있다

CREATE TABLE mytable4(
	col1 DOUBLE(3),
	col2 DOUBLE(5, 2)
);

INSERT INTO mytable4 (col1) VALUES (1100); -- 불가능
INSERT INTO mytable4 (col1) VALUES (100); -- 가능

INSERT INTO mytable4 (col2) VALUES (100.123); -- 불가능
INSERT INTO mytable4 (col2) VALUES (1000.12); -- 불가능
INSERT INTO mytable4 (col2) VALUES (100.12); -- 가능

* DECIMAL 은 DEC 으로 쓰기도 함

 

10create-table-datatype-date-time.sql

♠ datatype 종류 - DATE, DATETIME

DATE 는 '년-월-일', DATETIME 은 '년-월-일 시-분-초' 로 작성하면 된다

CREATE TABLE mytable5 (
	col1 DATE,
	col2 DATETIME
);

INSERT INTO mytable5 (col1) VALUES ('2021-11-25');
INSERT INTO mytable5 (col2) VALUES ('2021-11-25 12:30:10');

현재 시간: now()

SELECT now();

INSERT INTO mytable5 (col1, col2) VALUES (now(), now());

---

11drop-table.sql

♠ DROP TABLE : 테이블 삭제

DROP TABLE 테이블명;

♠ TRUNCATE TABLE : 테이블 내부의 데이터 삭제 (테이블 자체는 삭제되지 않음)

DELETE FROM 테이블명; 과 유사한 것 같다

TRUNCATE TABLE 테이블명;

둘 다 거의 쓰이지 않음