润乾报表模板里实现动态搜索提示（类似百度提示）

来源：互联网发布：jquery.artdialog.js 编辑：程序博客网时间：2024/06/07 06:30

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. 输入关键字,会自动过滤掉不想要的信息.

5. 基本实现原理
1.从数据库表提取站内搜索关键字段的信息
2.在参数模板里定义文本框为HTML类型,定义DIV层
3.利用雅虎的javascript搜索及CSS框架,在此基础上对数据进行二次封装
（关于框架可在网上搜索

6. 关键性程序说明
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>

<!–

–>

#statesautocomplete,#statesautocomplete2 {

width10em

padding-bottom1.5em;

}

#statesautocomplete {

z-index9000;

}

#statesinput,#statesinput2 {

_positionabsolute

} </head>

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” />

<tr>

<td>

//如果参数模板存在，则显示参数模板

if (f.exists()) {

<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>

}

<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>

//设置分页显示值

document.getElementById( “t_page_span” ).innerHTML=report1_getTotalPage();

document.getElementById( “c_page_span” ).innerHTML=report1_getCurrPage();

</script>

ReportFunDAO funcBar = new ReportFunDAO();

</table>

</body>

//加载数据库数据

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 = newYAHOO.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 = newYAHOO.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,。

润乾 报表模板里实现动态搜索提示（类似百度提示）

润乾报表模板里实现动态搜索提示（类似百度提示）