userdaoimpl.java

QQ聊天

package com.sato.server.dao.impl;

//第三步  实现
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 java.util.Vector; 

import com.sato.pub.PackType;
import com.sato.pub.QQpack;
import com.sato.pub.User;
import com.sato.server.dao.db.DBConn;
import com.sato.server.dao.inf.UserDAO;
import com.sato.server.dao.vo.UserBean;
public class UserDAOimpl implements UserDAO {
	public Vector<Vector<String>> selectAllemp(String sid, String sname,Integer nisonline) {
		Vector<Vector<String>> rsVector = new Vector<Vector<String>>();
		Connection conn = null;
		conn = DBConn.getCon();

		String sql = "select * from t_user where 1=1";

		if (sid != null&&!sid.equals("")) {
			sql = sql + " and sid=" +"'"+sid+ "'";
		}
		if (sname != null&&!sname.equals("")) {
			sql = sql + " and sname like"+"'%"+sname+"%'";
		}
		if(nisonline!=null){
			if(nisonline==0){
				sql=sql+"and nisonline=0";
			}
			else{
				sql=sql+"and nisonline=1";
			}
		}
		sql=sql+"order by sid";
		ResultSet rs=null;
		PreparedStatement ps=null;
		try {
			ps = conn.prepareStatement(sql);// 创建操作对象.
			rs = ps.executeQuery(sql);
			
			while (rs.next()) {
				Vector<String> rowVector = new Vector<String>();
				rowVector.add(rs.getString("sid"));
				rowVector.add(rs.getString("sname"));
				rowVector.add(rs.getString("ssex"));
				rowVector.add(String.valueOf(rs.getInt("nage")));
				rowVector.add(rs.getString("saddress"));
				if(rs.getInt("nisonline")==0)
				{
					rowVector.add("不在线");
				}
				else{
					rowVector.add("在线");
				}
				
				rowVector.add(rs.getString("dregtime"));
				rsVector.add(rowVector);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
//				ps.close();
//				rs.close();	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return rsVector;
	}

	public String updateUser(UserBean user) {
		// TODO 自动生成方法存根
		Connection conn = null;
		String sql = "update t_user set sname=? ,spassword=? ,ssex=? ,nage=? ,saddress=? where 1=1 and sid=? ";
		PreparedStatement ps =null;
		try {
			conn = DBConn.getCon();
			ps = conn.prepareStatement(sql);// 创建操作对象.
			ps.setString(1,user.getSname());
			ps.setString(2, user.getSpassword());
			ps.setString(3, user.getSsex());
			ps.setInt(4, user.getNage());
			ps.setString(5, user.getSaddress());
			ps.setString(6, user.getSid());
			ps.executeUpdate();
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
//				ps.close();	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return "ok";
	}


	public List<UserBean> getOnlineUser() {
		// TODO Auto-generated method stub
		List<UserBean> list =new ArrayList<UserBean>();
		Connection conn = null;
		conn = DBConn.getCon();	
		String sql = "select * from t_user t where nisonline=1 order by sid";
		ResultSet rs=null;
		PreparedStatement ps=null;
		try {
			ps = conn.prepareStatement(sql);// 创建操作对象.
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				UserBean emp=new UserBean();
				emp.setSname(rs.getString("sname"));
				emp.setSid(rs.getString("sid"));
				list.add(emp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
//				ps.close();
//				rs.close();	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public String updatePassword(String id) {
		// TODO Auto-generated method stub
		Connection conn = null;
		String sql1 = "update t_user set  spassword=?  where sid=? ";
		PreparedStatement ps=null;
		try {		
			conn = DBConn.getCon();		
			ps= conn.prepareStatement(sql1);// 创建操作对象.
			ps.setString(1, "123456");
			ps.setString(2, id);
			ps.executeUpdate();	
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	public String deleUser(String id) {
		// TODO Auto-generated method stub
		Connection conn = null;
		String sql1 = "delete from   t_user   where sid=? ";	
		try {		
			conn = DBConn.getCon();		
			PreparedStatement ps1 = conn.prepareStatement(sql1);// 创建操作对象.
			ps1.setString(1, id);
			ps1.executeUpdate();	
			
		} catch (Exception e) {
			// TODO 自动生成 catch 块			
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return "ok";
	}

	public String insertUser(UserBean user) {
		// TODO Auto-generated method stub
		Connection conn = null;
		String sql = "insert into  t_user(sid,sname,spassword,ssex,nage,saddress,nisonline) " +
				"values (?,?,?,?,?,?,?) ";
		try {
			conn = DBConn.getCon();
			PreparedStatement ps = conn.prepareStatement(sql);// 创建操作对象.
			ps.setString(1, user.getSid());
			ps.setString(2, user.getSname());
			ps.setString(3, user.getSpassword());
			ps.setString(4, user.getSsex());
			ps.setInt(5, user.getNage());
			ps.setString(6, user.getSaddress());
			ps.setInt(7, 0);
			ps.executeUpdate();

		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return "ok";
	}

	public String updateAllPassword() {
		// TODO Auto-generated method stub
		Connection conn = null;
		String sql1 = "update t_user set  spassword=? ";	
		try {		
			conn = DBConn.getCon();		
			PreparedStatement ps1 = conn.prepareStatement(sql1);// 创建操作对象.
			ps1.setString(1, "123456");
			ps1.executeUpdate();	
			
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	public Object checkUser(String id, String password) {
		// TODO Auto-generated method stub
		Connection conn = null;
		conn = DBConn.getCon();
		String sql = "select * from t_user where 1=1 and sid="+id;
		String str=null;
		QQpack qqpack=new QQpack();
		User from=new User();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				if(rs.getString("nisonline").equals("1")){
					str="在线";
					qqpack.setType(PackType.login_fail);
					qqpack.setContent(str);
				}
				else if(!rs.getString("spassword").equals(password)){
					str="密码不正确";
					qqpack.setType(PackType.login_fail);
					qqpack.setContent(str);
				}
				else{
					str="验证通过";
					qqpack.setType(PackType.login_succeed);
					qqpack.setContent(str);
					from.setSid(id);
					from.setSname(rs.getString("sname"));
					qqpack.setFrom(from);
					this.updateOnline(id, true);
				}
			}
			if(str==null){
				str="用户不存在";
				qqpack.setContent(str);
				qqpack.setType(PackType.login_fail);
			}
			return qqpack;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return null;
	}
	public void close() {
		// TODO Auto-generated method stub
		
	}
	public String setAllOffline() {
		// TODO Auto-generated method stub
		Connection conn = null;
		conn = DBConn.getCon();
		String sql = "update t_user set  nisonline=0";
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}// 创建操作对象.
		
		return null;
	}
	public String updateOnline(String id, boolean online) {
		// TODO Auto-generated method stub
		Connection conn = null;
		conn = DBConn.getCon();
		String sql = "update t_user set  nisonline=?  where sid="+id;

		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sql);
			if(online){
				ps.setInt(1, 1);
			}
			else{
				ps.setInt(1, 0);
			}
			ps.executeUpdate();	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return null;
	}

	public String changPassword(String id,String spassword){
		Connection conn = null;
		String sql1 = "update t_user set  spassword=?  where sid=? ";	
		try {		
			conn = DBConn.getCon();		
			PreparedStatement ps1 = conn.prepareStatement(sql1);// 创建操作对象.
			ps1.setString(1, spassword);
			ps1.setString(2, id);
			ps1.executeUpdate();
			
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	public String getNextId() {
		// TODO Auto-generated method stub
		String qqnum=null;
		Connection conn = null;
		conn = DBConn.getCon();
		String sql = "select lpad(max(sid+1),5,'0') as sid from t_user";
		try {
			conn = DBConn.getCon();
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery(sql);
			while(rs.next()){
				qqnum=rs.getString("sid");
			}
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return qqnum;
	}
	public Object checkPassword(String id, String password) {
		// TODO Auto-generated method stub
		Connection conn = null;
		conn = DBConn.getCon();
		String sql = "select * from t_user where 1=1 and sid="+id;
		String str=null;
		QQpack qqpack=new QQpack();
		User from=new User();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				if(rs.getString("nisonline").equals("1")){
					str="在线";
					qqpack.setType(PackType.login_fail);
					qqpack.setContent(str);
				}
				else if(!rs.getString("spassword").equals(password)){
					str="密码不正确";
					qqpack.setType(PackType.login_fail);
					qqpack.setContent(str);
				}
				else{
					str="通过";
					qqpack.setType(PackType.login_succeed);
					qqpack.setContent(str);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return qqpack;
	}
}