DB01 - 19일(sql01~05, jdbc01~02)

SQL : Structured Query Language

aws 는 클라우드라고 보면 됨. aws 에서 컴퓨터를 하나 빌려서 데이터를 저장해뒀다.

 

위의 + 아이콘 클릭 시 새로운 코드 창이 생성됨

Tables 하위 Categories, Customers, .. 등등은 모두 각각 table 이다

♠ 이 테이블을 사용하려면 코드 처음에 다음과 같이 작성해줘야 한다!

USE 테이블명;

 

USE test;

 

우리는 mariadb, mysql 기준으로 문법을 배울 예정

앞으로 sql 에서 작성되는 코드들은 모두 이클립스 WEB-INF > sql 폴더 안에 저장할것임

---

01intro

♠ sql 에서 한 줄 주석 작성하기: "-- "

-- 한 줄 주석

 

♠ 하나의 명령문은 세미클론으로 끝남

♠ 하나의 명령문을 실행하는 방법: 커서를 명령문 안(혹은 맨 끝) 에 두고 cmd+엔터 

OR 번개에 커서가 있는 모양의 아이콘 클릭

실행된 결과는 하단에 표시됨 (정상적으로 실행되면 초록색 체크표시가 뜸)

♠ sql 은 대소문자 구분을 하지 않음 (단, 벤더나 버전에 따라 다를 수 있음)

하지만 sql 작성관습 ↓

♠ 키워드는 대문자로 작성하기

USE test;
SELECT NOW();

preferences > SQL Editor - Query Editor > Use UPPERCASE keywords on completion 체크

하면 코드작성 시 대문자로 자동완성됨

♠ 작성한 파일은 cmd+s 로 저장가능

---

02select01

테이블의 내용을 읽고 싶을 때,

SELECT 키워드와 FROM 키워드의 조합으로 코드를 작성하여 실행함

♠ 테이블 전체 조회

SELECT * FROM 테이블명;

SELECT * FROM Employees;

결과창

 

♠ 만약, 특정 열만 조회하고 싶다면

SELECT 조회할 열 이름1, 열 이름2 FROM 테이블명;

SELECT LastName, FirstName FROM Employees;

* 위와 같이 여러 개의 컬럼을 나열할 수 있다 (콤마 ',' 사용)

 

♠ 테이블의 column (+ 제약사항) 조회하기 

DESCRIBE 테이블명;
DESC 테이블명;

DESCRIBE Employees;

---

02select02

♠ DISTINCT 키워드: 중복 제거

SELECT DISTINCT 컬럼명 FROM 테이블명;

SELECT DISTINCT City FROM Customers;

만약 컬럼을 여러개 나열한다면, 나열된 컬럼의 조합에서 중복이 없어짐

SELECT DISTINCT City, Country FROM Customers;

↑ Country 에서 Brazil 은 여러 개지만, City 가 모두 달라서 조합으로는 중복되지 않음

---

03select-where

이번에는 열 말고 행!

♠ 특정 행만 조회하기 : WHERE

SELECT * FROM 테이블명 WHERE 컬럼명='내용';

→ 해당 컬럼이 특정 내용인 행들만 조회됨

♠ sql 에서 문자열은 작은따옴표('') 를 사용함

SELECT * FROM Customers WHERE Country='France';

Customers 테이블에서 Country 가 France 인 행들만 조회

 

♠ WHERE 에서 사용할 수 있는 연산자들 : =, >, <, >=, <=, <>, !=, BETWEEN, LIKE, IN

♠ >, <, >=, <=

SELECT * FROM Customers WHERE CustomerID > 50;
SELECT * FROM Customers WHERE CustomerID < 5;
SELECT * FROM Customers WHERE CustomerID >= 89;
SELECT * FROM Customers WHERE CustomerID <= 5;

문자열은 사전순(캐릭터코드)으로 비교됨

SELECT * FROM Customers WHERE Country >= 'Mexico';
SELECT * FROM Customers WHERE Country < 'Mexico';

♠ <>, != 는 둘 다 같지 않음을 의미

SELECT * FROM Customers WHERE CustomerID <> 1;
SELECT * FROM Customers WHERE CustomerID != 1;

 

* number 와 string 연산 시 형변환 일어남 (string → number)

SELECT * FROM Customers WHERE CustomerID = 1;
SELECT * FROM Customers WHERE CustomerID = '1';

 

♠ BETWEEN a and b, LIKE, IN ( , ) 은 나중에 -

---

04and-or-not

♠ AND : 두 조건 모두 만족

-- Customer 테이블에서 Country가 Germany이고 City가 Berlin인 행 조회
SELECT * FROM Customers WHERE Country = 'Germany' AND City = 'Berlin';

