[JSP 게시판] BbsDAO.java

package com.shdev.bbs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

public class BbsDAO {

Connection conn;

//PreparedStatement pstmt;

ResultSet rs;

public BbsDAO() {

String dbURL = "jdbc:mysql://localhost:3306/bbs";

String dbID = "root";

String dbPassword = "root";

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(dbURL, dbID, dbPassword);

}catch(Exception e) {

}

}

public String getDate() {

String sql = "SELECT NOW()";

try {

PreparedStatement pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

if(rs.next()) {

return rs.getString(1);

}

}catch(Exception e) {

e.printStackTrace();

}

return ""; // database err

}

public int getNext() {

String sql = "SELECT bbsID FROM bbs ORDER BY bbsID DESC";

try {

PreparedStatement pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

if(rs.next()) {

return rs.getInt(1) + 1;

}

return 1;

}catch(Exception e) {

e.printStackTrace();

}

return -1; // database err

}

public int write(Bbs bbs) {

String sql = "INSERT INTO bbs VALUES(?,?,?,?,?,?)";

try {

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, getNext());

pstmt.setString(2, bbs.getBbsTitle());

pstmt.setString(3, bbs.getUserID());

pstmt.setString(4, getDate());

pstmt.setString(5, bbs.getBbsContent());

pstmt.setInt(6, 1);

return pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}

return -2; // database err

}

public ArrayList<Bbs> getList(int pageNumber) {

ArrayList<Bbs> list = new ArrayList<Bbs>();

String sql = "SELECT * FROM bbs WHERE bbsID < ? AND bbsAvailable = 1 ORDER BY bbsID DESC LIMIT 10";

PreparedStatement pstmt; 

try {

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, getNext() - (pageNumber -1) * 10);

rs = pstmt.executeQuery();

while(rs.next()) {

Bbs bbs = new Bbs();

bbs.setBbsID(rs.getInt(1));

bbs.setBbsTitle(rs.getString(2));

bbs.setUserID(rs.getString(3));

bbs.setBbsDate(rs.getString(4));

bbs.setBbsContent(rs.getString(5));

bbs.setBbsAvailable(rs.getInt(6));

list.add(bbs);

}

}catch(Exception e){

e.printStackTrace();

}

return list;

}

public boolean nextPage(int pageNumber) {

String sql = "SELECT * FROM bbs WHERE bbsID< ? AND bbsAvailable = 1 ORDER BY bbsID DESC LIMIT 10";

PreparedStatement pstmt; 

try {

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, getNext() - (pageNumber -1) * 10);

rs = pstmt.executeQuery();

if(rs.next()) {

return true;

}

}catch(Exception e){

e.printStackTrace();

}

return false;

}

public Bbs getBbs(int bbsID) {

String sql = "SELECT * FROM bbs WHERE bbsID = ?";

Bbs bbs = new Bbs();

try {

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, bbsID);

rs = pstmt.executeQuery();

if(rs.next()) {

bbs.setBbsID(rs.getInt(1));

bbs.setBbsTitle(rs.getString(2));

bbs.setUserID(rs.getString(3));

bbs.setBbsDate(rs.getString(4));

bbs.setBbsContent(rs.getString(5));

return bbs;

}

}catch(Exception e) {

e.printStackTrace();

}

return null;

}

public int update(int bbsID, String bbsTitle, String bbsContent) {

String sql = "UPDATE bbs SET bbsTitle = ?, bbsContent = ? WHERE bbsID = ?";

try {

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setString(1, bbsTitle);

pstmt.setString(2, bbsContent);

pstmt.setInt(3, bbsID);

return pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}

return -2; // database err

}

public int delete(int bbsID) {

String sql = "UPDATE bbs SET bbsAvailable = 0 WHERE bbsID = ?";

try {

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, bbsID);

return pstmt.executeUpdate();

}catch(Exception e) {

e.printStackTrace();

}

return -2; // database err

}

}