userinfodao.java

一个仿造淘宝的jsp网站。功能比较完善

package com.jc.taobao.gjj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jc.taobao.gjj.db.DBManager;
import com.jc.taobao.gjj.entity.UserInfo;

public class UserInfoDAO{
	DBManager dm = new DBManager();

	public int delete(Integer id) {
		String sql = "delete from userinfo where userid=" + id;
		return dm.updb(sql);
	}
	
	public int updateuser(Integer userid,Integer userscore)
	{
		String sql="update userinfo set userscore="+userscore+" where userid="+userid;
		return dm.updb(sql);
	}

	public int merge(UserInfo user) {
		String sql = "update userinfo set photoid=" + user.getPhotoid()
				+ ",usercardid='" + user.getUsercardid() + "',userpwd='"
				+ user.getUserpwd() + "',username='" + user.getUsername()
				+ "',userquestion='" + user.getUserquestion()
				+ "',useranswer='" + user.getUseranswer() + "',userunid='"
				+ user.getUserunid() + "',useremail='" + user.getUseremail()
				+ "',useraddress='" + user.getUseraddress() + "',usertel='"
				+ user.getUsertel() + "',userscore=" + user.getUserscore()
				+ ",userule=" + user.getUserule() + " where userid="
				+ user.getUserid();
		return dm.updb(sql);
	}
	

	public int save(UserInfo user) {     
		String sql = "insert into userinfo values("
				+ user.getPhotoid() + ",'" + user.getUsercardid() + "','"
				+ user.getUserpwd() + "','" + user.getUsername() + "','"
				+ user.getUserquestion() + "','" + user.getUseranswer() + "','"
				+ user.getUserunid() + "','" + user.getUseremail() + "','"
				+ user.getUseraddress() + "','" + user.getUsertel() + "',"
				+ user.getUserscore() + "," + user.getUserule() + ")";
		return dm.updb(sql);
	}

	private List<UserInfo> query(String sql) {// 查询方法
		List<UserInfo> list = new ArrayList<UserInfo>();
		DBManager db = new DBManager();
		ResultSet rs = db.getRs(sql);
		try {
			while (rs.next()) {
				UserInfo user = new UserInfo();
				user.setUserid(rs.getInt("userid"));
				user.setPhotoid(rs.getInt("photoid"));
				user.setUsercardid(rs.getString("usercardid"));
				user.setUsername(rs.getString("username"));
				user.setUserpwd(rs.getString("userpwd"));
				user.setUserunid(rs.getString("userunid"));
				user.setUseremail(rs.getString("useremail"));
				user.setUseraddress(rs.getString("useraddress"));
				user.setUsertel(rs.getString("usertel"));
				user.setUserscore(rs.getInt("userscore"));
				user.setUserule(rs.getInt("userule"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/*public int updateuserstate(Integer userid,Integer stateid)
	{
		String sql="update orderinfo set userscore="+stateid+" where userid="+userid;
		return dm.updb(sql);
	}*/

	public int login(UserInfo user) {// 用户登录，根据查处权限若为-1则不是用户，为0则为管理员，为1则为普通用户
		ResultSet rs = null;
		int userrule = -1;
		String sql = "select userule from userinfo where username='"
				+ user.getUsername() + "'";

		try {
			rs = dm.getRs(sql);
			while (rs.next()) {
				userrule = rs.getInt("userule");
			}
			return userrule;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
	}
	public int SmallLogin(String username,String pwd)
	{
		ResultSet rs=null;
		String sql="select * from userinfo where username='"+username+"' and userpwd='"+pwd+"'";
		try {
			rs=dm.getRs(sql);
			while(rs.next())
			{
				return 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public List<UserInfo> querybyuserunid(String userunid)//根据用户昵称查 
	{
		String sql="select * from userinfo where userunid='"+userunid+"'";
		return query(sql);
	}
	public List<UserInfo> queryAll()// 查询所有用户信息
	{
		String sql = "select * from userinfo";
		return query(sql);
	}

	public List<UserInfo> querybyuserscore(Integer score) {// 根据积分查用户信息
		String sql = "select * from userinfo where userscore ="
				+ score;
		return query(sql);
	}

	public List<UserInfo> querybyuserid(Integer userid) {//根据用户id查用户信息
		String sql = "select * from userinfo where userid="
				+ userid;
		return query(sql);
	}
	
	public List<UserInfo> querybyusername(UserInfo user)//根据用户名察看个人信息
	{
		String sql="select* from userinfo where username='"+user.getUsername()+"'";
		return query(sql);
	}
	public int getUserCardID(String username)
	{
		int count=0;
		String sql="select count(username)as isName from userinfo where username='"+username+"'";
		try {
			ResultSet rs=dm.getRs(sql);
			while(rs.next())
			{
				count=rs.getInt("isName");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
		
	}

	public List<UserInfo> querybyuserule(int userule)// 根据权限查信息
	{
		String sql = "select * from userinfo where userule="
				+ userule;
		return query(sql);
	}
	
	public int getCount(){//判断用户是否存在
		String sql="select count(usercardid) from userinfo";
		try {
			ResultSet rs=dm.getRs(sql);
			if(rs.next())
			{
				return rs.getInt(1);
			}
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
	}

	public String getuserpwd(UserInfo user)// 找回密码
	{
		ResultSet rs = null;
		try {
			String sql = "select userpwd from userinfo where username='"
					+ user.getUsername() + "' and question='"
					+ user.getUserquestion() + "' and answer='"
					+ user.getUseranswer() + "'";
			rs = dm.getRs(sql);
			if (rs.next()) {
				return rs.getString("userpwd");
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}
	
	public int queryUserInfoCount()
	{
		int userinfocount=0;
		String sql="select count(*) as userinfocount from userinfo";
		try {
			ResultSet rs=dm.getRs(sql);
			while(rs.next())
			{
				userinfocount=rs.getInt("userinfocount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userinfocount;
	}
}