DB(baek)

2021-11-24 06insert 07 update(JDBC21Servlet~JDBC26Servlet)

Hesitater 2021. 11. 24. 18:31
728x90

 

 

2021-11-24

 

06insert

 

 

 

 

 

 

 

https://www.w3schools.com/sql/sql_insert.asp

 

SQL INSERT INTO Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

 

07update

https://www.w3schools.com/sql/sql_update.asp

 

SQL UPDATE Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

 

 

jdbc02 에는 Bean
jdbc04 에는 DAO

 

 

 

 

 

 

06 insert

 

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

1

2.

3.

4.

5.



테이블명 + 컬럼명 나열 

NULL < 값이 없다. 빈스트링을 NULL 로 인식 ( 버전마다 조금다름)

컬럼의 이름을 넣어주는게 좋다

ID를 괄호안에 안썻는데도 자동으로 추가가됐습니다
ID는 자동 추가인가요

각 레코드를 유일하게 구분할수 있는 컬럼이 존재해야한다.

보통 구분할수 있는 컬럼이 있어야하고

그걸 설정한다. 그걸 키라고 한다.


여기서 레코드를 구분할수 있는게 CustomerID이다

 

 

★06Insert06

 

06insert

USE test;

SELECT * FROM Customers ORDER BY CustomerID DESC;

INSERT INTO Customers VALUES (92, 'Kim', 'Kim', 'GangNam', 'Seoul', '88888', 'Korea');

INSERT INTO Customers (CustomerID, CustomerName) VALUES (93, 'Lee');

INSERT INTO Customers (CustomerID, ContactName) VALUES (94, 'Choi');

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (95, 'Park', 'JiSung', 'Yeoksam', 'Seoul', '77777', 'Korea');

-- 96번째, 97번째 records 추가 sql 작성, 실행
INSERT INTO Customers (CustomerName) VALUES ('Cha');

INSERT INTO Customers (CustomerID, CustomerName) VALUES (97, 'Son');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (97, 'Ji');

INSERT INTO Customers (CustomerName) VALUES ('jimin');
INSERT INTO Customers (CustomerName) VALUES ('bts');
INSERT INTO Customers (CustomerName) VALUES ('hot');

-- 두 개의 row(record, data)를 CustomerID 컬럼의 값을 직접 작성하지 않고 추가하는 sql 작성/실행
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('ses', 'sm', 'gangnam', 'Seoul', '33333', 'Korea');

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('finkl', 'ds', 'samseong', 'Seoul', '22222', 'Korea');


-- 
SELECT * FROM Suppliers ORDER BY SupplierID DESC;
INSERT INTO Suppliers (SupplierName, ContactName, Address, City, PostalCode, Country, Phone)
VALUE (?, ?, ?, ?, ?, ?, ?);

 

 

 

 

 

 

 

○결과값

 

 

 

 

★JDBC21Servlet, CustomerDAO

 

 

 

JDBC21Servlet

@WebServlet("/jdbc05/s21")

package jdbc05;

import java.io.IOException;
import java.sql.Connection;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import jdbc02.bean.Customer;
import jdbc04.dao.CustomerDAO;

/**
 * Servlet implementation class JDBC21Servlet
 */
@WebServlet("/jdbc05/s21")
public class JDBC21Servlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JDBC21Servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		// 0. 사전 작업
			ServletContext application = request.getServletContext();
			DataSource ds =(DataSource) application.getAttribute("dbpool");
			CustomerDAO dao = new CustomerDAO();
			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 logic( 주로 db작업)
			//dao.insert("kim", "lee", "gangnam", "seoul", "88888", "korea"); // xxxx
			
			try ( Connection con = ds.getConnection()) {
				ok =  dao.insert(con, customer);   // 이걸로
			} catch (Exception e) {
				e.printStackTrace();
			}
					
			// 3.1 커넥션 얻기
			
			// 3.2 statement 얻기
			
			// 3.3 쿼리 실행 (resultSet 얻기)
			
			// 3.4 resultSet 처리
		
		// 4. setattribute
		
		// 5. forward/ redirect
		
		
		
		
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

 

