[Java / MySQL] 도서 관리 시스템 04 (MVC패턴)(마무리)

 

4장에서는 book.view 패키지에 데이터를 삽입 할 수 있는 BookInsertView와 데이터를 수정 할 수 있는 BookUpdateView를 만들어 주고 BookControoler 소스코드를 추가하겠습니다. 차례로 모든 소스 코드를 합치면 해당 도서 관리 프로그램을 사용할 수 있습니다. 참고 바랍니다.

 

데이터를 삽입 할 수 있는 BookInsertView 소스 코드입니다.

package book.view;

import java.awt.BorderLayout;
import java.awt.GridLayout;
import java.util.ArrayList;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import book.vo.BookVO;

public class BookInsertView extends JPanel {
	ArrayList<BookVO> bookVOList;
	JTable table;
	DefaultTableModel model;
	String searchWord;
	JPanel panS = new JPanel(new GridLayout(4, 4));
	String[] hearder = {"도서번호", "도서명", "저자명", "출판사", "가격", "분류명"};
	String[] comboStr = {"IT도서", "소설", "비소설", "경제", "사회"};
	JLabel[] lbls = new JLabel[hearder.length];
	JTextField[] tf = new JTextField[lbls.length-1];
	JComboBox<String> comboCategory = new JComboBox<String>(comboStr);
	JButton btnAdd = new JButton("도서추가");
	
	public BookInsertView() {
		setLayout(new BorderLayout(10, 10));
		
		for (int i = 0; i < hearder.length; i++) {
			lbls[i] = new JLabel(hearder[i]);
			panS.add(lbls[i]);
			if(i < hearder.length -1) {
				tf[i] = new JTextField();
				panS.add(tf[i]);
			}else {
				panS.add(comboCategory);
			}
		}
		for (int i = 0; i < 3; i++) {
			panS.add(new JLabel(""));
		}
		panS.add(btnAdd);
	}
	
	public void initView() {
		
		model = new DefaultTableModel(hearder, bookVOList.size());
		
		putSearchResult();
		
		table = new JTable(model);
		table.getColumnModel().getColumn(0).setPreferredWidth(50);
		table.getColumnModel().getColumn(1).setPreferredWidth(200);
		table.getColumnModel().getColumn(4).setPreferredWidth(50);
		table.getColumnModel().getColumn(5).setPreferredWidth(50);
		
		JScrollPane scroll = new JScrollPane(table);
		add("Center", scroll);
		add("South", panS);
	}
	
	public void putSearchResult() {
		model.setRowCount(bookVOList.size());
		BookVO vo = null;
		for (int i = 0; i < bookVOList.size(); i++) {
			vo = bookVOList.get(i);
			model.setValueAt(vo.getIsbn(), i, 0);
			model.setValueAt(vo.getName(), i, 1);
			model.setValueAt(vo.getAuthor(), i, 2);
			model.setValueAt(vo.getPublish(), i, 3);
			model.setValueAt(vo.getPrice(), i, 4);
			model.setValueAt(vo.getCategoryName(), i, 5);
		}
	}
	
	public void setBookVOList(ArrayList<BookVO> bookVOList) {
		this.bookVOList = bookVOList;
	}
	
	public JButton getBtnAdd() {
		return btnAdd;
	}
	
	public BookVO neededInsertDate() {
		BookVO vo = new BookVO();
		
		vo.setIsbn(Integer.parseInt(tf[0].getText()));
		vo.setName(tf[1].getText());
		vo.setAuthor(tf[2].getText());
		vo.setPublish(tf[3].getText());
		vo.setPrice(Integer.parseInt(tf[4].getText()));
		vo.setCategoryName((String)comboCategory.getSelectedItem());
		
		return vo;
	}
	
	public void initDate() {
		for (int i = 0; i < tf.length; i++) {
			tf[i].setText("");
		}
		comboCategory.setSelectedIndex(0);
	}

}

 

데이터를 업데이트 할 수 있는 BookUpdateView 소스 코드 입니다.

package book.view;

import java.awt.BorderLayout;
import java.awt.GridLayout;
import java.util.ArrayList;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import book.vo.BookVO;

