Example #1
0
  // 신간 리스트
  public List<Book> SelectNewDataList(int beginRow, int endRow, String month) {
    // 랭킹을 이용하여 모든 데이터를 조회한다.
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    String sql =
        "select bookcode, name, volume, writer, publisher, pubdate, genre, image, bookstat, bookstory, ranking ";
    sql += " from ";
    sql += " ( ";
    sql +=
        " select bookcode, name, volume, writer, publisher, pubdate, genre, image, bookstat, bookstory, rank() over( order by name asc, volume desc, bookcode desc) as ranking ";
    sql += " from books ";
    sql += " where bookstat != '대출 불가' and pubdate between " + month;
    sql += " ) ";
    sql += " where ranking between ? and ? ";

    List<Book> lists = new ArrayList<Book>();
    try {
      // if( this.conn == null ){
      this.conn = this.getConn();
      // }
      pstmt = this.conn.prepareStatement(sql);
      pstmt.setInt(1, beginRow);
      pstmt.setInt(2, endRow);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        Book bean = new Book();
        bean.setBookcode(rs.getInt("bookcode"));
        bean.setName(rs.getString("name"));
        bean.setVolume(rs.getInt("volume"));
        bean.setWriter(rs.getString("writer"));
        bean.setPublisher(rs.getString("publisher"));
        bean.setPubdate(String.valueOf(rs.getDate("pubdate")));
        bean.setGenre(rs.getString("genre"));
        bean.setImage(rs.getString("image"));
        bean.setBookstat(rs.getString("bookstat"));
        bean.setBookstory(rs.getString("bookstory"));

        lists.add(bean);
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (pstmt != null) {
          pstmt.close();
        }
        this.closeConn();
      } catch (Exception e2) {
        e2.printStackTrace();
      }
    }
    return lists;
  }
Example #2
0
  // 인기 도서
  public List<Book> SelectHitDataList() {
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    String sql =
        " select bookcode, name, volume, image, writer, publisher, pubdate, bookstat, cnt, ranking from ( ";
    sql +=
        " select bookcode, name, volume, image, writer, publisher, pubdate, bookstat, cnt, rank() over( order by cnt desc ) as ranking from ( ";
    sql +=
        " select bk.bookcode, bk.name, bk.volume, bk.image, bk.writer, bk.publisher, bk.pubdate, bk.bookstat, count(rc.bcode) as cnt ";
    sql += " from books bk inner join records rc ";
    sql += " on bk.bookcode = rc.bcode ";
    sql +=
        " group by bk.bookcode, bk.name, bk.volume, bk.image, bk.writer, bk.publisher, bk.pubdate, bk.bookstat)) ";
    sql += " where ranking <= 10 ";

    List<Book> lists = new ArrayList<Book>();
    try {
      if (this.conn == null) {
        this.conn = this.getConn();
      }
      pstmt = this.conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Book bean = new Book();
        bean.setBookcode(rs.getInt("bookcode"));
        bean.setName(rs.getString("name"));
        bean.setVolume(rs.getInt("volume"));
        bean.setWriter(rs.getString("writer"));
        bean.setPublisher(rs.getString("publisher"));
        bean.setPubdate(String.valueOf(rs.getDate("pubdate")));
        bean.setImage(rs.getString("image"));
        bean.setBookstat(rs.getString("bookstat"));

        lists.add(bean);
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (pstmt != null) {
          pstmt.close();
        }
        this.closeConn();
      } catch (Exception e2) {
        e2.printStackTrace();
      }
    }
    return lists;
  }
Example #3
0
  // 책 1건에 대한 상세 정보 보기
  public Book SelectDataByPk(int bookcode) {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "select * ";
    sql += " from books ";
    sql += " where bookcode = ? ";
    Book bean = null;
    try {
      if (this.conn == null) {
        this.conn = this.getConn();
      }
      pstmt = this.conn.prepareStatement(sql);
      // placehodelr 치환은 반드시 execute 메소드 실행 바로 직전에 하세요.
      pstmt.setInt(1, bookcode);
      rs = pstmt.executeQuery();

      if (rs.next()) {
        bean = new Book();
        bean.setBookcode(rs.getInt("bookcode"));
        bean.setName(rs.getString("name"));
        bean.setVolume(rs.getInt("volume"));
        bean.setWriter(rs.getString("writer"));
        bean.setPublisher(rs.getString("publisher"));
        bean.setPubdate(String.valueOf(rs.getDate("pubdate")));
        bean.setGenre(rs.getString("genre"));
        bean.setImage(rs.getString("image"));
        bean.setBookstat(rs.getString("bookstat"));
        bean.setBookstory(rs.getString("bookstory"));
      }

    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (pstmt != null) {
          pstmt.close();
        }
        this.closeConn();
      } catch (Exception e2) {
        e2.printStackTrace();
      }
    }
    return bean;
  }