13일차 Student.sql


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.24 16:51

 
create database sampledb;

use sampledb;

drop table student;

create table student
(
     name varchar(20) not null, -- 이름
    dept varchar(20) not null, -- 학과
    id char(10) primary key not null, -- 학번, 기본키
    address varchar(50) not null -- 주소
);

insert into student values('최규식', '스마트컨텐츠개발', '2014071023', '강원도 홍천군');
insert into student values('박태수', '스마트컨텐츠기획', '2014072008', '경기도 용인시');
insert into student values('정상혁', '스마트컨텐츠마케팅', '2014071020', '서울시 관악구');

insert into student values('이기자', '컴퓨터공학', '0494013000', '서울시 성북구');

select * from student order by name asc;

-- 도서대여 테이블 생성
create table bookRent
(
     no int not null primary key, -- 대여번호, 기본키
    id char(10) not null, -- 학번
    title varchar(20) not null, -- 책 제목
    rDate varchar(20) not null, -- 대여일자
    foreign key (id) references student(id) -- 참조키, 이 테이블의 id가 student 테이블의 id 참조
);

-- 부분출력 (현재날짜 및 시간) -> 인덱스번호는 1번부터...!!!
select substring(now(), 1, 20);

insert into bookRent values(201401, '2014072008', '입고살래?', substring(now(), 1, 20));
insert into bookRent values(201402, '2014071020', '난 정말 JAVA를 배운적이 없다구요', '20141202');
insert into bookRent values(201403, '2014071023', '짜투리 프로그래밍', '20141202');
insert into bookRent values(201404, '0494013000', '안드로이드', '20141223');

update bookRent set rDate='2014-12-02' where no=201402;
update bookRent set rDate='2014-12-02' where no=201403;
update bookRent set rDate='2014-12-23' where no=201404;

select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자
from student s, bookRent b
where s.id=b.id
order by id;

-- 컴퓨터시스템학과의 대출현황
select s.dept, count(b.no)
from student s, bookrent b
where s.id=b.id
group by s.dept;

select s.dept, count(b.no) as counts
from student s, bookrent b
where s.id=b.id
and s.dept='스마트컨텐츠개발';

 

신고

13일차 Book 클래스 (대출현황 출력)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.24 16:51

 

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;
import javax.swing.table.*;

public class Book extends JPanel
{
        Connection conn; // 연결 객체
        Statement stmt;
         DefaultTableModel model ;
         JTable table;
        
         String query;

        Book()
        {
                // 생성자에서 변수 초기화 (쿼리문)
                query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                              + " from student s, bookRent b"
                              + " where s.id=b.id";
               
                try
               {
                        Class. forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/sampledb" , "root" , "1234" );
                        stmt = conn.createStatement();
               }
                catch (Exception e1 )
               {
                        e1.printStackTrace();
               } // jdbc 드라이버 로드

               setLayout( null); // 레이아웃 설정 - 사용안함
               
                JLabel l_dept = new JLabel( "학과");
                l_dept.setBounds(10, 10, 50, 30);
               add( l_dept);
               
                String [] dept = { "전체", "스마트컨텐츠개발" , "스마트컨텐츠기획" , "스마트컨텐츠마케팅" , "컴퓨터공학" };
                JComboBox cb_dept = new JComboBox( dept);
                cb_dept.setBounds(70, 10, 150, 30);
               add( cb_dept);
               
                cb_dept.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               JComboBox cb = (JComboBox)e .getSource();
                              
                                System. out.println(cb .getSelectedIndex()); // 인덱스번호를 콘솔창에 출력
                              
                               int deptIndex = cb.getSelectedIndex();
                              
                               query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                                                     + " from student s, bookRent b"
                                                     + " where s.id=b.id";

                               if( deptIndex == 0)
                              {
                                       // '전체' 선택 시 모두 출력
                                       query += " order by id;";
                                      
                                       // List 메소드 호출 (목록버튼을 누를 필요가 없어진다.)
                                      List();
                              }
                               else if (deptIndex == 1)
                              {
                                       query += " and s.dept='스마트컨텐츠개발'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 2)
                              {
                                       query += " and s.dept='스마트컨텐츠기획'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 3)
                              {
                                       query += " and s.dept='스마트컨텐츠마케팅'" + " order by id;" ;
                                      List();
                              }
                               else if (deptIndex == 4)
                              {
                                       query += " and s.dept='컴퓨터공학'" + " order by id;" ;
                                      List();
                              }
                       }
               });
               
                JButton list = new JButton( "조회"); // // 'List'버튼 생성
                list.setBounds(234, 10, 80, 30);
               add( list);
               
                JButton out = new JButton( "종료"); // 'out'버튼 생성
                out.setBounds(378, 10, 80, 30);
               add( out);
        
                // Table의 Header 이름 지정
                String colName[] = { "학번", "이름" , "도서명" , "대여일자" };
             