public class BookUpdateView extends JPanel {
	ArrayList<BookVO> bookVOList;
	JTable table;
	DefaultTableModel model;
	String searchWord;
	JPanel panS = new JPanel(new GridLayout(4, 4));
	String[] hearder = {"도서번호", "도서명", "저자명", "출판사", "가격", "분류명"};
	String[] comboStr = {"IT도서", "소설", "비소설", "경제", "사회"};
	JLabel[] lbls = new JLabel[hearder.length];
	JTextField[] tf = new JTextField[lbls.length-1];
	JComboBox<String> comboCategory = new JComboBox<String>(comboStr);
	JButton btnUpdate = new JButton("도서수정");
	
	public BookUpdateView() {
		setLayout(new BorderLayout(10, 10));
		
		for (int i = 0; i < hearder.length; i++) {
			lbls[i] = new JLabel(hearder[i]);
			panS.add(lbls[i]);
			if(i < hearder.length - 1) {
				tf[i] = new JTextField();
				panS.add(tf[i]);
			}else {
				panS.add(comboCategory);
			}
		}
		tf[0].setEditable(false);
		
		for (int i = 0; i < 3; i++) {
			panS.add(comboCategory);
		}
		panS.add(btnUpdate);
	}

	public void setTextField(int row) {
		for (int i = 0; i < tf.length; i++) {
			tf[i].setText(model.getValueAt(row, i)+"");
		}
		comboCategory.setSelectedItem(model.getValueAt(row, 5));
	}
	
	public void initView() {
		model = new DefaultTableModel(hearder, bookVOList.size());
		putSearchResult();
		
		table = new JTable(model);
		table.getColumnModel().getColumn(0).setPreferredWidth(50);
		table.getColumnModel().getColumn(1).setPreferredWidth(200);
		table.getColumnModel().getColumn(4).setPreferredWidth(50);
		table.getColumnModel().getColumn(5).setPreferredWidth(50);
		
		JScrollPane scroll = new JScrollPane(table);
		add("Center", scroll);
		add("South", panS);
	}
	
	public void putSearchResult() {
		model.setRowCount(bookVOList.size());
		BookVO vo = null;
		for (int i = 0; i < bookVOList.size(); i++) {
			vo = bookVOList.get(i);
			model.setValueAt(vo.getIsbn(), i, 0);
			model.setValueAt(vo.getName(), i, 1);
			model.setValueAt(vo.getAuthor(), i, 2);
			model.setValueAt(vo.getPublish(), i, 3);
			model.setValueAt(vo.getPrice(), i, 4);
			model.setValueAt(vo.getCategoryName(), i, 5);
		}
	}
	
	public void setBookVOList(ArrayList<BookVO> bookVOList) {
		this.bookVOList = bookVOList;
	}
	
	public JButton getBtnUpdate() {
		return btnUpdate;
	}
	
	public BookVO neededUpdateDate() {
		BookVO vo = new BookVO();
		
		vo.setIsbn(Integer.parseInt(tf[0].getText()));
		vo.setName(tf[1].getText());
		vo.setAuthor(tf[2].getText());
		vo.setPublish(tf[3].getText());
		vo.setPrice(Integer.parseInt(tf[4].getText()));
		vo.setCategoryName((String)comboCategory.getSelectedItem());
		return vo;
	}
	
	public void initDate() {
		for (int i = 0; i < tf.length; i++) {
			tf[i].setText("");
		}
		comboCategory.setSelectedIndex(0);
	}
	
	public JTable getTable() {
		return table;
	}
	
}

 

데이터를 검색 할 수 있는 BookSearchView 소스 코드 입니다.

package book.view;

import java.awt.BorderLayout;
import java.util.ArrayList;
import java.util.Iterator;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import book.vo.BookVO;

public class BookSearchView extends JPanel {

	JTextField tf;
	JButton btnSearch;
	ArrayList<BookVO> bookVOList;
	JPanel panN;
	JTable table;
	DefaultTableModel model;
	String searchWord;
	String[] comboStr = {"도서명", "출판사명", "저자명"};
	JComboBox<String> comboSearch;
	
