210903_이클립스를 통해 오라클 출력(select,update,delete,insert,search)

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

 

 

public class DbMembers {

 

DbConnTest db=new DbConnTest();

 

//사원정보입력

    public void insertMember() {

    Connection conn=null;

    PreparedStatement pstmt=null;

    

    Scanner sc=new Scanner(System.in);

    String name,buseo,gender,position;

    int pay,bonus;

    

//사원번호,사원명,부서명,성별,직급,월급여,보너스

    System.out.println("사원명 입력");

    name=sc.nextLine();

    System.out.println("부서명 입력");

    buseo=sc.nextLine();

    System.out.println("성별 입력");

    gender=sc.nextLine();

    System.out.println("직급 입력");

    position=sc.nextLine();

    System.out.println("월급여 입력");

    pay=Integer.parseInt(sc.nextLine());

    System.out.println("보너스 입력");

    bonus=Integer.parseInt(sc.nextLine());

    

//변수처리 나중에

    String sql="insert into members values(seq_mem.nextval,?,?,?,?,?,?)";

    

    conn=db.getCloudOracle();

    

    try {

pstmt=conn.prepareStatement(sql);

 

//?에 해당하는 것을 순번대로 바인딩

pstmt.setString(1, name); 

pstmt.setString(2, buseo);

pstmt.setString(3, gender); 

pstmt.setString(4, position);

pstmt.setInt(5, pay);

pstmt.setInt(6, bonus);

 

int n=pstmt.executeUpdate();

if(n==1)

System.out.println("사원정보 추가 완료");

else 

    System.out.println("사원정보 추가 실패");

 

} catch (SQLException e) {

System.out.println("Insert 오류: "+e.getMessage());

} finally {

db.dbClose(pstmt, conn);

}

    }

 

 

//사원정보수정

//사원번호 입력후 사원명,부서명,직급,성별,월급여,보너스 수정할것

    public void updateMember() {

    

    Connection conn=null;

    PreparedStatement pstmt=null;

    

    Scanner sc=new Scanner(System.in);

    

    String name,buseo,gender,position;

    int pay,bonus,cno;

    

    System.out.println("수정할 사원번호 입력");

    cno=Integer.parseInt(sc.nextLine());

    System.out.println("수정할 사원명 입력");

    name=sc.nextLine();

    System.out.println("수정할 부서명 입력");

    buseo=sc.nextLine();

    System.out.println("수정할 성별 입력");

    gender=sc.nextLine();

    System.out.println("수정할 직급 입력");

    position=sc.nextLine();

    System.out.println("수정할 월급여 입력");

    pay=Integer.parseInt(sc.nextLine());

    System.out.println("수정할 보너스 입력");

    bonus=Integer.parseInt(sc.nextLine());

    

String sql="update members set name=?,buseo=?,gender=?, position=?, pay=?, bonus=? where cno=?";

    

    conn=db.getCloudOracle();

    

    try {

pstmt=conn.prepareStatement(sql);

 

pstmt.setString(1, name);

pstmt.setString(2, buseo);

pstmt.setString(3, gender);

pstmt.setString(4, position);

pstmt.setInt(5, pay);

pstmt.setInt(6, bonus);

pstmt.setInt(7, cno);

 

int a=pstmt.executeUpdate(); //수정됐는지 확인용

if(a==1)

System.out.println("수정 완료");

else 

System.out.println("수정할 데이터 존재하지 않음");

 

} catch (SQLException e) {

System.out.println("update 오류: "+e.getMessage());

} finally {

db.dbClose(pstmt, conn);

}

   }

 

 

//사원정보삭제

//사원번호 입력시 삭제

    public void deleteMember() {

    

    Scanner sc=new Scanner(System.in);

String cno="";

   

System.out.println("삭제할 번호 입력");

cno=sc.nextLine();

 

String sql="delete from members where cno="+cno;

    

Connection conn=null;

PreparedStatement pstmt=null;

 

conn=db.getCloudOracle();

 

try {

pstmt=conn.prepareStatement(sql);

 

int a=pstmt.executeUpdate();

if(a==0) //없는 번호 삭제시 0 반환

System.out.println("없는 데이터 번호");

else //삭제되면 1이 반환

System.out.println("삭제 완료");

 

 

} catch (SQLException e) {

System.out.println("delete 오류: "+e.getMessage());

} finally {

db.dbClose(pstmt, conn);

}

    }

 

 

//사원정보조회

public void selectMember() {

 

Connection conn=null;

PreparedStatement pstmt=null;

ResultSet rs=null;

 

String sql="select * from members order by cno";

 

conn=db.getCloudOracle();

 

try {

pstmt=conn.prepareStatement(sql);

rs=pstmt.executeQuery();

 

System.out.println("사원번호\t사원명\t부서\t성별\t직급\t월급여\t보너스");

System.out.println("------------------------------------------------------");

 

while(rs.next())

{

System.out.println(rs.getInt("cno")+"\t"+rs.getString("name")+"\t"+rs.getString("buseo")+"\t"+rs.getString("gender")+"\t"+rs.getString("position")+"\t"+rs.getInt("pay")+"\t"+rs.getInt("bonus"));

}

 

} catch (SQLException e) {

System.out.println("select 오류: "+e.getMessage());

} finally {

db.dbClose(rs, pstmt, conn);

}

}

 

 

//이름 검색

public void serchName() {

Scanner sc=new Scanner(System.in);

 

System.out.println("검색할 이름 입력(일부만 검색 가능)");

String name="";

name=sc.nextLine();

 

String sql="select * from members where name like '%"+name+"%'";

System.out.println(sql);

System.out.println("\t\t**검색 사원 명단**");

System.out.println();

System.out.println("사원번호\t사원명\t부서\t직급\t성별\t월급여\t보너스");

System.out.println("============================================");

 

//db연결

Connection conn=db.getCloudOracle();

Statement stmt=null;

ResultSet rs=null;

 

try {

stmt=conn.createStatement();

rs=stmt.executeQuery(sql);

 

while(rs.next())

{

System.out.println(rs.getInt("cno")+"\t"

+rs.getString("name")+"\t"

+rs.getString("buseo")+"\t"

+rs.getString("position")+"\t"

+rs.getString("gender")+"\t"

+rs.getString("pay")+"\t"

+rs.getString("bonus"));

}

} catch (SQLException e) {

} finally {

db.dbClose(rs, stmt, conn);

}

}

 

 

public static void main(String[] args) {

// TODO Auto-generated method stub

DbMembers mem=new DbMembers();

Scanner sc=new Scanner(System.in);

int n=0;

 

while(true)

{

System.out.println("*****사원정보*****");

System.out.println("1.입력   2.수정  3.삭제  4.조회   5.검색   9.종료");

 

System.out.print("선택번호: ");

n=Integer.parseInt(sc.nextLine());

 

if(n==1)

mem.insertMember();

else if(n==2)

mem.updateMember();

else if(n==3)

mem.deleteMember();

else if(n==4)

mem.selectMember();

else if(n==5)

mem.serchName();

else if(n==9)

{

System.out.println("프로그램 종료");

break;

}

}

}

}

 

 

 

 

입력

조회

수정

삭제

검색