DB04 - 24일(sql06~07, jdbc21~26) - insert, update

06insert.sql

w3schools - SQL INSERT INTO Statement : https://www.w3schools.com/sql/sql_insert.asp  

♠ INSERT INTO ~ VALUES ~ : 행 추가하기

INSERT INTO 테이블명 VALUES (컬럼1 값, 컬럼2 값, ...);
INSERT INTO 테이블명 (컬럼1 이름, 컬럼2 이름, ...) VALUES (컬럼1 값, 컬럼2 값, ...);

몇 개의 컬럼만 값을 넣어 행을 추가할 경우 나머지 값들은 null 이 된다

전체 컬럼에 값을 넣을 경우에도 컬럼명을 써주는 게 좋다

INSERT INTO Customers VALUES (92, 'Kim', 'Kim', 'GangNam', 'Seoul', '88888', 'Korea');
INSERT INTO Customers (CustomerID, ContactName) VALUES (93, 'Lee');
INSERT INTO Customers (CustomerName) VALUES ('Cha');

♠ key : 다른 행들과 내용이 겹치지 않고 유일한 컬럼, 그래서 이 컬럼의 값으로 행을 구분할 수 있음

Customers 테이블에서 key 가 되는 컬럼은 CustomerID → 행 추가 시 컬럼 값을 따로 작성하지 않아도 자동으로 추가된다!

---

jdbc05

JDBC21Servlet

	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 분석/가공
		Customer customer = new Customer();
		customer.setCustomerName("Son");
		customer.setContactName("HM");
		customer.setAddress("gangnam");
		customer.setCity("Seoul");
		customer.setPostalCode("8888");
		customer.setCountry("Korea");
		
		// 3. business 로직
//		dao.insert("kim", "lee", "gangnam", "seoul", "88888", "korea"); // xxxx
		
		try (Connection con = ds.getConnection()) {
			ok = dao.insert(con, customer); // oooo
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 4. add attribute
		
		// 5. forward/redirect
	}

boolean ok = false;

Customer 객체 customer 생성, 객체의 set 메소드로 값들을 추가

try 에서 ok = dao.insert(con, customer);

→ ok 는 false 였다가 dao.isert(con, customer) 결과 (true)로 바뀜

 

jdbc04.dao

CustomerDAO

public class CustomerDAO {

	public boolean insert(Connection con, Customer customer) {
		String sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) "
				+ "   VALUES (?, ?, ?, ?, ?, ?)";
		
		int rowCount = 0;
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			
			// ? 채우기 
			pstmt.setString(1, customer.getCustomerName());
			pstmt.setString(2, customer.getContactName());
			pstmt.setString(3, customer.getAddress());
			pstmt.setString(4, customer.getCity());
			pstmt.setString(5, customer.getPostalCode());
			pstmt.setString(6, customer.getCountry());
			
			rowCount = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rowCount == 1;
	}

기존 CustomerDAO 에서 public boolean insert(Connection con, Customer customer) 메소드 추가로 작성

return rowCount == 1; → rowCount 가 1이면 true, 1이 아니면 false 반환

sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (?, ?, ?, ?, ?, ?)" 

아래 try 에서 pstmt.setString(숫자 ,customer.get~); 로 ? 자리 채우기 (위 서블릿에서 customer.set~ 으로 값을 넣어줬음)

int rowCount = 0; rowCount = pstmt.executeUpdate(); → rowCount 는 0 이었다가 바뀜

executeUpdate

DML 인 INSESRT, UPDATE, DELETE 인 경우 추가/변경/삭제된 레코드 수 (행의 수)를 반환함

(DDL 인 CREATE, DROP, ALTER 인 경우 0을 반환)

따라서 여기에서 한 행이 추가되었으므로 1을 반환, 메소드는 true 를 반환

 

---

JDBC22Servlet

