userdaooracleimp.java

用java开发的QQ管理系统

package com.zlf.dao;

/**
 * oracle数据库用户表的增删改查的实现
 * @author zlf
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.Vector;

import javax.swing.JOptionPane;

import com.zlf.dao.vo.User;

/**
 * 
 * @author zlf
 *
 */
public class UserDaoOracleImp implements UserDao {
	
	
	
	/**
	 * 
	 * 
	 * 增加用户
	 * @param user
	 * @return 1,0
	 * 1成功
	 * 0失败
	 */
	public int addUser(User user) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pStatm = null;
		try {
			pStatm = conn
					.prepareStatement("Insert into t_user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pStatm.setString(1, user.getUserId());
			pStatm.setString(2, user.getUserPwd());
			pStatm.setString(3, user.getUserName());
			pStatm.setString(4, user.getUserNickName());
			pStatm.setString(5, user.getUserSex());
			pStatm.setString(6, user.getUserIcon());
			pStatm.setInt(7, user.getUserAge());
			pStatm.setString(8, user.getDeptId());
			pStatm.setString(9, user.getUserPhone());
			pStatm.setString(10, user.getUserAddr());
			pStatm.setDate(11, user.getUserLastLogin());
			pStatm.setDate(12, user.getUserRegDate());
			pStatm.setInt(13, user.getUserLoginNum());
			pStatm.setInt(14, user.getUserOnline());
			pStatm.executeUpdate();
			return 1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.close(pStatm);
			DBUtil.close(conn);
		}
	}

