Nth Level of Category or Location Tree Generation

code used to access the self-join table with parent child (single table) using java.

---

Database structure
Location Table
where
Lid is location id or you can use as category Id
Pid is parent id
Name is location name or category name

	Lid	Pid	Name
1	0	S1
2	0	S2
4	1	S1_1
5	1	S1_2
6	2	S2_1
7	6	S2_1_1

---

package com.joshi;



import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;



public class ParentChildNthLevelTree  {



	Connection con = BaseDao.getDbConnection();

	PreparedStatement pstmt;

	ParentChildNthLevelTree() {

		//Statement stmt;

		try {

			//stmt = con.createStatement();

			//ResultSet rs = stmt.executeQuery("SELECT * FROM location  WHERE pid=0 order by lid");

			pstmt = con.prepareStatement("SELECT * FROM location  WHERE pid=? order by lid");

			

			/*while(rs.next()) {

				int lid = rs.getInt("lid");

				int pid = rs.getInt("pid");

				String name = rs.getString("name");

				//LocationKeyVo keyVo=new LocationKeyVo(lid,pid);

				//LocationVo vo = new LocationVo(keyVo,name);

			//	System.out.print("\n"+lid);

			//	System.out.print("\t"+pid);

				System.out.println(name);

				childFound(lid,0);

			}*/

				

			childFound(0,0);

			

			

		} catch (SQLException e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		}

		

	}

	

	public void childFound(int p_pid,int depth) {

		try {

			ArrayList al = new ArrayList();

			pstmt.clearParameters();

			pstmt.setInt(1, p_pid);

			ResultSet rs = pstmt.executeQuery();

			

			while(rs.next()) {

				int lid = rs.getInt("lid");

				int pid = rs.getInt("pid");

				String name = rs.getString("name");

				al.add(new LocationVo(new LocationKeyVo(lid,pid),name));

				//childFound(lid);

			}

			depth++;

			for (LocationVo vo : al) {

				

				//System.out.println("**"+depth+"**");

				for(int i=0;i					System.out.print("\t");

				}

				System.out.println(vo.toString());

				

				childFound(vo.keyVo.locId,depth);

				

			}

			depth--;

			

		} catch (SQLException e) {

			System.out.println("----->");

			e.printStackTrace();

		}

	}

	

	public static void main(String[] args) {

		ParentChildNthLevelTree test = new ParentChildNthLevelTree();

		

	}



}



class LocationKeyVo {

	int locId;

	int parentId;

	

	public LocationKeyVo(int lid,int pid) {

		locId=lid;

		parentId=pid;

	}



	@Override

	public String toString() {

		StringBuffer objStrBuffer = new StringBuffer();

		//objStrBuffer.append("\n");

		objStrBuffer.append("\tLocation Id = ").append(locId);

		objStrBuffer.append("\tParent Id = ").append(parentId);

		//objStrBuffer.append("\n");

		

		return objStrBuffer.toString();

	

	}

}



class LocationVo

{

	LocationKeyVo keyVo;

	String name;

	

	LocationVo(LocationKeyVo p_keyVo,String p_name) {

		keyVo=p_keyVo;

		name=p_name;

	}

	

	@Override

	public String toString() {

		StringBuffer objStrBuffer = new StringBuffer();

		//objStrBuffer.append("\n");

		//objStrBuffer.append(keyVo.toString());

		//objStrBuffer.append("\tLocation Name = ");

		objStrBuffer.append(name);

		//objStrBuffer.append("\n");

		return objStrBuffer.toString();

	

	}

	

}