course 2021/JDBC\DB
DB04 - 24일(sql06~07, jdbc21~26) - insert, update
코딩하는토끼
2021. 12. 7. 12:42
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) 메소드 새로 작성됨
똑같음