// 신간 리스트
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;
}
// 인기 도서
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;
}
// 책 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;
}
// 책 시리즈 리스트
public List<Book> SelectDataList(String name, String writer) {
// 랭킹을 이용하여 모든 데이터를 조회한다.
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = " select bookcode, name, volume, writer ";
sql += " from books ";
sql += " where name = ? and writer = ? ";
sql += " order by volume asc ";
List<Book> lists = new ArrayList<Book>();
try {
// if( this.conn == null ){
this.conn = this.getConn();
// }
pstmt = this.conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, writer);
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"));
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;
}