package com.netctoss.service.dao;
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.netctoss.account.entity.Account;
import com.netctoss.exception.DAOException;
import com.netctoss.service.entity.Service;
import com.netctoss.service.entity.serviceBak;
import com.netctoss.service.vo.ServiceVO;
import com.netctoss.util.DBUtil;
public class ServiceDAOImpl implements IServiceDAO {
public List<ServiceVO> findByCondition(String osUsername, String unixHost,
String idcardNo, String status, int page, int pageSize)
throws DAOException {
StringBuffer sb=new StringBuffer();
List<Object> param=new ArrayList<Object>();
List<ServiceVO> services=new ArrayList<ServiceVO>();
sb.append("select * from ( ");
sb.append(" select s.*, ");
sb.append(" a.IDCARD_NO,a.REAL_NAME, ");
sb.append(" c.NAME,c.DESCR,rownum r ");
sb.append(" from pan_SERVICE s ");
sb.append(" inner join pan_ACCOUNT a ");
sb.append(" on s.ACCOUNT_ID=a.id ");
sb.append(" inner join pan_COST c ");
sb.append(" on s.COST_ID=c.id");
sb.append(" where 1=1 ");
if(osUsername!=null&&osUsername.length()>0){
sb.append("and s.os_username=? ");
param.add(osUsername);
}
if(unixHost!=null&&unixHost.length()>0){
sb.append("and unix_host=? ");
param.add(unixHost);
}
if(idcardNo!=null&&idcardNo.length()>0){
sb.append("and a.idcardNo=? ");
param.add(idcardNo);
}
if(status!=null&&!status.equals("-1")){
sb.append("and status=? ");
param.add(status);
}
sb.append(" and rownum<? ");
sb.append(") where r>? ");
int nextMin=page*pageSize+1;
param.add(nextMin);
int lastMax=(page-1)*pageSize;
param.add(lastMax);
Connection conn=DBUtil.getConnection();
try {
PreparedStatement stat=conn.prepareStatement(sb.toString());
for(int i=0;i<param.size();i++){
stat.setObject(i+1, param.get(i));
}
ResultSet rst= stat.executeQuery();
while(rst.next()){
ServiceVO s=createServiceVO(rst);
services.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new DAOException("查询业务账号",e);
}finally{
DBUtil.closeConnection();
}
return services;
}
public ServiceVO findDetailById(int id)
throws DAOException {
ServiceVO s=new ServiceVO();
String sql="select * from pan_service s " +
"inner join pan_account a on s.account_id=a.id " +
"inner join pan_cost c on s.cost_id=c.id " +
"where s.id=?";
Connection conn=DBUtil.getConnection();
try {
PreparedStatement stat=conn.prepareStatement(sql);
stat.setInt(1, id);
ResultSet rst=stat.executeQuery();
while(rst.next()){
s=createServiceVO(rst);
return s;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public int findBytalPage(String osUsername, String unixHost,
String idcardNo, String status, int pageSize) throws DAOException {
StringBuffer sb=new StringBuffer();
List<Object> param=new ArrayList<Object>();
sb.append("select count(*) from pan_service s ");
sb.append("inner join pan_account a ");
sb.append("on s.account_id=a.id ");
sb.append("inner join pan_cost c ");
sb.append("on s.cost_id=c.id ");
sb.append("where 1=1 ");
if(osUsername!=null&&osUsername.length()>0){
sb.append("and s.os_username=? ");
param.add(osUsername);
}
if(unixHost!=null&&unixHost.length()>0){
sb.append("and unix_host=? ");
param.add(unixHost);
}
if(idcardNo!=null&&idcardNo.length()>0){
sb.append("and a.idcardNo=? ");
param.add(idcardNo);
}
if(status!=null&&!status.equals("-1")){
sb.append("and status=? ");
param.add(status);
}
Connection conn=DBUtil.getConnection();
try {
PreparedStatement stat=conn.prepareStatement(sb.toString());
for(int i=0;i<param.size();i++){
stat.setObject(i+1, param.get(i));
}
ResultSet rst=stat.executeQuery();
while(rst.next()){
int rows=rst.getInt(1);
if(rows%pageSize==0){
return rows/pageSize;
}else{
return rows/pageSize+1;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
private ServiceVO createServiceVO(
ResultSet rs)
throws SQLException {
ServiceVO s = new ServiceVO();
s.setId(rs.getInt("ID"));
s.setAccountId(rs.getInt("ACCOUNT_ID"));
s.setUnixHost(rs.getString("UNIX_HOST"));
s.setOsUsername(rs.getString("OS_USERNAME"));
s.setLoginPassword(rs.getString("LOGIN_PASSWD"));
s.setStatus(rs.getString("STATUS"));
s.setCreateDate(rs.getDate("CREATE_DATE"));
s.setPauseDate(rs.getDate("PAUSE_DATE"));
s.setCloseDate(rs.getDate("CLOSE_DATE"));
s.setCostId(rs.getInt("COST_ID"));
s.setIdcardNo(rs.getString("IDCARD_NO"));
s.setRealName(rs.getString("REAL_NAME"));
s.setCostName(rs.getString("NAME"));
s.setDescr(rs.getString("DESCR"));
return s;
}
private Service createService(
ResultSet rs)
throws SQLException {
Service s = new Service();
s.setId(rs.getInt("ID"));
s.setAccountId(rs.getInt("ACCOUNT_ID"));
s.setUnixHost(rs.getString("UNIX_HOST"));
s.setOsUsername(rs.getString("OS_USERNAME"));
s.setLoginPassword(rs.getString("LOGIN_PASSWD"));
s.setStatus(rs.getString("STATUS"));
s.setCreateDate(rs.getDate("CREATE_DATE"));
s.setPauseDate(rs.getDate("PAUSE_DATE"));
s.setCloseDate(rs.getDate("CLOSE_DATE"));
s.setCostId(rs.getInt("COST_ID"));
return s;
}
public Account findAccountByServiceId(int serviceId) throws DAOException {
String sql="select * from pan_ACCOUNT " +
"where ID in (" +
"select ACCOUNT_ID from pan_SERVICE " +
"where ID=? " +
")";
Connection conn=DBUtil.getConnection();
try {
PreparedStatement stat=conn.prepareStatement(sql);
stat.setInt(1, serviceId);
ResultSet rst=stat.executeQuery();
if(rst.next()){
Account a=createAccount(rst);
return a;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private Account createAccount(ResultSet rs) throws SQLException {
Account a = new Account();
a.setId(rs.getInt("ID"));
a.setRecommenderId(rs.getInt("RECOMMENDER_ID"));
a.setLoginName(rs.getString("LOGIN_NAME"));
a.setLoginPassword(rs.getString("LOGIN_PASSWD"));
a.setStatus(rs.getString("STATUS"));
a.setCreateDate(rs.getDate("CREATE_DATE"));
a.setPauseDate(rs.getDate("PAUSE_DATE"));
a.setCloseDate(rs.getDate("CLOSE_DATE"));
a.setRealName(rs.getString("REAL_NAME"));
a.setIdcardNo(rs.getString("IDCARD_NO"));
a.setBirthdate(rs.getDate("BIRTHDATE"));
a.setGender(rs.getString("GENDER"));
a.setOccupation(rs.getString("OCCUPATION"));
a.setTelephone(rs.getString("TELEPHONE"));
a.setEmail(rs.getString("EMAIL"));
a.setMailAddress(rs.getString("MAILADDRESS"));
a.setZipCode(rs.getString("ZIPCODE"));
a.setQq(rs.getString("QQ"));
a.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
a.setLastLoginIp(rs.getString("LAST_LOGIN_IP"));
return a;
}
public void deleteService(int id) throws DAOException {
String sql="update pan_service set status='2'," +
"close_date=sysdate where ID=?";
Connection conn=DBUtil.getConnection();
try {
PreparedStatement stat=conn.prepareStatement(sql);
stat.setInt(1, id);
stat.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void pauseService(int id) throws DAOException {
String sql="update pan_service set status='1'," +
"pause_date=sysdate where ID=?";
Connection conn=DBUtil.getConnection();
try {
PreparedStatement st
供java学习用，电信系统
需积分: 0 128 浏览量
2014-08-04
10:48:08
上传
评论
收藏 12.65MB RAR 举报
供java学习用，电信系统 （286个子文件） 
ServiceDAOImpl.class 11KB AccountDAOImpl.class 11KB RoleDAOImpl.class 7KB AdminDAOImpl.class 7KB CostDAOImpl.class 6KB Account.class 5KB PrivilegeReader.class 4KB ImageUtil.class 3KB FindAccountAction.class 3KB ServiceVO.class 3KB FindServiceAction.class 3KB LoginDAOImpl.class 3KB Service.class 3KB FindAdminAction.class 3KB cost.class 2KB LoginAction.class 2KB Admin.class 2KB DBUtil.class 2KB updateAccountAction.class 2KB toUpdateServiceAction.class 2KB updateServiceAction.class 2KB findRoleAction.class 2KB FindCostAction.class 2KB toUpdateAccountAction.class 2KB toUpdateRoleAction.class 2KB startServiceAction.class 2KB DAOFactory.class 2KB deleteServiceAction.class 2KB pauseServiceAction.class 2KB Admin.class 2KB searchAccountByPwdAction.class 2KB toDetailAccountAction.class 2KB ImageCodeAction.class 2KB ResetPasswordAction.class 2KB HibernateUtil.class 2KB toDetailCostAction.class 1KB searchAccountAction.class 1KB CheckRepeatAction.class 1KB detailServiceAction.class 1KB LoginInterceptor.class 1KB deleteAccountAction.class 1KB ToUpdateCostAction.class 1KB addAccountAction.class 1KB ToAddServiceAction.class 1KB PauseAccountAction.class 1KB StartAccountAction.class 1KB Role.class 1KB deleteRoleAction.class 1KB AddCostAction.class 1KB IServiceDAO.class 1KB Privilege.class 1KB AddServiceAction.class 1KB IAccountDAO.class 1KB UpdateCostAction.class 1KB updateRoleAction.class 1KB AddRoleAction.class 1KB DeleteCostAction.class 1012B toAddRoleAction.class 1002B serviceBak.class 943B OpenSessionInViewInterceptor.class 937B ICostDAO.class 740B BaseAction.class 681B IRoleDAO.class 544B IAdminDAO.class 522B DAOException.class 438B ILoginDAO.class 287B .classpath 2KB global.css 10KB global_color.css 7KB
wrong.gif 115B
hibernate3.jar 2.17MB xwork-core-2.1.6.jar 1.49MB ojdbc15.jar 1.47MB freemarker-2.3.15.jar 859KB freemarker.jar 784KB struts2-core-2.1.8.jar 738KB mysql-connector-java-5.1.8-bin.jar 707KB mysql-connector-java-5.1.7-bin.jar 693KB c3p0-0.9.1.jar 594KB antlr-2.7.6.jar 433KB standard.jar 384KB hibernate-tools.jar 356KB log4j-1.2.11.jar 342KB dom4j-1.6.1.jar 307KB cglib-2.1.3.jar 276KB jaxen-1.1-beta-6.jar 239KB ognl-2.7.3.jar 234KB commons-collections-2.1.1.jar 171KB junit-4.4.jar 158KB commons-fileupload-1.2.1.jar 56KB struts2-json-plugin-2.1.8.1.jar 50KB commons-logging-1.0.4.jar 37KB asm.jar 26KB jstl.jar 20KB asm-attrs.jar 16KB jta.jar 9KB ServiceDAOImpl.java 11KB AccountDAOImpl.java 10KB CostDAOImpl.java 8KB RoleDAOImpl.java 7KB共 286 条
- 1
- 2
- 3
评论0