-- Customer 테이블에서 Country가 Germany이고 City가 Berlin인 행의 CustomerName 조회
SELECT CustomerName FROM Customers WHERE Country = 'Germany' AND City = 'Berlin';

♠ OR : 두 조건 중 하나라도 만족

SELECT * FROM Customers WHERE City = 'Paris' OR Country = 'UK';

♠ NOT : 조건에 해당하지 않음

SELECT * FROM Customers WHERE NOT Country = 'UK';

 

- 다양하게 조합해보기 연습

-- 나라는 독일이고 도시는 베를린 또는 뮌헨
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

-- 나라는 독일이고 도시는 베를린이나 뮌헨이 아닌 행 조회
SELECT * FROM Customers WHERE Country='Germany' AND NOT (City='Berlin' OR City='München');
SELECT * FROM Customers WHERE Country='Germany' AND (City != 'Berlin' AND City != 'München');

-- 나라가 독일과 미국이 아닌 행 조회
SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';

-- 나라가 독일이거나 미국인 행 조회
SELECT * FROM Customers WHERE Country = 'Germany' OR Country = 'USA';
SELECT * FROM Customers WHERE NOT Country!='Germany' OR NOT Country!='USA';

---

05orderBy

♠ 오름차순 정렬 : ORDER BY

ORDER BY 기준

-- Customers테이블에서 Country가 Germany인 행들을 City컬럼 기준으로 오름차순 정렬
SELECT * FROM Customers WHERE Country = 'Germany' ORDER BY City;

♠ 내림차순 정렬 : ORDER BY - DESC

ORDER BY 기준 DESC

-- Customers테이블에서 Country가 Germany인 행들을 City컬럼 기준으로 내림차순(역순) 정렬
SELECT * FROM Customers WHERE Country = 'Germany' ORDER BY City DESC;

♠ 여러 컬럼 기준으로 정렬하기

ORDER BY 기준1, 기준2

→ 기준1 로 먼저 정렬되며, 그 안에서 기준2로 정렬됨

SELECT * FROM Customers ORDER BY Country, City;

- 연습

-- 고객을 나라 역순, 고객명 오름차순으로 정렬
SELECT * FROM Customers ORDER BY Country DESC, CustomerName;

-- 오름차순임을 명시하고 싶으면 ASC 를 작성 (안써도 오름차순이 기본임)
SELECT * FROM Customers ORDER BY Country DESC, CustomerName ASC;

---

 

< 이클립스와 sql 을 연동하기 위한 작업 >

1) 깃에서 정보가 유출되지 않도록 

Repository - Repository settings - Ignored files 선택하고, 

/build/.classpath.project.settings/ 에 *.properties 추가로 작성

2) 이클립스에서 WEB-INF 안에 새 파일 생성 (파일이름: db.properties) 하고

↓ 아래와 같이 작성 (key=value)

 

jdbc : java 와 database 를 connect 한다는 의미

servlet 을 작성했던 폴더에 jdbc01 패키지 생성, jdbc01 패키지 안에 listener (JDBCListener1) 생성

 

application 의 또다른 이름 ServletContext

key=value 기반 작성할 것을 map 으로 만들어주는 객체

 

JDBCListener1

package jdbc01;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

import org.mariadb.jdbc.MariaDbPoolDataSource;

/**
 * Application Lifecycle Listener implementation class JDBCListener1
 *
 */
@WebListener
public class JDBCListener1 implements ServletContextListener {

	private MariaDbPoolDataSource pool = null;

	/**
	 * Default constructor.
	 */
	public JDBCListener1() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see ServletContextListener#contextDestroyed(ServletContextEvent)
	 */
	public void contextDestroyed(ServletContextEvent sce) {
		if (pool != null) {
			pool.close();
		}
	}

	/**
	 * @see ServletContextListener#contextInitialized(ServletContextEvent)
	 */
	public void contextInitialized(ServletContextEvent sce) {
		ServletContext application = sce.getServletContext();

		String path = "/WEB-INF/db.properties";
		InputStream is = null;

		Connection connection = null;

		try {
			is = application.getResourceAsStream(path);

			Properties props = new Properties();
			props.load(is);

			// jdbc:mariadb://ip:port/schema?user=username&password=password

			String ip = props.getProperty("ip");
			String schema = props.getProperty("schema");
			String user = props.getProperty("user");
			String password = props.getProperty("password");

			String dburl = "jdbc:mariadb://" + ip + "/" + schema + "?user=" + user + "&password=" + password;

			pool = new MariaDbPoolDataSource(dburl);

			pool.setMaxPoolSize(1);

			connection = pool.getConnection();
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT 333");
			if (rs.next()) {
				// 333 출력되면 쿼리 실행 OK
				if (rs.getInt(1) == 333) {
					System.out.println("데이터베이스 연결 완료!");
				}
			}

			application.setAttribute("dbpool", pool);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

	}

}

 

* mariadb jdbc driver download 

https://mariadb.com/kb/en/installing-mariadb-connectorj/ 

다운로드하여 lib 폴더 안에 저장

 

JDBC01Servlet

package jdbc01;

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 JDBC01Servlet
 */
@WebServlet("/jdbc01/s01")
public class JDBC01Servlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JDBC01Servlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//		System.out.println("jdbc01 s01 일함.");
		ServletContext application = request.getServletContext();
		
