오라클 실습용 데이터  

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;

 

-- 학번, 이름, 학과, 지도교수, 전화번호
-- 교수이름 => 교수테이블, 학과이름 => 학과테이블

select studno, s.name sname, dname, p.name pname, tel
from student s, professor p, department d
where s.profno=p.profno and s.deptno1=d.deptno order by sname;

 

-- inner join(내부조인) : 양쪽 테이블에 자료가 있는 경우
-- outer join(외부조인 ) : 짝이 맞지 않는 경우

 

select studno, s.name sname, dname, p.name pname, tel
from student s, professor p, department d
where s.profno=p.profno(+) and s.deptno1=d.deptno 
  and studno=9711;

 

-- 새로운 컬럼 추가
-- alter table 테이블이름 add 컬럼 자료형(사이즈);
-- alternate(변경)
 

alter table student add imag_path varchar2(500);

commit;

select * from student;

 

oracle.prop

url=jdbc:oracle:thin:@localhost:1521:xe
driver=oracle.jdbc.OracleDriver
id=java
password=1111

 

 class DBConnectManager

package common.db;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class DBConnectManager {

	public static Connection dbConn(){
		Connection conn=null;
		try{
			//db 접속 정보가 저장되 db.prop 파일을 로딩
			FileInputStream fis =new FileInputStream("db.prop");
			Properties prop=new Properties();
			prop.load(fis);
			String url=prop.getProperty("url");
			String id=prop.getProperty("id");
			String password=prop.getProperty("password");

			//mysql에 접속 처리
			conn=DriverManager.getConnection(url, id, password);
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}
	
	
	public static Connection oracleConn(){
		Connection conn=null;
		try{
			//db 접속 정보가 저장되 db.prop 파일을 로딩
			FileInputStream fis =new FileInputStream("oracle.prop");
			Properties prop=new Properties();
			prop.load(fis);
			String url=prop.getProperty("url");
			String id=prop.getProperty("id");
			String password=prop.getProperty("password");

			//mysql에 접속 처리
			conn=DriverManager.getConnection(url, id, password);
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}
	
	
	
	public static void close(Connection conn){
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(PreparedStatement pstmt){
		try {
			if(pstmt!=null)pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(ResultSet rs){
		try {
			if(rs!=null)rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(Connection conn, PreparedStatement pstmt ){
				
		try{
			if(pstmt!=null)pstmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	

	public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){

		try{
			if(rs!=null)rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		try{
			if(pstmt!=null)pstmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	
	
	public static void close(Connection conn, Statement stmt, ResultSet rs){

		try{
			if(rs!=null)rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		try{
			if(stmt!=null)stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	
	
	
	
}

 

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;
	
	private String dname;
	private String pname;
	
	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 String getDname() {
		return dname;
	}

	public void setDname(String dname) {
		this.dname = dname;
	}

	public String getPname() {
		return pname;
	}

	public void setPname(String pname) {
		this.pname = pname;
	}

	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 + ", dname=" + dname
				+ ", pname=" + pname + "]";
	}


	
	
	
	
	
}

 

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;
		}
		
		
		//학생 상세 정보
		public StudentDTO detailStudent(int studno){
			StudentDTO dto =new StudentDTO();
			Connection conn=null;
			PreparedStatement pstmt=null;
			ResultSet rs=null;
			try{
				conn=DBConnectManager.oracleConn();
				String sql="select studno, s.name sname, dname, p.name pname, tel "
						+ " from student s, professor p, department d "
						+ " where s.profno=p.profno(+) and s.deptno1=d.deptno and studno=?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, studno);
				rs=pstmt.executeQuery();
				if(rs.next()){
					dto.setStudno(rs.getInt("studno"));
					dto.setName(rs.getString("sname"));
					dto.setDname(rs.getString("dname"));
					dto.setPname(rs.getString("pname"));
					dto.setTel(rs.getString("tel"));
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBConnectManager.close(conn, pstmt,rs);
			}
			
			return dto;
		}
	
		
		
}

 

class TabbedPaneExam

 

package java21_oracle.menu;

import java.awt.EventQueue;
import java.awt.Image;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.util.ArrayList;

import javax.imageio.ImageIO;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTabbedPane;
import javax.swing.JTextField;
import javax.swing.border.EmptyBorder;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;

public class TabbedPaneExam extends JFrame {

	private JPanel contentPane;
	private JList list;

	private StudentDAO studentDAO;
	private JTextField tfName;
	private JTextField tfStudentNo;
	private JTextField tfMajor;
	private JTextField tfProfessor;
	private JTextField tfTel;
	
	
	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() {
		studentDAO=StudentDAO.getInstance();
		
		
		
		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);
		panel.setLayout(null);
		
		JLabel lblImage = new JLabel("사진");
		lblImage.setBounds(12, 23, 120, 140);
		panel.add(lblImage);
		
		JLabel lblNewLabel_1 = new JLabel("이름");
		lblNewLabel_1.setBounds(200, 23, 57, 15);
		panel.add(lblNewLabel_1);
		
		JLabel lblNewLabel_2 = new JLabel("학번");
		lblNewLabel_2.setBounds(200, 65, 57, 15);
		panel.add(lblNewLabel_2);
		
		JLabel lblNewLabel_3 = new JLabel("학과");
		lblNewLabel_3.setBounds(200, 108, 57, 15);
		panel.add(lblNewLabel_3);
		
		JLabel label = new JLabel("지도교수");
		label.setBounds(200, 174, 57, 15);
		panel.add(label);
		
		JLabel lblNewLabel_4 = new JLabel("전화번호");
		lblNewLabel_4.setBounds(200, 231, 57, 15);
		panel.add(lblNewLabel_4);
		
		JButton btnSave = new JButton("저장");
		btnSave.setBounds(200, 298, 97, 23);
		panel.add(btnSave);
		
		tfName = new JTextField();
		tfName.setBounds(284, 23, 116, 21);
		panel.add(tfName);
		tfName.setColumns(10);
		
		tfStudentNo = new JTextField();
		tfStudentNo.setEditable(false);
		tfStudentNo.setBounds(284, 62, 116, 21);
		panel.add(tfStudentNo);
		tfStudentNo.setColumns(10);
		
		tfMajor = new JTextField();
		tfMajor.setBounds(284, 105, 116, 21);
		panel.add(tfMajor);
		tfMajor.setColumns(10);
		
		tfProfessor = new JTextField();
		tfProfessor.setBounds(284, 171, 116, 21);
		panel.add(tfProfessor);
		tfProfessor.setColumns(10);
		
		tfTel = new JTextField();
		tfTel.setBounds(284, 228, 116, 21);
		panel.add(tfTel);
		tfTel.setColumns(10);
		
		JButton btnImage = new JButton("사진등록");
		btnImage.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				JFileChooser fc =new JFileChooser();//파일 선택 다이얼로그
				int result=fc.showOpenDialog(TabbedPaneExam.this);
				if(result==JFileChooser.APPROVE_OPTION){//열기 버튼을 누르면 실행
					File file=fc.getSelectedFile();//선택한 파일 정보
					try{
						//선택한 이미지를 라벨의 아이콘으로 설정
						ImageIcon icon =new ImageIcon(ImageIO.read(file));
						//아이콘에서 이미지를 리턴받음
						Image imageSrc =icon.getImage();
						//이미지를 resize 한 후
						Image imageNew=imageSrc.getScaledInstance(120, 140, Image.SCALE_AREA_AVERAGING);
						//아이콘을 변경시킴
						icon=new ImageIcon(imageNew);
						//라벨에 아이콘을 적용하
						lblImage.setIcon(icon);
					}catch(Exception e2){
						e2.printStackTrace();
					}
				}
			}
		});
		btnImage.setBounds(24, 211, 97, 23);
		panel.add(btnImage);
		
		JPanel panel_1 = new JPanel();
		tabbedPane.addTab("수강정보", null, panel_1, null);
		
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(26, 96, 118, 428);
		contentPane.add(scrollPane);
				
		
		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());
					//getSelectedValue() 선택한 값
					//split("구분자") 문자열을 구분자를 기준으로 분리시켜 배열을 리턴
					String[] arr=list.getSelectedValue().toString().split(" ");
					System.out.println(arr[0]);
					
					StudentDTO dto=studentDAO.detailStudent(Integer.parseInt(arr[0]));
					if(dto!=null){
						tfName.setText(dto.getName());
						tfStudentNo.setText(dto.getStudno()+"");
						tfMajor.setText(dto.getDname() +"");
						tfProfessor.setText(dto.getPname());
						tfTel.setText(dto.getTel());
					}else{
						tfName.setText("");
						tfStudentNo.setText("");
						tfMajor.setText("");
						tfProfessor.setText("");
						tfTel.setText("");
					}

				}						
				
			}
		});
		
		
		scrollPane.setViewportView(list);
	}
	
	
}