[java] PreparedStatement

PreparedStatement 메소드를 사용하여 sql문 실행하는 성적입력 프로그램 만들기

① Score : 사용자의 입력값을 저장하는 클래스 

② ScoreDAO :  Data Access Object. SQL문을 작성하여 데이터를 처리하는 클래스. - PreparedStatement 이용

③ ScoreDTO : Data Transfer Object. 데이터베이스에 데이터를 전달하는 역할을 하는 클래스

④ ScoreMain : 메인문

① Score : 사용자가 입력하는 값을 저장할 클래스

import java.util.Iterator;

import java.util.List;

import java.util.Scanner;

public class Score { //데이터 입력

Scanner sc = new Scanner(System.in);

ScoreDAO dao = new ScoreDAO();

//입력

public void insert(){

try {

ScoreDTO dto = new ScoreDTO();//사용자 입력데이터를 담을 dto 객체 생성

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

dto.setHak(sc.next());

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

dto.setName(sc.next());

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

dto.setKor(sc.nextInt());

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

dto.setEng(sc.nextInt());

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

dto.setMat(sc.nextInt());

//DAO에 입력된 값(dto)을 전달해서 쿼리 실행

int result = dao.insertData(dto);

if(result!=0){

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

}else{

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

}

} catch (Exception e) {

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

}

}

//수정

public void update(){

try {

ScoreDTO dto = new ScoreDTO();

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

dto.setHak(sc.next());

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

dto.setKor(sc.nextInt());

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

dto.setEng(sc.nextInt());

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

dto.setMat(sc.nextInt());

int result = dao.updateData(dto);

if(result!=0){

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

}else{

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

}

} catch (Exception e) {

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

}

}

//삭제

public void delete(){

try {

String delhak;

System.out.print("삭제할 학번? ");

delhak = sc.next();

int result = dao.deleteData(delhak);

if(result!=0){

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

}else{

System.out.println("삭제 실패!");

}

} catch (Exception e) {

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

}

}

//전체출력

public void selectAll(){

List<ScoreDTO> lists = dao.getList(); //리스트값이 반환되면 lists에 저장

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

//lists 출력

while(it.hasNext()){

ScoreDTO dto = it.next();

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

}

}

//이름검색

public void searchName(){

try {

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

String searchName = sc.next();

List<ScoreDTO> lists = dao.getList(searchName);

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

//lists 출력

while(it.hasNext()){

ScoreDTO dto = it.next();

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

}

} catch (Exception e) {

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

}

}

//학번검색

public void searchHak(){

try {

System.out.print("검색할 학번? : ");

String searchHak = sc.next();

ScoreDTO dto = dao.searchHak(searchHak);

if(dto!=null){

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

}

} catch (Exception e) {

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

}

}

}

② ScoreDAO : Data Access Object. SQL문을 작성하여 데이터를 처리하는 클래스. - PreparedStatement 이용 PreparedStatement : sql문을 입력받으면 먼저 유효한 쿼리인지 확인한 후 statement에 할당한다. SQL 문 쓰는 클래스를 별도로 생성

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

import com.db.DBConn;

