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