[RESOLVED] JSP Treeview

[ranbahadur]

Hi Friends,

I want to create the simple tree view which display the database fields as tree structure in jsp.I have to display the database field values for example, at first i have to dispaly clients tree, if i click the client i have to display the projects of the client, etc. I want to use simple jsp code not the using jsp custom tags. I have to dispaly a tree from database then inside value, etc in tree structure.I dont want to use JSTL or EL. Just i want to show Root Node, Parent Node, Child Node from mysql database using simple jsp. I have pasted the jsp code regarding this, but this is complex to apply to my requirement. I gone throug one website http://www.easywayserver.com/forum/viewtopic.php?t=41 . This site is having jsp code to connect with database and fetch and display database values in treeview structure. But my requirement is very simple to display database fields in Treeview. If anyone change this code and send me the simple code with database access with treeview its nice.

Thanks in advance.

Regards,
Mike

[Xeel]

Database:
- create 3 tables for Root, Parent, Child. (for ex.: root: rootid,txt; parent: parentid,rootid,txt; child: childid,parentid,txt);

App:
- write a function that would connect to the db and read the whole structure (you'd need some loops with queries inside)
- save it all in a multidimensional arraylist

JSP:
- using an algorithm structure similar to that for reading the db (i mean loops, etc.) read the array and paint it on the page.

That would be it. If need something more specific - just ask. =)

[Xeel]

Ok, special offer, today only, homeworks and other solutions for free!

database:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Table structure for table `menu_child`
--

