usersdao.java

来自「java带进度条上传尽量不要让站长把时间都花费在为您修正说明上」· Java 代码 · 共 858 行 · 第 1/2 页

package com.jmwl.dao;

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

import com.jmwl.common.BlogException;
import com.jmwl.dto.UsersInfoDTO;
import com.jmwl.dto.UsersLoginDTO;
import com.jmwl.vo.UsersInfoVO;
import com.jmwl.vo.UsersLoginVO;
import com.sun.org.apache.bcel.internal.generic.LUSHR;

public class UsersDAO extends BasicDAO {

	/**
	 * 用户登录，查询userslogin表all信息
	 * @param login_name
	 * @param user_password
	 * @return
	 */
	public List usersLogin(String login_name, String user_password) throws BlogException {
		Connection connDB = this.getConn();
		String sql = "select * from userslogin where loginName=? and userPassword=?";
		List list = new ArrayList();
		try {
			PreparedStatement ps = connDB.prepareStatement(sql);
			ps.setString(1, login_name);
			ps.setString(2, user_password);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				UsersLoginVO userloginvo = new UsersLoginVO();
				userloginvo.setU_id(rs.getInt("uId"));
				userloginvo.setLogin_name(rs.getString("loginName"));
				userloginvo.setNick_name(rs.getString("nickName"));
				userloginvo.setUser_password(rs.getString("userPassword"));
				userloginvo.setPower(rs.getInt("power"));
				userloginvo.setState(rs.getInt("state"));
				userloginvo.setFace(rs.getString("face"));
				userloginvo.setLastlogin_time(rs.getString("lastLoginTime"));
				userloginvo.setRegister_time(rs.getString("registerTime"));
				userloginvo.setCounter(rs.getInt("counter"));
				userloginvo.setBlog_name(rs.getString("blogName"));
				list.add(userloginvo);
			}
		} catch (SQLException e) {
			throw new BlogException("登录失败！登录名或密码错误");
		}
		return list;
	}


	
	/**
	 * 查询用户的id 和 loginName，分页
	 * @param curpage
	 * @param pagelog
	 * @return
	 * @throws BlogException
	 */
	public List selectIdName(int curpage,int pagelog) throws BlogException{
		Connection connDB = this.getConn();
		int first=(curpage-1)*pagelog;
		String sql="select uId,loginName from userslogin limit ?,?";	
		List list=new ArrayList();
		try {
			PreparedStatement ps = connDB.prepareStatement(sql);
			ps.setInt(1, first);
			ps.setInt(2, pagelog);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				UsersLoginVO usersloginvo=new UsersLoginVO();
				usersloginvo.setU_id(rs.getInt("uId"));
				usersloginvo.setLogin_name(rs.getString("loginName"));
				list.add(usersloginvo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BlogException("分页出错");
		}
		return list;
	}
	
	/**
	 * 通过id查询userlogin表all信息
	 * @param id
	 * @return
	 */
	public List UsersLoginById(int id) throws BlogException {
		Connection connDB = this.getConn();
		String sql = "select * from userslogin where id=?";
		List list = new ArrayList();
		try {
			PreparedStatement ps = connDB.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				UsersLoginVO userloginvo = new UsersLoginVO();
				userloginvo.setU_id(rs.getInt("uId"));
				userloginvo.setLogin_name(rs.getString("loginName"));
				userloginvo.setNick_name(rs.getString("nickName"));
				userloginvo.setUser_password(rs.getString("userPassword"));
				userloginvo.setPower(rs.getInt("power"));
				userloginvo.setState(rs.getInt("state"));
				userloginvo.setFace(rs.getString("face"));
				userloginvo.setLastlogin_time(rs.getString("lastLoginTime"));
				userloginvo.setRegister_time(rs.getString("registerTime"));
				userloginvo.setCounter(rs.getInt("counter"));
				userloginvo.setBlog_name(rs.getString("blogName"));
				list.add(userloginvo);
			}
		} catch (SQLException e) {
			throw new BlogException("数据库异常！");
		}
		return list;
	}

	
	/**
	 * 通过loginName查询userslogin表中对应的uId
	 * @param login_name
	 * @return
	 * @throws BlogException
	 */
	public int selectUId(String login_name) throws BlogException{
		int u_id=0;
		String sql="select uId from userslogin where loginName=?";
		try {
			PreparedStatement ps=this.getConn().prepareStatement(sql);
			ps.setString(1, login_name);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				u_id=rs.getInt("uId");			
			}
		} catch (SQLException e) {
			throw new BlogException("数据库异常,查询ID出错");
		}
		return u_id;
	}
	
	
	/**
	 * 查询userslogin表用户名是否存在
	 * @param login_name
	 * @return
	 */
	public boolean selectLoginName(String login_name) throws BlogException{
		Connection connDB = this.getConn();
		boolean b = false;
		String sql = "select loginName from userslogin where loginName=?";
		try {
			PreparedStatement ps = connDB.prepareStatement(sql);
			ps.setString(1, login_name);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				b = true;
			}
		} catch (SQLException e) {
			throw new BlogException("数据库异常！");
		}
		return b;
	}

	
	/**
	 * 通过外键usersinfoId查询usersinfo表个人信息
	 * @param userslogin_id
	 * @return
	 */
	public List selectUsersInfo(int userslogin_id) throws BlogException{
		Connection connDB = this.getConn();
		String sql = "select * from usersinfo where usersloginId=?";
		List list = new ArrayList();
		try {
			PreparedStatement ps = connDB.prepareStatement(sql);
			ps.setInt(1, userslogin_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				UsersInfoVO usersinfovo = new UsersInfoVO();
				usersinfovo.setU_id(rs.getInt("uId"));
				usersinfovo.setUserslogin_id(rs.getInt("usersloginId"));
				usersinfovo.setEmail(rs.getString("email"));
				usersinfovo.setSex(rs.getInt("sex"));
				usersinfovo.setBirthday(rs.getString("birthday"));
				usersinfovo.setAge(rs.getInt("age"));
				usersinfovo.setTel(rs.getString("tel"));
				usersinfovo.setAddress(rs.getString("address"));
				usersinfovo.setSelf_name(rs.getString("selfName"));
				usersinfovo.setIdiograph(rs.getString("idiograph"));
				usersinfovo.setIntroduce(rs.getString("introduce"));
				usersinfovo.setArea(rs.getString("area"));
				usersinfovo.setCity(rs.getString("city"));
				list.add(usersinfovo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BlogException("读取详细信息,数据库异常！");
		}
		return list;
	}

	
	/**
	 * 通过外键usersinfoId查询usersinfo表中的安全问题和答案
	 * @param userslogin_id
	 * @return
	 */
	public List selectUsersQuestionAnswer(int userslogin_id) throws BlogException{
		Connection connDB = this.getConn();
		List list = new ArrayList();
		String sql = "select question,answer from usersinfo where usersloginId=?";
		try {
			PreparedStatement ps = connDB.prepareStatement(sql);
			ps.setInt(1, userslogin_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				UsersInfoVO usersinfovo = new UsersInfoVO();
				usersinfovo.setAnswer(rs.getString("answer"));
				usersinfovo.setQuestion(rs.getString("question"));
				list.add(usersinfovo);
			}
		} catch (SQLException e) {
			throw new BlogException("读取数据库异常！");
		}

		return list;
	}

	
	/**
	 * 用户注册，插入userslogin用户信息
	 * @param logindto
	 * @return
	 * @throws BlogException
	 */
	public boolean insertUsersLogin(UsersLoginDTO logindto) throws BlogException {
		boolean b1 = false;
		Connection connDB = this.getConn();
		String sql = "INSERT INTO userslogin(loginName,nickName,userPassword,power,state,face,registerTime,counter,blogName,lastLoginTime) VALUES(?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps;
		try {
			ps = connDB.prepareStatement(sql);
			ps.setString(1, logindto.getLogin_name());
			ps.setString(2, logindto.getNick_name());
			ps.setString(3, logindto.getUser_password());
			ps.setInt(4, logindto.getPower());             // 注册时赋值权限 2 会员
			ps.setInt(5, logindto.getState());			 // 注册时赋值状态 1 正常状态
			ps.setString(6, logindto.getFace());
			ps.setString(7, logindto.getRegister_time());
			ps.setInt(8, logindto.getCounter());
			ps.setString(9, logindto.getBlog_name());
			ps.setString(10, logindto.getLastlogin_time());
			int i = ps.executeUpdate();
			if (i == 1)
				b1 = true;
			System.out.println("怎么了");
		} catch (SQLException e) {
			throw new BlogException("数据异常，userslogin注册失败！");
		}
		return b1;
	}

	
	/**
	 * 用户注册，插入usersinfo用户信息
	 * @param infodto
	 * @return
	 * @throws BlogException
	 */
	public boolean insertUsersInfo(int uid,UsersInfoDTO infodto) throws BlogException {
		boolean b2 = false;
		Connection connDB = this.getConn();
		String sql = "INSERT INTO usersinfo(question,answer,email,sex,birthday,age,tel,address,selfName,idiograph,introduce,area,city,usersloginId) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps;
		try {
			ps = connDB.prepareStatement(sql);
			ps.setString(1, infodto.getQuestion());
			ps.setString(2, infodto.getAnswer());
			ps.setString(3, infodto.getEmail());
			ps.setInt(4, infodto.getSex());
			ps.setString(5, infodto.getBirthday());
			ps.setInt(6, infodto.getAge());
			ps.setString(7, infodto.getTel());
			ps.setString(8, infodto.getAddress());
			ps.setString(9, infodto.getSelf_name());
			ps.setString(10, infodto.getIdiograph());
			ps.setString(11, infodto.getIntroduce());
			ps.setString(12, infodto.getArea());
			ps.setString(13, infodto.getCity());
			ps.setInt(14, uid);		
			int i = ps.executeUpdate();
			if (i == 1)
				b2 = true;
		} catch (SQLException e) {
			throw new BlogException("数据异常，usersinfo注册失败！");
		}
		return b2;
	}
	
	
	/**
	 * 删除用户，通过外键usersloginId删除usersinfo表中信息
	 * @param userslogin_id
	 * @return
	 * @throws BlogException
	 */
	public boolean deleteUsersinfo(int userslogin_id) throws BlogException{
		boolean b1=false;
		String sql="delete from usersinfo where usersloginId=?";
		try {
			PreparedStatement ps = this.getConn().prepareStatement(sql);
			ps.setInt(1, userslogin_id);
			if(ps.executeUpdate()==1){
				b1=true;
			}
		} catch (SQLException e) {
			throw new BlogException("删除用户失败！");
		}	
		return b1;
	}
	
	
	/**
	 * 删除用户，通过uId删除userslogin表中信息
	 * @param u_id
	 * @return
	 * @throws BlogException
	 */
	public boolean deleteUserslogin(int u_id) throws BlogException{
		boolean b2=false;
		String sql="delete from userslogin where uId=?";
		try {
			PreparedStatement ps = this.getConn().prepareStatement(sql);
			ps.setInt(1, u_id);
			if(ps.executeUpdate()==1){
				b2=true;
			}
		} catch (SQLException e) {
			throw new BlogException("删除用户失败！");
		}	
		return b2;
	}
	
	
	/**
	 * 综合查询记录数
	 * @param sex
	 * @param area
	 * @param nick_name
	 * @return
	 * @throws BlogException
	 */
	public int getLogCountByAll(int sex,String area,String nick_name) throws BlogException{
		int logcount=0;
		Connection connDB = this.getConn();
		String sql="select count(*) from userslogin,usersinfo where usersinfo.sex=? and usersinfo.area=? and userslogin.uId=usersinfo.usersloginId and userslogin.nickName like?";
		try {
			PreparedStatement ps = connDB.prepareStatement(sql);
			ps.setInt(1, sex);
			ps.setString(2, area);
			ps.setString(3, nick_name);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				logcount=rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new BlogException("综合查询记录总数出错!");
		}
		return logcount;
	}
	
	/**
	 * 综合查询userslogin表的记录
	 * @param sex
	 * @param area
	 * @param nick_name
	 * @param curpage
	 * @param pagelog
	 * @return
	 * @throws BlogException
	 */
	public List selectUsersloginByAll(int sex,String area,String nick_name,int curpage,int pagelog) throws BlogException{
		String sql="select userslogin.* from userslogin,usersinfo where usersinfo.sex=? and usersinfo.area=? and userslogin.uId=usersinfo.usersloginId and userslogin.nickName like? limit ?,?";
		int first=(curpage-1)*pagelog;
		List list=new ArrayList();
		try {
			PreparedStatement ps= this.getConn().prepareStatement(sql);
			ps.setInt(1, sex);
			ps.setString(2, area);
			ps.setString(3, nick_name);
			ps.setInt(4, first);
			ps.setInt(5, pagelog);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				UsersLoginVO userloginvo = new UsersLoginVO();
				userloginvo.setU_id(rs.getInt("uId"));
				userloginvo.setLogin_name(rs.getString("loginName"));
				userloginvo.setNick_name(rs.getString("nickName"));
				userloginvo.setUser_password(rs.getString("userPassword"));
				userloginvo.setPower(rs.getInt("power"));
				userloginvo.setState(rs.getInt("state"));
				userloginvo.setFace(rs.getString("face"));
				userloginvo.setLastlogin_time(rs.getString("lastLoginTime"));
				userloginvo.setRegister_time(rs.getString("registerTime"));
				userloginvo.setCounter(rs.getInt("counter"));
				userloginvo.setBlog_name(rs.getString("blogName"));
				list.add(userloginvo);
			}
		} catch (SQLException e) {
			throw new BlogException("综合查询用户失败！");
		}
		return list;
	}
	
	
	/**
	 * 通过性别nickName查询userslogin表中的记录信息数
	 * @param nickname
	 * @return
	 * @throws BlogException
	 */
	public int getLogCountByNickName(String nick_name) throws BlogException{
		int logcount=0;
		Connection connDB = this.getConn();
		String sql="select count(*) from userslogin where nickName=?";