국비학원 19일차) service, vo, test 패키지 나눠서 DB연동하기

 

 

 

<service 패키지>

 

 

1) Dao

 

package service;

import java.util.List;

import vo.Member;

public interface Dao {
	
	void insert(Member m);
	List<Member> selectAll();
	Member select(String name);
	boolean update(Member m);
	boolean delete(String name);
	
}

 

 

2) OracleDao

 

package service;

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

import vo.Member;

public class OracleDao implements Dao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	public void con() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521","hr","hr");
		} catch(Exception e) {
			System.out.println(e);
		}
	}

	public void discon() {
		try {
			ps.close();
			conn.close();
		} catch(Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public void insert(Member m) {
		con();
		String sql = "INSERT INTO ADDR"
				+ " VALUES(?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, m.getName());
			ps.setString(2, m.getTel());
			ps.setString(3, m.getAddress());
			ps.executeUpdate();

		} catch(Exception e) {
			System.out.println(e);
		}
		discon();
	}

	@Override
	public List<Member> selectAll() {
		con();

		List<Member> list = new ArrayList<>();
		String sql = "SELECT * FROM ADDR";

		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while(rs.next()) {
				list.add(new Member(rs.getString(1),rs.getString(2),rs.getString(3)));
			}

		} catch(Exception e) {
			System.out.println(e);
		}
		return list;
	}

	@Override
	public Member select(String name) {
		con();
		Member m = null;
		String sql = "SELECT * FROM ADDR"
				+ " WHERE NAME = ?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();

			if(rs.next()) {
				m = new Member(rs.getString(1),rs.getString(2),rs.getString(3));
				rs.close();
			}


		} catch(Exception e) {
			System.out.println(e);
		}
		discon();
		return m;
	}

	@Override
	public boolean update(Member m) {
		int num = 0;
		con();
		String sql = "UPDATE ADDR SET TEL = ?,ADDRESS = ?"
				+ " WHERE NAME = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, m.getTel());
			ps.setString(2, m.getAddress());
			ps.setString(3, m.getName());
			num = ps.executeUpdate();

		} catch(Exception e) {
			System.out.println(e);
		}
		discon();

		if (num <= 0)
			return false;
		else
			return true;
	}

	@Override
	public boolean delete(String name) {
		int num=0;
		con();
		String sql = "DELETE ADDR WHERE NAME = ?";

		try {

			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			num = ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println(e);
		}

		discon();
		if (num <= 0) {
			return false;
		}else {
			return true;
		}
	}

}

 

 

3) Service

 

package service;

import java.util.List;

import vo.Member;

public interface Service {
	
	void addMember(Member m);
	List<Member> getMembers();
	Member getMamber(String name);
	boolean editMember(Member m);
	boolean delMember(String name);
	
}

 

 

4) ServiceImpl

 

package service;

import java.util.List;

import vo.Member;

public class ServiceImpl implements Service {

	private Dao dao;
	
	public ServiceImpl() {
		dao = new OracleDao();
	}
	
	@Override
	public void addMember(Member m) {
		dao.insert(m);
	}

	@Override
	public List<Member> getMembers() {
		return dao.selectAll();
	}

	@Override
	public Member getMamber(String name) {
		return dao.select(name);
	}

	@Override
	public boolean editMember(Member m) {
		return dao.update(m);
	}

	@Override
	public boolean delMember(String name) {
		return dao.delete(name);
	}

}

 

 

 

 

<vo 패키지>

 

Member

 

package vo;

public class Member {
	
	private String name;
	private String tel;
	private String address;
	
	public Member() {
		super();
	}
	
	public Member(String name, String tel, String address) {
		super();
		this.name = name;
		this.tel = tel;
		this.address = address;
	}
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}

	@Override
	public String toString() {
		return "Member [name=" + name + ", tel=" + tel + ", address=" + address + "]";
	}
	
	
	
	
	
}

 

 

<test 패키지>

 

 

addrDBMain (메인 클래스)

 

package test;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import service.Service;
import service.ServiceImpl;
import vo.Member;

public class addrDBMain {

	public static void main(String[] args) {

		Service s = new ServiceImpl();
		boolean flag=true, flag2 = false;
		int menu, i;
		String name="", tel="", address="";
		List<Member> list = new ArrayList<>();
		Scanner sc = new Scanner(System.in);

		while(flag) {
			System.out.println("1.추가 2.전체검색 3.검색 4.종료 5.수정 6.삭제");
			menu = sc.nextInt();
			switch(menu) {
			case 1: //추가
				System.out.println("이름:");
				name = sc.next();
				System.out.println("전화:");
				tel = sc.next();
				sc.nextLine();
				System.out.println("주소:");
				address = sc.next();
				s.addMember(new Member(name,tel,address));
				break;
			case 2: //전체검색
				list = s.getMembers();
				for(Member m : list) {
					System.out.println(m);
				}
				break;
			case 3: //검색
				System.out.println("이름은?");
				name = sc.next();
				Member m = s.getMamber(name);
				if(m==null) {
					System.out.println("사람이 없어요");
				} else {
					System.out.println(m);
				}

				break;
			case 4: //종료
				System.out.println("종료되었습니다");
				flag=false;
				break;
			case 5: //수정
				System.out.println("이름:");
				name = sc.next();
				System.out.println("전화:");
				tel = sc.next();
				sc.nextLine();
				System.out.println("주소:");
				address = sc.next();
				flag2 = s.editMember(new Member(name,tel,address));
				if(flag2==false)
					System.out.println("해당 이름이 없습니다");
				else
					System.out.println("수정 완료");
				break;
			case 6: //삭제
				System.out.println("이름:");
				name = sc.next();
				flag2 = s.delMember(name);
				if(flag2==false)
					System.out.println("해당 이름이 없습니다");
				else
					System.out.println("삭제 완료");
				break;

			}

		}


	}

}

 

 

 

 

결과