[Project] #5 상품 조회 기능 구현

- 자바 스윙을 이용한 중고 상품 거래 시스템 만들기 -

 

 

Domain

SaleInfo.java

판매중인 상품의 정보를 담은 도메인 (Sales 테이블과 Products 테이블 조인문으로 판매중인 상품 검색한 데이터를 담기 위한 객체이다.)

package domain;

public class SaleInfo {
	
	/*
	 * Field
	 */
	private int saleId;
	private int sellerId;
	private String identification;
	private String saleStatus;
	private String productName;
	private int productQuantity;
	private int productPrice;
	private String productDescription;
	private String address;
	private int product_id;
	private int emdCd;
	private String productImage;
	
	
	/*
	 * Method
	 */
	public int getSaleId() {
		return saleId;
	}
	public void setSaleId(int saleId) {
		this.saleId = saleId;
	}
	public int getSellerId() {
		return sellerId;
	}
	public void setSellerId(int sellerId) {
		this.sellerId = sellerId;
	}
	public String getIdentification() {
		return identification;
	}
	public void setIdentification(String identification) {
		this.identification = identification;
	}
	public String getSaleStatus() {
		return saleStatus;
	}
	public void setSaleStatus(String saleStatus) {
		this.saleStatus = saleStatus;
	}
	public String getProductName() {
		return productName;
	}
	public void setProductName(String productName) {
		this.productName = productName;
	}
	public int getProductQuantity() {
		return productQuantity;
	}
	public void setProductQuantity(int productQuantity) {
		this.productQuantity = productQuantity;
	}
	public int getProductPrice() {
		return productPrice;
	}
	public void setProductPrice(int productPrice) {
		this.productPrice = productPrice;
	}
	public String getProductDescription() {
		return productDescription;
	}
	public void setProductDescription(String productDescription) {
		this.productDescription = productDescription;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public int getProduct_id() {
		return product_id;
	}
	public void setProduct_id(int product_id) {
		this.product_id = product_id;
	}
	public int getEmdCd() {
		return emdCd;
	}
	public void setEmdCd(int emdCd) {
		this.emdCd = emdCd;
	}
	public String getProductImage() {
		return productImage;
	}
	public void setProductImage(String productImage) {
		this.productImage = productImage;
	}
	@Override
	public String toString() {
		return "상품이름\t:\t" + productName +
				"\n\n상품가격\t:\t" + productPrice + 
				"\n\n상품수량\t:\t" + productQuantity +  
				"\n\n판매자\t:\t" + identification + 
				"\n\n판매상태\t:\t" + saleStatus +
				"\n\n판매지역\t:\t" + address +
				"\n\n상품설명\t:\t" + productDescription;
	}
}

 

Repository

JdbcSaleRepository.java

package repository.jdbc;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import domain.Sale;
import domain.SaleInfo;
import repository.inerface.SaleRepository;

public class JdbcSaleRepository extends DAO implements SaleRepository{
	
	/*
	 * Field
	 */
	// 싱글톤
	private static SaleRepository saleRepository = null;
		

	/*
	 * Constructor
	 */
	// 싱글톤
	private JdbcSaleRepository() {}

	
	/*
	 * Method
	 */
	// 싱글톤 -> 메서드로 인스턴스 생성
	public static SaleRepository getSaleRepository() {
		if (saleRepository == null) {
			saleRepository = new JdbcSaleRepository();
		}
		return saleRepository;
	}

