AccountTable.java
import java.util.Date;
public class AccountTable {
private int id;
private Date txDate;
private String txType;
private int accountId;
private int amount;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getTxDate() {
return txDate;
}
public void setTxDate(Date txDate) {
this.txDate = txDate;
}
public String getTxType() {
return txType;
}
public void setTxType(String txType) {
this.txType = txType;
}
public int getAccountId() {
return accountId;
}
public void setAccountId(int accountId) {
this.accountId = accountId;
}
public int getAmount() {
return amount;
}
public void setAmount(int amount) {
this.amount = amount;
}
}
ReadExcel.java
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
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;
public class ReadExcel {
static List<AccountTable>acctList = new ArrayList<AccountTable>();
public static void main(String[] args){
List<AccountTable>acctdetail=readExcel("D:\\Book1.xlsx");
excelToDb(acctdetail);
}
@SuppressWarnings("unchecked")
private static List<AccountTable> readExcel(String filepath) {
try {
FileInputStream file = new FileInputStream(new
File("D:\\Book1.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
AccountTable account = new AccountTable();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
continue;
}
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (cell.getColumnIndex() == 0 ) {
account.setId((int) cell.getNumericCellValue());
acctList.add(account);
System.out.print(cell.getNumericCellValue() + "\t\t");
}
else if (cell.getColumnIndex() == 1 ) {
account.setTxDate((Date)cell.getDateCellValue());
acctList.add(account);
System.out.print(cell.getDateCellValue() + "\t\t");
}
else if (cell.getColumnIndex() == 2 ) {
account.setTxType(cell.getStringCellValue());
acctList.add(account);
System.out.print(cell.getStringCellValue() + "\t\t");
}
else if (cell.getColumnIndex() == 3 ) {
account.setAccountId((int)cell.getNumericCellValue());
acctList.add(account);
System.out.print(cell.getNumericCellValue() + "\t\t");
}
else if (cell.getColumnIndex() == 4 ) {
account.setAmount((int)cell.getNumericCellValue());
acctList.add(account);
System.out.print(cell.getNumericCellValue() + "\t\t");
}
}
System.out.println("");
}
file.close();
}catch (FileNotFoundException fnfe) {
fnfe.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return acctList;
}
private static void excelToDb(List<AccountTable> accountDt) {
Configuration c = new Configuration();
c.configure("/hibernate.cfg.xml");
SessionFactory sf = c.buildSessionFactory();
Session s = sf.openSession();
Transaction tx = s.beginTransaction();
try {
for (int i = 0; i < accountDt.size(); i++) {
AccountTable acc = accountDt.get(i);
s.save(acc);
}
tx.commit();
s.close();
} catch (Exception e) {
tx.rollback();
}
}
}
mapping.hbm.xml
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.example.AccountTable" table="account_detail">
<id name="id" type="int" column="Tx_id">
</id>
<property name="txDate" column="Tx_Date" type="date" />
<property name="txType" column="Account_type" type="string" />
<property name="accountId" column="Account_id" type="int" />
<property name="amount" column="Amount" type="int" />
</class>
</hibernate-mapping>
hibernate.cgf.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="connection.url">jdbc:postgresql://localhost:5432/example</property>
<property name="connection.username">postgres</property>
<property name="connection.password">*****</property>
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<mapping resource="mapping.hbm.xml" />
</session-factory>
</hibernate-configuration>
I was working on a Java project using Hibernate. I have an Excel file that contains 10 rows. I've successfully retrieved data from Excel file. Now, I have to insert those data to the database.
The problem is only the last row is being added to the table. Other rows are not being inserted.
