Click here to Skip to main content
14,690,703 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
rfms_excel.java->

package com.rfmsProject.upload;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.Iterator;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.exception.DataException;
import org.hibernate.exception.SQLGrammarException;

import com.rfmsProject.expenses.rfmsExpenses;

@WebServlet("/readExcel")
public class rfms_excel extends HttpServlet {

	@SuppressWarnings("deprecation")
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		PrintWriter pw = response.getWriter();
		pw.println("Welcome to reading excel and save to database");
		
		read_SaveToDB();
		
		
		
		
		// RequestDispatcher view =
		// request.getRequestDispatcher("/uploading_client.jsp");
		// view.include(request, response);

	}

	@SuppressWarnings("resource")
	public static void read_SaveToDB() {
		SessionFactory sf = new Configuration().configure("/com/rfmsProject/res/hibernate.cfg.xml").buildSessionFactory();
		Session session = sf.openSession();
		// File file = new File("F:\\web
		// Development\\RegistrationMVC\\formats\\data.xlsx"); //creating a new
		// file instance
		FileInputStream file;
		try {
			file = new FileInputStream(new File("E:\\RFMS Expenses1.xlsx"));
			XSSFWorkbook workbook;
			workbook = new XSSFWorkbook(file);
			XSSFSheet sheet = workbook.getSheetAt(0);
			Row row;
			int rows = sheet.getLastRowNum();
			System.out.println("No. of rows" + rows);
			for (int i = 4; i <= rows; i++) {
				row = (Row) sheet.getRow(i);

				String _months;
				if (row.getCell(0) == null) {
					_months = "null";
				}

				else {
					_months = row.getCell(0).toString();
				}

				double _adExpenses;
				if (row.getCell(1) == null) {
					_adExpenses = 0;
				} else
					_adExpenses = row.getCell(1).getNumericCellValue();

				double _xerox;
				if (row.getCell(2) == null) {
					_xerox = 0;
				} else {
					_xerox = row.getCell(2).getNumericCellValue();
				}

				double _swacchBharatExpenses;
				if (row.getCell(3) == null) {
					_swacchBharatExpenses=0;
				} else {
					_swacchBharatExpenses = row.getCell(3).getNumericCellValue();
				}

				double _postageCourier;
				if (row.getCell(4) == null) {
					_postageCourier = 0;
				} else {
					_postageCourier = row.getCell(4).getNumericCellValue();
				}

				double _meetingConference;
				if (row.getCell(5) == null) {
					_meetingConference=0;
				} else {
					_meetingConference = row.getCell(5).getNumericCellValue();
				}

				double _manpowerExpenses_contract;
				if (row.getCell(6) == null) {
					_manpowerExpenses_contract = 0;
				} else {
					_manpowerExpenses_contract = row.getCell(6).getNumericCellValue();
				}

				double _manpowerStaffTour;
				if (row.getCell(7) == null) {
					_manpowerStaffTour = 0;
				}
				else
				{
					_manpowerStaffTour = row.getCell(7).getNumericCellValue();
				}
              
				double _ftsSalary;
				if(row.getCell(8)==null)
				{
					_ftsSalary = 0;
				}
				else
				{
					
					_ftsSalary = row.getCell(8).getNumericCellValue();
				}
				

				double _reclSalary;
				if (row.getCell(9) == null) {
					_reclSalary=0;
				} else {
					_reclSalary = row.getCell(9).getNumericCellValue();
				}

				double _toursTravelsExpenses;
				if (row.getCell(10) == null) {
				   _toursTravelsExpenses=0;
				} else {
				 _toursTravelsExpenses= row.getCell(10).getNumericCellValue();
				}
				
				double _toursTravels_Others;
				if(row.getCell(11)==null)
				{
					_toursTravels_Others=0;
				}
				else
				{
					_toursTravels_Others = row.getCell(11).getNumericCellValue();
				}
				
				double _krishiKayanExpenses;
				if(row.getCell(12)==null)
				{
					_krishiKayanExpenses=0;
				}
				else
				{
					
					_krishiKayanExpenses = row.getCell(12).getNumericCellValue();
				}
				
				double _tenderProcessingCharges;
				if(row.getCell(13)==null)
				{
					_tenderProcessingCharges=0;
				}
				else
				{
					_tenderProcessingCharges = row.getCell(13).getNumericCellValue();
				}
				
				double _printingStationary;
				if(row.getCell(14)==null)
				{
					_printingStationary=0;
				}
				else
				{
					_printingStationary = row.getCell(14).getNumericCellValue();
				}
				
				double _hospitalityExpenses;
				if(row.getCell(15)==null)
				{
					_hospitalityExpenses = 0;
				}
				else
				{
					_hospitalityExpenses = row.getCell(15).getNumericCellValue();
				}
				
				double _technicalExpModems;
				if(row.getCell(16)==null)
				{
				  _technicalExpModems = 0;
				}
				else
				{
					_technicalExpModems = row.getCell(16).getNumericCellValue();
				}
				
				double _technicalExpCloudNIC;
				if(row.getCell(17)==null)
				{
					_technicalExpCloudNIC = 0;
				}
				else
				{
					_technicalExpCloudNIC = row.getCell(17).getNumericCellValue();
				}
				
				double _technicalExp_MDAS;
				if(row.getCell(18)==null)
				{
					_technicalExp_MDAS = 0;
				}
				else
				{
					_technicalExp_MDAS = row.getCell(18).getNumericCellValue();
				}
				
				double _technicalExp_AuxiliaryContract;
				if(row.getCell(19)==null)
				{
					_technicalExp_AuxiliaryContract= 0;
				}
				else
				{
					_technicalExp_AuxiliaryContract = row.getCell(19).getNumericCellValue();
				}
				
				double _technicalMSTC_Tendoring;
				if(row.getCell(20)==null)
				{
					_technicalMSTC_Tendoring=0;
				}
				else
				{
					_technicalMSTC_Tendoring = row.getCell(20).getNumericCellValue();
				}
				
				double _ITexpenses;
				if(row.getCell(21)==null)
				{
					_ITexpenses=0;
				}
				else
				{
					_ITexpenses = row.getCell(21).getNumericCellValue();
				}
				
				double _taxi_service;
				if(row.getCell(22)==null)
				{
					_taxi_service=0;
				}
				else
				{
					_taxi_service = row.getCell(22).getNumericCellValue();
				}
				
				double _consultancyCharges;
				if(row.getCell(23)==null)
				{
					_consultancyCharges=0;
				}
				else
				{
					_consultancyCharges = row.getCell(23).getNumericCellValue();
				}
				
				double _legalProfessional_filingCharges;
				if(row.getCell(24)==null)
				{
					_legalProfessional_filingCharges=0;
				}
				else
				{
					_legalProfessional_filingCharges = row.getCell(25).getNumericCellValue();
				}
				
				double _miscExpenses;
				if(row.getCell(26)==null)
				{
					_miscExpenses = 0;
				}
				else
				{
					_miscExpenses = row.getCell(26).getNumericCellValue();
				}
				
				double _total;
				if(row.getCell(27)==null)
				{
					_total=0;
				}
				else
				{
					_total = row.getCell(27).getNumericCellValue();
				}
				
				double _paidDirectlyFromBank;
				if(row.getCell(28)==null)
				{
					_paidDirectlyFromBank =0;
				}
				else
				{
					_paidDirectlyFromBank = row.getCell(28).getNumericCellValue();
				}
				
				double _ddugujyExpenses;
				if(row.getCell(29)==null)
				{
					_ddugujyExpenses=0;
				}
				else
				{
					_ddugujyExpenses = row.getCell(29).getNumericCellValue();
				}
					
				Transaction t = session.beginTransaction();

				rfmsExpenses rfe = new rfmsExpenses();
				// bo.setSl_no(Integer.parseInt(sl_no));
				rfe.setMonths(_months);
				rfe.setAds_expenses(_adExpenses);
				rfe.setPhotoCopies(_xerox);
				rfe.setSwachBharat_expenses(_swacchBharatExpenses);
				rfe.setPostageCourierExpenses(_postageCourier);
				rfe.setMeetingConferences(_meetingConference);
				rfe.setManPowerExpenses_contract(_manpowerExpenses_contract);
				rfe.setManpowerFlexi_staffTour(_manpowerStaffTour);
				rfe.setFts_salary(_ftsSalary);
				rfe.setRecl_salary(_reclSalary);
				rfe.setTour_travelExpenses(_toursTravelsExpenses);
				rfe.setTour_travelOthers(_toursTravels_Others);
				rfe.setKrishi_kalyanExpenses(_krishiKayanExpenses);
				rfe.setTender_processingCharges(_tenderProcessingCharges);
				rfe.setPrinting_stationary(_printingStationary);
				rfe.setHospitality_expenses(_hospitalityExpenses);
				rfe.setTechnicalExp_Modems(_technicalExpModems);
				rfe.setTechnicalExp_CloudNic(_technicalExpCloudNIC);
				rfe.setTechnicalExp_MDAS(_technicalExp_MDAS);
				rfe.setTechnicalExp_auxiliaryContract(_technicalExp_AuxiliaryContract);
				rfe.setTechnicalMstc_Tendoring(_technicalMSTC_Tendoring);
				rfe.setIT_expenses(_ITexpenses);
				rfe.setTaxi_services(_taxi_service);
				rfe.setConsultancyCharges(_consultancyCharges);
				rfe.setLegalProfessional_filingCharges(_legalProfessional_filingCharges);
				rfe.setMisc_expenses(_miscExpenses);
				rfe.setTotal(_total);
				rfe.setPaidDirectly_fromBank(_paidDirectlyFromBank);
				rfe.setDDGUJY_expenses(_ddugujyExpenses);
				
				System.out.println("id:="+rfe.getId()+"\t Months:="+rfe.getMonths()+"\t Ad expenses:="+rfe.getAds_expenses()+"\t photocopies:="+rfe.getPhotoCopies()+ 
					"\t Swacch Bharat expenses:="+rfe.getSwachBharat_expenses()+"\t Postage Courier expenses:="+rfe.getPostageCourierExpenses()+"\t Meeting conference:="+rfe.getMeetingConferences()
					+"\t manpower expenses Contract:="+rfe.getManPowerExpenses_contract()+"\t manpower flexi staffing tour:="+rfe.getManpowerFlexi_staffTour()+
					"\t FTS salary:="+rfe.getFts_salary()+"\t recl salary:="+rfe.getRecl_salary()+"\t Tour & travels expenses:="+rfe.getTour_travelExpenses()+"\t tours and travels others:="+rfe.getTour_travelOthers()
					+"\t Krishi Kalyan expenses:="+rfe.getKrishi_kalyanExpenses()+"\t Tender & processing Charges:="+rfe.getTender_processingCharges()+"\t Printing & stationary:="+rfe.getPrinting_stationary()+
					"\t Hospitality Expenses:="+rfe.getHospitality_expenses()+"\t Technical Exp cloudnic:="+rfe.getTechnicalExp_CloudNic()+"\t Technical exp Mdas:="+ rfe.getTechnicalExp_MDAS()+
					"\t TechnicalExp Auxiliary Contract:="+rfe.getTechnicalExp_auxiliaryContract()+"\t Technical mstc Tendoring:="+rfe.getTechnicalMstc_Tendoring()+"\t IT expenses:="+rfe.getIT_expenses()
					+"\t Taxi services:="+rfe.getTaxi_services()+"\t Consultancy Services:="+rfe.getConsultancyCharges()+"\t Legal Professional filing charges:="+rfe.getLegalProfessional_filingCharges()+
					"\t Miscellenous Expenses:="+rfe.getMisc_expenses()+"\t Total:="+rfe.getTotal()+"\t paid directly from bank:="+rfe.getPaidDirectly_fromBank()+"\t DDUgujy expenses:="+rfe.getDDGUJY_expenses());
				 session.saveOrUpdate(rfe);
			//	System.out.println("No. of rows inserted"+ rows);
			//	session.flush();
			t.commit();
				// session.close();
			}

			file.close();
			
		 }
		
		
		catch (IOException e) {
            
			String message;
			message = "" + e.getMessage();
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally

		{
		

			if (session != null) {
				session.close();
			}

		}

	}

}