	@Override
	public void insert(Sale sale) {
		try {
			connect();

			String sql = "INSERT INTO sales VALUES (sales_seq.nextval ,?, ?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, sale.getSellerId());
			ps.setInt(2, sale.getProductId());
			ps.setString(3, sale.getSaleStatus());

			int result = ps.executeUpdate();

			if (result > 0) {
				System.out.println(result + "행 삽입에 성공했습니다.");
			} else {
				System.out.println("삽입에 실패했습니다.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			disconnect();
		}
	}

	@Override
	public void update(Sale sale) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void delete(int saleId) {
		try {
			connect();

			String sql = "DELETE FROM sales WHERE sale_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, saleId);

			int result = ps.executeUpdate();

			if (result > 0) {
				System.out.println(result + "행 삭제에 성공했습니다.");
			} else {
				System.out.println("삭제에 실패했습니다.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			disconnect();
		}
		
	}

	@Override
	public Sale selectOne(int saleId) {
		Sale sale = null;

		try {
			connect();

			String sql = "SELECT * FROM sales WHERE sale_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, saleId);

			rs = ps.executeQuery();

			if (rs.next()) {
				sale = new Sale();
				sale.setSaleId(rs.getInt(1));
				sale.setSellerId(rs.getInt(2));
				sale.setProductId(rs.getInt(3));
				sale.setSaleStatus(rs.getString(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			disconnect();
		}
		return sale;
	}

	// 상품 전체 검색
	@Override
	public List<SaleInfo> selectAll(int loginMemberId) {
		List<SaleInfo> list = new ArrayList<SaleInfo>();

		try {
			connect();

			String sql = "SELECT s.sale_id, s.seller_id, m.identification, s.sale_status, p.name, p.quantity, p.price, p.description, m.address, s.product_id, p.image "
						+ "FROM sales s "
						+ "JOIN products p ON (s.product_id = p.product_id) "
						+ "JOIN members m ON (s.seller_id = m.member_id) "
						+ "WHERE p.quantity > 0 AND s.seller_id <> ? "
						+ "ORDER BY s.product_id desc";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, loginMemberId);

			rs = ps.executeQuery();

			while (rs.next()) {
				SaleInfo saleInfo = new SaleInfo();
				saleInfo.setSaleId(rs.getInt(1));
				saleInfo.setSellerId(rs.getInt(2));
				saleInfo.setIdentification(rs.getString(3));
				saleInfo.setSaleStatus(rs.getString(4));
				saleInfo.setProductName(rs.getString(5));
				saleInfo.setProductQuantity(rs.getInt(6));
				saleInfo.setProductPrice(rs.getInt(7));
				saleInfo.setProductDescription(rs.getString(8));
				saleInfo.setAddress(rs.getString(9));
				saleInfo.setProduct_id(rs.getInt(10));
				saleInfo.setProductImage(rs.getString(11));
				
				
				list.add(saleInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			disconnect();
		}
		return list;
	}
	
	// 상품 키워드로 검색
	@Override
	public List<SaleInfo> selectAllByKeyword(int loginMemberId, String keyword) {
		List<SaleInfo> list = new ArrayList<SaleInfo>();

		try {
			connect();

			String sql = "SELECT s.sale_id, s.seller_id, m.identification, s.sale_status, p.name, p.quantity, p.price, p.description, m.address, s.product_id, p.image  "
						+ "FROM sales s "
						+ "JOIN products p ON (s.product_id = p.product_id) "
						+ "JOIN members m ON (s.seller_id = m.member_id) "
						+ "WHERE p.quantity > 0 "
						+ "AND s.seller_id <> ? "
						+ "AND p.name like '%'||?||'%'"
						+ "ORDER BY s.product_id desc";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, loginMemberId);
			ps.setString(2, keyword);

			rs = ps.executeQuery();

			while (rs.next()) {
				SaleInfo saleInfo = new SaleInfo();
				
				saleInfo.setSaleId(rs.getInt(1));
				saleInfo.setSellerId(rs.getInt(2));
				saleInfo.setIdentification(rs.getString(3));
				saleInfo.setSaleStatus(rs.getString(4));
				saleInfo.setProductName(rs.getString(5));
				saleInfo.setProductQuantity(rs.getInt(6));
				saleInfo.setProductPrice(rs.getInt(7));
				saleInfo.setProductDescription(rs.getString(8));
				saleInfo.setAddress(rs.getString(9));
				saleInfo.setProduct_id(rs.getInt(10));
				saleInfo.setProductImage(rs.getString(11));
				
				
				list.add(saleInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			disconnect();
		}
		return list;
	}
	
	// 상품 주변지역으로 검색
	@Override
	public List<SaleInfo> selectAllByNeighbor(int loginMemberId, int prevEmdCd, int nextEmdCd) {
		List<SaleInfo> list = new ArrayList<SaleInfo>();

		try {
			connect();

			String sql = "SELECT s.sale_id, s.seller_id, m.identification, s.sale_status, p.name, p.quantity, p.price, p.description, m.address, s.product_id, o.emd_cd, p.image  "
						+ "FROM sales s "
						+ "JOIN products p ON (s.product_id = p.product_id) "
						+ "JOIN members m ON (s.seller_id = m.member_id) "
						+ "JOIN suseong_map o ON (m.address = o.emd_nn) "
						+ "WHERE p.quantity > 0 AND s.seller_id <> ? "
						+ "AND o.emd_cd BETWEEN ? AND ?"
						+ "ORDER BY s.product_id desc";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, loginMemberId);
			ps.setInt(2, prevEmdCd);
			ps.setInt(3, nextEmdCd);

			rs = ps.executeQuery();

			while (rs.next()) {
				SaleInfo saleInfo = new SaleInfo();
				saleInfo.setSaleId(rs.getInt(1));
				saleInfo.setSellerId(rs.getInt(2));
				saleInfo.setIdentification(rs.getString(3));
				saleInfo.setSaleStatus(rs.getString(4));
				saleInfo.setProductName(rs.getString(5));
				saleInfo.setProductQuantity(rs.getInt(6));
				saleInfo.setProductPrice(rs.getInt(7));
				saleInfo.setProductDescription(rs.getString(8));
				saleInfo.setAddress(rs.getString(9));
				saleInfo.setProduct_id(rs.getInt(10));
				saleInfo.setEmdCd(rs.getInt(11));
				saleInfo.setProductImage(rs.getString(12));
				
				
				list.add(saleInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			disconnect();
		}
		return list;
	}
}

전체조회, 키워드로 조회, 주변지역 조회에 필요한 쿼리는 각 조건에 맞춰 조인문으로 쿼리를 구성하였다.

 

Service

SaleService.java

package service;

import java.util.List;

import domain.Sale;
import domain.SaleInfo;
import repository.inerface.SaleRepository;

public class SaleService {

	/*
	 * Field
	 */
	private final SaleRepository saleRepository;

	/*
	 * Constructor
	 */
	public SaleService(SaleRepository saleRepository) {
		this.saleRepository = saleRepository;
	}

	/*
	 * Method
	 */
	// 판매 등록
	public void createSale(Sale sale) {
		saleRepository.insert(sale);
	}

	// 판매 수정 - 판매상태 수정?
	public void updateSale(Sale sale) {
		saleRepository.update(sale);
	}

	// 판매 삭제
	public void deleteSale(int saleId) {
		saleRepository.delete(saleId);
	}

	// 판매 단건조회
	public Sale findOneSale(int saleId) {
		return saleRepository.selectOne(saleId);
	}

	// 판매 전체조회
	public List<SaleInfo> findAllSales(int loginMemberId) {
		return saleRepository.selectAll(loginMemberId);
	}

	// 판매 키워드조회
	public List<SaleInfo> findAllSalesByKeyword(int loginMemberId, String keyword) {
		return saleRepository.selectAllByKeyword(loginMemberId, keyword);
	}

	// 판매 키워드조회
	public List<SaleInfo> findAllSalesByNeighbor(int loginMemberId, int prevEmdCd, int nextEmdCd) {
		return saleRepository.selectAllByNeighbor(loginMemberId, prevEmdCd, nextEmdCd);
	}

}

 

View - 상품 검색 (검색 카테고리 : 전체, 키워드, 주변지역)

ShopFrame.java

일부 코드

	/*
	 * 전체, 키워드, 주변지역 검색 처리
	 */
	// 전체 검색
	private RoundedButton drawShowcaseFrame1() {

		// 상품 전체 검색 버튼
		RoundedButton allSearchBtn = new RoundedButton("ALL");
		allSearchBtn.setBounds(430, 35, 70, 40);
		allSearchBtn.setBackground(Color.yellow);

		allSearchBtn.addMouseListener(new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {

				searchFrame = new JFrame("ALL SEARCH");
				searchFrame.setLayout(null);
				searchFrame.setBounds(200, 120, 665, 800);
				searchFrame.setVisible(true);

				JLabel label = new JLabel("# SHOWCASE #");
				label.setBounds(230, -65, 300, 200);
				label.setFont(new Font("Arial", Font.BOLD, 23));
				label.setForeground(Color.orange);
				searchFrame.add(label);

				// 판매중인 상품 전체 검색
				List<SaleInfo> saleList = saleService.findAllSales(HomeFrame.logInMember.getMemberId());

				// 패널에 상품 버튼 추가
				for (SaleInfo saleInfo : saleList) {
					RoundedButton drawSaleButton = drawProductButton(saleInfo.getProductName(), saleInfo, saleList.size());
					searchFrame.add(drawSaleButton);
				}

				// 상품 버튼 추가 후 초기값으로 리셋
				saleBtn_X = 15;
				saleBtn_Y = 70;

				// 상품 등록 버튼
				RoundedButton postButton = postSaleButton();
				searchFrame.add(postButton);

				// 뒤로가기 버튼
				RoundedButton backBtn = new RoundedButton("BACK");
				backBtn.setBounds(16, 20, 70, 40);
				backBtn.setBackground(Color.pink);
				searchFrame.add(backBtn);

				// 뒤로가기 버튼 클릭시 ALL SEARCH 창 끄기
				backBtn.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent e) {
						searchFrame.dispose();
					}
				});
			}
		});

		return allSearchBtn;
	}

	// 키워드로 검색
	private RoundedButton drawShowcaseFrame2() {

		// 상품 키워드로 검색 버튼
		RoundedButton keywordSearchBtn = new RoundedButton("KEYWORD");
		keywordSearchBtn.setBounds(530, 35, 70, 40);
		keywordSearchBtn.setBackground(Color.yellow);

		keywordSearchBtn.addMouseListener(new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {

				// 키워드 입력할 기본 입력창 팝업
				String keyword = null;
				keyword = JOptionPane.showInputDialog(frame, "Input keyword !");

				searchFrame = new JFrame("KEYWORD SEARCH");
				searchFrame.setLayout(null);
				searchFrame.setBounds(200, 120, 665, 800);
				searchFrame.setVisible(true);

				JLabel label = new JLabel("# SHOWCASE #");
				label.setBounds(230, -65, 300, 200);
				label.setFont(new Font("Arial", Font.BOLD, 23));
				label.setForeground(Color.orange);
				searchFrame.add(label);

				// 판매중인 상품 키워드로 검색
				List<SaleInfo> saleList = saleService.findAllSalesByKeyword(HomeFrame.logInMember.getMemberId(), keyword);

				// 패널에 상품 버튼 추가
				for (SaleInfo saleInfo : saleList) {
					RoundedButton drawSaleButton = drawProductButton(saleInfo.getProductName(), saleInfo, saleList.size());
					searchFrame.add(drawSaleButton);
				}

				// 상품 버튼 추가 후 초기값으로 리셋
				saleBtn_X = 15;
				saleBtn_Y = 70;

				// 상품 등록 버튼
				RoundedButton postButton = postSaleButton();
				searchFrame.add(postButton);

				// 뒤로가기 버튼
				RoundedButton backBtn = new RoundedButton("BACK");
				backBtn.setBounds(16, 20, 70, 40);
				backBtn.setBackground(Color.pink);
				searchFrame.add(backBtn);

				// 뒤로가기 버튼 클릭시 KEYWORD SEARCH 창 끄기
				backBtn.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent e) {
						searchFrame.dispose();
					}
				});

			}
		});

		return keywordSearchBtn;
	}

	// 주변지역검색
	private RoundedButton drawShowcaseFrame3() {

		// 상품 전체 검색 버튼
		RoundedButton neighborSearchBtn = new RoundedButton("NEIGHBOR");
		neighborSearchBtn.setBounds(630, 35, 70, 40);
		neighborSearchBtn.setBackground(Color.yellow);

		neighborSearchBtn.addMouseListener(new MouseAdapter() {
			public void mouseClicked(MouseEvent e) {

				searchFrame = new JFrame("NEIGHBOR SEARCH");
				searchFrame.setLayout(null);
				searchFrame.setBounds(200, 120, 665, 800);
				searchFrame.setVisible(true);

				JLabel label = new JLabel("# SHOWCASE #");
				label.setBounds(230, -65, 300, 200);
				label.setFont(new Font("Arial", Font.BOLD, 23));
				label.setForeground(Color.orange);
				searchFrame.add(label);

				//// 주변지역 검색
				// 현재 로그인된 유저의 지역 검색
				int loginMemberEmdCd = mapService.findOneByName(HomeFrame.logInMember.getAddress()).getEmdCd();
				// 주변지역 코드 및 동명 출력
				System.out.println(
						"loginMemberEmdCd = " + loginMemberEmdCd + "(" + HomeFrame.logInMember.getAddress() + ")");
				System.out.println("neighborEmdCd = " + (loginMemberEmdCd - 1) + "("
						+ mapService.findOneByCode(loginMemberEmdCd - 1).getEmdNn() + "), " + (loginMemberEmdCd + 1)
						+ "(" + mapService.findOneByCode(loginMemberEmdCd + 1).getEmdNn() + ") ");
				System.out.println();

				// 판매중인 상품 주변 지역으로 검색
				List<SaleInfo> saleList = saleService.findAllSalesByNeighbor(HomeFrame.logInMember.getMemberId(), loginMemberEmdCd - 1, loginMemberEmdCd + 1);

				// 패널에 상품 버튼 추가
				for (SaleInfo saleInfo : saleList) {
					RoundedButton drawSaleButton = drawProductButton(saleInfo.getProductName(), saleInfo, saleList.size());
					searchFrame.add(drawSaleButton);
				}

				// 상품 버튼 추가 후 초기값으로 리셋
				saleBtn_X = 15;
				saleBtn_Y = 70;

				// 상품 등록 버튼
				RoundedButton postButton = postSaleButton();
				searchFrame.add(postButton);

				// 뒤로가기
				// 뒤로가기 버튼
				RoundedButton backBtn = new RoundedButton("BACK");
				backBtn.setBounds(16, 20, 70, 40);
				backBtn.setBackground(Color.pink);
				searchFrame.add(backBtn);

				// 뒤로가기 버튼 클릭시 NEIGHBOR SEARCH 창 끄기
				backBtn.addMouseListener(new MouseAdapter() {
					public void mouseClicked(MouseEvent e) {
						searchFrame.dispose();
					}
				});
			}
		});

		return neighborSearchBtn;
	}

주변 지역 검색은 현재 로그인 유저의 지역의 양 옆에 있는 지역을 검색하도록 구현하였음

수성구 읍면동 코드

 

실행 화면

상품 조회 화면 구현

 

 

 

- Just Do It -