2021. 05. 04(화) TIL

eastgun_·2021년 5월 4일
0
post-thumbnail

Database

JDBC

  • Java DataBase Connectivity
  • 자바가 데이터베이스에 접속해서 SQL을 실행할 수 있도록 하는 표준이다.
  • java.sql 패키지와 javax.sql 패키지에 관련 인터페이스와 클래스가 존재한다.

주요 API

  • Connection 인터페이스
    • 자바프로그램과 데이터베이스간의 연결을 담당하는 객체다.
    • Statement createStatement()
      • SQL전송을 담당하는 Statement객체를 반환한다.
    • PreparedStatement prepareStatement(String sql)
      • SQL전송을 담당하는 Statement객체를 반환한다.
    • void commit()
      • SQL실행결과를 DB에 반영시킨다.
    • void rollback()
      • SQL실행결과의 DB 반영을 취소시킨다.
    • void close()
      • DB와의 접속을 해제한다.
  • PreparedStatement 인터페이스
    • SQL의 전송을 담당하는 객체다.
    • int executeUpdate()
      • INSERT, UPDATE, DELETE 쿼리를 DB로 전송하고, 실행결과를 반환한다.
    • ResultSet executeQuery()
      • SELECT 쿼리를 DB로 전송하고, 조회결과를 반환한다.
    • void setXXX(int index, XXX value)
      • ?위치에 실제값(파라미터값)을 설정한다.
      • void setString(int index, String value)
      • void setInt(int index, int value)
      • void setLong(int index, long value)
      • void setDouble(int index, double value)
      • void setDate(int index, Date value)
    • void close()
      • DB 연결 자원을 해제한다.
  • ResultSet 인터페이스
    • SELECT 쿼리문의 조회결과를 담당하는 객체다.
    • boolean next()
      • 커서를 다음행으로 이동시킨다. 데이터행이 존재하면 true를 반환한다.
    • XXX getXXX(String columnName)
      • 컬럼명에 해당하는 값을 반환한다.
    • void close()
      • DB 연결 자원을 해제한다.
  • DriverManager 클래스
    • JDCB 표준을 구현한 JDBC드라이버를 관리하는 클래스다.
    • JDBC드라이버는 각 DBMS제작사가 자사의 DBMS에 엑세스할 수 있도록 위에 언급한 인터페이스를 구현한 클래스들의 모음이다.
    • Connection getConnection(String url, String username, String password)
      • url이 가르키는 데이터베이스에 지정된 계정정보로 연결을 시도한 후 연결을 담당하는 Connection객체를 반환한다.

JDBC API를 사용해서 자바와 데이터베이스 연동하기

  • 자바와 데이터베이스 연동 절차
    1. JDBC 드라이브 JVM 메모리에 로딩하기
      Class.forName("oracle.jdbc.OracleDriver");
    1. DBMS와 연결을 담당하는 Connection객체 획득하기
      String url = "jdbc:oracle:thin:@localhost:1521:xe";
      String username = "hta";
      String password = "zxcv1234";
      Connection connection = DriverManager.getConnection(url, username, password);
    1. DDBMS에 SQL 전송과 실행을 담당하는 PreparedStatement객체 획득하기
      String sql = "insert into department(deptno, dname, part, build) values (?, ?, ?, ?)";
      PreparedStatement pstmt = connection.prepareStatement(slq);
    1. SQL의 ?에 값을 대응시키기
      pstmt.setInt(1, 204);
      pstmt.setString(2, '정밀기계공학과');
      pstmt.setInt(3, 200);
      pstmt.setString(4, '정밀기계실험관');
    1. SQL을 DBMS에 보내서 실행시키기
      pstmt.executeUpdate();
    1. DBMS와 연결된 모든 자원을 해제하기
      pstmt.close();
      connection.close();

