Exemple #1
0
  public int insert(BookVo vo) {
    int count = 0;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      // 1. 드라이버 로딩
      Class.forName("oracle.jdbc.driver.OracleDriver");

      // 2. 연결 얻어오기
      String url = "jdbc:oracle:thin:@localhost:1521:xe";
      conn = DriverManager.getConnection(url, "skudb", "skudb");

      // 3. statement 준비
      String sql = "insert into book values(seq_book.nextval, ?, ?, ?, ?)";
      pstmt = conn.prepareStatement(sql);

      // 4. 바인딩
      pstmt.setString(1, vo.getTitle());
      pstmt.setInt(2, vo.getRate());
      pstmt.setInt(3, vo.getStatus());
      pstmt.setLong(4, vo.getAuthorNo());

      // 5. query 실행
      count = pstmt.executeUpdate();

    } catch (ClassNotFoundException e) {
      System.out.println("드라이버 로딩 실패 :" + e);
    } catch (SQLException e) {
      System.out.println("error:" + e);
    } finally {
      try {
        // 6. 자원정리
        if (pstmt != null) {
          pstmt.close();
        }

        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        System.out.println("error:" + e);
      }
    }

    return count;
  }
Exemple #2
0
  public List<BookVo> getList() {
    List<BookVo> list = new ArrayList<BookVo>();

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      // 1. 드라이버 로딩
      Class.forName("oracle.jdbc.driver.OracleDriver");

      // 2. 연결 얻어오기
      String url = "jdbc:oracle:thin:@localhost:1521:xe";
      conn = DriverManager.getConnection(url, "skudb", "skudb");

      // 3. statement 생성
      stmt = conn.createStatement();

      // 4. SQL문 실행
      String sql =
          "   select a.no,"
              + "          a.title,"
              + "          a.rate,"
              + "          a.status,"
              + "          b.name"
              + "     from book a,"
              + "          author b"
              + "    where a.author_no = b.no"
              + " order by a.no asc";
      rs = stmt.executeQuery(sql);

      // 5. 결과 처리
      while (rs.next()) {
        Long no = rs.getLong(1);
        String title = rs.getString(2);
        Integer rate = rs.getInt(3);
        Integer status = rs.getInt(4);
        String authorName = rs.getString(5);

        BookVo vo = new BookVo();
        vo.setNo(no);
        vo.setTitle(title);
        vo.setRate(rate);
        vo.setStatus(status);
        vo.setAuthorName(authorName);

        list.add(vo);
      }
    } catch (ClassNotFoundException e) {
      System.out.println("드라이버 로딩 실패 :" + e);
    } catch (SQLException e) {
      System.out.println("error:" + e);
    } finally {
      try {
        // 6. 자원정리
        if (rs != null) {
          rs.close();
        }

        if (stmt != null) {
          stmt.close();
        }

        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        System.out.println("error:" + e);
      }
    }

    return list;
  }