자바

sqlplus / as sysdba

-- 테이블 스페이스 생성

create tablespace 
java (테이블 스페이스 이름)
datafile 'D:\oracleDB\XE\java.dbf'  (데이터파일경로)
size 50m (초기사이즈)
autoextend on (자동증가)
next 10m (자동 증가 사이즈)
maxsize unlimited; (최대 사이즈)

 


create tablespace java datafile 'D:\oracleDB\XE\java.dbf'  size 50m autoextend on next 10m maxsize unlimited;

 

11g => 12c

-- 11g 와 호환

alter session set "_ORACLE_SCRIPT"=true;

-- 사용자 계정 만들기

create user 아이디 identified by 비번

default tablespace 테이블스페이스 이름

create user java identified by 1111 default tablespace java;


-- 유저 목록 보기

SELECT USERNAME FROM DBA_USERS

 

--  사용자에게 권한 부여

-- grant 권한 to 아이디;

-- connect : 로그인 권한

-- resource : 자원을 사용할 수 있는 권한

-- dba : db 관리자 권한

grant create view, connect, resource, dba  to java;

-- 점수 테이블
-- 토드 실행 F9
-- 실행
-- varchar , varchar2 : 최대 4000 byte
-- number  실수 값 지원
-- number() 괄호 안에 숫자를 넣으면 제한된 값
-- numer(3) 정수 3자리

create table score (

 student_no varchar2(20) primary key,
 name varchar2(20) not null,
 kor number(3) not null,    
 eng number(3) not null,
 mat number(3) not null

);

 

insert into score values('1' , 'kim' , 90, 80, 70);

select * from score;


commit;

 

 

 

 

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 ScoreList

 

package java20_oracle;

import java.awt.EventQueue;
import java.util.Vector;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

public class ScoreList extends JFrame {

	private static final long serialVersionUID = 1L;
	
	private JPanel contentPane;
	private JTable table;

	private ScoreDAO dao;
	private Vector<Object> data, col; // 테이블의 데이터와 제목 컬럼을 위한  벡터
	private JButton btnAdd;
	private JButton btnEdit;
	
	
	
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					ScoreList frame = new ScoreList();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public ScoreList() {
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 644, 496);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		

		//dao 에서 받은 데이터를  테이블 모델에 입력
		dao =ScoreDAO.getInstance();
		//테이블 헤더 구성
		col=new Vector<>();
		col.add("학번"); col.add("이름"); col.add("국어"); col.add("영어");
		col.add("수학"); col.add("총점"); col.add("평균");
		table = new JTable();
		refreshTable();
		
			
		//스크롤페인에 테이블을 붙임
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(12, 69, 604, 379);
		contentPane.add(scrollPane);
		scrollPane.setViewportView(table);
		
		btnAdd = new JButton("점수 추가");
		btnAdd.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
			
				//점수 입력 폼을 오픔함
				//점수 추가 폼을 생성할 때 현재폼의 주소를 전달함
				ScoreSave form=new ScoreSave(ScoreList.this);
				form.setVisible(true);
			}
		});
		btnAdd.setBounds(12, 10, 97, 23);
		contentPane.add(btnAdd);
		
		btnEdit = new JButton("수정/삭제");
		btnEdit.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				//선택한 행의 내용을 dto 로 저장
				int idx=table.getSelectedRow();//현재 클릭한 행의 인덱스값
				//테이블.getValueAt(행의 인덱스, 컬럼의 인덱스)
				String student_no=String.valueOf(table.getValueAt(idx, 0));
				String name=String.valueOf(table.getValueAt(idx, 1));
				int kor=Integer.valueOf(table.getValueAt(idx, 2)+"");
				int eng=Integer.valueOf(table.getValueAt(idx, 3)+"");
				int mat=Integer.valueOf(table.getValueAt(idx, 4)+"");
				
				ScoreDTO dto=new ScoreDTO(student_no, name,kor, eng, mat);
				//ScoreEdit.java 오픈 ( ScoreList의 주소와 dto 를 전달)
				ScoreEdit form =new ScoreEdit(ScoreList.this, dto);
				form.setVisible(true);
			}
		});
		btnEdit.setBounds(131, 10, 97, 23);
		contentPane.add(btnEdit);
		
	}
	
	
	public void refreshTable(){
		//테이블 모델 생성
		//dao 에서 받아온 데이터로 테이블 모델 생성
		DefaultTableModel model=new DefaultTableModel(dao.listScore(), col);
		//테이블에 데이터 모델을 입력함
		table.setModel(model);
	}
	
	
	

}

 

class ScoreSave

package java20_oracle;

import java.awt.BorderLayout;
import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextField;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

public class ScoreSave extends JFrame {

	//ScoreList 가리킬 변수 추가
	private ScoreList parent;
	
