因为生产环境银行记录大约14万条,需要多查询进行优化,现对银行档案表parentId字段上加位图索引。
点击子节点刷新右侧iframe list页面,不展开树,只有点子节点前的加号才会有展开事件。
create bitmap index index_bank_parent_id on S_SUPPLIER_BANK_RECORD (parent_id);
/**
*
* < 银行档案 >
* @author yangyang
* @version $Id$
* @since
* @see
*/
@Entity
@Table(name="S_SUPPLIER_BANK_RECORD")
public class SupplierBankRecord extends IdEntity {
/**
* @Fields serialVersionUID : 序列化
*/
private static final long serialVersionUID = -37012125196490147L;
/**
* 开户行名称
*/
private String name;
/**
* 银行编码
*/
private String code;
/**
* 简称
*/
private String shortName;
/**
* 爸爸
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PARENT_ID", updatable = false, insertable = false)
private SupplierBankRecord parent;
/**
* 爸爸Id
*/
@Column(name = "PARENT_ID")
private Integer parentId;
/**
* 根
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ROOT_ID", updatable = false, insertable = false)
private SupplierBankRecord root;
/**
* 根Id
*/
@Column(name = "ROOT_ID")
private Integer rootId;
/**
* 国家
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "COUNTRY_ID", updatable = false, insertable = false)
private SupplierBankArea country;
/**
* 国家Id
*/
@Column(name = "COUNTRY_ID")
private Integer countryId;
/**
* 省
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PROVINCE_ID", updatable = false, insertable = false)
private SupplierBankArea province;
/**
* 省Id
*/
@Column(name = "PROVINCE_ID")
private Integer provinceId;
/**
* 市
*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "city_ID", updatable = false, insertable = false)
private SupplierBankArea city;
/**
* 市Id
*/
@Column(name = "city_ID")
private Integer cityId;
/**
* 全国统一编码 银行编号
*/
private String bankCode;
/**
* 地址
*/
private String address;
/**
* 机构号
*/
private String institutionCode;
/**
* 联行号
*/
private String bankLineNum;
/**
* 人行联行号 人民银行PBOC
*/
private String PBOCLineNum;
/**
* 人行联行名称
*/
private String PBOCLineName;
/**
* swift号
*/
private String swiftAccount;
/**
* 助记码
*/
private String mnemonicCode;
/**
* 网银地区代码
*/
private String cyberBankAreaCode;
/**
* 位置
*/
private Integer position;
/**
* 创建日期
*/
@Column(name="create_date")
private Date createDate;
/**
* 编辑日期
*/
@Column(name="update_date")
private Date updateDate;
/**
* 是否删除 0:正常 1:已删除
*/
private Integer isDelete;
/**
* 是否为分类 0:否,1:是
*/
private Integer isCategory;
/**
*
*/
private String pkId;
/**
* 历史层级
*/
private String prefix;
@Formula("(select COUNT(*) from " + tablePrefix + "S_SUPPLIER_BANK_RECORD br where br.parent_id is NULL)")
private int isRoot;
@Formula("(select COUNT(*) from " + tablePrefix + "S_SUPPLIER_BANK_RECORD br where br.parent_id = id and br.is_delete=0)")
private int subCount;
}
/****************************************************************************************/
/**
* 〈 加载树〉 后台Action调用的方法
*
* @author yangyang
*/
public void getTrees() {
List<SupplierBankRecord> list = null;
List<Integer> listTreeList = null;
// 根据分类名称查询
if (bankName != null && !"".equals(bankName.trim())) {
try {
// 解码
bankName = java.net.URLDecoder.decode(bankName, "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
List<SupplierBankRecord> likeList = getCommonService().findByHQL(
SupplierBankRecord.class, "from SupplierBankRecord where name like ?",
new Object[] {"%" + bankName.trim() + "%"});
listTreeList = new ArrayList<Integer>(0);
if (!likeList.isEmpty()) {
for (SupplierBankRecord item : likeList) {
listTreeList.add(item.getId());
}
} else {
listTreeList.add(0);
}
}
DetachedCriteria dc = DetachedCriteria.forClass(SupplierBankRecord.class);
if (parentId == null || parentId.intValue() == 0) {
dc.add(Restrictions.isNull("parentId"));
} else {
dc.add(Restrictions.eq("parentId", parentId));
}
if (listTreeList != null) {
dc.add(Restrictions.in("id", listTreeList));
}
// 排除删除掉
dc.add(Restrictions.or(Restrictions.eq("isDelete", Constant.INT_FALSE),
Restrictions.isNull("isDelete")));
//dc.addOrder(Order.asc("position"));
dc.addOrder(Order.asc("id"));
list = getCommonService().findByCriteria(dc);
List<Map<String, Object>> trees = new ArrayList<Map<String, Object>>();
for (SupplierBankRecord bankRecord : list) {
Map<String, Object> tree = new HashMap<String, Object>();
tree.put("id", bankRecord.getId());
tree.put("isCategory", bankRecord.getIsCategory());
tree.put("name", bankRecord.getName());
if (StringUtils.isNotBlank(bankRecord.getName()) && bankRecord.getName().length()>15){
tree.put("text", bankRecord.getName().substring(0, 15)+"...");
} else {
tree.put("text", bankRecord.getName());
}
tree.put("expanded", false);
tree.put("qtip", bankRecord.getName());
tree.put("leaf", bankRecord.getSubCount() == 0);
trees.add(tree);
}
super.renderJson(trees);
}
/****************************************************************************************/
/**
*前端写的js
*/
var dt = new Date();
var tree;
var buildTree = function() {
var root = new Ext.tree.AsyncTreeNode({
id : '0',
text : '供方银行档案管理'
});
tree = new Ext.tree.TreePanel({
loader : new Ext.tree.TreeLoader({
dataUrl : 'supplier_bank!getTrees.ajax'
}),
root : root,
renderTo : 'supplierBankTree',
border : false,
autoHeight : false,
animate : true,// 以动画形式伸展,收缩子节点
autoScroll : true
});
tree.on('click', function(node) {
// writeObj(node);
// var isExpand =node.expanded;
// if (isExpand){
// node.collapse();
// }
$("#maskDiv").mask("页面加载中...");
var dt = new Date().getTime();
//node.expand();
node.select();
var bankName = $("#bankName").val();
if (isEmpty(bankName)){
$("#supplier_bank_frame").contents().find("#bankName").val("");
}
var bankCode = $("#bankCode").val();
if (isEmpty(bankCode)){
$("#supplier_bank_frame").contents().find("#bankCode").val("");
}
if (node.leaf == undefined) {
categoryIdChange("");
} else {
$("#supplier_bank_frame").contents().find("#parentId").val(node.id);
$("#supplier_bank_frame").contents().find("form").submit();
}
});
tree.on('beforeload', function(node) {
tree.loader.dataUrl = 'supplier_bank!getTrees.ajax?parentId=' + node.id+"&dt="+dt;
});
root.expand();
root.select();
};
var init = function() {
buildTree();
Ext.QuickTips.init();
var height = 360;
if(document.getElementById("supplier_bank_frame").contentWindow.document.body.scrollHeight>height){
height = document.getElementById("supplier_bank_frame").contentWindow.document.body.scrollHeight;
}
tree.setHeight(height);
};
Ext.onReady(init);
//添加新节点
function addNewNode(nodeId,nodeName){
var curNode = tree.getSelectionModel().getSelectedNode();
var innerHTMLNodeName = nodeName;
if (innerHTMLNodeName.length > 15){
innerHTMLNodeName =nodeName.substring(0, 15)+"...";
}
var newNode = new Ext.tree.TreeNode({
id : nodeId,
text : innerHTMLNodeName,
leaf : true
});
curNode.leaf = false;
curNode.getUI().removeClass('x-tree-node-leaf');
curNode.getUI().addClass('x-tree-node-expanded');
curNode.appendChild(newNode);
newNode.ui.textNode.qtip=nodeName;
curNode.expand();
}
//删除节点
function deleteNodes(nodeIds){
var curNode = tree.getSelectionModel().getSelectedNode();
if(nodeIds!="" && nodeIds!=null){
for(var x=0;x<nodeIds.split(",").length;x++) {
if(nodeIds.split(",")[x]!=null && nodeIds.split(",")[x]!=""){
try{
var node = curNode.findChild('id', nodeIds.split(",")[x]);
node.remove();
}catch(e){
}
}
}
}
if (curNode && !curNode.hasChildNodes()) {
curNode.getUI().removeClass('x-tree-node-expanded');
curNode.getUI().removeClass('x-tree-node-icon');
curNode.getUI().addClass('x-tree-node-leaf');
curNode.leaf = true;
}
}
/**
* 供方分类修改
*/
function categoryIdChange(categoryId){
$("#supplier_bank_frame").contents().find("#parentId").val(categoryId);
doSearch();
}
/**
* 分类名称修改
*/
function supplierNameChange(obj){
stringChange($(obj).val(), "categoryName");
}
/**
* 搜索条件修改,更新列表iframe中的搜索条件的值
* @param val 需要修改的值
* @param domId iframe中的id
*/
function stringChange(val, domId){
$("#supplier_bank_frame").contents().find("#" + domId).val(val);
}
/**
* 分类状态
*/
function categoryStatuss(obj){
stringCategoryStatusChange($(obj).val(), "categoryStatus");
}
/**
* 搜索条件修改,更新列表iframe中的搜索条件的值,并直接显示出查询结果
*/
function stringCategoryStatusChange(val, domId){
$("#supplier_bank_frame").contents().find("#" + domId).val(val);
doSearch();
}
/**
* 搜索
*/
function doSearch(){
$("#supplier_bank_frame").contents().find("form").submit();
}
//回车键事件
document.οnkeydοwn= function(e){
keyEnterFun(e,searchForm);
};
//查询
function searchForm(){
var bankName = $("#bankName").val();
var bankCode = $("#bankCode").val();
document.getElementById("supplier_bank_frame").contentWindow.document.getElementById("bankName").value=bankName;
document.getElementById("supplier_bank_frame").contentWindow.document.getElementById("bankCode").value=bankCode;
document.getElementById("supplier_bank_frame").contentWindow.document.getElementById("frm").submit();
}