customerDAO

package jdbc04.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc02.bean.Customer;

	public class CustomerDAO {
			public List<String> getCountryList(Connection con) {
		List<String> list = new ArrayList<String>();

		String sql = "SELECT DISTINCT country FROM Customers ORDER BY country";

		try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {

			while (rs.next()) {
				list.add(rs.getString(1));
			}

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

		return list;
	}

	public List<Customer> getCustomerListByCountry(Connection con, String country) {
		List<Customer> list = new ArrayList<Customer>();
		String sql = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
				+ " FROM Customers WHERE Country = ?";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {

			pstmt.setString(1, country);

			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Customer cus = new Customer();

					int i = 1;
					cus.setCustomerID(rs.getInt(i++));
					cus.setCustomerName(rs.getString(i++));
					cus.setContactName(rs.getString(i++));
					cus.setAddress(rs.getString(i++));
					cus.setCity(rs.getString(i++));
					cus.setPostalCode(rs.getString(i++));
					cus.setCountry(rs.getString(i));

					list.add(cus);
				}
			}

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

	}

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

//			pstmt.executeUpdate();
			rowCount = pstmt.executeUpdate();

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

		return rowCount == 1;

	}

}

 

 

 

○결과값

JDBC21Servlet 실행전

 

JDBC21Servlet실행후

각각 추가 되었다

 

 

 

 

 

★JDBC22Servlet, SupplierDAO

 

 

JDBC21Servelt에서와 같이

테이블 Supplier에  추가해보기

Supplier Bean을 이용해서 넘긴다.

 

JDBC22Servlet

@WebServlet("/jdbc05/s22")

package jdbc05;

import java.io.IOException;
import java.sql.Connection;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import jdbc02.bean.Supplier;
import jdbc04.dao.SupplierDAO;

/**
 * Servlet implementation class JDBC22Servlet
 */
@WebServlet("/jdbc05/s22")
public class JDBC22Servlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JDBC22Servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		// 0. 사전 작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		SupplierDAO dao = new SupplierDAO();
		boolean ok = false;
		
		// 2. request 분석, 가공
			Supplier supplier = new Supplier();
			
			supplier.setSupplierName("Kim");
			supplier.setContactName("Taehee");
			supplier.setAddress("Cheongdam");
			supplier.setCity("Seoul");
			supplier.setPostalCode("0329");
			supplier.setCountry("Korea");
			supplier.setPhone("010-1971-0329");
		
		// 3. business logic( 주로 db작업)
			try (Connection con = ds.getConnection()) {
				ok = dao.insert(con, supplier);
			} catch (Exception e) {
				e.printStackTrace();
			}

		
		// 4. setattribute
		
		
		// 5. forward/ redirect
		
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

 

 

SupplierDAO

package jdbc04.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc02.bean.Supplier;

public class SupplierDAO {

	public List<String> getCountryList(Connection con) {
		List<String> list = new ArrayList<String>();

		String sql = "SELECT DISTINCT Country FROM Suppliers ORDER BY Country";

		try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {

			while (rs.next()) {
				list.add(rs.getString(1));
			}

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

		return list;
	}

	public List<Supplier> getSupplierList(Connection con, String country) {
		List<Supplier> list = new ArrayList<Supplier>();

		String sql = "SELECT SupplierID, SupplierName, ContactName, Address, City," + " PostalCode, Country, Phone  "
				+ "FROM Suppliers WHERE Country = ?";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {

			pstmt.setString(1, country);

			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Supplier sup = new Supplier();

					int i = 1;
					sup.setSupplierID(rs.getInt(i++));
					sup.setSupplierName(rs.getString(i++));
					sup.setContactName(rs.getString(i++));
					sup.setAddress(rs.getString(i++));
					sup.setCity(rs.getString(i++));
					sup.setPostalCode(rs.getString(i++));
					sup.setCountry(rs.getString(i++));
					sup.setPhone(rs.getString(i++));

					list.add(sup);
				}

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

		return list;
	}

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

}

 

 

○결과값

JDBC22Servlet 실행전

JDBC22Servlet 실행후

 

 

 

 

 

 

★JDBC23Servlet, v23.jsp

 

 

 

JDBC23Servlet

@WebServlet("/jdbc05/s23")

package jdbc05;

import java.io.IOException;
import java.sql.Connection;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import jdbc02.bean.Customer;
import jdbc04.dao.CustomerDAO;

/**
 * Servlet implementation class JDBC23Servlet
 */
@WebServlet("/jdbc05/s23")
public class JDBC23Servlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public JDBC23Servlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

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