	private JPanel contentPane;
	private JTextField tfStuedentNo;
	private JTextField tfName;
	private JTextField tfKor;
	private JTextField tfEng;
	private JTextField tfMat;


	
	public ScoreSave(ScoreList parent) {
		
		this.parent=parent;
		
		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setBounds(100, 100, 485, 440);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		JLabel lblNewLabel = new JLabel("학번");
		lblNewLabel.setBounds(62, 62, 57, 15);
		contentPane.add(lblNewLabel);
		
		JLabel lblNewLabel_1 = new JLabel("이름");
		lblNewLabel_1.setBounds(62, 113, 57, 15);
		contentPane.add(lblNewLabel_1);
		
		JLabel lblNewLabel_2 = new JLabel("국어");
		lblNewLabel_2.setBounds(62, 170, 57, 15);
		contentPane.add(lblNewLabel_2);
		
		JLabel lblNewLabel_3 = new JLabel("영어");
		lblNewLabel_3.setBounds(62, 231, 57, 15);
		contentPane.add(lblNewLabel_3);
		
		JLabel lblNewLabel_4 = new JLabel("수학");
		lblNewLabel_4.setBounds(62, 291, 57, 15);
		contentPane.add(lblNewLabel_4);
		
		tfStuedentNo = new JTextField();
		tfStuedentNo.setBounds(165, 59, 116, 21);
		contentPane.add(tfStuedentNo);
		tfStuedentNo.setColumns(10);
		
		tfName = new JTextField();
		tfName.setBounds(165, 113, 116, 21);
		contentPane.add(tfName);
		tfName.setColumns(10);
		
		tfKor = new JTextField();
		tfKor.setBounds(165, 167, 116, 21);
		contentPane.add(tfKor);
		tfKor.setColumns(10);
		
		tfEng = new JTextField();
		tfEng.setBounds(165, 228, 116, 21);
		contentPane.add(tfEng);
		tfEng.setColumns(10);
		
		tfMat = new JTextField();
		tfMat.setBounds(165, 291, 116, 21);
		contentPane.add(tfMat);
		tfMat.setColumns(10);
		
		JButton btnSave = new JButton("저장");
		btnSave.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				ScoreDAO dao=ScoreDAO.getInstance();
				
				dao.insertScore(setScoreDTO());
				
				JOptionPane.showMessageDialog(ScoreSave.this, "추가 되었습니다.");

				//부모창 데이터 갱신
				parent.refreshTable();
				//현재 프레임 닫기
				dispose();
				
			}
		});
		btnSave.setBounds(107, 348, 97, 23);
		contentPane.add(btnSave);
	}
	
	
	
	//textArea 의 데이터 가져오기
	private ScoreDTO setScoreDTO(){
		
		ScoreDTO dto =new ScoreDTO();
		dto.setStudent_no(tfStuedentNo.getText());
		dto.setName(tfName.getText());
		dto.setKor(Integer.valueOf(tfKor.getText()));
		dto.setEng(Integer.valueOf(tfEng.getText()));
		dto.setMat(Integer.valueOf(tfMat.getText()));

		return dto;
	}
	
	
	
	
}



 

 

class ScoreEdit

package java20_oracle;

import java.awt.BorderLayout;
import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextField;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

public class ScoreEdit extends JFrame {

	//ScoreList 가리킬 변수 추가
	private ScoreList parent;
	private ScoreDTO dto;
	
	private JPanel contentPane;
	private JTextField tfStuedentNo;
	private JTextField tfName;
	private JTextField tfKor;
	private JTextField tfEng;
	private JTextField tfMat;


	
	public ScoreEdit(ScoreList parent, ScoreDTO dto) {
		
		this.parent=parent;
		this.dto=dto;
		
	
		
		
		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setBounds(100, 100, 485, 440);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		JLabel lblNewLabel = new JLabel("학번");
		lblNewLabel.setBounds(62, 62, 57, 15);
		contentPane.add(lblNewLabel);
		
		JLabel lblNewLabel_1 = new JLabel("이름");
		lblNewLabel_1.setBounds(62, 113, 57, 15);
		contentPane.add(lblNewLabel_1);
		
		JLabel lblNewLabel_2 = new JLabel("국어");
		lblNewLabel_2.setBounds(62, 170, 57, 15);
		contentPane.add(lblNewLabel_2);
		
		JLabel lblNewLabel_3 = new JLabel("영어");
		lblNewLabel_3.setBounds(62, 231, 57, 15);
		contentPane.add(lblNewLabel_3);
		
		JLabel lblNewLabel_4 = new JLabel("수학");
		lblNewLabel_4.setBounds(62, 291, 57, 15);
		contentPane.add(lblNewLabel_4);
		
		tfStuedentNo = new JTextField();
		tfStuedentNo.setBounds(165, 59, 116, 21);
		contentPane.add(tfStuedentNo);
		tfStuedentNo.setColumns(10);
		
		tfName = new JTextField();
		tfName.setBounds(165, 113, 116, 21);
		contentPane.add(tfName);
		tfName.setColumns(10);
		
		tfKor = new JTextField();
		tfKor.setBounds(165, 167, 116, 21);
		contentPane.add(tfKor);
		tfKor.setColumns(10);
		
		tfEng = new JTextField();
		tfEng.setBounds(165, 228, 116, 21);
		contentPane.add(tfEng);
		tfEng.setColumns(10);
		
		tfMat = new JTextField();
		tfMat.setBounds(165, 291, 116, 21);
		contentPane.add(tfMat);
		tfMat.setColumns(10);
		
		JButton btnSave = new JButton("저장");
		btnSave.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				ScoreDAO dao=ScoreDAO.getInstance();
				
				dao.insertScore(setScoreDTO());
				
				JOptionPane.showMessageDialog(ScoreEdit.this, "추가 되었습니다.");

				//부모창 데이터 갱신
				parent.refreshTable();
				//현재 프레임 닫기
				dispose();
				
			}
		});
		btnSave.setBounds(107, 348, 97, 23);
		contentPane.add(btnSave);
		
		
		//텍스트 필드에 dto의 값을 입력함
		if(dto!=null){
			tfStuedentNo.setText(dto.getStudent_no());
			tfName.setText(dto.getName());
			tfKor.setText(dto.getKor()+"");
			tfEng.setText(dto.getEng()+"");
			tfMat.setText(dto.getMat()+"");
		}else{
			JOptionPane.showMessageDialog(parent, "잘 못 선택 하셨습니다.");
			dispose();
		}
	}
	
	
	
	//textArea 의 데이터 가져오기
	private ScoreDTO setScoreDTO(){
		
		ScoreDTO dto =new ScoreDTO();
		dto.setStudent_no(tfStuedentNo.getText());
		dto.setName(tfName.getText());
		dto.setKor(Integer.valueOf(tfKor.getText()));
		dto.setEng(Integer.valueOf(tfEng.getText()));
		dto.setMat(Integer.valueOf(tfMat.getText()));

		return dto;
	}
	
	
	
	
}



 