데이터 조회하기

  • 레코드 한 개 조회하기
  public Department getDepartmentByNo(int deptNo) throws SQLException {
    Department department = null;
    String sql = "String * from department where deptno = ?";
  
    String driverClassName = "oracle.jdbc.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String username = "hta";
    String password = "zxcv1234";
  
    Class.forName(driverClassName);
    Connection connection = DriverManager.getConnection(url, username, password);
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setInt(1, deptNo);
    
    ResultSet rs = pstmt.executeQuery();
    if (rs.next()) {
      department = new Department();
      department.setNo(rs.getInt("deptno"));
      department.setName(rs.getString("dname"));
      department.setPart(rs.getInt("part"));
      department.setBuild(rs.getString("build"));
    }
    rs.close();
    pstmt.close();
    connection.close();
    
    return department;
  }
  • 레코드 여러 개 조회하기
  public List<Department> getAllDepartments(int deptNo) throws SQLException {
    List<Department> departmentList = new ArrayList<>();
    String sql = "String * from department";
  
    String driverClassName = "oracle.jdbc.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String username = "hta";
    String password = "zxcv1234";
  
    Class.forName(driverClassName);
    Connection connection = DriverManager.getConnection(url, username, password);
    PreparedStatement pstmt = connection.prepareStatement(sql);
    
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      Department department = new Department();
      department.setNo(rs.getInt("deptno"));
      department.setName(rs.getString("dname"));
      department.setPart(rs.getInt("part"));
      department.setBuild(rs.getString("build"));
      
      departmentList.add(department);
    }
    rs.close();
    pstmt.close();
    connection.close();
    
    return departmentList;
  }

  • 레코드를 추가하기
  public void insertDepartment(Department department) throws SQLException {
    String sql = "insert into department(deptno, dname, part, build) values (?,?,?,?)";
  
    String driverClassName = "oracle.jdbc.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String username = "hta";
    String password = "zxcv1234";
  
    Class.forName(driverClassName);
    Connection connection = DriverManager.getConnection(url, username, password);
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setInt(1, department.getNo());
    pstmt.setString(2, department.getName());
    pstmt.setInt(3, department.getPart());
    pstmt.setString(4, department.getBuild());
    pstmt.executeUpdate();
    
    pstmt.close();
    connection.close();
  }
  • 레코드 삭제하기
  public void deleteDepartmentByNo(int deptNo) throws SQLException {
    String sql = "delete from department where deptno = ?";
  
    String driverClassName = "oracle.jdbc.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String username = "hta";
    String password = "zxcv1234";
  
    Class.forName(driverClassName);
    Connection connection = DriverManager.getConnection(url, username, password);
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setInt(1, deptNo);
    pstmt.executeUpdate();
    
    pstmt.close();
    connection.close();
  }
  • 레코드 변경하기
  public void updateDepartment(Department department) throws SQLException {
    String sql = "update department ";
    sql += "      set ";
    sql += "      dname = ?, ";
    sql += "      part = ?, ";
    sql += "      build = ? ";
    sql += "      where deptno = ? "
    
    String driverClassName = "oracle.jdbc.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String username = "hta";
    String password = "zxcv1234";
  
    Class.forName(driverClassName);
    Connection connection = DriverManager.getConnection(url, username, password);
    PreparedStatement pstmt = connection.prepareStatement(sql);
   
    pstmt.setString(1, department.getName());
    pstmt.setInt(2, department.getPart());
    pstmt.setString(3, department.getBuild());
    pstmt.setInt(4, department.getNo());
    pstmt.executeUpdate();
    
    pstmt.close();
    connection.close();
  }

package demo;

import java.util.Date;

public class Book {

	private int no;
	private String title;
	private String writer;
	private int price;
	private int discountPrice;
	private int stock;
	private Date createdDate;
	
	public Book () {}

	public int getNo() {
		return no;
	}

	public void setNo(int no) {
		this.no = no;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getWriter() {
		return writer;
	}

	public void setWriter(String writer) {
		this.writer = writer;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

	public int getDiscountPrice() {
		return discountPrice;
	}

	public void setDiscountPrice(int discountPrice) {
		this.discountPrice = discountPrice;
	}

	public int getStock() {
		return stock;
	}

	public void setStock(int stock) {
		this.stock = stock;
	}

	public Date getCreatedDate() {
		return createdDate;
	}

	public void setCreatedDate(Date createdDate) {
		this.createdDate = createdDate;
	}

	@Override
	public String toString() {
		return "Book [no=" + no + ", title=" + title + ", writer=" + writer + ", price=" + price + ", discountPrice="
				+ discountPrice + ", stock=" + stock + ", createdDate=" + createdDate + "]";
	}
}
package demo;

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 java.util.Scanner;

public class BookApp {
	
	static String driverClassName = "oracle.jdbc.OracleDriver";
	static String url = "jdbc:oracle:thin:@localhost:1521:xe";
	static String username = "hr";
	static String password = "zxcv1234";
	
	public static void insertBook(Book book) {
		String sql = "INSERT INTO sample_books "
				   + "(book_no, book_title, book_writer, book_price, book_discount_price, book_stock) "
				   + "VALUES (?, ?, ?, ?, ?, ?)";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, book.getNo());
			pstmt.setString(2, book.getTitle());
			pstmt.setString(3, book.getWriter());
			pstmt.setInt(4, book.getPrice());
			pstmt.setInt(5, book.getDiscountPrice());
			pstmt.setInt(6, book.getStock());
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("[오류코드]" + e.getErrorCode());
			System.out.println("[오류메시지]" + e.getMessage());
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
			try { if (conn != null) conn.close(); } catch (SQLException e) {}
		}
	}
	
	// 반환값이 null 일 수도 있다. 조회된 데이터가 없으면 null을 반환한다.
	public static Book selectBookByNo(int bookNo) {
		String sql = "SELECT book_no, book_title, book_writer, book_price, book_discount_price, book_stock, book_created_date "
				   + "FROM sample_books "
				   + "WHERE book_no = ? ";
		
		Book book = null;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bookNo);
			rs = pstmt.executeQuery(); 
			