rfmsExpenses.java:
package com.rfmsProject.expenses;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name="rfmsreports", schema="rfms")
public class rfmsExpenses {
	@Id
	@GenericGenerator(name="auto", strategy="increment")
	@GeneratedValue(generator="auto")
	private int id;
	
@Column(name = "months")	
private String Months;

@Column(name="Adver_expense")
private double Ads_expenses;

@Column(name="photoCopy")
private double photoCopies;

@Column(name="SwachhBharatExpense")
private double swachBharat_expenses;

@Column(name="PostageCourierExpenses")
private double postageCourierExpenses;

@Column(name="MeetingConferences")
private double meetingConferences;

@Column(name="manpowerExpensesContract")
private double manPowerExpenses_contract;

@Column(name="ManpowerFlexi_staffTour")
private double manpowerFlexi_staffTour;

@Column(name="Salary_ FTS")
private double fts_salary;

@Column(name="Salary_RECL")
private double recl_salary;

@Column(name="Tours_Travels_Expenses")
private double tour_travelExpenses;

@Column(name="Tours_TravelsOthers")
private double tour_travelOthers;

@Column(name="Krishi_KalyanExpenses")
private double krishi_kalyanExpenses;

@Column(name="TenderProcessingCharges")
private double tender_processingCharges;

@Column(name="Printing_Stationery")
private double printing_stationary;

@Column(name="Hospitalityexpense")
private double hospitality_expenses;

@Column(name="TechnicalExp_Modems")
private double technicalExp_Modems;

@Column(name="TechnicalExpCloud_NIC")
private double technicalExp_CloudNic;

@Column(name="TechnicalExp_MDAS")
private double technicalExp_MDAS;

@Column(name="TechnicalExp_AuxiliaryContact")
private double technicalExp_auxiliaryContract;

@Column(name="Technical_MSTC_Tendoring")
private double technicalMstc_Tendoring;

@Column(name="ITEXpenses")
private double IT_expenses;

@Column(name="TaxiService")
private double taxi_services;

@Column(name="Consultancy_Charges")
private double consultancyCharges;

@Column(name="LegalProfessional_filingCharges")
private double LegalProfessional_FilingCharges;

@Column(name="MiscellaneousExpenses")
private double Misc_expenses;

@Column(name="total")
private double total;

@Column(name="PaiddirectlyfromBank")
private double paidDirectly_fromBank;

@Column(name="DDGUJYExpenses")
private double DDGUJY_expenses;


public int getId() {
	return id;
}
public void setId(int id) {
	this.id = id;
}
public double getPhotoCopies() {
	return photoCopies;
}
public void setPhotoCopies(double photoCopies) {
	this.photoCopies = photoCopies;
}
public double getLegalProfessional_FilingCharges() {
	return LegalProfessional_FilingCharges;
}
public void setLegalProfessional_FilingCharges(double legalProfessional_FilingCharges) {
	LegalProfessional_FilingCharges = legalProfessional_FilingCharges;
}
public String getMonths() {
	return Months;
}
public void setMonths(String Months) {
	this.Months = Months;
}
public double getAds_expenses() {
	return Ads_expenses;
}
public void setAds_expenses(double Ads_expenses) {
	Ads_expenses = this.Ads_expenses;
}

public double getSwachBharat_expenses() {
	return swachBharat_expenses;
}
public void setSwachBharat_expenses(double swachBharat_expenses) {
	this.swachBharat_expenses = swachBharat_expenses;
}
public double getPostageCourierExpenses() {
	return postageCourierExpenses;
}
public void setPostageCourierExpenses(double postageCourierExpenses) {
	this.postageCourierExpenses = postageCourierExpenses;
}
public double getMeetingConferences() {
	return meetingConferences;
}
public void setMeetingConferences(double meetingConferences) {
	this.meetingConferences = meetingConferences;
}
public double getManPowerExpenses_contract() {
	return manPowerExpenses_contract;
}
public void setManPowerExpenses_contract(double manPowerExpenses_contract) {
	this.manPowerExpenses_contract = manPowerExpenses_contract;
}
public double getManpowerFlexi_staffTour() {
	return manpowerFlexi_staffTour;
}
public void setManpowerFlexi_staffTour(double manpowerFlexi_staffTour) {
	this.manpowerFlexi_staffTour = manpowerFlexi_staffTour;
}
public double getFts_salary() {
	return fts_salary;
}
public void setFts_salary(double fts_salary) {
	this.fts_salary = fts_salary;
}
public double getRecl_salary() {
	return recl_salary;
}
public void setRecl_salary(double recl_salary) {
	this.recl_salary = recl_salary;
}
public double getTour_travelExpenses() {
	return tour_travelExpenses;
}
public void setTour_travelExpenses(double tour_travelExpenses) {
	this.tour_travelExpenses = tour_travelExpenses;
}
public double getTour_travelOthers() {
	return tour_travelOthers;
}
public void setTour_travelOthers(double tour_travelOthers) {
	this.tour_travelOthers = tour_travelOthers;
}
public double getKrishi_kalyanExpenses() {
	return krishi_kalyanExpenses;
}
public void setKrishi_kalyanExpenses(double krishi_kalyanExpenses) {
	this.krishi_kalyanExpenses = krishi_kalyanExpenses;
}
public double getTender_processingCharges() {
	return tender_processingCharges;
}
public void setTender_processingCharges(double tender_processingCharges) {
	this.tender_processingCharges = tender_processingCharges;
}
public double getPrinting_stationary() {
	return printing_stationary;
}
public void setPrinting_stationary(double printing_stationary) {
	this.printing_stationary = printing_stationary;
}
public double getHospitality_expenses() {
	return hospitality_expenses;
}
public void setHospitality_expenses(double hospitality_expenses) {
	this.hospitality_expenses = hospitality_expenses;
}
public double getTechnicalExp_Modems() {
	return technicalExp_Modems;
}
public void setTechnicalExp_Modems(double technicalExp_Modems) {
	this.technicalExp_Modems = technicalExp_Modems;
}
public double getTechnicalExp_CloudNic() {
	return technicalExp_CloudNic;
}
public void setTechnicalExp_CloudNic(double technicalExp_CloudNic) {
	this.technicalExp_CloudNic = technicalExp_CloudNic;
}
public double getTechnicalExp_MDAS() {
	return technicalExp_MDAS;
}
public void setTechnicalExp_MDAS(double technicalExp_MDAS) {
	this.technicalExp_MDAS = technicalExp_MDAS;
}
public double getTechnicalExp_auxiliaryContract() {
	return technicalExp_auxiliaryContract;
}
public void setTechnicalExp_auxiliaryContract(double technicalExp_auxiliaryContract) {
	this.technicalExp_auxiliaryContract = technicalExp_auxiliaryContract;
}
public double getTechnicalMstc_Tendoring() {
	return technicalMstc_Tendoring;
}
public void setTechnicalMstc_Tendoring(double technicalMstc_Tendoring) {
	this.technicalMstc_Tendoring = technicalMstc_Tendoring;
}
public double getIT_expenses() {
	return IT_expenses;
}
public void setIT_expenses(double iT_expenses) {
	IT_expenses = iT_expenses;
}
public double getTaxi_services() {
	return taxi_services;
}
public void setTaxi_services(double taxi_services) {
	this.taxi_services = taxi_services;
}
public double getConsultancyCharges() {
	return consultancyCharges;
}
public void setConsultancyCharges(double consultancyCharges) {
	this.consultancyCharges = consultancyCharges;
}
public double getLegalProfessional_filingCharges() {
	return LegalProfessional_FilingCharges;
}
public void setLegalProfessional_filingCharges(double legalProfessional_filingCharges) {
	LegalProfessional_FilingCharges = legalProfessional_filingCharges;
}
public double getMisc_expenses() {
	return Misc_expenses;
}
public void setMisc_expenses(double misc_expenses) {
	Misc_expenses = misc_expenses;
}
public double getTotal() {
	return total;
}
public void setTotal(double total) {
	this.total = total;
}
public double getPaidDirectly_fromBank() {
	return paidDirectly_fromBank;
}
public void setPaidDirectly_fromBank(double paidDirectly_fromBank) {
	this.paidDirectly_fromBank = paidDirectly_fromBank;
}
public double getDDGUJY_expenses() {
	return DDGUJY_expenses;
}
public void setDDGUJY_expenses(double dDGUJY_expenses) {
	DDGUJY_expenses = dDGUJY_expenses;
}

}