		// 2. request 분석, 가공

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

		// 4. setattribute

		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 logic( 주로 db작업)
		// dao.insert("kim", "lee", "gangnam", "seoul", "88888", "korea"); // xxxx

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



		// 4. setattribute

		// 5. forward/ redirect

	}

}

 

v23.jsp

<!-- .container>.row>.col>h1{고객 등록} -->

<!-- form>.form-group*6>label+input.form-control#input$ -->
<!-- select.form-control#select1 -->
<!-- input:s.btn.btn-outline-primary -->
dopost 에서 JDBC21Servlet 에서 doget 에 했던일과 비슷

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>


<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

<link rel="stylesheet" href="<%= request.getContextPath() %>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>
	<!-- .container>.row>.col>h1{고객 등록} -->
	<div class="container">
		<div class="row">
			<div class="col">
				<h1>고객 등록</h1>

				<!-- form>.form-group*6>label+input.form-control#input$ -->
				<form method="post">
					<div class="from-group">
						<lable for="input1">Customer Name</lable>
						<input type="text" class="form-control" id="input1" name="customerName">
					</div>
					<div class="from-group">
						<lable for="input2">Contact Name</lable>
						<input type="text" class="form-control" id="input2"  name="contactName">
					</div>
					<div class="from-group">
						<lable for="input3">Address</lable>
						<input type="text" class="form-control" id="input3" name="address">
					</div>
					<div class="from-group">
						<lable for="input4">City</lable>
						<input type="text" class="form-control" id="input4" name="city">
					</div>
					<div class="from-group">
						<lable for="input5">PostalCode</lable>
						<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:s.btn.btn-outline-primary -->
					<input type="submit" value="등록" class="btn btn-outline-primary">
				</form>
			</div>
		</div>
	</div>


	<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
	<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>

 

CustomerDAO

package jdbc04.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc02.bean.Customer;

public class CustomerDAO {

	public List<String> getCountryList(Connection con) {
		List<String> list = new ArrayList<String>();

		String sql = "SELECT DISTINCT country FROM Customers ORDER BY country";

		try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {

			while (rs.next()) {
				list.add(rs.getString(1));
			}

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

		return list;
	}

	public List<Customer> getCustomerListByCountry(Connection con, String country) {
		List<Customer> list = new ArrayList<Customer>();
		String sql = "SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country "
				+ " FROM Customers WHERE Country = ?";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {

			pstmt.setString(1, country);

			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Customer cus = new Customer();

					int i = 1;
					cus.setCustomerID(rs.getInt(i++));
					cus.setCustomerName(rs.getString(i++));
					cus.setContactName(rs.getString(i++));
					cus.setAddress(rs.getString(i++));
					cus.setCity(rs.getString(i++));
					cus.setPostalCode(rs.getString(i++));
					cus.setCountry(rs.getString(i));

					list.add(cus);
				}
			}

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

	}

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

//			pstmt.executeUpdate();
			rowCount = pstmt.executeUpdate();

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

		return rowCount == 1;

	}

}

 

 

○결과값

초기화면에 값 집어넣은 상태

 

 

추가전 Query 

 

값등록후 쿼리

 

 

 

 

 

★JDBC24Servlet,v24.jsp

 

JDBC22,23Servlet 들 활용해서 

JDBC23Servet처럼 하기 대신 Supplier 으로

 

 

JDBC24Servlet

package jdbc05;

import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import jdbc02.bean.Supplier;
import jdbc04.dao.SupplierDAO;

/**
 * Servlet implementation class JDBC24Servlet
 */
