Java Swing, JDBC를 활용한 데이터베이스 관리 프로그램

구현 내용 요약

• 직원 정보를 전체 조회(보고서 출력), 조건 검색, 조건 삭제, 직원 추가, 조건 수정을 수행할 수 있는 프로그램
• 기본 요구사항을 넘어, “조건 수정” 기능을 추가 구현
• 자바 GUI 프로그램 제작을 위한 Java Swing 라이브러리를 활용하여 standalone 프로그램으로 구현
• DB connector 는 Jdbc 라이브러리를 활용하여 MySQL 로컬 데이터베이스와 연결함
• 소스코드는 깃허브 레포지토리에서 확인할 수 있다.

 

개발 환경

• Programming Language : Java 17 (openjdk-17)
• IDE : intelliJ Ultimate
• mainly-used library : java swing (GUI), jdbc (database connector)
• Database : MySQL 8.0.16

 

프로그램 구조

• GUI
  • DB연결, 직원 삭제, 직원 추가, 직원 검색(조건 검색 포함), 직원 수정의 기능을 수행하는 클래스를 각각 정의하고 JDBC 클래스 내부에서 인스턴스화하여 해당 기능들을 모두 수행할 수 있도록 JDBC와 관련된 Class를 정의하였다.
• JDBC
  • 로그인 화면을 위한 LoginFrame, 다양한 컴포넌트들을 포함하기 위한 MainPanel, MainPanel을 포함하기 위한 MainFrame, 직원 추가를 위한 SubFrame으로 GUI와 관련된 Class를 정의하였다.

 

프로그램 및 로직 설명

로그인 화면

데이터베이스에 연결하기 위한 정확한 username과 password, 그리고 database 이름을 입력할 수 있는 LoginFrame을 추가해 두었다.

따라서 이를 통해 정보를 입력하여 원하는 database에 접속하여 데이터를 조작할 수 있다.

만약 정확한 정보를 입력하지 않으면, beginRequest() 메서드에서 exception이 발생하여 catch 구문으로 넘어가기 때문에 메인 프레임이 열리지 않는다.

 

try {
    JDBC jdbc = new JDBC(ID, PW, DB_NAME);
    Connection conn = jdbc.connectJDBC();
    conn.beginRequest();
		
		// if no exception ...
		jdbc.disconnectJDBC();
    MainFrame mainFrame = new MainFrame();
    dispose();
} catch (Exception exception) {
    exception.printStackTrace();
}

여기서 beginRequest()는 기본적으로 no-op 메서드이며, 데이터베이스와 정상적으로 connection 객체를 생성한 뒤 커넥션 풀 매니저에 의해 수행되는 연결상태 확인용 메서드이다. 이를 활용하여 정확하지 않은 값을 입력하여 커넥션이 정상적으로 메인 프레임이 열리는 이슈를 방지하였다.

 

보고서 출력(전체 조회)

createSql() 메서드를 정의하여, 입력되는 조건을 바탕으로 select / from / where 절로 구성되는 sql 문자열을 완성한 뒤, jdbc의 executeQuery를 사용하여 ResultSet을 얻는다. 이를 바탕으로 얻은 결과 records들을 model에 저장한 뒤, JTable에 model을 올려서 사용자에게 보여준다.

Statement stmt = conn.createStatement();
String attribute = parseAttribute(Objects.requireNonNull(category.getSelectedItem()).toString());
String condition = parseCondition(attribute, text, sex, department);
String sql = createSql(checkBox, attribute, condition);

전체 조회의 경우에는 SQL을 구성할 때 where 절의 조건을 사용하지 않는 조회이므로, 체크박스를 탐색하여 select와 from 구문에 들어갈 attributes들을 파싱하여 명령문을 구성한다.

for(JCheckBox j : checkBox){
    // 해당 attribute가 체크되어있다면 select 절에 포함시킨다
    if(j.isSelected()) {
        // 매핑 정보 임시 저장
        String tmpAttribute = attributeMap.get(j.getText());
        // 매핑 정보가 존재하면 매핑되어있는 정보로 attribute 이름 추가,
        // 매핑되어 있지 않다면 JCheckBox의 text 그대로 추가
        if(tmpAttribute != null){
            // 매핑된 정보가 dname인 경우 department table과의 join이 필요하므로 추가
            if(tmpAttribute.equals(attributeMap.get("Department"))){
                fromClause += attributeMap.get("Dname");
            } else if (tmpAttribute.equals(attributeMap.get("Supervisor"))) {
                fromClause += attributeMap.get("Super_ssn");
            }
            attributes.add(tmpAttribute);
        } else attributes.add("e." + j.getText());
    }
}
// 선택된 attributes 들을 join하여 select 절에 추가
selectClause += String.join(", ", attributes);