	public BookSearchView() {
		setLayout(new BorderLayout(10, 10));
		comboSearch = new JComboBox<String>(comboStr);
		JLabel lbl = new JLabel("검색어: ");
		tf = new JTextField(20);
		btnSearch = new JButton("검색");
		panN = new JPanel();
		panN.add(comboSearch);
		panN.add(lbl);
		panN.add(tf);
		panN.add(btnSearch);
	}
	
	public void initView() {
		String[] hearder = {"도서번호", "도서명", "저자명", "출판사", "가격", "분류명"};
		model = new DefaultTableModel(hearder, bookVOList.size());
		putSearchResult();
		table = new JTable(model);
		
		table.getColumnModel().getColumn(0).setPreferredWidth(50);
		table.getColumnModel().getColumn(1).setPreferredWidth(200);
		table.getColumnModel().getColumn(4).setPreferredWidth(50);
		table.getColumnModel().getColumn(5).setPreferredWidth(50);
		
		JScrollPane scroll = new JScrollPane(table);
		add("North", panN);
		add("Center", scroll);
	}
	
	public void putSearchResult() {
		model.setRowCount(bookVOList.size());
		BookVO vo = null;
		for (int i = 0; i < bookVOList.size(); i++) {
			vo = bookVOList.get(i);
			model.setValueAt(vo.getIsbn(), i, 0);
			model.setValueAt(vo.getName(), i, 1);
			model.setValueAt(vo.getAuthor(), i, 2);
			model.setValueAt(vo.getPublish(), i, 3);
			model.setValueAt(vo.getPrice(), i, 4);
			model.setValueAt(vo.getCategoryName(), i, 5);
		}
	}
	public void setBookVOList(ArrayList<BookVO> bookVOList) {
		this.bookVOList = bookVOList;
	}
	public String getSearchWord() {
		this.searchWord = tf.getText();
		return searchWord;
	}
	
	public JButton getBtnSearch() {
		return btnSearch;
	}

	public JComboBox<String> getComboSearch() {
		return comboSearch;
	}
}

 

BookController 소스 코드 입니다.

package book.controller;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.sql.Connection;
import java.util.ArrayList;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTabbedPane;
import javax.swing.JTable;

import book.dao.BookDAO;
import book.dao.JDBC_Connector;
import book.view.BookInsertView;
import book.view.BookSearchView;
import book.view.BookUpdateView;
import book.vo.BookVO;

public class BookController extends JFrame {
	Connection con;
	BookDAO dao;
	BookSearchView searchPan;
	ArrayList<BookVO> bookVOList;
	JComboBox<String> comboSearch;
	BookInsertView insertPan;
	BookUpdateView updatePan;
	JTable table;
	static final int YES = 0;
	
	
	public BookController() {
		JTabbedPane tab = new JTabbedPane();
		con = JDBC_Connector.getCon();
		dao = new BookDAO();
		
		// 도서 검색
		
		searchPan = new BookSearchView();
		bookVOList = dao.select(searchPan.getSearchWord(), 0);
		searchPan.setBookVOList(bookVOList);
		searchPan.initView();
		JButton btnSearch = searchPan.getBtnSearch();
		comboSearch = searchPan.getComboSearch();
		btnSearch.addActionListener(btnL);
		
		// 도서 추가
		insertPan = new BookInsertView();
		bookVOList = dao.select("", 0);
		insertPan.setBookVOList(bookVOList);
		insertPan.initView();
		JButton btnAdd = insertPan.getBtnAdd();
		btnAdd.addActionListener(btnAddL);
		
		// 도서 수정
		updatePan = new BookUpdateView();
		bookVOList = dao.select("", 0);
		updatePan.setBookVOList(bookVOList);
		updatePan.initView();
		table = updatePan.getTable();
		table.addMouseListener(tableL);
		JButton btnUpdate = updatePan.getBtnUpdate();
		btnUpdate.addActionListener(btnUpdateL);
		
		tab.add("도서검색", searchPan);
		tab.add("도서추가", insertPan);
		tab.add("도서수정 및 삭제", updatePan);
		
		// searchPan.putSearchResult();
		
		add(tab);
		
		setTitle("도서관리시스템");
		setDefaultCloseOperation(EXIT_ON_CLOSE);
		setBounds(300, 500, 600, 500);
		setVisible(true);
	}
	