                // Table에 들어갈 데이터 목록들(컬럼명, 추가될 Row 개수)
                model = new DefaultTableModel(colName , 0);        
                // Table 생성
                table = new JTable( model);       
                table.setPreferredScrollableViewportSize( new Dimension(470, 200));
          
               add( table);
                JScrollPane sp = new JScrollPane(table);
                sp.setBounds(10, 50, 450, 195);
               add( sp);

               
                // ----------------------------------------------------------------------------------
               
                // 익명 클래스 이용 (이벤트 처리) -> 목록 처리
                list.addActionListener( new ActionListener()
               {
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                              List(); // 목록 갱신 메소드 호출
                       }
               });
               
               
                // 익명 클래스 이용6 (이벤트 처리) -> 종료 처리
                out.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                                 catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                               
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                       }
               });
               
               
                // ----------------------------------------------------------------------------------
               
                // setResizable(false); // 화면 크기 고정
               setSize(488, 300); // 화면 크기 설정
               setVisible( true); // 화면 출력 설정
               
               List(); // List 메소드 호출 -> 프레임이 출력되는 동시에 전체목록이 출력된다.
        }
        
         public void List()
        {
                try
               {
                        System. out.println("JDBC Connection Complete!");
                       
                       ResultSet rs = stmt.executeQuery( query);
                                      
                        // JTable 초기화
                        model.setNumRows(0);
                       
                        while(rs .next())
                       {
                               String[] row = new String[4]; // 컬럼의 개수가 4
                               row[0] = rs.getString( "id");
                               row[1] = rs.getString( "이름");
                               row[2] = rs.getString( "도서명");
                               row[3] = rs.getString( "대여일자" );
                              
                               model.addRow( row);
                       }
                        rs.close(); // ResultSet을 닫는다.
               }
                catch(Exception ex )
               {      
                        System. out.println(ex .getMessage());
               }
        }
        
         public static void main(String[] args)
        {
                new Book();
        }
}
신고

13일차 Student 클래스 (학생현황 출력)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.24 16:51

 
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;
import javax.swing.table.*;

public class Student extends JPanel
{
        Connection conn; // 연결 객체
        Statement stmt;
         DefaultTableModel model ;
         JTable table;
        
        Student()
        {
                try
               {
                        Class. forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/sampledb" , "root" , "1234" );
                        stmt = conn.createStatement();
               }
                catch (Exception e1 )
               {
                        e1.printStackTrace();
               } // jdbc 드라이버 로드
               
               setLayout( new FlowLayout( FlowLayout. LEFT)); // 레이아웃 설정
               
               add( new JLabel( "학번 : "));
                JTextField T_id = new JTextField(10);
               add( T_id);

               add( new JLabel( "이름 : "));
                JTextField T_name = new JTextField(10);
               add( T_name);
               
               add( new JLabel( "                                                                 "));
               
               add( new JLabel( "학과 : "));
                JTextField T_dept = new JTextField(10);
               add( T_dept);
               
               add( new JLabel( "                                                                                                                              "));
               
               add( new JLabel( "주소 : "));
                JTextField T_address = new JTextField(25);
               add( T_address);
               
               
                // Table의 Header 이름 지정
                String colName[] = { "학번", "이름" , "학과" , "주소" };
             
                // Table에 들어갈 데이터 목록들(컬럼명, 추가될 Row 개수)
                model = new DefaultTableModel(colName , 0);        
                // Table 생성
                table = new JTable( model);       
                table.setPreferredScrollableViewportSize( new Dimension(470, 200));
          
               add( table); 
               add( new JScrollPane(table ));
             
               add( new JLabel( "        " ));
                JButton list = new JButton( "목록"); // // 'List'버튼 생성
               add( list);
               add( new JLabel( "        " ));
                JButton insert = new JButton( "등록"); // 'Insert'버튼 생성
               add( insert);
               add( new JLabel( "        " ));
                JButton update = new JButton( "수정"); // 'update'버튼 생성
               add( update);
               add( new JLabel( "        " ));
                JButton delete = new JButton( "삭제"); // 'delete'버튼 생성
               add( delete);
               add( new JLabel( "        " ));
                JButton search = new JButton( "검색"); // 'search'버튼 생성
               add( search);
               add( new JLabel( "        " ));
                JButton out = new JButton( "종료"); // 'out'버튼 생성
               add( out);
               
               
               
                table.addMouseListener( new MouseListener()
               {             
                        @Override
                        public void mouseClicked(MouseEvent e)
                       {
                               table = ( JTable) e.getComponent(); // 클릭한 테이블 구하기
                               model = (DefaultTableModel)table .getModel(); // 테이블의 모델 구하기
                              
                               String id = (String)model .getValueAt(table.getSelectedRow(), 0);
                               T_id.setText( id);
                               String name = (String)model .getValueAt(table.getSelectedRow(), 1);
                               T_name.setText( name);
                               String dept = (String)model .getValueAt(table.getSelectedRow(), 2);
                               T_dept.setText( dept);
                               String address = (String)model .getValueAt(table.getSelectedRow(), 3);
                               T_address.setText( address);
                       }
                       
                        @Override
                        public void mouseReleased(MouseEvent e) {}           
                        @Override
                        public void mousePressed(MouseEvent e) {}               
                        @Override
                        public void mouseExited(MouseEvent e) {}               
                        @Override
                        public void mouseEntered(MouseEvent e) {}               
               });
               
                // ----------------------------------------------------------------------------------
               
                // 익명 클래스 이용 (이벤트 처리) -> 목록 처리
                list.addActionListener( new ActionListener()
               {
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                              List(); // 목록 갱신 메소드 호출
                       }
               });
               
