userdaoimpl.java

来自「用jdbc写的一个存储过程的代码」· Java 代码 · 共 293 行

package com.bluedot.persist;

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

import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.oracore.OracleType;

import com.bluedot.common.Constants;
import com.bluedot.common.JdbcUtil;
import com.bluedot.domain.Page;
import com.bluedot.domain.Role;
import com.bluedot.domain.User;
import com.bluedot.exceptions.ApplicationException;
import com.bluedot.exceptions.DataAccessException;

public class UserDaoImpl implements UserDao {

	public User findUserByLoginName(String loginName) {
		Connection conn = null;
		User user = null;
		int roleId = -1;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "select * from oa_users where loginname=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, loginName);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				user = new User();

				user.setLoginName(rs.getString("loginname"));
				user.setPwd(rs.getString("pwd"));
				user.setAge(rs.getInt("age"));
				roleId = rs.getInt("role_id");
			}

			if (roleId != -1) {
				// 查询角色内容
				RoleDao dao = new RoleDaoImpl();
				Role role = dao.findRoleWithFunsAndMenusById(roleId);
				user.setRole(role);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("数据库操作失败！", e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException("系统异常！", e);
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return user;
	}

	@Override
	public void insertUser(User user) {
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "insert into oa_users values(?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);

			ps.setString(1, user.getLoginName());
			ps.setString(2, user.getPwd());
			ps.setInt(3, user.getAge());
			ps.setInt(4, user.getRole().getId());

			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("global.database.error", e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException("global.system.error", e);
		} finally {
			JdbcUtil.closeConnection(conn);
		}
	}

	@Override
	public List<User> findUserByProperty(User user) {
		Connection conn = null;
		List<User> list = new ArrayList<User>();
		try {
			conn = JdbcUtil.getConnection();
			String sql = genSQL(user);
			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setLoginName(rs.getString("loginname"));
				u.setAge(rs.getInt("age"));
				u.setPwd(rs.getString("pwd"));

				Role role = new Role();
				role.setId(rs.getInt("role_id"));

				u.setRole(role);

				list.add(u);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("global.database.error", e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException("global.system.error", e);
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return list;
	}

	private String genSQL(User user) {
		StringBuffer sb = new StringBuffer("select * from oa_users where 1=1");
		if (user.getLoginName() != null && user.getLoginName().length() > 0) {
			sb.append(" and loginname like '%").append(user.getLoginName())
					.append("%'");
		}
		if (user.getAge() != -1) {
			sb.append(" and age=").append(user.getAge());
		}
		if (user.getRole().getId() != -1) {
			sb.append(" and role_id=").append(user.getRole().getId());
		}
		return sb.toString();
	}

	private String genCountSQL(User user) {
		StringBuffer sb = new StringBuffer(
				"select count(*) from oa_users where 1=1");
		if (user.getLoginName() != null && user.getLoginName().length() > 0) {
			sb.append(" and loginname like '%").append(user.getLoginName())
					.append("%'");
		}
		if (user.getAge() != -1) {
			sb.append(" and age=").append(user.getAge());
		}
		if (user.getRole().getId() != -1) {
			sb.append(" and role_id=").append(user.getRole().getId());
		}
		return sb.toString();
	}

	private String genSQL2(User user, int from, int to) {
		StringBuffer sb = new StringBuffer(
				"select t.* from (select rownum rn,o.* from oa_users o where 1=1");
		if (user.getLoginName() != null && user.getLoginName().length() > 0) {
			sb.append(" and loginname like '%").append(user.getLoginName())
					.append("%'");
		}
		if (user.getAge() != -1) {
			sb.append(" and age=").append(user.getAge());
		}
		if (user.getRole().getId() != -1) {
			sb.append(" and role_id=").append(user.getRole().getId());
		}
		sb.append(") t where t.rn between ").append(from).append(" and ")
				.append(to);
		return sb.toString();
	}

	@Override
	public Page findUserByProperty2(User user, int curPage) {
		Connection conn = null;
		List<User> list = new ArrayList<User>();
		Page p = new Page();
		p.setCurrentPage(curPage);
		try {
			conn = JdbcUtil.getConnection();
			String sql = genCountSQL(user);
			PreparedStatement ps = conn.prepareStatement(sql);
			int totalNumber = 0;
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				totalNumber = rs.getInt(1);
			}
			p.setTotalNumber(totalNumber);

			int from = curPage * ((int) Constants.ITEM_PER_PAGE) - 1;
			int to = from + (int) Constants.ITEM_PER_PAGE - 1;
			String sql2 = genSQL2(user, from, to);

			PreparedStatement ps2 = conn.prepareStatement(sql2);
			ResultSet rs2 = ps2.executeQuery();

			while (rs2.next()) {
				User u = new User();
				u.setLoginName(rs2.getString("loginname"));
				u.setAge(rs2.getInt("age"));
				u.setPwd(rs2.getString("pwd"));

				Role role = new Role();
				role.setId(rs2.getInt("role_id"));

				u.setRole(role);

				list.add(u);
			}

			p.setList(list);

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("global.database.error", e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException("global.system.error", e);
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return p;
	}

	@Override
	public Page findUserByProperty3(User user, int curPage) {
		System.out.println("<<<<<<<<<<<<<<<DB Procedure");
		Connection conn = null;
		List<User> list = new ArrayList<User>();
		Page p = new Page();
		p.setCurrentPage(curPage);
		try {
			conn = JdbcUtil.getConnection();
			String sql = genSQL3(user);

			String sql2 = "call bluedot_pkg.page(?,?,?,?,?)";
			CallableStatement cs = conn.prepareCall(sql2);

			cs.setInt(1, curPage);
			cs.setString(2, sql);
			cs.setInt(3, (int) Constants.ITEM_PER_PAGE);
			cs.registerOutParameter(4, Types.INTEGER);
			cs.registerOutParameter(5, OracleTypes.CURSOR);

			cs.execute();

			p.setTotalPage(cs.getInt(4));
			ResultSet rs = (ResultSet) cs.getObject(5);
			while (rs.next()) {
				User u = new User();
				u.setLoginName(rs.getString("loginname"));
				u.setPwd(rs.getString("pwd"));
				u.setAge(rs.getInt("age"));

				Role role = new Role();
				role.setId(rs.getInt("role_id"));

				u.setRole(role);
				list.add(u);
			}

			p.setList(list);

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("global.database.error", e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException("global.system.error", e);
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return p;
	}

	private String genSQL3(User user) {
		StringBuffer sb = new StringBuffer(
				"select rownum rn,t.* from oa_users t where 1=1");
		if (user.getLoginName() != null && user.getLoginName().length() > 0) {
			sb.append(" and loginname like '%").append(user.getLoginName())
					.append("%'");
		}
		if (user.getAge() != -1) {
			sb.append(" and age=").append(user.getAge());
		}
		if (user.getRole().getId() != -1) {
			sb.append(" and role_id=").append(user.getRole().getId());
		}
		return sb.toString();
	}

}