userdao.java

交易撮合系统是一套买卖信息沟通的平台

package dao;

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 java.util.List;

import conn.DBConnection;
import domain.*;

public class UserDao {
	
	public List<Users> getCredit(){
		Connection conn = DBConnection.getConn();
    	String sql = "SELECT users.credit FROM webpk.users,webpk.offers WHERE users.email=offers.email AND trade=0";
    	List<Users> ulist= new ArrayList<Users>();
    	try{
    		Statement stmt = (Statement) conn.createStatement();
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		Users pro = null;
    		while(rs.next()){
    			pro = new Users();
    		    pro.setCredit(rs.getInt("credit"));
    		    ulist.add(pro);
    		}
    		
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    	DBConnection.closeConn();
    	return ulist;
        	 
	}
	public Users login(String email,String password){
		boolean b = false;
		Users user = new Users();
		Connection conn = DBConnection.getConn();
		PreparedStatement pstat =null;
		ResultSet rs = null;
		String sql = "select * from webpk.users where email = ? and password = ?";
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, email);
			pstat.setString(2, password);
			
			rs = pstat.executeQuery();
			if(rs.next()){
				user.setEmail(email);
				user.setPassword(password);
				user.setTruename(rs.getString("truename"));
				user.setSex(rs.getString("sex"));
				user.setEmail(rs.getString("email"));
				user.setProvince(rs.getString("province"));
				user.setCity(rs.getString("city"));
				user.setBirthday(rs.getDate("birthday"));
				user.setTelephone(rs.getString("telephone"));
				user.setMobile(rs.getString("mobile"));
				user.setZip(rs.getString("zip"));
				user.setAddress(rs.getString("address"));
				user.setCredit(rs.getInt("credit"));
				user.setIsclock(rs.getInt("isclock"));
				user.setIsadmin(rs.getInt("isadmin"));
				user.setJihuoma(rs.getString("jihuoma"));
			}
			else{
				return null;
			}
			
		
		}catch(Exception e){
			e.printStackTrace();
			
		}
		