                // 익명 클래스 이용2 (이벤트 처리) -> 등록 처리
                insert.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               try
                              {
                                       String n = T_name.getText();
                                       String d = T_dept.getText();
                                       String i = T_id.getText();
                                       String a = T_address.getText();
                                      
                                       // insert문 실행
                                       int result = stmt.executeUpdate("insert into student values('"+n+"','"+ d+ "','"+i +"','" +a +"')" );
                                      
                                       if( result == 1)
                                      {
                                             List(); // 목록 갱신 메소드 호출
                                               JOptionPane.showMessageDialog( null, "Insert Complete!", "Complete Dialog", JOptionPane.INFORMATION_MESSAGE );
                                      }
                              }
                               catch(Exception ex )
                              {      
                                       System. out.println(ex .getMessage());
                              }
                       }
               });
               
                // 익명 클래스 이용3 (이벤트 처리) -> 검색 처리
                search.addActionListener( new ActionListener()
               {
                       
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                              
                               if(( T_id.getText()).equals( ""))
                              {
                                       JOptionPane.showMessageDialog( null, "No ID in TextField!!!", "Error Dialog", JOptionPane.ERROR_MESSAGE );
                                       return; // 함수종료
                              }
                               else
                              {
                                       try
                                      {
                                              String i = T_id.getText();

                                              // select문 실행
                                             ResultSet rs = stmt.executeQuery("select * from student where id='"+i+"'");
                                             
                                              // JTable 초기화
                                              model.setNumRows(0);
                                             
                                              if( rs.next())
                                             {
                                                      T_id.setText( rs.getString( "id"));
                                                      T_name.setText( rs.getString( "name"));
                                                      T_dept.setText( rs.getString( "dept"));
                                                      T_address.setText( rs.getString( "address"));
                                                     
                                                      String[] row = new String[4]; // 컬럼의 개수가 4
                                                      row[0] = rs.getString("id" );
                                                      row[1] = rs.getString("name" );
                                                      row[2] = rs.getString("dept" );
                                                      row[3] = rs.getString("address" );
                                                     
                                                      model.addRow( row);
                                             }
                                              rs.close();
                                      }
                                       catch(Exception ex )
                                      {      
                                               System. out.println(ex .getStackTrace());
                                      }
                              }             
                       }             
               });
               
                // 익명 클래스 이용4 (이벤트처리) -> 수정 처리
                update.addActionListener( new ActionListener()
               {
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               if(( T_id.getText()).equals( ""))
                              {
                                       JOptionPane.showMessageDialog( null, "No ID in TextField!!!", "Error Dialog", JOptionPane.ERROR_MESSAGE );
                                       return; // 함수종료
                              }
                               else
                              {
                                       int result = JOptionPane.showConfirmDialog( null, "Update?" , "Update Dialog", JOptionPane.YES_NO_OPTION);
                                      
                                       if( result == JOptionPane.YES_OPTION )
                                      {
                                              try
                                             {
                                                      String i = T_id.getText();
                                                      String n = T_name.getText();
                                                      String d = T_dept.getText();
                                                      String a = T_address.getText();
        
                                                      // update문 실행
                                                      stmt.executeUpdate( "update student set name='"+n+"', dept='"+ d+ "', address='"+a +"' where id='"+i+"'");
                                                                           
                                                     List(); // 목록 갱신 메소드 호출                        
                                                      JOptionPane.showMessageDialog( null, "Update Complete!", "Complete Dialog", JOptionPane.INFORMATION_MESSAGE );
               
                                             }
                                              catch(Exception ex )
                                             {      
                                                      System. out.println(ex .getMessage());
                                             }
                                      }
                                       else if (result == JOptionPane.NO_OPTION )
                                      {
                                             
                                      }
                              }
                       }
               });
               
                // 익명 클래스 이용5 (이벤트 처리) -> 삭제 처리
                delete.addActionListener( new ActionListener()
               {
                       
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                              
                               if(( T_id.getText()).equals( ""))
                              {
                                       JOptionPane.showMessageDialog( null, "No ID in TextField!!!", "Error Dialog", JOptionPane.ERROR_MESSAGE );
                                       return; // 함수종료
                              }
                               else if ((T_dept .getText()).equals(""))
                              {
                                       JOptionPane.showMessageDialog( null, "No Dept in TextField!!!", "Error Dialog", JOptionPane.ERROR_MESSAGE );
                                       return; // 함수종료
                              }
                               else if ((T_name .getText()).equals(""))
                              {
                                       JOptionPane.showMessageDialog( null, "No Name in TextField!!!", "Error Dialog", JOptionPane.ERROR_MESSAGE );
                                       return; // 함수종료
                              }
                               else if ((T_address .getText()).equals(""))
                              {
                                       JOptionPane.showMessageDialog( null, "No address in TextField!!!", "Error Dialog", JOptionPane.ERROR_MESSAGE );
                                       return; // 함수종료
                              }
                               else
                              {
                                       int result = JOptionPane.showConfirmDialog( null, "Delete?" , "Delete Dialog", JOptionPane.YES_NO_OPTION);
                                      
                                       if( result == JOptionPane.YES_OPTION )
                                      {
                                              try
                                             {
                                                      String i = T_id.getText();
                                                     
                                                      // delete문 실행
                                                      int result2 = stmt.executeUpdate("delete from student where id='" +i +"'" );
                                                     
                                                      if( result2 == 1)
                                                     {
                                                            List(); // 목록 갱신 메소드 호출
                                                            
                                                             // TextArea 초기화
                                                             T_id.setText( "");
                                                             T_name.setText( "");
                                                             T_dept.setText( "");
                                                              T_address.setText( "");
                                                            
                                                              JOptionPane.showMessageDialog( null, "Delete Complete!", "Complete Dialog", JOptionPane.INFORMATION_MESSAGE );
                                                     }                     

                                             }
                                              catch(Exception ex )
                                             {      
                                                      System. out.println(ex .getStackTrace());
                                             }
                                      }
                                       else if (result == JOptionPane.NO_OPTION )
                                      {
                                             
                                      }
                              }                     
                       }
               });
               
                // 익명 클래스 이용6 (이벤트 처리) -> 종료 처리
                out.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                                 catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                       }
               });
               
                // ----------------------------------------------------------------------------------
               
               setSize(550, 420); // 화면 크기 설정
               setVisible( true); // 화면 출력 설정
        }
        
         public void List()
        {
                try
               {
                        System. out.println("JDBC Connection Complete!");
                       
                        // select문 실행
                       ResultSet rs = stmt.executeQuery( "select * from student"); // SQL문이 rs에 저장된다.
                                      
                        // JTable 초기화
                        model.setNumRows(0);
                       
                        while(rs .next())
                       {
                               String[] row = new String[4]; // 컬럼의 개수가 4
                               row[0] = rs.getString( "id");
                               row[1] = rs.getString( "name");
                               row[2] = rs.getString( "dept");
                               row[3] = rs.getString( "address");
                              
                               model.addRow( row);
                       }
                        rs.close(); // ResultSet을 닫는다.
               }
                catch(Exception ex )
               {      
                        System. out.println(ex .getStackTrace());
               }
        }
        

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

 

