blogviewdao.java

基于struts的网上商店源码

package com.mole.struts.dao;

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

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

import com.mole.struts.bean.CustomerBlogBean;

public class BlogViewDAO

{
	private int pageSize;

	public BlogViewDAO() {
	}

	public Connection getConn() {
		Connection conn = null;
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/crmdata");
			conn = ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public String getStyle(String cid) {
		String style = "";
		String sql = "SELECT [BlogStyle] FROM [Customer] WHERE [ID]=" + cid;
		Connection conn = getConn();
		Statement ps = null;
		ResultSet rs = null;
		try {
			conn.setAutoCommit(true);
			ps = conn.createStatement();
			rs = ps.executeQuery(sql);
			if (rs.next())
				style = rs.getString(1).trim();
			ps.close();
			conn.close();
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		return style;
	}

	public ArrayList executeQuery(String sql) {
		Connection conn = getConn();
		Statement ps = null;
		ResultSet rs = null;
		ArrayList<CustomerBlogBean> list = new ArrayList();
		try {
			conn.setAutoCommit(true);
			ps = conn.createStatement();
			rs = ps.executeQuery(sql);
			while (rs.next())// the arguments are
								// SonId,Content,IssueTime,TopicId,WriterName,ContentSize
			{
				CustomerBlogBean tempSon = new CustomerBlogBean();
				tempSon.setSonId(String.valueOf(rs.getInt(1)));
				tempSon.setContent(rs.getString(2));
				tempSon.setIssueTime(rs.getDate(3).toString());
				tempSon.setTopicId(rs.getString(4));
				tempSon.setWriterName(rs.getString(5));
				tempSon.setContentSize(rs.getString(6));
				list.add(tempSon);
			}
			return list;
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {

			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return null;

	}

	public void executeUpdate(String sql) {
		Connection conn = getConn();
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int getPageInfo(String articleId, int pageSize) {
		int count = 0;
		Connection conn = getConn();
		this.pageSize = pageSize;
		String sql = "select count(*) from v_customerblog where id="
				+ articleId + " or topicid=" + articleId;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public ArrayList queryBlog(String ArticleId, int currentPage)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String desp = null;
		ArrayList hash = new ArrayList();
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[title],a.[issuetime],(SELECT COUNT(*) FROM blogloglist where topicid=a.[ID]) as [ReplyCount],a.[ID],a.[nickname],a.[faceimage],a.[topicid],a.[content] "
				+ "FROM v_customerblog a  WHERE a.id=" + ArticleId
				+ " or a.topicid=" + ArticleId + " AND a.[ID] NOT IN ("
				+ "SELECT TOP " + (currentPage - 1) * pageSize
				+ " a.[ID] FROM v_customerblog  WHERE id=" + ArticleId
				+ " or topicid=" + ArticleId + ") order by issuetime asc";
		Connection conn = getConn();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerBlogBean tempBlog = new CustomerBlogBean();
				tempBlog.setTitle(rs.getString(1));
				tempBlog.setIssueTime(String.valueOf(rs.getDate(2)));
				int i = rs.getInt(3);
				tempBlog.setReplyCount(String.valueOf(i));
				tempBlog.setSonId(rs.getString(4));
				tempBlog.setNickName(rs.getString(5));
				tempBlog.setFaceImage(rs.getString(6));

				tempBlog.setTopicId(rs.getString(7));
				tempBlog.setContent(rs.getString(8));
				hash.add(tempBlog);
			}
			return hash;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}