티스토리 뷰
참고)
GUIDE LINE
[문제 25] 다음 프로그램을 작성하시오
(1) 프로젝트명 : MemberProject
(2) 테이블명: member
컬럼명 : idx 숫자, 기본키
name 가변형(15),널값허용안함
phone 가변형(15)
시퀀스 : mp_idx (1부터 1씩증가)
입력자료 : 강호동 010-123-4567
홍길동 011-789-4561
(3)구조
MemberProject
src
table_schma
member.sql
common
MyConnectionPool.java
com.edu.view
MemberMain.java <---main()메서드
MemberView.java
com.edu.entity
MemberEntity.java
com.edu.dao
MemberDAO.java
com.edu.controller
MemberController.java
(4)조건
1) MemberMain : 입력 코드를 완성한다
menu()는 MemberMain에서 만들고, 모든 결과 출력은 MemberView에서 한다
2) MemberEntity
-idx:int
-name:String
-phone:String
+MemberEntity()
+MemberEntity(idx:int,name:String,phone:String)
+getter & setter
3) MemberController
+MemberController()
+getMemberList():void
+getMember(name:String):void
+insertMember(ob:MemberEntity):void
+deleteMember(name:String):void
4) MemberDAO
+getMemberList():ArrayList<MemberEntity> ==> 전체보기
+getMember(name:String):MemberEntity ==> 찾기
+insertMember(ob:MemberEntity):int ==> 추가
+deleteMember(name:String):int ==> 삭제
[입.출력화면]
1.추가 2.삭제 3.전체보기 4.찾기 5.종료
선택(1~5) : 1
이름: 강호동
전화: 010-123-4567
추가되었습니다
1.추가 2.삭제 3.전체보기 4.찾기 5.종료
선택(1~5) : 3
강호동 010-123-4567
이만기 011-789-4561
1.추가 2.삭제 3.전체보기 4.찾기 5.종료
선택(1~5) : 4
찾는사람 : 강호동
강호동 010-123-4567
1.추가 2.삭제 3.전체보기 4.찾기 5.종료
선택(1~5) : 2
삭제할사람 : 강호동
삭제되었습니다
1.추가 2.삭제 3.전체보기 4 .찾기 5.종료
선택(1~5) : 5
*** 작업끝 ******MEMBER DAO에서 String sql 문에서 바꿔준다. 어디서 불러오는 건지는 여기서**
//추가하기
public int insertMember(MemberEntity entity) throws SQLException {
Connection conn = MyConnectionPool.getInstance().getConnection();
PreparedStatement pstmt = null;
int n = 0;
try {
String sql = "INSERT INTO member(NUM,NAME,PHONE) VALUES(NUM_SEQ.NEXTVAL,?,?)";
pstmt=conn.prepareStatement(
sql);
pstmt.setString(1, entity.getName());
pstmt.setString(2, entity.getPhone());
n=pstmt.executeUpdate();
if(n > 0) {
conn.commit();
} 아래)
Controller 패키지 / MemberController 클래스
package com.edu.controller;
import java.sql.SQLException;
import java.util.List;
import com.edu.dao.MemberDAO;
import com.edu.entity.MemberEntity;
import com.edu.view.MemberView;
//View와 model에서 중간처리 역할
public class MemberController {
MemberDAO dao = null;
MemberView view = null;
public MemberController() {
dao = new MemberDAO();
view = new MemberView();
}
public void insertMember(MemberEntity entity) throws SQLException { //추가하기
int n = dao.insertMember(entity);
view.insertMember(n);
}
public void deleteMember(String name) throws SQLException { // 삭제하기
int n = dao.deleteMember(name);
view.deleteMember(name,n);
}
public void listMember() throws SQLException { //리스트보여주기
List<MemberEntity> list = dao.listMember();
view.listMember(list);
}
public boolean searchSQL(String name) throws SQLException { // 찾기
MemberEntity ob = dao.searchSQL(name);
view.searchSQL(ob);
return (ob!=null)?true:false;
}
}
dao 패키지 / MemberDAO 클래스
package com.edu.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.edu.entity.MemberEntity;
import common.MyConnectionPool;
//DAO(Data Access Object):데이터베이스와 연동(CRUD)하기 위한 클래스
public class MemberDAO {
//추가하기
public int insertMember(MemberEntity entity) throws SQLException {
Connection conn = MyConnectionPool.getInstance().getConnection();
PreparedStatement pstmt = null;
int n = 0;
try {
String sql = "INSERT INTO member(NUM,NAME,PHONE) VALUES(NUM_SEQ.NEXTVAL,?,?)";
pstmt=conn.prepareStatement(
sql);
pstmt.setString(1, entity.getName());
pstmt.setString(2, entity.getPhone());
n=pstmt.executeUpdate();
if(n > 0) {
conn.commit();
}
}catch(SQLException e) {
e.printStackTrace();
conn.rollback();
}finally {
pstmt.close();
conn.close();
}
return n;
}
// 삭제하기
public int deleteMember(String name) throws SQLException {
Connection conn = MyConnectionPool.getInstance().getConnection();
PreparedStatement pstmt = null;
int n = 0;
try {
String sql = "DELETE FROM member WHERE NAME=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
n=pstmt.executeUpdate();
if(n>0) {
conn.commit();
}
}catch(SQLException e){
e.printStackTrace();
conn.rollback();
}
return n;
}
// 전체보기
public List<MemberEntity> listMember() throws SQLException {
Connection conn = MyConnectionPool.getInstance().getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
List<MemberEntity> list = new ArrayList<>();
MemberEntity entity = null;
try {
String sql = "SELECT * FROM member ORDER BY idx DESC";
pstmt = conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
entity = new MemberEntity();
entity.setidx(rs.getInt("idx"));
entity.setName(rs.getString("NAME"));
entity.setPhone(rs.getString("PHONE"));
list.add(entity);
}
}catch(SQLException e){
e.printStackTrace();
}finally {
rs.close();
pstmt.close();
conn.close();
}
return list;
}
public MemberEntity searchSQL(String name) throws SQLException {// 찾기
Connection conn=MyConnectionPool.getInstance().getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
MemberEntity entity=null;
try {
String sql="SELECT * FROM member WHERE NAME=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
while(rs.next()) {
entity = new MemberEntity();
entity.setidx(rs.getInt("NUM"));
entity.setName(rs.getString("NAME"));
entity.setPhone(rs.getString("PHONE"));
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
rs.close();
pstmt.close();
conn.close();
}
return entity;
}
}
entity 패키지/ MemberEntity 클래스
package com.edu.entity;
//관련성 있는 데이터를 하나의 객체로 만들기 위한 클래스
//Entity, DTO(Data Transfer Object), VO(Value Object), bean
public class MemberEntity {
private int idx;
private String name;
private String phone;
public MemberEntity() {
super();
// TODO Auto-generated constructor stub
}
public MemberEntity(int idx, String name, String phone) {
super();
this.idx = idx;
this.name = name;
this.phone = phone;
}
public int getidx() {
return idx;
}
public String getName() {
return name;
}
public String getPhone() {
return phone;
}
public void setidx(int idx) {
this.idx = idx;
}
public void setName(String name) {
this.name = name;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
View 패키지/ MemberMain 클래스
package com.edu.view;
import java.sql.SQLException;
import java.util.Scanner;
import com.edu.controller.MemberController;
import com.edu.entity.MemberEntity;
//입력 코드 완성
public class MemberMain {
Scanner scan = null;
MemberController control = null;
MemberEntity entity = null;
public MemberMain() {
scan = new Scanner(System.in);
control = new MemberController();
entity = new MemberEntity();
}
public void menu() throws SQLException{
int n =0;
String name = null, phone = null;
while(true) {
System.out.println("1.추가 2.삭제 3.전체보기 4.찾기 5.종료\r\n"
+ "선택(1~5) :");
n = scan.nextInt();
switch(n) {
case 1: // 추가하기
System.out.println("추가할 데이터를 입력하세용");
System.out.println("이름을 입력하세용:"); entity.setName(scan.next());
System.out.println("전화번호를 입력하세용:"); entity.setPhone(scan.next());
control.insertMember(entity);
break;
case 2: // 삭제하기
System.out.println("삭제할 사람을 입력하세용");
name = scan.next();
control.deleteMember(name);
break;
case 3: // 전체보기
System.out.println("현재 리스트입니당");
control.listMember();
break;
case 4: // 찾기
System.out.print("찾는 사람을 입력하세용:"); name=scan.next();
control.searchSQL(name);
break;
case 5: //종료
scan.close();
System.exit(0);
default:
System.out.println("다시 입력하세용");
continue;
}
}
}
public static void main(String[] args) throws SQLException {
new MemberMain().menu();
}
}
MemberView 클래스
package com.edu.view;
import java.util.List;
import com.edu.entity.MemberEntity;
public class MemberView {
public void insertMember(int n) {//추가
if(n > 0) {
System.out.println(n + "건의 데이터를 추가 했습니다");
}else {
System.out.println("데이터를 추가하지 못했습니다");
}
}
public void deleteMember(String name, int n) {//삭제
if(n>0) {
System.out.println(name + "님의 데이터를 삭제 하였습니다");
}else {
System.out.println("데이터를 삭제하지 못했습니다");
}
}
public void listMember(List<MemberEntity> list) { // 리스트 보여주기
if(list!=null) {
for(MemberEntity ob : list) {
System.out.println(ob.getName()+"\t");
System.out.println(ob.getPhone()+"\t");
}
}
}
public void searchSQL(MemberEntity ob) {
if(ob == null) {
System.out.println("찾는 사람이 없습니당");
}else {
System.out.print(ob.getidx()+"\t");
System.out.print(ob.getName() +"\t");
System.out.print(ob.getPhone()+"\t");
}
}
}
댓글