bookhandle.java

图书商城系统

package com.tang.foruse;

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



public class BookHandle {
	
	static GetConn gc = null;

	PreparedStatement pstm = null;
	
	Connection conn = null;
	
	ResultSet rs = null;
	
	static 
	{
		gc = new GetConn();
	}
	
	
	public BookBean getBook(int id)
	{
		BookBean bean = new BookBean();
		try {
			conn = gc.GetConnectioon();
			pstm = conn.prepareStatement("select * from books where id = ?");
			pstm.setInt(1, id);
			rs = pstm.executeQuery();
			if(rs.next())
			{
				bean.setId(rs.getInt(1));
				bean.setName(rs.getString(2));
				bean.setClas(rs.getString(3));
				bean.setPrice(rs.getDouble(4));
				bean.setTime(rs.getString(5));
				bean.setPurchasetimes(rs.getInt(6));
				bean.setComment(rs.getString(7));
				bean.setImage(rs.getString(8));
				bean.setAuthor(rs.getString(9));
				bean.setPublishhouse(rs.getString(10));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		return bean;
	}
	
	public ArrayList getHotBook(int i)
	{
		ArrayList al = new ArrayList();
		try {
			conn = gc.GetConnectioon();
			pstm = conn.prepareStatement("select * from books order by purchasetimes desc limit 0,"+i);
			rs = pstm.executeQuery();
			while(rs.next())
			{
				BookBean bean = new BookBean();
				bean.setId(rs.getInt(1));
				bean.setName(rs.getString(2));
				bean.setClas(rs.getString(3));
				bean.setPrice(rs.getDouble(4));
				bean.setTime(rs.getString(5));
				bean.setPurchasetimes(rs.getInt(6));
				bean.setComment(rs.getString(7));
				bean.setImage(rs.getString(8));
				bean.setAuthor(rs.getString(9));
				bean.setPublishhouse(rs.getString(10));
				al.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		return al;
	}
	
	/**
	 *�÷���ͨ���鼮���������鼮��ҳ�ĵ�ǰҳ�����鼮��arraylist
	 * @param type ָʾ�鼮�����࣬���������1����
	 * @param pageNow ʹ�ô˷���ʱ�������ڷ�ҳ��ҳ��
	 * @return
	 */
	public ArrayList getBookByType(int type,int pageNow)
	{
		ArrayList al = new ArrayList();
		try {
			conn = gc.GetConnectioon();
			String typeString = this.getType(type);
			int first = (pageNow-1)*6;
			int last = first+6;
			pstm = conn.prepareStatement("select * from books where class='"+typeString+"' limit "+first+","+last);
			rs = pstm.executeQuery();
			while(rs.next())
			{
				BookBean bean = new BookBean();
				bean.setId(rs.getInt(1));
				bean.setName(rs.getString(2));
				bean.setClas(rs.getString(3));
				bean.setPrice(rs.getDouble(4));
				bean.setTime(rs.getString(5));
				bean.setPurchasetimes(rs.getInt(6));
				bean.setComment(rs.getString(7));
				bean.setImage(rs.getString(8));
				bean.setAuthor(rs.getString(9));
				bean.setPublishhouse(rs.getString(10));
				al.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		return al;
	}
	
	public StringBuffer getBookByTypeString(int type,int pageNow)
	{
		StringBuffer hotbook = new StringBuffer();
		ArrayList alist = this.getBookByType(type, pageNow);
		//�Ѹ�ҳ���鼮����ȡ�����֮��ļ���
		int total = alist.size();
		for(int i = 0;i<total;i++)
		{
			BookBean bean = (BookBean)alist.get(i);
			int id = bean.getId();
			String name = bean.getName();
			String image = bean.getImage();
			if(i==0)
			{
				hotbook.append("<tr><td colspan=3 align=center><font size=2 color=gray>�����l推荐</font></td></tr>");
			}
			if(i==0||i==3)
			{
				hotbook.append("<tr align=center>");
			}
			hotbook.append("<td><table><tr><td> <a href=bookintroduce.do?id="+id+"><img src = "+image+"></a></td></tr>");
			hotbook.append("<tr><td align=center><a href=bookintroduce.do?id="+id+"><font size=1 color=\"gray\">"+name+"</font></a></td></tr></table></td>");
			if(i==2||i==5)
			{
				hotbook.append("</tr>");
			}
			if(i!=2&&i!=5&&i==total-1)
			{
				hotbook.append("</tr>");
			}
		}
		return hotbook ;
	}
	
	/**
	 * 
	 * @param i
	 * @return Ĭ�����Ϊ�������
	 */
	public String getType(int i)
	{
		String str = "�����";
		//iΪ1ʱ ʲôҲ�������Ĭ��״̬
		if(i==1)
			;
		if(i==2)
		{
			str = "��ѧ";
		}
		if(i==3)
		{
			str = "����";
		}
		//��ʱֻ������ϼ������ͣ������ݿ����и��¿����ڸ÷�������Ӱ���Ӧ����
		return str;
	}
	public void close()
	{
		try {
			if(rs!=null)
			{
				rs.close();
				rs = null;
			}
				
			if(pstm!=null)
			{
				pstm.close();
				pstm = null;
			}
			if(conn!=null&&!conn.isClosed())
			{
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public StringBuffer getHotBookByString()
	{
		StringBuffer hotbook = new StringBuffer();
		ArrayList alist = this.getHotBook(10);
		for(int i = 0;i<alist.size();i++)
		{
			BookBean bean = (BookBean)alist.get(i);
			int id = bean.getId();
			String name = bean.getName();
			if(i==0)
			{
				hotbook.append("<tr><td colspan=2 align=\"center\"><font size=2 color=gray>热门��������</font></td></tr>");
			}
			hotbook.append("<tr><td> <img src = img/d1.gif></td>");
			hotbook.append("<td align=center><a href=bookintroduce.do?id="+id+"><font size=2 color=\"gray\">"+name+"</font></a></td></tr>");
		}
		return hotbook ;
	}
	
	public StringBuffer getBookByString()
	{
		StringBuffer hotbook = new StringBuffer();
		ArrayList alist = this.getHotBook(6);
		for(int i = 0;i<alist.size();i++)
		{
			BookBean bean = (BookBean)alist.get(i);
			int id = bean.getId();
			String name = bean.getName();
			String image = bean.getImage();
			if(i==0)
			{
				hotbook.append("<tr><td colspan=3 align=center><font size=2 color=gray>�����Ƽ�</font></td></tr>");
			}
			if(i==0||i==3)
			{
				hotbook.append("<tr>");
			}
			hotbook.append("<td><table><tr><td> <a href=bookintroduce.do?id="+id+"><img src = "+image+"></a></td></tr>");
			hotbook.append("<tr><td align=center><a href=bookintroduce.do?id="+id+"><font size=1 color=\"gray\">"+name+"</font></a></td></tr></table></td>");
			if(i==2||i==5)
			{
				hotbook.append("</tr>");
			}
		}
		return hotbook ;
	}
	
	public boolean addPurchaseTimes(int id)
	{
		boolean b = false;
		Connection conn = null;
		PreparedStatement pstm = null;
		GetConn gc = new GetConn();
		conn = gc.GetConnectioon();
		try {
			pstm = conn.prepareStatement("update books set purchasetimes=purchasetimes+1 where id="+id);
			int i = pstm.executeUpdate();
			if(i!=0)
			{
				b= true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			this.close();
		}
		return b;
	}
	
	/**
	 * �������ڰ��û�ѡ����鼮���뵽collection����
	 * @param uid
	 * @param bid
	 * @return
	 */
	public boolean addToCollection(int uid,int bid)
	{
		boolean b = false;
		Connection conn = null;
		PreparedStatement pstm = null;
		GetConn gc = new GetConn();
		conn = gc.GetConnectioon();
		try {
			pstm = conn.prepareStatement("insert into collection values("+uid+","+bid+")");
			int i = pstm.executeUpdate();
			if(i!=0)
			{
				b = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//���û��Ѿ��ղ��˸���ʱ����׽���쳣���������κζ���
		}finally{
			this.close();
		}
		return b;
	}
	
	public ArrayList getCollectionIds(int uid)
	{
		ArrayList al = new ArrayList();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		GetConn gc = new GetConn();
		conn = gc.GetConnectioon();
		try {
			pstm = conn.prepareStatement("select bid from collection where uid="+uid);
			rs = pstm.executeQuery();
			while(rs.next())
			{
				al.add(new Integer(rs.getInt(1)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		return al;
	}
	
	/**
	 * �÷�����һ��book��������ArrayList���
	 * װ��bookbean��ArrayList
	 * @param al
	 * @return
	 */
	
	public ArrayList getClctBooks(int uid)
	{
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		GetConn gc = new GetConn();
		conn = gc.GetConnectioon();
		try {
			pstm = conn.prepareStatement("select bid from collection where uid="+uid);
			rs = pstm.executeQuery();
			while(rs.next())
			{
				int i = rs.getInt(1);
				BookBean bean = new BookBean ();
				bean = this.getBook(i);
				list.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		return list;
	}
	
	public int getTotalClctNum(int uid)
	{
		int num = 0;
		num = this.getClctBooks(uid).size();
		return num;
	}
	
	public int getTotalByType(int type)
	{
		int num = 0;
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		GetConn gc = new GetConn();
		conn = gc.GetConnectioon();
		String typeString = this.getType(type);
		try {
			pstm = conn.prepareStatement("select count(*) from books where class='"+typeString+"'");
			rs = pstm.executeQuery();
			if(rs.next())
			{
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.close();
		}
		return num;
	}
	public StringBuffer getCollections(int uid,int pageNow)
	{
		StringBuffer clctbook = new StringBuffer();
		ArrayList alist = this.getClctBooks(uid);
		if (alist != null && (alist.size() != 0)) {
			for (int i = (pageNow-1)*2 ; i < (pageNow-1)*2+2 && i < alist.size(); i++) {
				BookBean bean = (BookBean) alist.get(i);
				int id = bean.getId();
				String name = bean.getName();
				String image = bean.getImage();
				clctbook
						.append("<tr><td rowspan=3><a href=bookintroduce.do?id="
								+ id
								+ "><img src=\""
								+ image
								+ "\"</td><td align=\"left\"><font size=2 color=gray>���ߣ�"
								+ bean.getAuthor() + "</font></td>");
				clctbook
						.append("<tr><td align=\"left\"><font size=2 color=gray>����"
								+ name + "</font></td></tr>");
				clctbook
						.append("<tr><td align=\"left\"><font size=2 color=gray>����磺"
								+ bean.getPublishhouse() + "</font></td></tr>");
				clctbook
						.append("<tr><td colspan=2><hr width=450 size=1 color=\"gray\"></hr></td></tr>");
			}
		}
		return clctbook ;
	}
	

	
	
	
}