			// 한 행이 조회되거나 조회된 행이 없거나 2가지만 가능
			// rs.next() 실행결과가 true면 조회된 행이 있는경우
			// rs.next() 실행결과가 false면 조회된 행이 없는경우
			if (rs.next()) {	
				book = new Book();
				book.setNo(rs.getInt("book_no"));
				book.setTitle(rs.getString("book_title"));
				book.setWriter(rs.getString("book_writer"));
				book.setPrice(rs.getInt("book_price"));
				book.setDiscountPrice(rs.getInt("book_discount_price"));
				book.setStock(rs.getInt("book_stock"));
				book.setCreatedDate(rs.getDate("book_created_date"));
			}
			
		} catch (SQLException e) {
			System.out.println("[오류코드] " + e.getErrorCode());
			System.out.println("[오류메시지] " + e.getMessage());
		} finally {
			try {if (rs != null) rs.close(); } catch (SQLException e) {}
			try {if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
			try {if (conn != null) conn.close(); } catch (SQLException e) {}
		}
		
		
		return book;
	}
	
	// 반환값은 언제나 null이 아니다. 조회된 데이터가 없으면 List객체가 비어있을 뿐이다.
	public static List<Book> selectAllBooks() {
		String sql = "SELECT book_no, book_title, book_writer, book_price, book_discount_price, book_stock, book_created_date "
				   + "FROM sample_books "
				   + "ORDER BY book_no DESC ";
		
		
		List<Book> books = new ArrayList<Book>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery(); 
			
			// 여러 행이 조회되거나 조회된 행이 없을 수 있다.
			while (rs.next()) {
				Book book = new Book();
				book.setNo(rs.getInt("book_no"));
				book.setTitle(rs.getString("book_title"));
				book.setWriter(rs.getString("book_writer"));
				book.setPrice(rs.getInt("book_price"));
				book.setDiscountPrice(rs.getInt("book_discount_price"));
				book.setStock(rs.getInt("book_stock"));
				book.setCreatedDate(rs.getDate("book_created_date"));
				
				// Book객체를 ArrayList객체에 추가한다.
				books.add(book);
				
			}
		} catch (SQLException e) {
			System.out.println("[오류코드] " + e.getErrorCode());
			System.out.println("[오류메시지] " + e.getMessage());
		} finally {
			try {if (rs != null) rs.close(); } catch (SQLException e) {}
			try {if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
			try {if (conn != null) conn.close(); } catch (SQLException e) {}
		}
		
		return books;
	}
	
	public static void updateBook(Book book) {
		
	}
	
	public static void deleteBook(int bookNo) {
		String sql = "DELETE FROM sample_books "
				   + "WHERE book_no = ?";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DriverManager.getConnection(BookApp.url, BookApp.username, BookApp.password);
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, bookNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("[오류코드]" + e.getErrorCode());
			System.out.println("[오류메시지]" + e.getMessage());
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
			try { if (conn != null) conn.close(); } catch (SQLException e) {}
		}
	}

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}
		
		while (true) {
			System.out.println("---------------------------------------------------");
			System.out.println("1.조회 2.등록 3.수정 4.삭제 0.종료");
			System.out.println("---------------------------------------------------");
			
			System.out.print("메뉴를 선택하세요 : ");
			int menuNo = scanner.nextInt();
			System.out.println();
			scanner.nextLine();
			
			if (menuNo == 1) {
				System.out.println("[전체 도서 조회]");
				
				List<Book> books = BookApp.selectAllBooks();
				System.out.println(books);
				
				if (books.isEmpty()) {
					System.out.println("[안내] 조회된 책 정보가 존재하지 않습니다.");
				} else {
					System.out.println("------------------------------------------");
					for (Book book : books) {
						System.out.println("번호 : " + book.getNo());
						System.out.println("제목 : " + book.getTitle());
						System.out.println("가격 : " + book.getPrice());
						System.out.println();
					}
					System.out.println("------------------------------------------");
				}
				
			} else if (menuNo == 2) {
				System.out.println("[새 도서 등록]");
				
				System.out.print("제목을 입력해주세요 : ");
				String title = scanner.nextLine();
				
				System.out.print("저자를 입력해주세요 : ");
				String writer = scanner.nextLine();
				System.out.print("번호를 입력해주세요 : ");
				int no = scanner.nextInt();
				System.out.print("가격을 입력해주세요 : ");
				int price = scanner.nextInt();
				System.out.print("할인가격을 입력해주세요 : ");
				int discountPrice = scanner.nextInt();
				System.out.print("재고량을 입력해주세요 : ");
				int stock = scanner.nextInt();
				
				Book book = new Book();
				book.setTitle(title);
				book.setWriter(writer);
				book.setNo(no);
				book.setPrice(price);
				book.setDiscountPrice(discountPrice);
				book.setStock(stock);
				
				insertBook(book);
				System.out.println("[안내] 새 책이 등록되었습니다.");
			} else if (menuNo == 3) {
				System.out.println("[도서 정보 수정]");
				
			} else if (menuNo == 4) {
				System.out.println("[도서 정보 삭제]");
				
			} else if (menuNo == 0) {
				System.out.println("[프로그램 종료]");
				break;
			}
			
			System.out.println();
			System.out.println();
			System.out.println();
		}
		
		scanner.close();
	}
}

profile
게으르고 싶은 예비 개발자입니다.

관심 있을 만한 포스트

0개의 댓글