728x90
https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html
Processing SQL Statements with JDBC (The Java™ Tutorials > JDBC Database Access > JDBC Basics)
The Java Tutorials have been written for JDK 8. Examples and practices described in this page don't take advantage of improvements introduced in later releases and might use technology no longer available. See Java Language Changes for a summary of updated
docs.oracle.com
테이블 사용하려면 처음에
USE 테이블명; <<<---
다른 데이터베이스
CREATE DATABASE mytest1;
USE mytest1;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0. 사전 작업
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
// 5. forward/ redirect
}
src -> jdbc01 패키지에
★JDBC03Servlet , v03.jsp
String sql = "SELECT CustomerName FROM Customers";
이걸 날리고 싶었엉
next();
rs는 첫번째행 이전을 가르키고있다
Moves the cursor forward one row from its current position.
A ResultSet cursor is initially positionedbefore the first row;
the first call to the method next makes the first row the current row; thesecond call makes the second row the current row, and so on.
커서를 현재 위치에서 한 행 앞으로 이동합니다.ResultSet 커서는 처음에 첫 번째 행 앞에 위치합니다.
첫 번째 메서드 호출은 첫 번째 행을 현재 행으로 만듭니다. 두 번째 호출은 두 번째 행을 현재 행으로 만드는 식입니다.
첫번째행 이 있으면 true 없으면 false
while (rs.next()) {
System.out.println(rs.getString(1));
}
JDBC03Servlet
package jdbc01;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
/**
* Servlet implementation class JDBC03Servlet
*/
@WebServlet("/jdbc01/s03")
public class JDBC03Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC03Servlet() {
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 pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
List<String> names = new ArrayList<>();
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql = "SELECT CustomerName FROM Customers";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
while (rs.next()) {
// System.out.println(rs.getString(1));
names.add(rs.getNString(1));
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 3.5 자원 닫기 / exception 처리
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. setattribute
request.setAttribute("names", names);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc01/v03.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);
}
}
v03.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>JDBC03Servlet</title>
</head>
<body>
<h1>이름들</h1>
<ul>
<c:forEach items="${names }" var="name" varStatus="status">
<li>${status.index} : ${name }</li>
</c:forEach>
</ul>
<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>
○결과값
★JDBC04Servlet, v04.jsp
JDBC03Servlet을 참고해서
JDBC04Servlet로
SELECT LastName FROM Employees; 나오게
JDBC04Servlet
package jdbc01;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
/**
* Servlet implementation class JDBC04Servlet
*/
@WebServlet("/jdbc01/s04")
public class JDBC04Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC04Servlet() {
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 pool =(DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
List<String> names = new ArrayList<>();
// 2. request 정보 분석/가공
// 3. business login (주로 DB작업)
String sql = "SELECT LastName FROM Employees";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
while( rs.next()) {
// System.out.println(rs.getString(1));
names.add(rs.getString(1));
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 3.5 자원 닫기 / exception 처리
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. setattribute
request.setAttribute("names", names);
// 5. forward/redirect
String path = "/WEB-INF/view/jdbc01/v04.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);
}
}
v04.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>
<h1>이름들</h1>
<ul>
<c:forEach items="${names }" var="name" varStatus="status">
<li>${status.index} : ${name }</li>
</c:forEach>
</ul>
<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>
○결과값
★JDBC05Servlet, v05.jsp
하나의 컬럼만을 했지만
여러개의 컬럼을 얻어오고 싶었다.
@WebServlet("/jdbc01/s05")
SELECT CustomerName, ContactName, Address FROM Customers WHERE CustomerID = 1;
JDBC05Servlet
package jdbc01;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
/**
* Servlet implementation class JDBC05Servlet
*/
@WebServlet("/jdbc01/s05")
public class JDBC05Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC05Servlet() {
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 pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String contactName = "";
String customerName = "";
String address = "";
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql = "SELECT CustomerName, ContactName, Address FROM Customers WHERE CustomerID = 1";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
if (rs.next()) {
customerName = rs.getString(1) ;
contactName = rs.getString(2);
address = rs.getString(3);
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if ( stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if ( con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 4. setattribute
request.setAttribute("contactName", contactName);
request.setAttribute("customerName", customerName);
request.setAttribute("address", address);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc01/v05.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);
}
}
v05.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>JDBC05Servlet</title>
</head>
<body>
<h1>고객명 : ${customerName }</h1>
<h1>계약명 : ${contactName }</h1>
<h1>주소명 : ${address }</h1>
<hr>
<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>
○결과값
★JDBC05_1Servlet, v05_1.jsp
JDBC05Servlet 에서 수정해보기 인데 따로 파일 만듬 _1
@WebServlet("/jdbc01/s0501") 주소명
SELECT CustomerName, ContactName, Address FROM Customers WHERE CustomerID = 1;
추가 변경
JDBC05_1Servlet
package jdbc01;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
/**
* Servlet implementation class JDBC05Servlet
*/
@WebServlet("/jdbc01/s05_1")
public class JDBC05_1Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC05_1Servlet() {
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 pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String contactName = "";
String customerName = "";
String address = "";
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql = "SELECT CustomerName, ContactName, Address FROM Customers WHERE CustomerID = 1";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
if (rs.next()) {
customerName = rs.getString(1) ;
contactName = rs.getString(2);
address = rs.getString(3);
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if ( stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if ( con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 4. setattribute
request.setAttribute("contactName", contactName);
request.setAttribute("customerName", customerName);
request.setAttribute("address", address);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc01/v05_1.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);
}
}
v05_1.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>JDBC05_1Servelt modify</title>
</head>
<body>
<h1>고객명 : ${customerName }</h1>
<h1>계약명 : ${contactName }</h1>
<h1>주소명 : ${address }</h1>
<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>
○결과값
★JDBC06Servlet, v06.jsp
SELECT LastName, FirstName FROM Employees WHERE EmployeeID = 1;
JDBC06Servlet
@WebServlet("/jdbc01/s06")
package jdbc01;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
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 JDBC06Servlet
*/
@WebServlet("/jdbc01/s06")
public class JDBC06Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC06Servlet() {
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 pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String LastName = "";
String FirstName = "";
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql = "SELECT LastName, FirstName FROM Employees WHERE EmployeeID = 1";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
if (rs.next()) {
LastName = rs.getString(1);
FirstName = rs.getString(2);
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 4. setattribute
request.setAttribute("lastName", LastName);
request.setAttribute("firstName", FirstName);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc01/v06.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);
}
}
v06.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>JDBC06Servlet</title>
</head>
<body>
<h1>1번직원 이름</h1>
<h3> \${lastName } : ${lastName } </h3>
<h3> \${firstName} : ${firstName }</h3>
<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>
○결과값
★JDBC07Servlet, Customer, v07.jsp
src-> jdbc02패키지 안에 bean안에 Customer.java, servlet1 패키지 안에 JDBC07Servlet
수정해서
SELECT CustomerName, ContactName, Address, City
FROM Customers WHERE CustomerID = 1;
(City) 추가
JDBC07Servlet
package jdbc02.servlet1;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
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;
import jdbc02.bean.Customer;
/**
* Servlet implementation class JDBC05Servlet
*/
@WebServlet("/jdbc02/s07")
public class JDBC07Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC07Servlet() {
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 pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
Customer bean = new Customer();
// String contactName = "";
// String customerName = "";
// String address = "";
// 2. request 분석/가공
// 3. business logic
String sql = "SELECT CustomerName, ContactName, Address, City FROM Customers WHERE CustomerID = 1";
try {
// 1. connection 얻기
con = pool.getConnection();
// 2. statement 얻기
stmt = con.createStatement();
// 3. resultset 얻기
rs = stmt.executeQuery(sql);
if (rs.next()) {
int i = 1;
bean.setCustomerName(rs.getString(i++));
bean.setContactName(rs.getString(i++));
bean.setAddress(rs.getString(i++));
bean.setCity(rs.getString(i++));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute
request.setAttribute("customer", bean);
// 5. forward / redirect
String path = "/WEB-INF/view/jdbc02/v07.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);
}
}
Customer
package jdbc02.bean;
public class Customer {
private String customerName;
private String contactName;
private String address;
private String city;
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public String getContactName() {
return contactName;
}
public void setContactName(String contactName) {
this.contactName = contactName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
}
v07.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>JDBC07Servlet, bean</title>
</head>
<body>
<h1>1번 고객</h1>
<h3>고객명 : ${customer.customerName }</h3>
<h3>계약명 : ${customer.contactName }</h3>
<h3>주소 : ${customer.address }</h3>
<h3>도시 :${customer.city }</h3>
<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>
○결과값
★JDBC08Servlet, v08.jsp
한개의 행이 하나의 Bean이 되어서 list에 들어간다.
SELECT CustomerName, ContactName, Address, City FROM Customers;
JDBC08Servlet
package jdbc02.servlet1;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Customer;
/**
* Servlet implementation class JDBC08Servlet
*/
@WebServlet("/jdbc02/s08")
public class JDBC08Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC08Servlet() {
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 pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
List<Customer> list = new ArrayList<>();
// 2. request 분석/가공
// 3. business logic
String sql = "SELECT CustomerName, ContactName, Address, City FROM Customers";
try {
// 1. connection 얻기
con = pool.getConnection();
// 2. statement 얻기
stmt = con.createStatement();
// 3. resultset 얻기
rs = stmt.executeQuery(sql);
while (rs.next()) {
Customer bean = new Customer();
int i = 1;
bean.setCustomerName(rs.getString(i++));
bean.setContactName(rs.getString(i++));
bean.setAddress(rs.getString(i++));
bean.setCity(rs.getString(i++));
list.add(bean);
}
System.out.println("rs 결과 종료");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 4. add attribute
request.setAttribute("customers", list);
// 5. forward / redirect
String path = "/WEB-INF/view/jdbc02/v08.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);
}
}
v08.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>JDBC08Servlet</title>
</head>
<body>
<!-- .container>.row>.col>h1{고객들} -->
<div class="container">
<div class="row">
<div class="col">
<h1>고객들</h1>
<!-- table.table>thead>tr>th*4^^tbody -->
<table class="table">
<thead>
<tr>
<th>고객명</th>
<th>계약명</th>
<th>주소</th>
<th>도시</th>
</tr>
</thead>
<tbody>
<c:forEach items="${customers}" var="customer">
<tr>
<td>${customer.customerName }</td>
<td>${customer.contactName}</td>
<td>${customer.address }</td>
<td>${customer.city }</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>
○결과값
★JDBCExampleServlet, vexample.jsp
그냥 해보기
SELECT FirstName, LastName, BirthDate FROM Employees;
JDBCExampleServlet
@WebServlet("/jdbc01/example")
만들기 Bean도 만들고
Employees
EmployessID , LastName, FirstName, BirthDate, Notes
LastName, FirstName, BirthDate
vexample.jsp 도 설장하고
JDBCExampleServlet
package jdbc01;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Employees;
/**
* Servlet implementation class JDBCexampleServlet
*/
@WebServlet("/jdbc01/example")
public class JDBCExampleServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBCExampleServlet() {
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 pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String LastName = "";
String FirstName = "";
String BirthDate = "";
List<Employees> list = new ArrayList<>();
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql = "SELECT LastName, FirstName , BirthDate FROM Employees";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
while (rs.next()) {
Employees bean = new Employees();
int i = 1;
bean.setLastName(rs.getString(i++));
bean.setFirstName(rs.getString(i++));
bean.setBirthDate(rs.getString(i++));
list.add(bean);
}
System.out.println("ResultSet 처리 종료!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 4. setattribute
request.setAttribute("list", list);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc01/vexample.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);
}
}
vexample.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>
<!-- table.table>thead>tr>th*3^^tbody -->
<table class="table">
<thead>
<tr>
<th>Last Name</th>
<th>First Name</th>
<th>BirthDate</th>
</tr>
</thead>
<tbody>
<c:forEach items="${list }" var="emp">
<tr>
<td>${emp.lastName }</td>
<td>${emp.firstName }</td>
<td>${emp.birthDate }</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>
○결과값
-------------------------------------------------오후------------------------------------------
1. Establishing a connection.
2. Create a statement.
3. Execute the query.
4. Process the ResultSet object.
5. Close the connection.
연결을 설정하는 중입니다.
문을 만듭니다.
쿼리를 실행합니다.
ResultSet 개체를 처리합니다.
연결을 닫습니다.
★v09.jsp,JDBC09Servlet, Emplyees
jsp 완성시킨것 보고
servlet 만들기
bean package : Employees
v09.jsp
<!-- .container>.row>.col>h1{직원들} -->
<!-- table.table>thead>tr>th*2^^tbody -->
JDBC09Servlet
package jdbc02.servlet1;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Employees;
/**
* Servlet implementation class JDBC09Servlet
*/
@WebServlet("/jdbc02/s09")
public class JDBC09Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC09Servlet() {
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 pool = (DataSource) application.getAttribute("dbpool");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
List<Employees> list = new ArrayList<>();
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql = "SELECT LastName, FirstName FROM Employees";
try {
// 3.1 커넥션 얻기
con = pool.getConnection();
// 3.2 statement 얻기
stmt = con.createStatement();
// 3.3 쿼리 실행 (resultSet 얻기)
rs = stmt.executeQuery(sql);
// 3.4 resultSet 처리
while(rs.next()) {
Employees bean = new Employees();
int i = 1;
bean.setLastName(rs.getString(i++));
bean.setFirstName(rs.getString(i++));
list.add(bean);
}
} catch (Exception e ) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// 4. setattribute
request.setAttribute("list", list);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc02/v09.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);
}
}
Emplyees
package jdbc02.bean;
public class Employees {
private String lastName;
private String firstName;
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
}
JDBC09Servlet1
(샘 try with catch)
이것이자바다 439쪽
package jdbc02.servlet1;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Employees;
/**
* Servlet implementation class JDBC0901Servlet
*/
@WebServlet("/jdbc02/s091")
public class JDBC09Servlet1 extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC09Servlet1() {
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");
List<Employees> list = new ArrayList<>();
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql = "SELECT LastName, FirstName FROM Employees";
try (
Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
while( rs.next()) {
Employees employee = new Employees();
employee.setLastName(rs.getString(1));
employee.setFirstName(rs.getString(2));
list.add(employee);
}
System.out.println("rs결과 끝");
} catch (Exception e) {
e.printStackTrace();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("list", list);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc02/v09.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);
}
}
https://400sos400.tistory.com/197
Chapter10 예외처리
10.1 예외와 예외 클래스 10.2 실행 예외 10.3 예외 처리 코드 10.4 예외 종류에 따른 처리 코드 10.5 자동 리소스 닫기 10.6 예외 떠넘기기 10.7 사용자 정의 예외와 예외 발생 10.8 예외 정보 얻기 10.1 예
400sos400.tistory.com
(10.5 자동 리소스닫기 or try 검색 후보기)
○결과값
★JDBC10Servlet, v10.jsp, bean Supplier
SELECT
SupplierId, SupplierName, ContactName, Address, City, PostalCode, Country, Phone
FROM
Suppliers;
int는 rs.getInt(); 이다..
JDBC10Servlet
package jdbc02.servlet1;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import jdbc02.bean.Supplier;
/**
* Servlet implementation class JDBC10Servlet
*/
@WebServlet("/jdbc02/s10")
public class JDBC10Servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public JDBC10Servlet() {
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");
List<Supplier> list = new ArrayList<>();
// 2. request 분석, 가공
// 3. business logic( 주로 db작업)
String sql ="SELECT SupplierId, SupplierName, ContactName, Address, City, PostalCode, Country, Phone FROM Suppliers";
try (
Connection con = ds.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
while (rs.next()) {
Supplier supplier = new Supplier();
int i = 1;
supplier.setSupplierId(rs.getInt(i++));
supplier.setSupplierName(rs.getString(i++));
supplier.setContactName(rs.getString(i++));
supplier.setAddress(rs.getString(i++));
supplier.setCity(rs.getString(i++));
supplier.setPostalCode(rs.getString(i++));
supplier.setCountry(rs.getString(i++));
supplier.setPhone(rs.getString(i++));
list.add(supplier);
}
} catch (Exception e) {
e.printStackTrace();
}
// 3.1 커넥션 얻기
// 3.2 statement 얻기
// 3.3 쿼리 실행 (resultSet 얻기)
// 3.4 resultSet 처리
// 4. setattribute
request.setAttribute("list1", list);
// 5. forward/ redirect
String path = "/WEB-INF/view/jdbc02/v10.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);
}
}
v10.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>
<!-- .contatiner>.row>.col>h1{공급자들} -->
<div class="contatiner">
<div class="row">
<div class="col">
<h1>공급자들</h1>
<!-- table.table>thead>tr>th*8^^tbody -->
<table class="table">
<thead>
<tr>
<th>SupplierId</th>
<th>SupplierName</th>
<th>ContactName</th>
<th>Address</th>
<th>City</th>
<th>PostalCode</th>
<th>Country</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
<c:forEach items="${list1 }" var="sup">
<tr>
<td>${sup.supplierId }</td>
<td>${sup.supplierName }</td>
<td>${sup.contactName }</td>
<td>${sup.address }</td>
<td>${sup.city }</td>
<td>${sup.postalCode }</td>
<td>${sup.country }</td>
<td>${sup.phone }</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>
bean Supplier
package jdbc02.bean;
public class Supplier {
private int supplierId;
private String supplierName;
private String contactName;
private String address;
private String city;
private String postalCode;
private String country;
private String phone;
public int getSupplierId() {
return supplierId;
}
public void setSupplierId(int supplierId) {
this.supplierId = supplierId;
}
public String getSupplierName() {
return supplierName;
}
public void setSupplierName(String supplierName) {
this.supplierName = supplierName;
}
public String getContactName() {
return contactName;
}
public void setContactName(String contactName) {
this.contactName = contactName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getPostalCode() {
return postalCode;
}
public void setPostalCode(String postalCode) {
this.postalCode = postalCode;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
○결과값
★
JDBC08Servlet 수정해서
나머지 다 작성 하기
JDBC08Servlet1
@WebServlet("/jdbc02/s081")
SELECT CustomerName, ContactName, Address, City, CustomerID, PostalCode, Country FROM Customers;
Customer
package jdbc02.bean;
public class Customer {
private String customerName;
private String contactName;
private String address;
private String city;
private int customerId;
private String postalCode;
private String country;
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public String getContactName() {
return contactName;
}
public void setContactName(String contactName) {
this.contactName = contactName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public int getCustomerId() {
return customerId;
}
public void setCustomerId(int customerId) {
this.customerId = customerId;
}
public String getPostalCode() {
return postalCode;
}
public void setPostalCode(String postalCode) {
this.postalCode = postalCode;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
}
v0801.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>
<!-- table.table>thead>tr>th*4^^tbody -->
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>CustomerName</th>
<th>contactName</th>
<th>주소</th>
<th>도시</th>
<th>PostalCode</th>
<th>Country</th>
</tr>
</thead>
<tbody>
<c:forEach items="${customers}" var="customer">
<tr>
<td>${customer.customerId }</td>
<td>${customer.customerName }</td>
<td>${customer.contactName}</td>
<td>${customer.address }</td>
<td>${customer.city }</td>
<td>${customer.postalCode }</td>
<td>${customer.country }</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>
○결과값
'DB(baek)' 카테고리의 다른 글
| 2021-11-26-Servlet31~38 and SQL Constraints (0) | 2021.11.26 |
|---|---|
| 2021-11-25 08delete,Createtable, datatype.. Servlet27~30 (0) | 2021.11.25 |
| 2021-11-24 06insert 07 update(JDBC21Servlet~JDBC26Servlet) (0) | 2021.11.24 |
| 2021-11-23 preparedStatement ,DAO (0) | 2021.11.23 |
| 2021-11-19 sql01~05 + JDBCServlet (2) | 2021.11.19 |