대학생 코딩 과제 대행 java, python, oracle 스물 네 번째

https://open.kakao.com/o/s3aMpbA
요즘 학기 말이긴 한가 보다 ㅇㅅㅇ..
의뢰가 없다가 최근에 3개가 들어왔었다.

차례대로 포스팅을 오랜만에 해보려고 한다.
먼저 과제는 2문제이다.
1번은 WITH RECURSIVE문과 같은 기능을 하는 stored procedure를 구현하라는 내용의 문제였다.

WITH RECURSIVE문은 mysql 8버전 이후부터 사용이 가능하다.
하지만 과제에 사용하는 mysql 버전은 5.5로 해당 구문을 쓸 수 없게 되있었다.

따라서 1번은 계층형 쿼리를 사용해야 됬다.
oracle의 경우 계층형 쿼리를 start with, connect by 문을 사용해서 짜본적은 있었지만,
mysql로 계층형 쿼리를 짜본적이 없어서 당황하지 않을 수 없었다.

그래서 찾다보니 계층형 쿼리를 사용하지 않고도 left self join을 통해서 계층형 쿼리를
구현할 수 있었다.
뭐 완벽하진 않지만,,
제출 전날 의뢰를 해주어서 시간이 부족해서 꼼수를 부려서
그냥 돌아가게끔만 구현했다 ㅎㅎ
변명이지만 ㅎㅎ

2번은 프로시져를 사용하지 않고 쿼리를 통해서 문제해결을 요하는 문제였다.

과제에 사용 되는 테이블 내용



먼저 프로시져를 생성..

CREATE DEFINER=`s201403658`@`%` PROCEDURE `findBySSN`(essn INT)
BEGIN
DECLARE cnt INT DEFAULT 0;
SELECT COUNT(*) INTO cnt
FROM EMPLOYEE
WHERE Superssn = essn;
IF cnt > 0
THEN
SELECT lv1.ssn as lv1, lv2.ssn as lv2, lv3.ssn as lv3 , lv4.ssn as lv4, lv5.ssn as lv5, lv6.ssn as lv6, lv7.ssn as lv7, lv8.ssn as lv8, lv9.ssn as lv9
FROM EMPLOYEE lv1 left join EMPLOYEE lv2
on lv2.Superssn = lv1.ssn
left join EMPLOYEE lv3
on lv3.Superssn = lv2.ssn
left join EMPLOYEE lv4
on lv4.Superssn = lv3.ssn
left join EMPLOYEE lv5
on lv5.Superssn = lv4.ssn
left join EMPLOYEE lv6
on lv6.Superssn = lv5.ssn
left join EMPLOYEE lv7
on lv7.Superssn = lv6.ssn
left join EMPLOYEE lv8
on lv8.Superssn = lv7.ssn
left join EMPLOYEE lv9
on lv9.Superssn = lv8.ssn
where lv1.ssn = essn
ORDER BY 1;
ELSE
SELECT '','','','','','','','','';
END IF;
END


아래는 프로시져를 자바에서 호출해주고 값을 Level클래스를 통해서 처리해준다.

package mysql_JDBC;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Scanner;

public class Test {
private static Scanner scn;
private static Connection con;

public static void main(String args[]) {
scn = new Scanner(System.in);
problem_1();
scn.close();
}


private static void problem_1() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://db.hufs.ac.kr:3306/s201403658DB", "s201403658",
"01064895758");
Level levels = new Level();
ArrayList<String> level1 = levels.getLevel1();
ArrayList<String> level2 = levels.getLevel2();
ArrayList<String> level3 = levels.getLevel3();
ArrayList<String> level4 = levels.getLevel4();
ArrayList<String> level5 = levels.getLevel5();
ArrayList<String> level6 = levels.getLevel6();
ArrayList<String> level7 = levels.getLevel7();
ArrayList<String> level8 = levels.getLevel8();
System.out.println("DB Connection");
System.out.println("DB : s201403658DB");
System.out.println("userid : s201403658");
CallableStatement c = con.prepareCall("{call findBySSN(?)}");
System.out.print("Enter a ssn: ");
String ssn = scn.next(); // TEST CASE : 888665555, 453453453 , 999887777, 333445555
c.setString(1, ssn);
ResultSet r = c.executeQuery();
while (r.next()) {
if (r.getString(2) != null && r.getString(1).length() > 0) {
level1.add(r.getString(2));
}
if (r.getString(3) != null && r.getString(1).length() > 0) {
level2.add(r.getString(3));
}
if (r.getString(4) != null && r.getString(1).length() > 0) {
level3.add(r.getString(4));
}
if (r.getString(5) != null && r.getString(1).length() > 0) {
level4.add(r.getString(5));
}
if (r.getString(6) != null && r.getString(1).length() > 0) {
level5.add(r.getString(6));
}
if (r.getString(7) != null && r.getString(1).length() > 0) {
level6.add(r.getString(7));
}
if (r.getString(8) != null && r.getString(1).length() > 0) {
level7.add(r.getString(8));
}
if (r.getString(9) != null && r.getString(1).length() > 0) {
level8.add(r.getString(9));
}
}