신고

13일차 Swing04 클래스 (Book, Student 클래스와 연동)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.24 16:50

 
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;
import javax.swing.table.*;

public class Swing04 extends JFrame
{
        
        Connection conn; // 연결 객체
        Statement stmt;
         DefaultTableModel model ;
         JTable table;
        
         String query;

        Swing04()
        {
                // 생성자에서 변수 초기화 (쿼리문)
                query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                              + " from student s, bookRent b"
                              + " where s.id=b.id";
               
                try
               {
                        Class. forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/sampledb" , "root" , "1234" );
                        stmt = conn.createStatement();
               }
                catch (Exception e1 )
               {
                        e1.printStackTrace();
               } // jdbc 드라이버 로드
               
               setTitle( "학생관리" );
                setDefaultCloseOperation( JFrame. EXIT_ON_CLOSE);
               
               setLayout( null); // 레이아웃 설정 - 사용안함
               
                JLabel l_dept = new JLabel( "학과");
                l_dept.setBounds(10, 10, 50, 30);
               add( l_dept);
               
                String [] dept = { "전체", "스마트컨텐츠개발" , "스마트컨텐츠기획" , "스마트컨텐츠마케팅" , "컴퓨터공학" };
                JComboBox cb_dept = new JComboBox( dept);
                cb_dept.setBounds(70, 10, 150, 30);
               add( cb_dept);
               
                cb_dept.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               JComboBox cb = (JComboBox)e .getSource();
                              
                                System. out.println(cb .getSelectedIndex()); // 인덱스번호를 콘솔창에 출력
                              
                               int deptIndex = cb.getSelectedIndex();
                              
                               query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                                                     + " from student s, bookRent b"
                                                     + " where s.id=b.id";

                               if( deptIndex == 0)
                              {
                                       // '전체' 선택 시 모두 출력
                                       query += " order by id;";
                                      
                                       // List 메소드 호출 (목록버튼을 누를 필요가 없어진다.)
                                      List();
                              }
                               else if (deptIndex == 1)
                              {
                                       query += " and s.dept='스마트컨텐츠개발'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 2)
                              {
                                       query += " and s.dept='스마트컨텐츠기획'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 3)
                              {
                                       query += " and s.dept='스마트컨텐츠마케팅'" + " order by id;" ;
                                      List();
                              }
                               else if (deptIndex == 4)
                              {
                                       query += " and s.dept='컴퓨터공학'" + " order by id;" ;
                                      List();
                              }
                       }
               });
               
