Develop/JAVA

0628 JDBC와 Swing을 이용한 테이블 구성

포페PostFace 2022. 6. 29. 17:08

SQL insert data

drop sequence prdseq;
create sequence prdseq
increment by 1
start with 1
minvalue 1
nocache;

drop table products;
create table products(
id number primary key,
name varchar2(30),  
stock number, 
price number,
category varchar2(30), 
inputdate date default sysdate
);

insert into products values(prdseq.nextval,'mp3',20,300,'IT', default); 
insert into products values(prdseq.nextval,'갤럭시S6',30,200,'IT', default);
insert into products values(prdseq.nextval,'iPhone',40,500,'IT', default);
insert into products values(prdseq.nextval,'세탁기',20,300,'KJ', default);
insert into products values(prdseq.nextval,'냉장고',30,200,'KJ', default);
insert into products values(prdseq.nextval,'TV',40,500,'KJ', default);
insert into products values(prdseq.nextval,'Computer',20,300,'IT', default);
insert into products values(prdseq.nextval,'iMac',30,200,'IT', default);
commit ;

 

Main

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.*;
import java.awt.event.*;

import java.util.ArrayList;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;



public class ProductMain extends JFrame implements ActionListener{

	private ProductDao pdao=null;
	private ArrayList<ProductBean> lists=null;
	private String[] columnNames = {"아이디","이름","입고수량","단가","카테고리","입고일자"};
	private Object[][] rowData;
	private JTable table; //테이블이 아닌 테이블 관리자
	private JScrollPane scrollPane=null;
	private JButton[] btn = new JButton[4]; //버튼이 아닌 버튼 관리자 4개

	private JTextField txtId = new JTextField(15); //입력창
	private JTextField txtName = new JTextField(15);
	private JTextField txtStock = new JTextField(15);
	private JTextField txtPrice = new JTextField(15);
	private JTextField txtCategory = new JTextField(15);
	private JTextField txtInputdate = new JTextField(15);

	public ProductMain(String title) {
		super(title); //이름 설정

		pdao= new ProductDao();
		lists= pdao.getAllProduct();
		rowData=new Object[lists.size()][columnNames.length]; //8,6

		fillRowData();//2차원 배열에 ArrayList를 넣는 메소드
		compose();//화면 구성 메소드


		super.setSize(500,530);
		super.setVisible(true);
		super.setResizable(false);
		super.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}

