customermessageborderdao.java

基于struts的网上商店源码

package com.mole.struts.dao;

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

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

import com.mole.struts.bean.CustomerInfoBean;
import com.mole.struts.bean.CustomerMessageBorderBean;

public class CustomerMessageBorderDAO {
	private Connection conn;
	private int pageSize;

	public CustomerMessageBorderDAO() {
		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();
		}
	}

	// 获取用户博客主题风格
	public String getCustomerStyle(String ID) throws Exception {
		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).trim();
			}
			return null;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public int getPageInfo(String loginID, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "SELECT COUNT(*) FROM [v_CustomerMessageBoard] where Owner='"
				+ loginID + "'";
		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<CustomerMessageBorderBean> queryMessage(String loginID,
			int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<CustomerMessageBorderBean> al = new ArrayList<CustomerMessageBorderBean>();
		String sql = "SELECT TOP "
				+ pageSize
				+ " [ID],[Owner],[Deliver],[DeliverName],[FaceImage],[FaceWidth],[FaceHeight],[Context],[DeliverTime] "
				+ "FROM [v_CustomerMessageBoard] WHERE [Owner]='" + loginID
				+ "' and ID NOT IN (SELECT TOP " + (currentPage - 1) * pageSize
				+ " ID FROM [v_CustomerMessageBoard] WHERE [Owner]='" + loginID
				+ "')";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			System.out.println(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerMessageBorderBean info = new CustomerMessageBorderBean();

				info.setID(rs.getString(1));
				info.setOwner(rs.getString(2));
				info.setDeliver(rs.getString(3));
				info.setDeliverName(rs.getString(4));
				info.setFaceImage(rs.getString(5));
				info.setFaceWidth(rs.getString(6));
				info.setFaceHeight(rs.getString(7));
				info.setContext(rs.getString(8));
				info.setDeliverTime(rs.getString(9));

				al.add(info);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}
}