Learn & Record
SQL MariaDB (참조관계, 외래키 제약, 연계 참조 무결성 제약, JDBC 프로그램, ResultSet, DB코드, JDBC 실행문) 본문
SQL MariaDB (참조관계, 외래키 제약, 연계 참조 무결성 제약, JDBC 프로그램, ResultSet, DB코드, JDBC 실행문)
Walker_ 2024. 2. 15. 13:301. 참조관계
- 직원 테이블을 생성하고, 직원의 이름을 기본키로 지정하고 직원테이블과 관계 맺을 프로젝트 테이블 생성
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();
}
}