                JButton list = new JButton( "조회"); // // 'List'버튼 생성
                list.setBounds(234, 10, 80, 30);
               add( list);
               
                JButton out = new JButton( "종료"); // 'out'버튼 생성
                out.setBounds(378, 10, 80, 30);
               add( out);
        
                // Table의 Header 이름 지정
                String colName[] = { "학번", "이름" , "도서명" , "대여일자" };
             
                // Table에 들어갈 데이터 목록들(컬럼명, 추가될 Row 개수)
                model = new DefaultTableModel(colName , 0);        
                // Table 생성
                table = new JTable( model);       
                table.setPreferredScrollableViewportSize( new Dimension(470, 200));
          
               add( table);
                JScrollPane sp = new JScrollPane(table);
                sp.setBounds(10, 50, 450, 195);
               add( sp);

               
                // 종료 이벤트 처리
               addWindowListener( new WindowAdapter()
               {
                          public void windowClosing(WindowEvent e)
                          {
                                 try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                          catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                          }
               });

               
                // ----------------------------------------------------------------------------------
               
                // 익명 클래스 이용 (이벤트 처리) -> 목록 처리
                list.addActionListener( new ActionListener()
               {
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                              List(); // 목록 갱신 메소드 호출
                       }
               });
               
               
                // 익명 클래스 이용6 (이벤트 처리) -> 종료 처리
                out.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                                 catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                               
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                                 dispose();
                       }
               });
               
               
                // ----------------------------------------------------------------------------------
               
                // setResizable(false); // 화면 크기 고정
               setSize(488, 300); // 화면 크기 설정
               setVisible( true); // 화면 출력 설정
               
               List(); // List 메소드 호출 -> 프레임이 출력되는 동시에 전체목록이 출력된다.
        }
        
         public void List()
        {
                try
               {
                        System. out.println("JDBC Connection Complete!");
                       
                       ResultSet rs = stmt.executeQuery( query);
                                      
                        // JTable 초기화
                        model.setNumRows(0);
                       
                        while(rs .next())
                       {
                               String[] row = new String[4]; // 컬럼의 개수가 4
                               row[0] = rs.getString( "id");
                               row[1] = rs.getString( "이름");
                               row[2] = rs.getString( "도서명");
                               row[3] = rs.getString( "대여일자" );
                              
                               model.addRow( row);
                       }
                        rs.close(); // ResultSet을 닫는다.
               }
                catch(Exception ex )
               {      
                        System. out.println(ex .getMessage());
               }
        }
        
         public static void main(String[] args)
        {
                new Swing04();
        }

}

 

신고

12일차 Book 클래스 (Swing04에 적용할 버튼 작성)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.23 15:57

 

import javax.swing.*;

public class Book extends JPanel
{
        Book()
        {
                JButton bt = new JButton( "테스트");
               add( bt);
        }
        
         public static void main(String[] args)
        {
                new Book();
        }
}
신고

12일차 Student.sql (도서대여 테이블, 조인문)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.23 15:57

 
create database sampledb;

use sampledb;

drop table student;

create table student
(
     name varchar(20) not null, -- 이름
    dept varchar(20) not null, -- 학과
    id char(10) primary key not null, -- 학번, 기본키
    address varchar(50) not null -- 주소
);

insert into student values('최규식', '스마트컨텐츠개발', '2014071023', '강원도 홍천군');
insert into student values('박태수', '스마트컨텐츠기획', '2014072008', '경기도 용인시');
insert into student values('정상혁', '스마트컨텐츠마케팅', '2014071020', '서울시 관악구');

insert into student values('이기자', '컴퓨터공학', '0494013000', '서울시 성북구');

select * from student order by name asc;

-- 도서대여 테이블 생성
create table bookRent
(
     no int not null primary key, -- 대여번호, 기본키
    id char(10) not null, -- 학번
    title varchar(20) not null, -- 책 제목
    rDate varchar(20) not null, -- 대여일자
    foreign key (id) references student(id) -- 참조키, 이 테이블의 id가 student 테이블의 id 참조
);

-- 부분출력 (현재날짜 및 시간) -> 인덱스번호는 1번부터...!!!
select substring(now(), 1, 20);

insert into bookRent values(201401, '2014072008', '입고살래?', substring(now(), 1, 20));
insert into bookRent values(201402, '2014071020', '난 정말 JAVA를 배운적이 없다구요', '20141202');
insert into bookRent values(201403, '2014071023', '짜투리 프로그래밍', '20141202');
insert into bookRent values(201404, '0494013000', '안드로이드', '20141223');

update bookRent set rDate='2014-12-02' where no=201402;
update bookRent set rDate='2014-12-02' where no=201403;
update bookRent set rDate='2014-12-23' where no=201404;