level1 = new ArrayList<String>(new HashSet<String>(level1));
level2 = new ArrayList<String>(new HashSet<String>(level2));
level3 = new ArrayList<String>(new HashSet<String>(level3));
level4 = new ArrayList<String>(new HashSet<String>(level4));
level5 = new ArrayList<String>(new HashSet<String>(level5));
level6 = new ArrayList<String>(new HashSet<String>(level6));
level7 = new ArrayList<String>(new HashSet<String>(level7));
level8 = new ArrayList<String>(new HashSet<String>(level8));
Level.printLevel(level1, 1);
Level.printLevel(level2, 2);
Level.printLevel(level3, 3);
Level.printLevel(level4, 4);
Level.printLevel(level5, 5);
Level.printLevel(level6, 6);
Level.printLevel(level7, 7);
Level.printLevel(level8, 8);
System.out.println("END OF LIST");
con.close();
} catch (SQLException ex) {
System.out.println("SQLException" + ex);
} catch (Exception ex) {
System.out.println("Exception:" + ex);
}
}

}


Level 클래스

package mysql_JDBC;

import java.util.ArrayList;

public class Level {
private ArrayList<String> level1;
private ArrayList<String> level2;
private ArrayList<String> level3;
private ArrayList<String> level4;
private ArrayList<String> level5;
private ArrayList<String> level6;
private ArrayList<String> level7;
private ArrayList<String> level8;
private ArrayList<String> level9;
public ArrayList<String> getLevel1() {
if(this.level1 == null) {
this.level1 = new ArrayList<>();
}
return level1;
}
public ArrayList<String> getLevel2() {
if(this.level2 == null) {
this.level2 = new ArrayList<>();
}
return level2;
}
public ArrayList<String> getLevel3() {
if(this.level3 == null) {
this.level3 = new ArrayList<>();
}
return level3;
}
public ArrayList<String> getLevel4() {
if(this.level4 == null) {
this.level4 = new ArrayList<>();
}
return level4;
}
public ArrayList<String> getLevel5() {
if(this.level5 == null) {
this.level5 = new ArrayList<>();
}
return level5;
}
public ArrayList<String> getLevel6() {
if(this.level6 == null) {
this.level6 = new ArrayList<>();
}
return level6;
}
public ArrayList<String> getLevel7() {
if(this.level7 == null) {
this.level7 = new ArrayList<>();
}
return level7;
}
public ArrayList<String> getLevel8() {
if(this.level8 == null) {
this.level8 = new ArrayList<>();
}
return level8;
}
public ArrayList<String> getLevel9() {
if(this.level9 == null) {
this.level9 = new ArrayList<>();
}
return level9;
}
public static void printLevel(ArrayList<String> input, int level) {
int i = 0;
while (i < input.size()) {
System.out.println(input.get(i) + " at level " + String.valueOf(level));
i++;
}
}
}



아래는 문제 2번

package mysql_JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Scanner;

