merchantblogdao.java

基于struts的网上商店源码

package com.mole.struts.dao;

import java.sql.CallableStatement;
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.CustomerBlogArticleTypeBean;
import com.mole.struts.bean.CustomerBlogBean;

public class MerchantBlogDao {
	private Connection conn;

	public Connection getConn() {
		return conn;
	}

	public MerchantBlogDao() {// 初始化函数
		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();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public String getStyle(String mid) {
		String style = "default";
		ResultSet rs = null;
		String sql = "SELECT [BlogStyle] FROM [v_MerchantStore] WHERE [ID]="
				+ mid;
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				style = rs.getString(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return style;
	}

	public int getCount(String cid, String role, String where) {// 获取
		int count = 0;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM [v_merchantStoreBlog] a WHERE role="
				+ role + " and a.[writeid]=" + cid + where;
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public int getPageInfo(String userid, String role) {// 获取页面的大小
		int count = 0;
		if (conn == null)
			conn = getConn();
		// String sql =
		// "select count(*) from v_merchantStoreBlog where topicid=0 and writerid="+userid+" and role="+role;
		String sql = "select count(*) from v_merchantStoreBlog 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 int getPageInfo(String articleId) {// 获取页面的大小
		int count = 0;
		if (conn == null)
			conn = getConn();
		String sql = "select count(*) from v_merchantStoreBlog where topicid="
				+ articleId + " or id=" + 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<Object[]> getStoreInfo(String ID) throws Exception {// 通用函数
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select distinct a.[storeId],a.[storename] from [v_CustomerRecord] a where a.[customerId]="
				+ ID;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] o = new Object[2];
				o[0] = rs.getString(1);
				o[1] = rs.getString(2);
				al.add(o);
			}
			return al;
		} 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.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 ArrayList<Object[]> getRecord(String cid, String end,
			int currentPage, int pageSize) throws Exception {
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[Id],a.[StoreName],a.[NominalPrice],a.[totalPrice],a.[DealTime] FROM [v_CustomerRecord] a WHERE a.[CustomerID]="
				+ cid + end + " AND a.[ID] NOT IN (" + "SELECT TOP "
				+ (currentPage - 1) * pageSize
				+ " [ID] FROM [v_CustomerRecord] WHERE a.[CustomerID]=" + cid
				+ end + ")";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] o = new Object[5];
				o[0] = rs.getString(1);
				o[1] = rs.getString(2);
				o[2] = rs.getString(3);
				o[3] = rs.getString(4);
				o[4] = rs.getString(5);
				al.add(o);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public ArrayList getMerchantBlogTypes(String username) throws Exception // 获取该人博客的类型
	{

		ArrayList<Object[]> al = new ArrayList<Object[]>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select a.[id],a.[name] from merchantStoreBlogType a where a.[merchantStoreId]="
				+ username;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] o = new Object[2];
				o[0] = String.valueOf(rs.getInt(1));
				o[1] = rs.getString(2);
				al.add(o);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 根据商家的ID，获取商家的博客类型。
	public ArrayList<CustomerBlogArticleTypeBean> getMerchantBlogTypeById(
			String username) throws Exception // 获取该人博客的类型
	{

		ArrayList<CustomerBlogArticleTypeBean> al = new ArrayList<CustomerBlogArticleTypeBean>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select a.[id],a.[name],a.[merchantStoreId] from merchantStoreBlogType a where a.[merchantStoreId]="
				+ username;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerBlogArticleTypeBean o = new CustomerBlogArticleTypeBean();
				o.setId(String.valueOf(rs.getInt(1)));
				o.setName(String.valueOf(rs.getString(2)));
				o.setCustomerId(String.valueOf(rs.getInt(3)));
				al.add(o);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}

	}

	public boolean editBlogType(String id, String typename) throws Exception// 修改博客的类型
	{

		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "update table merchantStoreBlogType set typename='"
				+ typename + "' where id=" + id;
		try {
			ps = conn.prepareStatement(sql);
			ps.executeUpdate(sql);

			return true;
		} finally {
			if (ps != null)
				ps.close();
		}

	}

	public ArrayList getBlogsList(int currentPage, int pageSize, String vid,
			String role, String where) throws Exception// 获取日志的列表
	{

		String sql;
		ArrayList<CustomerBlogBean> al = new ArrayList<CustomerBlogBean>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String i = vid;
		try {
			/*
			 * sql="select top "+pageSize+" a.title,a.content,a.issuetime,a.replycount,a.merchantname,a.id from v_merchantstoreblog a where topicid=0 and writerid="
			 * +vid+
			 * " and role="+role+where+" and a.[id] not in("+"select top "+
			 * (currentPage-1)pageSize+
			 * " id from v_merchantStoreBlog where topicid=0 and a.[writerid]="
			 * +vid+" and a.[role]="+role+where+")";
			 */
			sql = "select top "
					+ pageSize
					+ " a.title,a.content,a.issuetime,a.replycount,a.merchantname,a.id from v_merchantstoreblog a where topicid=0 and writerid="
					+ vid
					+ " "
					+ where
					+ " and a.[id] not in("
					+ "select top "
					+ (currentPage - 1)
					* pageSize
					+ " id from v_merchantStoreBlog where topicid=0 and a.[writerid]="
					+ vid + " " + where + ")";

			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerBlogBean tempBean = new CustomerBlogBean();
				tempBean.setTitle(rs.getString(1));
				tempBean.setContent(rs.getString(2));
				tempBean.setIssueTime(String.valueOf(rs.getDate(3)));
				tempBean.setReplyCount(String.valueOf((rs.getInt(4))));
				tempBean.setWriterName(rs.getString(5));
				tempBean.setSonId(String.valueOf(rs.getInt(6)));
				al.add(tempBean);

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

	}

	public ArrayList<Object[]> executeStoredProcedure(String spName,
			Object... args) throws Exception {
		ResultSet rs = null;
		Object[] ret = null;
		CallableStatement stmt = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		try {
			conn.setAutoCommit(true);
			stmt = conn.prepareCall("{call " + spName + "}");
			for (int i = 1; i <= args.length; i++)
				stmt.setObject(i, args[i - 1]);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ret = new Object[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
					ret[i] = rs.getObject(i + 1);
				al.add(ret);
			}
			return al;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	// 获取博客的详细信息。
	public ArrayList getBlogDetail(String spName, String articleId,
			int currentPage, int pageSize) throws Exception// 获取日志详细信息
	{
		// 储存过程的参数】@storeID nchar(12),
		// @currentPage int,
		// @pageSize int

		String sql;
		ArrayList<CustomerBlogBean> al = new ArrayList<CustomerBlogBean>();
		CallableStatement stmt = null;
		ResultSet rs = null;
		try {
			if (conn == null)
				conn = getConn();
			// sql="select top "+pageSize+" a.title,a.content,a.issuetime,a.replycount,a.merchantname,a.id from v_merchantstoreblog a where id="+topicid+" or topicid="+topicid+" "+where+" and a.[id] not in("+"select top "+(currentPage-1)*pageSize+" id from v_merchantStoreBlog where  id="+topicid+" or topicid="+topicid+"  "+where+" order by issuetime)order by issuetime";
			conn.setAutoCommit(true);
			stmt = conn.prepareCall("{call " + spName + "(?,?,?)}");
			stmt.setObject(1, articleId);
			stmt.setObject(2, currentPage);
			stmt.setObject(3, pageSize);
			rs = stmt.executeQuery();
			while (rs.next()) {
				CustomerBlogBean tempBean = new CustomerBlogBean();
				tempBean.setSonId(String.valueOf(rs.getInt(1)));
				tempBean.setTitle(rs.getString(2));
				tempBean.setContent(rs.getString(3));
				tempBean.setIssueTime(String.valueOf(rs.getDate(4)));
				tempBean.setWriterId(String.valueOf(rs.getInt(5)));
				tempBean.setWriterRole(String.valueOf(rs.getInt(6)));
				tempBean.setWriterName(rs.getString(7));
				tempBean.setFaceImage(rs.getString(8));
				al.add(tempBean);

			}
			return al;
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	public boolean addBlogType(String userid, String typename) throws Exception// 添加博客的日志类型
	{

		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "insert into table merchantStoreBlogType (merchantStoreId,name)values('"
				+ userid + "','" + typename + "')";
		try {
			ps = conn.prepareStatement(sql);
			ps.executeUpdate(sql);

			return true;
		} finally {
			if (ps != null)
				ps.close();
		}

	}

	public boolean addAritcle(String content, String title, String username,
			String typeId, String role, String topicid) throws Exception// 添加博客文章
	{

		PreparedStatement ps = null;

		String sql = "insert into merchantStoreBlog(content,TopicId,writerid,title,role,typeid) values ('"
				+ content
				+ "','"
				+ topicid
				+ "','"
				+ username
				+ "','"
				+ title
				+ "','" + role + "','" + typeId + "')";
		try {
			if (conn == null)
				conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();

			return true;
		} finally {
			if (ps != null)
				ps.close();
		}

	}

	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 void executeUpdate(String[] sql) {
		Connection conn = getConn();
		Statement ps = null;
		try {
			conn.setAutoCommit(false);
			ps = conn.createStatement();
			for (int i = 0; i < sql.length; i++) {

				ps.executeUpdate(sql[i]);

			}
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}