Swing 을 이용한 DB 연동 프로그램

Swing 을 이용한 DB 연동 프로그램 입니다.

기능 설명 :
- ID : DB 접속 ID
- PASSWORD : DB 접속 PASSWORD
- Connect : ID,PASSWORD 입력 후 Connect 버튼 클릭시 DB 연결
- Close : DB 연결 후 Close 버튼 클릭 시 연결 해제
- Execute : 하단 SQL 문을 작성후 Enter 를 치거나 Execute 버튼 클릭시 SQL 문 실행
- Exit : 프로그램 Close
- 하단 SQL TextField : SQL 문을 작성 후 Enter 쳐도 실행이 된다. 

< JDBCSwing.java >

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.border.BevelBorder;
import javax.swing.border.TitledBorder;
import javax.swing.table.TableModel;

public class JDBCSwing implements ActionListener {
	private JFrame f;
	private Font font;
	private DBBean db;
	private Connection conn;
	private Container con;
	private JScrollPane pane;
	private JTable table;
	private JPanel nPanel, cPanel, sPanel;
	private JTextField tfID, tfPwd, tfSql;
	private JButton btnConnect, btnClose, btnExecute, btnExit;
	// DB 접속 Constructor
	JDBCSwing() throws IOException, ClassNotFoundException{
		db = new DBBean("oracle");
		db.loadDriver();
		font = new Font("Times New Romans", 
				                 Font.BOLD, 25);
	}
	private void go(){
		// Frame 제작
		f = new JFrame("My SQLgate");
		f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		con = f.getContentPane();
		con.setLayout(new BorderLayout());
		nPanel = getNorthPanel();
		cPanel = getCenterPanel();
		sPanel = getSouthPanel();
		con.add("North", nPanel);
		con.add("Center", cPanel);
		con.add("South", sPanel);
		f.setSize(600, 400);
		f.setVisible(true);
	}
	private JPanel getSouthPanel(){
		//SQL 문을 입력할 South Panel
		JPanel panel = new JPanel();
		BevelBorder bb = 
			new BevelBorder(BevelBorder.RAISED);
		TitledBorder tb = new TitledBorder(bb, 
				"SQL 문을 입력하세요");
		panel.setBorder(tb);
		panel.setLayout(new BorderLayout());
		panel.add(tfSql = new JTextField());
		// tfSql TextField 에서 Enter 눌렀을 경우 발생 Event
		tfSql.addActionListener(this);
		tfSql.setFont(font);
		return panel;
	}
	private JPanel getCenterPanel(){
		// 테이블을 출력할 Center Panel
		JPanel panel = new JPanel();
		panel.setLayout(new BorderLayout());
		pane = new JScrollPane(table = new JTable());
		panel.add(pane);
		return panel;
	}
	private JPanel getNorthPanel(){
		// ID, PASSWORD 와 Connect, Close, Excute, Exit 버튼을 넣을 North Panel
		JPanel panel = new JPanel();
		panel.setLayout(new GridLayout(2,4));
		panel.add(new JLabel("ID : ", JLabel.CENTER));
		panel.add(tfID = new JTextField());
		panel.add(new JLabel("PASSWORD : ", JLabel.CENTER));
		panel.add(tfPwd = new JPasswordField());
		panel.add(btnConnect = new JButton("Connect"));
		btnConnect.addActionListener(this);
		panel.add(btnClose = new JButton("Close"));
		btnClose.addActionListener(this);
		panel.add(btnExecute = new JButton("Execute"));
		btnExecute.addActionListener(this);
		panel.add(btnExit = new JButton("Exit"));
		btnExit.addActionListener(this);
		return panel;
	}
	// Button 을 클릭 했을 경우 발생하는 Event Method
	public void actionPerformed(ActionEvent evt){
		if(evt.getSource() == btnExit){
			System.exit(1);
		}else if(evt.getSource() == btnConnect){
			try{
				dbConnect();
				JOptionPane.showMessageDialog(f,
						"DB Connection Success", 
						"Information", 
						JOptionPane.INFORMATION_MESSAGE);
			}catch(Exception ex){
				JOptionPane.showMessageDialog(f,
						ex.getMessage(), "Exception", 
						JOptionPane.ERROR_MESSAGE);
				tfID.setText("");  tfPwd.setText("");
				//tfID.setNextFocusableComponent(aComponent)
			}
		}else if(evt.getSource() == btnClose){
			try{
				dbClose();
				JOptionPane.showMessageDialog(f,
						"Good Bye", 
						"Information", 
						JOptionPane.INFORMATION_MESSAGE);
			}catch(SQLException ex){
				JOptionPane.showMessageDialog(f,
						ex.getMessage(), "Exception", 
						JOptionPane.ERROR_MESSAGE);
			}
			//Excute Button or tfSql 에서 Event 발생 했을 경우 실행
		}else if(evt.getSource() == btnExecute ||
				evt.getSource() == tfSql){
			try{
				execute();
			}catch(SQLException ex){
				JOptionPane.showMessageDialog(f,
						ex.getMessage(), "Exception", 
						JOptionPane.ERROR_MESSAGE);
			}
		}
	}
	// Excute 버튼을 클릭했을 경우 실행될 Method
	private void execute() throws SQLException {
		Statement stmt = this.conn.createStatement();
		String sql = tfSql.getText().trim();
		// 사용자가 SQL 마지막에 ; 넣었을 경우
		if(sql.lastIndexOf(";") > 0) 
			sql = sql.substring(0, sql.length() - 1);
		java.util.StringTokenizer st = 
			new java.util.StringTokenizer(sql);
		// 사용자가 SELECT 문을 실행 했을 경우
		if(st.nextToken().toUpperCase().equals("SELECT")){
			ResultSet rs = stmt.executeQuery(sql);
			// SELECT 문 일경우 TABLE 정보 출력하기 위해
			// MyTableModel Class 생성
			TableModel tm = new MyTableModel(rs);
			this.table.setModel(tm);
		}else{
			// SELECT 외 실행 했을 경우
			//UPDATE, INSERT, DELETE, DML, DCL
			int rowcount = stmt.executeUpdate(sql);
			JOptionPane.showMessageDialog(f, 
					rowcount + "행이 잘 반영됐습니다.");
		}
	}
	// Close 버튼을 클릭 했을 경우 SQL 닫기 Method
	private void dbClose() throws SQLException {
		if(this.conn != null) conn.close();
	}
	// DB접속 & 접속 했을 경우 노출될 문구
	private void dbConnect() throws Exception {
		String id = this.tfID.getText().trim();
		String pwd = this.tfPwd.getText().trim();
		String url = "jdbc:oracle:thin:@localhost:1521/ORCL";
		this.conn = DriverManager.getConnection(url, id, pwd);
		java.net.InetAddress ia = 
			java.net.InetAddress.getLocalHost();
		f.setTitle(id + "@" + ia.getHostName() + ":1521/ORCL");
	}
	// 최초로 실행될 Main 으로 Swing 실행
	public static void main(String[] args) {
		try{
			new JDBCSwing().go();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
}

< MyTableModel.java >

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

import javax.swing.table.DefaultTableModel;

public class MyTableModel extends DefaultTableModel {
	private ResultSet rs;
	public MyTableModel(ResultSet rs) throws SQLException{
		this.rs = rs;
		go();
	}
	private void go() throws SQLException{
		// ResultSetMetaData 선언
		ResultSetMetaData rsmd = this.rs.getMetaData();
		// 해당 컬럼 갯수
		int count = rsmd.getColumnCount();
		// 컬럼명을 삽입할 Header Vector
		Vector<string> header = new Vector<string>(1,1);
		for(int i = 1 ; i <= count ; i++){
			header.addElement(rsmd.getColumnLabel(i));
		}
		Vector<object> dataVector = new Vector<object>(1,1);
		while(rs.next()){
			Vector<object> rowVector = new Vector<object>(1,1);
			// 해당 컬럼의 데이터를 타입별로 가져와서 rowVector 에 삽입 (열로 삽입)
			for(int i = 1 ;i <= count ; i++){
				switch(rsmd.getColumnType(i)){
				case Types.INTEGER :
				case Types.NUMERIC :
					rowVector.addElement(rs.getInt(i)); break;
				case Types.FLOAT :
					rowVector.addElement(rs.getFloat(i)); break;
				case Types.DOUBLE :
					rowVector.addElement(rs.getDouble(i)); break;
				case Types.DATE :
					rowVector.addElement(rs.getDate(i)); break;
				case Types.CHAR :
				default :
					rowVector.addElement(rs.getString(i)); break;
				}//switch end
			}//for end
			// 열로 들어가 있는 rowVecter 데이터를 DataVecotr에 행으로 삽입
			dataVector.addElement(rowVector);
		}
		// 모두 저장된 dataVector 와 header 통합
		this.setDataVector(dataVector, header);
	}
}

< DBBean.java >

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Properties;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.File;

public class DBBean {
	private Connection conn;
	private Properties info;
	private Statement stmt;
	private ResultSet rs;
	//Constructor
	public DBBean(String rdbms) throws IOException{
		File file = null;
		switch(rdbms.toUpperCase().charAt(0)){
			case 'O':    //Oracle
				file = new File("D:\\temp\\dbinfo.properties"); break;
			case 'M':    //MySQL
				file = new File("D:\\temp\\mysqlinfo.properties"); break;
			default :
		}
		info = new Properties();
		info.load(new FileInputStream(file));
	}
	public void loadDriver() throws ClassNotFoundException {
		Class.forName(this.info.getProperty("DBDRIVER"));
	}
	public void setConnection() throws SQLException {
		this.conn = DriverManager.getConnection(this.info.getProperty("DBURL"),
				                                     this.info.getProperty("DBID"),
				                                     this.info.getProperty("DBPWD"));
	}
	public Connection getConnection() throws SQLException {
		return this.conn;
	}
	public void setStatement() throws SQLException {
		this.stmt = conn.createStatement();
	}
	public Statement getStatement() throws SQLException{
		return this.stmt;
	}
	public ResultSet executeSelect(String sql) throws SQLException {
		//SELECT
		//System.out.println("DBBean sql = " + sql);
		rs = this.stmt.executeQuery(sql);
		return this.rs;
	}
	public int executeNonSelect(String sql) throws SQLException {
		int rowcount = 0;
		//UPDATE, DELETE, INSERT, DDL, DCL
		rowcount = this.stmt.executeUpdate(sql);
		return rowcount;
	}
	public void connClose() throws SQLException{
		if(this.conn != null) this.conn.close();
	}
	public void stmtClose() throws SQLException {
		if(this.stmt != null) this.stmt.close();
	}
	public void rsClose() throws SQLException {
		if(this.rs != null) this.rs.close();
	}
}