자바-오라클 CRUD

최성현·2023년 7월 11일

CRUD INSERT close메서드 connect메서드 delete search select update 오라클 드라이버 연동 오라클 주소 연동

Java

목록 보기

46/46

sql table 생성

table 생성후 이클립스로 insert, select, delete, update

sql 문

--시퀀스
create sequence seq1;

--테이블(myinfo)
create table myinfo(num number(5) primary key, name varchar2(20), addr varchar2(20), sdate date);

select * from myinfo;

--테이블(myshop)
create table myshop(shopnum number(5) primary key,
sangpum varchar2(20),
su number(5),
price number(5),
ipgo date);

select * from myshop;

connect 만들기

connect 와 close를 미리 만들어 놓아 간결한 코드 만들기

전역에 오라클 URL 선언
static final String ORACLE_URL="jdbc:oracle:thin:@localhost:1521:XE";
->"오라클 주소";

오라클 연동

프로젝트 Library -> build path -> configure build path -> Libraries -> Modulepath -> Add External JARs -> 오라클 driver에서 받은 파일 중 필요한 jar open
이클립스 전역에 driver 주소 선언
->driver는 database와 연결해주는 역할
String driver="oracle.jdbc.driver.OracleDriver";
->"오라클 드라이버 주소";

url은 하나의 경로 역할

3.DBConnect 메소드
Class.forName(driver); -> try/catch
성공출력은 try안에 실패출력은 catch 안에 출력문 입력

4.Connection gerConnection 메소드
conn=DriverManager.getConnection(오라클 URL,"오르클아이디","오라클 비밀번호"); -> try/catch
성공출력은 try안에 실패출력은 catch 안에 출력문 입력

return은 conn 반환

5.close 메서드 4가지(완성구문2개(1,2),미완성구문2개(3,4))

1)Result rs,Statement stmt,Connection conn

public void dbClose(ResultSet rs,Statement stmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

2)Statement stmt,Connection conn