	private void compose() { //화면 구성
		table = new JTable(rowData,columnNames); //테이블 생성		
		scrollPane=new JScrollPane(table); //스크롤바 패널에 테이블 얹기
		scrollPane.setBounds(0,0,500,200);
		table.addMouseListener(new MouseHandeler());

		Container contentPane=getContentPane(); //컨테이너에 스크롤바 얹기
		contentPane.setLayout(null);
		contentPane.add(scrollPane,BorderLayout.NORTH);

		JLabel lbId=new JLabel("아이디");
		JLabel lbName=new JLabel("이름");
		JLabel lbStock=new JLabel("입고수량");
		JLabel lbPrice=new JLabel("단가");
		JLabel lbCategory=new JLabel("카테고리");
		JLabel lbInputdate=new JLabel("입고일자");

		JPanel pCenter = new JPanel();
		pCenter.setLayout(null);
		pCenter.setBounds(0,220,500,200);

		lbId.setBounds(20,20,100,20);
		lbName.setBounds(20,40,100,20);
		lbStock.setBounds(20,60,100,20);
		lbPrice.setBounds(20,80,100,20);
		lbCategory.setBounds(20,100,100,20);
		lbInputdate.setBounds(20,120,100,20);

		txtId.setBounds(150,20,100,20);
		txtName.setBounds(150,40,100,20);
		txtStock.setBounds(150,60,100,20);
		txtPrice.setBounds(150,80,100,20);
		txtCategory.setBounds(150,100,100,20);
		txtInputdate.setBounds(150,120,100,20);
		txtId.setText("시퀀스 자동입력");
		txtId.setEnabled(false);

		pCenter.add(lbId);
		pCenter.add(lbName);
		pCenter.add(lbStock);
		pCenter.add(lbPrice);
		pCenter.add(lbCategory);
		pCenter.add(lbInputdate);

		pCenter.add(txtId);
		pCenter.add(txtName);
		pCenter.add(txtStock);
		pCenter.add(txtPrice);
		pCenter.add(txtCategory);
		pCenter.add(txtInputdate);

		contentPane.add(pCenter,BorderLayout.CENTER);

		JPanel pSouth = new JPanel();
		pSouth.setLayout(new GridLayout(1,4));

		pSouth.setBounds(0,420,500,40);
		String[] btnTitle= {"등록","수정","삭제","종료"};

		for(int i=0;i<btn.length;i++) {
			btn[i]=new JButton(btnTitle[i]);
			btn[i].addActionListener(this); //현재 클래스 안에 ActionPerformed가 있으므로 자신 호출
			pSouth.add(btn[i]);
		}

		contentPane.add(pSouth);
	}//compose
	class MouseHandeler extends MouseAdapter{
		public void mouseClicked(MouseEvent e) {
			System.out.println("mouse clicked");
			int seletedRow =table.getSelectedRow();
			System.out.println(seletedRow);

			txtId.setText(table.getValueAt(seletedRow, 0).toString());
			txtName.setText(table.getValueAt(seletedRow, 1).toString());
			txtStock.setText(table.getValueAt(seletedRow, 2).toString());
			txtPrice.setText(table.getValueAt(seletedRow, 3).toString());
			txtCategory.setText(table.getValueAt(seletedRow, 4).toString());
			txtInputdate.setText(table.getValueAt(seletedRow, 5).toString());
		}
	}//MouseHandeler()
	private void fillRowData() {// ArrayList의 내용을 2차원 배열에 채우는 메소드
		Object[] arr=lists.toArray();// 배열로 바꾸는 메소드
		int j=0;
		for(int i=0;i<arr.length;i++) {
			ProductBean pb = (ProductBean)arr[i];
			rowData[i][j++]=pb.getId();
			rowData[i][j++]=pb.getName();
			rowData[i][j++]=pb.getStock();
			rowData[i][j++]=pb.getPrice();
			rowData[i][j++]=pb.getCategory();
			rowData[i][j++]=pb.getInputdate();
			j=0;
		}				
	}//fill

	public static void main(String[] args) {
		new ProductMain("상품관리");

	}

	@Override
	public void actionPerformed(ActionEvent e) {
		Object obj = e.getSource();
		if(obj==btn[0]) {
			System.out.println("등록");
			insertData();
		}else if(obj==btn[1]) {
			System.out.println("수정");
			updateData();
		}else if(obj==btn[2]) {
			System.out.println("삭제");
			deleteData();
		}else if(obj==btn[3]) {
			System.out.println("종료");
			System.exit(0);
		}

	}

	private void deleteData() {
		int selectedRow=table.getSelectedRow();
		if(selectedRow!=-1) {

			int id=Integer.parseInt(table.getValueAt(selectedRow, 0).toString());
			int cnt = pdao.deleteProducts(id);
			if(cnt != -1) {
				lists= pdao.getAllProduct();
				rowData=new Object[lists.size()][columnNames.length];

				fillRowData();//2차원 배열에 ArrayList를 넣는 메소드
				table = new JTable(rowData,columnNames);
				table.addMouseListener(new MouseHandeler());
				scrollPane.setViewportView(table);

				clearText();
			}
		}
		else {
			JOptionPane.showMessageDialog(this,"삭제할 레코드를 선택하세요",
					"에러 발생",JOptionPane.INFORMATION_MESSAGE);
		}
	}

