PreparedStatement - 자바에서 오라클 쿼리 실행

===========================================================ScoreDTO======================================================================

package com.score2;

public class ScoreDTO {

private String hak, name, birth;

private int kor, eng, mat, tot, ave, rank;

public String getHak() {

return hak;

}

public void setHak(String hak) {

this.hak = hak;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getBirth() {

return birth;

}

public void setBirth(String birth) {

this.birth = birth;

}

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 int getAve() {

return ave;

}

public void setAve(int ave) {

this.ave = ave;

}

public int getRank() {

return rank;

}

public void setRank(int rank) {

this.rank = rank;

}

}

===========================================================ScoreDAO======================================================================

package com.score2;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import com.util.DBConn;

public class ScoreDAO {

private Connection conn=DBConn.getConnection();

public int insertScore(ScoreDTO dto) {

int result=0;

PreparedStatement pstmt=null;

String sql;

try {

sql="INSERT INTO score(hak, name, birth, kor, eng,mat) VALUES(?,?,?,?,?,?)";

pstmt=conn.prepareStatement(sql); 

// 선 컴파일 후 값적용

// 위의 쿼리가 올바른지 먼저 컴파일 한다는 이야기. 값적용은 물음표에 값을 셋팅한 후에 'result='를 써서 나중에 한다.

//오라클의 인덱스는 1부터, 쿼리의 물음표에 들어갈 데이터 순서대로 번호를 매긴다.

pstmt.setString(1, dto.getHak());  

pstmt.setString(2, dto.getName());

pstmt.setString(3, dto.getBirth());

pstmt.setInt(4, dto.getKor());

pstmt.setInt(5, dto.getEng());

pstmt.setInt(6, dto.getMat());

result=pstmt.executeUpdate(); 

// SELECT문을 제외한 모든 문에서는 result에executeUpdate로 DB작업을 한다.

// 이거 안써주면 return 할 값이 없으므로 꼭넣어줘야한다.

// 괄호안에 sql이 없는것이 다르다.

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return result;

}

public int updateScore(ScoreDTO dto){

int result=0;

PreparedStatement pstmt=null;

String sql;

try {

sql="UPDATE score SET name=?,birth=?,kor=?,eng=?,mat=? WHERE hak=?";

pstmt=conn.prepareStatement(sql);

pstmt.setString(1, dto.getName());

pstmt.setString(2, dto.getBirth());

pstmt.setInt(3, dto.getKor());

pstmt.setInt(4, dto.getEng());

pstmt.setInt(5, dto.getMat());

pstmt.setString(6, dto.getHak());

result=pstmt.executeUpdate();

// rs.close(); SELECT 문이 아니기 때문에 resultSet을 닫을 필요없다. 쓰지도 않았다.

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return result;

}

public int deleteScore(String hak){

int result=0;

PreparedStatement pstmt=null;

String sql;

try{

sql="DELETE FROM score WHERE hak=?";

pstmt=conn.prepareStatement(sql);

pstmt.setString(1, hak);

result=pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return result;

}

public ScoreDTO readScore(String hak){ // hak은 pk라 유일한 값을 가져서 어레이리스트 안씀

ScoreDTO dto=null;

PreparedStatement pstmt=null;

ResultSet rs=null;

StringBuffer sql=new StringBuffer();

try{

sql.append("SELECT hak, name, birth, kor, eng,mat,(kor+eng+mat) tot, ");

sql.append("(kor+eng+mat)/3 ave FROM score WHERE hak=?");

pstmt=conn.prepareStatement(sql.toString());

pstmt.setString(1, hak);

rs=pstmt.executeQuery();

//기본키 조건에 만족하는 데이터는 최대 하나이므로 WHILE() 문을 사용할 필요가 없다.

// 조건에 만족하는 데이터가 없으면 dto는 null

if(rs.next()){

dto=new ScoreDTO();

dto.setHak(rs.getString("hak"));

dto.setName(rs.getString("name"));

dto.setBirth(rs.getString("birth"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

dto.setTot(rs.getInt("tot"));

dto.setAve(rs.getInt("ave"));

}

rs.close();

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return dto;

}

public ArrayList<ScoreDTO> listScore() {

ArrayList<ScoreDTO> list=new ArrayList<ScoreDTO>();

PreparedStatement pstmt=null;

ResultSet rs=null; //SELECT 문이기 때문에 ResultSet으로 리턴값을 선언하였다.

StringBuffer sb=new StringBuffer();

try {

sb.append("SELECT hak, name,"); // append 뒤에 계속해서 입력된 데이터를 붙여준다.

sb.append(" TO_CHAR(birth,'YYYY-MM-DD') birth, ");

sb.append(" kor, eng, mat, ");

sb.append(" (kor+eng+mat) tot,");

sb.append(" (kor+eng+mat)/3 ave,");

sb.append(" RANK() OVER(ORDER BY(kor+eng+mat) DESC) rank");

sb.append(" FROM score"); //실수로 괄호안에 ;세미콜론 붙이면 안된다.

pstmt=conn.prepareStatement(sb.toString());

// excuteQuery() : SELECT 문

rs=pstmt.executeQuery();

while(rs.next()){ // 전체출력을 위해서 반복문 while을 사용함

ScoreDTO dto=new ScoreDTO();

dto.setHak(rs.getString(1));

// dto.setHak(rs.getString("hak")); 오라클의 인데스는 1부터 시작하여 1을 적어줬다.

dto.setName(rs.getString(2));

// dto.setName(rs.getString("name")); 

dto.setBirth(rs.getString("birth"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

dto.setTot(rs.getInt("tot"));

dto.setAve(rs.getInt("ave"));

dto.setRank(rs.getInt("rank"));

list.add(dto);

}

rs.close();

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return list;

}

public ArrayList<ScoreDTO> listNameScore(String name) { // name은 primary key가 아니기 때문에 중복될 수도 있으므로 ArrayList로 불러옴

ArrayList<ScoreDTO> list=new ArrayList<ScoreDTO>();

PreparedStatement pstmt=null;

ResultSet rs=null;

StringBuffer sb=new StringBuffer();

try {

sb.append("SELECT hak, name,");

sb.append(" TO_CHAR(birth,'YYYY-MM-DD') birth, ");

sb.append(" kor, eng, mat, ");

sb.append(" (kor+eng+mat) tot,");

sb.append(" (kor+eng+mat)/3 ave");

sb.append(" FROM score"); 

sb.append(" WHERE name LIKE ? || '%'"); // % => 한글자만 동일해도  출력됨.   || 오라클 문자결합

pstmt=conn.prepareStatement(sb.toString());

pstmt.setString(1, name);

rs=pstmt.executeQuery();

while(rs.next()){ // 전체출력을 위해서 반복문 while을 사용함

ScoreDTO dto=new ScoreDTO();

dto.setHak(rs.getString(1));

// dto.setHak(rs.getString("hak")); 오라클의 인데스는 1부터 시작하여 1을 적어줬다. 컬럼명을 적어줘도 무방

dto.setName(rs.getString(2));

dto.setBirth(rs.getString("birth"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

dto.setTot(rs.getInt("tot"));

dto.setAve(rs.getInt("ave"));

list.add(dto);

}

rs.close();

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return list;

}

}

===========================================================Score======================================================================

package com.score2;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.Scanner;

public class Score {

private ScoreDAO dao = new ScoreDAO();

private Scanner sc = new Scanner(System.in);

public void insertData() {

ScoreDTO dto = new ScoreDTO();

try {

System.out.println("\n >>> 자료 입력 <<<");

System.out.print("학번?");

dto.setHak(sc.next());

System.out.print("이름?");

dto.setName(sc.next());

System.out.print("생년월일[yyyy-mm-dd]?");

dto.setBirth(sc.next());

System.out.print("국어?");

dto.setKor(sc.nextInt());

System.out.print("영어?");

dto.setEng(sc.nextInt());

System.out.print("수학?");

dto.setMat(sc.nextInt());

int n = dao.insertScore(dto); // Scanner로 셋팅한 ScoreDTO값을  dao로 넘겨줌

// 오라클에서 한행의 데이터가 입력,업데이트, 삭제를 당하면 데이터를 수정한 행의 갯수만큼 숫자를 넘겨준다.

if (n == 1) 

System.out.println("추가 성공!!!");

else

System.out.println("추가 실패!!!!");

System.out.println();

} catch (Exception e) {

System.out.println("입력 오류!!!");

}

}

public void updateData() {

ScoreDTO dto = new ScoreDTO();

try {

System.out.println("\n >>> 자료 입력 <<<");

System.out.print("학번?");

dto.setHak(sc.next());

System.out.print("이름?");

dto.setName(sc.next());

System.out.print("생년월일[yyyy-mm-dd]?");

dto.setBirth(sc.next());

System.out.print("국어?");

dto.setKor(sc.nextInt());

System.out.print("영어?");

dto.setEng(sc.nextInt());

System.out.print("수학?");

dto.setMat(sc.nextInt());

int n = dao.updateScore(dto);

if (n == 1)

System.out.println("수정 성공!!!");

else

System.out.println("수정 실패!!!!");

System.out.println();

} catch (Exception e) {

System.out.println("입력 오류!!!");

}

}

public void deleteHak() {

String hak;

System.out.println("\n삭제할 학번?");

hak = sc.next();

int n = dao.deleteScore(hak);

if (n >= 1) {

System.out.println("삭제성공!!!");

} else {

System.out.println("등록된것이 업습니다.");

}

this.listAllData();

}

public void searchHak() {

String hak;

System.out.println("\n검색할 학번?");

hak = sc.next();

ScoreDTO dto = dao.readScore(hak);

if (dto == null) {

System.out.println("등록된 자료가 아닙니다.");

return;

}

System.out.println(dto.getHak() + "\t" + dto.getName() + "\t" + dto.getBirth() + "\t" + dto.getKor() + "\t" + dto.getEng()

+ "\t" + dto.getMat() + "\t" + dto.getTot() + "\t" + dto.getAve());

}

public void listAllData() {

ArrayList<ScoreDTO> list = dao.listScore();

System.out.println("\n>>>전체리스트<<<");

Iterator<ScoreDTO> it = list.iterator();

while (it.hasNext()) {

ScoreDTO dto = it.next();

System.out.println(dto.getHak() + "\t" + dto.getName() + "\t"

+ dto.getBirth() + "\t" + dto.getKor() + "\t"

+ dto.getEng() + "\t" + dto.getMat() + "\t" + dto.getTot()

+ "\t" + dto.getAve() + "\t" + dto.getRank());

}

System.out.println();

}

public void listNameData() {

String name;

System.out.print("검색할 이름 ? ");

name=sc.next();

ArrayList<ScoreDTO> list = dao.listNameScore(name);

System.out.println("\n>>>이름 검색 <<<");

Iterator<ScoreDTO> it = list.iterator();

while (it.hasNext()) {

ScoreDTO dto = it.next();

System.out.println(dto.getHak() + "\t" + dto.getName() + "\t"

+ dto.getBirth() + "\t" + dto.getKor() + "\t"

+ dto.getEng() + "\t" + dto.getMat() + "\t" + dto.getTot()

+ "\t" + dto.getAve() + "\t" + dto.getRank());

}

System.out.println();

}

}

===========================================================ScoreMain=====================================================================

package com.score2;

import com.util.DBConn;

public class ScoreMain {

public static void main(String[] args) throws Exception {

char ch;

Score score=new Score();

while(true){

do{

System.out.print("1.입력 2.수정 3.삭제 4.학번검색 5.이름검색 6.전체출력 7.종료 ==>");

ch=(char)System.in.read();

System.in.skip(2);

} while (ch<'1' || ch>'7');

switch (ch){

case '1': score.insertData();break;

case '2': score.updateData();break;

case '3': score.deleteHak();break;

case '4': score.searchHak();break;

case '5': score.listNameData();break;

case '6': score.listAllData();break;

case '7': DBConn.close();

System.exit(0);

}

}

}

}