I use Spring JDBC to insert informations that I read it from a Excel file(xlsx).First, I put the informations in a Vector(size 70 000!!) after that I use studentJDBCTemplate.insertListStudents(students) method from StudentJDBCTemplate class to isert all this Student object to oracle data base. My problem is that when I execute this SQL request (select count(*) from student ) using Oracle SQL developer, I get only 6041 rows was inserted and no exception was erased in Eclipse console also when I test the size of the Vector, I got 70 000. here is My class:
class Student : Entity
package com.tutorialspoint;
public class Student {
private Integer age;
private String name;
private Integer id;
public void setAge(Integer age) {
this.age = age;
}
public Integer getAge() {
return age;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Student [age=");
builder.append(age);
builder.append(", name=");
builder.append(name);
builder.append("]");
return builder.toString();
}
}
interface StudentDAO : contain all method will be implimented by StudentJDBCTemplate
package com.tutorialspoint;
import java.util.List;
import javax.sql.DataSource;
public interface StudentDAO {
/**
* This is the method to be used to initialize
* database resources ie. connection.
*/
public void setDataSource(DataSource ds);
/**
* This is the method to be used to create
* a record in the Student table.
*/
public void create(String name, Integer age);
/**
* This is the method to be used to insert
* a list of record in the Student table.
*/
public void insertListStudents(List<Student> listStudent);
}
class StudentJDBCTemplate : contain all CRUD methods
package com.tutorialspoint;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
public class StudentJDBCTemplate implements StudentDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
public void create(String name, Integer age) {
String SQL = "insert into Student (id,name, age) values (SEQ_STUDENT.nextval,?, ?)";
jdbcTemplateObject.update( SQL, name, age);
System.out.println("Created Record Name = " + name + " Age = " + age);
return;
}
@Override
public void insertListStudents(List<Student> listStudent) {
String sql = "insert into Student (id,name, age) values (SEQ_STUDENT.nextval,?, ?)";
jdbcTemplateObject.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Student student = listStudent.get(i);
System.out.println("Student "+ i);
ps.setString(1, student.getName());
ps.setInt(2, student.getAge());
}
@Override
public int getBatchSize() {
return listStudent.size();
}
});
}
}
class ExcelUtil : To read from xls and xlsx files
package com.tutorialspoint;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Vector;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtil {
public enum ExtentionFile{
XLS,XLSX,NONE
}
//private Student student = new Student();
public ExtentionFile checkExcelFile(String pathExcelFile) throws FileNotFoundException, IOException {
File file = new File(pathExcelFile);
if(file.isFile() && file.exists()){
if(POIXMLDocument.hasOOXMLHeader(new BufferedInputStream(new FileInputStream(pathExcelFile)))){
return ExtentionFile.XLSX;
}
if(POIFSFileSystem.hasPOIFSHeader(new BufferedInputStream(new FileInputStream(pathExcelFile)))){
return ExtentionFile.XLS;
}
}
return ExtentionFile.NONE;
}
public Vector<Student> readXlsxFile(String pathXlsxFile) throws IOException {
Vector<Student>students = new Vector<Student>();
Student student = new Student();
XSSFWorkbook workbook = new XSSFWorkbook(pathXlsxFile);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row;
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext()){
row = (XSSFRow) rowIterator.next();
Iterator<Cell>cellIterator = row.cellIterator();
student = new Student();
while(cellIterator.hasNext()){
Cell cell = cellIterator.next();
if(null !=cell && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)){
if(!TestISNumber.isNumber(cell.toString())){
student.setName(cell.toString());
}
else if(TestISNumber.isNumber(cell.toString())){
student.setAge((int)Double.parseDouble(cell.toString()));
}
}
}
//System.out.println(student);
students.add(student);
}
workbook.close();
return students;
}
public Vector<Student> readXlsFile(String pathXlsFile) throws IOException,InvalidFormatException {
Vector<Student> students = new Vector<Student>();
Student student = new Student();
FileInputStream inputStream = new FileInputStream(pathXlsFile);
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row;
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext()){
row = (HSSFRow) rowIterator.next();
Iterator<Cell>cellIterator = row.cellIterator();
student = new Student();
while(cellIterator.hasNext()){
Cell cell = cellIterator.next();
if(null !=cell && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)){
if(!TestISNumber.isNumber(cell.toString())){
student.setName(cell.toString());
}
else if(TestISNumber.isNumber(cell.toString())){
student.setAge(Integer.parseInt(cell.toString()));
}
}
}
//System.out.println(student);
students.add(student);
}
workbook.close();
inputStream.close();
return students;
}
public Vector<Student> readFile(String pathFile) throws FileNotFoundException, IOException, InvalidFormatException{
Vector<Student>students = new Vector<Student>();
if(checkExcelFile(pathFile) == ExtentionFile.XLS){
students = readXlsFile(pathFile);
}
if(checkExcelFile(pathFile) == ExtentionFile.XLSX){
students = readXlsxFile(pathFile);
}
return students;
}
public static void main(String[] args) {
ExcelUtil excelUtil = new ExcelUtil();
Vector<Student> students = new Vector<Student>();
try {
students = excelUtil.readFile("students.xlsx");
}
catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("Size of Students Vector: "+students.size());
//System.out.println(Arrays.toString(students.toArray()));
//System.out.println(Arrays.toString(new TreeSet<String>(students).toArray()));
}
}
class TestISNumber : to test if age of student read it from xlsx file can be parsed to double
package com.tutorialspoint;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class TestISNumber {
//Test if Cell contain a String that can be parsed to double
public static boolean isNumber (String s){
Pattern pattern = Pattern.compile("(.*^\\d.*)(\\d$)");
Matcher matcher = pattern.matcher(s);
if (matcher.matches()){
return true;
}
return false;
}
public static void main(String[] args) {
System.out.println(isNumber("100"));
}
}
class MainApp: main class
package com.tutorialspoint;
import java.io.IOException;
import java.util.Vector;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MainApp {
public static void main(String[] args) {
ApplicationContext context =
new ClassPathXmlApplicationContext("Beans.xml");
StudentJDBCTemplate studentJDBCTemplate =
(StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
ExcelUtil excelUtil = (ExcelUtil) context.getBean("excelUtilStudent");
try {
Vector<Student> students = excelUtil.readXlsxFile("students.xlsx");
/*for(Student student:students){
studentJDBCTemplate.create(student.getName(), student.getAge());
}*/
studentJDBCTemplate.insertListStudents(students);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
SQL Script :
--------------------------------------------------------
-- Fichier créé - jeudi-février-26-2015
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Sequence SEQ_STUDENT
--------------------------------------------------------
CREATE SEQUENCE "SPRINGJDBC"."SEQ_STUDENT" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 12181 CACHE 20 NOORDER NOCYCLE ;
--------------------------------------------------------
-- DDL for Table STUDENT
--------------------------------------------------------
CREATE TABLE "SPRINGJDBC"."STUDENT"
( "ID" NUMBER(*,0),
"NAME" VARCHAR2(20 BYTE),
"AGE" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
--------------------------------------------------------
-- DDL for Index SYS_C004040
--------------------------------------------------------
CREATE UNIQUE INDEX "SPRINGJDBC"."SYS_C004040" ON "SPRINGJDBC"."STUDENT" ("ID");
--------------------------------------------------------
-- Constraints for Table STUDENT
--------------------------------------------------------
ALTER TABLE "SPRINGJDBC"."STUDENT" MODIFY ("ID" NOT NULL ENABLE);
ALTER TABLE "SPRINGJDBC"."STUDENT" MODIFY ("NAME" NOT NULL ENABLE);
ALTER TABLE "SPRINGJDBC"."STUDENT" MODIFY ("AGE" NOT NULL ENABLE);
ALTER TABLE "SPRINGJDBC"."STUDENT" ADD PRIMARY KEY ("ID");
XML Bean configuration :
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://ift.tt/GArMu6"
xmlns:xsi="http://ift.tt/ra1lAU"
xsi:schemaLocation="http://ift.tt/GArMu6
http://ift.tt/QEDs1e ">
<!-- Initialization for data source -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
<property name="username" value="SPRINGJDBC" />
<property name="password" value="springjdbc" />
</bean>
<!-- Definition for studentJDBCTemplate bean -->
<bean id="studentJDBCTemplate" class="com.tutorialspoint.StudentJDBCTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- Definition for ExcelUtil bean -->
<bean id="excelUtilStudent" class="com.tutorialspoint.ExcelUtil" />
</beans>
PS: I use Spring-3.2.9.Release,Spring-JDBC-3.2.9.Release,ojdbc14,POI-3.11,oracle data base 10g express,Luna Eclipse and JavaSE 1.8 (Oracle JDK)
Aucun commentaire:
Enregistrer un commentaire