	private void updateData() {
		boolean result = checkData();
		if(result!=false) {
			int id = Integer.parseInt(txtId.getText());
			String name=txtName.getText();
			int stock=Integer.parseInt(txtStock.getText());
			int price=Integer.parseInt(txtPrice.getText());
			String category=txtCategory.getText();
			String inputdate=txtInputdate.getText();

			ProductBean pb= new ProductBean(id,name,stock,price,category,inputdate);
			int cnt = pdao.updateProducts(pb);
			if(cnt != -1) {
				lists= pdao.getAllProduct();
				rowData=new Object[lists.size()][columnNames.length];

				fillRowData();//2차원 배열에 ArrayList를 넣는 메소드
				table = new JTable(rowData,columnNames);
				table.addMouseListener(new MouseHandeler());
				scrollPane.setViewportView(table);

				clearText();
			}
		}
	}

	private void insertData() {
		boolean result = checkData();
		if(result!=false) {

			String name=txtName.getText();
			int stock=Integer.parseInt(txtStock.getText());
			int price=Integer.parseInt(txtPrice.getText());
			String category=txtCategory.getText();
			String inputdate=txtInputdate.getText();

			ProductBean pb= new ProductBean(0,name,stock,price,category,inputdate);
			int cnt = pdao.insertProduct(pb);
			if(cnt != -1) {
				lists= pdao.getAllProduct();
				rowData=new Object[lists.size()][columnNames.length];

				fillRowData();//2차원 배열에 ArrayList를 넣는 메소드
				table = new JTable(rowData,columnNames);
				scrollPane.setViewportView(table);

				clearText();
			}

		}
	}

	private void clearText() {
		txtName.setText("");
		txtStock.setText("");
		txtPrice.setText("");
		txtCategory.setText("");
		txtInputdate.setText("");	
	}

	private boolean checkData() {
		if(txtName.getText().length()==0) {
			JOptionPane.showMessageDialog(txtName,"상품명을 입력하세요"
					,"에러 발생",JOptionPane.INFORMATION_MESSAGE);
			return false;
		}
		if(txtStock.getText().length()==0) {
			JOptionPane.showMessageDialog(txtStock,"재고수량을 입력하세요"
					,"에러 발생",JOptionPane.ERROR_MESSAGE);
			return false;
		}
		if(txtPrice.getText().length()==0) {
			JOptionPane.showMessageDialog(txtPrice,"단가를 입력하세요"
					,"에러 발생",JOptionPane.WARNING_MESSAGE);
			return false;
		}
		if(txtCategory.getText().length()==0) {
			JOptionPane.showMessageDialog(txtCategory,"카테고리를 입력하세요"
					,"에러 발생",JOptionPane.YES_NO_CANCEL_OPTION);
			return false;
		}
		if(txtInputdate.getText().length()==0) {
			JOptionPane.showMessageDialog(txtInputdate,"입고일자를 입력하세요"
					,"에러 발생",JOptionPane.YES_NO_OPTION);
			return false;
		}
		return true;
	}

}

 

Dao

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

