자바DB연동 / PreparedStatement 활용 예제

PreparedStatement

= 단순하게 보자면 Statement보다 좋다고 생각하면 된다. (Statement 거의 잘 안씀)

= PreparedStatement의 특징은 전처리를 하는게 특징인데

이게 뭐냐면 미리 읽어 들인다는거다. Statementet는 실행 할때마다 전체를 전부 다시 읽어야 하는데

PreparedStatement는 그럴 필요가 없이 전처리를 해놔서 나중에 또 다시 읽을때는 바뀐 부분만 읽으면 된다는 것

속도감에서 차이가 크다.

 

바로 예제문을 보면서 해석 해보자.

예제 문제 내용

16-0301 PreparedStatement 회원 문제.pdf

0.11MB

 

package db.member;

import java.sql.SQLException;
import java.util.List;

public interface MemberDAO {
	public int insertMember(MemberDTO dto) throws SQLException;
	public int updateMember(MemberDTO dto) throws SQLException;
	public int deleteMember(String id) throws SQLException;
	
	public MemberDTO readMember(String id);
	public List<MemberDTO> listMember();
	public List<MemberDTO> listMember(String name);	
}

(MemberDAO)

= 인터페이스 클래스이다. 기본 틀을 잡는다고 생각하자

= 어떤 함수들을 사용할건지 미리 정리 한다고 생각

= 인터페이스를 보면 멤버 등록, 수정, 삭제 함수를 만들것이고

= 아이디를 받아서 Member를 검색하려는것, 전체 리스트 출력과, 이름을 입력 받아서 리스트를 출력하려는 것을 알 수 있다.

= 그리고 커스텀 예외(사용자 지정 예외) 사용을 위해서 throws (내가 원하는 예외 이름) 을 지정 해준다.

 

package db.member;

public class MemberDTO {
	private String id;
	private String pwd;
	private String name;
	private String birth;
	private String email;
	private String tel;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	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 String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	
}

(MemberDTO 클래스) = 한 사람분의 정보를 담을 수 있는 클래스

- 변수 형태는 다른 곳에서 접근 못하도록 private를 주고

- 다른 클래스에서 간접적으로 수정이나 가져올 수 있도록 getter, setter를 활용해준다.

 

package db.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.List;

import db.util.DBconn;


public class MemberDAOImpl implements MemberDAO {
	private Connection conn = DBconn.getConnection();


