public List<Account> findByCondition(
String idcardNo, String realName, String loginName, String status, int page, int pageSize)
throws DAOException {
// 用来封住查询条件的值
List<Object> params = new ArrayList<Object>();
StringBuffer sb = buildFIndSQL(idcardNo, realName, loginName, status, page, pageSize, params);
List<Account> accs = new ArrayList<Account>();
Connection conn = DBUtil.getConnection();
try {
PreparedStatement pstmt = conn.prepareStatement(sb.toString());
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Account a = createAccount(rs);
accs.add(a);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException("查询账务账户失败", e);
} finally {
DBUtil.closeConnection();
}
return accs;
}
public Account findById(int id) throws DAOException {
String sql = "select * from account where id=?";
Connection conn = DBUtil.getConnection();
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
return createAccount(rs);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException("根据id查询账务账号信息失败！", e);
} finally {
DBUtil.closeConnection();
}
return null;
}
public void pauseAccount(int id) throws DAOException {
String sql = "update account set status='1',pause_date=sysdate where id=?";
Connection conn = DBUtil.getConnection();
try {
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new DAOException("暂停账务账号失败！", e);
} finally {
DBUtil.closeConnection();
}
}
public Account findByIdcardNo(String idcardNo) throws DAOException {
if (idcardNo == null) {
return null;
}
String sql = "select * from account where idcard_no=?";
Connection conn = DBUtil.getConnection();
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, idcardNo);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
return createAccount(rs);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.closeConnection();
}
return null;
}
public int findTotalPage(
String idcardNo, String realName, String loginName, String status, int pageSize)
throws DAOException {
// 拼查询总行数的sql
StringBuffer sb = new StringBuffer();
sb.append("select count(*) from account where 1=1");
List<Object> params = new ArrayList<Object>();
if (idcardNo != null && idcardNo.length() > 0) {
sb.append("and idcard_no=? ");
params.add(idcardNo);
}
if (realName != null && realName.length() > 0) {
sb.append("and real_name=? ");
params.add(realName);
}
if (loginName != null && loginName.length() > 0) {
sb.append("and login_name=? ");
params.add(loginName);
}
if (status != null && !status.equals("-1")) {
sb.append("and status=? ");
params.add(status);
}
Connection conn = DBUtil.getConnection();
try {
PreparedStatement pstmt = conn.prepareStatement(sb.toString());
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(i + 1, params.get(i));
}
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
int rows = rs.getInt(1);
if (rows % pageSize == 0) {
return rows / pageSize;
} else {
return rows / pageSize + 1;
}
}
} catch (SQLException e) {
e.printStackTrace();
throw new DAOException("查询总页数失败！", e);
}
return 0;
}
public void addAccount(Account account) throws DAOException {
if (account == null) {
return;
}
String sql =
"insert into account values(seq_account.nextval,?,?,?,'1',sysdate,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
Connection conn = DBUtil.getConnection();
try {
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setObject(1, account.getRecommenderId());
pstmt.setObject(2, account.getLoginName());
pstmt.setObject(3, account.getLoginPassword());
pstmt.setObject(4, account.getPauseDate());
pstmt.setObject(5, account.getCloseDate());
pstmt.setObject(6, account.getRealName());
pstmt.setObject(7, account.getIdcardNo());
pstmt.setObject(8, account.getBirthdate());
pstmt.setObject(9, account.getGender());
pstmt.setObject(10, account.getOccupation());
pstmt.setObject(11, account.getTelephone());
pstmt.setObject(12, account.getEmail());
pstmt.setObject(13, account.getMailaddress());
pstmt.setObject(14, account.getZipcode());
pstmt.setObject(15, account.getQq());
pstmt.setObject(16, account.getLastLoginTime());
pstmt.setObject(17, account.getLastLoginIp());
pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
throw new DAOException("账务账号插入失败！", e);
}
}