select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자
from student s, bookRent b
where s.id=b.id
order by id;

 

신고

12일차 Swing04 클래스 (GridLayout 출력)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.23 15:57

 
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;
import javax.swing.table.*;

public class Swing04 extends JFrame
{
        
        Connection conn; // 연결 객체
        Statement stmt;
         DefaultTableModel model ;
         JTable table;
        
         String query;

        Swing04()
        {
                // 생성자에서 변수 초기화 (쿼리문)
                query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                              + " from student s, bookRent b"
                              + " where s.id=b.id" ;
               
                try
               {
                        Class. forName( "com.mysql.jdbc.Driver");
                        conn = DriverManager .getConnection( "jdbc:mysql://localhost:3306/sampledb" , "root" , "1234" );
                        stmt = conn.createStatement();
               }
                catch (Exception e1 )
               {
                        e1.printStackTrace();
               } // jdbc 드라이버 로드
               
               setTitle( "학생관리" );
                setDefaultCloseOperation( JFrame. EXIT_ON_CLOSE );
               
               setLayout( null ); // 레이아웃 설정 - 사용안함
               
                JLabel l_dept = new JLabel( "학과" );
                l_dept.setBounds(10, 10, 50, 30);
               add( l_dept);
               
                String [] dept = { "전체" , "스마트컨텐츠개발" , "스마트컨텐츠기획" , "스마트컨텐츠마케팅" , "컴퓨터공학" };
                JComboBox cb_dept = new JComboBox( dept );
                cb_dept.setBounds(70, 10, 150, 30);
               add( cb_dept);
               
                cb_dept.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed( ActionEvent e)
                       {
                               JComboBox cb = (JComboBox )e .getSource();
                              
                                System. out .println(cb .getSelectedIndex()); // 인덱스번호를 콘솔창에 출력
                              
                               int deptIndex = cb.getSelectedIndex();
                              
                               query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                                                     + " from student s, bookRent b"
                                                     + " where s.id=b.id" ;

                               if( deptIndex == 0)
                              {
                                       // '전체' 선택 시 모두 출력
                                       query += " order by id;" ;
                                      
                                       // List 메소드 호출 (목록버튼을 누를 필요가 없어진다.)
                                      List();
                              }
                               else if (deptIndex == 1)
                              {
                                       query += " and s.dept='스마트컨텐츠개발'" + " order by id;" ;
                                      List();
                              }
                               else if (deptIndex == 2)
                              {
                                       query += " and s.dept='스마트컨텐츠기획'" + " order by id;" ;
                                      List();
                              }
                               else if (deptIndex == 3)
                              {
                                       query += " and s.dept='스마트컨텐츠마케팅'" + " order by id;" ;
                                      List();
                              }
                               else if (deptIndex == 4)
                              {
                                       query += " and s.dept='컴퓨터공학'" + " order by id;" ;
                                      List();
                              }
                       }
               });
               
                JButton list = new JButton( "조회" ); // // 'List'버튼 생성
                list.setBounds(234, 10, 80, 30);
               add( list);
               
                JButton out = new JButton( "종료" ); // 'out'버튼 생성
                out.setBounds(378, 10, 80, 30);
               add( out);
        
                // Table의 Header 이름 지정
                String colName[] = { "학번" , "이름" , "도서명" , "대여일자" };
             
                // Table에 들어갈 데이터 목록들(컬럼명, 추가될 Row 개수)
                model = new DefaultTableModel (colName , 0);        
                // Table 생성
                table = new JTable( model);       
                table.setPreferredScrollableViewportSize( new Dimension (470, 200));
          
               add( table);
                JScrollPane sp = new JScrollPane( table);
                sp.setBounds(10, 50, 450, 195);
               add( sp);

               
                // 종료 이벤트 처리
               addWindowListener( new WindowAdapter ()
               {
                          public void windowClosing( WindowEvent e )
                          {
                                 try
                                 {
                                         if( conn != null )
                                         {
                                         conn.close();
                                         }
                                 }
                          catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                
                                 JOptionPane .showMessageDialog( null , "Good Bye" , "Exit Dialog" , JOptionPane .INFORMATION_MESSAGE );
                          }
               });

               
                // ----------------------------------------------------------------------------------
               
                // 익명 클래스 이용 (이벤트 처리) -> 목록 처리
                list.addActionListener( new ActionListener()
               {
                        @Override
                        public void actionPerformed( ActionEvent e)
                       {
                              List(); // 목록 갱신 메소드 호출
                       }
               });
               
               
                // 익명 클래스 이용6 (이벤트 처리) -> 종료 처리
                out.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed( ActionEvent e)
                       {
                               try
                                 {
                                         if( conn != null )
                                         {
                                         conn.close();
                                         }
                                 }
                                 catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                               
                                 JOptionPane .showMessageDialog( null , "Good Bye" , "Exit Dialog" , JOptionPane .INFORMATION_MESSAGE );
                                 dispose();
                       }
               });
               
               
                // ----------------------------------------------------------------------------------
               
                // setResizable(false); // 화면 크기 고정
               setSize(488, 300); // 화면 크기 설정
               setVisible( true ); // 화면 출력 설정
               
               List(); // List 메소드 호출 -> 프레임이 출력되는 동시에 전체목록이 출력된다.
        }
        
         public void List()
        {
                try
               {
                        System. out .println("JDBC Connection Complete!");
                       
                       ResultSet rs = stmt.executeQuery( query);
                                      
                        // JTable 초기화
                        model.setNumRows(0);
                       
                        while (rs .next())
                       {
                               String[] row = new String[4]; // 컬럼의 개수가 4
                               row[0] = rs.getString( "id" );
                               row[1] = rs.getString( "이름" );
                               row[2] = rs.getString( "도서명" );
                               row[3] = rs.getString( "대여일자" );
                              
                               model.addRow( row);
                       }
                        rs.close(); // ResultSet을 닫는다.
               }
                catch (Exception ex )
               {      
                        System. out .println(ex .getMessage());
               }
        }
        
         public static void main( String[] args)
        {
                new Swing04();
        }

}
신고

 

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;
import javax.swing.table.*;