		String sql = "SELECT CustomerName FROM Customers WHERE CustomerID = 1";
		
		DataSource pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체
		
		try (Connection con = pool.getConnection(); // db와의 연결 정보를 가진 객체
			Statement stmt = con.createStatement(); // 쿼리실행을 위한 객체
			) {
			ResultSet rs = stmt.executeQuery(sql); // 쿼리 실행 결과를 저장한 객체
			
			if (rs.next()) {
				String name = rs.getString(1);
				System.out.println(name);
				
				request.setAttribute("customerName", name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// forward
		String view = "/WEB-INF/view/jdbc01/v01.jsp";
		request.getRequestDispatcher(view).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);
	}

}

기본 서블릿에서 추가된 import ↓

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletContext;
import javax.sql.DataSource;

@WebServlet("/jdbc01/s01")

doGet 메소드 ↓

//System.out.println("jdbc01 s01 일함.");
ServletContext application = request.getServletContext();
		
String sql = "SELECT CustomerName FROM Customers WHERE CustomerID = 1";
		
DataSource pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체
		
try (Connection con = pool.getConnection(); // db와의 연결 정보를 가진 객체
	Statement stmt = con.createStatement(); // 쿼리실행을 위한 객체
	) {
	ResultSet rs = stmt.executeQuery(sql); // 쿼리 실행 결과를 저장한 객체
			
	if (rs.next()) {
		String name = rs.getString(1);
		System.out.println(name);
				
		request.setAttribute("customerName", name);
	}
} catch (Exception e) {
	e.printStackTrace();
}
		
// forward
String view = "/WEB-INF/view/jdbc01/v01.jsp";
request.getRequestDispatcher(view).forward(request, response);

문자열 sql 에 sql 코드를 저장

DataSource 타입 pool 에 dbpool 을 받아와 형변환하여 저장 - 연결들을 가지고 있는 객체

Connection 타입 con 에 pool.getConnection() 저장 - db 와의 연결정보를 가진 객체

Statement 타입 stmt 에 con.createStatement() 저장 - 쿼리실행을 위한 객체 

ResultSet 타입 rs 에 stmt.excuteQuery(sql) 저장 - 쿼리 실행 결과를 저장한 객체

---

if 

rs.next() 가 true 이면 커서가 한 칸 이동 

특정 컬럼의 값을 얻고싶다면 컬럼에 번호를 부여

String name =  rs.getString(1) 에서 숫자 1 은 컬럼 번호이다

name 에 sql 결과 저장, request.setAttribute 로 name 을 customerName 으로 전달

---

v01.jsp 로 포워딩

---

 

WEB-INF > view 안에 jdbc01 폴더 만들고 그 안에 jsp 파일 생성

v01.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>${customerName }</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>

전달받은 customerName 출력

 

---

JDBC02Servlet

요청이 /jdbc01/s02 의 경로로 왔을 때, JDBC02Servlet 이 일하여 

SELECT LastName FROM Employees WHERE EmployID = 1 의 결과 Davolio 가 콘솔에 출력되도록 작성

 

먼저, @WebServlet("/jdbc01/s02") 로 작성하고 

doGet 메소드는 아래와 같이 작성 ↓

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		ServletContext application = request.getServletContext();

		String sql = "SELECT LastName FROM Employees WHERE EmployeeID = 1";

		DataSource pool = (DataSource) application.getAttribute("dbpool"); // 연결들을 가지고 있는 객체

		try (Connection con = pool.getConnection(); // db와의 연결 정보를 가진 객체
				Statement stmt = con.createStatement(); // 쿼리실행을 위한 객체
		) {
			ResultSet rs = stmt.executeQuery(sql); // 쿼리 실행 결과를 저장한 객체

			if (rs.next()) {
				String name = rs.getString(1);
				System.out.println(name);

				request.setAttribute("LastName", name);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		// forward
		String view = "/WEB-INF/view/jdbc01/v02.jsp";
		request.getRequestDispatcher(view).forward(request, response);
	}

코드 01 과 모두 동일한데, sql 코드 다르고, name 을 LastName 으로 전달했으며, v02.jsp 로 포워딩 하였음

 

v02.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>${LastName }</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>