정보검색시스템 구현
• JDBC를 사용하여 MYSQL에 접근
→ COMPANY 데이터베이스 사용
• 원하는 데이터 조회, 삭제, 수정 기능 구현
→ 뒤에 나오는 총 5가지의 기능을 구현할 것
주어진 5가지 기능 외에 추가 기능 구현 시 가산점 부여
(단, 5가지 기능이 완벽할 경우에만 구현 / 추가 기능이 있지만 주어진 기능이 실행이 안되는 경우 가산점 X)
• JAVA GUI 또는 웹으로 구현
예제 프로그램
1. EMPLOYEE 테이블의 attribute들을 출력
단, Super_ssn 과 Dno 대신 상사 이름 부서명으로 바꾸어 출력할 것 (JOIN 사용)
2. 전체 직원 출력 또는 선택한 부서의 직원만을 검색하여 출력
3. 출력하고 싶은 attribute들을 선택하여 선택된 attribute만을 출력
4. 검색된 직원을 선택하고 DB에서 삭제
5. 검색된 직원을 선택하고 Salary를 입력하여 그 직원의 Salary를 수정
진행과정
1. 서론
a) 문제 제기
- 정형화된 데이터를 저장 및 관리하며 사용자가 필요로 하는 정보의 조회, 삭제, 수정을 효율적으로 수행할 수 있도록 하는 정보 검색 시스템을 생성한다.
- 관계형 데이터베이스에 저장된 데이터를 접근 및 조작할 수 있게 하는 API인 JDBC와 JAVA프로그램 및 SQL 명령어를 사용해 정보 검색 시스템을 구현한다.
b) 목표 설정
- 데이터베이스를 설계, 구현 및 응용하는 일련의 과정을 습득한다.
- MySQL로 명령 프롬프트에서 초기 데이터 베이스를 구축한다.
- Java와 JDBC를 이용해 데이터베이스를 연결 후 저장된 데이터에서 필요한 정보를 추출하는 프로그램을 구현한다.
- JOIN Query등을 사용해 데이터베이스를 응용한다.
2. 알고리즘 및 분석
▶ GUI 구조:
기능별로 각각의 JPanel을 구성함.
결과 table을 포함한 panel은 ‘검색’을 눌러야 actionPerformed를 통해서 add 됨.
▶ DB 연결:
actionPerformed에 의해 실행됨. Trigger는 ‘검색’, ‘UPDATE’, ‘삭제’ 버튼.
Try문에서 JDBC 드라이버 연결 후 사용자 정보와 DB url을 이용해서 DB access 시도함.
정상적으로 연결되지 않는다면 exception을 catch함.
▶ 선택된 attribute/부서만 출력:
검색 항목의 checkbox 선택 여부에 따라 Head (table 열 이름), SQL문이 결정됨.
검색 항목이 하나 이상 선택되었을 때에만 SQL query 작성.
검색 버튼을 누를 때마다 Head도 갱신되어야 하기 때문에 항상 처음에 .clear()로 초기화.
두번째 checkbox (c2)부터는 이전의 checkbox select 여부에 따라 경우를 나눠서 SQL SELECT문을 완성해야 함.
Left outer join을 하는 이유는 Supervisor가 없는 경우(NULL)도 표시하기 위함.
JComboBox Dept에서 선택된 부서의 직원만을 출력하도록 조건 추가.
검색 항목을 하나도 선택하지 않은 채 검색을 했을 때 경고창이 뜨게 됨.
▶ 검색 결과 출력:
DefaultTableModel (1열 checkbox 제외 table cell 직접 수정 불가) 및 JTable 생성. (Salary 수정 및 선택된 직원 이름 출력을 위해 해당 열 저장)
JTable은 이후 행의 1열의 Boolean 값을 ‘선택’ 열에서 체크박스로 나타내기 위해 Boolean.class 반환.
vector<Object>를 이용하여 체크박스로 선택된 열의 값을 테이블에 넣고 int rowCnt 변수를 통해 인원수를 더함. (정상 실행됐다면 count = 1)
이를 JScrollPane에 넣어 갱신된 화면을 revalidate()를 통해 출력.
이후 다시 table을 생성할 때는 .remove()를 통해서 container 내용을 초기화를 먼저 해줘야 함.
▶ 삭제 기능: ( // DELETE ~ // DELETE 끝 )
for, if문을 통해 각 ‘선택’ 체크박스가 체크된 행의 ssn를 vector에 더함.
.removeRow() 함수를 통해 해당 행 삭제 및 vector에 저장된 ssn을 이용하여 delete SQL문을 실행.
체크박스 제외 2번째 열의 column name이 SSN이 아니라면 검색 항목 중 NAME, SSN을 체크하도록 알림창을 띄우고, delete문은 실행하지 않음.
▶ Salary 갱신 기능: ( // UPDATE ~ // UPDATE 끝 )
for, if문을 통해 각 ‘선택’ 체크박스가 체크된 행의 ssn를 vector에 더함.
setValueAt() 함수를 통해 salary열을 찾아 수정된 salary 값을 삽입하고 vector에 저장된 ssn을 이용하여 update set SQL문을 실행.
만약 체크박스 제외 6번째 열의 column name이 SALARY가 아니라면 검색 항목을 모두 체크하도록 알림창을 띄우고, salary update는 하지 않음.
▶ 선택한 직원 출력:
public class CheckBoxModelListener implements TableModelListener{}
JTable에서 테이블의 특정 값이 바뀌었을 때 실행.
boolean(체크박스)값이 있는 열이 0번열이므로 상수로 위에서 지정,
if (column==BOOLEAN_COLUMN)의 값이 체크되었을 때, 체크 해제되었을 때 전체 열 중에 체크 되어있는 열의 NAME을 읽어서 출력.
만약 체크박스 바로 다음 열의 column name이 NAME이 아니라면 NAME 출력하지 않음.
3. 결과화면 실행창
0. 초기화면
1. EMPLOYEE 테이블의 attribute들 출력
EMPLOYEE, DEPARTMENT 테이블에서 Left outer join을 이용하고 e.super_ssn=s.ssn 와 e.dno = dnumber 의 조건을 이용해서 Super_ssn 대신 SUPERVISOR, Dno 대신 DEPARTMENT를 출력해서 보여준다.
2. 전체 직원 출력 또는 선택한 부서의 직원만을 검색하여 출력
검색범위를 전체에서 부서별로 바꾸고 오른쪽에 부서명을 원하는 부서명으로 설정한 후 검색버튼을 누르면 원하는 부서의 employee 정보만 보여준다.
검색 항목을 하나도 선택하지 않은 채 검색을 하게 되면 경고창이 뜨게 된다. 최소한 하나의 검색 항목을 선택해야 원하는 결과 table이 출력된다.
3. 출력하고 싶은 attribute들을 선택하여 선택된 attribute만을 출력
Name, Ssn, Address, Salary, Supervisor, Department를 선택해서 출력한 화면
Name, Ssn, Bdate, Sex, Salary, Department를 선택해서 출력한 화면
4. 검색된 직원을 선택하고 DB에서 삭제
검색된 직원 중에서 John B Smith와 Joyce A English를 선택한다. 선택된 직원은 왼쪽 아래에 표시해서 보여준다.
오른쪽 아래에 선택한 데이터 삭제 버튼을 누르면 선택되어 있던 employee 정보들이 모두 삭제된다.
위와 같이 John B Smith 와 Joyce A English의 정보가 사라진 것을 볼 수 있다.
누군가의 Supervisor로 등록된 직원을 선택 후 삭제한 경우, 결과는 위와 같다. Franklin T Wong을 삭제했지만 John, Joyce, Ramesh의 supervisor로 남아있다.
분명히 삭제는 되었지만 table에 바로 갱신이 되지는 않는다. 하지만 이 상태에서 검색을 누르면 아래 보이는 것처럼 갱신된 결과가 제대로 뜨게 된다.
검색 항목 중 Name, Ssn을 체크 후 검색해야만 선택된 직원이 삭제되도록 설정했다.
Attribute 중 Name이 선택되어 있을 때만 밑에 선택한 직원의 이름을 정상적으로 보여준다. Name을 선택하지 않으면 DB에서 name값을 읽어오지 않기 때문이다. 따라서 Name을 선택하지 않았다면 아래 보이는 것처럼 선택된 직원을 출력하지 않도록 했다.
5. 검색된 직원을 선택하고 Salary를 입력하여 그 직원의 Salary를 수정
Ahmad V Jabbar를 선택하고 새로운 Salary에 30000을 입력 후 오른쪽에 UPDATE 버튼을 클릭
Ahmad V Jabbar의 Salary가 25000에서 30000으로 수정된 것을 볼 수 있다.
하지만 Salary를 포함해서 attribute가 모두 선택되어 있을 때만 Salary의 값을 정상적으로 바꿀 수 있다. 6번째 column name이 Salary일 때에 수정이 가능하도록 했기 때문이다.
Code
package termProject;
import java.sql.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
public class Company extends JFrame implements ActionListener {
public Connection conn;
public Statement s;
public ResultSet r;
private JComboBox Category;
private JComboBox Dept;
private JCheckBox c1 = new JCheckBox("Name", true);
private JCheckBox c2 = new JCheckBox("Ssn", true);
private JCheckBox c3 = new JCheckBox("Bdate", true);
private JCheckBox c4 = new JCheckBox("Address", true);
private JCheckBox c5 = new JCheckBox("Sex", true);
private JCheckBox c6 = new JCheckBox("Salary", true);
private JCheckBox c7 = new JCheckBox("Supervisor", true);
private JCheckBox c8 = new JCheckBox("Department", true);
private Vector<String> Head = new Vector<String>();
private JTable table;
private DefaultTableModel model;
private static final int BOOLEAN_COLUMN = 0;
private int NAME_COLUMN = 0;
private int SALARY_COLUMN = 0;
private String dShow;
private JButton Search_Button = new JButton("검색");
Container me = this;
private JLabel totalEmp = new JLabel("인원수 : ");
final JLabel totalCount = new JLabel();
JPanel panel;
JScrollPane ScPane;
private JLabel Emplabel = new JLabel("선택한 직원: ");
private JLabel ShowSelectedEmp = new JLabel();
private JLabel Setlabel = new JLabel("새로운 Salary: ");
private JTextField setSalary = new JTextField(10);
private JButton Update_Button = new JButton("UPDATE");
private JButton Delete_Button = new JButton("선택한 데이터 삭제");
int count = 0;
public Company() {
JPanel ComboBoxPanel = new JPanel();
String[] category = { "전체", "부서별" };
String[] dept = { "Research", "Administration", "Headquarters" };
Category = new JComboBox(category);
Dept = new JComboBox(dept);
ComboBoxPanel.setLayout(new FlowLayout(FlowLayout.LEFT));
ComboBoxPanel.add(new JLabel("검색 범위 "));
ComboBoxPanel.add(Category);
ComboBoxPanel.add(Dept);
JPanel CheckBoxPanel = new JPanel();
CheckBoxPanel.setLayout(new FlowLayout(FlowLayout.LEFT));
CheckBoxPanel.add(new JLabel("검색 항목 "));
CheckBoxPanel.add(c1);
CheckBoxPanel.add(c2);
CheckBoxPanel.add(c3);
CheckBoxPanel.add(c4);
CheckBoxPanel.add(c5);
CheckBoxPanel.add(c6);
CheckBoxPanel.add(c7);
CheckBoxPanel.add(c8);
CheckBoxPanel.add(Search_Button);
JPanel ShowSelectedPanel = new JPanel();
ShowSelectedPanel.setLayout(new FlowLayout(FlowLayout.LEFT));
Emplabel.setFont(new Font("Dialog", Font.BOLD, 16));
ShowSelectedEmp.setFont(new Font("Dialog", Font.BOLD, 16));
dShow = "";
ShowSelectedPanel.add(Emplabel);
ShowSelectedPanel.add(ShowSelectedEmp);
JPanel TotalPanel = new JPanel();
TotalPanel.setLayout(new FlowLayout(FlowLayout.LEFT));
TotalPanel.add(totalEmp);
TotalPanel.add(totalCount);
JPanel UpdatePanel = new JPanel();
UpdatePanel.setLayout(new FlowLayout(FlowLayout.CENTER));
UpdatePanel.add(Setlabel);
UpdatePanel.add(setSalary);
UpdatePanel.add(Update_Button);
JPanel DeletePanel = new JPanel();
DeletePanel.setLayout(new FlowLayout(FlowLayout.RIGHT));
DeletePanel.add(Delete_Button);
JPanel Top = new JPanel();
Top.setLayout(new BoxLayout(Top, BoxLayout.Y_AXIS));
Top.add(ComboBoxPanel);
Top.add(CheckBoxPanel);
JPanel Halfway = new JPanel();
Halfway.setLayout(new BoxLayout(Halfway, BoxLayout.X_AXIS));
Halfway.add(ShowSelectedPanel);
JPanel Bottom = new JPanel();
Bottom.setLayout(new BoxLayout(Bottom, BoxLayout.X_AXIS));
Bottom.add(TotalPanel);
Bottom.add(UpdatePanel);
Bottom.add(DeletePanel);
JPanel ShowVertical = new JPanel();
ShowVertical.setLayout(new BoxLayout(ShowVertical, BoxLayout.Y_AXIS));
ShowVertical.add(Halfway);
ShowVertical.add(Bottom);
add(Top, BorderLayout.NORTH);
add(ShowVertical, BorderLayout.SOUTH);
Search_Button.addActionListener(this);
Delete_Button.addActionListener(this);
Update_Button.addActionListener(this);
setTitle("Information Retrival System");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setSize(1300, 600);
setLocationRelativeTo(null);
setVisible(true);
}
public void actionPerformed(ActionEvent e) {
// DB연결
try {
Class.forName("com.mysql.cj.jdbc.Driver"); // JDBC 드라이버 연결
String user = "root";
String pwd = "0000"; // 비밀번호 입력
String dbname = "company";
String url = "jdbc:mysql://localhost:3306/" + dbname + "?serverTimezone=UTC";
conn = DriverManager.getConnection(url, user, pwd);
System.out.println("정상적으로 연결되었습니다.");
} catch (SQLException e1) {
System.err.println("연결할 수 없습니다.");
e1.printStackTrace();
} catch (ClassNotFoundException e1) {
System.err.println("드라이버를 로드할 수 없습니다.");
e1.printStackTrace();
}
// ------------------------------------------------------------------------ //
if (count == 1) {
me.remove(panel);
revalidate();
}
if (e.getSource() == Search_Button) {
if (c1.isSelected() || c2.isSelected() || c3.isSelected() || c4.isSelected() || c5.isSelected()
|| c6.isSelected() || c7.isSelected() || c8.isSelected()) {
Head.clear();
Head.add("선택");
String stmt = "select";
if (c1.isSelected()) {
stmt += " concat(e.fname,' ', e.minit,' ', e.lname,' ') as Name";
Head.add("NAME");
}
if (c2.isSelected()) {
if (!c1.isSelected())
stmt += " e.ssn";
else
stmt += ", e.ssn";
Head.add("SSN");
}
if (c3.isSelected()) {
if (!c1.isSelected() && !c2.isSelected())
stmt += " e.bdate";
else
stmt += ", e.bdate";
Head.add("BDATE");
}
if (c4.isSelected()) {
if (!c1.isSelected() && !c2.isSelected() && !c3.isSelected())
stmt += " e.address";
else
stmt += ", e.address";
Head.add("ADDRESS");
}
if (c5.isSelected()) {
if (!c1.isSelected() && !c2.isSelected() && !c3.isSelected() && !c4.isSelected())
stmt += " e.sex";
else
stmt += ", e.sex";
Head.add("SEX");
}
if (c6.isSelected()) {
if (!c1.isSelected() && !c2.isSelected() && !c3.isSelected() && !c4.isSelected()
&& !c5.isSelected())
stmt += " e.salary";
else
stmt += ", e.salary";
Head.add("SALARY");
}
if (c7.isSelected()) {
if (!c1.isSelected() && !c2.isSelected() && !c3.isSelected() && !c4.isSelected() && !c5.isSelected()
&& !c6.isSelected())
stmt += " concat(s.fname, ' ', s.minit, ' ',s.lname,' ') as Supervisor ";
else
stmt += ", concat(s.fname, ' ', s.minit, ' ',s.lname,' ') as Supervisor ";
Head.add("SUPERVISOR");
}
if (c8.isSelected()) {
if (!c1.isSelected() && !c2.isSelected() && !c3.isSelected() && !c4.isSelected() && !c5.isSelected()
&& !c6.isSelected() && !c7.isSelected())
stmt += " dname";
else
stmt += ", dname";
Head.add("DEPARTMENT");
}
stmt += " from employee e left outer join employee s on e.super_ssn=s.ssn, department where e.dno = dnumber";
if (Category.getSelectedItem().toString() == "부서별") {
if (Dept.getSelectedItem().toString() == "Research")
stmt += " and dname = \"Research\";";
else if (Dept.getSelectedItem().toString() == "Administration")
stmt += " and dname = \"Administration\";";
else if (Dept.getSelectedItem().toString() == "Headquarters")
stmt += " and dname = \"Headquarters\";";
}
model = new DefaultTableModel(Head, 0) {
@Override
public boolean isCellEditable(int row, int column) {
if (column > 0) {
return false;
} else {
return true;
}
}
};
for (int i = 0; i < Head.size(); i++) {
if (Head.get(i) == "NAME") {
NAME_COLUMN = i;
} else if (Head.get(i) == "SALARY") {
SALARY_COLUMN = i;
}
}
table = new JTable(model) {
@Override
public Class getColumnClass(int column) {
if (column == 0) {
return Boolean.class;
} else
return String.class;
}
};
ShowSelectedEmp.setText(" ");
try {
count = 1;
s = conn.createStatement();
r = s.executeQuery(stmt);
ResultSetMetaData rsmd = r.getMetaData();
int columnCnt = rsmd.getColumnCount();
int rowCnt = table.getRowCount();
while (r.next()) {
Vector<Object> tuple = new Vector<Object>();
tuple.add(false);
for (int i = 1; i < columnCnt + 1; i++) {
tuple.add(r.getString(rsmd.getColumnName(i)));
}
model.addRow(tuple);
rowCnt++;
}
totalCount.setText(String.valueOf(rowCnt));
} catch (SQLException ee) {
System.out.println("actionPerformed err : " + ee);
ee.printStackTrace();
}
panel = new JPanel();
ScPane = new JScrollPane(table);
table.getModel().addTableModelListener(new CheckBoxModelListener());
ScPane.setPreferredSize(new Dimension(1100, 400));
panel.add(ScPane);
add(panel, BorderLayout.CENTER);
revalidate();
} else {
JOptionPane.showMessageDialog(null, "검색 항목을 한개 이상 선택하세요.");
}
}
// DELETE
if (e.getSource() == Delete_Button) {
Vector<String> delete_ssn = new Vector<String>();
try {
String columnName = model.getColumnName(2);
if (columnName == "SSN") {
for (int i = 0; i < table.getRowCount(); i++) {
if (table.getValueAt(i, 0) == Boolean.TRUE) {
delete_ssn.add((String) table.getValueAt(i, 2));
}
}
for (int i = 0; i < delete_ssn.size(); i++) {
for (int k = 0; k < model.getRowCount(); k++) {
if (table.getValueAt(k, 0) == Boolean.TRUE) {
model.removeRow(k);
totalCount.setText(String.valueOf(table.getRowCount()));
}
}
}
for (int i = 0; i < delete_ssn.size(); i++) {
String deleteStmt = "DELETE FROM EMPLOYEE WHERE Ssn=?";
PreparedStatement p = conn.prepareStatement(deleteStmt);
p.clearParameters();
p.setString(1, String.valueOf(delete_ssn.get(i)));
p.executeUpdate();
}
} else {
JOptionPane.showMessageDialog(null, "삭제 작업을 진행하시려면 NAME, SSN 항목을 모두 체크해주세요.");
}
ShowSelectedEmp.setText(" ");
} catch (SQLException e1) {
System.out.println("actionPerformed err : " + e1);
e1.printStackTrace();
}
panel = new JPanel();
ScPane = new JScrollPane(table);
ScPane.setPreferredSize(new Dimension(1100, 400));
panel.add(ScPane);
add(panel, BorderLayout.CENTER);
revalidate();
} // DELETE 끝
// UPDATE
if (e.getSource() == Update_Button) {
Vector<String> update_ssn = new Vector<String>();
try {
String columnName = model.getColumnName(6);
if (columnName == "SALARY") {
for (int i = 0; i < table.getRowCount(); i++) {
if (table.getValueAt(i, 0) == Boolean.TRUE) {
update_ssn.add((String) table.getValueAt(i, 2));
String updateSalary = setSalary.getText();
table.setValueAt(Double.parseDouble(updateSalary), i, SALARY_COLUMN);
}
}
for (int i = 0; i < update_ssn.size(); i++) {
String updateStmt = "UPDATE EMPLOYEE SET Salary=? WHERE Ssn=?";
PreparedStatement p = conn.prepareStatement(updateStmt);
p.clearParameters();
String updateSalary = setSalary.getText();
p.setString(1, updateSalary);
p.setString(2, String.valueOf(update_ssn.get(i)));
p.executeUpdate();
}
} else {
JOptionPane.showMessageDialog(null, "수정 작업을 진행하시려면 검색 항목을 모두 체크해주세요.");
}
ShowSelectedEmp.setText(" ");
} catch (SQLException e1) {
System.out.println("actionPerformed err : " + e1);
e1.printStackTrace();
}
panel = new JPanel();
ScPane = new JScrollPane(table);
ScPane.setPreferredSize(new Dimension(1100, 400));
panel.add(ScPane);
add(panel, BorderLayout.CENTER);
revalidate();
} // UPDATE 끝
}
public class CheckBoxModelListener implements TableModelListener {
public void tableChanged(TableModelEvent e) {
int row = e.getFirstRow();
int column = e.getColumn();
if (column == BOOLEAN_COLUMN) {
TableModel model = (TableModel) e.getSource();
String columnName = model.getColumnName(1);
Boolean checked = (Boolean) model.getValueAt(row, column);
if (columnName == "NAME") {
if (checked) {
dShow = "";
for (int i = 0; i < table.getRowCount(); i++) {
if (table.getValueAt(i, 0) == Boolean.TRUE) {
dShow += (String) table.getValueAt(i, NAME_COLUMN) + " ";
}
}
ShowSelectedEmp.setText(dShow);
} else {
dShow = "";
for (int i = 0; i < table.getRowCount(); i++) {
if (table.getValueAt(i, 0) == Boolean.TRUE) {
dShow += (String) table.getValueAt(i, 1) + " ";
}
}
ShowSelectedEmp.setText(dShow);
}
}
}
}
}
public static void main(String[] args) {
new Company();
}
}