[JDBC] 연습하기

기존에 생성한 booklist를 이용(파일첨부)

00_A.sql

0.01MB

1. select

package JDBC02;

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

public class BookSelect {
	public static void main(String[] args) {
		String url="jdbc:oracle:thin:@localhost:1521:xe";
		Connection con=null;
		PreparedStatement pstmt=null; 
		ResultSet rs=null;
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con=DriverManager.getConnection(url, "SCOTT", "tiger");
		
			String sql="select*from booklist order by num desc";
			// SQLSyntaxError가 뜨면 위의 코드를 잘못쓴거임
			System.out.println("도서번호\t출판년도\t입고가격\t대여가격\t등급\t제목");
			System.out.println("------------------------------------------------------------------");
						
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			
			while(rs.next()) {
				int num=rs.getInt("num");
				int makeyear=rs.getInt("makeyear");
				int inprice=rs.getInt("inprice");
				int rentprice=rs.getInt("rentprice");
				String grade=rs.getString("grade");
				String subject=rs.getString("subject");

				System.out.printf("%8d\t%d \t%d\t%d \t%s\t%s\n",
						num, makeyear, inprice, rentprice, grade, 
                        subject);
			}
			// sql문 실행
			int result=pstmt.executeUpdate();

		} catch (ClassNotFoundException e) {e.printStackTrace();
		} catch (SQLException e) {e.printStackTrace();}
		try {
			if(con!=null) con.close();
			if(pstmt!=null) pstmt.close();
			if(rs!=null) rs.close();
		} catch (SQLException e) {e.printStackTrace();}
	}
}

 

 

 

2. insert

package JDBC02;

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

public class BookInsert {
	public static void main(String[] args) {
		String url="jdbc:oracle:thin:@localhost:1521:xe";
		Connection con=null;
		PreparedStatement pstmt=null; 
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con=DriverManager.getConnection(url, "SCOTT", "tiger");
			
            Scanner sc=new Scanner(System.in);
			
			System.out.printf("도서제목: ");
			String subject=sc.nextLine();
			System.out.printf("출판년도: ");
			int makeyear=Integer.parseInt(sc.nextLine());
			System.out.printf("입고가격: ");
			int inprice=Integer.parseInt(sc.nextLine());
			System.out.printf("대여가격: ");
			int rentprice=Integer.parseInt(sc.nextLine());
			System.out.printf("등급(13,18,all): ");
			String grade=sc.nextLine();
			
			String sql="insert into booklist values(book_seq.nextVal,?,?,?,?,?)";
			// 콘솔창에 SQL thin text error 뜨면 위의 코드를 잘못 쓴거임

			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, subject);
			pstmt.setInt(2, makeyear);
			pstmt.setInt(3, inprice);
			pstmt.setInt(4, rentprice);
			pstmt.setString(5, grade);
			
			int result=pstmt.executeUpdate();
			if(result==1) System.out.println("저장 성공");
			else System.out.println("저장 실패");
			
		} catch (ClassNotFoundException e) {e.printStackTrace();
		} catch (SQLException e) {e.printStackTrace();}
		
		try {
			if(con!=null) con.close();
			if(pstmt!=null) pstmt.close();
		} catch (SQLException e) {e.printStackTrace();}
	}
}

 

3. update

package JDBC02;

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

public class BookUpdate {
	public static void main(String[] args) {
		String url="jdbc:oracle:thin:@localhost:1521:xe";
		Connection con=null;
		PreparedStatement pstmt=null; 
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con=DriverManager.getConnection(url, "SCOTT", "tiger");
		
         	Scanner sc=new Scanner(System.in);
			
			System.out.printf("수정할 도서번호: ");
			String num=sc.nextLine();
			String sql="Update booklist set";
			int cnt=0;
			System.out.printf("수정할 제목(건너뛰려면 엔터): ");
			String subject=sc.nextLine();
			if(!subject.equals("")) {
				sql=sql+" subject='"+subject+"',";
				cnt++;
			}
			System.out.printf("수정할 출판년도(건너뛰려면 엔터): ");
			String makeyear=sc.nextLine();
			if(!makeyear.equals("")) {
				sql=sql+" makeyear="+makeyear+",";
				cnt++;
			}
			System.out.printf("수정할 입고가격(건너뛰려면 엔터): ");
			String inprice=sc.nextLine();
			if(!inprice.equals("")) {
				sql=sql+" inprice="+inprice+",";
				cnt++;
			}
			System.out.printf("수정할 대여가격(건너뛰려면 엔터): ");
			String rentprice=sc.nextLine();
			if(!rentprice.equals("")) {
				sql=sql+" rentprice="+rentprice+",";
				cnt++;
			}
			System.out.printf("수정할 등급(건너뛰려면 엔터): ");
			String grade=sc.nextLine();
			if(!grade.equals("")) {
				sql=sql+" grade='"+grade+"',";
				cnt++;
			}
			if(cnt==0) {
				System.out.println("수정사항 없음");
				return;
			}

			String sql2=sql.substring(0,sql.length()-1);
			String sql3=sql2+" where num="+num;
			// System.out.println(sql3);
            // 생성된 SQL 명령을 확인할 수 있는 코드
			
			pstmt=con.prepareStatement(sql3);
			int result=pstmt.executeUpdate();
			if(result==1) System.out.println("수정 성공");
			else System.out.println("수정 실패");
			
		} catch (ClassNotFoundException e) {e.printStackTrace();
		} catch (SQLException e) {e.printStackTrace();}
		try {
			if(con!=null) con.close();
			if(pstmt!=null) pstmt.close();
		} catch (SQLException e) {e.printStackTrace();}
	}
}

System.out.println(sql3); → Update booklist set subject='자바의 정석', grade='18' where num=12

12번 레코드가 수정된 것을 확인할 수 있음

 

4. delete

package JDBC02;

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

public class BookDelete {
	public static void main(String[] args) {
		String url="jdbc:oracle:thin:@localhost:1521:xe";
		Connection con=null;
		PreparedStatement pstmt=null; 
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con=DriverManager.getConnection(url, "SCOTT", "tiger");
			
			Scanner sc=new Scanner(System.in);
			System.out.print("삭제할 도서번호: ");
			String num=sc.nextLine();
			String sql="delete from booklist where num=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(num));
			int result=pstmt.executeUpdate();
			if(result==1) System.out.println("삭제 성공");
			else System.out.println("삭제 실패");
			
		} catch (ClassNotFoundException e) {e.printStackTrace();
		} catch (SQLException e) {e.printStackTrace();}
		try {
			if(con!=null) con.close();
			if(pstmt!=null) pstmt.close();
		} catch (SQLException e) {e.printStackTrace();}
	}
}