21 을 이번엔 Supplier 테이블로 연습

	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 분석/가공
		Supplier supplier = new Supplier();
		supplier.setSupplierName("Son");
		supplier.setContactName("HM");
		supplier.setAddress("gangnam");
		supplier.setCity("Seoul");
		supplier.setPostalCode("8888");
		supplier.setCountry("Korea");
		supplier.setPhone("01022223333");

		// 3. business 로직
		try (Connection con = ds.getConnection()) {
			ok = dao.insert(con, supplier); // oooo
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute

		// 5. forward/redirect
	}

SupplierDAO dao = new SupplierDAO 객체 생성

boolean ok = false

Supplier 객체 supplier 생성, supplier.set~ 메소드로 값들을 전달

ok = dao.insert(con, supplier) - DAO 로 부터 true 반환받음

 

SupplierDAO

public class SupplierDAO {

	public boolean insert(Connection con, Supplier supplier) {
		String sql = "INSERT INTO Suppliers (SupplierName, ContactName, Address, City, PostalCode, Country, Phone) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?) ";
		boolean ok = false;

		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());

			ok = pstmt.executeUpdate() == 1;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return ok;
	}

기존 SupplierDAO 에서 public boolean insert(Connection con, Supplier supplier) 메소드 추가로 작성

sql 코드 작성, boolean ok = false 로 선언 및 초기화, try 안에서 pstmt.setString 으로 supplier.get~ 으로 받은 값을 ? 에 입력

executeUpdate 가 1 이므로 ==1 은 true 반환해서 ok 는 true 가 됨

메소드는 true 반환

 

→ 그러니까 Servlet 에서 값들을 bean 으로 전달하고 

DAO 에서 sql 작성하고 '?' 에 값 받아서 채우고

 

---

JDBC23Servlet

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();
		List<String> list = null;

		// 3. business logic
		try (Connection con = ds.getConnection()) {
			list = dao.getCountryList(con);
		} catch (Exception e) {
			e.printStackTrace();
		}

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

		// 5. forward / redirect
		String path = "/WEB-INF/view/jdbc05/v23.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 address = request.getParameter("address");
		String city = request.getParameter("city");
		String postalCode = request.getParameter("postalCode");
		String country = request.getParameter("country");
		
		Customer customer = new Customer();
		customer.setCustomerName(customerName);
		customer.setContactName(contactName);
		customer.setAddress(address);
		customer.setCity(city);
		customer.setPostalCode(postalCode);
		customer.setCountry(country);

		// 3. business 로직
//				dao.insert("kim", "lee", "gangnam", "seoul", "88888", "korea"); // xxxx

		try (Connection con = ds.getConnection()) {
			ok = dao.insert(con, customer); // oooo
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute

		// 5. forward/redirect
	}

doPost 까지 작성했네.. ㅎㅎ

1) doGet

리스트 만들어서 dao 에서 받아온 country 리스트 저장, setAttribute 로 countryList 로 전달, v23.jsp 로 포워딩

2) doPost

boolean ok = false

각 컬럼 값을 getParameter 로 전달받아 저장

방금 저장한 각 값들을 Customer 객체 생성하여 메소드 set~ 을 이용하여 저장(전달)

try 에서 ok 값을 dao 에서 받음

 

→ doGet 에서 country 리스트를 jsp 파일로 넘겨주고 doPost 에서 컬럼 값들을 받아서 전달해줌, 그리고 ok 

 

jdbc05

v23.jsp

<body>
<div class="container">
	<div class="row">
		<div class="col">
			<h1>고객 등록</h1>
            
            <form method="post">
            	<div class="form-group">
            		<label for="input1">Customer Name</label>
            		<input type="text" class="form-control" id="input1" name="customerName">
            	</div>
            	<div class="form-group">
            		<label for="input2">Contact Name</label>
            		<input type="text" class="form-control" id="input2" name="contactName">
            	</div>
            	<div class="form-group">
            		<label for="input3">Address</label>
            		<input type="text" class="form-control" id="input3" name="address">
            	</div>
            	<div class="form-group">
            		<label for="input4">City</label>
            		<input type="text" class="form-control" id="input4" name="city">
            	</div>
            	<div class="form-group">
            		<label for="input5">Postal Code</label>
            		<input type="text" class="form-control" id="input5" name="postalCode">
            	</div>
            	<div class="form-group">
            		<label for="select1">Country</label>
            		<!-- select.form-control#select1 -->
                    <select id="select1" class="form-control" name="country">
                      <c:forEach items="${countryList }" var="country">
                        <option value="${country }">${country }</option>
                      </c:forEach>
                    </select>
            	</div>
              
                <input type="submit" value="등록" class="btn btn-outline-primary">
            </form>
		</div>
	</div>
