汉字搜索效果图：
拼音首字母搜索效果图：
1）数据库表及函数(SQL Server 2008)
先来建立数据库表City，它包含两个字段CityID，CityName。
CREATE TABLE City ( CityID INT IDENTITY(1,1) Primary KEY , --城市主键 CityName NVARCHAR(50) NOT NULL, --城市名称 )
然后再插入一些示例数据
1 INSERT City(CityName) Values(‘北京市‘) 2 INSERT City(CityName) Values(‘天津市‘) 3 INSERT City(CityName) Values(‘上海市‘) 4 INSERT City(CityName) Values(‘重庆市‘) 5 INSERT City(CityName) Values(‘邯郸市‘) 6 INSERT City(CityName) Values(‘石家庄市‘) 7 INSERT City(CityName) Values(‘保定市‘) 8 INSERT City(CityName) Values(‘张家口市‘) 9 INSERT City(CityName) Values(‘承德市‘) 10 INSERT City(CityName) Values(‘唐山市‘) 11 //省略...
从表结构及示例数据来看，这里没有城市名称拼音首字母字段，那如何完成拼音搜索呢？不要着急，这得在数据库中建立一个函数，用来返回汉字的拼音首字母。
create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @strlen int,@re nvarchar(4000) declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) insert into @t(chr,letter) select ‘吖‘, ‘A ‘ union all select ‘八‘, ‘B ‘ union all select ‘嚓‘, ‘C ‘ union all select ‘咑‘, ‘D ‘ union all select ‘妸‘, ‘E ‘ union all select ‘发‘, ‘F ‘ union all select ‘旮‘, ‘G ‘ union all select ‘铪‘, ‘H ‘ union all select ‘丌‘, ‘J ‘ union all select ‘咔‘, ‘K ‘ union all select ‘垃‘, ‘L ‘ union all select ‘嘸‘, ‘M ‘ union all select ‘拏‘, ‘N ‘ union all select ‘噢‘, ‘O ‘ union all select ‘妑‘, ‘P ‘ union all select ‘七‘, ‘Q ‘ union all select ‘呥‘, ‘R ‘ union all select ‘仨‘, ‘S ‘ union all select ‘他‘, ‘T ‘ union all select ‘屲‘, ‘W ‘ union all select ‘夕‘, ‘X ‘ union all select ‘丫‘, ‘Y ‘ union all select ‘帀‘, ‘Z ‘ select @strlen=len(@str),@re= ‘ ‘ while @strlen> 0 begin select top 1 @re=letter+@re,@strlen=@strlen-1 from @t a where chr <=substring(@str,@strlen,1) order by chr desc if @@rowcount=0 select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 end return(@re) end
如果调用f_GetPy(‘北京‘)，则返回拼音首字母 ‘bj‘
2）前台页面
1 <!DOCTYPE html> 2 <html> 3 <head> 4 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 5 <link href="../js/jquery-ui/jquery-ui.min.css" rel="stylesheet" type="text/css"/> 6 <style> 7 .ui-autocomplete { 8 max-height: 100px; 9 overflow-y: auto; 10 /* prevent horizontal scrollbar */ 11 overflow-x: hidden; 12 } 13 /* IE 6 doesn‘t support max-height 14 * we use height instead, but this forces the menu to always be this tall 15 */ 16 * html .ui-autocomplete { 17 height: 100px; 18 } 19 </style> 20 <script type="text/javascript" src="../js/jquery-ui/jquery-1.9.0.min.js"></script> 21 <script type="text/javascript" src="../js/jquery-ui/jquery-ui.min.js"></script> 22 <script type="text/javascript"> 23 $( "#cityNameId" ).focus(function(){ 24 //智能提示 25 $( "#cityNameId" ).autocomplete({ 26 source:function(request,response){ 27 $.ajax({ 28 type:"POST", 29 url:"../cityManage/showTipsByCityName.action", 30 dataType:"json", 31 cache : false, 32 async: false, 33 data : { 34 "cityName": $("#cityNameId").val(), 35 }, 36 success:function(json){ 37 response($.map(json.autoSuggests,function(item){ 38 return { 39 label:item, 40 value:item 41 }; 42 })); 43 } 44 }); 45 } 46 }); 47 }); 48 </script> 49 </head> 50 <body> 51 城市名称： 52 <div class="ui-widget" style="display:inline"> 53 <input type ="text" id="cityNameId" name="cityName" > 54 </div> 55 </body> 56 </html>
cityName的值提交到服务器，作为搜索关键字。为了实现Ajax智能提示，需要用到JQuery UI AutoComplete插件，它要求返回JSON格式的数据。所以，下一步就是要在Action中返回JSON数据。
3)ACTION层
package com.dong.action; import java.util.ArrayList; import java.util.List; import org.apache.struts2.json.annotations.JSON; import com.dong.po.City; import com.dong.service.ICityService; import com.opensymphony.xwork2.ActionSupport; /** * 城市搜索Action * @version 1.0 2013/01/12 * @author dongliyang * */ public class CityAction extends ActionSupport{ /** SerialVersionUID*/ private static final long serialVersionUID = -8042695641942800870L; /** 城市Service */ private ICityService cityService; /** 搜索关键字，城市名称 */ private String cityName; /** 城市列表 */ private List<City> cityList; /** 智能提示列表，以JSON数据格式返回 */ private List<String> autoSuggests = new ArrayList<String>(); /** * 智能提示，自动补全功能 * @return String */ public String autoComplete(){ cityList = cityService.findByName(cityName); for(City city : cityList){ autoSuggests.add(city.getCityName()); } return SUCCESS; } public void setCityService(ICityService cityService) { this.cityService = cityService; } //搜索关键字不作为JSON数据返回，设置serialize=false @JSON(serialize=false) public String getCityName() { return cityName; } public void setCityName(String cityName) { this.cityName = cityName; } //搜索结果列表不作为JSON数据返回，设置serialize=false @JSON(serialize=false) public List<City> getCityList() { return cityList; } public void setCityList(List<City> cityList) { this.cityList = cityList; } //智能提示列表作为JSON数据返回，设置serialize=true @JSON(serialize=true) public List<String> getAutoSuggests() { return autoSuggests; } public void setAutoSuggests(List<String> autoSuggests) { this.autoSuggests = autoSuggests; } }
4）DAO层
1 package com.dong.dao.impl; 2 3 import java.util.List; 4 5 import com.dong.dao.ICityDao; 6 import com.dong.framework.BaseDao; 7 import com.dong.po.City; 8 9 public class CityDaoImpl extends BaseDao<City> implements ICityDao { 10 11 /** 12 * 根据名称或拼音搜索城市 13 * @param cityName 14 * @return List<City> 城市列表 15 */ 16 public List<City> findByName(String cityName){ 17 18 String hql = "from City c where c.cityName like ? or dbo.f_GetPy(c.cityName) like ?"; 19 return find(hql, "%" + cityName + "%",cityName + "%"); 20 21 } 22 23 }
hql语句中，使用cityName和f_GetPy(cityName) 来跟关键字进行like匹配。
比如：汉字搜索时，关键字"北京"传入方法，hql where子句中的c.cityName将能够匹配。
拼音搜索时，关键字"BJ"传入方法，hql where子句中的dbo.f_GetPy(c.cityName)将能够匹配。
5）struts.xml配置
<package name="cityManage" namespace="/cityManage" extends="json-default"> <action name="showTipsByCityName" class="cityAction" method="autoComplete"> <result name="success" type="json"></result> </action> </package>
参考资料：
1.文章来自：http://www.cnblogs.com/dongliyang/archive/2013/01/20/2868699.html
2.获取汉字拼音首字母的函数，来自：http://www.cnblogs.com/wuhuacong/archive/2010/01/25/1655916.html