Android Application to Read Data from Web Service

1- Mainactivity

package raosterapplication.siddhu.roaster.com.roasterandroid;

import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.TableLayout;
import android.widget.TextView;
import android.widget.Toast;

import android.os.AsyncTask;
import org.ksoap2.SoapEnvelope;
import org.ksoap2.serialization.SoapObject;
import org.ksoap2.serialization.SoapSerializationEnvelope;
import org.ksoap2.transport.HttpTransportSE;
import android.graphics.Color;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.TableRow;
import android.widget.TableRow.LayoutParams;
import java.util.Vector;

public class MainActivity extends AppCompatActivity implements
AdapterView.OnItemSelectedListener{

TextView label1;
Button button2;
Spinner spinner2;
EditText userNameText1;
String searchName, travellerName, travellerLocation;

String soapResponse, soapTravellerResponse;
TableLayout travellerInfoDataTable;

String DROP_TIME="";
String NAME= "";
String GENDER= "";
String DROP_ADDRESS= "";
String LOCATION= "";
String CABNO= "";

private SoapObject[] userList;// = { "India", "USA", "China", "Japan", "Other", };
private String[] userListArray = {"Select Data.."};

private final String NAMESPACE = "http://main.siddhu.com";
private final String URL = "http://XXXX:YYYY/mockExposeWebServiceSoap11Binding?wsdl";
private final String SOAP_ACTION = "http://main.siddhu.com/getCabInformations";
private final String METHOD_NAME = "getCabInformations";
private final String CAB_METHOD_NAME = "getCabInfo";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
setSupportActionBar(toolbar);
label1 = (TextView) findViewById(R.id.nameLabel1);
userNameText1= (EditText) findViewById(R.id.userNameText1);
button2 = (Button) findViewById(R.id.searchButton);
button2.setOnClickListener(new clicker());

}

//--------------------------------------------------
class clicker implements Button.OnClickListener
{
public void onClick(View v) {

if("".equals(userNameText1.getText().toString()))
{
Toast.makeText(getApplicationContext(), "Please Enter Name to Search", Toast.LENGTH_SHORT).show();
}else if(null != userNameText1.getText())
{

searchName = userNameText1.getText().toString();
travellerInfoDataTable = (TableLayout) findViewById(R.id.main_table);
travellerInfoDataTable.removeAllViews();
//Create instance for AsyncCallWS
AsyncCallWS task = new AsyncCallWS();
//Call execute
task.execute();

}

}
} //------------------------------------------------------

private class AsyncCallWS extends AsyncTask {
@Override
protected Void doInBackground(String... params) {
//Log.i(TAG, "doInBackground");
getSoapResponse(searchName);
return null;
}

@Override
protected void onPostExecute(Void result) {

//System.out.println("soapResponse >>>>>>>>>>>>>>>>>>>>>" + soapResponse);
if(null == soapResponse){
Toast.makeText(getApplicationContext(), "No User Found",Toast.LENGTH_SHORT).show();

}
else{
spinner2 = (Spinner) findViewById (R.id.spinner2);
spinner2.setOnItemSelectedListener(MainActivity.this);
ArrayAdapter adapterScrum = new ArrayAdapter(MainActivity.this,android.R.layout.simple_spinner_item, userListArray);
adapterScrum.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinner2.setAdapter(adapterScrum);
}
}
@Override
protected void onPreExecute() {
//Log.i(TAG, "onPreExecute");
Toast.makeText(getApplicationContext(), "Fetching Result...", Toast.LENGTH_SHORT).show();
}

@Override
protected void onProgressUpdate(Void... values) {
// Log.i(TAG, "onProgressUpdate");
}

}