</div>
</body>

각 컬럼에 들어갈 값을 직접 작성(혹은 선택)하여 post 방식으로 전달하게 한다

 

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 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 boolean insert(Connection con, Customer customer) {
		String sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) "
				+ "   VALUES (?, ?, ?, ?, ?, ?)";
		
		int rowCount = 0;
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			
			// ? 채우기 
			pstmt.setString(1, customer.getCustomerName());
			pstmt.setString(2, customer.getContactName());
			pstmt.setString(3, customer.getAddress());
			pstmt.setString(4, customer.getCity());
			pstmt.setString(5, customer.getPostalCode());
			pstmt.setString(6, customer.getCountry());
			
			rowCount = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rowCount == 1;
	}
}

더 작성된 것 없음, 메소드 3개

 

Country 는 목록에서 선택하도록 하고, 나머지는 직접 작성하도록 한다

입력한대로 추가된 것을 확인할 수 있다

---

JDBC24Servlet

- 23번 이번에는 Supplier 로 연습

doGet, doPost

	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<String> list = null;

		// 3. business logic
		try (Connection con = ds.getConnection()) {
			list = dao.getCountryList(con);
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attributes
		request.setAttribute("countryList", list);

		// 5. forward
		String path = "/WEB-INF/view/jdbc05/v24.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");
		SupplierDAO dao = new SupplierDAO();

		// 2. request 분석/ 가공
		String supplierName = request.getParameter("supplierName");
		String contactName = request.getParameter("contactName");
		String address = request.getParameter("address");
		String city = request.getParameter("city");
		String postalCode = request.getParameter("postalCode");
		String country = request.getParameter("country");
		String phone = request.getParameter("phone");

		Supplier supplier = new Supplier();

		supplier.setSupplierName(supplierName);
		supplier.setContactName(contactName);
		supplier.setAddress(address);
		supplier.setCity(city);
		supplier.setPostalCode(postalCode);
		supplier.setCountry(country);
		supplier.setPhone(phone);

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

		// 4. add attribute

		// 5. forward / redirect

	}

 

v24.jsp

<body>
  <div class="container">
    <div class="row">
      <div class="col">
        <h1>공급자 등록</h1>

        <form action="" method="post">
          <div class="form-group">
            <label for="input1">Supplier Name</label>
            <input type="text" class="form-control" id="input1" name="supplierName">
          </div>
          <div class="form-group">
            <label for="input2">Contact Name</label>
            <input type="text" class="form-control" id="input2" name="contactName">
          </div>
          <div class="form-group">
            <label for="input3">Address</label>
            <input type="text" class="form-control" id="input3" name="address">
          </div>
          <div class="form-group">
            <label for="input4">City</label>
            <input type="text" class="form-control" id="input4" name="city">
          </div>
          <div class="form-group">
            <label for="input5">Postal Code</label>
            <input type="text" class="form-control" id="input5" name="postalCode">
          </div>
          <div class="form-group">
            <label for="input6">Phone</label>
            <input type="text" class="form-control" id="input6" name="phone">
          </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 value="${country }">${country }</option>
              </c:forEach>
            </select>
          </div>
          
          <input type="submit" value="등록" class="btn btn-outline-primary">
        </form>
      </div>
    </div>
  </div>

