messdb.java

基于JSP的家电连锁超市管理系统 数据库也在文件内

package electric.dbs;

import java.util.Collection;
import java.util.ArrayList;
import java.sql.*;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Iterator;
import javax.servlet.http.*;
import electric.dbs.*;
import electric.dbs.Mess;
import electric.electricUtils.*;

public class Messdb
{
	//定义final的String的sql搜索语句
	private static final String LOAD_MESS_BY_ID="SELECT * FROM [MESS] WHERE [Id]=?";
	//初始化Id
	private int Id=0;
	public Messdb()
	{
	}
	//负责查看某一条记录信息，实现方法是通过留言信息的Id值进行判断
	public Mess show(int Id)
	{
	  this.Id=Id;
	  Mess mess=new Mess();
	  Connection con=null;
	  PreparedStatement pstmt=null;
	  try
	  {
	  	con=DbConnectionManager.getConnection();
	  	pstmt=con.prepareStatement(LOAD_MESS_BY_ID,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
	  	pstmt.setInt(1,Id);
	  	ResultSet rs=pstmt.executeQuery();
	  	if(rs.next())
	  	{
	  	  mess.setMname(rs.getString(2));
	  	  mess.setMtext(rs.getString(3));
	  	  mess.setMnote(rs.getString(4));
	  	  mess.setCreatedate(rs.getString(5));	
	  	}
	  }
	  catch(SQLException sqle)
	  {
	  	sqle.printStackTrace();
	  }
	  finally
	  {
	  	try
	  	{
	  		pstmt.close();
	  		con.close();
	  	}
	  	catch(Exception e)
	  	{
	  		e.printStackTrace();
	  	}
	  }
	  return mess;
	}
	
	//selectsql方法负责显示分页后所能显示的页面
	public int selectsql(int st)
	{
		String countSql="SELECT COUNT(*) FROM MESS WHERE STATUS<>1";
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int size=0;
		int sizepa=0;
		try
		{
		   con=DbConnectionManager.getConnection();
		   pstmt=con.prepareStatement(countSql);
		   rs=pstmt.executeQuery();
		   while(rs.next())
		   {
		   	size=rs.getInt(1);
		   	sizepa=(int)size/st;
		   	if(size%st>0)
		   	{
		   		sizepa=sizepa+1;
		   	}
		   }
		   rs.close();
		   con.close();	
		}	
		catch(SQLException sqle)
		{
			System.out.println(sqle.getMessage());
		}
		return sizepa;
	}
	
	//获取分页后的结果集
	public Collection select(int step,int page)
	{
	   Collection coll=new ArrayList();
	   Connection con=null;
	   ResultSet rs=null;
	   PreparedStatement pstmt=null;
	   int tip=step*(page-1);
	   try
	   {
	   	 String sql="SELECT * FROM [MESS] WHERE STATUS<>1";
	   	 con=DbConnectionManager.getConnection();
	   	 pstmt=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
	   	 rs=pstmt.executeQuery();
	   	 //判断是否有页面存在
	   	 if(tip<=0)
	   	 {
	   	 	rs.beforeFirst();
	   	 }
	   	 else
	   	 {
	   	 	if(!rs.absolute(tip))
	   	 	{
	   	 		rs.beforeFirst();
	   	 	}
	   	 }
	   	 for(int i=1;rs.next()&&i<=step;i++)
	   	 {
	   	 	Mess mess=new Mess();
	   	 	mess.setId(rs.getInt(1));
	   	 	mess.setMname(rs.getString(2));
	   	 	mess.setMtext(rs.getString(3));
	   	 	mess.setMnote(rs.getString(4));
	   	 	mess.setCreatedate(rs.getString(5));
	   	 	mess.setStatus(rs.getInt(6));
	   	 	coll.add(mess);
	   	 }
	   	 rs.close();
	   	 con.close();
	   }
	   catch(SQLException sqle)
	   {
	   	 System.out.println(sqle.getMessage());
	   }	
	   finally
	   {
	   	return coll;
	   }
	}	
	
	public static void main(String args[])
	{
		Messdb messdb=new Messdb();
		Collection coll=new ArrayList();
		coll=messdb.select(5,1);
		Iterator ii=coll.iterator();
		
		while(ii.hasNext()) 
		{
			Mess mess=(Mess)ii.next();
			System.out.println(mess.getId());
			System.out.println(mess.getMname());
			System.out.println(mess.getMnote());
			System.out.println(mess.getMtext());
			System.out.println(mess.getCreatedate());
			System.out.println(mess.getStatus());
		}
		int count=messdb.selectsql(5);
		System.out.println("###   "+count);	
		
		Mess mess=messdb.show(1);
		System.out.println(mess.getId());
		System.out.println(mess.getMname());
		System.out.println(mess.getMnote());
		System.out.println(mess.getMtext());
		System.out.println(mess.getStatus());	
	}
}