public void getSoapResponse(String searchName) {

//Create request
SoapObject request = new SoapObject(NAMESPACE, METHOD_NAME);

request.addProperty("name",searchName);
//Add the property to request object

//Create envelope
SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(
SoapEnvelope.VER11);
//Set output SOAP object
envelope.setOutputSoapObject(request);
//Create HTTP call object
HttpTransportSE androidHttpTransport = new HttpTransportSE(URL);

try {
//Invole web service
androidHttpTransport.call(SOAP_ACTION, envelope);
//Get the response

Vector result = (Vector)envelope.getResponse();
//scrumListArray = (String) result.toArray();
Object[] soapObject = result.toArray(new Object[result.size()]);
userList = new SoapObject[soapObject.length];
userListArray = new String[soapObject.length+1];
userListArray[0] = "Please Select Traveller Name";

for(int i=0;i{
userList[i] = (SoapObject)soapObject[i];
System.out.println("userList:" + userList[i]);
System.out.println("userList.tostring:" + userList[i].toString());
System.out.println("userList.tostring:" + userList[i].getProperty("NAME").toString()+ "-" + userList[i].getProperty("LOCATION").toString());
userListArray[i+1] = userList[i].getProperty("NAME").toString()+ "-" + userList[i].getProperty("LOCATION").toString();
System.out.println("userListArray:" + userListArray[i+1]);
System.out.println("userListArray.tostring:" + userListArray[i+1].toString());

}

//System.out.println("result:" + result);
soapResponse = result.toString();

} catch (Exception e) {
Toast.makeText(getApplicationContext(), "System Error Please try after some time !!!!!",Toast.LENGTH_SHORT).show();
e.printStackTrace();
}

}

@Override
public void onItemSelected(AdapterView arg0, View arg1, int position,long id) {
Toast.makeText(getApplicationContext(),userListArray[position] ,Toast.LENGTH_LONG).show();
if(!"Please Select Traveller Name".equalsIgnoreCase(userListArray[position]))
{
String[] inputStingArray = userListArray[position].split("-");
travellerName = inputStingArray[0];
travellerLocation = inputStingArray[1];

//Create instance for AsyncCallWS for Getting Sprint Task informations
travellerInfoDataTable = (TableLayout) findViewById(R.id.main_table);
travellerInfoDataTable.removeAllViews();

TableRow tr_head = new TableRow(this);
//tr_head.setId(20);
tr_head.setBackgroundColor(Color.GRAY);
tr_head.setLayoutParams(new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TextView label_list_of_kpi = new TextView(this);
//label_date.setId(20);
label_list_of_kpi.setText("Name");
label_list_of_kpi.setTextColor(Color.BLUE);
label_list_of_kpi.setPadding(5, 5, 5, 5);
tr_head.addView(label_list_of_kpi);// add the column to the table row here

TextView label_actual_kpi_value = new TextView(this);
//label_weight_kg.setId(21);// define id that must be unique
label_actual_kpi_value.setText("Descriptions"); // set the text for the header
label_actual_kpi_value.setTextColor(Color.BLUE); // set the color
label_actual_kpi_value.setPadding(5, 5, 5, 5); // set the padding (if required)
tr_head.addView(label_actual_kpi_value); // add the column to the table row here

travellerInfoDataTable.addView(tr_head, new TableLayout.LayoutParams( LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

//Create instance for AsyncCallWS
if ((!"".equals(travellerName) && !"Select Data..".equals(travellerName)) && (!"".equals(travellerLocation) && !"Select Data..".equals(travellerLocation))) {
AsyncTravellerInfoCallWS travellerInformation = new AsyncTravellerInfoCallWS();
//Call execute
travellerInformation.execute();
travellerInfoDataTable = (TableLayout) findViewById(R.id.main_table);
travellerInfoDataTable.removeAllViews();
}
}

}

private class AsyncTravellerInfoCallWS extends AsyncTask {
@Override
protected Void doInBackground(String... params) {
//Log.i(TAG, "doInBackground");
getTravellerInfoSoapResponse(travellerName, travellerLocation);
return null;
}

@Override
protected void onPostExecute(Void result) {

//System.out.println("soapResponse >>>>>>>>>>>>>>>>>>>>>" + soapResponse);
if (null == soapTravellerResponse) {
Toast.makeText(getApplicationContext(), "No Record Found", Toast.LENGTH_SHORT).show();

} else {
//Fill details of information in Table

// Create the table row
TableRow tr = new TableRow(getApplicationContext());
tr.setBackgroundColor(Color.LTGRAY);
tr.setLayoutParams(new LayoutParams(LayoutParams.FILL_PARENT,LayoutParams.WRAP_CONTENT));

//Create two columns to add as table data
// Create a TextView to add date
TextView labelDropTime = new TextView(getApplicationContext());
labelDropTime.setText("Drop Time");
labelDropTime.setPadding(2, 0, 5, 0);
labelDropTime.setTextColor(Color.BLACK);
tr.addView(labelDropTime);

TextView labelactualDropTime = new TextView(getApplicationContext());
labelactualDropTime.setText(DROP_TIME);
labelactualDropTime.setTextColor(Color.BLACK);
tr.addView(labelactualDropTime);

travellerInfoDataTable.addView(tr, new TableLayout.LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TableRow trOne = new TableRow(getApplicationContext());
trOne.setBackgroundColor(Color.LTGRAY);
trOne.setLayoutParams(new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TextView labelTravellerName = new TextView(getApplicationContext());
labelTravellerName.setText("Traveller Name");
labelTravellerName.setPadding(2, 0, 5, 0);
labelTravellerName.setTextColor(Color.BLACK);
trOne.addView(labelTravellerName);

TextView labelactualTravellerName = new TextView(getApplicationContext());
labelactualTravellerName.setText(NAME);
labelactualTravellerName.setTextColor(Color.BLACK);
trOne.addView(labelactualTravellerName);
travellerInfoDataTable.addView(trOne, new TableLayout.LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TableRow trTwo = new TableRow(getApplicationContext());
trTwo.setBackgroundColor(Color.LTGRAY);
trTwo.setLayoutParams(new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TextView labelGender = new TextView(getApplicationContext());
labelGender.setText("GENDER");
labelGender.setPadding(2, 0, 5, 0);
labelGender.setTextColor(Color.BLACK);
trTwo.addView(labelGender);

TextView labelactualGender= new TextView(getApplicationContext());
labelactualGender.setText(GENDER);
labelactualGender.setTextColor(Color.BLACK);
trTwo.addView(labelactualGender);
travellerInfoDataTable.addView(trTwo, new TableLayout.LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TableRow trThree = new TableRow(getApplicationContext());
trThree.setBackgroundColor(Color.LTGRAY);
trThree.setLayoutParams(new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TextView labelDropAddress = new TextView(getApplicationContext());
labelDropAddress.setText("DROP Address");
labelDropAddress.setPadding(2, 0, 5, 0);
labelDropAddress.setTextColor(Color.BLACK);
trThree.addView(labelDropAddress);

TextView labelactuallabelDropAddress= new TextView(getApplicationContext());
labelactuallabelDropAddress.setText(DROP_ADDRESS);
labelactuallabelDropAddress.setTextColor(Color.BLACK);
trThree.addView(labelactuallabelDropAddress);
travellerInfoDataTable.addView(trThree, new TableLayout.LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TableRow trSix = new TableRow(getApplicationContext());
trSix.setBackgroundColor(Color.LTGRAY);
trSix.setLayoutParams(new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TextView labelLocation = new TextView(getApplicationContext());
labelLocation.setText("LOCATION");
labelLocation.setPadding(2, 0, 5, 0);
labelLocation.setTextColor(Color.BLACK);
trSix.addView(labelLocation);

TextView labelactualLocation= new TextView(getApplicationContext());
labelactualLocation.setText(LOCATION);
labelactualLocation.setTextColor(Color.BLACK);
trSix.addView(labelactualLocation);
// finally add this to the table row

travellerInfoDataTable.addView(trSix, new TableLayout.LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TableRow trSeven = new TableRow(getApplicationContext());
trSeven.setBackgroundColor(Color.LTGRAY);
trSeven.setLayoutParams(new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

TextView labelCabNo = new TextView(getApplicationContext());
labelCabNo.setText("CABNO");
labelCabNo.setPadding(2, 0, 5, 0);
labelCabNo.setTextColor(Color.BLACK);
trSeven.addView(labelCabNo);

TextView labelactualCabNo= new TextView(getApplicationContext());
labelactualCabNo.setText(CABNO);
labelactualCabNo.setTextColor(Color.BLACK);
trSeven.addView(labelactualCabNo);
// finally add this to the table row

}
}

@Override
protected void onPreExecute() {
//Log.i(TAG, "onPreExecute");
Toast.makeText(getApplicationContext(), "Fetching Traveller Record Please wait...", Toast.LENGTH_SHORT).show();
}

@Override
protected void onProgressUpdate(Void... values) {
// Log.i(TAG, "onProgressUpdate");
}
}

public void getTravellerInfoSoapResponse(String travellerName, String travellerLocation) {

//Create request
SoapObject request = new SoapObject(NAMESPACE, CAB_METHOD_NAME);

request.addProperty("name",travellerName);
request.addProperty("location",travellerLocation);
//Add the property to request object

//Create envelope
SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(
SoapEnvelope.VER11);
//Set output SOAP object
envelope.setOutputSoapObject(request);
//Create HTTP call object
HttpTransportSE androidHttpTransport = new HttpTransportSE(URL);

try {
//Invole web service
androidHttpTransport.call(SOAP_ACTION, envelope);
//Get the response

SoapObject result = (SoapObject)envelope.getResponse();

System.out.println("result:" + result);

if(!result.getProperty("DROP_TIME").toString().equals("") || null != result.getProperty("DROP_TIME").toString())
{
DROP_TIME = result.getProperty("DROP_TIME").toString();

//Toast.makeText(getApplicationContext(), "Redirecting...",Toast.LENGTH_SHORT).show();
}
if(!result.getProperty("NAME").toString().equals("") || null != result.getProperty("NAME").toString())
{
NAME = result.getProperty("NAME").toString();
}
if(!result.getProperty("GENDER").toString().equals("") || null != result.getProperty("GENDER").toString())
{
GENDER = result.getProperty("GENDER").toString();
}
if(!result.getProperty("DROP_ADDRESSS").toString().equals("") || null != result.getProperty("DROP_ADDRESSS").toString())
{
DROP_ADDRESS = result.getProperty("DROP_ADDRESSS").toString();
}

if(!result.getProperty("LOCATION").toString().equals("") || null != result.getProperty("LOCATION").toString())
{
LOCATION = result.getProperty("LOCATION").toString();
}

if(!result.getProperty("CABNO").toString().equals("") || null != result.getProperty("CABNO").toString())
{
CABNO = result.getProperty("CABNO").toString();
}

soapTravellerResponse = result.toString();

} catch (Exception e) {
Toast.makeText(getApplicationContext(), "System Error Please try after some time !!!!!",Toast.LENGTH_SHORT).show();
e.printStackTrace();
}
}

@Override
public void onNothingSelected(AdapterView arg0) {
// TODO Auto-generated method stub
Toast.makeText(getApplicationContext(),"No Value selected" ,Toast.LENGTH_LONG).show();

}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();

//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}

return super.onOptionsItemSelected(item);
}
}

2- activity_main.xml

xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:fitsSystemWindows="true"
tools:context="raosterapplication.siddhu.roaster.com.roasterandroid.MainActivity">

android:layout_width="match_parent"
android:layout_height="wrap_content"
android:theme="@style/AppTheme.AppBarOverlay">

android:id="@+id/toolbar"
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
android:background="?attr/colorPrimary"
app:popupTheme="@style/AppTheme.PopupOverlay" />

3- AndroidManifest.xml

package="raosterapplication.siddhu.roaster.com.roasterandroid">

android:allowBackup="true"
android:icon="@drawable/cab"
android:label="@string/app_name"
android:supportsRtl="true"
android:theme="@style/AppTheme">
android:name=".MainActivity"
android:label="@string/app_name"
android:theme="@style/AppTheme.NoActionBar">

4- content.xml

xmlns:app="http://schemas.android.com/apk/res-auto"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:weightSum="1">

android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_gravity="center_horizontal"
android:scrollbarAlwaysDrawVerticalTrack="true"
android:scrollIndicators="top|left|bottom|start|end|right"
android:background="@drawable/flower"
android:scrollbarStyle="outsideInset">

android:id="@+id/userNameText1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:ems="10"
android:inputType="textNoSuggestions"
android:clickable="false"
android:editable="true"
android:enabled="true"
android:layout_above="@+id/searchButton"
android:layout_toRightOf="@+id/nameLabel1"
android:layout_toEndOf="@+id/nameLabel1" />

android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/spinner2"
android:fadeScrollbars="false"
android:visibility="visible"
android:layout_centerVertical="true"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />

android:id="@+id/textView8"
android:layout_width="210px"
android:layout_height="100px"
android:text="Select Traveller Name"
android:textSize="17sp"
android:typeface="serif"
android:textStyle="italic"
android:textColor="#0015ff"
android:layout_x="10px"
android:layout_y="82px"
android:theme="@style/AppTheme"
android:autoText="false"
android:background="#ffffff"
android:layout_above="@+id/spinner2"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true" />

android:stretchColumns="0,1"
android:id="@+id/main_table" android:layout_weight="0.11" android:layout_height="wrap_content" android:layout_width="match_parent"
android:isScrollContainer="true"
android:minHeight="80dp"
android:orientation="vertical"
android:layout_below="@+id/spinner2"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_marginTop="89dp">

JSP Code to read data from xls and insert data in MYSQL Server

JSP Code to read data from xls and insert data in MYSQL Server

1- DbConnection

package DB;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class DbConnection {

// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/";

// Database credentials
static final String USER = "siddhu";
static final String PASS = "siddhu";

public Connection getConnection()
{
/*try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

System.out.println("Where is your Oracle JDBC Driver?");
e.printStackTrace();

}

System.out.println("Oracle JDBC Driver Registered!");

Connection connection = null;

try {

connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe", "siddhu","siddhu");

} catch (SQLException e) {

System.out.println("Connection Failed! Check output console");
e.printStackTrace();

}

if (connection != null) {
return connection;
} else {
return null;
}*/
try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

System.out.println("Where is your MySQL Driver?");
e.printStackTrace();

}

System.out.println("MySQL JDBC Driver Registered!");

Connection connection = null;

try {

connection = DriverManager.getConnection(DB_URL,USER,PASS);

} catch (SQLException e) {

System.out.println("Connection Failed! Check output console");
e.printStackTrace();

}

if (connection != null) {
return connection;
} else {
return null;
}
}
}

2- ReadDataFromxls.jsp

-- %@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"  
   pageEncoding="ISO-8859-1"%>  
 -- !DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
 -- %@ page import ="java.util.Date" %>  
 -- %@ page import ="java.io.*" %>  
 -- %@ page import ="java.io.FileNotFoundException" %>  
 -- %@ page import ="java.io.IOException" %>  
 -- %@ page import ="java.util.Iterator" %>  
 -- %@ page import ="java.util.ArrayList" %>  
 //Apache POI Libraries  
 -- %@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>  
 -- %@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>  
 -- %@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>  
 -- %@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>  
 -- %@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>  
 -- html>  
 -- head>  
 -- meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">  
 -- title>Insert title here-- /title>  
 -- /head>  
 -- body>  
 -- jsp:useBean id="connection" class="DB.DB_Connection" scope="page">  
   -- jsp:setProperty name="connection" property="*"/>  
 -- /jsp:useBean>  
 -- %!    
 Connection con;  
 PreparedStatement ps=null;  
 public static ArrayList readExcelFile(String fileName)  
 {  
   /** --Define a ArrayList  
     --Holds ArrayList Of Cells  
    */  
   ArrayList cellArrayLisstHolder = new ArrayList();  
   try{  
   /** Creating Input Stream**/  
     FileInputStream myInput = new FileInputStream(fileName);  
   /** Create a POIFSFileSystem object**/  
   POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);  
   /** Create a workbook using the File System**/  
    HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);  
    /** Get the first sheet from workbook**/  
   HSSFSheet mySheet = myWorkBook.getSheetAt(0);  
   /** We now need something to iterate through the cells.**/  
    Iterator rowIter = mySheet.rowIterator();  
    while(rowIter.hasNext()){  
      HSSFRow myRow = (HSSFRow) rowIter.next();  
      Iterator cellIter = myRow.cellIterator();  
      ArrayList cellStoreArrayList=new ArrayList();  
      while(cellIter.hasNext()){  
        HSSFCell myCell = (HSSFCell) cellIter.next();  
        cellStoreArrayList.add(myCell);  
      }  
      cellArrayLisstHolder.add(cellStoreArrayList);  
    }  
   }catch (Exception e){e.printStackTrace(); }  
   return cellArrayLisstHolder;  
 }%>  
 -- %  
 String fileName="testExcel.xls"; //testExcel.xls Excel File name  
 //Read an Excel File and Store in a ArrayList  
 ArrayList dataHolder=readExcelFile(fileName);  
 //Print the data read  
 //printCellDataToConsole(dataHolder);  
 con=connection.getConn();  
 String query="insert into Student values(?,?,?)";  
 ps=con.prepareStatement(query);  
 int count=0;  
 ArrayList cellStoreArrayList=null;  
 //For inserting into database  
 for (int i=1;i --  dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);  
     ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());  
     ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());  
     ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());  
    count= ps.executeUpdate();  
     System.out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "t");  
     }  
 //For checking data is inserted or not?  
   if(count>0)  
     { %>  
         Following deatils from Excel file have been inserted in student table of database  
           -- table>  
             -- tr>  
               -- th>Student's Name-- /th>  
               -- th>Class-- /th>  
               -- th>Age-- /th>  
             -- /tr>  
     -- % for (int i=1;i --  dataHolder.size(); i++) {  
   cellStoreArrayList=(ArrayList)dataHolder.get(i);%>  
   -- tr>  
     -- td>-- %=((HSSFCell)cellStoreArrayList.get(0)).toString() %>-- /td>  
     -- td>-- %=((HSSFCell)cellStoreArrayList.get(1)).toString() %>-- /td>  
     -- td>-- %=((HSSFCell)cellStoreArrayList.get(2)).toString() %>-- /td>  
   -- /tr>  
     -- %}  
    }  
   else  
   {%>  
   -- center> Details have not been inserted!!!!!!!!!-- /center>  
   -- %  }  %>  
     -- /table>  
 -- /body>  
 -- /html>  

3- MYSQL Script
CREATE SCHEMA `testschema` ;

CREATE TABLE `testschema`.`TEST` (
`ID` INT NOT NULL COMMENT 'ID for rows',
`DT` VARCHAR(45) NOT NULL COMMENT 'DROP Time for the Cab',
`NAME` VARCHAR(45) NULL COMMENT 'NAME of the Candidate to Travel',
`G` VARCHAR(45) NULL COMMENT 'GENDER of the Candidate to Travel',
`TEXT1` VARCHAR(45) NULL COMMENT 'DROP ADDRESS of the Candidate',
`TEXT2` VARCHAR(45) NULL COMMENT 'Drop Location of the candidate',
`TEXT3` VARCHAR(45) NULL COMMENT 'Cab no ',
PRIMARY KEY (`ID`))
COMMENT = 'TESTCABDETAILS Table';

ALTER TABLE `roaster`.`TEST`
CHANGE COLUMN `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID for rows' ;

OpenXAVA Ajax Frame work to create JAVA Web application

Would like to share with you OpenXava an AJAX Java Framework for Rapid Development of Enterprise Web Applications.

With this frame work developer just need to create Entity Class and rest of the web application effort is taken care by Frame work.

For example lets say we had a table name as Login having some fild in DB (It support both Oracle and MYSQL) we had used MYSQL.

We just need to create a class given below and we can get Web application having functionality like Add, Update, Delete, Table dispaly with Data, Sorting, Searching, Reporting PDF and XLS, Formating Report ETc.

package com.scrum.model;

import java.io.*;

import javax.persistence.*;
import org.openxava.annotations.*;

@Entity // This marks Project class as an entity

public class Login implements Serializable{

@Id
@Column(length=11)
private int loginId;

@Column(length=45)
@Required
private String userName;

@Column(length=45)
@Required
private String userPassword;
@Column(length=11)
@Required
private int prjId;
public int getLoginId() {
return loginId;
}
public void setLoginId(int loginId) {
this.loginId = loginId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public int getPrjId() {
return prjId;
}
public void setPrjId(int prjId) {
this.prjId = prjId;
}
@Override
public String toString() {
return "Login [loginId=" + loginId + ", userName=" + userName + ", userPassword=" + userPassword + ", prjId="
+ prjId + "]";
}
}
Surprise to belive ..let's do some handon exercise now

Step -1 Download OpenXava frame work from

http://www.openxava.org/

Step 2- Extract donwnloaded Zip

Step 4- Open your installed eclipse and switch to the workspace provided in zip extract

i.e. C:\openxava-5.5\workspace

Step 5:- Now either you can try existing project provided by Openxava for learning

http://openxava.wikispaces.com/quick-start_en

Or you can build your own project as we are going to do it now

Enter required information and it will create project Folder for you as shown below.

Step 5:- As we are using MY SQL as DB and we want our web server to connect it when it start up for that open the file

C:\openxava-5.5\tomcat\conf\context.xml

and add following line in it
type="javax.sql.DataSource"
maxActive="20" maxIdle="5" maxWait="10000"
username="siddhu" password="siddhu"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/"/>

Step 6:- Add Server in Eclipse as given below (Select the Server which given by OpenXAVA)
C:\openxava-5.5\tomcat

Step 7:- Create package *.*.model in your source folder and keep our JAVA class inside it.

Please not
A:- Name of the JAVA class must be similar to the name of the Table
B:- Name of the field must be similar to the Colum of Table
C:- Your Table must have mandatory PK key.
D:- Use annocation i.e. @Entity to define this class can be linked by Frame work with Table in DB.

package com.scrum.model;

import java.io.*;

import javax.persistence.*;
import org.openxava.annotations.*;

@Entity // This marks Project class as an entity

public class Login implements Serializable{

@Id
@Column(length=11)
private int loginId;

@Column(length=45)
@Required
private String userName;

@Column(length=45)
@Required
private String userPassword;
@Column(length=11)
@Required
private int prjId;
public int getLoginId() {
return loginId;
}
public void setLoginId(int loginId) {
this.loginId = loginId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public int getPrjId() {
return prjId;
}
public void setPrjId(int prjId) {
this.prjId = prjId;
}
@Override
public String toString() {
return "Login [loginId=" + loginId + ", userName=" + userName + ", userPassword=" + userPassword + ", prjId="
+ prjId + "]";
}

}

Step 8 :- Make changes in following two files

C:\openxava-5.5\workspace\ScrumAndroidWebApplication\xava\application.xml

Add following line in it i.e. android stand here for our Schema name.

class="org.openxava.actions.SetDefaultSchemaAction">

C:\openxava-5.5\workspace\ScrumAndroidWebApplication\xava\controllers.xml

Step 9 :- Run you created apllication on tomcat server of Eclipse and you will be able to see below screen

Files that are needed to change in Tomcat to expose Apache Axis Web Service.

1- In axis.xml in side /webapps/YOURPROJECT/WEB-INF/conf we had removed addressing line

2- Added permission inside /conf/catalina.policy
//by siddhu
permission java.lang.RuntimePermission "setContextClassLoader";
permission java.lang.RuntimePermission "createClassLoader";
permission java.lang.RuntimePermission "getProtectionDomain";
permission java.lang.RuntimePermission "getClassLoader";
permission java.lang.RuntimePermission "modifyThreadGroup";
permission java.lang.RuntimePermission "modifyThread";
permission java.lang.RuntimePermission "shutdownHooks";
permission java.lang.RuntimePermission "accessDeclaredMembers";
permission java.lang.RuntimePermission "loadLibrary.*";
permission java.util.PropertyPermission "org.apache.axis2.classloader.JarFileClassLoader", "read";
permission java.util.PropertyPermission "org.apache.ws.commons.extensions.ExtensionRegistry", "read";

permission java.lang.reflect.ReflectPermission "suppressAccessChecks";
permission java.net.SocketPermission "YOURDBIPADDRESS:PORT", "connect,resolve";

Scrum MYSQL Script

CREATE SCHEMA `android` ;
==================

CREATE TABLE `android`.`PROJECT` (
  `PRJID` INT NOT NULL COMMENT 'Project ID for Projects',
  `PRJNAME` VARCHAR(45) NOT NULL COMMENT 'Project Name',
  `ESCALATION1` VARCHAR(45) NULL COMMENT 'Escalation 1',
  `ESCALATION2` VARCHAR(45) NULL COMMENT 'Escalation 2',
  `ESCALATION3` VARCHAR(45) NULL COMMENT 'Escalation 3',
  `SERVICEOWNERNAME` VARCHAR(45) NULL COMMENT 'Service Owner Name',
  `COUNTRY` VARCHAR(45) NULL COMMENT 'Country',
  PRIMARY KEY (`PRJID`))
COMMENT = 'PROJECT Table';

ALTER TABLE `android`.`project`
CHANGE COLUMN `PRJID` `PRJID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Project ID for Projects' ;



===============================================================================

CREATE TABLE `android`.`TEAMLEAD` (
  `TLID` INT NOT NULL COMMENT 'Team Lead Id',
  `TLNAME` VARCHAR(45) NOT NULL COMMENT 'Team Lead Name',
  `TKID` INT NOT NULL COMMENT 'Task Id PK to Task table',
  `TOTAL_REPORTIES` VARCHAR(45) NULL COMMENT 'Total Number of Reporties',
  `ESCALATION1` VARCHAR(45) NULL COMMENT 'Escalation 1',
  PRIMARY KEY (`TLID`))
COMMENT = 'TEAMLEAD Table';

ALTER TABLE `android`.`teamlead`
CHANGE COLUMN `TLID` `TLID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Team Lead Id' ;


================================================================================


CREATE TABLE `android`.`TEAMMEMBER` (
  `TMEMID` INT NOT NULL COMMENT 'TEAM Member ID',
  `TMEMNAME` VARCHAR(45) NOT NULL COMMENT 'TEAM Member NAME',
  `TMEMEMAILID` VARCHAR(45) NULL COMMENT 'TEAM Member EMAILID',
  `TMEMPHONE` VARCHAR(45) NULL COMMENT 'TEAM Member PHONE',
  `TLID` INT NOT NULL COMMENT 'Team Lead ID PK to TeamLead Table',
  PRIMARY KEY (`TMEMID`))
COMMENT = 'TEAMMEMBER Table';

ALTER TABLE `android`.`teammember`
CHANGE COLUMN `TMEMID` `TMEMID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'TEAM Member ID' ;


===============================================================================


CREATE TABLE `android`.`LOGIN` (
  `LOGINID` INT NOT NULL COMMENT 'Login ID of User',
  `USERNAME` VARCHAR(45) NOT NULL COMMENT 'Login User Name',
  `USERPASSWORD` VARCHAR(45) NULL COMMENT 'Login User Password',
  `PRJID` INT NOT NULL COMMENT 'Login User Projet Id',
  PRIMARY KEY (`LOGINID`))
COMMENT = 'LOGIN Table';

ALTER TABLE `android`.`login`
CHANGE COLUMN `LOGINID` `LOGINID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Login ID of User' ;

===============================================================================


CREATE TABLE `android`.`TASK` (
  `TKID` INT NOT NULL COMMENT 'Task ID OF USER',
  `TKNAME` VARCHAR(45) NOT NULL COMMENT 'Task NAME',
  `STATUS` VARCHAR(45) NULL COMMENT 'Task STATUS',
  `TMEMID` INT NOT NULL COMMENT 'Task Start Date',
  `SPRINTID` INT NOT NULL COMMENT 'Task End Date',
  `TASKSTARTDATE` VARCHAR(45) NULL COMMENT 'ID of Task assigned to team member',
  `TASKENDDATE` VARCHAR(45) NULL COMMENT 'Task sprint Id  PK to Scrum',
  PRIMARY KEY (`TKID`))
COMMENT = 'TASK Table';

ALTER TABLE `android`.`task`
CHANGE COLUMN `TKID` `TKID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Task ID OF USER' ;

===============================================================================

CREATE TABLE `android`.`SCRUM` (
  `SCRUMID` INT NOT NULL COMMENT 'Scrum ID OF USER',
  `SCRUMNAME` VARCHAR(45) NOT NULL COMMENT 'Scrum NAME',
  `SCRUMDESCRIPTION` VARCHAR(45) NULL COMMENT 'Scrum Descriptions',
  `PRJID` INT NOT NULL COMMENT 'Project Id PK to Project',
  PRIMARY KEY (`SCRUMID`))
COMMENT = 'SCRUM Table';

ALTER TABLE `android`.`scrum`
CHANGE COLUMN `SCRUMID` `SCRUMID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Scrum ID OF USER' ;

===============================================================================


CREATE TABLE `android`.`SPRINT` (
  `SPRINTID` INT NOT NULL COMMENT 'SPRINT ID OF USER',
  `SPRINTNAME` VARCHAR(45) NOT NULL COMMENT 'SPRINT NAME',
  `SCRUMID` INT NOT NULL COMMENT 'SPRINT scrum Id PK to Scrum Table',
  `SPRINTSTARTDATE` VARCHAR(45) NOT NULL COMMENT 'SPRINT start Date',
  `SPRINTENDDATE` VARCHAR(45) NOT NULL COMMENT 'SPRINT end Date',
  PRIMARY KEY (`SPRINTID`))
COMMENT = 'SPRINT Table';


ALTER TABLE `android`.`sprint`
CHANGE COLUMN `SPRINTID` `SPRINTID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SPRINT ID OF USER' ;

===============================================================================

CREATE TABLE `android`.`RETROSPECT` (
  `RETID` INT NOT NULL COMMENT 'Retrospect ID',
  `COMMENTS` VARCHAR(45) NOT NULL COMMENT 'Retrospect Comments',
  `SPRINTNAME` VARCHAR(45) NOT NULL COMMENT 'Retrospect Sprint name',
  `SPRINTID` INT NOT NULL COMMENT 'Retrospect Team Sprint Id PK to Sprint',
  PRIMARY KEY (`RETID`))
COMMENT = 'RETROSPECT Table';

ALTER TABLE `android`.`retrospect`
CHANGE COLUMN `RETID` `RETID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Retrospect ID' ;

===========================================================================================

CREATE TABLE `android`.`KPI` (
  `KPIID` INT NOT NULL COMMENT 'KPIID ID',
  `SPRINTID` VARCHAR(45) NOT NULL COMMENT 'KPI Sprint ID',
  `SPRINTNAME` VARCHAR(45) NOT NULL COMMENT 'KPI Sprint name',
  `DEFECTDENSITY` INT NOT NULL COMMENT 'KPI Defect Density',
  `SCHEDULE_SLIPPAGE` INT NOT NULL COMMENT 'KPI Schedule Sclippage',
  `DESIGN_REVIEW_COVERAGE` INT NOT NULL COMMENT 'KPI Design review Coverage',
  `CODE_REVIEW_COVERAGE` INT NOT NULL COMMENT 'KPI Code Review Coverage',
  `TEST_CASE_COVERAGE` INT NOT NULL COMMENT 'KPI Test Case Coverage',
  PRIMARY KEY (`KPIID`))
COMMENT = 'KPI Table';

ALTER TABLE `android`.`kpi`
CHANGE COLUMN `KPIID` `KPIID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'KPIID ID' ;

====================================
CREATE TABLE `android`.`SCRUMROLE` (
  `SRID` INT NOT NULL COMMENT 'SCRUMROLE ID',
  `SRNAME` VARCHAR(45) NOT NULL COMMENT 'SCRUMROLE Name',
  PRIMARY KEY (`SRID`))
COMMENT = 'SCRUMROLE Table';

ALTER TABLE `android`.`scrumrole`
CHANGE COLUMN `SRID` `SRID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SCRUMROLE ID' ;

==========================

CREATE TABLE `android`.`SCRUMROLERESPONSIBILITY` (
  `SRRID` INT NOT NULL COMMENT 'SCRUMROLERESPONSIBILITY ID',
  `SRID` INT NOT NULL COMMENT 'SCRUMROLERESPONSIBILITY Scrum Role ID',
  `RR` VARCHAR(45) NOT NULL COMMENT 'SCRUMROLERESPONSIBILITY Responsibilities',
  PRIMARY KEY (`SRRID`))
COMMENT = 'SCRUMROLERESPONSIBILITY Table';

ALTER TABLE `android`.`scrumroleresponsibility`
CHANGE COLUMN `SRRID` `SRRID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SCRUMROLERESPONSIBILITY ID' ;

=================================

CREATE TABLE `android`.`CHECKLIST` (
  `CLID` INT NOT NULL COMMENT 'CHECKLIST ID',
  `CLLID` INT NOT NULL COMMENT 'CHECKLIST Item Id',
  `CLDESCRIPTION` VARCHAR(45) NOT NULL COMMENT 'CHECKLIST Descriptions',
  PRIMARY KEY (`CLID`))
COMMENT = 'CHECKLIST Table';

ALTER TABLE `android`.`checklist`
CHANGE COLUMN `CLID` `CLID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'CHECKLIST ID' ;

=====================
CREATE TABLE `android`.`CHECKLISTITEM` (
  `CLIID` INT NOT NULL COMMENT 'CHECKLISTIteam ID',
  `CLIDESCRIPTION` VARCHAR(45) NOT NULL COMMENT 'CHECKLISTIteam Descriptions',
  PRIMARY KEY (`CLIID`))
COMMENT = 'CHECKLISTITEM Table';

ALTER TABLE `android`.`checklistitem`
CHANGE COLUMN `CLIID` `CLIID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'CHECKLISTIteam ID' ;

===========================

CREATE TABLE `android`.`SCRUMKNOWLEDGE` (
  `SKID` INT NOT NULL COMMENT 'SCRUMKNOWLEDGE ID',
  `TOPICID` INT NOT NULL COMMENT 'SCRUMKNOWLEDGE Topic ID',
  `TOPICDESCRIPTIONS` VARCHAR(45) NOT NULL COMMENT 'SCRUMKNOWLEDGE Topic Descriptions',
  PRIMARY KEY (`SKID`))
COMMENT = 'SCRUMKNOWLEDGE Table';

ALTER TABLE `android`.`scrumknowledge`
CHANGE COLUMN `SKID` `SKID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SCRUMKNOWLEDGE ID' ;

=====================================

INSERT INTO `android`.`login` (`LOGINID`, `USERNAME`, `USERPASSWORD`, `PRJID`) VALUES ('1', 'un', 'up', '1');

INSERT INTO `ANDROID`.`PROJECT` (PRJID, PRJNAME, ESCALATION1, ESCALATION2, ESCALATION3, SERVICEOWNERNAME, COUNTRY) VALUES ('1', 'projectname1', 'eescallation1', 'escallation2', 'escallation3', 'serviceownwername1', 'c1');

INSERT INTO `ANDROID`.`RETROSPECT` (RETID, COMMENTS, SPRINTNAME, SPRINTID) VALUES ('1', 'ret1comments', 'sprint1', '1');
INSERT INTO `ANDROID`.`RETROSPECT` (RETID, COMMENTS, SPRINTNAME, SPRINTID) VALUES ('2', 'ret2comments', 'sprint2', '2');
INSERT INTO `ANDROID`.`RETROSPECT` (RETID, COMMENTS, SPRINTNAME, SPRINTID) VALUES ('3', 'ret3comments', 'sprint3', '3');


INSERT INTO `ANDROID`.`SCRUM` (SCRUMID, SCRUMNAME, SCRUMDESCRIPTION, PRJID) VALUES ('1', 'scrumname1', 'scrum1description', '1');
INSERT INTO `ANDROID`.`SCRUM` (SCRUMID, SCRUMNAME, SCRUMDESCRIPTION, PRJID) VALUES ('2', 'sscrumname2', 'scrum2description', '1');

INSERT INTO `ANDROID`.`SPRINT` (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('1', 'sprint1', '1', '18-04-2016', '6-05-2016')
INSERT INTO `ANDROID`.`SPRINT` (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('2', 'sprint2', '1', '18-04-2016', '6-05-2016')
INSERT INTO `ANDROID`.`SPRINT` (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('3', 'sprint3', '1', '18-04-2016', '6-05-2016')

INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('1', 'taskname1', 'complete', '1', '1', '18-04-2016', '19-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('2', 'taskname1', 'inprogress', '2', '1', '27-04-2016', '30-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('3', 'taskname1', 'open', '3', '1', '3-05-2016', '5-05-2016')

INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('4', 'taskname2', 'complete', '1', '2', '18-04-2016', '19-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('5', 'taskname2', 'inprogress', '2', '2', '27-04-2016', '30-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('6', 'taskname2', 'open', '3', '2', '3-05-2016', '5-05-2016')

INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('7', 'taskname3', 'complete', '1', '3', '18-04-2016', '19-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('8', 'taskname3', 'inprogress', '2', '3', '27-04-2016', '30-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('9', 'taskname3', 'open', '3', '3', '3-05-2016', '5-05-2016')

INSERT INTO `ANDROID`.`TEAMLEAD` (TLID, TLNAME, TKID, TOTAL_REPORTIES, ESCALATION1) VALUES ('1', 'teamlead1', '1', '5', 'escallationteamlead1');
INSERT INTO `ANDROID`.`TEAMLEAD` (TLID, TLNAME, TKID, TOTAL_REPORTIES, ESCALATION1) VALUES ('2', 'teamlead2', '2', '5', 'escallationteamlead1');
INSERT INTO `ANDROID`.`TEAMLEAD` (TLID, TLNAME, TKID, TOTAL_REPORTIES, ESCALATION1) VALUES ('3', 'teamlead3', '3', '5', 'escallationteamlead1');



INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('1', 'teammember1', 'teammember1@test.com', '123456', '1')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('2', 'teammember2', 'teammember2@test.com', '5678902', '1')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('3', 'teammember3', 'teammember3@test.com', '22345', '2')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('4', 'teammember4', 'teammember4@test.com', '33222', '2')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('5', 'teammember5', 'teammember5@test.com', '44433', '3')


INSERT INTO `ANDROID`.`KPI` (KPIID, SPRINTID, SPRINTNAME, DEFECTDENSITY, SCHEDULE_SLIPPAGE, DESIGN_REVIEW_COVERAGE, CODE_REVIEW_COVERAGE, TEST_CASE_COVERAGE) VALUES ('1', '1', 'sprint1', '1', '1', '1', '1', '1')
INSERT INTO `ANDROID`.`KPI` (KPIID, SPRINTID, SPRINTNAME, DEFECTDENSITY, SCHEDULE_SLIPPAGE, DESIGN_REVIEW_COVERAGE, CODE_REVIEW_COVERAGE, TEST_CASE_COVERAGE) VALUES ('2', '2', 'sprint2', '22', '21', '21', '21', '2')
INSERT INTO `ANDROID`.`KPI` (KPIID, SPRINTID, SPRINTNAME, DEFECTDENSITY, SCHEDULE_SLIPPAGE, DESIGN_REVIEW_COVERAGE, CODE_REVIEW_COVERAGE, TEST_CASE_COVERAGE) VALUES ('3', '3', 'sprint3', '3', '33', '3', '31', '32')
INSERT INTO `ANDROID`.`SCRUMROLE` (SRID, SRNAME) VALUES ('1', 'PRODUCT OWNER')
INSERT INTO `ANDROID`.`SCRUMROLE` (SRID, SRNAME) VALUES ('2', 'SCRUMMASTER')
INSERT INTO `ANDROID`.`SCRUMROLE` (SRID, SRNAME) VALUES ('3', 'DEVELOPMENTTEAM')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('1', '1', 'RR1')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('2', '1', 'RR2')

INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('3', '2', 'RR1')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('4', '2', 'RR2')

INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('5', '3', 'RR1')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('6', '3', 'RR2')


INSERT INTO `ANDROID`.`CHECKLIST` (CLID, CLLID, CLDESCRIPTION) VALUES ('1', '1', 'CL1')
INSERT INTO `ANDROID`.`CHECKLIST` (CLID, CLLID, CLDESCRIPTION) VALUES ('2', '1', 'CL2')

INSERT INTO `ANDROID`.`SCRUMKNOWLEDGE` (SKID, TOPICID, TOPICDESCRIPTIONS) VALUES ('1', '1', 'topic1')
INSERT INTO `ANDROID`.`SCRUMKNOWLEDGE` (SKID, TOPICID, TOPICDESCRIPTIONS) VALUES ('2', '1', 'topic2')

INSERT INTO `ANDROID`.`CHECKLISTITEM` (CLIID, CLIDESCRIPTION) VALUES ('1', 'Item 1')
INSERT INTO `ANDROID`.`CHECKLISTITEM` (CLIID, CLIDESCRIPTION) VALUES ('2', 'Item 2')