</body>

 

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;
	}

	public boolean insert(Connection con, Supplier supplier) {
		String sql = "INSERT INTO Suppliers (SupplierName, ContactName, Address, City, PostalCode, Country, Phone) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?) ";
		boolean ok = false;

		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());

			ok = pstmt.executeUpdate() == 1;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return ok;
	}

더 작성된 것 없음, 메소드 3개 

---

07update.sql

w3schools - SQL UPDATE Statement : https://www.w3schools.com/sql/sql_update.asp  

♠ 컬럼 값 변경하기 : UPDATE ~ SET ~

UPDATE 테이블명 SET 컬럼1 이름 = 컬럼1 값, 컬럼2 이름 = 컬럼2 값, ... WHERE 특정 컬럼 명 = 특정 컬럼 값;

UPDATE Customers SET Address = 'yeoksam' WHERE CustomerID = 105;

---

jdbc06

JDBC25Servlet

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 분석/가공
		Customer customer = new Customer();
		customer.setCustomerName("KIM");
		customer.setContactName("gildong");
		customer.setCity("Seoul");
		customer.setCountry("Korea");
		customer.setAddress("gangnam");
		customer.setPostalCode("111111");
		customer.setCustomerID(105);
		
		// 3. business logic
		try (Connection con = ds.getConnection()) {
			ok = dao.update(con, customer);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 4. add attribute
		
		// 5. forward / redirect
	}

CustomerDAO 객체 생성하는 것 똑같고, boolean ok = false 작성한 것도 똑같음

Customer 객체 생성해서 각 컬럼 값 저장(전달)

try 에서 ok 값 받아오는 것도 똑같음

그럼 뭐가 다른데? → 이번에는 update!

일단 여기서는 try 안에 ok = dao.update(con, customer) 로 작성된 점이 다르다

왜냐면 아래에서 update 메소드를 사용할 거거든

그니까 결국 con 이랑 customer 전달해서 CustomerDAO 에서 update 메소드를 실행하게 만드는 코드였음

위에서는 insert 메소드 실행해야 하니까 insert 라고 썼겠고(executeUpdate 는 그냥 반환해주는 것)

 

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 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 boolean insert(Connection con, Customer customer) {
		String sql = "INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) "
				+ "   VALUES (?, ?, ?, ?, ?, ?)";
		
		int rowCount = 0;
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			
			// ? 채우기 
			pstmt.setString(1, customer.getCustomerName());
			pstmt.setString(2, customer.getContactName());
			pstmt.setString(3, customer.getAddress());
			pstmt.setString(4, customer.getCity());
			pstmt.setString(5, customer.getPostalCode());
			pstmt.setString(6, customer.getCountry());
			
			rowCount = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rowCount == 1;
	}

	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 boolean update(Connection con, Customer customer) 메소드 새로 작성됨

sql 작성하되 값들은 ? 로 작성

서블릿에서 전달받은 컬럼값을 저장해줌

업데이트이긴 한데 (기존 것을 바꾸는) 어차피 그 차이는 sql 코드가 만들고 여기서는 값만 저장해주면 됨

그리고 executeUpdate 어쩌고 해서 반환

뭐 암튼 잘 바뀌었다고 한다~

---

JDBC26Servlet

- 이번에는 Supplier

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

		// 2. request 분석/가공
		Supplier supplier = new Supplier();
		supplier.setSupplierName("KIM");
		supplier.setContactName("gildong");
		supplier.setCity("Seoul");
		supplier.setCountry("Korea");
		supplier.setAddress("gangnam");
		supplier.setPostalCode("111111");
		supplier.setPhone("01022222222");
		supplier.setSupplierID(32);

		// 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 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;
	}

	public boolean insert(Connection con, Supplier supplier) {
		String sql = "INSERT INTO Suppliers (SupplierName, ContactName, Address, City, PostalCode, Country, Phone) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?) ";
		boolean ok = false;

		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());

			ok = pstmt.executeUpdate() == 1;

		} catch (Exception e) {
			e.printStackTrace();
		}

		return ok;
	}

	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 boolean update(Connection con, Supplier supplier) 메소드 새로 작성됨

똑같음