报表模板巧妙实现动态搜索提示
问题背景
由于用户对百度或google搜索的使用习惯，想使用润乾报表实现一个类似站内搜索的功能,要求在参数模板查询的文本框里,根据输入的关键字,能够实现基于动态搜索提示,类似百度、google,默认取排名前10条信息,根据输入关键字自动提示、支持键盘下移回车等操作。
以下内容是以数据库表的关键字段为数据来源的实例场景。
实例演示一
1.数据的来源以数据库EMPLOYEE表为例,提取所有EMPNAME信息:
2.点击参数模板文本框,自动从数据库提取前10条信息,作为参数查询条件
3.输入关键字,会自动过滤掉不想要的信息.
4. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息.
5.选择要查询的参数,根据具体参数值查询出相应的结果.
5. 参数模板的设计：
实例演示二
1.在系统站内实现,数据的来源以数据库EMPLOYEE表为例,提取所有EMPNAME信息:
2. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息带结果提示
3. 输入关键字,会自动过滤掉不想要的信息.带结果提示
实例演示三
1.在系统站内实现,数据的来源以数据库EMPLOYEE表为例,提取所有EMPNAME信息:
2. 当输入的关键字,过滤的结果比较多的时候,默认提取排名前10条信息.
3. 输入关键字,会自动过滤掉不想要的信息.
基本思路
1.从数据库表提取站内搜索关键字段的信息
2.在参数模板里定义文本框为HTML类型,定义DIV层
3.利用雅虎的javascript搜索及CSS框架,在此基础上对数据进行二次封装
（关于框架可在网上搜索）
实现方式
关键性程序说明：
1.执行数据库管理（打开关闭连接,执行增删改查）,DBManager.java代码：
package org.xaccp.bar;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBManager {
public static final String DRIVERNAME = “com.newatlanta.jturbo.driver.Driver”;
public static final String URL = “jdbc:JTurbo://127.0.0.1/DBA/charset=GBK”;
public static final String UID = “sa”;
public static final String PWD = “sa”;
private static DBManager manager = new DBManager();
private Connection conn = null;
public DBManager() {
try {
openConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
//单例模式
public static DBManager getInstance() {
return manager;
}
//打开连接
public void openConnection() throws ClassNotFoundException, SQLException {
try {
Class.forName(DRIVERNAME);
conn = DriverManager.getConnection(URLUIDPWD);
} catch (Exception e) {
e.printStackTrace();
}
}
//执行增删改
public void executeUpdate(String strSql) throws SQLException {
Statement stat = this.conn.createStatement();
stat.executeUpdate(strSql);
}
//执行查询
public ResultSet executeQuerry(String strSql) throws SQLException {
Statement stat = this.conn.createStatement();
return stat.executeQuery(strSql);
}
//关闭连接
public void closeConnection() throws SQLException {
if (this.conn == null)
return;
if (!(this.conn.isClosed())) {
this.conn.close();
this.conn = null;
}
}
//自行存储过程
public CallableStatement getCallableStatement(String proc)
throws SQLException {
return this.conn.prepareCall(proc);
}
public PreparedStatement getpreParedstatement(String strsql)
throws SQLException {
PreparedStatement pstat = this.conn.prepareStatement(strsql);
return pstat;
}
//返回结果集
public ResultSet getstatement(String strsql) throws SQLException {
Statement stat = this.conn.createStatement();
ResultSet rs = stat.executeQuery(strsql);
return rs;
}
}
2.执行SQL语句,提取查询结果,字符格式转换等,ReportFunDAO.java代码：
package org.xaccp.bar;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 查询关键字信息
*
* @param 可自行加入一些条件
* @return List
*/
public class ReportFunDAO {
// 查询所有记录
public static String resResult() throws SQLException,
ClassNotFoundException {
List<String> areaList = new ArrayList<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();
return str;
}
// 根据Name,ID查询top 10记录
public static String resNameResult(String strInit) throws SQLException,
ClassNotFoundException {
strInit = strInit == null ? “” : strInit;
Map<String, String> hashMap = new HashMap<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);
boolean is = 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();
return str;
}
// 根据Name查询top 10记录
public static String resNameResult2(String strInit) throws SQLException,
ClassNotFoundException {
strInit = strInit == null ? “” : strInit;
String str = “”;
List<String> areaList = new ArrayList<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);
boolean is = 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();
return str;
}
// d1,d2,d3 –> ‘d1′,’d2′,’d3′
public static String stringTransfer(List<String> strInit) {
String strResult = “”;
for (int i = 0; i < strInit.size(); i++) {
strResult += “‘” + strInit.get(i) + “‘,”;
}
strResult = strResult.substring(0, strResult.length() – 1);
return strResult;
}
// 格式:compResp(“1234″, new Array(“1234歌“), new Array(“93 结果“));
public static String stringTransfer2(Map<String, String> params,
String strInit, boolean is) {
String s = null;
if (is) {
Set<?> entrySet = params.entrySet();
StringBuffer str = new StringBuffer();
List<String> sqls = new ArrayList<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 = new StringBuffer();
str.append(“compResp(‘” + strInit
+ “‘, new Array(‘NO’), new Array(‘NO’))”);
s = str.toString();
}
return s;
}
// 格式:”window.sogou.sug(['2',['2144小游戏','2011高考作文','2011高考','2011高考语文试卷','2020','2011高考作文题','2345','2011高考试卷','2011高考语文','2144']],-1);
public static String stringTransfer3(List<String> strInit, String s,
boolean is) {
String $s = null;
if (is) {
String strResult = “”;
StringBuffer str = new StringBuffer();
str.append(“window.sogou.sug(['").append(s).append("',[");
for (int i = 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 = new StringBuffer();
str.append(“window.sogou.sug(['").append(s).append("',['']],-1);”);
$s = str.toString();
}
return $s;
}
public static void main(String[] args) throws Exception {
// 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 {
width10em
padding-bottom1.5em;
}
#statesautocomplete {
z-index9000;
}
#statesinput,#statesinput2 {
_positionabsolute
} </head>
<body topmargin=leftmargin=rightmargin=bottomMargin=class=“yui-skin-sam”>
<%
request.setCharacterEncoding(“GBK”);
String report = request.getParameter(“raq”);
String reportFileHome = Context.getInitCtx().getMainDir();
StringBuffer param = new StringBuffer();
//保证报表名称的完整性
int iTmp = 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 = new File(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>
<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 = new ReportFunDAO();
%>
</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>
总结
集智数据平台内报表单元格提供的多种数据类型支持，帮助用户结合简单程序设计巧妙且轻松完成看似复杂，甚至不可能实现问题。