package org.cupd.spdb.report.importexcel.dao.impl;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.cupd.spdb.report.importexcel.dao.CardBinDao;
import com.websurf.spdb.bo.DBConnBOImpl;
public class CardBinDaoImpl implements CardBinDao {
private static final Log log=LogFactory.getLog(CardBinDaoImpl.class);
private static DBConnBOImpl dbConn = new DBConnBOImpl();
PreparedStatement ps = null;
Connection conn = null;
ResultSet rs = null;
private String filePath="E:\\Import.xlsx";
/**
* 插入数据
*/
public boolean insertDB(){
boolean flag=true;
try {
//文件流指向excel文件
//FileInputStream fin=new FileInputStream(filePath);
FileInputStream fileInput = new FileInputStream(filePath);;
XSSFWorkbook workbook = new XSSFWorkbook(fileInput);//创建工作薄
XSSFSheet sheet=workbook.getSheetAt(0);//得到工作表
XSSFRow row=null;//对应excel的行
XSSFCell cell=null;//对应excel的列
int totalRow=sheet.getLastRowNum();//得到excel的总记录条数
log.info("得到excel的总记录数"+totalRow);
//以下的字段一一对应数据库表的字段
String cardName="";
String cardType="";
String cardBin="";
String cardLength="";
String activityCode="";
String note="";
String sql ="insert into BizCardBin(cardName,cardType,cardBin,cardLength,activityCode,note) values(?,?,?,?,?,?)";
for(int i=2;i<=totalRow;i++){
row=sheet.getRow(i);
cell=row.getCell((short) 0);
cardName=cell.getStringCellValue().toString();
cell=row.getCell((short) 1);
cardType=cell.getStringCellValue().toString();
cell=row.getCell((short) 2);
cardBin=cell.getStringCellValue().toString();
cell=row.getCell((short) 3);
cardLength=cell.getStringCellValue().toString();
cell=row.getCell((short) 4);
activityCode=cell.getStringCellValue().toString();
cell=row.getCell((short) 5);
note=cell.getStringCellValue().toString();
conn=dbConn.getConnection();
log.info("获取JDBC连接完成");
ps=conn.prepareStatement(sql);
ps.setString(1,cardName);
ps.setString(2,cardType);
ps.setString(3,cardBin);
ps.setString(4,cardLength);
ps.setString(5,activityCode);
ps.setString(6,note);
ps.execute();
System.out.println("preparestatement successful");
}
dbConnClose(conn, dbConn);
psClose(ps, rs);
} catch (FileNotFoundException e) {
flag=false;
e.printStackTrace();
} catch(IOException ex){
flag=false;
ex.printStackTrace();
} catch(SQLException exx){
flag=false;
exx.printStackTrace();
}
return flag;
}
public static void main(String[] args) {
CardBinDaoImpl e=new CardBinDaoImpl();
e.insertDB();
}
/**
* 插入数据 只需要传入插入sql即可
* 插入sql的样例:insert into t_department values('D004','金融部');
* @param insert 插入语句
* @return
* @throws SQLException
*/
/*public int insertCardBin(BizCardBin cardBin){
int result=0;
PreparedStatement ps = null;
Connection conn = null;
String sql ="insert into BizCardBin(cardName,cardTyoe,cardBin,cardLength,cardLength,activityCode,note) values(?,?,?,?,?,?,?)";
try {
conn=dbConn.getConnection();
log.info("获取JDBC连接完成");
ps = conn.prepareStatement(sql);
ps.setString(1, cardBin.getCardName());
ps.setString(2, cardBin.getCardType());
ps.setString(3, cardBin.getCardBin());
ps.setString(4, cardBin.getCardLength());
ps.setString(5, cardBin.getActivityCode());
ps.setString(6, cardBin.getNote());
result = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
log.error("SQL Exception "+ e);
e.printStackTrace();
return 0;
}finally{
dbConnClose(conn, dbConn);
psClose(ps, rs);
}
return result;
}
*/
public void dbConnClose(Connection conn,DBConnBOImpl dbConn){
if(conn!=null){
dbConn.close(conn);
}
}
private static void psClose(final PreparedStatement preparedStatement,
final ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
log.error("error", e);
}
}
}
下面是报错信息 到底哪里出错了啊
org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:61)
at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:256)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:196)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:179)
at org.cupd.spdb.report.importexcel.dao.impl.CardBinDaoImpl.insertDB(CardBinDaoImpl.java:39)
at org.cupd.spdb.report.importexcel.service.impl.CardBinServiceImpl.insertDB(CardBinServiceImpl.java:31)
at org.cupd.spdb.report.importexcel.action.CardBinAction.insertCardBin(CardBinAction.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at wdk.mng.filter.LogFilter.doFilter(LogFilter.java:30)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at wdk.mng.filter.CharsetFilter.doFilter(CharsetFilter.java:30)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:881)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:674)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:541)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:59)
... 38 more
Caused by: java.lang.ExceptionInInitializerError
at sun.misc.Unsafe.ensureClassInitialized(Native Method)
at sun.reflect.UnsafeFieldAccessorFactory.newFieldAccessor(UnsafeFieldAccessorFactory.java:25)
at sun.reflect.ReflectionFactory.newFieldAccessor(ReflectionFactory.java:122)
at java.lang.reflect.Field.acquireFieldAccessor(Field.java:918)
at java.lang.reflect.Field.getFieldAccessor(Field.java:899)
at java.lang.reflect.Field.get(Field.java:358)
at org.apache.xmlbeans.XmlBeans.typeSystemForClassLoader(XmlBeans.java:768)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument.<clinit>(Unknown Source)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.model.StylesTable.readFrom(StylesTable.java:102)
at org.apache.poi.xssf.model.StylesTable.<init>(StylesTable.java:91)
... 43 more
Caused by: java.lang.RuntimeException: Could not instantiate SchemaTypeSystemImpl (java.lang.reflect.InvocationTargetException): is the version of xbean.jar correct?
at schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.TypeSystemHolder.loadTypeSystem(Unknown Source)
at schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.TypeSystemHolder.<clinit>(Unknown Source)
... 54 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
... 56 more
Caused by: org.apache.xmlbeans.SchemaTypeLoaderException: XML-BEANS compiled schema: Incompatible minor version - expecting up to 23, got 24 (schemaorg_apache_xmlbeans.system.sE130CAA0A01A7CDE5A2B4FEB8B311707.index) - code 3
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl$XsbReader.<init>(SchemaTypeSystemImpl.java:1520)
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.initFromHeader(SchemaTypeSystemImpl.java:260)
at org.apache.xmlbeans.impl.schema.SchemaTypeSystemImpl.<init>(SchemaTypeSystemImpl.java:183)
... 60 more
插入失败2014-01-24 15:05:30,988 INFO org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/spdb] - User access! User IP: 127.0.0.1 Resource: /spdb/cupd/manage/cardBinImportExcel.do Milliseconds used: 829
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