public void dbClose(Statement stmt,Connection conn)
	{
		try {
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

3)ResultSet rs,PreparedStatement pstmt,Connection conn

public void dbClose(ResultSet rs,PreparedStatement pstmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

4)PreparedStatement pstmt,Connection conn

public void dbClose(PreparedStatement pstmt,Connection conn)
	{
		try {
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

코드전체

package dbtest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConnect {
	
	//url
	static final String ORACLE_URL="jdbc:oracle:thin:@localhost:1521:XE";
	
	//driver
	String driver="oracle.jdbc.driver.OracleDriver";
	
	public DBConnect() {
		try {
			Class.forName(driver);
			System.out.println("오라클 드라이버 성공!!!");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("오라클 드라이버 실패!!!");
		}
	}
	
	//Connection
	public Connection getConnection()
	{
		
		Connection conn=null;
		
		try {
			conn=DriverManager.getConnection(ORACLE_URL, "tjdgus", "1234");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("오라클 연결 실패: url,계정,비밀번호 확인 요함! "+e.getMessage());
		}
		return conn;
	}
	
	//close메서드..총 4개
	//완성구문 2개
	public void dbClose(ResultSet rs,Statement stmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void dbClose(Statement stmt,Connection conn)
	{
		try {
			if(stmt!=null) stmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//미완구문 2개 //preparedstatement 나중에 정의해줌
	public void dbClose(ResultSet rs,PreparedStatement pstmt,Connection conn)
	{
		try {
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void dbClose(PreparedStatement pstmt,Connection conn)
	{
		try {
			if(pstmt!=null) pstmt.close();
			if(conn!=null) conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

CRUD(입력,조회,수정,삭제,검색)

전역에 connect 호출
DBConnect db=new DBConnect();

insert

public void insert()
	   {
	      
	      Scanner sc=new Scanner(System.in);
	      
	      System.out.println("이름입력");
	      String name=sc.nextLine();
	      System.out.println("주소입력");
	      String addr=sc.nextLine();
	      
	      //sql문에는 ''을 써서 실행시키고 java에서는 ""로 사용하기때문에 '"를 잘 구분해서 사용해야한다
	      String sql="insert into myinfo values (seq1.nextval,'"+name+"','"+addr+"',sysdate)";
	      
	      //1. db연결
	      Connection conn=db.getConnection();
	      //2. statement
	      Statement stmt=null;
	      
	      try {
	         stmt=conn.createStatement();
	         stmt.execute(sql);
	         
	         System.out.println("***정보가 추가되었습니다***");
	         
	      } catch (SQLException e) {
	         // TODO Auto-generated catch block
	         e.printStackTrace();
	         System.out.println("insert error"+e.getMessage());
	      } finally {
	         db.dbClose(stmt, conn);
	      }
	      
	   }

select

public void select()
	   {
		   System.out.println("시퀀스\t이름\t주소\t날짜");
		   System.out.println("=================================");
		   
		   //요청한 sql문을 String에 저장
		   String sql="select * from myinfo order by num";
		   
		   //Connection
		   Connection conn=null;
		   Statement stmt=null;
		   ResultSet rs=null; //조회출력이므로 필요
		   
		   conn=db.getConnection();
		   try {
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			
			//2개 이상일 경우 while문
			//resultset객체의 next이용해서 행을 선택하고 get메서드를 이용해서 테이블의 컬럼 값을 얻는다
			while(rs.next())
			{
				System.out.println(rs.getInt("num")
									+"\t"+rs.getString("name")
									+"\t"+rs.getString("addr")
									+"\t"+rs.getDate("sdate"));
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, stmt, conn);
		}
	   }

delete

public void delete()
	   {
		   //시퀀스 입력 후 삭제
		   Scanner sc=new Scanner(System.in);
		   String num;
		   
		   System.out.println("삭제할 시퀀스는?");
		   num=sc.nextLine();
		   
		   String sql="delete from myinfo where num="+num;
		   
		   //db연결
		   Connection conn=db.getConnection();
		   //statement
		   Statement stmt=null;
		   
		   
		   try {
			stmt=conn.createStatement();
			
			//sql문 실행
			int a=stmt.executeUpdate(sql); //execute는 boolean 값임//executeUpdate는 성공한 갯수 반환 가능
										   //둘다 사용해도 상관없음
			
			if(a==0) //없는 번호 입력시 실제 삭제가 되지않으므로 0반환
				System.out.println("없는 데이터 번호입니다");
			else //삭제성공되면 1 반환 (boolean으로 0,1 false,true 이기 때문
				System.out.println("****삭제되었습니다****");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   }

update (2개 메서드)

업데이트 메서드

public void update()
	   {
		   //수정할 시퀀스 입력 후 이름, 주소 입력
		   Scanner sc=new Scanner(System.in);
		   String num;
		   
		   System.out.println("수정할 시퀀스는?");
		   num=sc.nextLine();
		   
		   //boolean메서드 가져와서 실행
		   if(!this.isData(num))
		   {
			   System.out.println("해당 번호는 존재하지 않습니다");
			   return; //메서드 종료
		   }
		   
		   
		   
		   System.out.println("수정할 이름은?");
		   String name=sc.nextLine();
		   System.out.println("수정할 주소는?");
		   String addr=sc.nextLine();
		   
		   //sql//where문 적을때 1칸띄워야하니까 '뒤에 스페에스1칸
		   String sql="update myinfo set name='"+name+"',addr='"+addr+"' where num="+num;
		   
		   System.out.println(sql);
		   
		   //db연결
		   Connection conn=db.getConnection();
		   //statement
		   Statement stmt=null;
		   
		   try {
			stmt=conn.createStatement();
			
			int a=stmt.executeUpdate(sql);
			
			//update sql문시 없는 번호면 출력 안되야하는데 데이터 전부 입력해야 뜨기때문에 밑에 boolean메서드 활용해서 위에 조건추가
			if(a==0)
				System.out.println("수정할 데이터가 존재하지 않습니다");
			else
				System.out.println("**수정되었습니다**");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("update error: "+e.getMessage());
		}finally {
			db.dbClose(stmt, conn);
		}
	   }

2.update시 시퀀스 유무 확인 메서드

public boolean isData(String num)
	   {
		   //num에 해당하는 데이터가 있으면 true, 없으면 false
		   boolean flag=false;
		   
		   String sql="select * from myinfo where num="+num;
		   
		   Connection conn=db.getConnection();//null;도 가능
		   Statement stmt=null;
		   ResultSet rs=null;
		   
		   try {
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			
			//1개데이터일 경우는 if문
			if(rs.next()) //데이터가 있는 경우
				flag=true;
			else //데이터가 없는 경우
				flag=false;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, stmt, conn);
		}
		   
		   return flag;
	   }

메인

public static void main(String[] args) {
	      // TODO Auto-generated method stub
	      
	      CRUDTest crud=new CRUDTest();
	      
	      Scanner sc=new Scanner(System.in);
	      int n;
	      
	      while(true)
	      {
	         System.out.println("***Oracle db 연습_myinfo***");
	         System.out.println("1.insert 2.select 3.delete 4.update 9.종료");
	         
	         n=Integer.parseInt(sc.nextLine());
	         
	         if(n==1)
	            crud.insert();
	         else if(n==9)
	         {
	            System.out.println("프로그램 종료");
	            break;
	         }
	         
	         else if(n==2)
	        	crud.select();
	         else if(n==3)
	        	crud.delete();
	         else if(n==4)
	        	crud.update();
	         
	      }
	      
	   }

전체코드

package dbtest;

import java.awt.Taskbar.State;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class CRUDTest {

	   DBConnect db=new DBConnect();
	   
	   //insert
	   
	   public void insert()
	   {
	      
	      Scanner sc=new Scanner(System.in);
	      
	      System.out.println("이름입력");
	      String name=sc.nextLine();
	      System.out.println("주소입력");
	      String addr=sc.nextLine();
	      
	      //sql문에는 ''을 써서 실행시키고 java에서는 ""로 사용하기때문에 '"를 잘 구분해서 사용해야한다
	      String sql="insert into myinfo values (seq1.nextval,'"+name+"','"+addr+"',sysdate)";
	      
	      //1. db연결
	      Connection conn=db.getConnection();
	      //2. statement
	      Statement stmt=null;
	      
	      try {
	         stmt=conn.createStatement();
	         stmt.execute(sql);
	         
	         System.out.println("***정보가 추가되었습니다***");
	         
	      } catch (SQLException e) {
	         // TODO Auto-generated catch block
	         e.printStackTrace();
	         System.out.println("insert error"+e.getMessage());
	      } finally {
	         db.dbClose(stmt, conn);
	      }
	      
	   }
	   
	   //select
	   public void select()
	   {
		   System.out.println("시퀀스\t이름\t주소\t날짜");
		   System.out.println("=================================");
		   
		   //요청한 sql문을 String에 저장
		   String sql="select * from myinfo order by num";
		   
		   //Connection
		   Connection conn=null;
		   Statement stmt=null;
		   ResultSet rs=null; //조회출력이므로 필요
		   
		   conn=db.getConnection();
		   try {
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			
			//2개 이상일 경우 while문
			//resultset객체의 next이용해서 행을 선택하고 get메서드를 이용해서 테이블의 컬럼 값을 얻는다
			while(rs.next())
			{
				System.out.println(rs.getInt("num")
									+"\t"+rs.getString("name")
									+"\t"+rs.getString("addr")
									+"\t"+rs.getDate("sdate"));
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, stmt, conn);
		}
	   }
	   
	   //delete
	   public void delete()
	   {
		   //시퀀스 입력 후 삭제
		   Scanner sc=new Scanner(System.in);
		   String num;
		   
		   System.out.println("삭제할 시퀀스는?");
		   num=sc.nextLine();
		   
		   String sql="delete from myinfo where num="+num;
		   
		   //db연결
		   Connection conn=db.getConnection();
		   //statement
		   Statement stmt=null;
		   
		   
		   try {
			stmt=conn.createStatement();
			
			//sql문 실행
			int a=stmt.executeUpdate(sql); //execute는 boolean 값임//executeUpdate는 성공한 갯수 반환 가능
										   //둘다 사용해도 상관없음
			
			if(a==0) //없는 번호 입력시 실제 삭제가 되지않으므로 0반환
				System.out.println("없는 데이터 번호입니다");
			else //삭제성공되면 1 반환 (boolean으로 0,1 false,true 이기 때문
				System.out.println("****삭제되었습니다****");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   }
	   
	   //update
	   public void update()
	   {
		   //수정할 시퀀스 입력 후 이름, 주소 입력
		   Scanner sc=new Scanner(System.in);
		   String num;
		   
		   System.out.println("수정할 시퀀스는?");
		   num=sc.nextLine();
		   
		   //boolean메서드 가져와서 실행
		   if(!this.isData(num))
		   {
			   System.out.println("해당 번호는 존재하지 않습니다");
			   return; //메서드 종료
		   }
		   
		   
		   
		   System.out.println("수정할 이름은?");
		   String name=sc.nextLine();
		   System.out.println("수정할 주소는?");
		   String addr=sc.nextLine();
		   
		   //sql//where문 적을때 1칸띄워야하니까 '뒤에 스페에스1칸
		   String sql="update myinfo set name='"+name+"',addr='"+addr+"' where num="+num;
		   
		   System.out.println(sql);
		   
		   //db연결
		   Connection conn=db.getConnection();
		   //statement
		   Statement stmt=null;
		   
		   try {
			stmt=conn.createStatement();
			
			int a=stmt.executeUpdate(sql);
			
			//update sql문시 없는 번호면 출력 안되야하는데 데이터 전부 입력해야 뜨기때문에 밑에 boolean메서드 활용해서 위에 조건추가
			if(a==0)
				System.out.println("수정할 데이터가 존재하지 않습니다");
			else
				System.out.println("**수정되었습니다**");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("update error: "+e.getMessage());
		}finally {
			db.dbClose(stmt, conn);
		}
	   }
	   
	   //update할 때 없는 번호인지 있는 번호인지 찾아주기
	   public boolean isData(String num)
	   {
		   //num에 해당하는 데이터가 있으면 true, 없으면 false
		   boolean flag=false;
		   
		   String sql="select * from myinfo where num="+num;
		   
		   Connection conn=db.getConnection();//null;도 가능
		   Statement stmt=null;
		   ResultSet rs=null;
		   
		   try {
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			
			//1개데이터일 경우는 if문
			if(rs.next()) //데이터가 있는 경우
				flag=true;
			else //데이터가 없는 경우
				flag=false;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, stmt, conn);
		}
		   
		   return flag;
	   }
	   
	   
	   
	   
	   public static void main(String[] args) {
	      // TODO Auto-generated method stub
	      
	      CRUDTest crud=new CRUDTest();
	      
	      Scanner sc=new Scanner(System.in);
	      int n;
	      
	      while(true)
	      {
	         System.out.println("***Oracle db 연습_myinfo***");
	         System.out.println("1.insert 2.select 3.delete 4.update 9.종료");
	         
	         n=Integer.parseInt(sc.nextLine());
	         
	         if(n==1)
	            crud.insert();
	         else if(n==9)
	         {
	            System.out.println("프로그램 종료");
	            break;
	         }
	         
	         else if(n==2)
	        	crud.select();
	         else if(n==3)
	        	crud.delete();
	         else if(n==4)
	        	crud.update();
	         
	      }
	      
	   }

	}

CRUD -PreparedStatement 활용

전연변수로 DBConnect와 Scanner 호출
DBConnect db=new DBConnect();
Scanner sc=new Scanner(System.in);

insert

public void insertMyshop()
	{
		//Connection
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
	
		System.out.println("상품명은?");
		String sang=sc.nextLine();
		System.out.println("수량?");
		int su=Integer.parseInt(sc.nextLine());
		System.out.println("가격?");
		int dan=Integer.parseInt(sc.nextLine());
		
		//sql...PreparedStatement는 미완의 sql문을 작성
		//위에 값을 '""'안에 넣지 않고 ?로 처리
		String sql="insert into myshop values(seq1.nextval,?,?,?,sysdate)";
		
		try {
			pstmt=conn.prepareStatement(sql);//미리 전달하고 ?에 대한 전달이 나중에
			
			//? 순서 중요//?를 순서대로 바인딩
			pstmt.setString(1, sang);//1번째 ?는
			pstmt.setInt(2, su);
			pstmt.setInt(3, dan);
			
			
			//업데이트
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("insert 성공!!!");
			else
				System.out.println("insert 실패ㅠㅠ");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}

select

public void selectMyshop()
	{
		System.out.println("시퀀스\t상품\t수량\t가격\t입고날짜");
		System.out.println("======================================");
		
		String sql="select * from myshop";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
	
		try {
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				System.out.println(rs.getInt("shopnum")
									+"\t"+rs.getString("sangpum")
									+"\t"+rs.getInt("su")
									+"\t"+rs.getInt("price")
									+"\t"+rs.getDate("ipgo"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
		
	}

delete

public void deleteMyshop()
	{
		String num;
		
		System.out.println("삭제할 시퀀스는?");
		num=sc.nextLine();
		
		String sql="delete from myshop where shopnum=?";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setString(1, num);
			
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("***삭제했어요***");
			else
				System.out.println("없는 데이터 입니다");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}

update(메서드 2개)

1.update 메서드

public void updateMyshop()
	{
		String num;
		
		System.out.println("수정할 시퀀스는?");
		num=sc.nextLine();
		
		if(!isData(num))
		{
			System.out.println("데이터가 없습니다");
			return;
		}
		
		
		System.out.println("수정할 상품명은?");
		String name=sc.nextLine();
		
		System.out.println("수정할 수량은?");
		String su=sc.nextLine();
		
		System.out.println("수정할 가격은?");
		String price=sc.nextLine();
		
		String sql="update myshop set sangpum=?, su=?, price=? where shopnum=?";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		try {
			//sql문 전달
			pstmt=conn.prepareStatement(sql);
			
			//? 4개 바인딩
			pstmt.setString(1, name);
			pstmt.setString(2, su);
			pstmt.setString(3, price);
			pstmt.setString(4, num);
			
			
			//업데이트
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("***수정되었습니다***");
			else
				System.out.println("데이터에 없습니다");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}

2.시퀀스 확인 메서드

public boolean isData(String shopnum)
	{
		boolean flag=false;
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="select * from myshop where shopnum=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			
			//?
			pstmt.setString(1, shopnum);
			
			rs=pstmt.executeQuery();
			
			if(rs.next())
				flag=true;
			else
				flag=false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
		
		return flag;
	}

select로 search

public void searchSangpum()
	{
		System.out.println("검색할 상품명(일부단어)");
		String sang=sc.nextLine();
		
		//?를 바인딩할때 정의
		String sql="select * from myshop where sangpum like ?";
		System.out.println(sql);
		
		System.out.println("시퀀그\t상품명\t수량\t단가\t날짜");
		System.out.println("-----------------------------------------------------");
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, "%"+sang+"%"); // sangpum의 일부 단어만 포함해도 검색되도록
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				System.out.println(rs.getInt("shopnum")
						+"\t"+rs.getString("sangpum")
						+"\t"+rs.getInt("su")
						+"\t"+rs.getInt("price")
						+"\t"+rs.getDate("ipgo"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
	}

메인

public static void main(String[] args) {

		PrepareMyShop shop=new PrepareMyShop();
		//클래스안에 전연변수에 선언해줘도 메인과는 별개라서 출력용 Scanner에 선언
		Scanner sc=new Scanner(System.in);
		int n;
		
		while(true)
		{
			System.out.println("****상품입고****");
			System.out.println("1.상품추가   2.상품삭제   3.상품수정   4.상품전체출력    5.상품검색    9.종료");
			n=Integer.parseInt(sc.nextLine());
			
			if(n==1)
				shop.insertMyshop();
			else if(n==2)
				shop.deleteMyshop();
			else if(n==3)
				shop.updateMyshop();
			else if(n==4)
				shop.selectMyshop();
			else if(n==5)
				shop.searchSangpum();
			else if(n==9)
			{
				System.out.println("종료합니다");
				break;
			}
		}
				
	}

전체코드

public class PrepareMyShop {
	
	DBConnect db=new DBConnect();
	Scanner sc=new Scanner(System.in);
	
	//insert
	public void insertMyshop()
	{
		//Connection
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
	
		System.out.println("상품명은?");
		String sang=sc.nextLine();
		System.out.println("수량?");
		int su=Integer.parseInt(sc.nextLine());
		System.out.println("가격?");
		int dan=Integer.parseInt(sc.nextLine());
		
		//sql...PreparedStatement는 미완의 sql문을 작성
		//위에 값을 '""'안에 넣지 않고 ?로 처리
		String sql="insert into myshop values(seq1.nextval,?,?,?,sysdate)";
		
		try {
			pstmt=conn.prepareStatement(sql);//미리 전달하고 ?에 대한 전달이 나중에
			
			//? 순서 중요//?를 순서대로 바인딩
			pstmt.setString(1, sang);//1번째 ?는
			pstmt.setInt(2, su);
			pstmt.setInt(3, dan);
			
			
			//업데이트
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("insert 성공!!!");
			else
				System.out.println("insert 실패ㅠㅠ");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public void selectMyshop()
	{
		System.out.println("시퀀스\t상품\t수량\t가격\t입고날짜");
		System.out.println("======================================");
		
		String sql="select * from myshop";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
	
		try {
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				System.out.println(rs.getInt("shopnum")
									+"\t"+rs.getString("sangpum")
									+"\t"+rs.getInt("su")
									+"\t"+rs.getInt("price")
									+"\t"+rs.getDate("ipgo"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
		
	}
	
	public void deleteMyshop()
	{
		String num;
		
		System.out.println("삭제할 시퀀스는?");
		num=sc.nextLine();
		
		String sql="delete from myshop where shopnum=?";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setString(1, num);
			
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("***삭제했어요***");
			else
				System.out.println("없는 데이터 입니다");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public void updateMyshop()
	{
		String num;
		
		System.out.println("수정할 시퀀스는?");
		num=sc.nextLine();
		
		if(!isData(num))
		{
			System.out.println("데이터가 없습니다");
			return;
		}
		
		
		System.out.println("수정할 상품명은?");
		String name=sc.nextLine();
		
		System.out.println("수정할 수량은?");
		String su=sc.nextLine();
		
		System.out.println("수정할 가격은?");
		String price=sc.nextLine();
		
		String sql="update myshop set sangpum=?, su=?, price=? where shopnum=?";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		try {
			//sql문 전달
			pstmt=conn.prepareStatement(sql);
			
			//? 4개 바인딩
			pstmt.setString(1, name);
			pstmt.setString(2, su);
			pstmt.setString(3, price);
			pstmt.setString(4, num);
			
			
			//업데이트
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("***수정되었습니다***");
			else
				System.out.println("데이터에 없습니다");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public boolean isData(String shopnum)
	{
		boolean flag=false;
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="select * from myshop where shopnum=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			
			//?
			pstmt.setString(1, shopnum);
			
			rs=pstmt.executeQuery();
			
			if(rs.next())
				flag=true;
			else
				flag=false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
		
		return flag;
	}
	
	//검색
	public void searchSangpum()
	{
		System.out.println("검색할 상품명(일부단어)");
		String sang=sc.nextLine();
		
		//?를 바인딩할때 정의
		String sql="select * from myshop where sangpum like ?";
		System.out.println(sql);
		
		System.out.println("시퀀그\t상품명\t수량\t단가\t날짜");
		System.out.println("-----------------------------------------------------");
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, "%"+sang+"%"); // sangpum의 일부 단어만 포함해도 검색되도록
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				System.out.println(rs.getInt("shopnum")
						+"\t"+rs.getString("sangpum")
						+"\t"+rs.getInt("su")
						+"\t"+rs.getInt("price")
						+"\t"+rs.getDate("ipgo"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
	}
	
	

	public static void main(String[] args) {

		PrepareMyShop shop=new PrepareMyShop();
		//클래스안에 전연변수에 선언해줘도 메인과는 별개라서 출력용 Scanner에 선언
		Scanner sc=new Scanner(System.in);
		int n;
		
		while(true)
		{
			System.out.println("****상품입고****");
			System.out.println("1.상품추가   2.상품삭제   3.상품수정   4.상품전체출력    5.상품검색    9.종료");
			n=Integer.parseInt(sc.nextLine());
			
			if(n==1)
				shop.insertMyshop();
			else if(n==2)
				shop.deleteMyshop();
			else if(n==3)
				shop.updateMyshop();
			else if(n==4)
				shop.selectMyshop();
			else if(n==5)
				shop.searchSangpum();
			else if(n==9)
			{
				System.out.println("종료합니다");
				break;
			}
		}
				
	}

}

과제

과제 조건

시퀀스:seq_stu
mystudent

stu_num:숫자(5) 기본키
stu_namepu:문자열(20)
stu_grade:숫자(5) :학번
hp:문자열(20)
addr:문자열(30)
age:숫자(5)
sdate-현재날짜

학생정보
1.입력
2.출력
3.삭제
4.수정
5.검색
9.종료

===========================================
시퀀스 입력해서 삭제,수정할 것 (수정할 번호 없으면 바로 종료되게)
학생명의 일부 입력해서 검색되게 하세요
=============================다 하면 카페(쌍용강남)
JDDB과제물 최성현

숙제확인 가능하게 캡처해서 올리기

ex 1~9번 기능 작동하는지

과제용 sql문

--시퀀스
create sequence seq1;

--테이블(myinfo)
create table myinfo(num number(5) primary key, name varchar2(20), addr varchar2(20), sdate date);

select * from myinfo;

--테이블(myshop)
create table myshop(shopnum number(5) primary key,
sangpum varchar2(20),
su number(5),
price number(5),
ipgo date);

select * from myshop;

Connect class

package homework_0711;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConnect {
	
	static final String URL="jdbc:oracle:thin:@localhost:1521:XE";

	String driver="oracle.jdbc.driver.OracleDriver";
	
	public DBConnect() {
		try {
			Class.forName(driver);
			System.out.println("오라클 드라이버 성공!!!");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("오라클 드라이버 실패!!!");
		}
	}
	
	public Connection getConnection()
	{
		Connection conn=null;
		
		try {
			conn=DriverManager.getConnection(URL, "tjdgus", "1234");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	public void dbClose(ResultSet rs,PreparedStatement pstmt,Connection conn)
	{
		try {
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void dbClose(PreparedStatement pstmt,Connection conn)
	{
		try {
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

CRUD 클래스

package homework_0711;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class homework0711 {
	
	DBConnect db=new DBConnect();
	Scanner sc=new Scanner(System.in);
	
	public void insertMystudent()
	{
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		System.out.println("학생명은?");
		String name=sc.nextLine();
		System.out.println("학번은?");
		String grade=sc.nextLine();
		System.out.println("휴대폰 번호는?");
		String hp=sc.nextLine();
		System.out.println("주소는?");
		String addr=sc.nextLine();
		System.out.println("나이는?");
		int age=Integer.parseInt(sc.nextLine());
		
		String sql="insert into mystudent values(seq_stu.nextval,"
												+ "?,?,?,?,?,sysdate)";
		
		try {
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setString(1, name);
			pstmt.setString(2, grade);
			pstmt.setString(3, hp);
			pstmt.setString(4, addr);
			pstmt.setInt(5, age);
			
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("insert SUCCESS");
			else
				System.out.println("insert FAIL");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public void selectMystudent()
	{
		System.out.println("SEQUENCE\tNAME\tGRADE\tHP\tADDR\tAGE\tDATE");
		System.out.println("-----------------------------------------------------------------");
		
		String sql="select * from mystudent";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				System.out.println(rs.getInt("stu_num")
									+"\t"+rs.getString("stu_name")
									+"\t"+rs.getInt("stu_grade")
									+":학년\t"+rs.getString("hp")
									+"\t"+rs.getString("addr")
									+"\t"+rs.getInt("age")
									+"\t"+rs.getDate("sdate"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
	}
	
	public void deleteMystudent()
	{
		String num;
		
		System.out.println("삭제할 학생 번호는?");
		num=sc.nextLine();
		
		String sql="delete from mystudent where stu_num=?";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setString(1, num);
			
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("DELETE SUCCESS");
			else
				System.out.println("DELETE FAIL");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	public void updateMystudent()
	{
		System.out.println("수정할 학생번호는?");
		String num=sc.nextLine();
		
		if(!isData(num))
		{
			System.out.println("학생 정보가 없습니다");
			return;
		}
		
		System.out.println("수정할 학생 이름은?");
		String name=sc.nextLine();
		System.out.println("수정할 학생 학년은?");
		String grade=sc.nextLine();
		System.out.println("수정할 학생 번호는?");
		String hp=sc.nextLine();
		System.out.println("수정할 학생 주소는?");
		String addr=sc.nextLine();
		System.out.println("수정할 학생 나이는?");
		String age=sc.nextLine();
		
		String sql="update mystudent set stu_name=?, stu_grade=?,"
					+"hp=?, addr=?, age=? where stu_num=?";
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, grade);
			pstmt.setString(3, hp);
			pstmt.setString(4, addr);
			pstmt.setString(5, age);
			pstmt.setString(6, num);
			
			int a=pstmt.executeUpdate();
			
			if(a==1)
				System.out.println("UPDATE SUCCESS");
			else
				System.out.println("UPDATE FAIL");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(pstmt, conn);
		}
	}
	
	
	public boolean isData(String stu_num) {
		
		boolean flag = false;
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="select * from mystudent where stu_num=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, stu_num);
			
			rs=pstmt.executeQuery();
			
			if(rs.next())
				flag=true;
			else
				flag=false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
		
		return flag;
	}
	
	public void searchMystudent()
	{
		System.out.println("검색할 학생명");
		String name=sc.nextLine();
		
		String sql="select * from mystudent where stu_name like ?";
		System.out.println(sql);
		
		System.out.println("SEQUENCE\tNAME\tGRADE\tHP\tADDR\tAGE\tDATE");
		System.out.println("-----------------------------------------------------------------");
		
		Connection conn=db.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, "%"+name+"%");
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				System.out.println(rs.getInt("stu_num")
						+"\t"+rs.getString("stu_name")
						+"\t"+rs.getInt("stu_grade")
						+":학년\t"+rs.getString("hp")
						+"\t"+rs.getString("addr")
						+"\t"+rs.getInt("age")
						+"\t"+rs.getDate("sdate"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			db.dbClose(rs, pstmt, conn);
		}
		
	}

	public static void main(String[] args) {
		homework0711 s=new homework0711();
		Scanner sc=new Scanner(System.in);
		int n;
		
		while(true)
		{
			System.out.println("****학생정보****");
			System.out.println("1.INSERT   2.SELECT   3.DELETE  4.UPDATE   5.SEARCH   9.EXIT");
			n=Integer.parseInt(sc.nextLine());
			
			if(n==1)
				s.insertMystudent();
			else if(n==9)
			{
				System.out.println("끝!");
				break;
			}
			else if(n==2)
				s.selectMystudent();
			else if(n==3)
				s.deleteMystudent();
			else if(n==4)
				s.updateMystudent();
			else if(n==5)
				s.searchMystudent();
					
		}
	}

}

최성현

백엔드 개발자로서 성장해 나가는 성현이의 블로그~

이전 포스트

자바-오라클 CRUD

Java

sql table 생성

sql 문

connect 만들기

오라클 연동

코드전체

CRUD(입력,조회,수정,삭제,검색)

insert

select

delete

update (2개 메서드)

메인

전체코드

CRUD -PreparedStatement 활용

insert

select

delete

update(메서드 2개)

select로 search

메인

전체코드

과제

과제 조건

과제용 sql문

Connect class

CRUD 클래스

자바-오라클 조인

0개의 댓글