本次涉及到的知识点：
hql和sql语句的投影查询
（1）如果投影查询是一个字段，此时返回List<Object>，例如 String hql = "SELECT DISTINCT o.keyword FROM ElecSystemDDL o"; List<Object> list = this.getHibernateTemplate().find(hql); （2）如果投影查询是多个字段，此时返回List<Object[]>，例如 String hql = "SELECT DISTINCT o.keyword,o.ddlName FROM ElecSystemDDL o"; List<Object[]> list = this.getHibernateTemplate().find(hql); （3）如果投影查询是多个字段，此时返回List<Object[]>，例如 String hql = "SELECT o,o.ddlName FROM ElecSystemDDL o"; List<Object[]> list = this.getHibernateTemplate().find(hql); 数组的第一个值，是一个ElecSystemDDL的对象，数组的第二个值表示字段ddlName的值。 （4）如果投影查询是一个对象，此时返回List<ElecSystemDDL>，例如 String hql = "SELECT o FROM ElecSystemDDL o"; List<ElecSystemDDL> list = this.getHibernateTemplate().find(hql); （5）如果是hql语句，使用hql语句直接将投影查询的字段放置到对象中，例如 String hql = "SELECT DISTINCT new cn.itcast.elec.domain.ElecSystemDDL(o.keyword) FROM ElecSystemDDL o"; List<ElecSystemDDL> list = this.getHibernateTemplate().find(hql);
当使用hql语句直接将投影查询的字段放入对象中，需要在ElecSystemDDL类中增加对应的构造方法：
public ElecSystemDDL(){ } public ElecSystemDDL(String keyword){ this.keyword = keyword; }
同时，对应的ElecSystemDDLDaoImpl.java中的查询应该改为：
@Override public List<ElecSystemDDL> findSystemDDLListByDistinct() { List<ElecSystemDDL> systemList = new ArrayList<ElecSystemDDL>(); /*String hql = "SELECT DISTINCT o.keyword from ElecSystemDDL o"; //hql语句 from ElecSystemDDL对象 List<Object> list = this.getHibernateTemplate().find(hql); if(list != null && list.size() > 0 ){ for(Object o : list){ ElecSystemDDL elecSystemDDL = new ElecSystemDDL(); elecSystemDDL.setKeyword(o.toString()); systemList.add(elecSystemDDL); } }*/ //方法二： String hql = "SELECT DISTINCT new com.elec.domain.ElecSystemDDL(o.keyword) from ElecSystemDDL o"; systemList = this.getHibernateTemplate().find(hql); return systemList; }
*1.查询数据库已有类型，返回List<ElecSystemDDL>集合，遍历到页面的下拉菜单中；
select DISTINCT o.keyword from elec_systemddl o;
Hql语句中，是从对象中获取数据：String hql = "SELECT DISTINCT o.keyword from ElecSystemDDL o";
返回的是Object类型
Action中：ElecSystemDDLAction.java
/** * */ package com.elec.web.action; import java.util.List; import javax.annotation.Resource; import org.apache.struts2.ServletActionContext; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Controller; import com.elec.domain.ElecCommonMsg; import com.elec.domain.ElecSystemDDL; import com.elec.service.IElecCommonMsgService; import com.elec.service.IElecSystemDDLService; import com.elec.service.IElecTextService; import com.elec.web.utils.ValueUtils; import com.opensymphony.xwork2.ModelDriven; /** * @author kj * */ @SuppressWarnings("serial") @Controller("elecSystemDDLAction") @Scope(value="prototype") public class ElecSystemDDLAction extends BaseAction<ElecSystemDDL> { ElecSystemDDL elecSystemDDL = this.getModel(); @Resource(name=IElecSystemDDLService.SERVICE_NAME) IElecSystemDDLService elecSystemDDLService; /** * @Name * @Description: 数据字典首页 * @author kj * @version: * @create Date: * @return String 跳转到dictionaryIndex.jsp */ public String home(){ // 跳转到：/system/dictionIndex.jsp页面 List<ElecSystemDDL> list = elecSystemDDLService.findSystemDDLListByDistinct(); request.setAttribute("list", list); return "home"; } }
ElecSystemDDLServiceImpl.java
package com.elec.service.impl; import java.util.List; import javax.annotation.Resource; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.elec.dao.IElecSystemDDLDao; import com.elec.domain.ElecSystemDDL; import com.elec.service.IElecSystemDDLService; @Service(IElecSystemDDLService.SERVICE_NAME) @Transactional(readOnly=false) public class ElecSystemDDLServiceImpl implements IElecSystemDDLService { //数据字典Dao @Resource(name=IElecSystemDDLDao.SERVICE_NAME) IElecSystemDDLDao elecSystemDDLDao; @Override public List<ElecSystemDDL> findSystemDDLListByDistinct() { List<ElecSystemDDL> list = elecSystemDDLDao.findSystemDDLListByDistinct(); return list; } }
IElecSystemDDLDao.java
package com.elec.dao; import java.util.List; import com.elec.domain.ElecSystemDDL; public interface IElecSystemDDLDao extends ICommonDao<ElecSystemDDL>{ public static final String SERVICE_NAME = "com.elec.dao.ElecSystemDDLDaoImpl"; List<ElecSystemDDL> findSystemDDLListByDistinct(); }
ElecSystemDDLDaoImpl.java
此处使用的是HQL语句，查询的是对象，返回Object类型
package com.elec.dao.impl; import java.io.Serializable; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.springframework.stereotype.Repository; import com.elec.dao.IElecSystemDDLDao; import com.elec.domain.ElecSystemDDL; @Repository(IElecSystemDDLDao.SERVICE_NAME) public class ElecSystemDDLDaoImpl extends CommonDaoImpl<ElecSystemDDL> implements IElecSystemDDLDao { /** * 查询数据字典，去掉重复内容； */ @Override public List<ElecSystemDDL> findSystemDDLListByDistinct() { List<ElecSystemDDL> systemList = new ArrayList<ElecSystemDDL>(); String hql = "SELECT DISTINCT o.keyword from ElecSystemDDL o"; //hql语句 from ElecSystemDDL对象 List<Object> list = this.getHibernateTemplate().find(hql); if(list != null && list.size() > 0 ){ for(Object o : list){ ElecSystemDDL elecSystemDDL = new ElecSystemDDL(); elecSystemDDL.setKeyword(o.toString()); systemList.add(elecSystemDDL); } } return systemList; } }
然后先对应的页面：
页面中显示数据有两种方法：
第一种：
<select name="keyword" class="bg" style="180px" onchange="changetype()"> <option value="jerrynew"></option> <s:iterator value="#request.list" var="sys"> <option value="<s:property value="#sys.keyword" />"> <s:property value="keyword"/> </option> </s:iterator> </select>
第二种：
<s:select list="#request.list" id="keyword" name="keyword" listKey="keyword" listValue="keyword" headerKey="jerrynew" headerValue="" cssClass="bg" cssStyle="180px" onchange="changetype()"> </s:select>
dictionaryIndex.jsp
<%@ page language="java" pageEncoding="UTF-8"%> <%@ taglib uri="/struts-tags" prefix="s" %> <HTML> <HEAD> <title>系统设置</title> <LINK href="${pageContext.request.contextPath }/css/Style.css" type="text/css" rel="stylesheet"> <script language="javascript" src="${pageContext.request.contextPath }/script/function.js"></script> <script type="text/javascript" src="${pageContext.request.contextPath }/script/pub.js"></script> <script type="text/javascript" src="${pageContext.request.contextPath }/script/jquery-1.4.2.js"></script> <script language="javascript"> function changetype(){ if(document.Form1.keyword.value=="jerrynew"){ var textStr="<input type="text" name="keywordname" maxlength="50" size="24"> "; document.getElementById("newtypename").innerHTML="类型名称："; document.getElementById("newddlText").innerHTML=textStr; Pub.submitActionWithForm('Form2','${pageContext.request.contextPath }/system/dictionaryEdit.jsp','Form1'); }else{ var textStr=""; document.getElementById("newtypename").innerHTML=""; document.getElementById("newddlText").innerHTML=textStr; Pub.submitActionWithForm('Form2','${pageContext.request.contextPath }/system/dictionaryEdit.jsp','Form1'); } } function saveDict(){ if(document.Form1.keyword.value=="jerrynew"){ if(Trim(document.Form1.keywordname.value)==""){ alert("请输入类型名称"); return false; } var allkeywords= document.Form1.keyword; for(var i=0;i<allkeywords.length;i++){ if(allkeywords[i].value==Trim(document.Form1.keywordname.value)){ alert("已存在此类型名称,请重新输入"); return false; } } document.Form2.keywordname.value=document.Form1.keywordname.value; document.Form2.typeflag.value="new"; }else{ document.Form2.keywordname.value=document.Form1.keyword.value; document.Form2.typeflag.value="add"; } var tbl=document.getElementById("dictTbl"); for (i=1;i<tbl.rows.length;i++){ var name = tbl.rows[i].cells[1].getElementsByTagName("input")[0].value; if(Trim(name)==""){ alert("名称不能为空！"); return false; } } for(k=1;k<=tbl.rows.length-2;k++) { for(m=k+1;m<=tbl.rows.length-1;m++) { var name1 = tbl.rows[k].cells[1].getElementsByTagName("input")[0].value; var name2 = tbl.rows[m].cells[1].getElementsByTagName("input")[0].value; if(name1 == name2){ alert("名称不能相同！"); return false; } } } document.Form2.action="savedict.do"; document.Form2.submit(); } /** function insertRows(){ var tempRow=0; var tbl=document.getElementById("dictTbl"); tempRow=tbl.rows.length; var Rows=tbl.rows;//类似数组的Rows var newRow=tbl.insertRow(tbl.rows.length);//插入新的一行 var Cells=newRow.cells;//类似数组的Cells for (i=0;i<3;i++)//每行的3列数据 { var newCell=Rows[newRow.rowIndex].insertCell(Cells.length); newCell.align="center"; switch (i) { case 0 : newCell.innerHTML=""+tempRow+"";break; case 1 : newCell.innerHTML="<input name="itemname" type="text" id=""+tempRow+"" size="45" maxlength=25>";break; case 2 : newCell.innerHTML="<a href='javascript:delTableRow(""+tempRow+"")'><img src=${pageContext.request.contextPath }/images/delete.gif width=15 height=14 border=0 style=CURSOR:hand></a>";break; } //alert(newCell.innerHTML); } } function delTableRow(rowNum){ var tbl=document.getElementById("dictTbl"); if (tbl.rows.length >rowNum){ tbl.deleteRow(rowNum); for (i=rowNum;i<tbl.rows.length;i++) { tbl.rows[i].cells[0].innerHTML=i; tbl.rows[i].cells[2].innerHTML="<a href='javascript:delTableRow(""+i+"")'><img src=${pageContext.request.contextPath }/images/delete.gif width=15 height=14 border=0 style=CURSOR:hand></a>"; tbl.rows[i].cells[1].childNodes[0].id=i; } } } */ function insertRows(){ //获取表格对象 var tb1 = $("#dictTbl"); var tempRow = $("#dictTbl tr").size();//获取表格的行数 var $tdNum = $("<td align='center'></td>"); $tdNum.html(tempRow); var $tdName = $("<td align='center'></td>"); $tdName.html("<input name="itemname" type="text" id=""+tempRow+"" size="45" maxlength=25>"); var $tdDel = $("<td align='center'></td>"); $tdDel.html("<a href='javascript:delTableRow(""+tempRow+"")'><img src=${pageContext.request.contextPath }/images/delete.gif width=15 height=14 border=0 style=CURSOR:hand></a>"); // 创建tr，将3个td放置到tr中 var $tr = $("<tr></tr>"); $tr.append($tdNum); $tr.append($tdName); $tr.append($tdDel); //在表格的最后追加新增的tr tb1.append($tr); } function delTableRow(rowNum){ //改变行号和删除的行号 var tb1 = $("#dictTbl"); var tempRow = $("#dictTbl tr").size();//获取表格的行数 if (tempRow >rowNum){ //获取删除行的id指定的对象，例如：<input name="itemname" type="text" id=""+tempRow+"" size="45" maxlength=25> $("#"+rowNum).parent().parent().remove(); //加1表示寻找下一个id，目的是将后面tr的格式向上移动 for (i=(parseInt(rowNum)+1);i<tempRow;i++){ //将i-1的值赋值给编号 $("#"+i).parent().prev().html(i-1); //将i-1的值赋值给超链接的删除 $("#"+i).parent().next().html("<a href='javascript:delTableRow(""+(i-1)+"")'><img src=${pageContext.request.contextPath }/images/delete.gif width=15 height=14 border=0 style=CURSOR:hand></a>");// //将i-1的值赋值给文本框的id，用于删除 $("#"+i).attr("id",(i-1));//将id设置成i-1 } } } function returnMethod(){ return saveDict(); } </script> </HEAD> <body> <Form name="Form1" id="Form1" method="post" style="margin:0px;"> <table cellSpacing="1" cellPadding="0" width="90%" align="center" bgColor="#f5fafe" border="0"> <TBODY> <tr> <td class="ta_01" colspan=3 align="center" background="${pageContext.request.contextPath }/images/b-info.gif"> <font face="宋体" size="2"><strong>数据字典维护</strong></font> </td> </tr> <TR height=10><td colspan=3></td></TR> <tr> <td class="ta_01" align="right" width="35%" >类型列表：</td> <td class="ta_01" align="left" width="30%" > <!-- 写法二 --> <s:select list="#request.list" id="keyword" name="keyword" listKey="keyword" listValue="keyword" headerKey="jerrynew" headerValue="" cssClass="bg" cssStyle="180px" onchange="changetype()"> </s:select> <%-- <!-- 写法一 --> <select name="keyword" class="bg" style="180px" onchange="changetype()"> <option value="jerrynew"></option> <s:iterator value="#request.list" var="sys"> <option value="<s:property value="#sys.keyword" />"> <s:property value="keyword"/> </option> </s:iterator> </select> --%> </td> <td class="ta_01" align="right" width="35%" > </td> </tr> <tr> <td class="ta_01" align="right" width="35%" id="newtypename">类型名称：</td> <td class="ta_01" align="left" width="30%" height=20 id="newddlText"> <input type="text" name="keywordname" maxlength="25" size=24> </td> <td class="ta_01" align="right" width="35%" ></td> </tr> <TR height=10><td colspan=3 align="right"> <input type="button" name="saveitem" value="添加选项" style="font-size:12px; color:black; height=20;width=80" onClick="insertRows()"> </td></TR> </TBODY> </table> </Form> <Form name="Form2" id="Form2" method="post" style="margin:0px;"> <table cellSpacing="1" cellPadding="0" width="90%" align="center" bgColor="#f5fafe" border="0" > <tr> <td > <table cellspacing="0" cellpadding="1" rules="all" bordercolor="gray" border="1" id="dictTbl" style="BORDER-RIGHT:gray 1px solid; BORDER-TOP:gray 1px solid; BORDER-LEFT:gray 1px solid; WIDTH:100%; WORD-BREAK:break-all; BORDER-BOTTOM:gray 1px solid; BORDER-COLLAPSE:collapse; BACKGROUND-COLOR:#f5fafe; WORD-WRAP:break-word"> <tr style="FONT-WEIGHT:bold;FONT-SIZE:12pt;HEIGHT:25px;BACKGROUND-COLOR:#afd1f3"> <td class="ta_01" align="center" width="20%" height=22 background="${pageContext.request.contextPath }/images/tablehead.jpg">编号</td> <td class="ta_01" align="center" width="60%" height=22 background="${pageContext.request.contextPath }/images/tablehead.jpg">名称</td> <td class="ta_01" align="center" width="20%" height=22 background="${pageContext.request.contextPath }/images/tablehead.jpg">删除</td> </tr> <tr> <td class="ta_01" align="center" width="20%">1</td> <td class="ta_01" align="center" width="60%"> <input name="itemname" type="text" size="45" maxlength="25"></td> <td class="ta_01" align="center" width="20%"></td> </tr> </table> </td> </tr> <tr> <td > </td> </tr> <TR height=10><td colspan=3></td></TR> <tr> <td align="center" colspan=3> <input type="button" name="saveitem" value="保存" style="font-size:12px; color:black; height=20;width=50" onClick="returnMethod()"> </td> </tr> <input type="hidden" name="keywordname" > <input type="hidden" name="typeflag" > </table> </Form> </body> </HTML>
然后再数据库中插入对应的数据：
页面上的显示效果：