	/**
	 * 
	 * 修改用户
	 * @param user
	 * @return 1,0
	 * 1成功
	 * 0失败
	 */
	public int updateUser(User user) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pStatm = null;
		String sql = "Update t_user set USER_PWD=?,USER_NAME=?,USER_NICKNAME=?,USER_SEX=?,"
				+ "USER_ICON=?,USER_AGE=?,DEPT_ID=?,USER_PHONE=?,USER_ADDR=?,USER_LASTLOGIN=?,USER_REGDATE=?,"
				+ "USER_LOGINNUM=?,USER_ONLINE=to_number(?) where user_Id=?";
		try {
			pStatm = conn.prepareStatement(sql);
			pStatm.setString(1, user.getUserPwd());
			pStatm.setString(2, user.getUserName());
			pStatm.setString(3, user.getUserNickName());
			pStatm.setString(4, user.getUserSex());
			pStatm.setString(5, user.getUserIcon());
			pStatm.setInt(6, user.getUserAge());
			pStatm.setString(7, user.getDeptId());
			pStatm.setString(8, user.getUserPhone());
			pStatm.setString(9, user.getUserAddr());
			pStatm.setDate(10, user.getUserLastLogin());
			pStatm.setDate(11, user.getUserRegDate());
			pStatm.setInt(12, user.getUserLoginNum());
			pStatm.setInt(13, user.getUserOnline());
			pStatm.setString(14, user.getUserId());
			pStatm.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.close(pStatm);
			DBUtil.close(conn);
		}

	}

	/**
	 * 
	 * 删除用户
	 * @param userid
	 * @return 1,0
	 * 1成功
	 * 0失败
	 */
	public int deleteUser(String userId) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pStatm = null;
		try {
			pStatm = conn
					.prepareStatement("delete from t_user where user_Id= ?");
			pStatm.setString(1, userId);
			pStatm.executeUpdate();
			
			return 1;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			return 0;
		} finally {
			DBUtil.close(pStatm);
			DBUtil.close(conn);
		}
	}

	/**
	 * 
	 * 查找用户
	 * @param hashmap
	 * @return vector

	 */
	public Vector selectUser(HashMap h) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pStatm = null;
		ResultSet rs = null;
		Vector data = new Vector();

		Set set = h.keySet();
		Iterator iter = set.iterator();
		while (iter.hasNext()) {
			String st = (String) iter.next();
			if (st.equals("1")) {
				/** 1. 查询出显示在表格中的字段*/
				try {
					pStatm = conn
							.prepareStatement("select t.user_id,t.user_name,t.user_nickname,"
									+ "t.user_sex,td.dept_name,t.user_regdate from t_user t,"
									+ "t_dept td where t.dept_id=td.dept_id");
					rs = pStatm.executeQuery();
					while (rs.next()) {
						int i = 1;
						Vector row = new Vector();
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						data.add(row);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("2")) {
				/** 2.查询出用户表某个用户中所有的字段 */
				try {
					pStatm = conn
							.prepareStatement("select * from t_user where user_id=?");
					pStatm.setString(1, (String) h.get("2"));
					rs = pStatm.executeQuery();
					while (rs.next()) {
						int i = 1;
						Vector row = new Vector();
						row.add(rs.getString(i++));// 1
						row.add(rs.getString(i++));// 2
						row.add(rs.getString(i++));// 3
						row.add(rs.getString(i++));// 4
						row.add(rs.getString(i++));// 5
						row.add(rs.getString(i++));// 6
						row.add(rs.getString(i++));// 7
						row.add(rs.getString(i++));// 8
						row.add(rs.getString(i++));// 9
						row.add(rs.getString(i++));// 10
						row.add(rs.getString(i++));// 11
						row.add(rs.getString(i++));// 12
						row.add(String.valueOf(rs.getInt(i++)));// 13
						row.add(String.valueOf(rs.getInt(i++)));// 14
						data.add(row);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("3")) {
				/** 3 返回是否存在部门表的引用 */
				int count = 0;
				try {
					pStatm = conn
							.prepareStatement("select count(*) from t_user where dept_id=?");
					pStatm.setString(1, (String) h.get("3"));
					rs = pStatm.executeQuery();
					while (rs.next()) {
						count = rs.getInt(1);
						data.add(String.valueOf(count));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("4")) {
				/** 4 返回最大编号加一，实现编号自动加一功能 */
				try {
					pStatm = conn
							.prepareStatement("select trim(to_char(to_number(max(t.user_id))+1,'00000')) from t_user t");
					rs = pStatm.executeQuery();
					while (rs.next()) {
						if (rs.getString(1) == null) {
							data.add("10001");
						} else {
							data.add(rs.getString(1));
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("5")) {
				/** 5返回是否存在该用户id */
				int count = 0;
				try {
					pStatm = conn
							.prepareStatement("select count(*) from t_user where user_Id=?");
					pStatm.setString(1, (String) h.get("5"));
					rs = pStatm.executeQuery();
					while (rs.next()) {
						count = rs.getInt(1);
						data.add(String.valueOf(count));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("6")) {
				/** 6 返回用户的状态，是否在线的状态 */
				int onLine = 0;
				try {
					pStatm = conn
							.prepareStatement("select USER_ONLINE from t_user where user_Id=?");
					pStatm.setString(1, (String) h.get("6"));
					rs = pStatm.executeQuery();
					while (rs.next()) {
						onLine = rs.getInt(1);
						data.add(String.valueOf(onLine));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("7")) {
				/**  7 返回用户的密码 */
				try {
					pStatm = conn
							.prepareStatement("select USER_PWD from t_user where user_Id=?");
					pStatm.setString(1, (String) h.get("7"));
					rs = pStatm.executeQuery();
					while (rs.next()) {
						data.add(rs.getString(1));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("8")) {
				/**  8 返回数据库中用户表所有用户信息 */
				try {
					pStatm = conn.prepareStatement("select * from t_user");
					rs = pStatm.executeQuery();
					while (rs.next()) {
						int i = 1;
						Vector row = new Vector();
						row.add(rs.getString(i++));// 1
						row.add(rs.getString(i++));// 2
						row.add(rs.getString(i++));// 3
						row.add(rs.getString(i++));// 4
						row.add(rs.getString(i++));// 5
						row.add(rs.getString(i++));// 6
						row.add(rs.getString(i++));// 7
						row.add(rs.getString(i++));// 8
						row.add(rs.getString(i++));// 9
						row.add(rs.getString(i++));// 10
						row.add(rs.getString(i++));// 11
						row.add(rs.getString(i++));// 12
						row.add(String.valueOf(rs.getInt(i++)));// 13
						row.add(String.valueOf(rs.getInt(i++)));// 14
						data.add(row);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("9")) {
				/** 9 返回在线用户的信息 */
				try {
					pStatm = conn
							.prepareStatement("select t.user_id,t.user_name,t.user_nickname,"
									+ "t.user_sex,td.dept_name,t.user_regdate from t_user t,"
									+ "t_dept td where t.dept_id=td.dept_id and t.user_online=1");
	
					rs = pStatm.executeQuery();

					while (rs.next()) {
						int i = 1;
						Vector row = new Vector();
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						row.add(rs.getString(i++));
						data.add(row);
					}
				} catch (SQLException e) {
					e.printStackTrace();
					JOptionPane.showMessageDialog(null, "数据库表不存在",
							"提示框", JOptionPane.WARNING_MESSAGE);
					System.exit(1);
					
				}

			} else if (st.equals("10")) {
				/** 10 查询某个用户所在的部门的编号 */
				try {
					pStatm = conn
							.prepareStatement("select t.dept_id from t_user t where t.user_id=? ");
					pStatm.setString(1, (String) h.get("10"));
					rs = pStatm.executeQuery();
					while (rs.next()) {
						data.add(rs.getString(1));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

			} else if (st.equals("11")) {
				/** 11  根据条件查询用户 */
				HashMap m = (HashMap) h.get("11");
				Set s = m.keySet();
				Iterator it = s.iterator();
				while (it.hasNext()) {
					String str = (String) it.next();
					String strSql = "select t.user_id,t.user_name,t.user_nickname,"
							+ "t.user_sex,td.dept_name,t.user_regdate from t_user t,"
							+ "t_dept td where t.dept_id=td.dept_id ";
					try {
						if (str.equals("1")) {
							// 根据编号查用户

							pStatm = conn.prepareStatement(strSql
									+ "and t.user_id=?");
							pStatm.setString(1, (String) m.get("1"));

						} else if (str.equals("2")) {
							pStatm = conn.prepareStatement(strSql
									+ "and t.user_name=?");
							pStatm.setString(1, (String) m.get("2"));

						} else if (str.equals("3")) {
							pStatm = conn.prepareStatement(strSql
									+ "and t.dept_id=?");
							pStatm.setString(1, (String) m.get("3"));
						}

						rs = pStatm.executeQuery();
						while (rs.next()) {
							int i = 1;
							Vector row = new Vector();
							row.add(rs.getString(i++));
							row.add(rs.getString(i++));
							row.add(rs.getString(i++));
							row.add(rs.getString(i++));
							row.add(rs.getString(i++));
							row.add(rs.getString(i++));
							data.add(row);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}

		}
		return data;
	}
	
	/**
	 * 
	 * 修改在线状态信息
	 */
	public int updateOnLine(int onLine, String userId) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pStatm = null;
		try {
			pStatm = conn
					.prepareStatement("update t_user set USER_ONLINE=? where user_Id=?");
			pStatm.setInt(1, onLine);
			pStatm.setString(2, userId);
			pStatm.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.close(pStatm);
			DBUtil.close(conn);
		}
	}

	/**
	 * 修改特定密码和全部密码
	 */
	public int updatePwd(String userId,String userPwd) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pStatm = null;
		try {
			if (!userId.equals("")){
				pStatm = conn
				.prepareStatement("update t_user set USER_PWD=? where user_Id=?");
				pStatm.setString(1, userPwd);
				pStatm.setString(2, userId);
			}else {
				pStatm = conn
				.prepareStatement("update t_user set USER_PWD=?");
				pStatm.setString(1, userPwd);
			}
			pStatm.executeUpdate();
			
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.close(pStatm);
			DBUtil.close(conn);
		}
	}




}