DB(baek)
2021-11-25 08delete,Createtable, datatype.. Servlet27~30
Hesitater
2021. 11. 25. 18:32
728x90
2021-11-25
2021-11-25 08delete,Createtable, datatype.. Servlet27~30
★JDBC27Servlet, , CustomerDAO, v27.jsp
JDBC25Servlet + 화면 만들기
get -> jsp(form) 보여주기
post -> 실제 db 변경
update 받아서 하는건 지금 생략
update는 변경전 정보가 담겨져있어야함
변경전 정보를 한번 확인해야함
v27.jsp
form>(.form-group>label[for=input$]+input.form-control#input$[name])*6
.form-group>label[for=select1]+select#select1.form-control
더보기
JDBC27Servlet
JDBC25Servlet + 화면 만들기
get -> jsp(form) 보여주기
post -> 실제 db 변경
update 받아서 하는건 지금 생략
update는 변경전 정보가 담겨져있어야함
변경전 정보를 한번 확인해야함
JDBC27Servlet
@WebServlet("/jdbc06/s27")
v27.jsp
form>(.form-group>label[for=input$]+input.form-control#input$[name])*6
.form-group>label[for=select1]+select#select1.form-control
수정할떄 이전 값이 존재 해야하니깐 4. addattribute에서 넘겨준다
http://localhost:8080/jsp1026/jdbc06/s27
http://localhost:8080/jsp1026/jdbc06/s27?id=1
http://localhost:8080/jsp1026/jdbc06/s27?id=2
결과값 나와야함
바꾸고 서밋 버튼 누르면 수정되게
서밋버튼 눌렀을때 post 방식으로 날라가도록
25서블릿 doget의내용을 27 dopost 에 붙여넣기
customerID 값은 만든기없어서 만들어야하는데
그걸 히든으로?
<form action="" method="post">
<input type="hidden" name="customerID" value="${customer.customerID }">
27서블릿
int customerID = Integer.parseInt(request.getParameter("customerID"));
customer.setCustomerID(customerID);
예시로
http://localhost:8080/jsp1026/jdbc06/s27?id=105
http://localhost:8080/jsp1026/jdbc06/s27?id=104
http://localhost:8080/jsp1026/jdbc06/s27?id=103
JDBC27Servlet
@WebServlet("/jdbc06/s27")
package jdbc06;
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 JDBC27Servlet
*/
@WebServlet("/jdbc06/s27")
public class JDBC27Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC27Servlet() {
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();
Customer customer = null;
List<String> countryList = null;
// 2. request parameter 분석, 가공
String idStr = request.getParameter("id");
int customerID = Integer.parseInt(idStr);
// 3. business logic( 주로 db작업)
try ( Connection con = ds.getConnection()
) {
customer = dao.selectedById(con, customerID);
countryList = dao.getCountryList(con);
} catch (Exception e) {
e.printStackTrace();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("customer", customer);
request.setAttribute("countryList", countryList);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc06/v27.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 0. 사전 작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
CustomerDAO dao = new CustomerDAO();
boolean ok = false;
// 2. request 분석, 가공
String customerName = request.getParameter("customerName");
String contactName = request.getParameter("contactName");
String city = request.getParameter("city");
String country = request.getParameter("country");
String address = request.getParameter("address");
String postalCode = request.getParameter("postalCode");
int customerID = Integer.parseInt(request.getParameter("customerID"));
Customer customer = new Customer();
customer.setCustomerName(customerName);
customer.setContactName(contactName);
customer.setCity(city);
customer.setCountry(country);
customer.setAddress(address);
customer.setPostalCode(postalCode);
customer.setCustomerID(customerID);
// 3. business logic( 주로 db작업)
try( Connection con = ds.getConnection()) {
ok = dao.update(con, customer);
} catch (Exception e) {
e.printStackTrace();
}
// 4. setattribute
// 5. forward/ redirect
}
}
CustomerDAO
( selectedById, getCountryList 사용) (더있으나 2개만 가져옴)
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 Customer selectedById(Connection con, int customerID) {
String sql = "SELECT CustomerName, ContactName, Address, City, "
+ "PostalCode, Country "
+ " FROM Customers "
+ "WHERE CustomerID = ? ";
Customer customer = new Customer();
try ( PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, customerID);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
// String customerName = rs.getString(1);
String customerName = rs.getString("CustomerName");
String contactName = rs.getString("ContactName");
String address = rs.getString("Address");
String city = rs.getString("City");
String postalCode = rs.getString("PostalCode");
String country = rs.getString("Country");
customer.setCustomerID(customerID);
customer.setCustomerName(customerName);
customer.setContactName(contactName);
customer.setAddress(address);
customer.setCity(city);
customer.setPostalCode(postalCode);
customer.setCountry(country);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return customer;
}
}
v27.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$[name])*5 -->
<form action="" method="post">
<input type="hidden" name="customerID" value="${customer.customerID }">
<div class="form-group">
<label for="input1">CustomerName</label>
<input value="${customer.customerName }" type="text" class="form-control" id="input1" name="customerName">
</div>
<div class="form-group">
<label for="input2">ContactName</label>
<input value="${customer.contactName }" type="text" class="form-control" id="input2" name="contactName">
</div>
<div class="form-group">
<label for="input3">Address</label>
<input value="${customer.address }" type="text" class="form-control" id="input3" name="address">
</div>
<div class="form-group">
<label for="input4">City</label>
<input value="${customer.city }" type="text" class="form-control" id="input4" name="city">
</div>
<div class="form-group">
<label for="input5">PostalCode</label>
<input value="${customer.postalCode }" type="text" class="form-control" id="input5" name="postalCode">
</div>
<!-- .form-group>label[for=select1]+select#select1.form-control -->
<div class="form-group">
<label for="select1">Country</label>
<select name="country" id="select1" class="form-control">
<c:forEach items="${countryList }" var="country">
<option ${customer.country eq country ? 'selected' : '' } value=${country }>${country }</option>
</c:forEach>
</select>
</div>
<!-- input: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>
JDBC28Servlet 실행시 오류나옴 그런건 조금 제외하고 예를 들어
http://localhost:8080/jsp1026/jdbc06/s27?id=103(CustomerID) 로 결과값으 해보자
○결과값
처음실행시에 쿼리스트링값 줘서
http://localhost:8080/jsp1026/jdbc06/s27?id=103
수정하기전 쿼리
수정 입력버튼 클릭
★JDBC28Servlet, Supplier, v28.jsp
JDBC28Servlet , Supplier
JDBC28Servlet의 doGet JDBC27의doGet 내용 참조
JDBC28Servlet의 dopost JDBC26Servlet의 doGet 내용 참고
JDBC28Servlet
@WebServlet("/jdbc06/s28")
package jdbc06;
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 JDBC28Servlet
*/
@WebServlet("/jdbc06/s28")
public class JDBC28Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC28Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// get : 화면 forward
// 0. 사전 작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
SupplierDAO dao = new SupplierDAO();
Supplier supplier = new Supplier();
List<String> countryList = null;
// 2. request 분석, 가공
String idStr = request.getParameter("id");
int supplierID = Integer.parseInt(idStr);
// int supplierID = Integer.parseInt(request.getParameter("id"));
// 3. business logic( 주로 db작업)
try (Connection con = ds.getConnection()) {
supplier = dao.selectById(con, supplierID);
countryList = dao.getCountryList(con);
} catch (Exception e) {
e.printStackTrace();
}
// 4. setattribute
request.setAttribute("supplier", supplier);
request.setAttribute("countryList", countryList);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc06/v28.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// post : update db
// 0. 사전 작업
ServletContext application = request.getServletContext();
DataSource ds = (DataSource) application.getAttribute("dbpool");
SupplierDAO dao = new SupplierDAO();
boolean ok = false;
// 2. request 분석, 가공
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");
int supplierID = Integer.parseInt(request.getParameter("supplierID"));
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);
supplier.setSupplierID(supplierID);
// 위에 두식 합친것
/*
Supplier supplier = new Supplier();
supplier.setSupplierName(request.getParameter("supplierName"));
supplier.setContactName(request.getParameter("contactName"));
supplier.setCity(request.getParameter("city"));
supplier.setCountry(request.getParameter("country"));
supplier.setAddress(request.getParameter("address"));
supplier.setPostalCode(request.getParameter("postalCode"));
supplier.setPhone(request.getParameter("phone"));
supplier.setSupplierID(Integer.parseInt(request.getParameter("supplierID")));
*/
// 3. business logic( 주로 db작업)
try (Connection con = ds.getConnection()) {
ok = dao.update(con, supplier);
} catch (Exception e) {
e.printStackTrace();
}
// 4. add setattribute
// 5. forward / redirect
}
}
SupplierDAO
(selectById, getCountryList 만 넣엇음 코드가 길어서)
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 Supplier selectById(Connection con, int supplierID) {
String sql = "SELECT SupplierName, ContactName, Address, City, PostalCode, Country, Phone "
+ " FROM Suppliers "
+ " WHERE SupplierID = ? ";
Supplier supplier = new Supplier();
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, supplierID);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
String supplierName = rs.getString("SupplierName");
String contactName = rs.getString("ContactName");
String address = rs.getString("Address");
String city = rs.getString("City");
String postalCode = rs.getString("PostalCode");
String country = rs.getString("Country");
String phone = rs.getString("Phone");
supplier.setSupplierID(supplierID);
supplier.setSupplierName(supplierName);
supplier.setContactName(contactName);
supplier.setAddress(address);
supplier.setCity(city);
supplier.setPostalCode(postalCode);
supplier.setCountry(country);
supplier.setPhone(phone);
// 위에 코드를 한번에쓴것
/*
supplier.setAddress(rs.getString("Address"));
supplier.setSupplierName(rs.getString("SupplierName"));
supplier.setContactName(rs.getString("ContactName"));
supplier.setCity(rs.getString("City"));
supplier.setPostalCode(rs.getString("PostalCode"));
supplier.setCountry(rs.getString("Country"));
supplier.setPhone(rs.getString("Phone"));
supplier.setSupplierID(supplierID);
*/
}
}
} catch (Exception e) {
e.printStackTrace();
}
return supplier;
}
}
v28.jsp
(v27.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>label[for=input$]+input.form-control#input$[name])*7 -->
<form action="" method="post">
<input type="hidden" name="supplierID" value="${supplier.supplierID }">
<div class="form-group">
<label for="input1">SupplierName</label>
<input value="${supplier.supplierName }" type="text" class="form-control" id="input1" name="supplierName">
</div>
<div class="form-group">
<label for="input2">ContactName</label>
<input value="${supplier.contactName }" type="text" class="form-control" id="input2" name="contactName">
</div>
<div class="form-group">
<label for="input3">Address</label>
<input value="${supplier.address }" type="text" class="form-control" id="input3" name="address">
</div>
<div class="form-group">
<label for="input4">City</label>
<input value="${supplier.city }" type="text" class="form-control" id="input4" name="city">
</div>
<div class="form-group">
<label for="input5">PostalCode</label>
<input value="${supplier.postalCode }" type="text" class="form-control" id="input5" name="postalCode">
</div>
<div class="form-group">
<label for="input6">Phone</label>
<input value="${supplier.phone }" type="text" class="form-control" id="input6" name="phone">
</div>
<!-- .form-group>label[select1]+select#select1.form-control -->
<div class="form-group">
<label for="" select1="">Country</label>
<select name="country" id="select1" class="form-control">
<c:forEach items="${countryList }" var="country">
<option ${supplier.country eq country ? 'selected' : '' } 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>
JDBC28Servlet 실행시 오류는 조금 넘기고 쿼리스트링값 준것으로
예를들어
http://localhost:8080/jsp1026/jdbc06/s28?id=31
http://localhost:8080/jsp1026/jdbc06/s28?id=32
(?id=~~ 로 들어가면 기존의 값들이 함께 나온다. 그것도 구현 해야한다는걸 )
이런식으로 들어가서 수정해보기
수정할 id 들어가서 (http://localhost:8080/jsp1026/jdbc06/s28?id=39)
수정본 쓴것
○결과값
수정하기전 쿼리값(SupplierID = 39)
수정이후 쿼리값 (SupplierID=39)
★08delete.sql
DELETE
형태
DELETE FROM table_name WHERE condition;
DELETE FROM table_name;
세이프모드 여러개의 것을 지울때 안지워지기도함
SET SQL_SAFE_UPDATES = 0; 세이프모드 해제
SET SQL_SAFE_UPDATES = 1; 세이프 모드 설정
USE test;
SELECT * FROM Customers ORDER BY CustomerID DESC;
DELETE FROM Customers WHERE CustomerID = 105;
DELETE FROM Customers WHERE Address = 'gangnam';
DELETE FROM Customers; -- xxxx 이런거 쓰지마 절대
SET SQL_SAFE_UPDATES = 0; -- 세이프 모드 해제
SET SQL_SAFE_UPDATES = 1; -- 세이프 모드 설정
○결과값
★JDBC29Servlet, CustomerDAO
JDBC29Servlet
@WebServlet("/jdbc07/s29")
// 3. business logic( 주로 db작업)
dao.deleteById(con, customerID);
이걸 하고 싶었는데 이걸위해 나머지 작성
기능만을
key : 가능한 변경된일이 적어야함
확인할때
SELECT * FROM Customers ORDER BY CustomerID DESC;
JDBC29Servlet
@WebServlet("/jdbc07/s29")
package jdbc07;
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 jdbc04.dao.CustomerDAO;
/**
* Servlet implementation class JDBC29Servlet
*/
@WebServlet("/jdbc07/s29")
public class JDBC29Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC29Servlet() {
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 분석, 가공
int customerID = Integer.parseInt(request.getParameter("id"));
// 3. business logic( 주로 db작업)
// dao.deleteById(con, customerID); 이거 만들려고 나머지작업
try (Connection con = ds.getConnection()) {
ok = dao.deleteById(con, customerID);
} 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
(deleteById부분 만)
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 boolean deleteById(Connection con, int customerID) {
String sql = "DELETE FROM Customers WHERE CustomerID = ?";
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, customerID);
int count = pstmt.executeUpdate();
return count == 1;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
{
JDBC29Servlet 실행해서 확인
○결과값
http://localhost:8080/jsp1026/jdbc07/s29?id=94 실행전
http://localhost:8080/jsp1026/jdbc07/s29?id=94 실행후
CustomerID = 94 없어짐
★
JDBC30Servlet,SupplierDAO
JDBC30Servlet,SupplierDAO 메서드 작성
JDBC30Servlet에서 dao.deleteById(con, supplierID); 을 실행시키기위한
ex)
http://localhost:8080/jsp1026/jdbc07/s30?id=41
확인할때
SELECT * FROM Suppliers ORDER BY SupplierID DESC;
JDBC30Servlet
@WebServlet("/jdbc07/s30")
package jdbc07;
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 jdbc04.dao.SupplierDAO;
/**
* Servlet implementation class JDBC30Servlet
*/
@WebServlet("/jdbc07/s30")
public class JDBC30Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC30Servlet() {
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 분석, 가공
int supplierID = Integer.parseInt(request.getParameter("id"));
// 3. business logic( 주로 db작업)
// dao.deleteById(con, supplierID); 이거 작성하려고 만듬
try (Connection con = ds.getConnection()) {
ok = dao.deleteById(con, supplierID);
} 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
(deleteById 쪽만)
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 boolean deleteById(Connection con, int supplierID) {
String sql = "DELETE FROM Suppliers "
+ " WHERE SupplierID = ?";
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, supplierID);
int count = pstmt.executeUpdate();
return count == 1;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
}
○결과값
http://localhost:8080/jsp1026/jdbc07/s30?id=31 실행전
http://localhost:8080/jsp1026/jdbc07/s30?id=31 실행후
(SupplierID = 31) 사라짐
--------------------------------------오후-------------------------------------------
SQL Create DB
SQL Drop DB
SQL Backup DB
https://www.w3schools.com/sql/sql_create_table.asp
SQL CREATE TABLE 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
★create DB
CREATE TABLE 문은 데이터베이스에 새 테이블을 만드는 데 사용됩니다.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);대소문자 상황따라 다름 (회사,버전,..등등)
CHAR(size)
고정 길이 문자열(문자, 숫자 및 특수 문자를 포함할 수 있음). 크기 매개 변수는 열 길이를 문자 단위로 지정합니다. 이 길이는 0 ~ 255 사이일 수 있습니다. 기본값은 1입니다.
VARCHAR(size)
가변 길이 문자열(문자, 숫자 및 특수 문자를 포함할 수 있음). 크기 매개 변수는 최대 열 길이를 문자 단위로 지정합니다. 0 ~ 65535 사이일 수 있습니다.
컬럼명 다음에 datatype를 입력
()안에는 길이라고 생각해도됨
CREATE TABLE mytable1 (
col1 CHAR(1),
col2 CHAR(2),
col3 CHAR(3),
col4 CHAR(4)
);
09create-table-datatype-string.sql
USE test;
-- CHAR
CREATE TABLE mytable1 (
col1 CHAR(1),
col2 CHAR(2),
col3 CHAR(3),
col4 CHAR(4)
);
INSERT INTO mytable1 (col1, col2, col3, col4)
VALUES ('a', 'ab', 'abc', 'abcd');
SELECT * FROM mytable1;
INSERT INTO mytable1 (col1)
VALUES ('ab'); -- 안됨
INSERT INTO mytable1 (col4)
VALUES ('ab');
-- VARCHAR
CREATE TABLE mytable2 (
c1 VARCHAR(1),
c2 VARCHAR(2),
c3 VARCHAR(3),
c4 VARCHAR(4)
);
INSERT INTO mytable2 (c1, c2, c3, c4) VALUES ('a', 'ab', 'abc', 'abcd');
INSERT INTO mytable2 (c2) VALUES ('abc'); -- X
INSERT INTO mytable2 (c4) VALUES ('ab');
SELECT * FROM mytable2;
-- 연습: 2개의 테이블 mytable3, mytable4 만들기
-- VARCHAR, CHAR 타입을 사용
CREATE TABLE mytable3 (
name VARCHAR(11),
address VARCHAR(255),
postCode CHAR(5)
);
INSERT INTO mytable3 (name, address, postCode) VALUES ('kim', 'seoul', '00000');
INSERT INTO mytable3 (name, address, postCode) VALUES ('lee', 'busan', '00');
SELECT * FROM mytable3;
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
SET sql_mode = '';
CREATE TABLE mytable4 (
id VARCHAR(255),
nickName VARCHAR(255),
note VARCHAR(65535)
);
DESC mytable4;
INSERT INTO mytable4 (id, nickName, note) VALUES ('donald', 'trump', 'president');
select * from mytable4;○결과값
★10create-table-datatype-numeric.sql
10create-table-datatype-numeric.sql
USE test;
CREATE TABLE mytable5 (
name VARCHAR(255),
age INT
);
DESC mytable5;
INSERT INTO mytable5 (name, age) VALUES ('son', 77);
SELECT * FROM mytable5;
CREATE TABLE mytable6 (
name VARCHAR(255),
score DOUBLE
);
DESC mytable6;
INSERT INTO mytable6 (name, score) VALUES ('park', 3.14);
SELECT * FROM mytable6;
CREATE TABLE mytable7 (
col1 DECIMAL(3),
col2 DECIMAL(5, 2)
);
INSERT INTO mytable7 (col1, col2) VALUES (100, 100.33);
SELECT * FROM mytable7;
INSERT INTO mytable7 (col1) VALUES (1100);
INSERT INTO mytable7 (col2) VALUES (100.123);
INSERT INTO mytable7 (col2) VALUES (1100.12);
-- 연습: mytable8 만들기
CREATE TABLE mytable8 (
id INT,
name VARCHAR(255),
password VARCHAR(255),
score DOUBLE
);
DESC mytable8;
○결과값
★10create-table-datatype-time.sql
10create-table-datatype-time.sql
USE test;
CREATE TABLE mytable9 (
col1 DATE,
col2 DATETIME
);
DESC mytable9;
SELECT * FROM mytable9;
INSERT INTO mytable9 (col1) VALUES ('2021-11-25');
INSERT INTO mytable9 (col2) VALUES('2021-11-25 12:30:10');
-- 현재시간 알고싶다
SELECT now();
INSERT INTO mytable9 (col1, col2) VALUES (now(), now());
○결과값
★11drop-table.sql
DROP TABLE 문은 데이터베이스의 기존 테이블을 삭제하는 데 사용됩니다.
TRUNCATE TABLE 문은 테이블 내부의 데이터를 삭제하는 데 사용되지만 테이블 자체는 삭제되지 않습니다.
11drop-table.sql
DROP TABLE table_name;
TRUNCATE TABLE table_name;이건 그냥 안쓴다고 보면된다 테이블 삭제관련이지만
사용하지않는다 절대 네버