I have written a code to read the data from the excel file and based on that data, I have written a code to fetch some values from a particular table of sql database. Now I want to display that content (fetched from the database) on the browser. Can anyone suggest me how to do that?? (I have used servlets and jsp)
Here is my servlet class code
package com.test2.mainClass;
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class Servlet1 extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// TODO Auto-generated method stub
super.doGet(req, resp);
System.out.println("In doGet()");
}
@Override
protected void service(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
// TODO Auto-generated method stub
super.service(req, res);
Servlet1 s1 = new Servlet1();
s1.method();
int i = 1;
if(i==1){
RequestDispatcher dispatcher =
req.getRequestDispatcher("/exceloutput.jsp");
dispatcher.forward( req, res );
return;
}
}
int rowIndex;
int columnIndex;
//***********************************************************************************
//Taking the Standard Headers in variables
private static String key1 = "Product";
private static String key2 = "Model";
private static String key3 ="Part Number";
private static String key4 ="U_PRE";
private static String key5 ="P_PRE";
private static String key6 ="U_ENHP";
private static String key7 ="P_ENHP";
private static String key8 ="U_ENH";
private static String key9 ="P_ENH";
private static String key10 ="U_BASIC";
private static String key11 ="P_BASIC";
//***********************************************************************************
//Creating One Master MAP which will be iterated to obtain values
public static Map<String,ArrayList<Object>> M = new LinkedHashMap<String,ArrayList<Object>>();
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//Lists for every particular key
static ArrayList<Object> productList = new ArrayList<Object>();
static ArrayList<Object> modelList = new ArrayList<Object>();
static ArrayList<Object> partnoList = new ArrayList<Object>();
static ArrayList<Object> u_preList = new ArrayList<Object>();
static ArrayList<Object> p_preList = new ArrayList<Object>();
static ArrayList<Object> u_enhpList = new ArrayList<Object>();
static ArrayList<Object> p_enhpList = new ArrayList<Object>();
static ArrayList<Object> u_enhList = new ArrayList<Object>();
static ArrayList<Object> p_enhList = new ArrayList<Object>();
static ArrayList<Object> u_basicList = new ArrayList<Object>();
static ArrayList<Object> p_basicList = new ArrayList<Object>();
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
public void method(){
try {
FileInputStream file = new FileInputStream(new File("C:\\Users\\SoniAd\\Desktop\\TOOL WORK EXCEL\\Copy of Original Sheets\\New.xls"));
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
Row row;
//Check if there is next row present in the excel sheet
if(rowIterator.hasNext())
{
rowIterator.next();//Go to next row
}
if(rowIterator.hasNext())
{
rowIterator.next();
}
while(rowIterator.hasNext()) {
row = rowIterator.next();
//For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
//Check if there is next cell present in the row
if(cellIterator.hasNext())
{
cellIterator.next();//Go to next cell
}
while(cellIterator.hasNext()) {
Cell cell = cellIterator.next();
//Get the type of entry in the cell
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
break;
case Cell.CELL_TYPE_NUMERIC:
rowIndex = cell.getRowIndex();
columnIndex = cell.getColumnIndex();
if(rowIndex>2)
{
if(columnIndex==7)
{
u_preList.add(cell.getNumericCellValue());
}
if(columnIndex==8)
{
p_preList.add(cell.getNumericCellValue());
}
if(columnIndex==9)
{
u_enhpList.add(cell.getNumericCellValue());
}
if(columnIndex==10)
{
p_enhpList.add(cell.getNumericCellValue());
}
if(columnIndex==11)
{
u_enhList.add(cell.getNumericCellValue());
}
if(columnIndex==12)
{
p_enhList.add(cell.getNumericCellValue());
}
if(columnIndex==13)
{
u_basicList.add(cell.getNumericCellValue());
}
if(columnIndex==14)
{
p_basicList.add(cell.getNumericCellValue());
}
}
break;
case Cell.CELL_TYPE_STRING:
//Skip iterations for first three keys
if(cell.getStringCellValue()!=key1||cell.getStringCellValue()!=key2||cell.getStringCellValue()!=key3||cell.getStringCellValue()!=key4||cell.getStringCellValue()!=key5||cell.getStringCellValue()!=key6||cell.getStringCellValue()!=key7||cell.getStringCellValue()!=key8||cell.getStringCellValue()!=key9||cell.getStringCellValue()!=key10||cell.getStringCellValue()!=key11)
{
rowIndex = cell.getRowIndex();
columnIndex = cell.getColumnIndex();
if(rowIndex>2 && cell.getStringCellValue()!=null)
{
if(columnIndex==1)
{
productList.add(cell.getStringCellValue());
}
if(columnIndex==2)
{
modelList.add(cell.getStringCellValue());
}
if(columnIndex==3)
{
partnoList.add(cell.getStringCellValue());
}
}
}
break;
}
}
System.out.println("");
}
//Entering data (keys and their respective values
M.put(key1,productList);
M.put(key2,modelList);
M.put(key3,partnoList);
M.put(key4,u_preList);
M.put(key5,p_preList);
M.put(key6,u_enhpList);
M.put(key7,p_enhpList);
M.put(key8,u_enhList);
M.put(key9,p_enhList);
M.put(key10,u_basicList);
M.put(key11,p_basicList);
file.close();
FileOutputStream out =
new FileOutputStream(new File("C:\\Users\\Ad\\Desktop\\TOOL WORK EXCEL\\Copy of Original Sheets\\New.xls"));
workbook.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
try {
//$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
String serviceType = null;
String serviceProgram = null;
double price = 0;
//$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
String choiceValuePremium = null;
String choiceValueEnhancedPlus = null;
String choiceValueEnhanced = null;
String choiceValueBasic = null;
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
String url = "jdbc:oracle:thin:@sv-decdb1.ecom.fjcs.net:1522:ECOMDEV";
//Create Connection object
Connection conn = DriverManager.getConnection(url, "dev",
"dev");
// Create statement object
Statement st = conn.createStatement();
//------------------------------------------------------------------------------------------------------
//FOR PRE
ResultSet rs = st.executeQuery("select choicevalue from TABLENAME where choicedescription = 'Premium'");
while(rs.next()){
choiceValuePremium = rs.getString("choicevalue");
}
//-------------------------------------------------------------------------------------------------------------
//FOR ENH PL
ResultSet rs1 = st.executeQuery("select choice_value from TABLENAME where choicedescription = 'Enhanced +'");
while(rs1.next()){
choiceValueEnhancedPlus = rs1.getString("choicevalue");
}
//-------------------------------------------------------------------------------------------------------------
//FOR ENH
ResultSet rs2 = st.executeQuery("select choicevalue from TABLENAME where choicedescription = 'Enhanced'");
while(rs2.next()){
choiceValueEnhanced = rs2.getString("choicevalue");
}
//-------------------------------------------------------------------------------------------------------------
//FOR BAS
ResultSet rs3 = st.executeQuery("select choicevalue from TABLENAME where choice_description = 'Basic' and rownum<1");
while(rs3.next()){
choiceValueBasic = rs3.getString("choicevalue");
}
//-------------------------------------------------------------------------------------------------------------
//Store entry (Key/Value)of HashMap in set
Set mapSet = (Set) M.entrySet();
//Create iterator on Set
Iterator mapIterator = mapSet.iterator();
System.out.println("Display the INSERT Queries of every Entry in the Excel.\n");
while (mapIterator.hasNext()) {
Map.Entry mapEntry = (Map.Entry) mapIterator.next();
// getKey Method of HashMap access a key of map
String keyValue = (String) mapEntry.getKey();
//getValue method returns corresponding key's value
//ArrayList<Object> value = (ArrayList<Object>) mapEntry.getValue();
ArrayList<Object> value = M.get(keyValue);
//KKKKKKKKKKKKKKKKKKKKKKK
System.out.println("\n\n\n");
System.out.println("------------------------------------------------------------------------------");
System.out.println("------------------------------------------------------------------------------");
System.out.println("------------------------------------------------------------------------------");
System.out.println(keyValue);
//%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
FileInputStream file = new FileInputStream(new File("C:\\Users\\SoniAd\\Desktop\\TOOL WORK EXCEL\\Copy of Original Sheets\\New.xls"));
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);
//&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
for (int k = 0; k<=sheet.getPhysicalNumberOfRows()-5;k++)
{
//System.out.println(k);
if(keyValue==key4||keyValue==key5)
{
serviceProgram = choiceValuePremium;
if(keyValue==key4){
serviceType = "U";
price = (Double) u_preList.get(k);
}
if(keyValue==key5){
serviceType = "P";
price = (Double) p_preList.get(k);
}
}
if(keyValue==key6||keyValue==key7)
{
serviceProgram = choiceValueEnhancedPlus;
if(keyValue==key6){
serviceType = "U";
price = (Double) u_enhpList.get(k);
}
if(keyValue==key7){
serviceType = "P";
price = (Double) p_enhpList.get(k);
}
}
if(keyValue==key8||keyValue==key9)
{
serviceProgram = choiceValueEnhanced;
if(keyValue==key8){
serviceType = "U";
price = (Double) u_enhList.get(k);
}
if(keyValue==key9){
serviceType = "P";
price = (Double) p_preList.get(k);
}
}
if(keyValue==key10||keyValue==key11)
{
serviceProgram = "041";
if(keyValue==key10)
{
serviceType = "U";
price = (Double) u_basicList.get(k);
}
if(keyValue==key11)
{
serviceType = "P";
price = (Double) p_basicList.get(k);
}
}
if(keyValue!=key1&&keyValue!=key2&&keyValue!=key3){
//++++++++++++++++++++++++++++++++++
System.out.println("**********************************");
/*System.out.println(k);
System.out.println(productList.get(k));
System.out.println(modelList.get(k));
System.out.println(serviceType);
System.out.println(serviceProgram);
System.out.println(price);
System.out.println(partnoList.get(k));
*/ //+++++++++++++++++++++++++++++++++++++
String insertQuery = "INSERT INTO TABLENAME ("productive,modeling,servicetype,serviceprogram,price,effectivedate,expirydate,createuser,createdate,pricetype,gsaprice,systemboards,manhours,partnumber,updatedate,updateuser,gsacode)"+"VALUES('"+productList.get(k)+"','"+modelList.get(k)+"','"+serviceType+"','"+serviceProgram+"',"+price+",SYSDATE-1,to_date('31-DEC-99','DD-MON-RR'),'system','SYSDATE','EXTENDED',null,null,null,'"+partnoList.get(k)+"',null,null,null)\n";
//st.executeUpdate(insertQuery);
System.out.println(insertQuery);
}
}//end of FOR Loop
//%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
}//end of WHILE loop
conn.close();
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
e.printStackTrace();
}
}
}
I have added the code above. please take a look. Its working and fetching the data from the database table. Now I want this data to be displayed on the browser. I would appreciate if someone can tell me how to fetch this data (in the html table) in the jsp page named exceloutput.jsp(which I am using for browser view). Below is the jsp code:-
<%@ page language="java" 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">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Title</title>
</head>
<body>
<table border = '1'>
<thead>
<th>Product</th>
<th>Model</th>
<th>Service Type</th>
<th>Service Program</th>
<th>Price</th>
<th>Effective Date</th>
<th>Expiry Date</th>
<th>Create User</th>
<th>Create Date</th>
<th>Price Type</th>
<th>GSA Price</th>
<th>System Boards</th>
<th>Man Hours</th>
<th>Part Number</th>
<th>Update Date</th>
<th>Update User</th>
<th>GSA Code</th>
</thead>
<tbody>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
</body>
</html>
Now I have to get those values and display them below the corresponding headings mentioned in this jsp page. How to do that. Please help ???
