oracleuserdao.java

电子商城

package com.lmh.dao.impl;

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

import com.lmh.dao.db.DBTool;
import com.lmh.dao.inf.UserDAO;
import com.lmh.dao.vo.UserBean;

public class OracleUserDAO implements UserDAO {
	private Connection conn = null;

	/**
	 * 查询用户.
	 * 
	 * @param suser
	 *            注册用户名(精确查) (null:表无此条件).
	 * @param sname
	 *            用户姓名(模糊查) (null:表无此条件).
	 * @param nlevel
	 *            用户级别(1 5 9) (-1:表无此条件).
	 * @param slock
	 *            是否冻结(0 1) (null:表无此条件).
	 * @param pastart
	 *            记录 开始行 (-1:表无此条件).
	 * @param paend
	 *            记录 结束行 (-1:表无此条件).
	 * @return 用户对象集.
	 */
	public List searchUser(String suser, String sname, int nlevel,
			String slock, int pastart, int paend) {

		conn = DBTool.getConn();

		List<UserBean> userList = new ArrayList<UserBean>();

		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer allStr = new StringBuffer();

		allStr.append("select * from ");
		allStr.append("(");
		allStr
				.append("select rownum as myRow,suser,spwd,sname,ssex,to_char(dbirth,'yyyy-mm-dd') as dbirth,semail,");
		allStr.append("sphone,saddress,scode,nlevel,");
		allStr
				.append("to_char(dregdate,'yyyy-mm-dd hh24:mi:ss') as dregdate,slock,to_char(dlastdate,'yyyy-mm-dd hh24:mi:ss') as dlastdate,nlogin");
		allStr.append("	from t_user ");
		allStr.append(" where 1=1 ");
		if (suser != null && suser.length() != 0) {
			allStr.append(" and suser = '" + suser + "'");
		}
		if (sname != null && sname.length() != 0) {
			allStr.append(" and sname like '%" + sname + "%'");
		}
		if (nlevel != -1) {
			allStr.append(" and nlevel = " + nlevel);
		}
		if (slock != null && slock.length() != 0) {
			allStr.append(" and slock = '" + slock + "'");
		}
		if (paend != -1) {
			allStr.append(" and rownum <=" + paend);
		}
		allStr.append(" )");
		if (pastart != -1) {
			allStr.append(" where myRow >= " + pastart);
		}
		try {
			ps = conn.prepareStatement(allStr.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				UserBean user = new UserBean();

				user.setSuser(rs.getString("suser"));
				user.setSpwd(rs.getString("spwd"));
				user.setSname(rs.getString("sname"));
				user.setSsex(rs.getString("ssex"));
				user.setDbirth(rs.getString("dbirth"));
				user.setSemail(rs.getString("semail"));
				user.setSphone(rs.getString("sphone"));
				user.setSaddress(rs.getString("saddress"));
				user.setScode(rs.getString("scode"));
				user.setNlevel(rs.getInt("nlevel"));
				user.setDregdate(rs.getString("dregdate"));
				user.setSlock(rs.getString("slock"));
				user.setDlastdate(rs.getString("dlastdate"));
				user.setNlogin(rs.getInt("nlogin"));

				userList.add(user);
			}
			return userList;
		} catch (SQLException e) {
			e.printStackTrace();
			return userList;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 查询用户.
	 * 
	 * @param suser
	 *            注册用户名(精确查).
	 * @return 用户对象.
	 */
	public UserBean searchUser(String suser) {

		List userList = searchUser(suser, null, -1, null, -1, -1);

		if (userList.size() != 0) {
			return (UserBean) userList.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 获取用户总数.
	 * 
	 * @return 用户总数.
	 */
	public int getCount() {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int num = 0;

		String numStr = "select  count(DISTINCT(suser))as countAll from t_user";

		try {
			ps = conn.prepareStatement(numStr);
			rs = ps.executeQuery();
			while (rs.next()) {
				num = rs.getInt("countAll");
			}
			return num;
		} catch (SQLException e) {
			e.printStackTrace();
			return num;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 获取用户总数.
	 * 
	 * @param suser
	 *            注册用户名(精确查) (null:表无此条件).
	 * @param sname
	 *            用户姓名(模糊查) (null:表无此条件).
	 * @param nlevel
	 *            用户级别(1 5 9) (-1:表无此条件).
	 * @param slock
	 *            是否冻解(0 1) (null:表无此条件).
	 * @return 用户总数.
	 */
	public int getCount(String suser, String sname, int nlevel, String slock) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer countStr = null;
		int num = 0;

		countStr = new StringBuffer(
				"select  count(*)as countAll from t_user where 1=1");
		if (suser != null && suser.length() != 0) {
			countStr.append(" and suser = '" + suser + "'");
		}
		if (sname != null && sname.length() != 0) {
			countStr.append(" and sname like '%" + sname + "%' ");
		}
		if (nlevel != -1) {
			countStr.append(" and nlevel = " + nlevel);
		}
		if (slock != null && slock.length() != 0) {
			countStr.append(" and slock = '" + slock + "'");
		}

		try {
			ps = conn.prepareStatement(countStr.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				num = rs.getInt("countAll");
			}
			return num;
		} catch (SQLException e) {
			e.printStackTrace();
			return num;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 删除用户.
	 * 
	 * @param suser
	 *            用户名.
	 * @return 删除是否成功.
	 */
	public boolean deleteUser(String suser) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		String sql = "delete from t_user where suser='" + suser + "'";
		try {
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 注册用户.
	 * 
	 * @param userBean
	 *            用户对象.
	 * @return 添加是否成功.
	 */
	public boolean insertUser(UserBean userBean) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;

		StringBuffer sql = new StringBuffer("insert into t_user");
		sql = sql.append("(suser,spwd,sname,ssex,dbirth,semail,");
		sql = sql.append("sphone,saddress,scode,nlevel,dregdate");
		sql = sql.append(",slock,dlastdate,nlogin)");
		sql = sql.append("values");
		sql = sql.append("('" + userBean.getSuser() + "','"
				+ userBean.getSpwd() + "','" + userBean.getSname() + "','"
				+ userBean.getSsex() + "',to_date('" + userBean.getDbirth()
				+ "','yyyy-mm-dd'),");
		sql = sql
				.append("'" + userBean.getSemail() + "','"
						+ userBean.getSphone() + "','" + userBean.getSaddress()
						+ "','");
		sql = sql.append(userBean.getScode() + "'," + userBean.getNlevel()
				+ ",sysdate,'" + userBean.getSlock() + "',sysdate,"
				+ userBean.getNlogin() + ")");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 修改用户基础信息 (根据suser修改sname,ssex,dbirth,semail,sphone,saddress,scode字段的值).
	 * 
	 * @param userBean
	 *            用户对象.
	 * @return 修改是否成功.
	 */
	public boolean updateUser(UserBean userBean) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		StringBuffer updateStr = new StringBuffer(" Update t_user set ");

		updateStr.append(" suser= '" + userBean.getSuser() + "'");

		if (userBean.getSname() != null && userBean.getSname().length() != 0) {
			updateStr.append(" ,sname = '" + userBean.getSname() + "'");
		}
		if (userBean.getSsex() != null && userBean.getSsex().length() != 0) {
			updateStr.append(" ,ssex = '" + userBean.getSsex() + "'");
		}
		if (userBean.getDbirth() != null && userBean.getDbirth().length() != 0) {
			updateStr.append(" ,dbirth = to_date('" + userBean.getDbirth()
					+ "','yyyy-mm-dd')");
		}
		if (userBean.getSemail() != null && userBean.getSemail().length() != 0) {
			updateStr.append(" ,semail = '" + userBean.getSemail() + "'");
		}
		if (userBean.getSphone() != null && userBean.getSphone().length() != 0) {
			updateStr.append(" ,sphone = '" + userBean.getSphone() + "'");
		}
		if (userBean.getSaddress() != null
				&& userBean.getSaddress().length() != 0) {
			updateStr.append(" ,saddress = '" + userBean.getSaddress() + "'");
		}
		if (userBean.getScode() != null && userBean.getScode().length() != 0) {
			updateStr.append(" ,scode = '" + userBean.getScode() + "'");
		}
		updateStr.append(" where  suser= '" + userBean.getSuser() + "'");

		try {
			ps = conn.prepareStatement(updateStr.toString());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 冻解 或 解除冻解用户.
	 * 
	 * @param suser
	 *            注册用户名.
	 * @param status
	 *            标识(1:冻结; 0:解除冻结).
	 * @return 操作是否成功.
	 */
	public boolean updateUserForLock(String suser, String status) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		StringBuffer updateStr = new StringBuffer(" Update t_user set ");
		updateStr.append(" slock = '" + status + "'");
		updateStr.append(" where  suser= '" + suser + "'");

		try {
			ps = conn.prepareStatement(updateStr.toString());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 修改密码.
	 * 
	 * @param suser
	 *            注册用户名.
	 * @param spwd
	 *            密码.
	 * @return 修改是否成功.
	 */
	public boolean updateUserForPWD(String suser, String spwd) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		StringBuffer updateStr = new StringBuffer(" Update t_user set ");
		updateStr.append(" spwd= '" + spwd + "'");
		updateStr.append(" where  suser= '" + suser + "'");

		try {
			ps = conn.prepareStatement(updateStr.toString());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	/**
	 * 更新用户登录次数,最后登录时间等.
	 * 
	 * @param suser
	 *            注册用户名.
	 */
	public void updateUserForUp(String suser) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		StringBuffer updateStr = new StringBuffer(" Update t_user set ");
		updateStr.append(" dlastdate = sysdate ,");
		updateStr
				.append(" nlogin = (select nvl(nlogin,0)+1 from t_user where suser = '");
		updateStr.append(suser + "') ");
		updateStr.append(" where  suser= '" + suser + "'");

		try {
			ps = conn.prepareStatement(updateStr.toString());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}
}