@WebServlet("/jdbc05/s24")
public class JDBC24Servlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JDBC24Servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		// 0. 사전 작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		SupplierDAO dao = new SupplierDAO();
		List<String> list = null;
		
		// 2. request 분석, 가공
			Supplier supplier = new Supplier();
		
		// 3. business logic( 주로 db작업)
			try(Connection con = ds.getConnection()) {
				
				list = dao.getCountryList(con);
			} catch (Exception e) {
				e.printStackTrace();
			}
		
		// 4. setattribute
				request.setAttribute("countryList", list);
		
		// 5. forward/ redirect
				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();
		boolean ok = false;

		// 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 로직
//		dao.insert("kim", "lee", "gangnam", "seoul", "88888", "korea"); // xxxx
		try (Connection con = ds.getConnection()) {
			ok = dao.insert(con, supplier); // oooo
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4. add attribute

		// 5. forward/redirect
		
	}

}


v24.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>


<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

<link rel="stylesheet" href="<%= request.getContextPath() %>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">
<title>Insert title here</title>
</head>
<body>

	<!-- .container>.row>.col>h1{Supplier 등록} -->
	<div class="container">
		<div class="row">
			<div class="col">
				<h1>Supplier 등록</h1>
				<!-- form>.form-group*7>label+input.form-control#input$ -->
				<form method="post">
					<div class="form-group">
						<label for="input1">supplierName</label>
						<input type="text" class="form-control" id="input1" name="supplierName">
					</div>
					<div class="form-group">
						<label for="input2">contactName</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">postalCode</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>
					<div class="form-group">
						<label for="input7">phone</label>
						<input type="text" class="form-control" id="input7" name="phone">
					</div>
					<!-- input:s.btn.btn-outline-primary -->
					<input type="submit" value="등록" class="btn btn-outline-primary">
				</form>
			</div>
		</div>
	</div>

	<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
	<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>

 

SupplierDAO

package jdbc04.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc02.bean.Supplier;

public class SupplierDAO {

	public List<String> getCountryList(Connection con) {
		List<String> list = new ArrayList<String>();

		String sql = "SELECT DISTINCT Country FROM Suppliers ORDER BY Country";

		try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {

			while (rs.next()) {
				list.add(rs.getString(1));
			}

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

		return list;
	}

	public List<Supplier> getSupplierList(Connection con, String country) {
		List<Supplier> list = new ArrayList<Supplier>();

		String sql = "SELECT SupplierID, SupplierName, ContactName, Address, City," + " PostalCode, Country, Phone  "
				+ "FROM Suppliers WHERE Country = ?";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {

			pstmt.setString(1, country);

			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Supplier sup = new Supplier();

					int i = 1;
					sup.setSupplierID(rs.getInt(i++));
					sup.setSupplierName(rs.getString(i++));
					sup.setContactName(rs.getString(i++));
					sup.setAddress(rs.getString(i++));
					sup.setCity(rs.getString(i++));
					sup.setPostalCode(rs.getString(i++));
					sup.setCountry(rs.getString(i++));
					sup.setPhone(rs.getString(i++));

					list.add(sup);
				}

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

		return list;
	}

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

 

 

 

 

○결과값

초기화면

 

JDBC24Servlet 실행전 쿼리

 

JDBC24Servlet 실행후 값들 입력하고 등록후 쿼리

 

 

 

 

--------------------------------------------오후------------------------------------

 

★JDBC24Servlet1, v241.jsp

 


선생님 코드

JDBC22,23Servlet 들 활용해서 

 

 

JDBC24Servlet1

package jdbc05;

import java.io.IOException;
import java.sql.Connection;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import jdbc02.bean.Supplier;
import jdbc04.dao.SupplierDAO;

/**
 * Servlet implementation class JDBC24Servlet1
 */
@WebServlet("/jdbc05/s241")
public class JDBC24Servlet1 extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JDBC24Servlet1() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// 0. 사전 작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		SupplierDAO dao = new SupplierDAO();
		List<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/v241.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
		
		
	}

}


v241.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