public class Swing04 extends JFrame
{
        
        Connection conn; // 연결 객체
        Statement stmt;
         DefaultTableModel model ;
         JTable table;
        
         String query;

        Swing04()
        {
                // 생성자에서 변수 초기화 (쿼리문)
                query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                              + " from student s, bookRent b"
                              + " where s.id=b.id";
               
                try
               {
                        Class. forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/sampledb" , "root" , "1234" );
                        stmt = conn.createStatement();
               }
                catch (Exception e1 )
               {
                        e1.printStackTrace();
               } // jdbc 드라이버 로드
               
               setTitle( "학생관리" );
                setDefaultCloseOperation( JFrame. EXIT_ON_CLOSE);
               
               setLayout( new FlowLayout( FlowLayout. LEFT)); // 레이아웃 설정
               
               add( new JLabel( "학과 : "));
               
               
                String [] dept = { "전체", "스마트컨텐츠개발" , "스마트컨텐츠기획" , "스마트컨텐츠마케팅" , "컴퓨터공학" };
                JComboBox cb_dept = new JComboBox( dept);
               add( cb_dept);
               
                cb_dept.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               JComboBox cb = (JComboBox)e .getSource();
                              
                                System. out.println(cb .getSelectedIndex()); // 인덱스번호를 콘솔창에 출력
                              
                               int deptIndex = cb.getSelectedIndex();
                              
                               query = "select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자"
                                                     + " from student s, bookRent b"
                                                     + " where s.id=b.id";

                               if( deptIndex == 0)
                              {
                                       // '전체' 선택 시 모두 출력
                                       query += " order by id;";
                                      
                                       // List 메소드 호출 (목록버튼을 누를 필요가 없어진다.)
                                      List();
                              }
                               else if (deptIndex == 1)
                              {
                                       query += " and s.dept='스마트컨텐츠개발'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 2)
                              {
                                       query += " and s.dept='스마트컨텐츠기획'" + " order by id;";
                                      List();
                              }
                               else if (deptIndex == 3)
                              {
                                       query += " and s.dept='스마트컨텐츠마케팅'" + " order by id;" ;
                                      List();
                              }
                               else if (deptIndex == 4)
                              {
                                       query += " and s.dept='컴퓨터공학'" + " order by id;" ;
                                      List();
                              }
                       }
               });
               
               add( new JLabel( "                                          " ));
                JButton list = new JButton( "조회"); // // 'List'버튼 생성
               add( list);
               
               add( new JLabel( "      " ));
                JButton out = new JButton( "종료"); // 'out'버튼 생성
               add( out);
        
                // Table의 Header 이름 지정
                String colName[] = { "학번", "이름" , "도서명" , "대여일자" };
             
                // Table에 들어갈 데이터 목록들(컬럼명, 추가될 Row 개수)
                model = new DefaultTableModel(colName , 0);        
                // Table 생성
                table = new JTable( model);       
                table.setPreferredScrollableViewportSize( new Dimension(470, 200));
          
               add( table); 
               add( new JScrollPane(table ));


               
                // 종료 이벤트 처리
               addWindowListener( new WindowAdapter()
               {
                          public void windowClosing(WindowEvent e)
                          {
                                 try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                          catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                          }
               });

               
                // ----------------------------------------------------------------------------------
               
                // 익명 클래스 이용 (이벤트 처리) -> 목록 처리
                list.addActionListener( new ActionListener()
               {
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                              List(); // 목록 갱신 메소드 호출
                       }
               });
               
               
                // 익명 클래스 이용6 (이벤트 처리) -> 종료 처리
                out.addActionListener( new ActionListener()
               {      
                        @Override
                        public void actionPerformed(ActionEvent e)
                       {
                               try
                                 {
                                         if( conn != null)
                                         {
                                         conn.close();
                                         }
                                 }
                                 catch (SQLException e1 )
                                 {
                                         e1.printStackTrace();
                                 }
                                               
                                 JOptionPane.showMessageDialog( null, "Good Bye", "Exit Dialog" , JOptionPane.INFORMATION_MESSAGE );
                                 dispose();
                       }
               });
               
               
                // ----------------------------------------------------------------------------------
               
               setResizable( false); // 화면 크기 고정
               setSize(488, 300); // 화면 크기 설정
               setVisible( true); // 화면 출력 설정
               
               List(); // List 메소드 호출 -> 프레임이 출력되는 동시에 전체목록이 출력된다.
        }
        
         public void List()
        {
                try
               {
                        System. out.println("JDBC Connection Complete!");
                       
                       ResultSet rs = stmt.executeQuery( query);
                                      
                        // JTable 초기화
                        model.setNumRows(0);
                       
                        while(rs .next())
                       {
                               String[] row = new String[4]; // 컬럼의 개수가 4
                               row[0] = rs.getString( "id");
                               row[1] = rs.getString( "이름");
                               row[2] = rs.getString( "도서명");
                               row[3] = rs.getString( "대여일자" );
                              
                               model.addRow( row);
                       }
                        rs.close(); // ResultSet을 닫는다.
               }
                catch(Exception ex )
               {      
                        System. out.println(ex .getMessage());
               }
        }
        
         public static void main(String[] args)
        {
                new Swing04();
        }

}
신고