	@Override
	public int insertMember(MemberDTO dto) throws SQLException {
		int result = 0;
		PreparedStatement pstmt = null;
		String sql;

		/*
		  - 하나의 테이블에 추가
		     INSERT INTO 테이블명(컬럼명, 컬럼명) VALUES (값, 값)
		  - 두개의 테이블에 추가
		     INSERT ALL
		       INTO 테이블명(컬럼명, 컬럼명) VALUES (값, 값)
		       INTO 테이블명(컬럼명, 컬럼명) VALUES (값, 값)
		       SELECT * FROM dual 
		*/

		// member1 테이블과 member2 테이블에 데이터 추가
		// 방법1) member1 추가 후 member2 추가
		// 방법2) member1과 member2를 한번에 추가
		try {
			
			sql = "INSERT ALL INTO member1(id, pwd, name) VALUES(?, ?, ?)"
					+ "INTO member2(id, birth, email, tel) VALUES(?, ?, ?, ?)"
					+ "SELECT * FROM dual ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, dto.getId());
			pstmt.setString(2, dto.getPwd());
			pstmt.setString(3, dto.getName());
			pstmt.setString(4, dto.getId());
			pstmt.setString(5, dto.getBirth());
			pstmt.setString(6, dto.getEmail());
			pstmt.setString(7, dto.getTel());
			
			result = pstmt.executeUpdate();
			
			
			// 트랜잭션 처리가 안되서 위험한 코드임
			// member1,2에 둘중 하나라도 추가가 안되면 커밋이 되면 안되는데 자바로 sql문 실행하면 자동 커밋이라서
			// 하나만 처리가 될 수 도있음 = 위험함.
			
		} catch (SQLIntegrityConstraintViolationException e) {
			e.printStackTrace();
			throw e;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} 
		
		finally {
			if(pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}
		
		
		return result;
	}

	@Override
	public int updateMember(MemberDTO dto) throws SQLException {
		@SuppressWarnings("unused")
		int result = 0;
		int result2 = 0;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		String sql;
		String sql2;

		// UPDATE 테이블명 SET 컬럼=값, 컬럼=값 WHERE 조건

		// id 조건에 맞는 member1 테이블과 member2 테이블에 데이터 수정
		// member1 수정 후 member2 수정
		
		try {
			sql = "UPDATE member1 SET pwd = ? WHERE id=?";
			sql2 = "UPDATE member2 SET birth=?, email=?,tel=? WHERE id=?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, dto.getPwd());
			pstmt.setString(2, dto.getId());
			result = pstmt.executeUpdate();

			pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setString(1, dto.getBirth());
			pstmt2.setString(2, dto.getEmail());
			pstmt2.setString(3, dto.getTel());
			pstmt2.setString(4, dto.getId());
			
			
			result2 = pstmt2.executeUpdate();
			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} 
		
		finally {
			if(pstmt != null && pstmt2 != null) {
				try {
					pstmt.close();
					pstmt2.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
			
		}
		
		return result2;
	}

	@Override
	public int deleteMember(String id) throws SQLException {
		int result = 0;
		int result2 = 0;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		String sql,sql2;

		// DELETE FROM 테이블명 WHERE 조건
		
		// id 조건에 맞는 member1 테이블과 member2 테이블 데이터 삭제
		// member2 삭제후 member1 삭제
		
		try {
	
			sql = "DELETE FROM member2 WHERE id = ?";
			sql2 = "DELETE FROM member1 WHERE id = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			result = pstmt.executeUpdate();
			
			pstmt2 = conn.prepareStatement(sql2);
			pstmt2.setString(1, id);
			result2 = pstmt2.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} 
		
		finally {
			if(pstmt != null && pstmt2 != null) {
				try {
					pstmt.close();
					pstmt2.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
			

			
		}

		return result2;
	}

	@Override
	public MemberDTO readMember(String id) {
		MemberDTO dto = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;

		/*
		  - 하나의 테이블
		    SELECT 컬럼, 컬럼 FROM 테이블 WHERE 조건
		  - EQUI 조인
		    SELECT 테이블1.컬럼, 테이블1.컬럼, 테이블2.컬럼
            FROM 테이블1
            JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼
            WHERE 조건
		  - LEFT OUTER JOIN 조인
		    SELECT 테이블1.컬럼, 테이블1.컬럼, 테이블2.컬럼
            FROM 테이블1
            LEFT OUTER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼
            WHERE 조건
		 */
		
		// id 조건에 맞는 member1 테이블과 member2 테이블 데이터를 OUTER JOIN 해서 아이디 검색
		
		try {
			
			sql = "SELECT m1.id, name, pwd, birth, email, tel FROM member1 m1\r\n"
					+ "LEFT OUTER JOIN member2 m2 ON m1.id = m2.id\r\n"
					+ "WHERE m1.id = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				dto = new MemberDTO();
				dto.setId(rs.getString("id"));
				dto.setPwd(rs.getString("pwd"));
				dto.setName(rs.getString("name"));
				dto.setBirth(rs.getString("birth"));
				dto.setEmail(rs.getString("email"));
				dto.setTel(rs.getString("tel"));
			
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
			if(pstmt != null) {
				try {
					
					pstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}

		return dto;
	}

	@Override
	public List<MemberDTO> listMember() {

		// member1 테이블과 member2 테이블의 전체 데이터를 OUTER JOIN 해서 전체 리스트
		
		List<MemberDTO> list = new ArrayList<MemberDTO>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			
			sql = "SELECT m1.id, name, pwd, birth, email, tel FROM member1 m1 "
					+ "LEFT OUTER JOIN member2 m2 ON m1.id = m2.id";
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				MemberDTO dto = new MemberDTO();
				
				dto.setId(rs.getString("id"));
				dto.setName(rs.getString("name"));
				dto.setPwd(rs.getString("pwd"));
				dto.setBirth(rs.getString("birth").toString());
				dto.setEmail(rs.getString("email"));
				dto.setTel(rs.getString("tel"));
				list.add(dto);
				
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) {
					try {
						rs.close();
					} catch (Exception e2) {
						// TODO: handle exception
					}
				}
				
				if(pstmt != null) {
					try {
						pstmt.close();
					} catch (Exception e2) {
						// TODO: handle exception
					}
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		
		

		return list;
	}

	@Override
	public List<MemberDTO> listMember(String name) {
		List<MemberDTO> list = new ArrayList<MemberDTO>();

		// member1 테이블과 member2 테이블의 전체 데이터를 OUTER JOIN 해서 이름 검색
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			
			sql = "SELECT name,m1.id, pwd, birth, email, tel FROM member1 m1\r\n"
					+ "LEFT OUTER JOIN member2 m2 ON m1.id = m2.id\r\n"
					+ "WHERE INSTR(name, ?) = 1";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				MemberDTO dto = new MemberDTO();
				dto.setId(rs.getString("id"));
				dto.setName(rs.getString("name"));
				dto.setPwd(rs.getString("pwd"));
				dto.setBirth(rs.getString("birth").toString());
				dto.setEmail(rs.getString("email"));
				dto.setTel(rs.getString("tel"));
				list.add(dto);
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
			
			if(pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
			
		}

		return list;
	}
}

(MemberDAOImpl 클래스)

= MemberDAO를 상속 받는 클래스

= 여기서 주요 기능이 구현된다.

 

- DB와 연결을 위해 Connection 클래스의 DBconn.getConnection() 사용

- 클래스 내 대부분의 함수가 DB연결을 요하기 때문에 맨 위에 배치

 

* insertMember 함수

- MemberDTO의 객체를 입력 받는다.

- 여기서 이제 PreparedStatement 객체를 만들고 사용한다. 처음엔 null 값을 꼭 주자.

- 이제 본격적으로 소스 작업을 위해서 try - catch문 작성

- sql 변수에 쿼리 명령문을 입력 해준다.

- 새로 등록을 해야 하니까(테이블은 이미 만들었다는 가정하임) INSERT INTO 함수를 이용한다.

(그러나 값을 넣어야 하는 테이블이 2개니까 INSERT를 2번 하던지 INSERT ALL을 하던지 결정하자.)

- 여기서 VALUES값들은 전부 ? 처리 되어있는데 이건 들어가는 값들이 변하는 값들이니까 그런거다

- ?가 들어가는게 PreparedStatement의 특징이다.

- 그리고 아까 PreparedStatement의 객체 pstmt에 sql 명령문을 넣어준다. conn. <<아까 DB연결 객체PreparedStatement(sql)

- 그리고 sql에 ? 에 들어갈 값을 정해준다.

pstmt.setString(1, dto.getId()); << 1번째 ?에 dto객체에서 Id를 가져오겠다 라는 의미임.

- 그리고 result 변수에다가 pstmt.executeUpdate(); 를 이용해서 sql 문을 실행 시킨다.

(하지만 이 코드는 트랜잭션 처리가 안되어서 위험하다 !)

- catch문에서 아까 지정했던 사용자 지정 예외 사용을 하고 throw e를 통해 실행 가능하게 한다.

- finally문을 통해서 .close()를 꼭 해줘야 한다.

- 완료 되면 int형인 return 값을 반환한다.

 

* updateMember함수

- insert 함수와 비슷한 개념이다.

- 다만 여기서 UPDATE는 따로 테이블 별로 나누었다 (그래서 PreparedStatement 객체가 2개임)

- 객체가 2개면 결과 값을 담아줄 변수 result,sql도 2개로 만들어 주자.

- sql문에 UPDATE SET WHERE 형식을 이용해서 가변성 값에는 ?를 넣어주고 설정한다.

- 여기도 트랜잭션 처리가 안되어있어서 위험하다.

(예를들면 member1 테이블에는 정상적으로 값이 수정이 되었는데 meber2는 오류가 있어서 수정이 안되었으면

member1에 수정된 값들도 취소가 되어야 하는데 그렇지 않고 적용(커밋)이 되어 버린다. 그렇기 때문에 트랜잭션이

안되어 있는 상황임 자바 같은 언어에서 바로 DB에 명령문 쏴버리면 자동 커밋이다.)

- 마찬가지로 finally문을 통해서 .close()를 꼭 해줘야 한다.

 

* deleteMember함수

- 이 함수도 위 함수들과 구조는 비슷하다.

- UPDATE처럼 DELETE를 2번해야 하는 점.

 

* readMember함수

- 매개변수로 id를 입력받아서 MemberDTO의 형태를 반환한다.

- 간단하게 입력 받은 id의 정보를 보기 위한 함수이다.

- 이 함수의 sql문은 LEFT OUTER JOIN을 사용 했다. (모든 정보를 보기 위해서)

- 결과를 가져오기 위해서 ResultSet을 사용했다.

- ResultSet을 출력하기 위해서 .next() 사용

- finally에서 close()해줄때 PreparedStatement의 객체, ResultSet의 객체 2개를 닫아준다.

 

* listMember함수

- 이름 그대로 매개 변수가 없으니 전체 리스트를 출력 하려는것을 알 수 있다.

- 전체를 출력하기 위해서 List 클래스 사용 형태는 ArrayList

- 마찬가지로 결과 가져오기 위해서 ResultSet 사용

- 읽어온 결과 값을 DTO의 객체 dto에 값을 저장하고

- 마지막에 값이 다 저장된 dto를 리스트에 추가한다,

- 위 2가지의 작업을 next()를 이용한 반복문을 통해 모든 내용을 list에 추가 할 수 있다.

 

* listMember(String name)함수

- 얘는 이름을 매개변수로 받아서 이름에 해당하는 정보만 출력한다.

- sql명령문에 WHERE 조건문만 추가해주면 된다.

 

package db.member;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.List;

import db.util.DBconn;

public class MemberUI {
	private BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
	private MemberDAO dao = new MemberDAOImpl();

	public void menu() {
		int ch;
		
		System.out.println("회원-예제...");
		
		while(true) {
			try {
				do {
					System.out.print("1.회원가입 2.수정 3.탈퇴 4.아이디검색 5.이름검색 6.리스트 7.종료 => ");
					ch=Integer.parseInt(br.readLine());
				} while(ch < 1 || ch > 7);
				
				if(ch == 7) {
					DBconn.close();
					return;
				}
	
				switch(ch) {
				case 1:insert(); break;
				case 2:update(); break;
				case 3:delete(); break;
				case 4:findById(); break;
				case 5:findByName(); break;
				case 6:listAll(); break;
				}
			}catch (Exception e) {
			}
		}
	}
	
	public void insert() {
		System.out.println("\n회원가입...");
		
		MemberDTO dto = new MemberDTO();
		
		try {

			System.out.println(" 아이디 ? ");
			dto.setId(br.readLine());
			
			System.out.println(" 패스워드 ");
			dto.setPwd(br.readLine());
			
			System.out.println(" 이름 ? ");
			dto.setName(br.readLine());
			
			System.out.println(" 생일 ? ");
			dto.setBirth(br.readLine());
			
			System.out.println(" 이메일 ? ");
			dto.setEmail(br.readLine());
			
			System.out.println(" 전화번호 ? ");
			dto.setTel(br.readLine());
			
			dao.insertMember(dto);
			System.out.println(" 회원가입 완료 ");
			
		} catch (Exception e) {
			System.out.println(" 회원가입 실패 ");
		}

	}

	public void update() {
		System.out.println("\n회원정보수정...");
		
		String id;
		
		try {
			
			System.out.println("수정할 아이디 입력");
			id = br.readLine();
			
			MemberDTO dto = dao.readMember(id);
			if(dto == null) {
				System.out.println("등록된 자료 없음");
				return;
			}
			
			System.out.println("수정할 패스워드");
			dto.setPwd(br.readLine());
			
			System.out.println("수정할 생년월일");
			dto.setBirth(br.readLine());
			
			System.out.println("수정할 이메일");
			dto.setEmail(br.readLine());
			
			System.out.println("수정할 전화번호");
			dto.setTel(br.readLine());
			
			dao.updateMember(dto);
			
			System.out.println("데이터 수정 완료");
			
		} catch (Exception e) {
			e.printStackTrace();
		}



	}

	public void delete() {
		System.out.println("\n회원탈퇴...");
		
		String id;
		try {
			System.out.println("삭제할 id 입력");
			id = br.readLine();
			
			
			
			int result = dao.deleteMember(id);
			if(result > 0) {
				System.out.println("회원 탈퇴 완료");
			} else {
				System.out.println(" 탈퇴 실패 ");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}


	}

	public void findById() {
		System.out.println("\n아이디 검색...");

		String id;
		
		try {
			
			System.out.println("아이디 입력");
			id = br.readLine();
			
			MemberDTO dto = dao.readMember(id);
			if(dto == null) {
				System.out.println("등록된 자료 없음");
				return;
			}
			
			System.out.print(dto.getId() + "\t");
			System.out.print(dto.getPwd() + "\t");
			System.out.print(dto.getName() + "\t");
			System.out.print(dto.getBirth() + "\t");
			System.out.print(dto.getEmail() + "\t");
			System.out.println(dto.getTel());
			System.out.println();
			
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void findByName() {
		System.out.println("\n이름 검색...");
		
		String name;
		
		try {
			
			name = br.readLine();
			List<MemberDTO> list = dao.listMember(name);
			if(list.size() == 0 ) {
				System.out.println("그런 이름 없음");
				return;
			}
			
			for(MemberDTO dto : list) {
				System.out.print(dto.getId() + "\t");
				System.out.print(dto.getName() + "\t");
				System.out.print(dto.getPwd() + "\t");
				System.out.print(dto.getBirth() + "\t");
				System.out.print(dto.getEmail() + "\t");
				System.out.println(dto.getTel());
			}
			System.out.println();
			
		} catch (Exception e) {
			e.printStackTrace();
		}


	}

	public void listAll() {
		System.out.println("\n전체 리스트...");

		List<MemberDTO> list = dao.listMember();
		for(MemberDTO dto : list) {
			System.out.print(dto.getId() + "\t");
			System.out.print(dto.getName() + "\t");
			System.out.print(dto.getPwd() + "\t");
			System.out.print(dto.getBirth() + "\t");
			System.out.print(dto.getEmail() + "\t");
			System.out.println(dto.getTel());
		}
		System.out.println();

	}
}

(UI 클래스)

- 화면에 표시되는 클래스 / 이 클래스를 통해서 MemberDAOImpl 클래스의 함수를 호출 한다.

- 입력을 받아야 하니까 BufferedReader사용

(문자 입력 받기 위해서 InputStreamReader 사용 = 문자로 읽음)

(그러나 엔터 치기전까지 모든 데이터들을 한번에 입력 받기 위해서  BufferedReader사용)

- 실질적 구현된 클래스에서 함수 가져오기 위해서 MemberDAO 선언하는데 자식 클래스인 MemberDAOImpl을 넣어줌

(자식 클래스에선 아버지 클래스도 사용 가능하니까)

- menu함수를 통해 기본 표시 내용 만들어주고

- insert함수로 여러 정보들 MemberDTO 객체에 입력 받고 마지막에 MemberDAOimpl의 insertMember 함수호출

매개 변수는 아까 입력 넣었던 dto

- update함수도 동일

- delete함수에서 deleteMember(id) 함수가 성공이 되면 1을 반환하고 실패하면 0을 반환하는 점을 이용해서

if문 작성해서 탈퇴가 되었나 안되었나 확인 가능

- findById 함수도 마찬가지로 readMember(id)를 이용한다

(DTO 객체를 반환하니까 출력은 여기서 해준다.)

- findByName 함수는 list를 반환하는 함수를 호출해야 하니까 마찬가지로 list 선언 해주고 그 list에다가 담는다.

그리고 list출력을 하는데 향상된 for문을 통해서 출력했다.

- listAll은 함수 호출한 값 list에 담고 마찬가지로 for문 돌려주면 된다.

 

package db.member;

public class App {
	public static void main(String[] args) {
		MemberUI ui=new MemberUI();
		ui.menu();
	}

}

APP 클래스

여기서 프로그램 실행을 한다.