Dev/SQL
SQL MariaDB (참조관계, 외래키 제약, 연계 참조 무결성 제약, JDBC 프로그램, ResultSet, DB코드, JDBC 실행문)
Walker_
2024. 2. 15. 13:30
1. 참조관계
- 직원 테이블을 생성하고, 직원의 이름을 기본키로 지정하고 직원테이블과 관계 맺을 프로젝트 테이블 생성
2. 외래키 제약
- 직원과 프로젝트의 샘플 데이터는 규칙에 맞게 입력하여 참조 관계가 정확
INSERT INTO `tProject` VALUES (7, '홍길동', ' 원자재 매입', 900);CREATE TABLE `tProject`
(
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,
CONSTRAINT FK_emp FOREIGN KEY(`employee`) REFERENCES `temployee` (`name`)
);- 프로젝트 테이블 삭제 후 다시 생성
INSERT INTO `tProject` VALUES (1, '김상형', '홍콩 수출건', 800);
INSERT INTO `tProject` VALUES (2, '김상형', 'TV 광고건', 3400);
INSERT INTO `tProject` VALUES (3, '김상형', '매출분석건', 200);
INSERT INTO `tProject` VALUES (4, '문종민', '경영 혁신안 작성', 120);
INSERT INTO `tProject` VALUES (5, '문종민', '대리점 계획', 85);
INSERT INTO `tProject` VALUES (6, '권성직', '노조 협상건', 24);- 데이터 추가
INSERT INTO `tproject` VALUES (7, '홍길동', '원자재 매입', 900);- 에러 발생
- 직원을 먼저 등록해야 이 직원에게 프로젝트를 맡길 수 있음. 순서가 바뀌면 안됨 ( INSERT employee > INSERT project)
- 마찬가지로 직원을 삭제하려면 다음 두 명령을 순서대로 실행 ( DELETE project > DELETE employee)
- 외래키 제약은 참조 무결성을 지키는 강력한 수단
- 그러나 개발 중에는 외래키 제약이 오히려 방해될 수 있다
3. 연계 참조 무결성 제약
- 직원을 삭제하려면 이 직원이 담당한 프로젝트를 먼저 삭제하는 단계를 거쳐야 함
- 그래서 관련 작업을 자동화하여 한 번에 처리하는 연계 참조 무결성 제약이 추가됨
- 테이블 추가
CREATE TABLE `tProject`
(
`projectID` INT PRIMARY KEY,
`employee` CHAR(10) NOT NULL,
`project` VARCHAR(30) NOT NULL,
`cost` INT,
CONSTRAINT FK_emp FOREIGN KEY (`employee`) REFERENCES `temployee` (`name`)
ON DELETE CASCADE
);- 다음 명령어로 직원삭제
- 이번에는 명령어 정상 작동
DELETE FROM `temployee` WHERE `name` = '김상형';
- 마찬가지로 UPDATE 외래키 설정
- 직원을 수정하면, 프로젝트에 변경
4. JDBC 프로그램
- UserID의 중복을 확인하는 메서드
private boolean isDupUserID(String userID) throws SQLException {
int totalRow; // 레코드 수를 저장
String sql = "SELECT * FROM tUser WHERE userID = ?";
preparedStatement = conn.prepareStatement(sql); // 쿼리 실행 준비
// ?에 들어갈 값을 지정해주는데, ?는 순서에 따라 1번부터 번호가 부여
preparedStatement.setString(1, userID);
resultSet = preparedStatement.executeQuery();
// ResultSet의 개수를 구하는 메서드는 없어서 다른 방법으로 개수를 구함
resultSet.last(); // 마지막 row로 이동
totalRow = resultSet.getRow(); // row count를 추출
resultSet.beforeFirst(); // 처음 row로 커서를 이동. (초기 상태로 돌아감)
return totalRow == 1;
}
5. ResultSet
// ResultSet의 개수를 구하는 메서드는 없어서 다른 방법으로 개수를 구함
resultSet.last(); // 마지막 row로 이동
totalRow = resultSet.getRow(); // row count를 추출
resultSet.beforeFirst(); // 처음 row로 커서를 이동. (초기 상태로 돌아감) private boolean isDupUserID2(String userID) throws SQLException {
int totalRow;
String sql = "SELECT COUNT(*) FROM tUser WHERE userID = ?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, userID);
resultSet = preparedStatement.executeQuery();
resultSet.next();
return resultSet.getInt(1)>0;
}
public void insertUser() throws SQLException {
User user = setUser();
if (isDupUserID(user.userID)) {
System.out.println(user.userID + "는 중복되는 아이디입니다.");
return;
}
// 매개변수화된 SQL 문 작성
String insertSql = " INSERT INTO tUser (userID, name, age, job ) VALUES (?, ?, ?, ?) ";
preparedStatement = conn.prepareStatement(insertSql);
preparedStatement.setString(1, user.userID);
preparedStatement.setString(2, user.name);
preparedStatement.setInt(3, user.age);
preparedStatement.setString(4, user.job);
// SQL 문 실행
int rows = preparedStatement.executeUpdate(); // executeUpdate()을 호출하면 sql문이 실행.
// 영향을 받은 데이터 갯수(정수) 반환.
// pstmt.excuteQuery() : select
// pstmt.excuteUpdate() : insert, update, delete ...
// insert의 경우 정상적으로 실행이 되었으면 1을 리턴.
if (rows == 1) {
System.out.println("데이터 입력에 성공했습니다.");
} else {
System.out.println("데이터 입력에 실패했습니다.");
}
}
- boolean next(), boolean previous(), Statement
[ JDBC 구성 코드 + 실행 ]
6. DB 코드
public class DB {
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
public void connectDB() {
// 접속 정보
final String driver = "org.mariadb.jdbc.Driver"; // JDBC 드라이버
final String DB_HOST = "127.0.0.1"; // DB IP
final String DB_PORT = "3306"; // DB PORT 번호
final String DB_NAME = "sample"; // 접속 데이터베이스 이름
final String DB_URL = "jdbc:mariadb://" + DB_HOST + ":" + DB_PORT + "/" + DB_NAME; // 경로 URL 생성
final String DB_USER = "root"; // 유저 root 명
final String DB_PASS = "5046"; // DB 패스워드
try {
Class.forName(driver); // JDBC 드라이버 등록
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS); // 디비 연결
if (conn != null) {
System.out.println("DB 접속 성공");
}
} catch (ClassNotFoundException e) { // JDBC 드라이버 등록실패시
System.out.println("드라이버 로드 실패");
e.printStackTrace();
} catch (SQLException e) { // DriverManager.getConnection() 실패시
System.out.println("DB 접속 실패");
e.printStackTrace();
}
}
public void closeDB() {
/* 데이터베이스 연결 해제 */
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (conn != null && !conn.isClosed()) {
conn.close();
System.out.println("DB 접속 해제");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
7. DBCreat 코드
package ch_01.day24021415;
public class DBcreat extends DB {
public void createTableUser() {
String tableName = "tuser";
String sql = "CREATE TABLE " + tableName + " (userID VARCHAR(100) PRIMARY KEY, "
+ "name VARCHAR(100), age INT, job VARCHAR(100)) ";
createTable(tableName, sql);
}
public void createTable(String tableName, String sql) {
// 테이블을 생성하는 메서드
// 생성할 테이블 이름과 생성하는 sql 문을 매개 변수로 받음
// 1) 테이블이 존재하는 지 확인 후 2) 없으면 생성
System.out.println(sql);
try {
// 테이블이 존재하는 지 확인
String tableSql = "show tables";
boolean isTable = false;
// 아래 두 항목은 부모 클래스에서 정의
// SQL문을 전송할 수 있는 PreparedStatement 객체를 생성
preparedStatement = conn.prepareStatement(tableSql); // 쿼리 실행 준비
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
if (tableName.equals(resultSet.getString(1))) {
isTable = true;
break;
}
}
if (isTable) {
System.out.println(tableName + "이란 이름의 테이블이 이미 존재합니다.");
} else {
preparedStatement = conn.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
if (resultSet != null) {
System.out.println(tableName + " 테이블 생성에 성공했습니다.");
}
else {
System.out.println(tableName + " 테이블 생성에 실패했습니다.");
}
}
} catch (Exception e) {
System.out.println("db connect err : " + e);
}
}
}
8.DBInsert
package ch_01.day24021415;
import java.sql.SQLException;
import java.util.Scanner;
public class DBInsert extends DB {
private User setUser() throws SQLException {
User user = new User();
Scanner scanner = new Scanner(System.in);
boolean validate = false;
String validateTemp;
do {
System.out.println("신규 회원의 아이디를 입력하세요 (영어나 숫자, 이전 데이터에서 사용한 값은 안됨) : ");
user.userID = scanner.nextLine();
if (isDupUserID(user.userID)) {
System.out.println(user.userID + "는 중복되는 아이디입니다. ");
validate = true;
continue;
}
System.out.println("신규 회원의 이름을 입력하세요 : ");
user.name = scanner.nextLine();
System.out.println("신규 회원의 나이를 입력하세요 : ");
user.age = Integer.parseInt(scanner.nextLine());
System.out.println("신규 회원의 직업을 입력하세요 : ");
user.job = scanner.nextLine();
System.out.println("신규 회원 : " + user.userID + " / " + user.name
+ " / " + user.age + " / " + user.job + "이 맞습니까? (y/n)");
validateTemp = scanner.nextLine();
validate = !validateTemp.equals("y");
} while (validate);
scanner.close();
return user;
}
public void insertUser() throws SQLException {
User user = setUser();
if (isDupUserID(user.userID)) {
System.out.println(user.userID + "는 중복되는 아이디입니다.");
return;
}
// 매개변수화된 SQL 문 작성
String insertSql = " INSERT INTO tUser (userID, name, age, job ) VALUES (?, ?, ?, ?) ";
preparedStatement = conn.prepareStatement(insertSql);
preparedStatement.setString(1, user.userID);
preparedStatement.setString(2, user.name);
preparedStatement.setInt(3, user.age);
preparedStatement.setString(4, user.job);
// SQL 문 실행
int rows = preparedStatement.executeUpdate(); // executeUpdate()을 호출하면 sql문이 실행.
// 영향을 받은 데이터 갯수(정수) 반환.
// pstmt.excuteQuery() : select
// pstmt.excuteUpdate() : insert, update, delete ...
// insert의 경우 정상적으로 실행이 되었으면 1을 리턴.
if (rows == 1) {
System.out.println("데이터 입력에 성공했습니다.");
} else {
System.out.println("데이터 입력에 실패했습니다.");
}
}
private boolean isDupUserID(String userID) throws SQLException {
int totalRow; // 레코드 수를 저장
String sql = "SELECT * FROM tUser WHERE userID = ?";
preparedStatement = conn.prepareStatement(sql); // 쿼리 실행 준비
// ?에 들어갈 값을 지정해주는데, ?는 순서에 따라 1번부터 번호가 부여
preparedStatement.setString(1, userID);
resultSet = preparedStatement.executeQuery();
// ResultSet의 개수를 구하는 메서드는 없어서 다른 방법으로 개수를 구함
resultSet.last(); // 마지막 row로 이동
totalRow = resultSet.getRow(); // row count를 추출
resultSet.beforeFirst(); // 처음 row로 커서를 이동. (초기 상태로 돌아감)
return totalRow == 1;
}
private boolean isDupUserID2(String userID) throws SQLException {
int totalRow;
String sql = "SELECT COUNT(*) FROM tUser WHERE userID = ?";
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, userID);
resultSet = preparedStatement.executeQuery();
resultSet.next();
return resultSet.getInt(1)>0;
}
}
9. DBSelect
package ch_01.day24021415;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class DBSelect extends DB{
Scanner scanner = new Scanner(System.in);
public void getAllUser() throws SQLException {
/* 전체출력 */
System.out.println("전체 회원 목록입니다.");
String sql = "SELECT * FROM tUser";
printUser(sql);
System.out.println("=================");
}
private void printUser(String sql) throws SQLException {
/* select 실행 결과를 받아서 콘솔에 출력.
전체 목록 출력과 한 열만 출력하는 기능
*/
int totalRow;
int num = 1;
ResultSet resultSet = querySelect(sql);
// ResultSet의 개수를 구해서 1일 경우와 1이 아닐 경우를 분리
// ResultSet의 개수를 구하는 메서드는 없어서 다른 방법으로 개수를 구함
resultSet.last(); // 마지막 row로 이동
totalRow = resultSet.getRow(); // row count를 추출
resultSet.beforeFirst(); // 처음 row로 커서를 이동
if (totalRow == 1) {
if(resultSet.next()) {
System.out.println("아이디 : " + resultSet.getString(1));
System.out.println("이름 : " + resultSet.getString(2));
System.out.println("나이 : " + resultSet.getString(3));
System.out.println("직업 : " + resultSet.getString(4));
System.out.println();
}
} else if (totalRow > 1) {
// 전체 목록 출력
// 데이터의 처음부터 읽어오므로 next를 만나면 한 줄을 읽어오고, 다음 줄로 커서가 이동
// 한줄 한줄 내려가면서 데이터를 가져오고, 데이터가 끝까지 가면 종료
while (resultSet.next()) {
System.out.println(num + "번째 회원");
System.out.println("아이디 : " + resultSet.getString("userID"));
System.out.println("이름 : " + resultSet.getString("name"));
System.out.println("나이 : " + resultSet.getString("age"));
System.out.println("직업 : " + resultSet.getString("job"));
System.out.println();
num++;
}
} else {
System.out.println("입력하신 아이디의 데이터는 존재하지 않습니다.");
}
}
protected ResultSet querySelect(String sql) {
/* select 실행 후 결과 반환/
이후 update 와 delete 관련 클래스에서 사용하기 위해 protected 사용 */
try {
preparedStatement = conn.prepareStatement(sql); // 쿼리 실행 준비
// INSETE, UPDATE, DELETE일 경우에는 executeUpdate() 메소드를 호출하지만,
// 데이터를 가져오는 SELECT문일 경우에는 executeQuery() 메소드를 호출
resultSet = preparedStatement.executeQuery();
} catch (SQLException e) {
System.out.println("error: " + e);
}
// ResultSet은 SELECT 문에 기수로딘 컬럼으로 구성된 행 row의 집합.
return resultSet;
}
}
10. JDBC 실행문
- [LoadDriver]
package ch_01.day24021415;
public class My_01_LoadDriver {
public static void loadDriver() {
try {
Class.forName("org.mariadb.jdbc.Driver");
System.out.println("Driver Load Success!");
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
loadDriver();
}
}- [ConnectDB]
package ch_01.day24021415;
public class My_02_ConnectDB {
// Connection 객체를 생성해 DataBase 연결하기
public static void main(String[] args) {
DB myDB = new DB();
myDB.connectDB();
myDB.closeDB();
}
}- [CreateTable]
package ch_01.day24021415;
import java.sql.SQLException;
public class My_03_CreateTable {
public static void main(String[] args) throws SQLException {
DBcreat myDB = new DBcreat();
myDB.connectDB();
myDB.createTableUser();
myDB.closeDB();
}
}- [InsertUser]
package ch_01.day24021415;
import java.sql.SQLException;
public class My_04_InsertUser {
public static void main(String[] args) throws SQLException {
DBInsert myDB = new DBInsert();
myDB.connectDB();
myDB.insertUser();
myDB.closeDB();
}
}- [SelectUser]
package ch_01.day24021415;
import java.sql.SQLException;
public class My_05_SelectUser {
public static void main(String[] args) throws SQLException {
DBSelect myDB = new DBSelect();
myDB.connectDB();
myDB.getAllUser();
myDB.getOneUser();
myDB.closeDB();
}
}