DB(baek)
2021-11-26-Servlet31~38 and SQL Constraints
Hesitater
2021. 11. 26. 18:03
728x90
2021-11-26
double은 정말~ 정확하진 않다( 과학 통계,금융등은 DECIMAL(size, d) 많이씀 우리는 편의상 DOUBLE사용)
Constraints, primary-key, alter-table
★JDBC31Servlet
일단 서블릿안에서 해결함
SQL 에서 테이블 생성후 JDBC31Servlet 실행해서 값 넣기
JDBC31Servlet
package jdbc08;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
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;
/**
* Servlet implementation class JDBC31Servlet
*/
@WebServlet("/jdbc08/s31")
public class JDBC31Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC31Servlet() {
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");
// 2. request 분석, 가공
String sql = "INSERT INTO mytable10 (col1, col2, col3, col4, col5, col6, col7) "
+ " VALUES (?, ?, ?, ?, ?, ?, ?)";
// 3. business logic( 주로 db작업)
try (Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, 9999999); //int
pstmt.setDouble(2, 3.14); //DOUBLE
pstmt.setDouble(3, 1234567.1234); //DEC(10,3)
pstmt.setString(4, "ABC"); // CHAR(3)
pstmt.setString(5, "Hello World"); //VARCHAR
pstmt.setDate(6, java.sql.Date.valueOf("2021-11-26")); //DATE
pstmt.setTimestamp(7, java.sql.Timestamp.valueOf("2021-11-26 09:33:12")); //DATETIME
pstmt.executeQuery();
} 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);
}
}
12sample-jdbc31.sql
CREATE TABLE mytable10 (
col1 int,
col2 double,
col3 DEC(10, 3),
col4 CHAR(3),
col5 VARCHAR(255),
col6 DATE,
col7 DATETIME
);
SELECT * FROM mytable10; -- 확인용
○결과값
CREATE TABLE mytable10 (); 이후 SELECT *FROM mytable10;으로 확인
JDBC31Servlet 실행후 SELECT * FROM mytable10; 으로확인하면 값이 나옴
★JDBC32Servlet
셀렉트에서 얻어오는 방법 ( 일단 console창에 )
JDBC31Servlet에서 입력한 것을 읽고 싶었어요
// 자동 박싱 언박싱 되고
//1번과2번 같은효과 , 3번과 4번 같은효과
//차이는 참조타입이면 null값이 들어갈 수 있다.
1번-> int col1 = rs.getInt("col1"); // INT
2번-> Integer col1 = rs.getInt("col1"); // INT
3번-> double col2 = rs.getDouble("col2"); // DOUBLE
4번-> Double col2 = rs.getDouble("col2"); // DOUBLE
JDBC32Servlet
@WebServlet("/jdbc08/s32")
package jdbc08;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
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;
/**
* Servlet implementation class JDBC32Servlet
*/
@WebServlet("/jdbc08/s32")
public class JDBC32Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC32Servlet() {
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");
String sql = "SELECT col1, col2, col3, col4, col5, col6, col7 FROM mytable10";
try (Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);) {
while (rs.next()) {
// int col1 = rs.getInt("col1"); // INT
Integer col1 = rs.getInt("col1"); // INT
// double col2 = rs.getDouble("col2"); // DOUBLE
Double col2 = rs.getDouble("col2"); // DOUBLE
double col3 = rs.getDouble("col3"); // DEC(10,3)
String col4 = rs.getString("col4"); // CHAR(3)
String col5 = rs.getString("col5"); // VARCHAR(255)
java.sql.Date col6 = rs.getDate("col6"); // DATE
// Local Date
java.sql.Timestamp col7 = rs.getTimestamp("col7"); // DATETIME
//LocalDateTime
System.out.println(col1 + ", " + col2 + ", " + col3 + ", " + col4 + ", "
+ col5 + ", " + col6 + ", " + col7 );
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @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);
}
}
○결과값
현재 SELECT *FROM mytable10; 확인한 결과값
console창에 출력
★JDBC33Servlet
자바안에서 Date, DATETIME 이 따로 있다
더보기
java.time
java.time.LocalDate date; // Local Date
java.time.LocalDateTime dateTime; // LocalDateTime
java.sql.Date col6 = rs.getDate("col6"); // DATE
java.sql.Timestamp col7 = rs.getTimestamp("col7"); // DATETIME
java.time.LocalDate date = col6.toLocalDate(); // Local Date
java.time.LocalDateTime dateTime = col7.toLocalDateTime(); // LocalDateTime
System.out.println(col6 + ", " + col7);
System.out.println(date + ", " + dateTime);
JDBC33Servlet
@WebServlet("/jdbc08/s33")
package jdbc08;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
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;
/**
* Servlet implementation class JDBC33Servlet
*/
@WebServlet("/jdbc08/s33")
public class JDBC33Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC33Servlet() {
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");
String sql = "SELECT col1, col2, col3, col4, col5, col6, col7 FROM mytable10";
try (Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);) {
while (rs.next()) {
// int col1 = rs.getInt("col1"); // INT
java.sql.Date col6 = rs.getDate("col6"); // DATE
java.sql.Timestamp col7 = rs.getTimestamp("col7"); // DATETIME
java.time.LocalDate date = col6.toLocalDate(); // Local Date
java.time.LocalDateTime dateTime = col7.toLocalDateTime(); // LocalDateTime
System.out.println(col6 + ", " + col7);
System.out.println(date + ", " + dateTime);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @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);
}
}
○결과값
★JDBC34Servlet
31의 doget 메서드 이용해서 34doget에 복붙하고
LocalDate, LocalDateTime
java.time.LocalDate date = LocalDate.parse("2021-11-26");
java.time.LocalDateTime dateTime = LocalDateTime.parse("2021-11-26T09:33:12"); //시간앞에 T붙어야함
pstmt.setDate(6, java.sql.Date.valueOf(date)); //DATE
pstmt.setTimestamp(7, java.sql.Timestamp.valueOf(dateTime)); //DATETIME
JDBC34Servlet
@WebServlet("/jdbc08/s34")
package jdbc08;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.time.LocalDate;
import java.time.LocalDateTime;
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;
/**
* Servlet implementation class JDBC34Servlet
*/
@WebServlet("/jdbc08/s34")
public class JDBC34Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC34Servlet() {
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");
// 2. request 분석, 가공
String sql = "INSERT INTO mytable10 (col1, col2, col3, col4, col5, col6, col7) VALUES (?, ?, ?, ?, ?, ?, ?)";
// 3. business logic( 주로 db작업)
try (Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, 9999999); //int
pstmt.setDouble(2, 3.14); //DOUBLE
pstmt.setDouble(3, 1234567.1234); //DEC(10,3)
pstmt.setString(4, "ABC"); // CHAR(3)
pstmt.setString(5, "Hello World"); //VARCHAR
java.time.LocalDate date = LocalDate.parse("2021-11-26");
java.time.LocalDateTime dateTime = LocalDateTime.parse("2021-11-26T09:33:12");
pstmt.setDate(6, java.sql.Date.valueOf(date)); //DATE
pstmt.setTimestamp(7, java.sql.Timestamp.valueOf(dateTime)); //DATETIME
pstmt.executeQuery();
} 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);
}
}
○결과값
JDBC34Servlet 실행후
★JDBC35Servlet, MyTable10Dao, Bean10, v35.jsp
JDBC31,32,33,34 는 내부에서 일어나는 일들을 했다면 이걸 합쳐서..
화면부터 데이터베이스까지
jdbc08.bean패키지 안에 Bean10
jdbc08.dao패키지 안에 MyTable10Dao
JDBC35Servlet 에서 dao.insert(con, bean);를 실행하려고 나머지 작성
사용할 테이블
JDBC35Servlet
@WebServlet("/jdbc08/s35")
dao.insert(con, bean); 실행 시키려고
package jdbc08;
import java.io.IOException;
import java.sql.Connection;
import java.time.LocalDate;
import java.time.LocalDateTime;
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 jdbc08.bean.Bean10;
import jdbc08.dao.MyTable10Dao;
/**
* Servlet implementation class JDBC35Servlet
*/
@WebServlet("/jdbc08/s35")
public class JDBC35Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC35Servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 5
String path = "/WEB-INF/view/jdbc08/v35.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");
MyTable10Dao dao = new MyTable10Dao();
boolean ok = false;
// 2request 분석, 가공
String col1 = request.getParameter("col1");
String col2 = request.getParameter("col2");
String col3 = request.getParameter("col3");
String col4 = request.getParameter("col4");
String col5 = request.getParameter("col5");
String col6 = request.getParameter("col6");
String col7 = request.getParameter("col7");
Bean10 bean = new Bean10();
bean.setCol1(Integer.parseInt(col1));
bean.setCol2(Double.parseDouble(col2));
bean.setCol3(Double.parseDouble(col3));
bean.setCol4(col4);
bean.setCol5(col5);
bean.setCol6(LocalDate.parse(col6));
bean.setCol7(LocalDateTime.parse(col7));
// 3. business logic(주로db작업)
try (Connection con = ds.getConnection()) {
dao.insert(con, bean);
} catch (Exception e) {
e.printStackTrace();
}
// 4 add attribute
// 5 forward / redirect
String location = request.getContextPath() + "/jdbc08/s36";
response.sendRedirect(location);
}
}
Bean10
package jdbc08.bean;
import java.time.LocalDate;
import java.time.LocalDateTime;
public class Bean10 {
private Integer col1;
private Double col2;
private Double col3;
private String col4;
private String col5;
private LocalDate col6;
private LocalDateTime col7;
public Integer getCol1() {
return col1;
}
public void setCol1(Integer col1) {
this.col1 = col1;
}
public Double getCol2() {
return col2;
}
public void setCol2(Double col2) {
this.col2 = col2;
}
public Double getCol3() {
return col3;
}
public void setCol3(Double col3) {
this.col3 = col3;
}
public String getCol4() {
return col4;
}
public void setCol4(String col4) {
this.col4 = col4;
}
public String getCol5() {
return col5;
}
public void setCol5(String col5) {
this.col5 = col5;
}
public LocalDate getCol6() {
return col6;
}
public void setCol6(LocalDate col6) {
this.col6 = col6;
}
public LocalDateTime getCol7() {
return col7;
}
public void setCol7(LocalDateTime col7) {
this.col7 = col7;
}
}
MyTable10Dao
package jdbc08.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import jdbc08.bean.Bean10;
public class MyTable10Dao {
public boolean insert(Connection con, Bean10 bean) {
String sql = "INSERT INTO mytable10 (col1, col2, col3, col4, col5, col6, col7) "
+ " VALUEs (?, ?, ?, ?, ?, ?, ?) ";
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setInt(1, bean.getCol1());
pstmt.setDouble(2, bean.getCol2());
pstmt.setDouble(3, bean.getCol3());
pstmt.setString(4, bean.getCol4());
pstmt.setString(5, bean.getCol5());
pstmt.setDate(6, Date.valueOf(bean.getCol6()));
pstmt.setTimestamp(7, Timestamp.valueOf(bean.getCol7()));
return pstmt.executeUpdate() == 1;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
}
v35.jsp (JDBC35Servlet dopost)
<%@ 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>
<!-- form>(input+br)*8 -->
<form action="" method="post">
<input type="number" value = "345" name="col1"> <br>
<input type="number" step="0.1" name="col2"> <br>
<input type="number" step="0.001" name="col3"> <br>
<input type="text" maxlength ="3" name="col4"> <br>
<input type="text" name="col5"> <br>
<input type="date" name="col6"> <br>
<input type="datetime-local" name="col7"> <br>
<input type="submit" value="입력"> <br>
</form>
<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>
○결과값
JDBC35Servlet 실행시 초기화면
화면에서 값들 입력후 입력 버튼 누르면 값이 적용됨 아직 읽는걸 작성하지 않아서 쿼리로확인
★JDBC36Servlet, Mytable10Dao, v36.jsp
결과값 리스트 보이게 하려고
JDBC36Servlet에서 list = dao.getList(con); 실행시키려고 나미저 작성
JDBC36Servlet
@WebServlet("/jdbc08/s36")
list = dao.getList(con); 실행 위주
package jdbc08;
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 jdbc08.bean.Bean10;
import jdbc08.dao.MyTable10Dao;
/**
* Servlet implementation class JDBC36Servlet
*/
@WebServlet("/jdbc08/s36")
public class JDBC36Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC36Servlet() {
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");
MyTable10Dao dao = new MyTable10Dao();
List<Bean10> list = null;
// 3
try (Connection con = ds.getConnection()) {
list = dao.getList(con);
} catch (Exception e) {
e.printStackTrace();
}
// 4
request.setAttribute("list", list);
// 5
String path = "/WEB-INF/view/jdbc08/v36.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @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);
}
}
Mytable10Dao (getList() 부분만 여기에작성)
package jdbc08.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import jdbc08.bean.Bean10;
public class MyTable10Dao {
public List<Bean10> getList(Connection con) {
List<Bean10> list = new ArrayList<>();
String sql = "SELECT col1, col2, col3, col4, col5, col6, col7 FROM mytable10";
try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
Bean10 bean = new Bean10();
bean.setCol1(rs.getInt("col1"));
bean.setCol2(rs.getDouble("col2"));
bean.setCol3(rs.getDouble("col3"));
bean.setCol4(rs.getString("col4"));
bean.setCol5(rs.getString("col5"));
bean.setCol6(rs.getDate("col6").toLocalDate());
bean.setCol7(rs.getTimestamp("col7").toLocalDateTime());
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
v36.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>table.table>thead>tr>th*7^^tbody -->
<div class="container">
<div class="row">
<div class="col">
<table class="table">
<thead>
<tr>
<th>c1</th>
<th>c2</th>
<th>c3</th>
<th>c4</th>
<th>c5</th>
<th>c6</th>
<th>c7</th>
</tr>
</thead>
<tbody>
<c:forEach items ="${list }" var="bean">
<!-- tr>td*7 -->
<tr>
<td>${bean.col1 }</td>
<td>${bean.col2 }</td>
<td>${bean.col3 }</td>
<td>${bean.col4 }</td>
<td>${bean.col5 }</td>
<td>${bean.col6 }</td>
<td>${bean.col7 }</td>
</tr>
</c:forEach>
</tbody>
</table>
</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>
○결과값
JDBC35Servlet 실행후 입력후 mytable10 에 보임
★JDBC37Servlet, JDBC38Servlet
위의 JDBC36,37Servlet 랑 bean, dao, jsp 다 만들어야함
JDBC37Servlet, JDBC38Servlet
-- JDBC37Servlet , JDBC 38Servlet v37.jsp, v38.jsp, Bean11, MyTableDao
CREATE TABLE mytable11 (
name VARCHAR(255),
age INT,
birth DATE
JDBC37Servlet
package jdbc08;
import java.io.IOException;
import java.sql.Connection;
import java.time.LocalDate;
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 jdbc08.bean.Bean11;
import jdbc08.dao.MyTable11Dao;
/**
* Servlet implementation class JDBC37Serlvet
*/
@WebServlet("/jdbc08/s37")
public class JDBC37Serlvet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC37Serlvet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc08/v37.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");
MyTable11Dao dao = new MyTable11Dao();
boolean ok = false;
// 2. request 분석, 가공
String name = request.getParameter("name");
String age = request.getParameter("age");
String birth = request.getParameter("birth");
Bean11 bean = new Bean11();
bean.setName(name);
bean.setAge(Integer.parseInt(age));
bean.setBirth(LocalDate.parse(birth));
// 3. business logic( 주로 db작업)
try ( Connection con = ds.getConnection()) {
dao.insert(con, bean);
} catch (Exception e) {
e.printStackTrace();
}
// 4. setattribute
// 5. forward/ redirect
String location = request.getContextPath() + "/jdbc08/s38";
response.sendRedirect(location);
}
}
Bean11
package jdbc08.bean;
import java.sql.Date;
import java.time.LocalDate;
public class Bean11 {
private String name;
private Integer age;
private LocalDate birth;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public LocalDate getBirth() {
return birth;
}
public void setBirth(LocalDate birth) {
this.birth = birth;
}
}
v37.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>
<!-- form>(.form-group>label[for=input$]+input.form.control#input$[name])*3 -->
<form action="" method="post">
<div class="form-group">
<label for="input1"></label>
<input type="text" class="form control" id="input1" name="name">
</div>
<div class="form-group">
<label for="input2"></label>
<input type="number" class="form control" id="input2" name="age">
</div>
<div class="form-group">
<label for="input3"></label>
<input type="date" class="form control" id="input3" name="birth">
</div>
<input type="submit" value="입력" class="btn btn-outline-primary">
</form>
<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>
MyTable11Dao
package jdbc08.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import jdbc08.bean.Bean11;
public class MyTable11Dao {
public boolean insert(Connection con, Bean11 bean) {
String sql = "INSERT INTO mytable11 (name, age, birth) VALUES (?, ? , ?)";
try (PreparedStatement pstmt = con.prepareStatement(sql)) {
pstmt.setString(1, bean.getName());
pstmt.setInt(2, bean.getAge());
pstmt.setDate(3, Date.valueOf(bean.getBirth()));
return pstmt.executeUpdate() == 1;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
public List<Bean11> getList(Connection con) {
List<Bean11> list = new ArrayList<>();
String sql = "SELECT name, age, birth FROM mytable11";
try (Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
Bean11 bean = new Bean11();
bean.setName(rs.getString("name"));
bean.setAge(rs.getInt("age"));
bean.setBirth(rs.getDate("birth").toLocalDate());
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
JDBC38Servlet
package jdbc08;
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 jdbc08.bean.Bean11;
import jdbc08.dao.MyTable11Dao;
/**
* Servlet implementation class JDBC38Servlet
*/
@WebServlet("/jdbc08/s38")
public class JDBC38Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC38Servlet() {
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");
MyTable11Dao dao = new MyTable11Dao();
List<Bean11> list = null;
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
try ( Connection con = ds.getConnection()) {
list = dao.getList(con);
} catch (Exception e) {
e.printStackTrace();
}
// 4. setattribute
request.setAttribute("list", list);
// 5. forward/ redirect
String path ="/WEB-INF/view/jdbc08/v38.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @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);
}
}
v38.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>table.table>thead>tr>th*3^^tbody -->
<div class="container">
<div class="row">
<div class="col">
<table class="table">
<thead>
<tr>
<th>name</th>
<th>age</th>
<th>birth</th>
</tr>
</thead>
<tbody>
<c:forEach items="${list }" var="list">
<!-- tr>td*3 -->
<tr>
<td>${list.name }</td>
<td>${list.age }</td>
<td>${list.birth }</td>
</tr>
</c:forEach>
</tbody>
</table>
</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>
v38 bootstrap 추가
더보기
<%@ 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>table.table>thead>tr>th*3^^tbody -->
<div class="container">
<div class="row">
<div class="col">
<table class="table-striped table-dark table table-bordered">
<thead>
<tr >
<th class="text-info" scope="col">name</th>
<th class="text-danger" scope="col">age</th>
<th class="text-primary" scope="col">birth</th>
</tr>
</thead>
<tbody>
<c:forEach items="${list }" var="list">
<!-- tr>td*3 -->
<tr scope="col">
<td class="text-white bg-dark table-primary" >${list.name }</td>
<td class="text-warning table-secondary">${list.age }</td>
<td class="text-muted table-success">${list.birth }</td>
</tr>
</c:forEach>
</tbody>
</table>
</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>
v37.jsp bootstrap추가
더보기
<%@ 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>
<style>
input::-webkit-input-placeholder {
border-color: teal;
color: purple;
font-style: italic;
font-weight: bold;
background-color: skyblue;
}
</style>
<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>
<!-- form>(.form-group>label[for=input$]+input.form.control#input$[name])*3 -->
<form action="" method="post">
<div class="form-group">
<label for="input1"></label>
<input type="text" class="form control" id="input1" name="name" placeholder="name">
</div>
<div class="form-group">
<label for="input2"></label>
<input type="number" class="form control" id="input2" name="age" placeholder="age">
</div>
<div class="form-group">
<label for="input3"></label>
<input type="date" class="form control" id="input3" name="birth">
</div>
<input type="submit" value="입력" class="btn btn-outline-primary">
</form>
<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>
○결과값
JDBCServlet37 실행후 input에 넣고 값 쿼리에 저장확인
JDBCServlet38 실행시 읽는거 확인
부트스트랩 추가한것
v37.jsp placeholder 추가후 <style></style>적용
★baek t code
○결과값
---------------------------------참조코드------------
★
JDBC37ServletEx , JDBC38ServletEx, v37Ex, v38Ex , Bean11Ex, MyTableDao11Ex
위에것 참조하여
더 많은 타입해보기
JDBC37ServletEx
package jdbc08;
import java.io.IOException;
import java.sql.Connection;
import java.time.LocalDate;
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 jdbc08.bean.Bean11;
import jdbc08.dao.MyTable11Dao;
/**
* Servlet implementation class JDBC37ServletEx
*/
@WebServlet("/JDBC37ServletEx")
public class JDBC37ServletEx extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC37ServletEx() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String path = "/WEB-INF/view/jdbc08/v37Ex.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");
MyTable11Dao dao = new MyTable11Dao();
// 2. request 분석, 가공
Bean11 bean = new Bean11();
bean.setName(request.getParameter("name"));
bean.setAge(Integer.parseInt(request.getParameter("age")));
bean.setBirth(LocalDate.parse(request.getParameter("birth")));
// 3. business logic( 주로 db작업)
try ( Connection con = ds.getConnection()) {
dao.insert(con, bean);
} catch (Exception e) {
e.printStackTrace();
}
// 4. setattribute
// 5. forward/ redirect
String location = request.getContextPath() + "/JDBC38ServletEx";
response.sendRedirect(location);
}
}JDBC38ServletEx
package jdbc08;
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 jdbc08.bean.Bean11;
import jdbc08.dao.MyTable11Dao;
/**
* Servlet implementation class JDBC38ServletEx
*/
@WebServlet("/JDBC38ServletEx")
public class JDBC38ServletEx extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC38ServletEx() {
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");
MyTable11Dao dao = new MyTable11Dao();
List<Bean11> list = null;
// 3 일
try (Connection con = ds.getConnection()) {
list = dao.getList(con);
} catch (Exception e) {
e.printStackTrace();
}
// 04 add setAttribute
request.setAttribute("list", list);
// 05 forward /redirect
String path = "/WEB-INF/view/jdbc08/v38Ex.jsp";
request.getRequestDispatcher(path).forward(request, response);
}
/**
* @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);
}
}
v37Ex
<%@ 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>
<h1>mytable11 insert</h1>
<form action="" method="post">
<input type="text" name="name">
<br>
<input type="number" name="age" min="0">
<br>
<input type="date" name="birth">
<br>
<input type="submit" value="입력">
<br>
</form>
<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>v38Ex
<%@ 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>
<h1>mytable11 list</h1>
<table class="table">
<thead>
<tr>
<th>NAME</th>
<th>AGE</th>
<th>BIRTH</th>
</tr>
</thead>
<tbody>
<c:forEach items="${list }" var="bean">
<tr>
<td>${bean.name }</td>
<td>${bean.age }</td>
<td>${bean.birth }</td>
</tr>
</c:forEach>
</tbody>
</table>
<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>Bean11Ex(Bean11대체)
MyTableDao11Ex(MyTableDao11Ex 대체)
○결과값
--------------------------------오후------------------------------------
★SQL Constraints
NOT NULL - 열에 NULL 값을 가질 수 없습니다.
UNiQUE - 열의 모든 값이 서로 다른지 확인합니다.
PRIMARY KEY - NULL이 아닌 고유 키입니다. 테이블의 각 행을 고유하게 식별합니다.
FOREIGN KEY - 테이블 간의 링크를 파괴하는 작업을 방지합니다.
CHECK - 열의 값이 특정 조건을 충족하는지 확인합니다.
DEFAULT - 값이 지정되지 않은 경우 열의 기본값을 설정합니다.
CREATE INDEX - 데이터베이스에서 데이터를 매우 빠르게 만들고 검색하는 데 사용됩니다.
ex)
col2 VARCHAR(255) NOT NULL
table name datatype constraint
★13Constraints.sql
SQL Constraints
제약사항의 관련된것
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
NOT NULL
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
UNIQUE
CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Primary Key
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
CHECK
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
mysql은 null은 값이 없다
NOT NULL
UNIQUE
mariadb에서
null은 값이 아니고 값이 없다 라서 여러번 가능
P
CHECK
테이블다시 보고싶을떄
-- DESC
DESC mytable12;
DESC mytable13;
DESC mytable15;
DESC mytable16;
13constraints.sql
USE test;
-- not null
CREATE TABLe mytable12 (
col1 VARCHAR(255),
col2 VARCHAR(255) NOT NULL
);
SELECT * FROM mytable12;
INSERT INTO mytable12 (col1, col2) VALUES ('abc','def');
INSERT INTO mytable12 (col2) VALUES ('ghi');
INSERT INTO mytable12 (col1) VALUES ('jkl'); -- 오류남 default value 값이어야함
-- UNIQUE
CREATE TABLE mytable13 (
col1 VARCHAR(5),
col2 VARCHAR(5) UNIQUE
);
SELECT * FROM mytable13;
INSERT INTO mytable13 (col1 , col2) VALUES ('abc', 'def');
INSERT INTO mytable13 (col1 , col2) VALUES ('abc', 'def');
INSERT INTO mytable13 (col1 , col2) VALUES ('abc', 'de');
INSERT INTO mytable13 (col1) VALUES ('abc');
-- NOT NULL, UNIQUE
CREATE TABLE mytable14 (
col1 VARCHAR(5),
col2 VARCHAR(5) NOT NULL UNIQUE
);
SELECT * FROM mytable14;
INSERT INTO mytable14 (col1 , col2) VALUES ('abc','def');
INSERT INTO mytable14 (col2) VALUES ('def');
-- CHECK
CREATE TABLE mytable15 (
col1 INT,
col2 INT CHECK (col2 >= 100 AND col2 <=200)
);
SELECT * FROM mytable15;
INSERT INTO mytable15 (col1, col2) VALUES (-1000, 100);
INSERT INTO mytable15 (col1, col2) VALUES (-1000, 300); -- col2가 200넘어서 안됨
-- DEFAULT
CREATE TABLE mytable16 (
col1 INT DEFAULT 0,
col2 VARCHAR(5) DEFAULT 'a',
col3 DATE DEFAULT NOW(),
col4 DATETIME DEFAULT NOW()
);
SELECT * FROM mytable16;
INSERT INTO mytable16 () VALUES (); -- 기본값 적용됨
INSERT INTO mytable16 (col1) VALUES (10);
INSERT INTO mytable16 (col2, col4) VALUES ('hello', '1991-11-11 11:22:33');
-- DESC
DESC mytable12;
DESC mytable13;
DESC mytable15;
DESC mytable16;
○결과값1
mytable13 ( NOT NULL)
INSERT INTO mytable12 (col1) VALUES ('jkl'); 실행시 오류 문구
○결과값2
mytable13 ( UNIQUE)
INSERT INTO mytable13 (col1, col2) VALUES ('abc', 'def'); 실행시 오류
○결과값3
mytable14 (NOT NULL + UNIQUE)
INSERT INTO mytable14 (col2) VALUES ('def'); 사용시
○결과값4
mytable15 (CHECK)
○결과값5
mytable16 (DEFAULT)
★14primary-key
mytable17,mytable18 같은 상황
update, delete 하나의 레코드를 지울떄 보통 PRIMARY KEY를 사용한다.
테이블 만들때 특별한 이유가 없으면 만든다.
그래서 어떤것을 PRIMARY KEY로 사용할 지가 중요
아무리생각해도 테이블레코드의 PRIMARY KEY로 명시할 것이 없었다면
임의의 컬럼을 하나 생성한다. 보통 id라고 넣는다(INT)
근데 상관이 없는 임의이값일수도 있어
mysql에서는
AUTO_INCREMENT 준다
ex)
id INT PRIMARY KEY AUTO_INCREMENT
AUTO INCREMENT
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
자동 증분을 사용하면 테이블에 새 레코드를 삽입할 때 고유 번호가 자동으로 생성됩니다.
Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
새 레코드를 삽입할 때마다 자동으로 생성되는 기본 키 필드인 경우가 많습니다.
14primary-key
USE test;
-- PRIMARY KEY (기본키, 주키)
-- NOT NULL, UNIQUE
CREATE TABLE mytable17 (
col1 INT NOT NULL UNIQUE,
col2 INT
);
DESC mytable17;
CREATE TABLE mytable18 (
col1 INT PRIMARY KEY,
col2 INT
);
DESC mytable18;
--
CREATE TABLE mytable19Student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
birth DATE NOT NULL,
address VARCHAR(255) NOT NULL
);
DESC mytable19Student;
SELECT * FROM mytable19Student;
INSERT INTO mytable19Student (name, birth, address) VALUES ('kim', '2000-01-01', 'seoul');
DESC Customers;
DESC Employees;
DESC Suppliers;
○결과값
mytable17
mytable18
mytable19Student(AUTO_INCREMENT 는 자동증가)
★15alter-table
내가만든거
형식
ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ];
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
15alter-table
USE test;
DESC mytable19Student;
-- add column
ALTER TABLE mytable19Student ADD COLUMN (
height DOUBLE
);
ALTER TABLE mytable19Student ADD COLUMN (
weight DOUBLE,
foot DOUBLE
);
ALTER TABLE mytable19Student DROP COLUMN foot;
-- AFTER, FIRST// BEFORE는 찾아봐야함
ALTER TABLE mytable19Student ADD foot DOUBLE AFTER name;
DESC mytable19Student;
○결과값
위에 상태에서
ALTER TABLE mytable19Student DROP COLUMN foot; 한경우 foot 사라짐
위상태에서
ALTER TABLE mytable19Student ADD foot DOUBLE AFTER name; 한경우
(foot DOUBLE 타입이 AFTER name 뒤에)
