搜索文章

推荐内容

快逸做的更好

产品优势

您的位置: 首页 > 报表技术分享 > 报表模板里实现动态搜索提示(类似百度提示)

报表模板里实现动态搜索提示(类似百度提示)

第29章 报表模板里实现动态搜索提示(类似百度提示)

1. 问题概述
客户想实现一个类似站内搜索的功能,要求在参数模板查询的文本框里,根据输入的关键字,能够实现基于动态搜索提示,类似百度,google那种,默认取排名前10条信息,支持键盘下移回车等操作,数据来源为数据库表的关键字段.经过一番功夫的整理,详见实例演示.

2. 实例演示一
1.数据的来源以数据库EMPLOYEE表为例,提取所有EMPNAME信息:

2.点击参数模板文本框,自动从数据库提取前10条信息,作为参数查询条件


3.输入关键字,会自动过滤掉不想要的信息.


4. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息.

5.选择要查询的参数,根据具体参数值查询出相应的结果.

5. 参数模板的复杂报表设计" target="_blank" class="quieeLink1">设计:

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里引入的JsCss,可详见附件内容。