<link rel="stylesheet" href="<%=request.getContextPath()%>/resource/css/icon/css/all.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css" integrity="sha384-zCbKRCUGaJDkqS1kPbPd7TveP5iyJE0EjAuZQTgFLD2ylzuqKfdKlfG/eSrtxUkn" crossorigin="anonymous">

<title>Insert title here</title>
</head>
<body>
  <!-- .container>.row>.col>h1{공급자 등록} -->
  <div class="container">
    <div class="row">
      <div class="col">
        <h1>공급자 등록</h1>

        <!-- form>(.form-group>label[for=input$]+input.form-control#input$)*6+.form-group>label[for=select1]+select.form-control#select1 -->
        <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:s.btn.btn-outline-primary -->
          <input type="submit" value="등록" class="btn btn-outline-primary">
        </form>
      </div>
    </div>
  </div>

  <script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-fQybjgWLrvvRgtW6bFlB7jaZrFsaBXjsOMm/tB9LTS58ONXgqbR9W8oWht/amnpF" crossorigin="anonymous"></script>
</body>
</html>

 

 

 

○결과값

초기값

각각 입력하고 등록시 쿼리에 잘 등록됨

 

 

 

 

 

07update.sql

 

 

예시와 같이 작성한다

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

 

★07update

SELECT * FROM Customers ORDER BY CustomerID DESC;

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

UPDATE Customers 
SET 
    Address = 'mancity',
    City = 'Suwon',
    PostalCode = '33333'
WHERE
    CustomerID = 105;
    
    
-- JDBC 용
UPDATE Customers
SET 
	CustomerName = ?,
    ContactName = ?,
    Address = ?,
    City = ?,
    PostalCode = ?,
    Country = ?
WHERE
    CustomerID = ?
;

-- Suppliers
SELECT * FROM Suppliers ORDER BY SupplierID DESC;

 

 

 

 

package jdbc06 에서..

 

 

★JDBC25Servlet, CustomerDAO

 

ok = dao.update(con, customer);   <-- 실제로 하고 싶은일 (이것을 위해 나머지 코드작성..)

 

한개만 바꾸기

 

JDBC25Servlet
@WebServlet("/jdbc06/s25")

package jdbc06;

import java.io.IOException;
import java.sql.Connection;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import jdbc02.bean.Customer;
import jdbc04.dao.CustomerDAO;

/**
 * Servlet implementation class JDBC25Servlet
 */
@WebServlet("/jdbc06/s25")
public class JDBC25Servlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JDBC25Servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		// 0. 사전 작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		CustomerDAO dao = new CustomerDAO();
		
		boolean ok = false;
		
		// 2. request 분석, 가공
		Customer customer = new Customer();
		
		customer.setCustomerName("Kim");
		customer.setContactName("taehee");
		customer.setCity("Seoul");
		customer.setCountry("Korea");
		customer.setAddress("gangnam");
		customer.setPostalCode("111111");
		customer.setCustomerID(104);
		
		// 3. business logic( 주로 db작업)
			
		
		
		try( Connection con = ds.getConnection()) {
			ok = dao.update(con, customer); 
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 4. setattribute
		// 5. forward/ redirect
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

 

CustomerDAO

package jdbc04.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc02.bean.Supplier;

public class SupplierDAO {

	public List<String> getCountryList(Connection con) {
		List<String> list = new ArrayList<String>();

		String sql = "SELECT DISTINCT Country FROM Suppliers ORDER BY Country";

		try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {

			while (rs.next()) {
				list.add(rs.getString(1));
			}

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

		return list;
	}

	public List<Supplier> getSupplierList(Connection con, String country) {
		List<Supplier> list = new ArrayList<Supplier>();

		String sql = "SELECT SupplierID, SupplierName, ContactName, Address, City," + " PostalCode, Country, Phone  "
				+ "FROM Suppliers WHERE Country = ?";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {

			pstmt.setString(1, country);

			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Supplier sup = new Supplier();

					int i = 1;
					sup.setSupplierID(rs.getInt(i++));
					sup.setSupplierName(rs.getString(i++));
					sup.setContactName(rs.getString(i++));
					sup.setAddress(rs.getString(i++));
					sup.setCity(rs.getString(i++));
					sup.setPostalCode(rs.getString(i++));
					sup.setCountry(rs.getString(i++));
					sup.setPhone(rs.getString(i++));

					list.add(sup);
				}

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

		return list;
	}

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

 

 

 

 

○결과값

JDBC25Servlet 실행전 쿼리

JDBC25Servlet 실행후 쿼리

 

 

 

 

★JDBC26Servlet , SupplierDAO 


Supplier 업데이트하는

JDBC26Servlet , SupplierDAO 작성

 

 

JDBC26Servlet

@WebServlet("/jdbc06/s26")

package jdbc06;

import java.io.IOException;
import java.sql.Connection;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import jdbc02.bean.Supplier;
import jdbc04.dao.SupplierDAO;

/**
 * Servlet implementation class JDBC26Servlet
 */
@WebServlet("/jdbc06/s26")
public class JDBC26Servlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JDBC26Servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		// 0. 사전 작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		SupplierDAO dao = new SupplierDAO();
		boolean ok = false;
		
		// 2. request 분석, 가공
		Supplier supplier = new Supplier();
		
		supplier.setSupplierName("lim");
		supplier.setContactName("sujung");
		supplier.setAddress("kunkook");
		supplier.setCity("Seoul");
		supplier.setCountry("Korea");
		supplier.setPostalCode("");
		supplier.setPhone("010-1979-0711");
		supplier.setSupplierID(38);
		
		
		// 3. business logic( 주로 db작업)
		try (Connection con = ds.getConnection()) {
				ok = dao.update(con, supplier);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
			// 3.1 커넥션 얻기	
			// 3.2 statement 얻기		
			// 3.3 쿼리 실행 (resultSet 얻기)		
			// 3.4 resultSet 처리	
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

 

SupplierDAO

package jdbc04.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jdbc02.bean.Supplier;

public class SupplierDAO {

	public List<String> getCountryList(Connection con) {
		List<String> list = new ArrayList<String>();

		String sql = "SELECT DISTINCT Country FROM Suppliers ORDER BY Country";

		try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {

			while (rs.next()) {
				list.add(rs.getString(1));
			}

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

		return list;
	}

	public List<Supplier> getSupplierList(Connection con, String country) {
		List<Supplier> list = new ArrayList<Supplier>();

		String sql = "SELECT SupplierID, SupplierName, ContactName, Address, City," + " PostalCode, Country, Phone  "
				+ "FROM Suppliers WHERE Country = ?";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {

			pstmt.setString(1, country);

			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Supplier sup = new Supplier();

					int i = 1;
					sup.setSupplierID(rs.getInt(i++));
					sup.setSupplierName(rs.getString(i++));
					sup.setContactName(rs.getString(i++));
					sup.setAddress(rs.getString(i++));
					sup.setCity(rs.getString(i++));
					sup.setPostalCode(rs.getString(i++));
					sup.setCountry(rs.getString(i++));
					sup.setPhone(rs.getString(i++));

					list.add(sup);
				}

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

		return list;
	}

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

 

 

 

○결과값

JDBC26Servlet 실행전 쿼리

 

JDBC26Servlet 실행후 쿼리

 

 

 

아마 다음 시간에 이것들을 jsp로 보여주는것을 할 것이다.

저작자표시 변경금지

'DB(baek)' 카테고리의 다른 글

2021-11-26-Servlet31~38 and SQL Constraints  (0) 2021.11.26
2021-11-25 08delete,Createtable, datatype.. Servlet27~30  (0) 2021.11.25
2021-11-23 preparedStatement ,DAO  (0) 2021.11.23
2021-11-22 JDBC03Servlet~JDBC10Servlet  (0) 2021.11.22
2021-11-19 sql01~05 + JDBCServlet  (2) 2021.11.19

'DB(baek)'의 다른글

  • 현재글2021-11-24 06insert 07 update(JDBC21Servlet~JDBC26Servlet)

관련글

댓글

티스토리툴바