public class ProductDao {
	String driver="oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:orcl";
	String id = "sqlid";
	String pw = "sqlpw";
	Connection conn=null;
	ProductDao(){
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			System.out.println("error 1");
		}
	}// 생성자
	public void connect() {
		try {
			conn=DriverManager.getConnection(url,id,pw);
		} catch (SQLException e) {
			System.out.println("error 2");
		}
	}// connect
	public ArrayList<ProductBean> getAllProduct() {
		connect();
		ArrayList<ProductBean> lists=new ArrayList<ProductBean>();
		String sql = "select * from products order by id";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				//				int id=rs.getInt("id");
				//				String name=rs.getString("name");
				//				int stock=rs.getInt("stock");
				//				int price=rs.getInt("price");
				//				String category=rs.getString("category");
				//				String inputdate=String.valueOf(rs.getDate("inputdate"));

				ProductBean pb=new ProductBean();

				pb.setId(rs.getInt("id"));
				pb.setName(rs.getString("name"));
				pb.setStock(rs.getInt("stock"));
				pb.setPrice(rs.getInt("price"));
				pb.setCategory(rs.getString("category"));
				pb.setInputdate(String.valueOf(rs.getDate("inputdate")));

				lists.add(pb);

			}

		} catch (SQLException e) {
			System.out.println("error 3");
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return lists;
	}//getAll
	public ProductBean getProductById(int id) {
		connect();

		String sql = "select * from products where id="+id;
		ProductBean pb = null;

		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				pb=new ProductBean();
				pb.setId(rs.getInt("id"));
				pb.setName(rs.getString("name"));
				pb.setStock(rs.getInt("stock"));
				pb.setPrice(rs.getInt("price"));
				pb.setCategory(rs.getString("category"));
				pb.setInputdate(String.valueOf(rs.getDate("inputdate")));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return pb;


	}
	public ArrayList<ProductBean> getProductByCategory(String cate) {
		connect();
		String sql = "select * from products where upper(category)=?";
		ProductBean pb = null;
		ArrayList<ProductBean> lists=new ArrayList<ProductBean>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, cate.toUpperCase());
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				pb=new ProductBean();
				pb.setId(rs.getInt("id"));
				pb.setName(rs.getString("name"));
				pb.setStock(rs.getInt("stock"));
				pb.setPrice(rs.getInt("price"));
				pb.setCategory(rs.getString("category"));
				pb.setInputdate(String.valueOf(rs.getDate("inputdate")));
				lists.add(pb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return lists;
	}
	public int insertProduct(ProductBean pb) {
		connect();
		String sql ="insert into products values"
				+ "(prdseq.nextval,?,?,?,?,?)";
		int result=-1;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1,pb.getName());
			ps.setInt(2, pb.getStock());
			ps.setInt(3, pb.getPrice());
			ps.setString(4, pb.getCategory());
			ps.setString(5, pb.getInputdate());
			result = ps.executeUpdate();
			System.out.println("result:"+result);

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return result;
	}
	public int deleteProducts(int del) {
		connect();
		String sql ="delete products where id=?";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1,del);
			result = ps.executeUpdate();
			

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return result;
	}
	public int updateProducts(ProductBean pb) {
		connect();
		int result=0;
		String sql ="update products set "
				+ "name=?,stock=?,price=?,category=?,inputdate=? where id=?";
		try {			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1,pb.getName());
			ps.setInt(2, pb.getStock());
			ps.setInt(3, pb.getPrice());
			ps.setString(4, pb.getCategory());
			ps.setString(5, pb.getInputdate());
			ps.setInt(6, pb.getId());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
		return result;
	}


}//Dao

 

Bean

 

public class ProductBean {
	private int id;
	private String name;
	private int stock;
	private int price;
	private String category;
	private String inputdate;
	
	public ProductBean() {
		
	}

	public ProductBean(int id, String name, int stock, int price, String category, String inputdate) {
		super();
		this.id = id;
		this.name = name;
		this.stock = stock;
		this.price = price;
		this.category = category;
		this.inputdate = inputdate;
	}


	public String toString() {
		return  id + "," + name + "," + stock + "," + price + ","
				+ category + "," + inputdate;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getStock() {
		return stock;
	}
	public void setStock(int stock) {
		this.stock = stock;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getInputdate() {
		return inputdate;
	}
	public void setInputdate(String date) {
		this.inputdate = date;
	}
	

}

'Develop > JAVA' 카테고리의 다른 글

0627 JDBC 개인 과제  (0) 2022.06.29
0624 JDBC 응용 마무리  (0) 2022.06.24
0623 JDBC 응용 2일차  (0) 2022.06.24
0622 JDBC 응용  (0) 2022.06.24
0621 JDBC 사용  (0) 2022.06.24

'Develop/JAVA'의 다른글

  • 현재글0628 JDBC와 Swing을 이용한 테이블 구성

관련글

티스토리툴바