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

 

3장에서는 category 테이블을 추가로 생성해 주고 테이블 join을 통하여 category 테이블에서 categoryName을 받아 온 다음 검색 기능을 마무리하겠습니다.

 

MySQL에서 category 테이블을 생성해 줍니다. 칼럼은 category와 categoryName입니다.

그리고 임의로 데이터를 넣어줍니다.

 

BookVO 소스코드 수정본입니다.

package book.vo;

public class BookVO {
	private int isbn;
	private String name;
	private String publish;
	private String author;
	private int price;
	//Modify int category > string categoryName
	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;
	}
	
	//Modify
	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 + "]";
	}
	
	
}

기존의 int category 부분을 String categoryName으로 변경해 주었습니다.

 

BookDAO 소스코드 수정본입니다.

Modify는 전 코드에서 수정한 부분이며, add 부분은 추가된 소스코드입니다.

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 {

	// Modify1 public ArrayList<BookVO> bookVOList = new ArrayList<BookVO>();
	
	//Modify1 add
	ArrayList<BookVO> bookVOList;
	//ADD
	String[] searchColName = {"name", "publish", "author"};
	
	// Modify public ArrayList<BookVO> select(Connection con, String searchWord){
	
	public ArrayList<BookVO> select(Connection con, String searchWord, int comboSearchIndex){
		
		//Modify1 add
		bookVOList = new ArrayList<BookVO>();
		
		try {
			// Modify String sql = "select * from book where name like ?";
			
			String sql = "select isbn, name, publish, author, price, categoryName from book, category where book.category=category.category and "+searchColName[comboSearchIndex]+" like ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%"+searchWord+"%");
			ResultSet 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();
		}
		return bookVOList;
	}
}

 

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;
	
	//add 29~30
	String[] comboStr = {"도서명", "출판사명", "저자명"};
	JComboBox<String> comboSearch;
	
	public BookSearchView() {
		setLayout(new BorderLayout(10, 10));
		//add 35
		comboSearch = new JComboBox<String>(comboStr);
		JLabel lbl = new JLabel("검색어: ");
		tf = new JTextField(20);
		btnSearch = new JButton("검색");
		panN = new JPanel();
		//add 41
		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);
		
		//add 54~57
		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() {
		//add 66
		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;
	}
	
	//add 74~ 80
	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.sql.Connection;
import java.util.ArrayList;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JTabbedPane;

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

public class BookController extends JFrame {
	//add
	Connection con;
	BookDAO dao;
	BookSearchView searchPan;
	ArrayList<BookVO> bookVOList;
	JComboBox<String> comboSearch;
	
	
	public BookController() {
		//DB 연결
		//ADD 18
		JTabbedPane tab = new JTabbedPane();
		con = JDBC_Connector.getCon();
		dao = new BookDAO();
		
		searchPan = new BookSearchView();
		bookVOList = dao.select(con, searchPan.getSearchWord(), 0);
		
		searchPan.setBookVOList(bookVOList);
		searchPan.initView();
		//add
		JButton btnSearch = searchPan.getBtnSearch();
		comboSearch = searchPan.getComboSearch();
		btnSearch.addActionListener(btnL);
		
		tab.add("도서검색", searchPan);
		
		searchPan.putSearchResult();
		
		// delete add(searchPan);
		//add 48
		add(tab);
		
		setTitle("도서관리시스템");
		setDefaultCloseOperation(EXIT_ON_CLOSE);
		setBounds(300, 500, 600, 500);
		setVisible(true);
	}
	
	//add actionListener
	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();
		}
	};

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

}

각종 객체들을 전역변수로 선언 후 actionlistener을 추가하였습니다.

 

 

검색어 "1"을 입력하게 되면 검색 버튼 클릭은 출력되지만, 검색된 데이터는 없는걸 확인할 수 있습니다.

 

검색이 정상적으로 되며 분류명 역시 제대로 동작합니다.