619
No
-- 작업할 데이터베이스 선택
use java;
--사원 테이블(사원번호, 사원이름, 입사일자, 급여)
create table emp(
empno int primary key,
ename varchar(50) not null,
hiredate datetime,
sal int default 0
);
-- 레코드 입력
insert into emp values (10, '김철수' , '2017-01-01', 3000);
-- 일부의 컬럼을 이력할 경우
insert into emp (empno, ename) values (20 , '박철수');
select * from emp;
DBConnectManager
package common.db;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class DBConnectManager {
public static Connection dbConn(){
Connection conn=null;
try{
//db 접속 정보가 저장되 db.prop 파일을 로딩
FileInputStream fis =new FileInputStream("db.prop");
Properties prop=new Properties();
prop.load(fis);
String url=prop.getProperty("url");
String id=prop.getProperty("id");
String password=prop.getProperty("password");
//mysql에 접속 처리
conn=DriverManager.getConnection(url, id, password);
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn){
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(PreparedStatement pstmt){
try {
if(pstmt!=null)pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs){
try {
if(rs!=null)rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(Connection conn, PreparedStatement pstmt ){
try{
if(pstmt!=null)pstmt.close();
}catch(Exception e){
e.printStackTrace();
}
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){
try{
if(rs!=null)rs.close();
}catch(Exception e){
e.printStackTrace();
}
try{
if(pstmt!=null)pstmt.close();
}catch(Exception e){
e.printStackTrace();
}
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs){
try{
if(rs!=null)rs.close();
}catch(Exception e){
e.printStackTrace();
}
try{
if(stmt!=null)stmt.close();
}catch(Exception e){
e.printStackTrace();
}
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
class EmpDTO
package java19.emp;
import java.sql.Date;
//dto: Data Transfer Object(데이터를 저장, 전달하는 객체)
//vo: Value Object
public class EmpDTO {
private int empno;
private String ename;
private Date hiredate;
private int sal;
public EmpDTO() {
super();
}
public EmpDTO(int empno, String ename, Date hiredate, int sal) {
super();
this.empno = empno;
this.ename = ename;
this.hiredate = hiredate;
this.sal = sal;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
@Override
public String toString() {
return "EmpDTO [empno=" + empno + ", ename=" + ename + ", hiredate=" + hiredate + ", sal=" + sal + "]";
}
}
class EmpDAO
package java19.emp;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import common.db.DBConnectManager;
//dao : Data Access Object (데이터 처리 객체)
public class EmpDAO {
private static EmpDAO dao ;
private EmpDAO (){
}
public static EmpDAO getInstance(){
if(dao==null){
dao=new EmpDAO();
}
return dao;
}
//사원 목록을 리턴하는 코드
public List<EmpDTO> listEmp(){
List<EmpDTO> items=new ArrayList<>();
Connection conn=null;//db 접속 처리
PreparedStatement pstmt=null;//sql 실행
ResultSet rs=null;//실행결과를 한 레코드씩 읽음
try{
conn =DBConnectManager.dbConn();
String sql ="select * from emp";
pstmt=conn.prepareStatement(sql);//쿼리 실행 준비
rs=pstmt.executeQuery();// 쿼리 실행=> 결과 셋에 전달
//결과셋.next() 다음 레코드를 읽음, 결과셋.previous() 앞 레코드를 읽음
while(rs.next()){
// 결과셋.get자료형("컬럼이름") 컬럼의 값을 읽음
int empno=rs.getInt("empno");
String ename=rs.getString("ename");
Date hiredate=rs.getDate("hiredate");
int sal=rs.getInt("sal");
//리스트에 dto 추가
items.add(new EmpDTO(empno, ename, hiredate, sal));
}
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt, rs);
}
return items;//리스트를 리턴함
}
//사원 레코드 추가
public void isertEmp(EmpDTO dto){
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn=DBConnectManager.dbConn();
String sql ="insert into emp (empno, ename, hiredate, sal) "
+ " values ( ?, ? , ? , ?)";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, dto.getEmpno());
pstmt.setString(2, dto.getEname());
pstmt.setDate(3, dto.getHiredate());
pstmt.setInt(4, dto.getSal());
pstmt.execute();
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt);
}
}
//사원 레코드 삭제
public int deleteEmp(int empno){
int result=0;
Connection conn =null;
PreparedStatement pstmt =null;
try{
conn=DBConnectManager.dbConn();
String sql ="delete from emp where empno=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, empno);
result=pstmt.executeUpdate();
}catch (Exception e) {
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt);
}
return result;
}
}
class EmpManage
package java19.emp;
import java.sql.Date;
import java.util.List;
import java.util.Scanner;
public class EmpManage {
EmpDAO dao;
public EmpManage() {
dao =EmpDAO.getInstance();
}
//사원 목록 출력
public void list(){
List<EmpDTO> items=dao.listEmp();
System.out.println("---------------------------------------");
System.out.println("사번\t이름\t입사일자\t급여");
System.out.println("---------------------------------------");
for(EmpDTO dto : items){
System.out.print(dto.getEmpno()+"\t");
System.out.print(dto.getEname()+"\t");
System.out.print(dto.getHiredate()+"\t");
System.out.print(dto.getSal()+"\t\n");
}
System.out.println("---------------------------------------");
}
void insert(){
Scanner scan=new Scanner(System.in);
System.out.print("사번을 입력하세요");
int empno=scan.nextInt();
System.out.print("이름을 입력하세요");
String ename=scan.next();
System.out.print("입사일 입력하세요");
String hiredate=scan.next();
System.out.print("급여을 입력하세요");
int sal=scan.nextInt();
//Date.valueOf(문자열) 문자열을 Date type 으로 변환
dao.isertEmp(new EmpDTO(empno, ename, Date.valueOf(hiredate), sal));
System.out.println("저장 되었습니다.");
}
void delete(){
Scanner scan=new Scanner(System.in);
System.out.print("사번을 입력하세요");
int empno=scan.nextInt();
if(dao.deleteEmp(empno)==1){
System.out.println("삭제 하였습니다.");
}else{
System.out.println("없는 사번 입니다.");
}
}
public static void main(String[] args) {
EmpManage manage=new EmpManage();
@SuppressWarnings("resource")
Scanner scan =new Scanner(System.in);
while(true){
System.out.println("메뉴를 선택하세요(1. 목록, 2.추가, 3.삭제, 0: 종료 )");
int code=scan.nextInt();
switch (code) {
case 0:
System.out.println("종료합니다.");
System.exit(0);
break;
case 1:
manage.list();
break;
case 2:
manage.insert();
break;
case 3:
manage.delete();
break;
default:
System.out.println("잘못 선택하셨습니다.");
break;
}
}
}
}
출력 =>
메뉴를 선택하세요(1. 목록, 2.추가, 3.삭제, 0: 종료
1
---------------------------------------
사번 이름 입사일자 급여
---------------------------------------
10 김철수 2017-01-01 3000
20 박철수 null 0
30 홍길동 2015-12-30 7000
---------------------------------------
메뉴를 선택하세요(1. 목록, 2.추가, 3.삭제, 0: 종료
3
사번을 입력하세요30
삭제 하였습니다.
메뉴를 선택하세요(1. 목록, 2.추가, 3.삭제, 0: 종료
1
---------------------------------------
사번 이름 입사일자 급여
---------------------------------------
10 김철수 2017-01-01 3000
20 박철수 null 0
---------------------------------------
메뉴를 선택하세요(1. 목록, 2.추가, 3.삭제, 0: 종료
3
사번을 입력하세요11
없는 사번 입니다.
메뉴를 선택하세요(1. 목록, 2.추가, 3.삭제, 0: 종료
0
댓글 ( 4)
댓글 남기기