		finally{
			DBConnection.closeConn();
		    }
		return user;
	}
	public boolean selUse(String username){
		boolean b = false;
		Connection conn = DBConnection.getConn();
		String sql = "select * from webpk.users where email = ?";
		PreparedStatement pstat =null;
		ResultSet rs =null;
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, username);
			rs = pstat.executeQuery();
			System.out.println(rs);
			if(!rs.next()){
				b = true;
				return b;
			}else{
				
				return b;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return b;
		}
		finally{
			DBConnection.closeConn();
		}
	}
	
	public boolean reg(Users user){
		boolean b = false;
		Connection conn = DBConnection.getConn();
		String sql = "insert into webpk.users value(?,?,?,?,?,?,?,?,?,?,?,100,0,0,0)";
		PreparedStatement pstat = null;
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(4, user.getEmail());
			pstat.setString(1, user.getPassword());
			pstat.setString(2, user.getTruename());
			pstat.setString(3, user.getSex());
			
			pstat.setString(5, user.getProvince());
			pstat.setString(6, user.getCity());
			pstat.setDate(7, user.getBirthday());
			pstat.setString(8, user.getTelephone());
			pstat.setString(9, user.getMobile());
			pstat.setString(10, user.getZip());
			pstat.setString(11, user.getAddress());
			pstat.executeUpdate();
			b = true;
			
		    DBConnection.closeConn();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("怎么注册异常了哪？");
			e.printStackTrace();
		}
		finally{
			DBConnection.closeConn();
		}
		return b;
	}
	public boolean login(Users user){
		boolean b = false;
		Connection conn = DBConnection.getConn();
		String sql = "insert into webpk.users value(?,?,?,?,?,?,?,?,?,?,?,100,1,0,0)";
		PreparedStatement pstat = null;
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(4, user.getEmail());
			pstat.setString(1, user.getPassword());
			pstat.setString(2, user.getTruename());
			pstat.setString(3, user.getSex());
			
			pstat.setString(5, user.getProvince());
			pstat.setString(6, user.getCity());
			pstat.setDate(7, user.getBirthday());
			pstat.setString(8, user.getTelephone());
			pstat.setString(9, user.getMobile());
			pstat.setString(10, user.getZip());
			pstat.setString(11, user.getAddress());
			pstat.executeUpdate();
			b = true;
			
		    DBConnection.closeConn();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("怎么注册异常了哪？");
			e.printStackTrace();
		}
		finally{
			try {
				if(pstat != null){
					pstat.close();
					}
				if(conn != null){
				conn.close();
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
		    }
		}
		return b;
	}
	
	public List<Users> getUsersMessage(String name){
    	Connection conn = DBConnection.getConn();
    	Statement stmt =null;
    	List<Users> ulist = new ArrayList<Users>();
    	Users p = null;
    	String sql = "SELECT * FROM webpk.users";
    	try{
    		stmt = conn.createStatement();
    		
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		while(rs.next()){
    			p = new Users();
    			p.setEmail(rs.getString("email"));    			
    			p.setPassword(rs.getString("password"));
    			p.setTruename(rs.getString("truename"));
    			p.setSex(rs.getString("sex"));
    			p.setProvince(rs.getString("province"));
    			p.setCity(rs.getString("city"));
    			p.setBirthday(rs.getDate("birthday"));
    			p.setTelephone(rs.getString("telephone"));
    			p.setMobile(rs.getString("mobile"));
    			p.setZip(rs.getString("zip"));
    			p.setAddress(rs.getString("address"));
    			p.setCredit(rs.getInt("credit"));
    			p.setIsclock(rs.getInt("isclock"));
    			p.setIsadmin(rs.getInt("isadmin"));
    			ulist.add(p);
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		DBConnection.closeConn();
    	}
    	return ulist;
    }
	
	/**
	 * 获得表中数据记录的个数
	 *  
	 * @return totalRecord 表中的总记录数
	 */
	public static int getRowNumber() {
		Connection conn = DBConnection.getConn();
		int totalRecord = 0;
		try {
			Statement stmt = conn.createStatement();
			String tsql = "SELECT count(*) FROM webpk.users";
			ResultSet rs = stmt.executeQuery(tsql);
			rs.next();
			totalRecord = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		
		return totalRecord;
	}

	/**
	 * 获得总的页数
	 * 
	 * @param pageSize
	 *            每页显示的条数
	 * @return 返回总页数
	 */
	public static int getTotalPage(int pageSize) {
		int totalPage = 1;
		int tmpPage = 0;
		int rowNum = getRowNumber();
		tmpPage = rowNum % pageSize;
		if (tmpPage == 0) {
			totalPage = rowNum / pageSize;
		} else {
			totalPage = (int)(Math.floor(rowNum / pageSize) + 1);
		}
		if (totalPage == 0) {
			totalPage = 1;
		}
		return totalPage;
	}
	/*
	 * 根据传来的email查出该用户信息
	 */
	public Users getUserContent(String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "SELECT * FROM webpk.users u WHERE u.email=?";
		Users of = null;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				of = new Users();
				of.setEmail(rs.getString("email"));
				of.setPassword(rs.getString("password"));
				of.setTruename(rs.getString("truename"));
				of.setSex(rs.getString("sex"));
				of.setProvince(rs.getString("province"));
				of.setCity(rs.getString("city"));
				of.setBirthday(rs.getDate("birthday"));
				of.setTelephone(rs.getString("telephone"));
				of.setMobile(rs.getString("mobile"));
				of.setZip(rs.getString("zip"));
				of.setAddress(rs.getString("address"));
				of.setCredit(rs.getInt("credit"));
				of.setIsadmin(rs.getInt("isadmin"));
				of.setIsclock(rs.getInt("isclock"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return of;

	}
	/*
	 * 修改帖子状态
	 */
	public int changeUser(int isadmin,int isclock,int credit,String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.isadmin=?,u.isclock=?,u.credit=? WHERE u.email=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, isadmin);
			pstmt.setInt(2, isclock);
			pstmt.setInt(3, credit);
			pstmt.setString(4, email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;

	}
	/*
	 * 修改用户状态
	 */
	public int changeIsClock(String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.isclock=0 WHERE u.email=? and u.isclock=1";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;

	}
	/*
	 * 添加激活码
	 *
	 *
	 * 修改用户状态
	 */
	public int addJiHuoMa(String jihuoma,String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.jihuoma=? WHERE u.email=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,jihuoma);
			pstmt.setString(2, email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;

	}
	public int changePassword(String password,String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.password=? WHERE u.email=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,password);
			pstmt.setString(2, email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;

	}
	public int changeMobile(String mobile,String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.mobile=? WHERE u.email=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,mobile);
			pstmt.setString(2,email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;
	}
	public int changeTel(String tele,String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.telephone=? WHERE u.email=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tele);
			pstmt.setString(2,email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;
	}
	public int changeName(String name,String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.truename=? WHERE u.email=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,name);
			pstmt.setString(2,email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;
	}
	public int changeZip(String zip,String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.users u SET u.zip=? WHERE u.email=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,zip);
			pstmt.setString(2,email);
			
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return count;
	}
	public String getJiHuoMa(String email) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "SELECT u.jihuoma FROM webpk.users u WHERE u.email=?";
		ResultSet rs = null;
		String jihuoma = null;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1,email);
			rs = pstmt.executeQuery();
			rs.next();
			jihuoma = rs.getString("jihuoma");
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
		    DBConnection.closeConn();
		}
		return jihuoma;
	}
}