626
No
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;
select * from score;
update score set name ='김철수', kor=99, eng=88, mat=75
where student_no=1 ;
commit;
--레코드 삭제
delete from score where student_no=4;
commit;
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 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 + "]";
}
}
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());
//update 성공한 레코드 갯수가 result 에 리턴
result=pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt);
}
return result;
}
public int updateScore(ScoreDTO dto){
int result=0;
Connection conn=null; // db 접속
PreparedStatement pstmt=null; //
try{
conn=DBConnectManager.oracleConn();
String sql="update score set name =?, kor=?, eng=?, mat=? where student_no=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, dto.getName());
pstmt.setInt(2, dto.getKor());
pstmt.setInt(3, dto.getEng());
pstmt.setInt(4, dto.getMat());
pstmt.setString(5, dto.getStudent_no());
result=pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt);
}
return result;
}
public int deleteScore(String student_no){
int result=0;
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn=DBConnectManager.oracleConn();
String sql ="delete from score where student_no=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, student_no);
result=pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt);
}
return result;
}
}
class ScoreList
package java20_oracle;
import java.awt.EventQueue;
import java.util.Vector;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
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() {
@SuppressWarnings("unused")
public void actionPerformed(ActionEvent e) {
//선택한 행의 내용을 dto 로 저장
Integer idx=table.getSelectedRow();//현재 클릭한 행의 인덱스값
if(idx==-1){
JOptionPane.showMessageDialog(ScoreList.this, "테이블의 데이터를 선택해 주세요.");
return;
}
//테이블.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;
private JButton btnDelete;
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.updateScore(setScoreDTO());
JOptionPane.showMessageDialog(ScoreEdit.this, "수정 되었습니다.");
//부모창 데이터 갱신
parent.refreshTable();
//현재 프레임 닫기
dispose();
}
});
btnSave.setBounds(62, 348, 97, 23);
contentPane.add(btnSave);
btnDelete = new JButton("삭제");
btnDelete.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
int response=JOptionPane.showConfirmDialog(ScoreEdit.this, "삭제하시겠습니까?");
if(response==JOptionPane.YES_OPTION){
//학번 조회
ScoreDAO dao=ScoreDAO.getInstance();
dao.deleteScore(tfStuedentNo.getText()); //레코드 삭제
//ScoreList 의 테이블 삭제
parent.refreshTable();
JOptionPane.showMessageDialog(ScoreEdit.this, "삭제 되었습니다.");
dispose();//현재 창을 닫음
}
}
});
btnDelete.setBounds(220, 348, 97, 23);
contentPane.add(btnDelete);
//텍스트 필드에 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;
}
}
댓글 ( 4)
댓글 남기기