이 때, Dname을 조회하려고 하는 경우에는 Department 테이블의 정보가 필요하므로 join연산이 발생하며, 만약 Dname이 조회 attribute로 포함되지 않는 경우에는 join 쿼리가 나가지 않도록 하였다. 이와 유사한 맥락으로, Super_ssn이 체크되지 않으면 “join Employee as s on e.super_ssn = s.ssn” 이라는 구문 또한 실행하려는 SQL에 구태여 추가되지 않도록 하여 경제적인 쿼리 구성을 노력하였다.

위와 같이 SQL문을 구성한 이후에는 table에 model을 추가하여 보여준다. table은 기본적으로 여러 record를 담아야 하므로 scroll이 가능한 scrollPane에 담아주고, 이 테이블에 checkbox 기능을 넣어서 향후 업데이트 또는 삭제에 활용해야 하므로 이벤트리스너를 연결해 주었다.

model = jdbc.printReport(model, items, category, text, sex, department); // 모델이 반환됨

...

tablePanel = new JPanel();
scrollPane = new JScrollPane(this.table);
table.getModel().addTableModelListener(new modelEventListener());
scrollPane.setPreferredSize(new Dimension(1000, 300));
tablePanel.add(scrollPane);
super.add(tablePanel, BorderLayout.CENTER);
super.revalidate();

 

조건 검색

조건 검색은 기본적으로 검색 범위(검색 필터)로부터 검색 카테고리와 검색 조건을 입력받아 파싱하여 SQL의 where 절 부분 생성에 활용된다.

if(!attribute.isBlank() && !condition.isBlank()){
    if (attribute.equals("CONCAT(e.Fname, ' ', e.Minit, ' ', e.Lname)")) whereClause = baseWhereCluase + attribute + " = " + "\\"" + condition + "\\"";
    else if(attribute.equals("Salary")) whereClause = baseWhereCluase + "e." + attribute + " >= " + "\\"" + condition + "\\"";
    else if(attribute.equals("Supervisor")) whereClause = baseWhereCluase + attributeMap.get("where"+attribute) + " = " + "\\"" + condition + "\\"";
    else if(attribute.equals("Dname")) whereClause = baseWhereCluase + attribute + " = " + "\\"" + condition + "\\"";

    else whereClause = baseWhereCluase + "e." + attribute + " = " + "\\"" + condition + "\\"";
}

검색 필터를 통한 where 절 구성 외에, select 하는 attribute를 checkbox로 지정하여 SQL을 구성하는 것은 위 전체 보고서 출력 로직에서 다뤘던 내용과 동일하며, 이를 통해 생성한 명령 쿼리를 수행하여 얻은 Resultset으로 테이블에 입력할 model을 만들어서 JTable에 입혀서 사용자에게 보여주게 된다.

 

조건 삭제

조건 삭제는 조건 검색을 통해 확인한 레코드들 중 체크된 레코드들에 대하여 삭제를 하는 기능이다. 이를 위해서 테이블 상의 체크박스가 클릭되는 이벤트가 발생했을 때 그 이벤트를 받은 레코드의 ssn 을 Set 에 담아두고, 삭제 버튼 클릭 이벤트가 발생했을 때 그 ssn 집합을 순회하며 해당 ssn을 가진 record를 DB에서 삭제할 수 있게 하였다.

private PreparedStatement getQueryForm(Connection conn) throws SQLException {
    PreparedStatement pstmt;
    StringBuilder sb = new StringBuilder();
    String sql = sb.append(baseDeleteClause).append("ssn = ?;").toString();
    pstmt = conn.prepareStatement(sql);
    return pstmt;
}

private static void setQueryForm(Set<String> ssnList, PreparedStatement pstmt) throws SQLException {
    for (String ssn : ssnList) {
        pstmt.setString(1, ssn);
        pstmt.addBatch();
        pstmt.clearParameters();
    }
}

이 과정에서 여러 레코드를 지워야 하는 경우가 많게 될텐데, 매번 SQL을 작성하고 하나하나 쿼리를 단 건씩 커밋하는 것은 매우 비효율적이다. 따라서 batch 방식으로 쿼리문을 전송할 수 있도록 처리해 주었다. (만약 문제가 있으면 롤백될 수 있도록 autoCommit()은 false로 설정하였다.)

 

직원 추가

직원을 추가하기 위해서는 직원 정보를 입력받아야 했기에 서브프레임을 띄워서 새로 등록하려는 직원의 정보를 받고, 이렇게 입력되는 정보를 기반으로 insert 구문을 완성하여 database로 commit해주었다.

sql = "INSERT INTO EMPLOYEE (fname, minit, lname, ssn, bdate, address, sex, salary, super_ssn, dno) " +
          "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

PreparedStatement p = conn.prepareStatement(sql);
p.setString(1, firstName);
p.setString(2, middleInitial);
p.setString(3, lastName);
p.setString(4, ssn);
p.setString(5, birthdate);
p.setString(6, address);
p.setString(7, sex);
p.setDouble(8, salary);
p.setString(9, super_ssn);
p.setInt(10, dno);
p.executeUpdate();

 

조건 수정

