查询的结果和条件中,包含多张表的数据,需要使用外键account_id,cost_id来关联查询出相关的数据。
创建VO对象,封装3张表的字段
代码:
--DAO
--Action
--struts.xml
DAO:
Action:
struts.xml:
Jsp:
创建VO对象,封装3张表的字段
代码:
--DAO
--Action
--struts.xml
--JSP
VO:
public class ServiceVO extends Service{
private String idcardNo;
private String realName;
private String costName;
private String costDescr;
public String getIdcardNo() {
return idcardNo;
}
public void setIdcardNo(String idcardNo) {
this.idcardNo = idcardNo;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getCostName() {
return costName;
}
public void setCostName(String costName) {
this.costName = costName;
}
public String getCostDescr() {
return costDescr;
}
public void setCostDescr(String costDescr) {
this.costDescr = costDescr;
}
}
DAO:
public class ServiceDAOImpl implements IServiceDAO{
public List<ServiceVO> findByCondition(String osUserName, String unixHost,
String idcardNo, String status, Integer page, Integer pageSize)
throws DAOException {
//动态拼SQL
List<Object> params = new ArrayList<Object>();
String sql = "select * from ("+
" select s.*, rownum r,"+
" a.idcard_no, a.real_name,"+
" c.name, c.descr"+
" from service s"+
" join account a on s.account_id = a.id"+
" join cost c on s.cost_id = c.id"+
" where 1=1";
if(osUserName!=null && osUserName.length()>0){
sql += " and s.os_username=? ";
params.add(osUserName);
}
System.out.println("unix:"+unixHost);
if(unixHost!=null && unixHost.length()>0){
sql += " and s.unix_host=? ";
params.add(unixHost);
}
if(idcardNo!=null && idcardNo.length()>0 ){
sql += " and a.idcard_no=? ";
params.add(idcardNo);
}
if(status!=null && status.length()>0 && !status.equals("-1")){
sql += " and s.status=? ";
params.add(status);
}
sql += ") where r>? and r<?";
params.add(pageSize*(page-1));
params.add(pageSize*page+1);
System.out.println("sql:"+sql);
Connection con = DBUtil.getConnection();
List<ServiceVO> list = new ArrayList<ServiceVO>();
try {
PreparedStatement ps = con.prepareStatement(sql);
for(int i=0; i<params.size(); i++){
ps.setObject(i+1, params.get(i));
}
ResultSet rs = ps.executeQuery();
while(rs.next()){
ServiceVO vo = createServiceVO(rs);
list.add(vo);
}
} catch (SQLException e) {
e.printStackTrace();
throw new DAOException("查询业务账号失败", e);
} finally{
DBUtil.close();
}
return list;
}
private ServiceVO createServiceVO(ResultSet rs) throws SQLException{
ServiceVO vo = new ServiceVO();
vo.setId(rs.getInt("id"));
vo.setAccountId(rs.getInt("account_id"));
vo.setUnixHost(rs.getString("unix_host"));
vo.setOsUserName(rs.getString("os_username"));
vo.setLoginPassword(rs.getString("login_passwd"));
vo.setStatus(rs.getString("status"));
vo.setCreateDate(rs.getDate("create_date"));
vo.setPauseDate(rs.getDate("pause_date"));
vo.setCloseDate(rs.getDate("close_date"));
vo.setCostId(rs.getInt("cost_id"));
vo.setIdcardNo(rs.getString("idcard_no"));
vo.setRealName(rs.getString("real_name"));
vo.setCostName(rs.getString("name"));
vo.setCostDescr(rs.getString("descr"));
return vo;
}
public Integer findTotalPage(String osUserName, String unixHost,
String idcardNo, String status, Integer pageSize)
throws DAOException {
List<Object> params = new ArrayList<Object>();
String sql = "select count(*) "+
"from service s "+
" join account a on s.account_id = a.id "+
" join cost c on s.cost_id = c.id "+
" where 1=1 ";
if(osUserName!=null && osUserName.length()>0){
sql += " and s.os_userName=? ";
params.add(osUserName);
}
if(unixHost!=null && unixHost.length()>0){
sql += " and s.unix_host=? ";
params.add(unixHost);
}
if(idcardNo!=null && idcardNo.length()>0){
sql += " and a.idcard_no=? ";
params.add(idcardNo);
}
if(status!=null && status.length()>0 && !status.equals("-1")){
sql += " and s.status=? ";
params.add(status);
}
Connection con = DBUtil.getConnection();
try {
PreparedStatement ps = con.prepareStatement(sql);
for(int i=0; i<params.size(); i++){
ps.setObject(i+1, params.get(i));
}
ResultSet rs = ps.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);
} finally{
DBUtil.close();
}
return 0;
}
}
Action:
public class FindServiceAction {
public String execute(){
IServiceDAO dao = DAOFactory.getServiceDAO();
try {
serviceVOs = dao.findByCondition(osUserName, unixHost, idcardNo, status, page, pageSize);
totalPage = dao.findTotalPage(osUserName, unixHost, idcardNo, status, pageSize);
} catch (DAOException e) {
e.printStackTrace();
return "error";
}
return "success";
}
public String getOsUserName() {
return osUserName;
}
public void setOsUserName(String osUserName) {
this.osUserName = osUserName;
}
public String getUnixHost() {
return unixHost;
}
public void setUnixHost(String unixHost) {
this.unixHost = unixHost;
}
public String getIdcardNo() {
return idcardNo;
}
public void setIdcardNo(String idcardNo) {
this.idcardNo = idcardNo;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalPage() {
return totalPage;
}
public void setTotalPage(Integer totalPage) {
this.totalPage = totalPage;
}
public List<ServiceVO> getServiceVOs() {
return serviceVOs;
}
public void setServiceVOs(List<ServiceVO> serviceVOs) {
this.serviceVOs = serviceVOs;
}
//input
private String osUserName;
private String unixHost;
private String idcardNo;
private String status;
private Integer page = 1;
private Integer pageSize;
//output
private Integer totalPage;
private List<ServiceVO> serviceVOs;
}
struts.xml:
<!-- 业务账号配置 -->
<package name="service" extends="netctoss" namespace="/service">
<!-- 查询业务账号Action -->
<action name="findService" class="netctoss.action.service.FindServiceAction">
<param name="pageSize">5</param>
<result name="success">
/WEB-INF/service/findService.jsp
</result>
</action>
</package>
Jsp:
<s:hidden name="page" id="page"/>
<div id="pages">
<a href="javascript:toPage(1);">首页</a>
<s:if test="page==1">
<a href="#">上一页</a>
</s:if>
<s:else>
<a href="javascript:toPage(<s:property value="page-1"/>);">上一页</a>
</s:else>
<s:iterator begin="1" end="totalPage" var="p">
<s:if test="page==#p">
<a href="javascript:toPage(<s:property value="#p"/>);" class="current_page"><s:property value="#p"/></a>
</s:if>
<s:else>
<a href="javascript:toPage(<s:property value="#p"/>);"><s:property value="#p"/></a>
</s:else>
</s:iterator>
<s:if test="page==totalPage">
<a href="#">下一页</a>
</s:if>
<s:else>
<a href="javascript:toPage(<s:property value="page+1"/>);">下一页</a>
</s:else>
<a href="javascript:toPage(<s:property value="totalPage"/>);">末页</a>
</div>
<!--javascript-->
function toPage(currPage){
document.getElementById("page").value = currPage;
document.forms[0].submit();
}