	ActionListener btnL = new ActionListener() {
		
		@Override
		public void actionPerformed(ActionEvent e) {
			// TODO Auto-generated method stub
			System.out.println("검색버튼 클릭");
			bookVOList.clear();
			bookVOList = dao.select(con, searchPan.getSearchWord(), comboSearch.getSelectedIndex());
			searchPan.setBookVOList(bookVOList);
			searchPan.putSearchResult();
		}
	};
	
	ActionListener btnAddL = new ActionListener() {
		
		@Override
		public void actionPerformed(ActionEvent e) {
			// TODO Auto-generated method stub
		BookVO vo = insertPan.neededInsertDate();
		dao.insert(vo);
		bookVOList = dao.select("", 0);
		insertPan.setBookVOList(bookVOList);
		insertPan.putSearchResult();
		insertPan.initDate();
		}
	};
	
	ActionListener btnUpdateL = new ActionListener() {
		
		@Override
		public void actionPerformed(ActionEvent e) {
			// TODO Auto-generated method stub
			BookVO vo = updatePan.neededUpdateDate();
			dao.update(vo);
			bookVOList = dao.select("", 0);
			updatePan.setBookVOList(bookVOList);
			updatePan.putSearchResult();
		}
	};
	
	MouseAdapter tableL = new MouseAdapter() {
		public void mouseClicked(java.awt.event.MouseEvent e) {
			if(e.getClickCount() == 1) {
				int row = table.getSelectedRow();
				updatePan.setTextField(row);
			}
			if(e.getClickCount() == 2) {
				int result = JOptionPane.showConfirmDialog(BookController.this, "정말로 삭제 하시겠습니까?", "삭제여부", JOptionPane.WARNING_MESSAGE);
				if(result == YES) {
					BookVO vo = updatePan.neededUpdateDate();
					dao.delete(vo);
					bookVOList = dao.select("", 0);
					updatePan.setBookVOList(bookVOList);
					updatePan.putSearchResult();
				}
			}
		}
	};
	
	static final int SEARCH_PAN = 0;
	static final int INSERT_PAN = 1;
	static final int UPDATE_PAN = 2;
	
	public void updateView(JPanel pan, int panType) {
		bookVOList = dao.select("", 0);
		
		switch (panType) {
		case SEARCH_PAN:
			break;
		case INSERT_PAN:
			break;
		case UPDATE_PAN:
			((BookUpdateView)pan).setBookVOList(bookVOList);
			((BookUpdateView)pan).putSearchResult();
			break;
		}
		bookVOList = dao.select("", 0);
		pan.setBookVOList(bookVOList);
		updatePan.putSearchResult();
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		new BookController();
	}

}

 

BookDAO 소스 코드 입니다.

package book.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import book.vo.BookVO;

public class BookDAO {

	ArrayList<BookVO> bookVOList;
	String[] searchColName = {"name", "publish", "author"};
	
