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')

Exposing and Consuming JAVA Webservice as CXF using JDK1.7

Note: JDK 1.8 is having issue and we have WTP plugin till 2.X version of CXF hence used CXF 2.7.1 Jar even though latest CXF 3.2 version is available.

Eclipse Set UP :- 

Installing CXF

CXF Server Project and CXF Client Project using Eclipse

package com.test;

import java.io.File;
import java.net.MalformedURLException;
import java.net.URL;

import javax.xml.namespace.QName;

public class TestSiddhuMain {
private static final QName SERVICE_NAME = new QName("http://test.com/", "TestSiddhuService");

public static void main(String args[])
{
try {
URL wsdlURL = TestSiddhuService.WSDL_LOCATION;
if (args.length > 0 && args[0] != null && !"".equals(args[0])) {
File wsdlFile = new File(args[0]);
try {
if (wsdlFile.exists()) {
wsdlURL = wsdlFile.toURI().toURL();
} else {
wsdlURL = new URL(args[0]);
}
} catch (MalformedURLException e) {
e.printStackTrace();
}
}

TestSiddhuService ss = new TestSiddhuService(wsdlURL, SERVICE_NAME);
TestSiddhu port = ss.getTestSiddhuPort();

{
System.out.println("Invoking add...");
java.lang.String _add_arg0 = "3";
java.lang.String _add_arg1 = "4";
java.lang.String _add__return = port.add(_add_arg0, _add_arg1);
System.out.println("add.result=" + _add__return);

}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}