我用jxl把一个4500行49列的excel文件导入mysql时出现了题目所示的异常,查了网上的资料,说是内存溢出,可能是读入的文件太大了,把插入数据库语句屏蔽掉,运行没有问题,换了个500行的数据试了下,可以导入没问题,所以我判断是文件太大,无法一次写入数据库,那我怎样把4500行的excel表导入数据库,希望高手指点一下,谢谢!!
异常报告:
Exception in thread "AWT-EventQueue-0" java.lang.OutOfMemoryError: Java heap space
at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:437)
at com.mysql.jdbc.Connection.clientPrepareStatement(Connection.java:2187)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4829)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
at eetsoft.com.SubMenuActionDo.databasein(SubMenuActionDo.java:4576)
at eetsoft.com.SubMenuActionDo.actionPerformed(SubMenuActionDo.java:464)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6134)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
at java.awt.Component.processEvent(Component.java:5899)
at java.awt.Container.processEvent(Container.java:2023)
at java.awt.Component.dispatchEventImpl(Component.java:4501)
at java.awt.Container.dispatchEventImpl(Container.java:2081)
at java.awt.Component.dispatchEvent(Component.java:4331)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4301)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3965)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3895)
at java.awt.Container.dispatchEventImpl(Container.java:2067)
at java.awt.Window.dispatchEventImpl(Window.java:2458)
at java.awt.Component.dispatchEvent(Component.java:4331)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
异常报告:
Exception in thread "AWT-EventQueue-0" java.lang.OutOfMemoryError: Java heap space
at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:437)
at com.mysql.jdbc.Connection.clientPrepareStatement(Connection.java:2187)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4829)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
at eetsoft.com.SubMenuActionDo.databasein(SubMenuActionDo.java:4576)
at eetsoft.com.SubMenuActionDo.actionPerformed(SubMenuActionDo.java:464)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6134)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
at java.awt.Component.processEvent(Component.java:5899)
at java.awt.Container.processEvent(Container.java:2023)
at java.awt.Component.dispatchEventImpl(Component.java:4501)
at java.awt.Container.dispatchEventImpl(Container.java:2081)
at java.awt.Component.dispatchEvent(Component.java:4331)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4301)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3965)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3895)
at java.awt.Container.dispatchEventImpl(Container.java:2067)
at java.awt.Window.dispatchEventImpl(Window.java:2458)
at java.awt.Component.dispatchEvent(Component.java:4331)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
eclipse.ini里面,
eclipse
-vmargs
-Xms256m
-Xmx512m
我把代码贴出来,大家给看看try {
PreparedStatement pst=null;
ResultSet rst=null;
//加载jdbc-odbc桥驱动程序
Class.forName("com.mysql.jdbc.Driver"); //定义JDBC URL
String url="jdbc:mysql://localhost/adm?useUnicode=true&characterEncoding=gb2312" ; //得到与数据库的连接
Connection con = DriverManager.getConnection(url,"root","");
String filename="d:/";
filename=filename.concat(number1_content);
filename=filename.concat(".xls");
InputStream is = new FileInputStream(filename);//创建输入
Workbook rwb = Workbook.getWorkbook(is);
Sheet rs = rwb.getSheet(0); //读取第一个sheet
int colNum=rs.getColumns();//列数
int rowNum=rs.getRows();//行数
System.out.println(rowNum+" "+colNum);
int index=0; //数据报表数据分段导入
int length=0;//每一段数据的行数
if(rowNum%500==0)
index=rowNum/500;
else
index=rowNum/500+1;
for(int r=0;r<=index-1;r++)
{
Workbook book = Workbook.getWorkbook(new File(filename));
Sheet sheet = book.getSheet(0);
if(r!=index-1)
length=500;
else
length=rowNum-r*500;
for(int i=r*500+1;i<length;i++)
{
pst=con.prepareStatement("select number from user where number=?");
Cell cell_=sheet.getCell(2, i);
String n=cell_.getContents();
pst.setString(1, n);
rst=pst.executeQuery();
if(!rst.next())
{
pst=con.prepareStatement("insert into user(name,address,number,telephone,date,inside,checkman,A1,A2,A5,A6,A7,A8,A9,b5,b7,b6,b2,b1,b3,C1,C2,pai,kind,a14,biaoju,position,D1,D2,D4,D5,e1,E2,E3,E4,e5,f1,F2,F3,F4,yali1,yali2,G4,loudian,beizhu,a71,a3,pipenumber,operatornumber) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
con.setAutoCommit(false);
for(int j=0;j<49;j++)
{
Cell cell = sheet.getCell(j, i);
String result = cell.getContents();
pst.setString(j+1, result);
}
pst.executeUpdate();
con.commit();
}
else
System.out.println("有一条重复记录!");
}
sheet=null;
book.close();
}//for(int r=1;r<=index;r++)
pst.close();
rst.close();
con.close();
System.out.println("已成功导入数据库!");
} catch (Exception e) {
e.printStackTrace();
}
所以我想是不是应该把excel文件分成几段读入,但是不知道怎么做
各位有什么好的办法