customeraddbolgdao.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;
import com.mole.struts.bean.CustomerInfoBean;

public class CustomerAddBolgDAO {
	private Connection conn;

	public CustomerAddBolgDAO() {
	}

	public Connection getConn() {
		if (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 getCustomerStyle(String ID) throws Exception {
		Connection conn = getConn();
		ResultSet rs = null;
		PreparedStatement ps = null;
		CustomerInfoBean bean = new CustomerInfoBean();
		String sql = "SELECT [BlogStyle] FROM [Customer] WHERE [ID]=?";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getString(1);
			}
			return null;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	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));
				tempSon.setTitle(rs.getString("title"));
				list.add(tempSon);
			}
			return list;
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {

			try {
				if (ps != null)
					ps.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 userid) {
		int count = 0;
		if (conn == null)
			conn = getConn();
		String sql = "select count(*) from blogloglist where topicid=0 and writerid="
				+ userid;
		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<CustomerBlogBean> queryBlog(String articleId,
			int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String desp = null;
		ArrayList<CustomerBlogBean> al = new ArrayList<CustomerBlogBean>();
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[title],a.[issuetime],(SELECT COUNT(*) FROM blogloglist where topicid=a.[ID]) as cont ,a.[ID] "
				+ "FROM blogloglist a  WHERE a.topicid=0 AND writerid="
				+ articleId + " AND a.[ID] NOT IN (" + "SELECT TOP "
				+ (currentPage - 1) * pageSize
				+ " [ID] FROM blogloglist  WHERE topicid=0 and writerid="
				+ articleId
				+ " order by issuetime desc) order by issuetime desc";
		// String
		// sql="SELECT TOP "+pageSize+" a.[title],a.[issuetime],a.[Replycount],a.[ID] FROM BLOGLOGLIST a WHERE topicid=0 and WRITERID='"+writername+"'";
		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));
				al.add(tempBlog);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public ArrayList<CustomerBlogBean> queryBlogByType(String writername,
			String id, int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String desp = null;
		ArrayList<CustomerBlogBean> al = new ArrayList<CustomerBlogBean>();
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[title],a.[issuetime],(SELECT COUNT(*) FROM blogloglist where topicid=a.[ID]) as [Replycount],a.[ID] "
				+ "FROM blogloglist a  WHERE a.topicid=0 AND writerid="
				+ writername + " AND typeid=" + id + " and a.[ID] NOT IN ("
				+ "SELECT TOP " + (currentPage - 1) * pageSize
				+ " [ID] FROM blogloglist  WHERE topicid=0 and writerid="
				+ writername + " and typeid=" + id + ")";
		// String
		// sql="SELECT TOP "+pageSize+" a.[title],a.[issuetime],a.[Replycount],a.[ID] FROM BLOGLOGLIST a WHERE topicid=0 and WRITERID='"+writername+"'";
		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));
				al.add(tempBlog);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}