Web页面的仿plsql功能的实现：
界面如下：
实现界面代码：
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<html>
<head>
<%
com.nantian.ofpiwap.secure.Account loginAccount = com.nantian.ofpiwap.secure.SecurityContext.getLoginAccount();
String accountId=loginAccount.getAccountId();
boolean isSupervisor=com.nantian.ofpiwap.IWapContext.isSupervisor(accountId);
%>
<%@ include file="/screen/common/header.jsp"%>
<script type="text/javascript">
var _CONTEXT_PATH = '<%=request.getContextPath()%>';
var _SuperUser = '<%=isSupervisor%>';
</script>
<title>数据库查看</title>
<link href="<%=request.getContextPath()%>/css/tip-yellowsimple.css" rel="stylesheet" type="text/css"/>
<script type='text/javascript' src='<%=request.getContextPath()%>/dwr/interface/dbReadDao.js'></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery.poshytip.min.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/Tip.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/Page2.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/sysctrl/dbRead2.js"></script>
<style type="text/css">
.scrollbar{
overflow-x: auto;
overflow-y: auto;
width:100%;
border :1px solid #c1d7ff;
scrollbar-face-color:#DEDEDE;
scrollbar-base-color:#F5F5F5;
scrollbar-arrow-color:black;
scrollbar-track-color:#F5F5F5;
scrollbar-shadow-color:#EBF5FF;
scrollbar-highlight-color:#F5F5F5;
scrollbar-3dlight-color:#C3C3C3;
scrollbar-darkshadow-Color:#9D9D9D;
}
.selected{
cursor:hand;
padding:3px;
margin:3px;
background-color: #3399FF;
border-right:1px solid gray;
border-left:1px solid gray
}
.unselected{
cursor:hand;
padding:3px;
margin:3px;
background-color: #D1EEEE
}
</style>
</head>
<body class="body_css" bgcolor="#ffffff">
<div id="divPage1">
<div id="divPage2"><img src="<%=request.getContextPath()%>/erm/img/prcture.gif" /></div>
<div id="divPage3">
<a href="#">首页</a> >
<a href="#"> 风险监控系统</a> >
<a href="#"> 系统监控</a> >
<font class="gray"> 数据库查看</font></div>
</div>
<br/>
<textarea id="sqlText" rows="10" cols="100" style="font-family: Consolas;font-size: 15px;" οnkeyup="DbRead.ctrlenterEvent(event)"></textarea>
<input id="todo" type="button" value="查询" class="btn_width11113" οnclick="DbRead.toDo(1);" />
<input id="toupdate" type="button" value="更新" class="btn_width11113" οnclick="DbRead.toDo(0);" />
<br/>
<table width="450px" id='selectTable' border='0' cellpadding='0' cellspacing='0' style='margin-bottom: 3px'>
</table>
</body>
</html>
涉及到的两个Js代码分别如下：
/**
* <pre>
* 数据库查看
* </pre>
* @author ps
* @createTime 2013年1月18日 16:49:33
*/
var DbRead = function($){
var pageList = null;
var totalCount = 0;
var curIndex = 0;
var isPageClick = false;
function doQueryMulti(){
isPageClick = false;
curIndex = 0;
pageList = new Array();
var sqlList = new Array();
var sql = DbRead.SQL;
sqls = sql.split(";");
for ( var i = 0; i < sqls.length; i++) {
if($.trim(sqls[i]) == ""){
continue;
}
sqlList.push(sqls[i]);
}
dbReadDao.getDbDataMulti(sqlList,{
callback:function(result){
$("#todo").val("查询");
$("#todo").attr("disabled",false);
$("div[id^=selectDiv]").empty().remove();
$("#selectTable").empty();
if(result.length == 0){
return;
}
var selectTable = $("#selectTable");
var selectRow = $("<tr>");
selectTable.append(selectRow);
//遍历所有查询结果
for ( var k = 0; k < result.length; k++) {
var selectTd = $("<td>");
selectTd.attr("id","selectRow"+k);
selectTd.attr("title",sqls[k]);
selectTd.attr("width","10%");
selectTd.append("<span>"+k+"</span>");
selectTd.addClass("unselected");
//点击事件时，显示点击的div
selectTd.bind("click",function(){
var rowId = $(this).attr("id");
rowId = rowId.substr(rowId.length-1,1);
curIndex = rowId;
$("div[id^=selectDiv]").each(function(index){
var divId = $(this).attr("id");
divId = divId.substr(divId.length-1,1);
if(divId == rowId){
$(this).css("display","");
$("#selectRow"+divId).addClass("selected");
$("#selectRow"+divId).addClass("selected");
toggleClass($("#selectRow"+divId), "selected", "unselected");
}else{
$(this).css("display","none");
toggleClass($("#selectRow"+divId), "unselected", "selected");
}
});
});
selectRow.append(selectTd);
if(result[k] != null){
doPlainTable(result[k], 1, k);
var page2 = new Page2();
page2.sql = sqls[k];
page2.id = ""+k;
page2.positionId = "page"+k;
pageList[k] = page2;
page2.showPageHtml(totalCount, 1, 10);
}else{
var selectDiv = $("<div id='selectDiv"+k+"'>");
selectDiv.css("display","none");
selectDiv.html("查询失败");
$("body").append(selectDiv);
}
}
$("#selectDiv0").css("display","");
toggleClass($("#selectRow0"), "selected", "unselected");
},
errorHandler:function(errMsg){
$("#todo").val("查询");
$("#todo").attr("disabled", false);
alert(errMsg);
}
});
}
function toggleClass(obj,addClass,removeClass){
if(typeof(obj) == "string"){
$("#"+obj).removeClass(removeClass);
$("#"+obj).addClass(addClass);
}else{
obj.removeClass(removeClass);
obj.addClass(addClass);
}
}
function doPlainTable(data,curPage,index){
if(!isPageClick){
$("#dataTable").empty();
}
var selectDiv = $("<div id='selectDiv"+index+"'>");
selectDiv.css("display","none");
var scrollDiv = $("<div class='scrollbar'>");
var table = $("<table cellpadding='0' cellspacing='0' border=1 class=listcenter></table>");
table.attr("id","dataTable"+index);
//总条数
totalCount = data[0];
var th = $("<tr>");
th.css("background-color","rgb(217, 233, 255)");
th.css("color","#104184");
th.css("background-image","url('../images/table/headerOverBg.gif')");
//标题部分
var meta = data[1];
//i=1,是去掉trownum
for(var i = 1;i < meta.length;i ++){
var td = $("<th>");
td.html(meta[i]);
th.append(td);
}
table.append(th);
//数据部分
var odd = 1;
for(var row = 2;row < data.length;row ++){
var tr = $("<tr>");
tr.css("background-color",odd%2==0?"rgb(243, 244, 243)":"rgb(255, 255, 255)");
tr.attr("name",odd);
//当鼠标悬挂在元素上时，背景颜色的切换
tr.hover(
function () {
$(this).css("background-color","#3399FF");
},
function () {
var _odd = $(this).attr("name");
$(this).css("background-color",_odd%2==0?"rgb(243, 244, 243)":"rgb(255, 255, 255)");
});
odd ++;
for(var j = 1;j < meta.length;j ++){
var td = $("<td>");
td.css("word-break","keep-all");
td.css("word-wrap","normal");
td.css("white-space","nowrap");
td.attr("title",data[row][meta[j]] == null?" ":data[row][meta[j]]);
td.html(data[row][meta[j]] == null?" ":data[row][meta[j]]);
tr.append(td);
}
table.append(tr);
}
scrollDiv.append(table);
selectDiv.append(scrollDiv);
selectDiv.append("<br/><div id='page"+index+"' style='height:30px;text-align:center;margin-top: 15px;'>");
$("body").append(selectDiv);
}
function doQuery(curPage){
//获取网页选定文本
var sql = pageList[curIndex].sql;
isPageClick = true;
dbReadDao.getDbData(sql,curPage,{
callback:function(data){
$("#todo").val("查询");
$("#todo").attr("disabled",false);
$("#selectDiv"+curIndex).empty().remove();
doPlainTable(data, curPage, curIndex);
$("#selectDiv"+curIndex).css("display","");
pageList[curIndex].showPageHtml(totalCount, curPage, 10);
},
errorHandler:function(errMsg){
$("#todo").val("查询");
$("#todo").attr("disabled", false);
alert(errMsg);
}
});
}
//操纵DML语句方法
function update(){
var sql = DbRead.SQL;
if(window.confirm("你提交的DML语句如下:\n"+sql+"\n是否真的要提交?")){
var sqlList = new Array();
var sqls = sql.split(";");
for ( var row = 0; row < sqls.length; row++) {
if($.trim(sqls[row]) == ""){
continue;
}
sqlList.push(sqls[row]);
}
dbReadDao.updateList(sqlList, {
callback : function(data) {
if ("true" == data) {
Tip.prettyTip("toupdate", "Sql操作成功!");
} else {
Tip.prettyTip("toupdate", "Sql操作失败,请检查!");
}
$("#toupdate").val("更新");
$("#toupdate").attr("disabled", false);
},
errorHandler : function(errMsg) {
$("#toupdate").val("更新");
$("#toupdate").attr("disabled", false);
alert(errMsg);
}
});
}else{
$("#toupdate").val("更新");
$("#toupdate").attr("disabled", false);
}
}
//执行按钮的事件方法
function toDo(type){
var text = "";
if(document.all){
text = document.selection.createRange().text;
}else{
text = window.getSelection().toString();
}
var sql = $("#sqlText").val();
if(sql == "" || sql == null){
Tip.prettyTip("sqlText","请输入SQL语句");
return;
}
if(text != "" && text != null && text != undefined){
sql = text;
}
DbRead.SQL = sql;
if(type == 0){
$("#toupdate").val("正在执行中...请稍候!");
$("#toupdate").attr("disabled",true);
update();
}else if(type == 1){
$("#todo").val("正在执行中...请稍候!");
$("#todo").attr("disabled",true);
doQueryMulti();
}
}
function ctrlenterEvent(event){
event = window.event || event;
if(event.ctrlKey == true && event.keyCode == 13){
toDo(1);
}
}
return {
doQuery : doQuery,
update : update,
toDo : toDo,
ctrlenterEvent : ctrlenterEvent
};
}(jQuery);
var jq = jQuery.noConflict();
jq(document).ready(function(){
Tip.doTip("sqlText");
Tip.doTip("todo");
Tip.doTip("toupdate");
if(_SuperUser != "true"){
jq("#toupdate").css("display","none");
}
});
//选择页面
function selectPage(row){
//让执行按钮disabled
jq("#todo").val("正在执行中...请稍候!");
jq("#todo").attr("disabled",true);
DbRead.doQuery(row);
}
分页js
/**
* <pre>
* 分页
* </pre>
* @author ps
* @createTime 2013年1月18日 16:49:33
*/
var Page2 = function(){
var $this = this;
this.id = "";//每个分页的唯一标识
this.positionId = "";
this.downloadAction = "/dataListDownload.do";//下载的路径MVC的.do
this.setDownloadAction = function(downloadAction){
$this.downloadAction = downloadAction;
};
this.sql = null;//sql查询语句
/**
* 设置分页对应的查询sql
*/
this.setSql = function(sql){
$this.sql = sql;
};
//默认为5
this.showPageSize = 5;
/**
* 设置可以显示的页链接数
*/
this.setShowPageSize = function(showPageSize){
$this.showPageSize = showPageSize;
};
/**
* <pre>
* 显示分页的Html
* </pre>
* @param totalCount 总记录数
* @param curPage 当前页
* @param pageCount 每页显示记录数
*/
this.showPageHtml = function (totalCount,curPage,pageCount){
totalCount = totalCount * 1;
curPage = curPage * 1;
pageCount = pageCount * 1;
//计算页数
var pageNum = Math.floor((totalCount - 1)/pageCount) + 1;
var firstHtml = "<font class='black'>共[" + totalCount + "]条记录,共[" + pageNum + "]页,第["+curPage+"]页</font>";
var middleHtml = "";
var nextHtml = "";
var space = " ";
firstHtml += space;
//显示首页与上一页
if(curPage == 1 || pageNum == 0){
firstHtml += "<font class='black' title='首页'>首页</font>";
firstHtml += space;
firstHtml += "<font class='black' title='上一页'>上一页</font>";
}else{
firstHtml += "<a href='javascript:void(0);' title='首页' οnclick='selectPage(1)' class='blue'>首页</a>";
firstHtml += space;
firstHtml += "<a href='javascript:void(0);' title='上一页' οnclick='selectPage("+(curPage - 1)+")' class='blue'>上一页</a>";
}
firstHtml += space;
//显示末页与下一页
if(curPage == pageNum || pageNum == 0){
nextHtml += "<font class='black' title='下一页'>下一页</font>";
nextHtml += space;
nextHtml += "<font class='black' title='末页'>末页</font>";
}else{
nextHtml += "<a href='javascript:void(0);' title='下一页' οnclick='selectPage("+(curPage + 1)+")' class='blue'>下一页</a>";
nextHtml += space;
nextHtml += "<a href='javascript:void(0);' title='末页' οnclick='selectPage("+(pageNum)+")' class='blue'>末页</a>";
}
nextHtml += space;
var row = 1;
var endRow = pageNum;
if(pageNum > $this.showPageSize){
if((curPage + 2) <= $this.showPageSize){
row = 1;
endRow = $this.showPageSize;
}else if((curPage + 2) > $this.showPageSize && (curPage + 2) < pageNum){
row = curPage - 2;
endRow = curPage + 2;
}else{
row = pageNum - ($this.showPageSize - 1);
endRow = pageNum;
}
}
for(;row <= endRow;row ++){
if(curPage == row){
middleHtml += "<font class='gray' style='font-size:20px;' title='第"+(row)+"页'>"+(row)+"</font>";
}else{
middleHtml += "<a href='javascript:void(0);' title='第"+(row)+"页' οnclick='selectPage("+(row)+")'>"+(row)+"</a>";
}
middleHtml += space;
}
//输入页数文本框
var inputPage = jQuery("<input id='_page_num_"+$this.id+"' style='width:30px' title='请输入页数!'/>");
inputPage.bind("keyup",function(){
$this.enterPage(pageNum, event);
});
//GO按钮
var goBtn = jQuery("<input type='button' class='btn_width11113' value='GO'/>");
goBtn.bind("click",function(){
$this.goPage(pageNum);
});
//下载XLS
var downloadXls = jQuery("<a href='javascript:void(0);' title='同时按下shift和鼠标左键可以输入记录数' class='blue'>导出XLS</a>");
downloadXls.bind("click",function(){
$this.downloadData("xls", event);
});
//下载TXT
var downloadTxt = jQuery("<a href='javascript:void(0);' title='同时按下shift和鼠标左键可以输入记录数' class='blue' >导出TXT</a>");
downloadTxt.bind("click",function(){
$this.downloadData("text", event);
});
var pageHtml = firstHtml + middleHtml + nextHtml;
var pageLink = jQuery(pageHtml);
var pagePosition = jQuery("#"+$this.positionId);
pagePosition.append(pageLink);
pagePosition.append(space);
pagePosition.append(inputPage);
pagePosition.append(goBtn);
pagePosition.append(space);
pagePosition.append(downloadXls);
pagePosition.append(space);
pagePosition.append(downloadTxt);
};
//Enter事件
this.enterPage = function(pageNum,event){
event = window.event || event;
if(event.keyCode == 13){
$this.goPage(pageNum);
}
};
//GO按钮事件
this.goPage = function (pageNum){
var page = jQuery("#_page_num_"+$this.id).val();
if(page == ""){
alert("请输入页数!");
return;
}
var regex = /\d+/ig;
if(!regex.test(page)){
alert("请输入数字!");
return;
}
if(parseInt(page, 10) > parseInt(pageNum, 10)){
alert("输入页数大于最大页数,请重新输入!");
return;
}
if(parseInt(page, 10) <= 0){
alert("请输入大于0的数字!");
return;
}
selectPage(page);
};
//下载数据
this.downloadData = function (type,event){
event = window.event || event;
var pageNum = 1000;
if (event != null && event.shiftKey){
pageNum = window.prompt("请输入下载的记录数","10000");
}
jQuery("#_myPageDataDownLoad"+$this.id).empty().remove();
var form = jQuery("<form>");
form.attr("method","POST");
form.attr("id","_myPageDataDownLoad"+$this.id);
form.attr("action",_CONTEXT_PATH+$this.downloadAction);
form.attr("target","_self");
var typeInput = jQuery("<input type='hidden' name='type'/>");
var sqlInput = jQuery("<input type='hidden' name='sql'/>");
var pageNumInput = jQuery("<input type='hidden' name='pageNum'/>");
typeInput.val(type);
sqlInput.val($this.sql);
pageNumInput.val(pageNum);
form.append(typeInput);
form.append(sqlInput);
form.append(pageNumInput);
jQuery("body").append(form);
form.submit();
};
};
后台Java代码：
/**
*
*/
package com.huateng.erm.services.sysctrl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
/**
* @author Administrator
*
*/
public class DbReadDao extends JdbcDaoSupport {
private static Logger log = Logger.getLogger(DbReadDao.class);
/**
*
* @param sql
* @param curPage
* @return
*/
public List<?> getDbData(String sql,int curPage){
Statement stmt = null;
ResultSet rs = null;
Connection connection = null;
try {
List result = new ArrayList();
List<String> metaList = new ArrayList<String>();
int total = getTotalCount(sql);
result.add(total);
int end = curPage * 10;
sql = packagingSql(sql, end);
connection = getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int colCount = metaData.getColumnCount();
for (int i = 0; i < colCount; i++) {
metaList.add(metaData.getColumnName(i+1));
}
result.add(metaList);
while (rs.next()) {
Map<String,String> map = new HashMap<String, String>();
for (int i = 0; i < colCount; i++) {
String colLabel = metaData.getColumnName(i+1);
String colValue = rs.getString(colLabel);
map.put(colLabel, colValue);
}
result.add(map);
}
return result;
} catch (SQLException e) {
logger.error("查询异常，查询语句[" + sql + "]", e);
return null;
} finally {
try {
if (rs != null){
rs.close();
}
if (stmt != null){
stmt.close();
}
if(connection != null){
connection.close();
}
} catch (Exception e) {
logger.error("statement关闭异常", e);
}
}
}
/**
*
* @param sqls
* @return
*/
public List<?> getDbDataMulti(List<String> sqls){
List result = new ArrayList();
for (String sql : sqls) {
List<?> list = getDbData(sql, 1);
result.add(list);
}
return result;
}
/**
* 封装SQL的分页
* @param sql SQL语句
* @param end
* @return
*/
public String packagingSql(String sql,int end){
int start = end - 10;
sql = "select * from( select rownum trownum,t.* from (" + sql + ") t where rownum <="+ end +") where trownum >"+start;
log.info(sql);
return sql;
}
/**
* 返回数据的总条数
* @param sql
* @return
*/
public int getTotalCount(String sql){
sql = "select count(1) from (" + sql + ")";
log.info(sql);
return getJdbcTemplate().queryForInt(sql);
}
/**
* DML语句
* @param sql sql语句
* @return 操纵的sql数
*/
public int update(String sql){
return getJdbcTemplate().update(sql);
}
/**
* DML语句
* @param sqlLists sql 列表
*/
public String updateList(List<String> sqlLists){
Connection conn = null;
Statement stmt = null;
try{
conn = getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
for (String sql : sqlLists) {
if(sql == null || "".equals(sql)){
continue;
}
if(sql.trim().intern() == "".intern()){
continue;
}
stmt.execute(sql);
}
conn.commit();
return "true";
}catch(Exception e){
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return "false";
}finally{
try {
if(conn != null){
conn.close();
}
if(stmt != null){
stmt.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
}
JavaScript说明：
<script type='text/javascript' src='<%=request.getContextPath()%>/dwr/interface/dbReadDao.js'></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/jquery.poshytip.min.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/Tip.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/js/Page2.js"></script>
<script type="text/javascript" src="<%=request.getContextPath()%>/sysctrl/dbRead2.js"></script>
第一个dbReadDao.js是Dwr自动生成的JS代码，对应的Java后台为DbReadDao.java
Tip.js是封装jquery.poshytip.min.js中的一个提示方式。
Page2.js是封装分页的代码
dbRead2.js是生成对应的HTML代码。
下面提供Tip.js的源码
/**
* <pre>
* 美化提示信息
* </pre>
* @author ps
* @createTime 2012年12月19日 10:25:15
*/
var Tip = function ($){
/**
* 美化提示方式
* @param selector jquery选择器
* @param title 提示信息
* @return
*/
function prettyTip(selector,title){
var _jqobj = null;
if(typeof selector == 'string'){
_jqobj = $("#"+selector);
}else{
_jqobj = selector;
}
_jqobj.poshytip("update",title);
_jqobj.poshytip("show");
_jqobj.focus();
}
/**
* 为每个输入框增加提示信息
* @param selector
* @return
*/
function doTip(selector){
var _jqobj = null;
if(typeof selector == 'string'){
_jqobj = $("#"+selector);
}else{
_jqobj = selector;
}
_jqobj.poshytip({
className: 'tip-yellowsimple',
showOn: 'none',
alignTo: 'target',
alignX: 'inner-left',
timeOnScreen: 3000,
offsetX: 0,
offsetY: 5
});
}
function alert(msg){
$("#_showMsgDiv_").empty().remove();
var showMsgDiv = $("<div id='_showMsgDiv_' align='center'>");
var h = window.screen.availHeight/4;
var w = window.screen.availWidth/3;
var table = $("<table align='center' style='height:100%'>");
var tr = "<tr><td style='text-align: center;font-size:12px;font-family:Consolas;'></td></tr>";
table.append(tr);
showMsgDiv.css({
width:"250px",
maxWidth:"300px",
height:"100px",
maxHeight:"300px",
zIndex:10000,
border:"1px solid #c7bf93",
borderRadius:"4px",
textAlign:"center",
top:parseFloat(h),
left:parseFloat(w),
backgroundColor:"#fff9c9",
color:"#000",
display:"none",
position:"absolute",
opacity:0.9
});
msg = msg.replace(/\n/g,"</br>");
table.find("td").html(msg);
showMsgDiv.append(table);
$("body").append(showMsgDiv);
showMsgDiv.fadeIn("slow",function(){
setTimeout(function(){showMsgDiv.fadeOut("slow");},3000);
});
}
return {
prettyTip : prettyTip,
doTip : doTip,
alert : alert
};
}(jQuery);
192
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