---------------------*---------------
when i run this project:
i get the following in the console:
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Server version name:   Apache Tomcat/9.0.17
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Server built:          Mar 13 2019 15:55:27 UTC
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Server version number: 9.0.17.0
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: OS Name:               Windows 10
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: OS Version:            10.0
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Architecture:          amd64
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Java Home:             C:\Program Files\Java\jdk1.8.0_152\jre
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: JVM Version:           1.8.0_152-b16
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: JVM Vendor:            Oracle Corporation
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: CATALINA_BASE:         D:\tushark\.metadata\.plugins\org.eclipse.wst.server.core\tmp0
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: CATALINA_HOME:         D:\tushark\apache-tomcat-9.0.17
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Dcatalina.base=D:\tushark\.metadata\.plugins\org.eclipse.wst.server.core\tmp0
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Dcatalina.home=D:\tushark\apache-tomcat-9.0.17
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Dwtp.deploy=D:\tushark\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Djava.endorsed.dirs=D:\tushark\apache-tomcat-9.0.17\endorsed
Feb 02, 2020 1:03:59 PM org.apache.catalina.startup.VersionLoggerListener log
INFO: Command line argument: -Dfile.encoding=Cp1252
Feb 02, 2020 1:03:59 PM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFO: Loaded APR based Apache Tomcat Native library [1.2.21] using APR version [1.6.5].
Feb 02, 2020 1:03:59 PM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFO: APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].
Feb 02, 2020 1:03:59 PM org.apache.catalina.core.AprLifecycleListener lifecycleEvent
INFO: APR/OpenSSL configuration: useAprConnector [false], useOpenSSL [true]
Feb 02, 2020 1:03:59 PM org.apache.catalina.core.AprLifecycleListener initializeSSL
INFO: OpenSSL successfully initialized [OpenSSL 1.1.1a  20 Nov 2018]
Feb 02, 2020 1:04:00 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["http-nio-8080"]
Feb 02, 2020 1:04:04 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["ajp-nio-8009"]
Feb 02, 2020 1:04:04 PM org.apache.catalina.startup.Catalina load
INFO: Server initialization in [10,162] milliseconds
Feb 02, 2020 1:04:05 PM org.apache.catalina.core.StandardService startInternal
INFO: Starting service [Catalina]
Feb 02, 2020 1:04:05 PM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet engine: [Apache Tomcat/9.0.17]
Feb 02, 2020 1:04:25 PM org.apache.jasper.servlet.TldScanner scanJars
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Feb 02, 2020 1:04:25 PM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["http-nio-8080"]
Feb 02, 2020 1:04:25 PM org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["ajp-nio-8009"]
Feb 02, 2020 1:04:25 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in [20,722] milliseconds
Feb 02, 2020 1:06:30 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {5.3.1.Final}
Feb 02, 2020 1:06:30 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
Feb 02, 2020 1:06:31 PM org.hibernate.boot.jaxb.internal.stax.LocalXmlResourceResolver resolveEntity
WARN: HHH90000012: Recognized obsolete hibernate namespace http://hibernate.sourceforge.net/hibernate-configuration. Use namespace http://www.hibernate.org/dtd/hibernate-configuration instead.  Support for obsolete DTD/XSD namespaces may be removed at any time.
Feb 02, 2020 1:06:33 PM org.hibernate.spatial.integration.SpatialService <init>
INFO: HHH80000001: hibernate-spatial integration enabled : true
Feb 02, 2020 1:06:34 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {5.0.3.Final}
Feb 02, 2020 1:06:35 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Feb 02, 2020 1:06:35 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.cj.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/rfms]
Feb 02, 2020 1:06:35 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {user=root, password=****}
Feb 02, 2020 1:06:35 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Feb 02, 2020 1:06:35 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
Feb 02, 2020 1:06:39 PM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
Feb 02, 2020 1:06:40 PM org.hibernate.envers.boot.internal.EnversServiceImpl configure
INFO: Envers integration enabled? : true
Feb 02, 2020 1:06:49 PM org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@7ab57450] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
No. of rows72
id:=0	 Months:=Apr-2016	 Ad expenses:=0.0	 photocopies:=12000.0	 Swacch Bharat expenses:=123.0	 Postage Courier expenses:=1000.0	 Meeting conference:=789.78	 manpower expenses Contract:=120000.0	 manpower flexi staffing tour:=10000.0	 FTS salary:=1.2046557E7	 recl salary:=123400.0	 Tour & travels expenses:=29154.0	 tours and travels others:=120000.0	 Krishi Kalyan expenses:=1200.0	 Tender & processing Charges:=12.0	 Printing & stationary:=1200.0	 Hospitality Expenses:=9000.0	 Technical Exp cloudnic:=100.0	 Technical exp Mdas:=900000.0	 TechnicalExp Auxiliary Contract:=9000.0	 Technical mstc Tendoring:=1000.0	 IT expenses:=78000.0	 Taxi services:=100.0	 Consultancy Services:=2300.0	 Legal Professional filing charges:=10000.0	 Miscellenous Expenses:=1.350051478E7	 Total:=10000.0	 paid directly from bank:=1.338050278E7	 DDUgujy expenses:=0.0
Hibernate: select max(id) from rfmsreports
Hibernate: insert into rfmsreports (Adver_expense, DDGUJYExpenses, ITEXpenses, LegalProfessional_filingCharges, MiscellaneousExpenses, months, Consultancy_Charges, Salary_ FTS, Hospitalityexpense, Krishi_KalyanExpenses, manpowerExpensesContract, ManpowerFlexi_staffTour, MeetingConferences, PaiddirectlyfromBank, photoCopy, PostageCourierExpenses, Printing_Stationery, Salary_RECL, SwachhBharatExpense, TaxiService, TechnicalExpCloud_NIC, TechnicalExp_MDAS, TechnicalExp_Modems, TechnicalExp_AuxiliaryContact, Technical_MSTC_Tendoring, TenderProcessingCharges, total, Tours_Travels_Expenses, Tours_TravelsOthers, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Feb 02, 2020 1:06:56 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1064, SQLState: 42000
Feb 02, 2020 1:06:56 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FTS, Hospitalityexpense, Krishi_KalyanExpenses, manpowerExpensesContract, Manpow' at line 1
Feb 02, 2020 1:06:56 PM org.hibernate.internal.ExceptionMapperStandardImpl mapManagedFlushFailure
ERROR: HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: could not execute statement]
Feb 02, 2020 1:06:56 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [com.rfmsProject.upload.rfms_excel] in context with path [/rfmsProject] threw exception
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1460)
	at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
	at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3278)
	at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2474)
	at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271)
	at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
	at com.rfmsProject.upload.rfms_excel.read_SaveToDB(rfms_excel.java:382)
	at com.rfmsProject.upload.rfms_excel.doGet(rfms_excel.java:40)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3136)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3651)
	at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:90)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
	... 34 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FTS, Hospitalityexpense, Krishi_KalyanExpenses, manpowerExpensesContract, Manpow' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1109)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1057)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1377)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1042)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
	... 42 more