public class ScoreDAO {

//1.insert

public int insertData(ScoreDTO dto){

//DTO를 전달받아 입력 진행. dto는 전달만 해주고 사라짐

int result = 0;

Connection conn = DBConn.getConnection();

PreparedStatement pstmt = null;

String sql;

try {

//try문에서 문제발생시 sql문 자체의 문제거나, 실행시 문제

sql = "insert into score (hak, name, kor, eng, mat) ";

sql += "values (?,?,?,?,?)"; //인수의 개수만큼 물음표로 넣음. 정해진 기호

pstmt = conn.prepareStatement(sql);

// prepareStatement: sql문을 미리 가져가서 유효한 쿼리인지 확인한 뒤 pstmt에 넣음.

// 다만 데이터는 없는 상태.

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

//Sets the designated parameter to the given Java String value

//sql문의 첫번째 인덱스의 물음표 자리에 dto의 hak 입력

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

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

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

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

result = pstmt.executeUpdate();//PreparedStatement에서 이미 검사를 진행했으므로 매개변수 없이 실행

pstmt.close();

} catch (Exception e) {

try { pstmt.close();} catch (Exception e2) {} // try문에서 오류 발생시 pstmt 연결이 끊긴 경우

try { DBConn.close();} catch (Exception e2) {} // try문에서 오류 발생시 디비연결이 끊긴 경우

}

return result;

}

//2.update

public int updateData(ScoreDTO dto){

int result = 0;

Connection conn = DBConn.getConnection();

PreparedStatement pstmt = null;

String sql;

try {

sql = "update score set kor=?, eng=?, mat=? ";

sql += "where hak=?";

pstmt = conn.prepareStatement(sql);

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

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

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

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

result = pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

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

}

return result;

}

//3.delete

public int deleteData(String hak){//삭제는 dto 전부 받을 필요없음. 학번 string하나만 입력받아도 문제없음.

int result = 0;

Connection conn = DBConn.getConnection();

PreparedStatement pstmt = null;

String sql;

try {

sql = "delete 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;

}

//4.selectAll : dto가 담길 리스트가 필요하다.

//쿼리에 해당하는 레코드가 dto에 담기고 이 dto들을 리스트에 담아서 반환값을 리스트로 설정

public List<ScoreDTO> getList(){

List<ScoreDTO> lists = new ArrayList<ScoreDTO>();

Connection conn = DBConn.getConnection();

PreparedStatement pstmt = null;

ResultSet rs=null; //select문의 경우 테이블을 반환하기 때문에 RESULTSET을 사용해야 한다.

String sql;

try {

sql = "select hak, name, kor, eng, mat, ";

sql += "(kor+eng+mat) tot, (kor+eng+mat)/3 avg, ";

sql +="rank() over (order by (kor+eng+mat) desc) rank ";

sql +="from score";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

//테이블에 레코드가 있을때까지 반복

while(rs.next()){

//레코드에 있는 내용을 dto에 입력

ScoreDTO dto = new ScoreDTO();

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

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

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

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

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

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

dto.setAvg(rs.getInt("avg"));

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

//dto를 리스트에 추가

lists.add(dto);

}

rs.close();

pstmt.close();

} catch (Exception e) {

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

}

return lists;

}

//5.searchname

public List<ScoreDTO> getList(String searchname){

List<ScoreDTO> lists = new ArrayList<ScoreDTO>();

Connection conn = DBConn.getConnection();

PreparedStatement pstmt = null;

ResultSet rs=null;

String sql;

try {

sql = "select hak, name, kor, eng, mat, ";

sql += "(kor+eng+mat) tot, (kor+eng+mat)/3 avg ";

sql +="from score where name like ?";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1,searchname + "%");

rs = pstmt.executeQuery();

//테이블에 레코드가 있을때까지 반복

while(rs.next()){

//레코드에 있는 내용을 dto에 입력

ScoreDTO dto = new ScoreDTO();

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

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

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

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

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

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

dto.setAvg(rs.getInt("avg"));

//dto를 리스트에 추가

lists.add(dto);

}

rs.close();

pstmt.close();

} catch (Exception e) {

// TODO: handle exception

}

return lists;

}

//6.searchhak

public ScoreDTO searchHak(String hak){

Connection conn = DBConn.getConnection();

ScoreDTO dto = null;//검색한 데이터가 결과가 없을수도 있어서 객체를 null로 생성해도 됨

PreparedStatement pstmt = null;

ResultSet rs=null;

String sql;

try {

sql = "select hak, name, kor, eng, mat, ";

sql += "(kor+eng+mat) tot, (kor+eng+mat)/3 avg ";

sql +="from score where hak = ? ";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, hak);

rs = pstmt.executeQuery();

//데이터가 있거나 없거나 둘중 하나

if(rs.next()){

dto = new ScoreDTO();

//레코드에 있는 내용을 dto에 입력

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

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

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

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

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

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

dto.setAvg(rs.getInt("avg"));

}

rs.close();

pstmt.close();

} catch (Exception e) {

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

}

return dto;

}

}

③ ScoreDTO : Data Transfer Object. 데이터베이스에 데이터를 전달하는 역할을 하는 클래스 기존에는 VO를 생성해서 ScoreVO를 리스트에 담아 이 저장된 리시트를 파일에 담았다. DB를 배웠으니 접목하여 사용할 것 VO가 이제 Database 데이터를 전달하는 역할로 바뀜 -> DTO(Data Transfer Object). 데이터를 전달 해주고 사라짐

public class ScoreDTO {//Data Transfer Object

//DB에 존재하는 컬럼

private String hak;

private String name;

private int kor;

private int eng;

private int mat;

//DB에 존재하지 않는 컬럼

private int tot;

private int avg;

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

return avg;

}

public void setAvg(int avg) {

this.avg = avg;

}

public int getRank() {

return rank;

}

public void setRank(int rank) {

this.rank = rank;

}

@Override

public String toString(){

String str;

str = String.format("%5s %8s %4d %4d %4d %4d %4d %4d ",

hak, name, kor, eng, mat, tot, avg, rank);

return str;

}

}

④ ScoreMain : 메인문

import java.util.Scanner;

import com.db.DBConn;

public class ScoreMain {

public static void main(String[] args) {

Scanner sc= new Scanner(System.in);

int ch;

Score ob = new Score();

try {

while(true){

do{

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

ch = sc.nextInt();

}while(ch<1||ch>6);

switch(ch){

case 1:

ob.insert(); break;

case 2:

ob.update(); break;

case 3:

ob.delete(); break;

case 4:

ob.selectAll(); break;

case 5:

ob.searchName(); break;

case 6:

ob.searchHak(); break;

case 7:

DBConn.close();

System.exit(0);

}

}

} catch (Exception e) {

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

}

}

}