class ScoreDAO

package java20_oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import common.db.DBConnectManager;

public class ScoreDAO {

	private static ScoreDAO dao;
	
	private ScoreDAO(){
		
	}
	
	public static ScoreDAO getInstance(){
		if(dao==null){
			dao=new ScoreDAO();
		}
		return dao;
	}
	
	
	//점수 목록을 벡터에 저장하여 리턴
	public Vector<Object> listScore(){
		Vector<Object> items=new Vector<Object>();
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=DBConnectManager.oracleConn();
			String sql="select student_no, name , kor, eng, mat , (kor+eng+mat) as tot, "
					+ " ((kor+eng+mat)/3.0) as avg from score ";
			
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				//JTable 이 Vecotr 만 들어간다.
				Vector<Object> row=new Vector<>();
				row.add(rs.getString("student_no"));
				row.add(rs.getString("name"));
				row.add(rs.getInt("kor"));
				row.add(rs.getInt("eng"));
				row.add(rs.getInt("mat"));
				row.add(rs.getInt("tot"));
				row.add(rs.getDouble("avg"));
				items.add(row);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt, rs);
		}
		return items;
	}
	
	
	public int insertScore(ScoreDTO dto){
		int result=0;
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=DBConnectManager.oracleConn();
			String sql ="insert into score values( ? , ? , ? , ? , ?)";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dto.getStudent_no());
			pstmt.setString(2, dto.getName());
			pstmt.setInt(3, dto.getKor());
			pstmt.setInt(4, dto.getEng());
			pstmt.setInt(5, dto.getMat());
			result=pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt);
		}
		return result;
	}
	
	
	
	
}

 

 

class ScoreDTO

package java20_oracle;

public class ScoreDTO {

	private String student_no;
	private String name;
	private int kor;
	private int eng;
	private int mat;
	private int tot;
	private double avg;
	
	public ScoreDTO() {
		
	}

	public ScoreDTO(String student_no, String name, int kor, int eng, int mat) {
		super();
		this.student_no = student_no;
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.mat = mat;
	}

	public String getStudent_no() {
		return student_no;
	}

	public void setStudent_no(String student_no) {
		this.student_no = student_no;
	}

	public String getName() {
		return name;
	}

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

	public int getKor() {
		return kor;
	}

	public void setKor(int kor) {
		this.kor = kor;
	}

	public int getEng() {
		return eng;
	}

	public void setEng(int eng) {
		this.eng = eng;
	}

	public int getMat() {
		return mat;
	}

	public void setMat(int mat) {
		this.mat = mat;
	}

	public int getTot() {
		return tot;
	}

	public void setTot(int tot) {
		this.tot = tot;
	}

	public double getAvg() {
		return avg;
	}

	public void setAvg(double avg) {
		this.avg = avg;
	}

	@Override
	public String toString() {
		return "ScoreDTO [student_no=" + student_no + ", name=" + name + ", kor=" + kor + ", eng=" + eng + ", mat="
				+ mat + ", tot=" + tot + ", avg=" + avg + "]";
	}
	
	
	
	
}

 

 

 

 

 

 

about author

PHRASE

Level 60  머나먼나라

인생은 반복된 생활이다. 좋은 일을 반복하면 좋은 인생을, 나쁜 일을 반복하면 불행한 인생을 보내는 것이다. - W.NL.영안-

댓글 ( 1)

댓글 남기기

작성
  •    
  •