11일차 Student.sql (Swing03용 SQL작성)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.22 15:53

 

create database sampledb;

use sampledb;

drop table student;

create table student
(
     name varchar(20) not null, -- 이름
    dept varchar(20) not null, -- 학과
    id char(10) primary key not null, -- 학번, 기본키
    address varchar(50) not null -- 주소
);

insert into student values('최규식', '스마트컨텐츠개발', '2014071023', '강원도 홍천군');
insert into student values('박태수', '스마트컨텐츠기획', '2014072008', '경기도 용인시');
insert into student values('정상혁', '스마트컨텐츠마케팅', '2014071020', '서울시 관악구');

select * from student order by name asc;

-- 도서대여 테이블 생성
create table bookRent
(
     no int not null primary key, -- 대여번호, 기본키
    id char(10) not null, -- 학번
    title varchar(20) not null, -- 책 제목
    rDate varchar(20) not null, -- 대여일자
    foreign key (id) references student(id) -- 참조키, 이 테이블의 id가 student 테이블의 id 참조
);

-- 부분출력 (현재날짜 및 시간) -> 인덱스번호는 1번부터...!!!
select substring(now(), 1, 20);

insert into bookRent values(201401, '2014072008', '입고살래?', substring(now(), 1, 20));
insert into bookRent values(201402, '2014071020', '난 정말 JAVA를 배운적이 없다구요', '20141202');
insert into bookRent values(201403, '2014071023', '짜투리 프로그래밍', '20141202');

update bookRent set rDate='2014-12-02' where no=201402;
update bookRent set rDate='2014-12-02' where no=201403;

select s.id, s.name 이름, b.title 도서명, b.rDate 대여일자
from student s, bookRent b
where s.id=b.id
order by id;
신고

11일차 Swing02 클래스 (Table로 출력)


2014학년도 겨울 심화학기/JAVA

Written by 긍지높은취객 긍지높은취객 on 2014.12.22 15:53

 

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

import javax.swing.*;
import javax.swing.table.*;


public class Swing02 extends JFrame
{
               
        Connection conn; // 연결 객체
        Statement stmt;
         DefaultTableModel model ;
         JTable table;
        
        Swing02()
        {
                try
               {
                        Class. forName("com.mysql.jdbc.Driver");
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/sampledb" , "root" , "1234" );
                        stmt = conn.createStatement();
               }
                catch (Exception e1 )
               {
                        e1.printStackTrace();
               } // jdbc 드라이버 로드
               
                try
               {
                        UIManager. setLookAndFeel("com.sun.java.swing.plaf.gtk.GTKLookAndFeel");
               }
                catch (Exception e )
               {
                        e.printStackTrace();
               }
               
               setTitle( "학생관리" );
                setDefaultCloseOperation( JFrame. EXIT_ON_CLOSE);
               
               setLayout( new FlowLayout( FlowLayout. LEFT)); // 레이아웃 설정
               
               add( new JLabel( "학번 : "));
                JTextField T_id = new JTextField(10);
               add( T_id);

               add( new JLabel( "이름 : "));
                JTextField T_name = new JTextField(10);
               add( T_name);
               
               add( new JLabel( "                                                             "));
               
               add( new JLabel( "학과 : "));
                JTextField T_dept = new JTextField(10);
               add( T_dept);
               
               add( new JLabel( "                                                                                                                              "));
               
               add( new JLabel( "주소 : "));
                JTextField T_address = new JTextField(25);
               add( T_address);
               
               
                // Table의 Header 이름 지정
                String colName[] = { "학번", "이름" , "학과" , "주소" };