CREATE TABLE IF NOT EXISTS `menu_child` (
  `id_child` tinyint(2) NOT NULL,
  `id_parent` tinyint(2) NOT NULL,
  `tx_child` varchar(15) NOT NULL,
  PRIMARY KEY  (`id_child`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `menu_child`
--

INSERT INTO `menu_child` (`id_child`, `id_parent`, `tx_child`) VALUES
(0, 1, 'child0_1_0'),
(1, 2, 'child0_2_1'),
(2, 2, 'child0_2_2');

-- --------------------------------------------------------

--
-- Table structure for table `menu_parent`
--

CREATE TABLE IF NOT EXISTS `menu_parent` (
  `id_parent` tinyint(2) NOT NULL,
  `id_root` tinyint(2) NOT NULL,
  `tx_parent` varchar(15) NOT NULL,
  PRIMARY KEY  (`id_parent`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `menu_parent`
--

INSERT INTO `menu_parent` (`id_parent`, `id_root`, `tx_parent`) VALUES
(0, 0, 'parent0_0'),
(1, 0, 'parent0_1'),
(2, 0, 'parent0_2'),
(3, 2, 'parent2_3'),
(4, 2, 'parent2_4');

-- --------------------------------------------------------

--
-- Table structure for table `menu_root`
--

CREATE TABLE IF NOT EXISTS `menu_root` (
  `id_root` tinyint(2) NOT NULL,
  `tx_root` varchar(15) NOT NULL,
  PRIMARY KEY  (`id_root`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `menu_root`
--

INSERT INTO `menu_root` (`id_root`, `tx_root`) VALUES
(0, 'root0'),
(1, 'root1'),
(2, 'root2'),
(3, 'root3');

java class:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MyClass {
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rsRoot, rsParent, rsChild;
    private static String sql;

    public static void main(String args[]){
        //arraylist to fill with the whole structure, fill it by yourself =)))
        ArrayList<Object> menuArray = new ArrayList<Object>();
        
        try{
            conn = MySQLConnector.connect2db(); //connecting
            sql = "SELECT id_root,tx_root FROM menu_root"; //getting all roots
            ps = conn.prepareStatement(sql);
            rsRoot = ps.executeQuery();
            while(rsRoot.next()){ //running through roots
                String[] root = new String[2];
                root[0] = rsRoot.getString(1);
                root[1] = rsRoot.getString(2);
                System.out.println(root[1]);
                
                //getting parents for each root
                sql = "SELECT id_parent,tx_parent FROM menu_parent WHERE id_root = ?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,root[0]);
                rsParent = ps.executeQuery();
                while(rsParent.next()){ //running through parents
                    String[] parent = new String[2];
                    parent[0] = rsParent.getString(1);
                    parent[1] = rsParent.getString(2);
                    System.out.println("  "+parent[1]);
                    
                    //getting all children for each parent
                    sql = "SELECT id_child,tx_child FROM menu_child WHERE id_parent = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setString(1,parent[0]);
                    rsChild = ps.executeQuery();
                    while(rsChild.next()){ //running through children
                        String[] child = new String[2];
                        child[0] = rsChild.getString(1);
                        child[1] = rsChild.getString(2);
                        System.out.println("    "+child[1]);
                    }
                }
            }
        }catch(SQLException e){
            e.printStackTrace(System.out);
        }finally{
            try{
                ps.close();
                rsRoot.close();
                rsParent.close();
                rsChild.close();
                if(!conn.isClosed()){conn.close();}
            }catch(SQLException e){e.printStackTrace(System.out);}
        }
    }
}

class MySQLConnector {
    private static Connection conn;
    private static String url = "jdbc:mysql://localhost/ztest_menu"; //path to my database
    private static String user = "dbuser";
    private static String pwd = "dbpassword";
    
    public static Connection connect2db(){
        try{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, user, pwd);
        }catch(Exception e){
            e.printStackTrace(System.out);
        }
        return conn;
    }
} 

The result should look like this:

root0
  parent0_0
  parent0_1
    child0_1_0
  parent0_2
    child0_2_1
    child0_2_2
root1
root2
  parent2_3
  parent2_4
root3

P.S. For those who know all this already: I know there are more modern and optimized ways to do it. But this is basics. It's better to part from it.

[ranbahadur]

Hi Xeel,

The Code is working pretty well. Thanks for your help.......
Thanks & Regards,
Mike

[ranbahadur]

Hi Xeel,
Thanks for your help......
Thanks & Regards,
Mike

[Xeel]

hmm, I'm not sure I understood the question...

As I said before, I suppose, you could save the structure in a multidim array and then display it in your JSP. No need to run the actual query in there probably. But if you like it this way, it's ok.

Here is the direct way:
Save the class MySQLConnector as a separate class. Then just replace System.out-s with HTML code you need.

Now about trees in the examples. If you just want to look it the way a classical dir tree looks you could do it the way I showed before.

If you want to work it like on examples as well - you would need JS for this and fill the tree JS constructor with the data from our saved multidim array somewhow (depending on input format of the constructor) or whatever this script uses to configure the tree structure. In other words just write a class similar to my first example, and in JSP call for the function that returns the filled array. Now you could use it anyway you need.

[ranbahadur]

Thanks........

[ranbahadur]

Thanks.....

[Xeel]

here you go:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MyClass {
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rsRoot, rsParent, rsChild;
    private static String sql;

    public static void main(String args[]){
        ArrayList<Object> filledMenuArray = getMenuStructure();
        
        for(int i=0; i<filledMenuArray.size(); i=i+2){
            String[] root = (String[])filledMenuArray.get(i);
            System.out.println(root[1]);
            
            ArrayList<Object> alParents = (ArrayList<Object>)filledMenuArray.get(i+1);
            for(int j=0; j<alParents.size(); j=j+2){
                String[] parent = (String[])alParents.get(j);
                System.out.println("  "+parent[1]);
                
                ArrayList<String[]> alChildren = (ArrayList<String[]>)alParents.get(j+1);
                for(String[] child : alChildren){
                    System.out.println("    "+child[1]);
                }
            }
        }
    }
    
    static ArrayList<Object> getMenuStructure(){
        ArrayList<Object> menuArray = new ArrayList<Object>();
        
        try{
            conn = MySQLConnector.connect2db(); //connecting
            sql = "SELECT id_root,tx_root FROM menu_root"; //getting all roots
            ps = conn.prepareStatement(sql);
            rsRoot = ps.executeQuery();
            while(rsRoot.next()){ //running through roots
                String[] root = new String[2];
                root[0] = rsRoot.getString(1);
                root[1] = rsRoot.getString(2);
                menuArray.add(root); //adding current root data
                
                //getting parents for each root
                sql = "SELECT id_parent,tx_parent FROM menu_parent WHERE id_root = ?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,root[0]);
                
                ArrayList<Object> alParents = new ArrayList<Object>();
                rsParent = ps.executeQuery();
                while(rsParent.next()){ //running through parents
                    String[] parent = new String[2];
                    parent[0] = rsParent.getString(1);
                    parent[1] = rsParent.getString(2);
                    alParents.add(parent); //adding current parent data
                    
                    //getting all children for each parent
                    sql = "SELECT id_child,tx_child FROM menu_child WHERE id_parent = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setString(1,parent[0]);
                    
                    ArrayList<String[]> alChildren = new ArrayList<String[]>();
                    rsChild = ps.executeQuery();
                    while(rsChild.next()){ //running through children
                        String[] child = new String[2];
                        child[0] = rsChild.getString(1);
                        child[1] = rsChild.getString(2);
                        alChildren.add(child); //adding current child
                    }
                    alParents.add(alChildren); //adding children data under current parent
                }
                menuArray.add(alParents); //adding parents data under current root
            }
        }catch(SQLException e){
            e.printStackTrace(System.out);
        }finally{
            try{
                ps.close();
                rsRoot.close();
                rsParent.close();
                rsChild.close();
                if(!conn.isClosed()){conn.close();}
            }catch(SQLException e){e.printStackTrace(System.out);}
        }
        return menuArray;
    }
}

class MySQLConnector {
    private static Connection conn;
    private static String url = "jdbc:mysql://localhost/ztest_menu"; //path to my database
    private static String user = "dbuser";
    private static String pwd = "dbpassword";
    
    public static Connection connect2db(){
        try{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, user, pwd);
        }catch(Exception e){
            e.printStackTrace(System.out);
        }
        return conn;
    }
} 

[ranbahadur]

Hi Xeel,

I hope U would be fine. Thanks for your help. Its works fine.
Thanks & Regards,
Mike

[Xeel]

C'mon man!... Wouldn't you put any effort to it at all? Look at the code I've written for you. 99% is done already, the only thing left is to replace System.out's with JSP/JSTL tags....

[dlorde]

If you feed them too much rich food they may become dependent - better to give them the knowledge to forage for themselves...

Teachers open the door, but you must enter by yourself...
Chinese proverb

[ranbahadur]

Teachers must give their hands to special kids and show the right way........

Taiwanese Proverb