原来一直使用id与 parent_id结合的办法设计树,最近发现有些问题:
1、查询此结点下所有子结点的需求。
2、查询此结点上所有父结点的需求。
这些需求在oracle和sql server中可以使用一些办法在数据库端进行处理,但在mysql中处理就稍显麻烦,在sqlite中基本无解。所以想办法重新设计一下就显的很有必要的了。
添加两列:structure_node varchar(128)和 level int(11)
root 001
第一级第一个结点 001 001
第一级第二个结点 001 002
第二级第一个结点 001 001 001
这样查询起来就很方便了。
问题来了,
问题1:将现在id与parent_id的结构迁移到新结构上:
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.druid.DruidPlugin;public classMain {public static List list_parent_id=new ArrayList();public static List list_sql=new ArrayList();/**
* @param args
* @throws Exception*/
public static voidmain(String[] args) throws Exception
{
Properties prop= newProperties();
String classDir= Main.class.getResource("/").getPath();
FileInputStream fis= new FileInputStream(classDir+"/dsideal_config.properties");
prop.load(fis);//配置druid数据库连接池插件
String driver =prop.getProperty("driverClass");
DruidPlugin druidPlugin= new DruidPlugin(prop.getProperty("jdbcUrl"), prop.getProperty("user"), prop.getProperty("password"), driver);
druidPlugin.start();//配置ActiveRecord插件
ActiveRecordPlugin arp = newActiveRecordPlugin(druidPlugin);
arp.start();
String sql="select distinct scheme_id from t_resource_structure";
List scheme_list=Db.find(sql);for(int t=0;t
{
list_parent_id.clear();
String scheme_id=scheme_list.get(t).get("scheme_id").toString();//设置根
sql="update t_resource_structure set structure_node='001',level=1 where structure_id=?";
Db.update(sql,scheme_id);
list_parent_id.add(scheme_id);int level=1;while(list_parent_id.size()>0)
{
level++;
update_node_byparent_id(level);
}
}
System.out.println("结构转换成功完成!");//提取新的映射关系到map里
Map mymap=new HashMap();
sql="select structure_id,structure_node from t_resource_structure";
List myr=Db.find(sql);for(int i=0;i
{
mymap.put(myr.get(i).get("structure_id").toString(), myr.get(i).get("structure_node").toString());
}//获取到资源表中的对应数据
sql="select resource_id,node_id from t_resource_base";
myr=Db.find(sql);for(int i=0;i
{
list_sql.add("update t_resource_base set structure_node='"+mymap.get(myr.get(i).get("node_id"))+"' where resource_id='"+myr.get(i).get("resource_id")+"'");
}
System.out.println("正在提交资源的数据,请稍等...");//事务提交
Db.batch(list_sql, 1000);
System.out.println("所有操作成功完成!");
}private static void update_node_byparent_id(intlevel)
{
List tmp_list_parent_id=new ArrayList();for( int k=0;k
{
System.out.println("正在处理,共"+list_parent_id.size()+"个,第"+(k+1)+"个");
String sql="SELECT structure_id,parent_id FROM t_resource_structure where parent_id=? order by sort_id";
List mylist= Db.find(sql,list_parent_id.get(k));for(int i=0;i
{
tmp_list_parent_id.add(mylist.get(i).get("structure_id").toString());int code=1000+(i+1);//父结点的node
sql="select structure_node from t_resource_structure where structure_id=?";
String parent_structure_node=Db.queryStr(sql,mylist.get(i).get("parent_id").toString());
String result_code=parent_structure_node+String.valueOf(code).substring(1,4);
sql="update t_resource_structure set structure_node='"+result_code+"',level="+level+"where structure_id='"+mylist.get(i).get("structure_id")+"'";
Db.update(sql);
}
}
list_parent_id.clear();
list_parent_id=tmp_list_parent_id;
}
}
2、有了structure_code没有structure_id和parent_id
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.druid.DruidPlugin;public classMain {/**
* @param args
* @throws Exception*/
public static voidmain(String[] args) throws Exception
{
Properties prop= newProperties();
String classDir= Main.class.getResource("/").getPath();
FileInputStream fis= new FileInputStream(classDir+"/dsideal_config.properties");
prop.load(fis);//配置druid数据库连接池插件
String driver =prop.getProperty("driverClass");
DruidPlugin druidPlugin= new DruidPlugin(prop.getProperty("jdbcUrl"), prop.getProperty("user"), prop.getProperty("password"), driver);
druidPlugin.start();//配置ActiveRecord插件
ActiveRecordPlugin arp = newActiveRecordPlugin(druidPlugin);
arp.start();//要设置哪个结构
String scheme_id="5CA47D74-B745-4E09-A7B2-9E02DCB06DBF";//处理一下根结点,根结点应该是有guid号 ,parent_id和structure_code的
String sql="update t_resource_structure set parent_id='11111111-1111-1111-1111-111111111111',level=1 where scheme_id=? and structure_node='001'";
Db.update(sql,scheme_id);//保留结构ID和结构CODE两种方法
sql="select structure_id,structure_node from t_resource_structure where SCHEME_ID=? and parent_id is null";
List no_parent_list=Db.find(sql,scheme_id);
Map mymap=new HashMap();//放到Map里
for(int i=0;i
{
mymap.put(no_parent_list.get(i).get("structure_node").toString(), no_parent_list.get(i).get("structure_id").toString());
}//循环取回来
List list_sql=new ArrayList();for(int i=0;i
{
String code=no_parent_list.get(i).get("structure_node");
String id=no_parent_list.get(i).get("structure_id");
sql="update t_resource_structure set parent_id='"+mymap.get(code.substring(0, code.length()-3))+"' where structure_id='"+id+"'";
list_sql.add(sql);
}if(list_sql.size()>0)
{
Db.batch(list_sql,1000);
}
System.out.println("所有操作成功完成!");
}
}
update t_resource_structure a inner join
(select structure_node,structure_id,scheme_id from t_resource_structure where scheme_id='F639942E-5211-4254-A3DF-E817A47C4D50') c
on a.scheme_id=c.scheme_id and SUBSTRING(a.structure_node,1,LENGTH(a.structure_node)-3)=c.structure_nodeset a.parent_id=c.structure_id where a.scheme_id='F639942E-5211-4254-A3DF-E817A47C4D50' and LENGTH(a.structure_node)>3
3、获取一个可用CODE的代码
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.druid.DruidPlugin;public classMain {public static List list_parent_id=new ArrayList();public static List list_sql=new ArrayList();/**
* @param args
* @throws Exception*/
public static voidmain(String[] args) throws Exception
{
Properties prop= newProperties();
String classDir= Main.class.getResource("/").getPath();
FileInputStream fis= new FileInputStream(classDir+"/dsideal_config.properties");
prop.load(fis);//配置druid数据库连接池插件
String driver =prop.getProperty("driverClass");
DruidPlugin druidPlugin= new DruidPlugin(prop.getProperty("jdbcUrl"), prop.getProperty("user"), prop.getProperty("password"), driver);
druidPlugin.start();//配置ActiveRecord插件
ActiveRecordPlugin arp = newActiveRecordPlugin(druidPlugin);
arp.start();
String scheme_id="ED1695FB-6B6E-11E2-B11E-00FF2D04A858";
System.out.println(getNode(scheme_id,"001003"));
System.out.println("所有操作成功完成!");
}/**
* 功能:传入一个父结点ID,获取这个结点下新生成一个子结点的可用ID
* 作者:黄海
* 时间:2013-07-22
* @param parent_node
* @return
* @throws Exception*/
private staticString getNode(String scheme_id,String parent_node) throws Exception
{int level=parent_node.length()/3+1;
String result="";
String select_sql="select structure_node from t_resource_structure_copy where scheme_id=? and structure_node like ? and level="+level +"order by structure_node desc";
String sql=select_sql+"limit 1";
String structure_node=Db.queryStr(sql,scheme_id,parent_node+"%");if(structure_node==null)
{
result=parent_node+"001";
}else{//后三位是啥
String last_three=structure_node.substring(structure_node.length()-3, structure_node.length());//如果没有到999就一直向后加1
if(Integer.parseInt(last_three)+1<=999)
{
result=parent_node+String.valueOf(1000+Integer.parseInt(last_three)+1).substring(1,4);
}else//如果到了999了,那么我们检查一下是不是前面有断号的,如果没有,那么就是超出了这个算法的极限,我们只支持一个结点下最多999个子节点
{
Listmylist=Db.find(select_sql,scheme_id,parent_node+"%") ;
Map mymap=new HashMap();for(int i=0;i
{
last_three=mylist.get(i).getStr("structure_node").substring(mylist.get(i).getStr("structure_node").length()-3, mylist.get(i).getStr("structure_node").length());
mymap.put(last_three,"1");
}
boolean found=false;for(int i=1;i<=999;i++)
{
String key=String.valueOf(1000+i).substring(1,4);if(mymap.get(key)!=null)
{continue;
}else{
result=key;
found=true;break;
}
}if(!found) throw new Exception("不行啊,实在找不着可以用的号啊!");
}
}returnresult;
}
}
3、测试一下效率:
explain select * from t_resource_structure_copy force index(index_structure_node) where scheme_id='ED1695FB-6B6E-11E2-B11E-00FF2D04A858' and structure_node like '001001%'
强制使用了索引,这时执行效率如下:
查询时间为0.27秒查询出10000多条,也算可以了。