oracledao.java

聊天工具

package server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import utils.ConnDB;
import utils.UserInfoBean;
/**
 * Oracle数据库访问对象
 * @author 洪景泉
 *
 */
public class OracleDAO implements UserDAO{
	/**
	 * 添加用户信息
	 */
	  
	 public void insert(UserInfoBean userBean) {
		Connection conn = ConnDB.getConnection();

		String sql = "insert into qq_user(ID,Name,NickName,PassWord,Sex,Age,Telephone,Department,Address,nisonLine,LoginCount,RegTime,Icon)" +
		" values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, userBean.getSID());
			ps.setString(2, userBean.getSName());
			ps.setString(3, userBean.getSNickName());
			ps.setString(4, userBean.getSPassword());
			ps.setString(5, userBean.getSSex());
			ps.setByte(6, userBean.getNAge());
			ps.setString(7, userBean.getSPhone());
			ps.setString(8, userBean.getSDepartment());
			ps.setString(9, userBean.getSAddress());
			ps.setString(10,userBean.getSOnline());
			
			ps.setLong(11, userBean.getNLoginCount());
			ps.setTimestamp(12, new java.sql.Timestamp(userBean.getDRegTime().getTime()));
			ps.setString(13, userBean.getSIcon());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
	}
	 /**
	  * 更新用户信息
	  */
	public void update(UserInfoBean userBean) {

		Connection conn = ConnDB.getConnection();
		
		String sql = "update qq_user set Name=?,NickName=?,PassWord=?,Sex=?,Age=?,Telephone=?,Department=?,Address=?,Icon=? where ID=?";
	
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, userBean.getSName());
			ps.setString(2, userBean.getSNickName());
			ps.setString(3, userBean.getSPassword());
			ps.setString(4, userBean.getSSex());
			ps.setByte(5, userBean.getNAge());
			ps.setString(6, userBean.getSPhone());
			ps.setString(7, userBean.getSDepartment());
			ps.setString(8, userBean.getSAddress());
			ps.setString(9, userBean.getSIcon());
			ps.setString(10, userBean.getSID());			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
	}
	/**
	 * 删除用户信息
	 */
	public void delete(List list) {
		Connection conn = ConnDB.getConnection();
		PreparedStatement ps = null;
		String sql="delete from qq_user where ID=?";
		
		try {
			ps = conn.prepareStatement(sql);
			for(int i=0;i<list.size();i++){
				ps.setString(1, (String)list.get(i));
				ps.executeUpdate();
			}
		} catch (SQLException e) {
				
			e.printStackTrace();
		}finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
		

	}
	/**
	 * 重置所有用户密码
	 */
	public void resetPWD() {

		Connection conn = ConnDB.getConnection();
		
		String sql = "update qq_user set PassWord=?";
	
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		
			ps.setString(1,"123456");
				
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
	}
	
	/**
	 * 查找用户ID的最大值
	 */
	public String getMaxID() {
		Connection conn = ConnDB.getConnection();
		
		PreparedStatement ps = null;
		String str=null;
		try {
//			nvl函数的作用是如果第一个参数为空，值就是第二个参数的值
			String sql = "select nvl(MAX(ID),10000)+1 from qq_user";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
					str=rs.getString(1);
			}
							
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
		return str;

	}
	/**
	 * 查询用户信息
	 */
	public Vector<Vector<String>> select(String colList,Map<String,String>  sqlMap) {
		
		Connection conn = ConnDB.getConnection();
		Vector<Vector<String>> data = new Vector<Vector<String>>();
		PreparedStatement ps = null;
		
		//通过迭代器遍历返回的映射关系图
		Set eSet=sqlMap.entrySet();
		Iterator eSetIter=eSet.iterator();
		String sql=new String("select "+colList+" from qq_user where 1=1");
		//输出字段
//		String col;
//		String[] sArr = colList.split(",");
//		for (int i = 0; i < sArr.length; i++) {
//			col+=sArr[i];
//		}
		
		
		while (eSetIter.hasNext()){
			Map.Entry element=(Map.Entry)eSetIter.next();
			
			sql+=" and "+element.getKey()+"='"+element.getValue()+"'";
		}
	
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			
			ResultSetMetaData rsmd = rs.getMetaData();
		
			while (rs.next()) {
				
				Vector<String> vRow = new Vector<String>();
				
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					vRow.add(rs.getString(i));
				}
				
				data.add(vRow);
				
			}
		
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
		return data;
	}
	/**
	 * 更新用户在线状态
	 */
	public void updateOnLine(String id, String flag) {
		Connection conn = ConnDB.getConnection();
		String sql = "update qq_user set nisonLine=? where ID=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setString(1,flag);
			ps.setString(2, id);			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
	}
	/**
	 * 更新客户端修改的信息
	 */	
	public void updateClient(UserInfoBean userBean) {

		Connection conn = ConnDB.getConnection();
		
		String sql = "update qq_user set NickName=?,PassWord=?,Age=?,Telephone=?,Department=?,Address=?,Icon=? where ID=?";
	
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, userBean.getSNickName());
			ps.setString(2, userBean.getSPassword());
			ps.setByte(3, userBean.getNAge());
			ps.setString(4, userBean.getSPhone());
			ps.setString(5, userBean.getSDepartment());
			ps.setString(6, userBean.getSAddress());
			ps.setString(7, userBean.getSIcon());
			ps.setString(8, userBean.getSID());			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnDB.close(ps);
			ConnDB.close(conn);
		}
	}


}