0913 sql
2022.09.13
-- https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html
-- mysql 8.0.21
https://downloads.mysql.com/archives/installer/
-- workbench에서 scott 계정 생성
-- DML
CREATE TABLE IF NOT EXISTS BONUS (
ENAME varchar(10) DEFAULT NULL,
JOB varchar(9) DEFAULT NULL,
SAL double DEFAULT NULL,
COMM double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS DEPT (
DEPTNO int(11) NOT NULL,
DNAME varchar(14) DEFAULT NULL,
LOC varchar(13) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
CREATE TABLE IF NOT EXISTS EMP (
EMPNO int(11) NOT NULL,
ENAME varchar(10) DEFAULT NULL,
JOB varchar(9) DEFAULT NULL,
MGR int(11) DEFAULT NULL,
HIREDATE datetime DEFAULT NULL,
SAL double DEFAULT NULL,
COMM double DEFAULT NULL,
DEPTNO int(11) DEFAULT NULL,
PRIMARY KEY (EMPNO),
KEY PK_EMP (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300, NULL, 10);
CREATE TABLE IF NOT EXISTS SALGRADE (
GRADE double DEFAULT NULL,
LOSAL double DEFAULT NULL,
HISAL double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
ALTER TABLE EMP
ADD CONSTRAINT PK_EMP FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE SET NULL ON UPDATE CASCADE;
// Dept.java
package model.domain;
public class Dept {
private int deptno;
private String dname;
private String loc;
public Dept() {
super();
}
public Dept(int deptno, String dname, String loc) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public String toString() {
return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
}}
// 새로운 부서 생성
// Query : INSERT INTO DEPT VALUES(deptno, dname, loc)
public static boolean insertDept(Dept dept) throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/scott?serverTimezone=Asia/Seoul", "scott", "tiger");
pstmt = con.prepareStatement("SELECT * FROM DEPT WHERE DNAME = ?");
} finally {
pstmt.close();
con.close();
}
return false;
}
// 부서번호로 검색하여 해당 부서 삭제
// Query : delete from dept where deptno = ?
public static boolean deleteDept(int deptno) throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/scott?serverTimezone=Asia/Seoul", "scott", "tiger");
pstmt = con.prepareStatement("delete from dept where deptno = ?");
pstmt.setInt(1, deptno);
int result = pstmt.executeUpdate();
if(result != 0) {
return true;
}
} finally {
pstmt.close();
con.close();
}
return false;
}
// 부서번호로 검색하여 해당 부서의 위치를 수정
private static boolean updateDeptLoc(int deptno, String loc) throws SQLException{
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/scott?serverTimezone=Asia/Seoul", "scott", "tiger");
pstmt = con.prepareStatement("update dept set loc = ? where deptno = ?");
pstmt.setString(1, loc);
pstmt.setInt(2, deptno);
int result = pstmt.executeUpdate();
if(result != 0) {
return true;
}
}finally {
pstmt.close();
con.close();
}
return false;
}
// delete
// DELETE FROM dept WHERE deptno = 50;
public static boolean deleteDept(int i) throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/scott?serverTimezone=Asia/Seoul", "scott", "tiger");
pstmt = con.prepareStatement("DELETE FROM dept WHERE deptno = ?;");
pstmt.setInt(1, i);
int result = pstmt.executeUpdate();
System.out.println(result);
if(result != 0) {
return true;
}
} finally {
pstmt.close();
con.close();
}
return false;
}// DBUtil.java
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
// 1단계 : Driver 로딩
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 2단계 : DB 연결 가능 메소드
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/scott?serverTimezone=Asia/Seoul", "scott", "tiger");
}
// 6단계 : 자원반환 메소드
public static void close(ResultSet rset, Statement stmt, Connection con) throws SQLException {
if(rset != null) {
rset.close();
}
if(stmt != null) {
stmt.close();
}
if(con != null) {
con.close();
}
}
public static void close(Statement stmt, Connection con) throws SQLException {
if(stmt != null) {
stmt.close();
}
if(con != null) {
con.close();
}
}}
