报表模板里实现动态搜索提示（类似百度提示）
第29章 报表模板里实现动态搜索提示（类似百度提示）
1. 问题概述
客户想实现一个类似站内搜索的功能,要求在参数模板查询的文本框里,根据输入的关键字,能够实现基于动态搜索提示,类似百度,google那种,默认取排名前10条信息,支持键盘下移回车等操作,数据来源为数据库表的关键字段.经过一番功夫的整理,详见实例演示.
2. 实例演示一
1.数据的来源以数据库EMPLOYEE表为例,提取所有EMPNAME信息:
2.点击参数模板文本框,自动从数据库提取前10条信息,作为参数查询条件
3.输入关键字,会自动过滤掉不想要的信息.
4. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息.
5.选择要查询的参数,根据具体参数值查询出相应的结果.
5. 参数模板的设计：
3. 实例演示二
1.在系统站内实现,数据的来源以数据库EMPLOYEE表为例,提取所有EMPNAME信息:
2. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息. 带结果提示
3. 输入关键字,会自动过滤掉不想要的信息.带结果提示
4. 实例演示三
1.在系统站内实现,数据的来源以数据库EMPLOYEE表为例,提取所有EMPNAME信息:
2. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息.
3. 输入关键字,会自动过滤掉不想要的信息.
4. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息.
5. 输入关键字,会自动过滤掉不想要的信息.
5. 基本实现原理
1.从数据库表提取站内搜索关键字段的信息
2.在参数模板里定义文本框为HTML类型,定义DIV层
3.利用雅虎的javascript搜索及CSS框架,在此基础上对数据进行二次封装
（关于框架可在网上搜索
5. 关键性程序说明
1.执行数据库管理（打开关闭连接,执行增删改查）,DBManager.java代码：
packageorg.xaccp.bar;
importjava.sql.CallableStatement;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
publicclassDBManager {
publicstaticfinalString DRIVERNAME= “com.newatlanta.jturbo.driver.Driver”;
publicstaticfinalString URL= “jdbc:JTurbo://127.0.0.1/DBA/charset=GBK”;
publicstaticfinalString UID= “sa”;
publicstaticfinalString PWD= “sa”;
privatestaticDBManager manager= newDBManager();
privateConnection conn= null;
publicDBManager() {
try{
openConnection();
} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException e) {
e.printStackTrace();
}
}
//单例模式
publicstaticDBManager getInstance() {
returnmanager;
}
//打开连接
publicvoidopenConnection() throwsClassNotFoundException, SQLException {
try{
Class.forName(DRIVERNAME);
conn= DriverManager.getConnection(URL, UID, PWD);
} catch(Exception e) {
e.printStackTrace();
}
}
//执行增删改
publicvoidexecuteUpdate(String strSql) throwsSQLException {
Statement stat = this.conn.createStatement();
stat.executeUpdate(strSql);
}
//执行查询
publicResultSet executeQuerry(String strSql) throwsSQLException {
Statement stat = this.conn.createStatement();
returnstat.executeQuery(strSql);
}
//关闭连接
publicvoidcloseConnection() throwsSQLException {
if(this.conn== null)
return;
if(!(this.conn.isClosed())) {
this.conn.close();
this.conn= null;
}
}
//自行存储过程
publicCallableStatement getCallableStatement(String proc)
throwsSQLException {
returnthis.conn.prepareCall(proc);
}
publicPreparedStatement getpreParedstatement(String strsql)
throwsSQLException {
PreparedStatement pstat = this.conn.prepareStatement(strsql);
returnpstat;
}
//返回结果集
publicResultSet getstatement(String strsql) throwsSQLException {
Statement stat = this.conn.createStatement();
ResultSet rs = stat.executeQuery(strsql);
returnrs;
}
}
2.执行SQL语句,提取查询结果,字符格式转换等,ReportFunDAO.java代码：
packageorg.xaccp.bar;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
/**
*查询关键字信息
*
*@param可自行加入一些条件
*@returnList
*/
publicclassReportFunDAO {
// 查询所有记录
publicstaticString resResult() throwsSQLException,
ClassNotFoundException {
List<String> areaList = newArrayList<String>();
String str = “”;
String sql = “SELECT EMPNAME FROM EMPLOYEE”;
DBManager dbm = DBManager.getInstance();
dbm.openConnection();
ResultSet rs = dbm.executeQuerry(sql);
while(rs.next()) {
String strResult = rs.getString(1).trim();
areaList.add(strResult);
}
str = stringTransfer(areaList);
dbm.closeConnection();
returnstr;
}
// 根据Name,ID查询top 10记录
publicstaticString resNameResult(String strInit) throwsSQLException,
ClassNotFoundException {
strInit = strInit == null? “”: strInit;
Map<String, String> hashMap = newHashMap<String, String>();
String str = “”;
String sql = “SELECT top 10 EMPNAME,EMPID FROM EMPLOYEE WHERE EMPNAME LIKE ‘%”
+ strInit + “%’”;
System.out.println(“SQL语句:”+ sql);
DBManager dbm = DBManager.getInstance();
dbm.openConnection();
ResultSet rs = dbm.executeQuerry(sql);
booleanis = false;// 判断行数是否为空
while(rs.next()) {
is = true;
String strResult1 = rs.getString(1).trim();
String strResult2 = rs.getString(2).trim();
hashMap.put(strResult1, strResult2);
}
str = stringTransfer2(hashMap, strInit, is);
dbm.closeConnection();
returnstr;
}
// 根据Name查询top 10记录
publicstaticString resNameResult2(String strInit) throwsSQLException,
ClassNotFoundException {
strInit = strInit == null? “”: strInit;
String str = “”;
List<String> areaList = newArrayList<String>();
String sql = “SELECT top 10 EMPNAME FROM EMPLOYEE WHERE EMPNAME LIKE ‘%”
+ strInit + “%’”;
System.out.println(“SQL语句:”+ sql);
DBManager dbm = DBManager.getInstance();
dbm.openConnection();
ResultSet rs = dbm.executeQuerry(sql);
booleanis = false;// 判断行数是否为空
while(rs.next()) {
is = true;
String strResult = rs.getString(1).trim();
areaList.add(strResult);
}
str = stringTransfer3(areaList, strInit, is);
System.out.println(“str:”+ str);
dbm.closeConnection();
returnstr;
}
// d1,d2,d3 –> ‘d1′,’d2′,’d3′
publicstaticString stringTransfer(List<String> strInit) {
String strResult = “”;
for(inti = 0; i < strInit.size(); i++) {
strResult += “‘”+ strInit.get(i) + “‘,”;
}
strResult = strResult.substring(0, strResult.length() – 1);
returnstrResult;
}
// 格式:compResp(“1234″, new Array(“1234歌“), new Array(“93 结果“));
publicstaticString stringTransfer2(Map<String, String> params,
String strInit, booleanis) {
String s = null;
if(is) {
Set<?> entrySet = params.entrySet();
StringBuffer str = newStringBuffer();
List<String> sqls = newArrayList<String>();
str.append(“compResp(‘”+ strInit + “‘, new Array(“);
for(Iterator<?> ite = entrySet.iterator(); ite.hasNext();) {
Entry<?, ?> entry = (Entry<?, ?>) ite.next();
Object objectKey = entry.getKey().toString();
Object objectValue = entry.getValue();
sqls.add(objectValue.toString());
str.append(“‘”+ objectKey + “‘”).append(“,”);
}
str.deleteCharAt(str.length() – 1);
str.append(“), new Array(“);
for(String ss : sqls) {
str.append(“‘”+ ss + “ 结果‘”).append(“,”);
}
s = str.toString();
if(s.endsWith(“,”))
s = s.substring(0, s.length() - “,”.length());
s += “))”;
} else{
StringBuffer str = newStringBuffer();
str.append(“compResp(‘”+ strInit
+ “‘, new Array(‘NO’), new Array(‘NO’))”);
s = str.toString();
}
returns;
}
// 格式:”window.sogou.sug(['2',['2144小游戏','2011高考作文','2011高考','2011高考语文试卷','2020','2011高考作文题','2345','2011高考试卷','2011高考语文','2144']],-1);
publicstaticString stringTransfer3(List<String> strInit, String s,
booleanis) {
String $s = null;
if(is) {
String strResult = “”;
StringBuffer str = newStringBuffer();
str.append(“window.sogou.sug(['").append(s).append("',[");
for(inti = 0; i < strInit.size(); i++) {
strResult += "'"+ strInit.get(i) + "',";
}
strResult = strResult.substring(0, strResult.length() - 1);
str.append(strResult).append("]],-1);”);
$s = str.toString();
} else{
StringBuffer str = newStringBuffer();
str.append(“window.sogou.sug(['").append(s).append("',['']],-1);”);
$s = str.toString();
}
return$s;
}
publicstaticvoidmain(String[] args) throwsException {
// String fBtnLst = resResult();
String s = resNameResult2(“小“);
System.out.println(“html:”+ s);
}
}
3.展示加载数据JS等, showReport2.jsp代码：
<%@ page contentType=“text/html;charset=GBK”%>
<%@ taglib uri=“/WEB-INF/runqianReport4.tld” prefix=“report”%>
<%@ page import=“java.io.*”%>
<%@ page import=“java.util.*”%>
<%@ page import=“com.runqian.report4.usermodel.Context”%>
<%@page import=“org.xaccp.bar.ReportFunDAO;”%>
<html>
<head>
<title>全文搜索</title>
<!–
<link rel=”stylesheet” type=”text/css” href=”<%=request.getContextPath()%>/css/fonts-min.css” />
<link rel=”stylesheet” type=”text/css” href=”<%=request.getContextPath()%>/css/autocomplete.css” />
–>
<script type=“text/javascript” src=“<%=request.getContextPath()%>/js/yahoo-dom-event.js“></script>
<script type=“text/javascript” src=“<%=request.getContextPath()%>/js/animation.js“></script>
<script type=“text/javascript” src=“<%=request.getContextPath()%>/js/autocomplete.js“></script>
<style type=“text/css”>
#statesautocomplete,#statesautocomplete2{
width: 10em;
padding-bottom: 1.5em;
}
#statesautocomplete{
z-index: 9000;
}
#statesinput,#statesinput2{
_position: absolute;
</head>
<body topmargin=0 leftmargin=0 rightmargin=0 bottomMargin=0 class=“yui-skin-sam”>
<%
request.setCharacterEncoding(“GBK”);
String report = request.getParameter(“raq”);
String reportFileHome = Context.getInitCtx().getMainDir();
StringBuffer param = newStringBuffer();
//保证报表名称的完整性
intiTmp = 0;
if((iTmp = report.lastIndexOf(“.raq”)) <= 0) {
report = report + “.raq”;
iTmp = 0;
}
Enumeration paramNames = request.getParameterNames();
if(paramNames != null) {
while(paramNames.hasMoreElements()) {
String paramName = (String) paramNames.nextElement();
String paramValue = request.getParameter(paramName);
if(paramValue != null) {
//把参数拼成name=value;name2=value2;…..的形式
param.append(paramName).append(“=”).append(paramValue)
.append(“;”);
}
}
}
//以下代码是检测这个报表是否有相应的参数模板
String paramFile = report.substring(0, iTmp) + “_arg.raq”;
File f = newFile(application.getRealPath(reportFileHome
+ File.separator + paramFile));
%>
<jsp:include page=“toolbar.jsp” flush=“false” />
<table id=rpt align=center>
<tr>
<td>
<%
//如果参数模板存在，则显示参数模板
if(f.exists()) {
%>
<table id=param_tbl>
<tr>
<td>
<report:param name=“form1″ paramFileName=“<%=paramFile%>“
needSubmit=“no” params=“<%=param.toString()%>/>
</td>
<td>
<a href=“javascript:_submit( form1 )”><img
src=“../images/query.jpg” border=no
style=“vertical-align: middle”>
</a>
</td>
</tr>
</table>
<%
}
%>
<table align=center>
<tr>
<td>
<report:html name=“report1″ reportFileName=“<%=report%>“
funcBarLocation=“” needPageMark=“yes” generateParamForm=“no”
needLinkStyle=“yes” params=“<%=param.toString()%>width=“-1″
exceptionPage=“/reportJsp/myError2.jsp” />
</td>
</tr>
</table>
<script language=“javascript”>
//设置分页显示值
document.getElementById( “t_page_span” ).innerHTML=report1_getTotalPage();
document.getElementById( “c_page_span” ).innerHTML=report1_getCurrPage();
</script>
<%
ReportFunDAO funcBar = newReportFunDAO();
%>
</table>
</body>
<script type=“text/javascript”>
//加载数据库数据
YAHOO.example.statesArray = [<%out.print(funcBar.resResult());%>];
YAHOO.example.ACJSArray = new function() {
// Instantiate first JS Array DataSource
this.oACDS = new YAHOO.widget.DS_JSArray(YAHOO.example.statesArray);
// Instantiate first AutoComplete
this.oAutoComp = new YAHOO.widget.AutoComplete(‘key’,‘statescontainer’, this.oACDS);
this.oAutoComp.prehighlightClassName = “yui-ac-prehighlight”;
this.oAutoComp.typeAhead = true;
this.oAutoComp.useShadow = true;
this.oAutoComp.minQueryLength = 0;
this.oAutoComp.textboxFocusEvent.subscribe(function(){
var sInputValue = YAHOO.util.Dom.get(‘key’).value;
if(sInputValue.length === 0) {
var oSelf = this;
setTimeout(function(){oSelf.sendQuery(sInputValue);},0);
}
});
// Instantiate second JS Array DataSource
this.oACDS2 = new YAHOO.widget.DS_JSArray(YAHOO.example.areacodesArray);
// Instantiate second AutoComplete
this.oAutoComp2 = new YAHOO.widget.AutoComplete(‘statesinput2′,‘statescontainer2′, this.oACDS2);
this.oAutoComp2.prehighlightClassName = “yui-ac-prehighlight”;
this.oAutoComp2.typeAhead = true;
this.oAutoComp2.useShadow = true;
this.oAutoComp2.forceSelection = true;
this.oAutoComp2.formatResult = function(oResultItem, sQuery) {
var sMarkup = oResultItem[0] + ” (“ + oResultItem[1] + “)”;
return (sMarkup);
};
};
</script>
</html>
7.总结
JSP里引入的Js和Css,可详见附件内容。