Feb 02, 2020 1:17:53 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/rfmsProject] has started
Feb 02, 2020 1:17:53 PM org.apache.catalina.loader.WebappClassLoaderBase clearReferencesJdbc
WARNING: The web application [rfmsProject] registered the JDBC driver [com.mysql.cj.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
Feb 02, 2020 1:17:53 PM org.apache.catalina.loader.WebappClassLoaderBase clearReferencesThreads
WARNING: The web application [rfmsProject] appears to have started a thread named [mysql-cj-abandoned-connection-cleanup] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
 java.lang.Object.wait(Native Method)
 java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:143)
 com.mysql.cj.jdbc.AbandonedConnectionCleanupThread.run(AbandonedConnectionCleanupThread.java:85)
 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
 java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 java.lang.Thread.run(Thread.java:748)
Feb 02, 2020 1:17:53 PM org.apache.catalina.loader.WebappClassLoaderBase clearReferencesThreads
WARNING: The web application [rfmsProject] appears to have started a thread named [pool-1-thread-1] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
 sun.misc.Unsafe.park(Native Method)
 java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
 java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
 java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
 java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
 java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
 java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 java.lang.Thread.run(Thread.java:748)
Feb 02, 2020 1:17:53 PM org.apache.catalina.loader.WebappClassLoaderBase checkThreadLocalMapForLeaks
SEVERE: The web application [rfmsProject] created a ThreadLocal with key of type [java.lang.ThreadLocal] (value [java.lang.ThreadLocal@68805780]) and a value of type [org.apache.xmlbeans.impl.schema.SchemaTypeLoaderImpl] (value [org.apache.xmlbeans.impl.schema.SchemaTypeLoaderImpl@370077c]) but failed to remove it when the web application was stopped. Threads are going to be renewed over time to try and avoid a probable memory leak.
Feb 02, 2020 1:17:57 PM org.apache.catalina.loader.WebappClassLoaderBase checkStateForResourceLoading
INFO: Illegal access: this web application instance has been stopped already. Could not load []. The following stack trace is thrown for debugging purposes as well as to attempt to terminate the thread which caused the illegal access.
java.lang.IllegalStateException: Illegal access: this web application instance has been stopped already. Could not load []. The following stack trace is thrown for debugging purposes as well as to attempt to terminate the thread which caused the illegal access.
	at org.apache.catalina.loader.WebappClassLoaderBase.checkStateForResourceLoading(WebappClassLoaderBase.java:1383)
	at org.apache.catalina.loader.WebappClassLoaderBase.getResource(WebappClassLoaderBase.java:1036)
	at com.mysql.cj.jdbc.AbandonedConnectionCleanupThread.checkThreadContextClassLoader(AbandonedConnectionCleanupThread.java:117)
	at com.mysql.cj.jdbc.AbandonedConnectionCleanupThread.run(AbandonedConnectionCleanupThread.java:84)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

Feb 02, 2020 1:18:06 PM org.apache.jasper.servlet.TldScanner scanJars
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Feb 02, 2020 1:18:06 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/rfmsProject] is completed


What I have tried:

i have add the throws exception :
public static void read_SaveToDB() throws SQLGrammarException{
Posted
Updated 2-Feb-20 0:28am
v2
Comments
Member 14639038 2-Feb-20 3:10am
   
it is still giving me sql grammar exception
OriginalGriff 2-Feb-20 3:28am
   
Congratulations!
Somewhere in that pile of code you have a problem.
Do I know where? No.
Do I want to wade through your whole log entry to find out? No.
Do I have any idea what that code is supposed to do? No.
You couldn't even be bothered to format it so it was slightly more readable - I had to do that for you.

Don't post your whole code - and certainly don't post undocumented code with commented out rubbish - don;t post your whole log. Post just the relevant code fragments that should us where the error is being detected, and what shows there is an error.

Help us to help you!

Use the "Improve question" widget to edit your question and provide better information.

1 solution

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FTS, Hospitalityexpense, Krishi_KalyanExpenses, manpowerExpensesContract, Manpow' at line 1

You made a mistake while building your SQL query. And despite the whole lot of code that you provided, you did not show the one which is relevant.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900