import java.sql.*;
import jet.datasource.*;
import java.util.*;

public class HierarchicalDatasetMetaData implements JRHierarchicalDatasetMetaData  
{		
	//connect database
	Connection connection;	
	//store branch name and each branch's ResultSetMetaData
	Hashtable colInfo = new Hashtable();

	//ResultSetMetaData of employee table
	ResultSetMetaData empMetaData = null;
	//ResultSetMetaData of orders table
	ResultSetMetaData orderMetaData = null;
	//ResultSetMetaData of orders detail table
	ResultSetMetaData detailMetaData = null;
			
	public HierarchicalDatasetMetaData()
	{	
		try 
		{
			//connect database
            		Class.forName("org.hsqldb.jdbcDriver");            
            		connection = DriverManager.getConnection("jdbc:hsqldb:C:\\JReport\\Demo\\db\\DemoDB", "sa", "");            			
        	}
        	catch (ClassNotFoundException ex) 
		{
            		System.err.println("Cannot find the database driver classes.");
            		System.err.println(ex);
        	}
        	catch (SQLException ex) {
           		System.err.println("Cannot connect to this database.");
            		System.err.println(ex);
        	}
		getMetaData();			
	}

	private void getMetaData()
	{
		try{
			//set ResultSetMetaData for employee table
			String sql = "select EMPLOYEEID,LASTNAME from EMPLOYEE";
			Statement statement1 = connection.createStatement();						
			ResultSet rs1 = statement1.executeQuery(sql);
			empMetaData = rs1.getMetaData();					
			colInfo.put("Employee",empMetaData);
						
			//set ResultSetMetaData for orders table
			sql = "select ORDERID,CUSTOMERID,ORDERDATE,SHIPDATE from ORDERS";
			Statement statement2 = connection.createStatement();
			ResultSet rs2 = statement2.executeQuery(sql);
			orderMetaData = rs2.getMetaData();							
			colInfo.put("Employee.Orders",orderMetaData);
						
			//set ResultSetMetaData for orders detail table
			
			sql = "select PRODUCTID,UNITPRICE,QUANTITY,DISCOUNT from ORDERS_DETAIL";
			Statement statement3 = connection.createStatement();
			ResultSet rs3 = statement3.executeQuery(sql);
			detailMetaData = rs3.getMetaData();
			colInfo.put("Employee.Orders.OrdersDetail",detailMetaData);
		}catch(SQLException e){
			e.printStackTrace();
		}		
	}

	/** 
	 * Return root node names
	 */
	public String getRoot() 
	{		
		return "Employee";
	}
    
	/**
	 * Return the name of the parent.
	 * @param name is full name of node.
	 */
	public String getParentName(String name)
	{ 	
		int idx = name.lastIndexOf(".");
		if(idx >= 0)
			return name.substring(0,name.lastIndexOf("."));
		else 
			return "";
	}

	/**
	 * Return all leaf node names.
	 * @param parentName is full name of node.
	 */
	public String[] getLeafNames(String parentName) 
	{			
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			String[] leafNames = new String[metadata.getColumnCount()];;
			for(int i = 0 ; i < metadata.getColumnCount(); i++)		
				leafNames[i] = metadata.getColumnName(i + 1);			
			return leafNames;		
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
    
	/**
	 * Return the number of leaves of the specified branch node.
	 * @param parentName is full name of node.
	 */
	public int getLeafCount(String parentName)
	{
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			return metadata.getColumnCount();		
		}catch(SQLException e){
			e.printStackTrace();
		}
		return 0;
	}	

	/**
	 * Return all branch names for the specified parent node. 
	 * If there are not branch node in this parent node, then return null.
	 * @param parentName is full name of node.
	 */
	public String[] getBranchNames(String parentName) 
	{		
		if(parentName.equals("Employee"))
			return new String[] {"Orders"};
		else if(parentName.equals("Employee.Orders"))
			return new String[] {"OrdersDetail"};		
		else 
			return new String[] {};
	}

	/**
	 * Return the data type of the leaf.
	 * leaf type should be java.sql.Types.	 
	 * @param parentName is full name of node.
	 * @param leafName is real name of node.
	 */
	public int getLeafType(String parentName, String leafName)
	{		
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			for(int i = 0; i < metadata.getColumnCount(); i++)
			{
				if(leafName.equals(metadata.getColumnName(i + 1)))
					return metadata.getColumnType(i + 1);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return 12;		
	}

	/**
	 * Return the name of data type.
	 */
	public String getLeafTypeName(String parentName, String leafName)
	{		
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			for(int i = 0; i < metadata.getColumnCount(); i++)
			{
				if(leafName.equals(metadata.getColumnName(i + 1)))
					return metadata.getColumnTypeName(i + 1);
			}		
		}catch(SQLException e){
			e.printStackTrace();
		}
		return "VARCHAR";
	}

	/**
	 * Return the precision of the leaf.
	 * @param parentName is full name of node.
	 * @param leafName is real name of node.
	 */
	public int getPrecision(String parentName, String leafName)
	{		
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			for(int i = 0; i < metadata.getColumnCount(); i++)
			{	
				if(leafName.equals(metadata.getColumnName(i + 1)))
				{	
					int Dprecision = metadata.getPrecision(i + 1);
					
					if(Dprecision < 1)
					return 100;		
					
					/*The HSQL driver cannot get the precision of the columns, in order to get the precision, a default value 100 is specifed here.*/
					
					return Dprecision;
				}
			}		
		}catch(SQLException e){
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * Return the scale of the leaf.
	 * @param parentName is full name of node.
	 * @param leafName is real name of node.
	 */	
	public int getScale(String parentName, String leafName)
	{		
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			for(int i = 0; i < metadata.getColumnCount(); i++)
			{
				if(leafName.equals(metadata.getColumnName(i + 1)))
					return metadata.getScale(i + 1);
			}		
		}catch(SQLException e){
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * Return the nullable state of the leaf.
	 * @param parentName is full name of node.
	 * @param leafName is real name of node.
	 */
	public int isNullable(String parentName, String leafName)
	{		
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			for(int i = 0; i < metadata.getColumnCount(); i++)
			{
				if(leafName.equals(metadata.getColumnName(i + 1)))
					return metadata.isNullable(i + 1);
			}		
		}catch(SQLException e){
			e.printStackTrace();
		}
		return 1;
	}
    
	/**
	 * Return the currency state of the leaf.
	 * @param parentName is full name of node.
	 * @param leafName is real name of node.
	 */
	public boolean isCurrency(String parentName, String leafName)
	{	
		ResultSetMetaData metadata = (ResultSetMetaData)colInfo.get(parentName);
		try{
			for(int i = 0; i < metadata.getColumnCount(); i++)
			{
				if(leafName.equals(metadata.getColumnName(i + 1)))
					return metadata.isCurrency(i + 1);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * If the leaf is multiple values, then return true. Otherwise, return false.
	 * @param parentName is full name of node.
	 * @param leafName is real name of node.
	 */
	public boolean isArray(String s1, String s2)
	{
		return false;
	}
}