	public ArrayList<BookVO> select(String searchWord, int comboSearchIndex){
		
		bookVOList = new ArrayList<BookVO>();
		Connection con = JDBC_Connector.getCon();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			String sql = "select isbn, name, publish, author, price, categoryName from book, category where book.category=category.category and "+searchColName[comboSearchIndex]+" like ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%"+searchWord+"%");
			rs = pstmt.executeQuery();
			while(rs.next()) {
				BookVO vo = new BookVO();
				vo.setIsbn(rs.getInt("isbn"));
				vo.setName(rs.getString("name"));
				vo.setPublish(rs.getString("publish"));
				vo.setAuthor(rs.getString("author"));
				vo.setPrice(rs.getInt("price"));
				vo.setCategoryName(rs.getString("categoryName"));
				bookVOList.add(vo);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//DB 과부화 방지를 위해 DB 종료
		finally {
			try {
				if(rs != null)
					rs.close();
				
				if(pstmt != null)
					rs.close();
				
				if(con != null)
					rs.close();
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
		return bookVOList;
	}
	
	public void insert(BookVO vo) {
		Connection con = JDBC_Connector.getCon();
		PreparedStatement pstmt = null;
		String sql = "insert into book values(?,?,?,?,?,?);";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, vo.getIsbn());
			pstmt.setString(2, vo.getName());
			pstmt.setString(3, vo.getPublish());
			pstmt.setString(4, vo.getAuthor());
			pstmt.setInt(5, vo.getPrice());
			int categoryId = 0;
			switch (vo.getCategoryName()){
			case "IT 도서":
				categoryId = 1;
				break;
			case "소설":
				categoryId = 2;
				break;
			case "비소설":
				categoryId = 3;
				break;
			case "경제":
				categoryId = 4;
				break;
			case "사회":
				categoryId = 5;
				break;
			}
			
			pstmt.setInt(6, categoryId);
			
			pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			try {
				if(pstmt != null)
					pstmt.close();
				
				if(con != null)
					con.close();
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
	}
	
	public void update(BookVO vo) {
		Connection con = JDBC_Connector.getCon();
		PreparedStatement pstmt = null;
		String sql = "update book set name=?, publish=?, author=?, price=?, category=?, where isbn =?;";
		try {
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, vo.getName());
			pstmt.setString(2, vo.getPublish());
			pstmt.setString(3, vo.getAuthor());
			pstmt.setInt(4, vo.getPrice());
			int categoryId = 0;
			switch (vo.getCategoryName()) {
			case "IT도서":
				categoryId = 10;
				break;
			case "소설":
				categoryId = 20;
				break;
			case "비소설":
				categoryId = 30;
				break;
			case "경제":
				categoryId = 40;
				break;
			case "사회":
				categoryId = 50;
				break;
			}
			
			pstmt.setInt(5, categoryId);
			pstmt.setInt(6, vo.getIsbn());
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null)
					pstmt.close();
				
				if(con != null)
					con.close();
			} catch (Exception e2) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
	}
	
	public void delete(BookVO vo) {
		Connection con = JDBC_Connector.getCon();
		PreparedStatement pstmt = null;
		
		String sql = "delete from book where isbn = ?;";
		try {
			pstmt = con.prepareStatement(sql);
			
			pstmt.setInt(1, vo.getIsbn());
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(pstmt != null)
					pstmt.close();
				
				if(con != null)
					con.close();
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
	}
}

 

JDBC_Connector 소스 코드 입니다.

package book.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC_Connector {
	
	private static final String DRIVER_PATH = "com.mysql.cj.jdbc.Driver";
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/book_db?serverTimezone=UTC&useUnicode=yes&characterEncoding=UTF-8";
	private static final String ID = "root";
	private static final String PWD = "1234";
	private static Connection con;
	
	public static Connection getCon() {
		try {
			Class.forName(DRIVER_PATH);
			System.out.println("정상적으로 JDBC Driver가 로드되었습니다.");
			con = DriverManager.getConnection(URL, ID, PWD);
			System.out.println("정상 연결이 되었습니다.");
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		return con;
	}
	
	public static void resultSetTest() {
		Statement stmt;
		try {
			stmt = con.createStatement();
			String sql = "select * from book";
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				System.out.print(rs.getString("name")+" ");
				System.out.print(rs.getString("author")+" ");
				System.out.println(rs.getString("price")+" ");
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public static void main(String[] args) {
		getCon();
		resultSetTest();
	}

}

 

BookVO 소스 코드 입니다.

package book.vo;

public class BookVO {
	private int isbn;
	private String name;
	private String publish;
	private String author;
	private int price;
	private String categoryName;
	
	public int getIsbn() {
		return isbn;
	}
	public void setIsbn(int isbn) {
		this.isbn = isbn;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPublish() {
		return publish;
	}
	public void setPublish(String publish) {
		this.publish = publish;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	
	public String getCategoryName() {
		return categoryName;
	}
	public void setCategoryName(String categoryName) {
		this.categoryName = categoryName;
	}
	
	@Override
	public String toString() {
		return "BookVO [isbn=" + isbn + ", name=" + name + ", publish=" + publish + ", author=" + author + ", price="
				+ price + ", categoryName=" + categoryName + "]";
	}
	
	
}

 

도서 관리 시스템 실행 화면 입니다.

 

파일 자료 구조 입니다.