1. JAVA/1.3 문제
220421_JDBC_정보 입력프로그램(2)
마느링
2022. 4. 21. 23:01
전에 했던 정보 입력 프로그램이 각 클래스별로 중복되는 코드가 너무 많았어서
추상클래스를 이용해서 한번 작성해보았다.
(NullPointException이 자꾸 떠서 애먹었다.ㅠㅠㅠ)
일단 틀은 똑같고 알맹이만 다르기 때문에
커넥션 연결, Statement ,PreparedStatement 는 공통적으로 중복되어 추상 클래스에 셋팅해두었고
학생 정보 프로그램, 선생님 정보 표현 프로그램이 상속받게 하였다..ㅎ
1. 추상클래스 : 학생, 선생님 정보 프로그램이 공통으로 구현되는 클래스를 추상클래스로 셋팅하였다.
package jdbc.tmt;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import jdbc.SQL.conSQL;
public abstract class Stmt {
protected PreparedStatement pstmt;
protected Statement stmt;
String name;
Connection con;
public Stmt(String name){
this.name = name;
}
public void setConnect() {
conSQL csql = new conSQL();
csql.setDriver();
con = csql.setCon();
System.out.println("==========연결 완료========");
}
public String[] setStr() {
String[] str = null;
str = new String[] {"테이블 생성","데이터 추가","테이블 조회","테이블 삭제","종료"};
return str;
}
public void arrPrint(String[] str) {
for(int i = 0; i<str.length;i++) {
System.out.println(" "+(i+1)+" : "+str[i]);
}
System.out.println();
}
public void setting() {
System.out.println("<<< "+this+" 정보 프로그램 >>> 다음 중 고르시오");
String[] str = setStr();
arrPrint(str);
System.out.print("선택 : ");
}
public int setInt(Scanner sc) {
return sc.nextInt();
}
public abstract String choose(int no);
public abstract String idCheck(String str);
public void idCheck(PreparedStatement pstmt,String id, String ch) {
if(! id.startsWith(ch)) {
System.out.println("다시 입력");
return;
}
else {
try {
pstmt.setString(1, id);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public Statement setStmt() {
Statement stmt = null;
try {
stmt = con.createStatement();
}
catch(Exception e) {
System.out.println("%%% stmt 실패%%%");
}
return stmt;
}
public PreparedStatement setPstmt(String sql) {
PreparedStatement pstmt = null;
try {
pstmt = con.prepareStatement(sql);
}
catch(Exception e) {
System.out.println("%%% pstmt 실패%%%");
}
return pstmt;
}
public String toString() {
return name;
}
}
2. 학생 정보 클래스 : 추상클래스를 상속받음.. 학생정보에 대하여 테이블 생성부터 추가입력, 조회, 삭제하는 기능
package jdbc.tmt.stmt;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Scanner;
import VO.StudVO;
import jdbc.SQL.studSQL;
import jdbc.tmt.Stmt;
public class Student extends Stmt{
ResultSet rs;
ArrayList<StudVO> list;
Scanner sc;
public Student(){
super("학생");
setConnect();
setData();
}
public void setData() {
while(true) {
setting();
sc = new Scanner(System.in);
int no = setInt(sc);
System.out.println();
String str = choose(no);
String[] ar = setStr();
System.out.println("**** ["+ar[no-1]+"] 선택 ****");
if(no==2) {
pstmt = setPstmt(str);
list = setArr(str);
toPrint(list);
}
else if(no==5) {
System.out.println("프로그램 종료");
break;
}
else {
stmt = setStmt();
rs = setStmtRs(no,str);
}
System.out.println(" ["+ar[no-1]+"] 성공");
}
}
@Override
public String choose(int no) {
String str = null;
studSQL sql = new studSQL();
str = sql.setData(no);
return str;
}
public String idCheck(String str) {
String id = null;
while(true) {
id = sc.next();
if(!id.startsWith(str)) {
System.out.print("\tID 다시 입력 : ");
continue;
}
else {
try {
pstmt.setString(1, id);
System.out.println("\t입력 완료");
return id;
}
catch(Exception e) {
e.printStackTrace();
}
}
}
}
public ArrayList<StudVO> setArr(String str) {
list = null;
try {
list = new ArrayList<StudVO>();
while(true) {
System.out.print("\tID : ");
String id = idCheck("s");
System.out.print("\t이름 :");
String name = sc.next();
pstmt.setString(2, name);
System.out.print("\t성별 :");
String gen= sc.next();
pstmt.setString(3, gen);
System.out.print("\t반 : ");
int ban = sc.nextInt();
pstmt.setInt(4, ban);
System.out.print("\t학년 : ");
int grade = sc.nextInt();
pstmt.setInt(5, grade);
rs = pstmt.executeQuery();
StudVO svo = new StudVO();
svo.setId(id);
svo.setName(name);
svo.setGen(gen);
svo.setGrade(grade);
svo.setBan(ban);
list.add(svo);
System.out.print("\t1.추가입력 / 2. 그만입력 : ");
int stop = sc.nextInt();
if(stop==2) {
System.out.println();
System.out.println("***** 입력 종료 *****");
break;
}
}
}
catch(Exception e) {
e.printStackTrace();
}
return list;
}
public void toPrint(ArrayList<StudVO> list) {
for(StudVO s : list) {
System.out.println(s);
}
}
public ResultSet setStmtRs(int no,String str) {
ResultSet rs = null;
try {
rs = stmt.executeQuery(str);
if(no==3) {
System.out.println(" < 조회 결과 > ");
while(rs.next()) {
String id = rs.getString("id");
String name =rs.getString("name");
String gen = rs.getString("gen");
int grade = rs.getInt("grade");
int ban = rs.getInt("ban");
System.out.println("id : "+id+" , name : "+name+" , 성별 : "+gen+"("+grade+"학년"+ban+"반)");
}
}
}
catch(Exception e) {
e.printStackTrace();
}
return rs;
}
}
3. 선생님 정보 클래스 : 추상클래스를 상속받음.. 선생님 정보에 대하여 테이블 생성부터 추가입력, 조회, 삭제하는 기능
package jdbc.tmt.stmt;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Scanner;
import VO.TeacVO;
import jdbc.SQL.teacSQL;
import jdbc.tmt.Stmt;
public class Teacher extends Stmt{
ResultSet rs;
ArrayList<TeacVO> list;
Scanner sc;
public Teacher(){
super("선생님");
setConnect();
setData();
}
public void setData() {
while(true) {
setting();
sc = new Scanner(System.in);
int no = setInt(sc);
System.out.println();
String str = choose(no);
String[] ar = setStr();
System.out.println("**** ["+ar[no-1]+"] 선택 ****");
if(no==2) {
pstmt = setPstmt(str);
list = setArr(str);
toPrint(list);
}
else if(no==5) {
System.out.println("프로그램 종료");
break;
}
else {
stmt = setStmt();
rs = setTtmtRs(no,str);
}
System.out.println(" ["+ar[no-1]+"] 성공");
}
}
@Override
public String choose(int no) {
String str = null;
teacSQL sql = new teacSQL();
str = sql.setData(no);
return str;
}
public String idCheck(String str) {
String id = null;
while(true) {
id = sc.next();
if(!id.startsWith(str)) {
System.out.print("\tID 다시 입력 : ");
continue;
}
else {
try {
pstmt.setString(1, id);
System.out.println("\t입력 완료");
return id;
}
catch(Exception e) {
e.printStackTrace();
}
}
}
}
public ArrayList<TeacVO> setArr(String str) {
list = null;
try { ////id,name,gen,type
list = new ArrayList<TeacVO>();
while(true) {
TeacVO tvo = new TeacVO();
System.out.print("\tID : ");
String id = idCheck("t");
System.out.print("\t이름 :");
String name = sc.next();
pstmt.setString(2, name);
System.out.print("\t성별 :");
String gen= sc.next();
pstmt.setString(3, gen);
System.out.print("담임선생님입니까? 1. 예, 2. 아니오");
int type = sc.nextInt();
String teac = null;
if(type==1) {
System.out.print("담당하는 학년과 반 : ");
int hak = sc.nextInt();
int ban = sc.nextInt();
teac = "담임선생님 ("+hak+"학년"+ban+"반)";
tvo.setGrade(hak);
tvo.setBan(ban);
}
if(type==2) {
System.out.print("담당 과목 : ");
String subj = sc.next();
teac = subj+"과목";
tvo.setsubj(subj);
}
pstmt.setString(4, teac);
rs = pstmt.executeQuery();
tvo.setId(id);
tvo.setName(name);
tvo.setGen(gen);
tvo.setType(type);
list.add(tvo);
System.out.print("\t1.추가입력 / 2. 그만입력 : ");
int stop = sc.nextInt();
if(stop==2) {
System.out.println();
System.out.println("***** 입력 종료 *****");
break;
}
}
}
catch(Exception e) {
e.printStackTrace();
}
return list;
}
public void toPrint(ArrayList<TeacVO> list) {
for(TeacVO t : list) {
System.out.println(t);
}
}
public ResultSet setTtmtRs(int no,String str) {
ResultSet rs = null;
try {
rs = stmt.executeQuery(str);
if(no==3) {
System.out.println(" < 조회 결과 > ");
while(rs.next()) {
String id = rs.getString("id");
String name =rs.getString("name");
String gen = rs.getString("gen");
String type = rs.getString("type");
System.out.println("id : "+id+" , name : "+name+" , 성별 : "+gen+"("+type+")");
}
}
}
catch(Exception e) {
e.printStackTrace();
}
return rs;
}
}4번부터는 전에 했던것과 동일하다..
4. VO 클래스
package VO;
public interface VO {
public String getName();
public void setName(String name);
public String getId();
public void setId(String id);
public String getGen();
public void setGen(int no);
public int getGrade();
public void setGrade(int grade);
}5. StudVO 클래스
package VO;
public class StudVO implements VO{
private String name,id,gen;
private int ban,grade;
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public int getBan() {
return ban;
}
public void setBan(int ban) {
this.ban= ban;
}
@Override
public String getName() {
return name;
}
@Override
public void setName(String name) {
this.name= name;
}
@Override
public String getId() {
return id;
}
@Override
public void setId(String id) {
this.id = id;
}
@Override
public String getGen() {
return gen;
}
@Override
public void setGen(int no) {
this.gen = no==1?"여자":"남자";
}
public void setGen(String gen) {
this.gen = gen;
}
public String toString() {
return "학생 >> 이름 : "+name+"("+gen+") ID : "+id +", "+grade+"학년"+ban+"반";
}
}6. TeacVO 클래스
package VO;
public class TeacVO implements VO{
private String name,id,gen,type,subj;
private int ban,grade;
public String getsubj() {
return subj;
}
public void setsubj(String subj) {
this.subj = subj;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public void setType(int no) {
this.type = no==1?grade+"학년"+ban+"반 담임선생님":"담당과목 : "+subj+"";
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public int getBan() {
return ban;
}
public void setBan(int ban) {
this.ban= ban;
}
@Override
public String getName() {
return name;
}
@Override
public void setName(String name) {
this.name= name;
}
@Override
public String getId() {
return id;
}
@Override
public void setId(String id) {
this.id = id;
}
@Override
public String getGen() {
return gen;
}
public void setGen(String gen) {
this.gen = gen;
}
@Override
public void setGen(int no) {
this.gen = no==1?"여자":"남자";
}
public String toString() {
return "선생님"+">> 이름 : "+name+"("+gen+", "+type+") ID : "+id;
}
}7. conSQL 클래스
package jdbc.SQL;
import java.sql.Connection;
import java.sql.DriverManager;
public class conSQL {
public conSQL() {
}
//드라이버 로딩, 커넥션 연결
public void setDriver() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(Exception e) {
System.out.println("=====드라이버 로딩 실패=====");
}
}
public Connection setCon() {
Connection con = null;
try {
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "scott";
String pw = "tiger";
con = DriverManager.getConnection(url,user,pw);
}
catch(Exception e) {
System.out.println("%%% 커넥션 연결 실패 %%%%");
}
return con;
}
}8. studSQL 클래스
package jdbc.SQL;
import VO.StudVO;
public class studSQL {
public final static int CREATE = 1;
public final static int INSERT = 2;
public final static int SELECT = 3;
public final static int DELETE = 4;
public String setData(int num) {
StringBuffer buff = new StringBuffer();
switch(num) {
case CREATE:
buff.append("create table info (");
buff.append(" id varchar2(10 char) constraint info_id_pk primary key, ");
buff.append(" name varchar2(4 char) constraint info_name_nn not null, ");
buff.append(" gen varchar2(3 char) constraint info_gen_nn not null, ");
buff.append(" ban number(1) constraint info_ban_nn not null, ");
buff.append(" grade number(1) constraint info_grad_nn not null ) ");
break;
case INSERT:
buff.append("insert into info ");
buff.append(" values (?,?,?,?,?) ");
break;
case SELECT:
buff.append("select ");
buff.append(" id,name,gen,ban,grade ");
buff.append("from ");
buff.append(" info ");
break;
case DELETE:
buff.append("drop table info");
break;
}
return buff.toString();
}
}9. teacSQL 클래스
package jdbc.SQL;
import VO.StudVO;
public class teacSQL {
//private String name,id,gen,type,subj;
//private int ban,grade;
public final static int CREATE = 1;
public final static int INSERT = 2;
public final static int SELECT = 3;
public final static int DELETE = 4;
public String setData(int num) {
StringBuffer buff = new StringBuffer();
switch(num) {
case CREATE:
buff.append("create table tinfo ( ");
buff.append(" id varchar2(10 char) constraint tinfo_id_pk primary key, ");
buff.append(" name varchar2(4 char) constraint tinfo_name_nn not null, ");
buff.append(" gen varchar2(3 char) constraint tinfo_gen_nn not null, ");
buff.append(" type varchar2(20 char) constraint tinfo_type_nn not null ) ");
break;
case INSERT:
buff.append("insert into tinfo ");
buff.append(" values (?,?,?,?) ");
break;
case SELECT:
buff.append("select ");
buff.append(" id, name, gen, type ");
buff.append("from ");
buff.append(" tinfo ");
break;
case DELETE:
buff.append("drop table tinfo");
break;
}
return buff.toString();
}
}10. 메인 : 스캐너로 입력받게 하였다..
package Test;
import java.util.Scanner;
import jdbc.tmt.Stmt;
import jdbc.tmt.stmt.Student;
import jdbc.tmt.stmt.Teacher;
public class Test {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("1. 학생 조회, 2. 선생님 조회");
int no= sc.nextInt();
Stmt tmt = null;
if(no==1) {
tmt = new Student();
}
else if(no==2) {
tmt = new Teacher();
}
}
}
일단 내가 원하도록 출력은 되었지만..
제약조건도 수정하고 좀 더 간결하게 코드를 짤 수 있는 방법을 생각해봐야겠다.ㅠㅠㅠㅠ