조건 수정 조건 검색을 통해 확인한 레코드들 중 체크된 레코드들에 대하여 수정을 하는 기능이다. 이를 위해서 조건 삭제와 유사하게 테이블 상의 체크박스가 클릭되는 이벤트가 발생했을 때 그 이벤트를 받은 레코드의 ssn 을 Set 에 담아두고, 수정 버튼 클릭 이벤트가 발생했을 때 그 ssn 집합을 순회하며 해당 ssn을 가진 record를 하단에 입력한 수정 카테고리와 수정 값을 입력 받아 파싱하여 해당 값으로 수정될 수 있게 하였다.

String attribute = parseAttribute(Objects.requireNonNull(category.getSelectedItem()).toString());
String condition = parseCondition(attribute, text, sex, department);

try (Statement stmt = conn.createStatement()) {
    for (String ssn : ssnList) {
        String sql = createSql(ssn, attribute, condition, conn);
        stmt.addBatch(sql);
    }
    stmt.executeBatch();
} catch (SQLException sqlException){
    if(sqlException.getMessage().startsWith("Duplicate")) throw new SQLIntegrityConstraintViolationException();
    else throw sqlException;
}

parseAttribute(), parseCondition() 함수를 정의하여 입력 받은 매개 변수를 토대로 수정할 attribute와 수정할 값(condition)을 구한다. 이어 createSql을 정의하여 변경할 레코드의 ssn, 파싱한 attribute, condition과 connection을 넘겨주고 sql문을 생성하여 쿼리를 진행할 준비를 한다. 이는 ssnList내의 모든 ssn에(선택된 레코드들에) 대하여 수행된다.

 

조건 삭제와 마찬가지로 여러 레코드에 대해서 수정하는 경우 매번 SQL을 작성하지 않기 위해 batch 방식으로 쿼리문을 전송할 수 있도록 처리해주었다.

 

catch문에서는 쿼리 도중 발생할 수 있는 예외를 처리한다. 예외 내용이 Duplicate…인 경우 개체 무결성을 위반한 것이므로 SQLIntegrity…예외를 던지고 이 외의 경우는 일반적인 sqlException 예외를 던져 오류 화면을 구분하여 표시하도록 했다.(적용 화면은 결과 화면 항목에 기재)

 

createSql() 함수의 로직은 아래와 같다.

String updateClause = baseUpdateClause;
String setClause = baseSetClause;
String whereClause = baseWhereClause;

if(setAttribute.equals("Dno")){
    String tmpDno = findAttribute("selectDno", setCondition, conn);
    if(tmpDno == null) throw new SQLDataException();
    setClause += setAttribute + " = " + tmpDno;
} else if(setAttribute.equals(attributeMap.get("Name")) || setAttribute.equals(attributeMap.get("Supervisor"))){
    // 수정하려는 값이 Supervisor일 경우 자신의 Ssn을 이용하여 Supervisor의 Ssn값을 찾는다.
    if(setAttribute.equals(attributeMap.get("Supervisor"))){
        ssn = findAttribute("selectSuperSsn", ssn, conn);
    }
    String parsedName = parseName(attributeMap.get("Name"), setCondition);
    if(parsedName == null) throw new SQLDataException();
    setClause += parsedName;
}else{
    setClause += setAttribute + " = " + "\\"" + setCondition + "\\"";
}
whereClause += "Ssn = " + "\\"" + ssn + "\\"" ;
return updateClause + setClause + whereClause;

 

선택된 attribute가 Dno인 경우 변경하려는 부서 이름의 Dno를 알아야 하기 때문에 findAttribute() 함수를 통하여 Dno을 알아낸다.(findAttribute는 parameter로 전달한 값과 미리 정의한 sql문을 이용하여 원하는 attribute의 값을 얻는 함수이다).

반환 된 값이 null이라면 Dno에 해당하는 부서가 없는 것이므로(유효하지 않은 Dno) SQLDataException 예외를 던진다. 값이 존재한다면 변경하려는 부서의 Dno를 더해주어 setClause를 완성한다.

 

선택된 attribute가 Name인 경우 입력한 Name 값을 parseName()함수를 이용하여 각각 Fname, Minit, Lname으로 파싱한 set절을 만들어 낸다. parseName()이 제대로 실행되지 않아 null을 반환한 경우 입력된 데이터가 유효하지 않으므로 예외를 던진다.

 

선택된 attribute가 Supervisor 인 경우 Dno와 마찬가지로 선택한 레코드의 Super_ssn을 가져와 그 Super_ssn의 이름을 변경해야 한다. 이때 위와 동일하게 findAttribute() 함수를 사용한다.

이 외의 경우에는 선택된 attribute와 condition을 그대로 setClause에 더하여 setClause를 완성한다. whereClause의 경우 해당 레코드의 Ssn을 기준으로 변경하는 것이므로 특별한 조건 없이 Ssn을 더해 완성한다.

완성된 update, set, where절을 모두 더하여 sql문을 완성한다.

 

Reference

구현 당시 swing을 잘 몰라서 특정 블로그를 참고하였다.