<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true">

	<Resource name="jdbc/freeboardDB"  auth="Container" type="javax.sql.DataSource"
		maxActive="100" maxIdle="30" maxWait="10000" removeAbandoned="true"
		username="root" password="1024" driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost:3306/freeboard" />
</Context>







package board.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class MysqlcpPostDAO implements PostDAO {
	DataSource pool;

	public MysqlcpPostDAO() {
		try {
			InitialContext ctx = new InitialContext();

			pool = (DataSource) ctx.lookup("java:comp/env/jdbc/freeboardDB");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<PostVO> getPostList(int page) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<PostVO> postList = null;
		String sql = "SELECT id, title, hits, date FROM post ORDER BY id DESC LIMIT ?, 10";
		int num = page * 10 - 10;

		try {
			conn = pool.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			postList = new ArrayList<PostVO>();

			while (rs.next()) {
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String date = rs.getString("date");
				int hits = rs.getInt("hits");

				postList.add(new PostVO(id, title, date, hits));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {rs.close();}
				if (pstmt != null) {pstmt.close();}
				if (conn != null) {conn.close();}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return postList;
	}

	@Override
	public PostVO getPostById(PostVO postVO) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT body FROM post WHERE id=?";

		try {
			conn = pool.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, postVO.getNo());
			rs = pstmt.executeQuery();

			rs.next();

			postVO.setBody(rs.getString("body"));

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {rs.close();}
				if (pstmt != null) {pstmt.close();}
				if (conn != null) {conn.close();}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return postVO;
	}

	@Override
	public void insertPost(String title, String body) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO post (title, body) VALUES (?, ?)";

		try {
			conn = pool.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, title);
			pstmt.setString(2, body);
			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {pstmt.close();}
				if (conn != null) {conn.close();}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void updateHits(int id, PostVO postVO) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "UPDATE post SET hits=hits+1 WHERE id=?";
		String query = "SELECT hits FROM post WHERE id="+id;

		try {
			conn = pool.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();

			rs = pstmt.executeQuery(query);

			rs.next();

			postVO.setHits(rs.getInt("hits"));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {rs.close();}
				if (pstmt != null) {pstmt.close();}
				if (conn != null) {conn.close();}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public int countPost() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int total = 0;
		String sql = "SELECT COUNT(*) AS totalPost FROM post";

		try {
			conn = pool.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			rs.next();

			total = rs.getInt("totalPost");

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {rs.close();}
				if (pstmt != null) {pstmt.close();}
				if (conn != null) {conn.close();}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return total;
	}
}