public class Test2 {
private static Scanner scn;
private static Connection con;
private static PreparedStatement psmt;
private static ResultSet rset;
public static void main(String args[]) {
scn = new Scanner(System.in);
problem_2();
scn.close();
}
private static void problem_2() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://db.hufs.ac.kr:3306/s201403658DB", "s201403658",
"01064895758");
Level levels = new Level();
ArrayList<String> level1 = levels.getLevel1();
ArrayList<String> level2 = levels.getLevel2();
ArrayList<String> level3 = levels.getLevel3();
ArrayList<String> level4 = levels.getLevel4();
ArrayList<String> level5 = levels.getLevel5();
ArrayList<String> level6 = levels.getLevel6();
ArrayList<String> level7 = levels.getLevel7();
ArrayList<String> level8 = levels.getLevel8();
System.out.println("DB Connection");
System.out.println("DB : s201403658DB");
System.out.println("userid : s201403658");
String query = "SELECT COUNT(*) FROM EMPLOYEE WHERE Superssn = ?";
psmt = con.prepareStatement(query);
System.out.print("Enter a ssn: ");
String ssn = scn.next(); // TEST CASE : 888665555, 453453453 , 999887777, 333445555
psmt.setString(1, ssn);
rset = psmt.executeQuery();
int cnt = 0;
while (rset.next()) {
cnt = rset.getInt(1);
}
if (cnt > 0) {
query = "SELECT lv1.ssn as lv1, lv2.ssn as lv2, lv3.ssn as lv3 , lv4.ssn as lv4, lv5.ssn as lv5, lv6.ssn as lv6, lv7.ssn as lv7, lv8.ssn as lv8, lv9.ssn as lv9"
+ " FROM EMPLOYEE lv1 left join EMPLOYEE lv2" + " on lv2.Superssn = lv1.ssn"
+ " left join EMPLOYEE lv3 " + " on lv3.Superssn = lv2.ssn" + " left join EMPLOYEE lv4"
+ " on lv4.Superssn = lv3.ssn" + " left join EMPLOYEE lv5" + " on lv5.Superssn = lv4.ssn"
+ " left join EMPLOYEE lv6" + " on lv6.Superssn = lv5.ssn" + " left join EMPLOYEE lv7"
+ " on lv7.Superssn = lv6.ssn" + " left join EMPLOYEE lv8" + " on lv8.Superssn = lv7.ssn"
+ " left join EMPLOYEE lv9" + " on lv9.Superssn = lv8.ssn" + " where lv1.ssn = ?";
psmt = con.prepareStatement(query);
psmt.setString(1, ssn);
rset = psmt.executeQuery();
while (rset.next()) {
if (rset.getString(2) != null && rset.getString(1).length() > 0) {
level1.add(rset.getString(2));
}
if (rset.getString(3) != null && rset.getString(1).length() > 0) {
level2.add(rset.getString(3));
}
if (rset.getString(4) != null && rset.getString(1).length() > 0) {
level3.add(rset.getString(4));
}
if (rset.getString(5) != null && rset.getString(1).length() > 0) {
level4.add(rset.getString(5));
}
if (rset.getString(6) != null && rset.getString(1).length() > 0) {
level5.add(rset.getString(6));
}
if (rset.getString(7) != null && rset.getString(1).length() > 0) {
level6.add(rset.getString(7));
}
if (rset.getString(8) != null && rset.getString(1).length() > 0) {
level7.add(rset.getString(8));
}
if (rset.getString(9) != null && rset.getString(1).length() > 0) {
level8.add(rset.getString(9));
}
}
level1 = new ArrayList<String>(new HashSet<String>(level1));
level2 = new ArrayList<String>(new HashSet<String>(level2));
level3 = new ArrayList<String>(new HashSet<String>(level3));
level4 = new ArrayList<String>(new HashSet<String>(level4));
level5 = new ArrayList<String>(new HashSet<String>(level5));
level6 = new ArrayList<String>(new HashSet<String>(level6));
level7 = new ArrayList<String>(new HashSet<String>(level7));
level8 = new ArrayList<String>(new HashSet<String>(level8));
Level.printLevel(level1, 1);
Level.printLevel(level2, 2);
Level.printLevel(level3, 3);
Level.printLevel(level4, 4);
Level.printLevel(level5, 5);
Level.printLevel(level6, 6);
Level.printLevel(level7, 7);
Level.printLevel(level8, 8);
} else {

}
System.out.println("END OF LIST");
con.close();
} catch (SQLException ex) {
System.out.println("SQLException" + ex);
} catch (Exception ex) {
System.out.println("Exception:" + ex);
}

}
}


문제 1,2번 모두 아래와 같은 테스트 케이스를 통과한다.




추후에 mysql 쿼리도 많이 공부해야 겠다...
좋은 쿼리를 잘 짰으면 자바단에서 데이터를 받아서 가공할 필요가 없기 때문에,,
나의 부족함을 많이 느끼게끔 해주는 케이스였다.

댓글

이 블로그의 인기 게시물

About JVM Warm up

About idempotent

About Kafka Basic

About ZGC

sneak peek jitpack

Spring Boot Actuator readiness, liveness probes on k8s

About Websocket minimize data size and data transfer cost on cloud

About G1 GC

대학생 코딩 과제 대행 java, python, oracle 네 번째