오라클 실습용 데이터  

http://braverokmc.dothome.co.kr/m04/oracle/view/410

 

 

 

-- 과목 테이블
drop table subject;
-- number  : 정수 , 실수
-- number (3, 3)
-- char(고정길이), varchar(가변길이)
 

create table subject(
 subject_code number not null primary key,
 subject_name varchar2(50) not null,
 profno number not null,
 point number default 3
);

insert into subject values (1, 'java', 1001, 3);
insert into subject values (2, 'db', 1002, 4);
insert into subject values (3, 'jsp' , 1003, 2);

select * from subject;

commit;

select * from professor;

insert into subject (subject_code, subject_name, profno) 
values (4, '안드로이드', 1001);

select * from subject;

 

-- 수강테이블

create table lecture (
  studno number not null,
  subject_code number not null,
  grade VARCHAR2(2),
  primary key(studno, subject_code)
);

insert into lecture values (9411, 1, 'A0');
insert into lecture values (9411, 2, 'A+');
insert into lecture values (9411, 3, 'B0');
insert into lecture values (9412, 3, 'C0');
insert into lecture values (9413, 4, 'F');
insert into lecture values (9413, 4, 'B+');
insert into lecture values (9411, 3, 'A+');
insert into lecture values (9413, 2, 'B0');
insert into lecture values (9413, 1, 'A+');

commit;

select * from lecture;
select * from student;
select * from PROFESSOR;
select * from subject;

 

-- 학번 , 이름, 과목명, 담당교수, 학점, 등급

 

select  l.STUDNO,  s.NAME , sb.SUBJECT_NAME , p.name,  sb.point, l.grade 
  from student s, LECTURE l, professor p, subject sb 
 where s.STUDNO=l.STUDNO and l.SUBJECT_CODE =sb.SUBJECT_CODE and   p.PROFNO = sb.PROFNO;

-- A문자열  || B문자열  => 연결

select (studno || ' ' || name) name from student;

class StudentDAO

	package java21_oracle.menu;
	
	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.util.ArrayList;
	
	import common.db.DBConnectManager;
	
	public class StudentDAO {
		
		private static StudentDAO dao;
		private StudentDAO(){
			
		}
		public static StudentDAO getInstance(){
			if(dao==null){
				dao=new StudentDAO();
			}
			return dao;
		}
		
		public ArrayList<String> listStudent(){
			ArrayList<String> items=new ArrayList<>();
			Connection conn=null;
			PreparedStatement pstmt=null;
			ResultSet rs=null;
			try{
				conn =DBConnectManager.oracleConn();
				String sql="select (studno || ' ' || name) name from student ";
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				while(rs.next()){
					items.add(rs.getString("name"));
				}
						
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBConnectManager.close(null, pstmt, rs);
			}
			return items;
		}
		
		
	
		
		
	}

 

class StudentDTO

package java21_oracle.menu;

public class StudentDTO {

	private int studno;
	private String name;
	private int grade; //학년
	private String jumin;
	private String tel;
	private int deptno1;
	private int deptno2;
	private int profno;
	
	public StudentDTO() {
		
	}

	public StudentDTO(int studno, String name, int grade, String jumin, String tel, int deptno1, int deptno2,
			int profno) {
		super();
		this.studno = studno;
		this.name = name;
		this.grade = grade;
		this.jumin = jumin;
		this.tel = tel;
		this.deptno1 = deptno1;
		this.deptno2 = deptno2;
		this.profno = profno;
	}

	public int getStudno() {
		return studno;
	}

	public void setStudno(int studno) {
		this.studno = studno;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getGrade() {
		return grade;
	}

	public void setGrade(int grade) {
		this.grade = grade;
	}

	public String getJumin() {
		return jumin;
	}

	public void setJumin(String jumin) {
		this.jumin = jumin;
	}

	public String getTel() {
		return tel;
	}

	public void setTel(String tel) {
		this.tel = tel;
	}

	public int getDeptno1() {
		return deptno1;
	}

	public void setDeptno1(int deptno1) {
		this.deptno1 = deptno1;
	}

	public int getDeptno2() {
		return deptno2;
	}

	public void setDeptno2(int deptno2) {
		this.deptno2 = deptno2;
	}

	public int getProfno() {
		return profno;
	}

	public void setProfno(int profno) {
		this.profno = profno;
	}

	@Override
	public String toString() {
		return "StudentDTO [studno=" + studno + ", name=" + name + ", grade=" + grade + ", jumin=" + jumin + ", tel="
				+ tel + ", deptno1=" + deptno1 + ", deptno2=" + deptno2 + ", profno=" + profno + "]";
	}
	
	
	
	
}

 

class TabbedPaneExam

package java21_oracle.menu;

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

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.JTabbedPane;
import javax.swing.JScrollPane;
import javax.swing.JList;
import javax.swing.AbstractListModel;
import javax.swing.ScrollPaneConstants;
import javax.swing.event.ListSelectionListener;
import javax.swing.event.ListSelectionEvent;

public class TabbedPaneExam extends JFrame {

	private JPanel contentPane;
	private JList list;

	private StudentDAO studentDAO;
	
	
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					TabbedPaneExam frame = new TabbedPaneExam();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}


	public TabbedPaneExam() {
		
		
		
		
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 794, 665);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		JTabbedPane tabbedPane = new JTabbedPane(JTabbedPane.TOP);
		tabbedPane.setBounds(194, 87, 552, 437);
		contentPane.add(tabbedPane);
		
		JPanel panel = new JPanel();
		tabbedPane.addTab("학생정보", null, panel, null);
		
		JPanel panel_1 = new JPanel();
		tabbedPane.addTab("수강정보", null, panel_1, null);
		
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(26, 96, 118, 428);
		contentPane.add(scrollPane);
		
		
		
		studentDAO=StudentDAO.getInstance();
		ArrayList<String> studentList=studentDAO.listStudent();
		//ArrayList 를 JList에 입력
		//toArray ArrayList 를 배열로 변환 
		list = new JList(studentList.toArray());
		list.addListSelectionListener(new ListSelectionListener() {
			public void valueChanged(ListSelectionEvent e) {
				
				if(e.getValueIsAdjusting()){
					System.out.println(list.getSelectedValue());
				}
				
			}
		});
		
		
		
		
		
		scrollPane.setViewportView(list);
	}
	
	
	
	
	
}