The implementation of tree data (hierarchical relational data) in the Web--a case study of the administrative district tree

In web development, the operation of tree data is often encountered, such as menu, organization, administrative district (province, city, county) and other data with hierarchical relationship.

The following example illustrates the storage and implementation of tree-shaped data (hierarchical relational data), as seen by the effect.

1 database table structure design

Tree data generally through the parent node and child nodes to achieve hierarchical association between data, hierarchical relationship in the database mainly through the primary key and foreign key to achieve.

--Using the Oracle database--Create the Administrative table created table TB_XZQ (code number NOT NULL,--Administrative Code, primary key Parent_code number,--Parent row Political district code, assuming there is no upper administrative region. is empty name VARCHAR2 (50)-The name of the canton);--set CODE to primary key ALTER TABLE TB_XZQ ADD constraint pk_tb_xzq primary key (code) u Sing index;--set foreign key ALTER TABLE TB_XZQ ADD constraint Fk_tb_xzq_parent_code foreign key (Parent_code) references Tb_xzq (CO  DE) on delete set null;--inserting the administrative data insert into TB_XZQ (code,parent_code,name) VALUES (420000, NULL, ' Hubei Province '); INSERT INTO TB_XZQ (Code,parent_code,name) VALUES (420100, 420000, ' Wuhan '); insert into TB_XZQ (code,parent_code,name) VALUES (420101, 420100, ' municipal districts '); INSERT INTO TB_XZ Q (Code,parent_code,name) VALUES (420102, 420100, ' Jiang ' an '); INSERT into Tb_xzq (code,parent_code,name) VALUES (420103, 42010 0, ' Jianghan District '); insert into Tb_xzq (code,parent_code,name) VALUES (420104, 420100, ' Qiaokou District '); insert into Tb_xzq (Code,parent_code, NAME) VALUES (420105, 420100, ' Hanyang District '); INSERT into Tb_xzq (code,parent_code,name) VALUES (421000, 420000, ' Jingzhou cityInsert into TB_XZQ (code,parent_code,name) VALUES (421001, 421000, ' Municipal District '), insert into TB_XZQ (code,parent_code,name) V  Alues (421002, 421000, ' Shashi District '); insert into Tb_xzq (code,parent_code,name) VALUES (421003, 421000, ' Jinzhou District '); INSERT INTO TB_XZQ (Code,parent_code,name) VALUES (430000, NULL, ' Hunan Province '), insert into TB_XZQ (code,parent_code,name) VALUES (430100, 430000, ' Changsha '); INSERT INTO TB_XZQ (Code,parent_code,name) VALUES (430101, 430100, ' districts '); insert into TB_XZQ (code,parent_code,name) VALUES (430102, 430100, ' Furong '); INSERT INTO TB_XZ Q (Code,parent_code,name) VALUES (430103, 430100, ' Tianxin District '); INSERT into Tb_xzq (code,parent_code,name) VALUES (430104, 43010 0, ' Yuelu District ');

Post-insert data, such as what you see

2 Tree-shaped data Java implementation

Typically, data that is read from a database needs to be converted to a tree structure. TreeNode is a Java tree data implementation class. By static method Buildtree can easily build TreeNode into a tree.

Import java.util.arraylist;import java.util.list;/** * tree node.  Support for Web controls such as EXT, ztree * * @author [email protected] * @param <T> tree node binding data classes */public class Treenode<t> {/** * Tree Node ID * For compatibility with multiple cases, use String type */private string id;/** * tree node ancestor ID */private string parentid;/** * tree node display text */private string te xt;/** * tree node name.  The content is the same as text * This field is primarily compatible with ext and ztree */private String name;/** * is the leaf node */private boolean leaf = True;private Boolean expanded = False;private T nodedata;/** * is a parent node, the field and the leaf are repeated, primarily for compatibility with ext and ztree */private Boolean isparent = false;/** * child nodes. Assuming there are no child nodes, the list length is 0 */private list<treenode<t>> children = new arraylist<treenode<t>> ();p ublic String getId () {return ID;} public void SetId (String id) {this.id = ID;} Public String Getparentid () {return parentid;} public void Setparentid (String parentid) {this.parentid = ParentID;} Public String GetText () {return text;} public void SetText (String text) {this.name = Text;this.text = text;} Public String GetName () {return name;} Public void SetName (String name) {this.name = Name;this.text = name;} Public Boolean getexpanded () {return expanded;} public void setexpanded (Boolean expanded) {this.expanded = expanded;} Public list<treenode<t>> GetChildren () {return children;} public void Setleaf (Boolean leaf) {this.leaf = Leaf;this.isparent =!leaf;} Public Boolean Getleaf () {return this.leaf;} Public Boolean getisparent () {return isparent;} public void Setisparent (Boolean isparent) {this.isparent = Isparent;this.leaf =!isparent;} Public T Getnodedata () {return nodedata;} public void Setnodedata (T nodedata) {this.nodedata = Nodedata;} /** * Constructs a tree node list into a tree, and finally returns the root node of the tree, assuming that the incoming list has multiple root nodes, a root node is created dynamically.
 
 
 * @param nodes tree Node list * @return root node */public static <T> treenode<t> buildtree (list<treenode<t>> No DES) {if (nodes = = NULL | | nodes.size () = = 0) {return null;} if (nodes.size () = = 1) {return nodes.get (0);} Used to store nodes inside the top tree node//That is, the node without the parent node is put into the tops inside to list<treenode<t>> tops = new arraylist<treenode<t> > (); Boolean hasparent = false;//First traversal, gets a node as a child node for (treenode<t> child:nodes) {hasparent = false;// The parent node of the current node child idstring pid = Child.getparentid ();//Assuming PID does not exist or is empty//The current node is the top node if (PID = = NULL | | pid.equals ("")) {// Add the current node to tops as the top node tops.add (child);//Skip the current node and go to the next round of continue;} Traverse all nodes on the nodes to infer if there is a child parent node for (treenode<t> parent:nodes) {String id = parent.getid ();// Assuming that the parent node's ID equals the child node's PID, then the parent node is the child node's parents node if (id! = NULL && id.equals (PID)) {// Add child to Parent under Parent.getchildren (). Add (Child);p arent.setleaf (false);//child node has parent node hasparent = true;continue;}} Assume that the child node does not have a parent node. Then child is the top node//Add child to tops if (!hasparent) {tops.add (child);}} treenode<T> root;if (tops.size () = = 1) {//assuming that the top node has only one. The top node is the root node, root = Tops.get (0);} ELSE{//assumes that there are multiple top-level nodes, create a root node, place the top-level node under the root node, root = new treenode<t> (), Root.setleaf (false), Root.setid ("1"); Root.setname ("root"); Root.setparentid (""); Root.getchildren (). AddAll (tops);} return root;}}

3 Generating the administrative tree

Dao (listing only the main code)

@Repository ("Xzqdao") public class Xzqdaoimpl implements Xzqdao {@Resourceprivate JdbcTemplate jdbctemplate;public List <XzqEntity> Select () {String sql = "Select Code,parent_code,name from Tb_xzq";//Database operation with spring JDBC return Jdbctemplate.query (SQL, new rowmapper<xzqentity> () {public xzqentity Maprow (ResultSet rs, int index) throws SQLException {xzqentity xzq = new xzqentity (); Xzq.setcode (Rs.getint ("CODE")); Xzq.setname (rs.getstring ("NAME")); Xzq.setparentcode (Rs.getint ("Parent_code")); return xzq;});}}

Service (column main code only)

@Service ("Xzqservice") public class Xzqserviceimpl implements Xzqservice {@Resourceprivate Xzqdao xzqdao;public Treenode<xzqentity> tree () {list<xzqentity> List = Xzqdao.select (); list<treenode<xzqentity>> nodes = new arraylist<treenode<xzqentity>> ();//Convert the administrative class to tree node for ( xzqentity xzq:list) {treenode<xzqentity> node = new treenode<xzqentity> ();//Node Idnode.setid (Xzq.getCode () . ToString ());//Node ancestor Idnode.setparentid (Xzq.getparentcode (). ToString ()); Node.settext (Xzq.getname ());// Place the Canton class in the node data for use with Node.setnodedata (XZQ); Nodes.Add (node);} return Treenode.buildtree (nodes);}}

Controller (only the main code is listed)

@Controller @requestmapping ("/xzq") public class Xzqcontroller {@Resourceprivate xzqservice xzqservice;/** * Administrative tree, Returns the JSON format *  * @param response */@RequestMapping ("/TREE.MVC") public void Tree (HttpServletResponse response) {String JSON = ""; try {json = json.tojsonstring (Xzqservice.tree ());} catch (Exception e) {e.printstacktrace ();} Output JSON data//here directly through the response output JSON string//spring MVC also provides a way to output JSON data//Set the encoding format Response.setcontenttype ("Text/plain; Charset=utf-8 "); Response.setcharacterencoding (" Utf-8 "); PrintWriter out = null;try {out = Response.getwriter (); Out.write (JSON); Out.flush ();} catch (IOException e) { E.printstacktrace ();}}}

Ztree display of the borough tree

<%@ page language= "java" contenttype= "text/html; Charset=utf-8 "pageencoding=" UTF-8 "%><%string context = Request.getcontextpath ();%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >

 
The implementation of tree data (hierarchical relational data) in the Web--a case study of the administrative district tree