java.lang.OutOfMemoryError错误,使用JXL将数据库中表的内容写到EXCEL中,记录数》10000的时候就出现

lliuxxingw 2006-03-06 10:48:17
在应用中,将数据表导出到EXCEL中,使用“select * from table1” ,使用while (rs.next )循环写到一个EXCEL文件中,表的记录数》10000的时候就出现 java.lang.OutOfMemoryError错误
后面考虑一次只取一千条记录,也就是使用一个例示分页的存储过程每次取1000条记录,再通过
while (rs.next ) 写到EXCEL中,写入一千条后再取1000条记录,到12000左右又开始出现了
java.lang.OutOfMemoryError错误,真不知道 怎么增解决?
...全文
333 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
cuiyingfeng 2006-04-03
同意楼上的做法,楼主可能是使用jxl吧,我以前也遇到过,内存调大能够降低出问题的机会,但是彻底解决问题可能还需要高人出现支招。
回复
yown 2006-04-03
这个问题我以前遇到过,你可以试试将tomcat内存调大些看看!!!

在bin/catalina.bat 中找到 rem JAVA_OPTS='-Xms256m -Xmx512m' 将rem去掉试试了!!
回复
lliuxxingw 2006-03-07
这是分每批1000条记录写到EXCEL文件中的代码:
private void CreateExcelFile(String sFileName, String sSheetName,
String sTableName) {
Statement stmt = null;
CallableStatement cstmt = null ;
Connection con = null;
ResultSet rs = null;
String temp = sFileName.substring(0,sFileName.length()-4) ;
try {
connMgr = DBConnectionManager.getInstance("logfile");
con = connMgr.getConnection("odb");
stmt = con.createStatement();
cstmt = con.prepareCall("{ call PagetoPage (?,?,?,?,?)}") ;

File myfile = new File(sFileName);
if (! (myfile.exists())) {
myfile.createNewFile();
System.out.println("an new excel create successful : "+ sFileName);
}
//create an new excel
WritableWorkbook workbook = Workbook.createWorkbook(myfile);
//use first excel's sheet
WritableSheet ws = workbook.createSheet(sSheetName, 0);

String strSql = " SELECT COUNT(*) FROM " + sTableName;
rs = stmt.executeQuery(strSql) ;
int iCount = 0 ;
while (rs.next())
{
iCount = rs.getInt(1);
}
rs.close();

if (iCount < 3000)
{
strSql = " SELECT * FROM " + sTableName;
//System.out.println("strSql : " + strSql);
rs = stmt.executeQuery(strSql);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

jxl.write.Label labelTemp = null;
for (int i = 1; i <= numberOfColumns; i++) {
//System.out.println(rsmd.getColumnName(i));
labelTemp = new jxl.write.Label(i - 1, 0, rsmd.getColumnName(i));
ws.addCell(labelTemp);
}
int row = 1;
while (rs.next()) {
for (int i = 1; i <= numberOfColumns; i++) {
if (rs.getString(rsmd.getColumnName(i)) == null) {
labelTemp = new jxl.write.Label(i - 1, row, "");
}
else {
labelTemp = new jxl.write.Label(i - 1, row,
rs.getString(rsmd.getColumnName(i)));
}
ws.addCell(labelTemp);
}
row++;
}
}else
{
int row = 1 ;
int per = 1000 ;
int iRow = (iCount / per) ;
if ((iCount % per) > 0 )
iRow = (iCount / per)+1 ;

for (int i= 0 ; i< iRow ; i++)
{
cstmt.setString(1, "select * ");
cstmt.setString(2, " from " + sTableName);
cstmt.setString(3, "");
cstmt.setInt(4, i*per+1);
cstmt.setInt(5, per*(i+1));

rs = cstmt.executeQuery() ;
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
//System.out.println("numberOfColumns: " + numberOfColumns);

jxl.write.Label labelTemp = null;
if (i == 0 ){
for (int m = 1; m <= numberOfColumns; m++) {
//System.out.println(rsmd.getColumnName(m));
labelTemp = new jxl.write.Label(m - 1, 0, rsmd.getColumnName(m));
ws.addCell(labelTemp);
}
System.gc();
}

while (rs.next()) {
for (int m = 1; m <= numberOfColumns; m++) {
if (rs.getString(rsmd.getColumnName(m)) == null) {
labelTemp = new jxl.write.Label(m - 1, row, "");
}
else {
labelTemp = new jxl.write.Label(m - 1, row,
rs.getString(rsmd.getColumnName(m)));
}
ws.addCell(labelTemp);
}
System.out.println("row : " + row );
row++;
}
rs.close() ;
System.gc();

}
}
workbook.write();
workbook.close();
}
catch (SQLException sqle) {
sqle.printStackTrace();
System.err.println("Execute SQL Error!");
}
catch (Exception e) {
if (con == null) {
System.out.println("?????????.");
}
e.printStackTrace();
System.err.println("Exception:" + e.getMessage());
//return "00001";
}
finally {
try {
if (rs != null) {
rs.close();
}
}
catch (Exception e) {
System.out.println("Error closing ResultSet: " + e);
}

try {
if (stmt != null) {
stmt.close();
}
}
catch (Exception e) {
System.out.println("Error closing Statement: " + e);
}

try {
if (cstmt != null) {
cstmt.close();
}
}
catch (Exception e) {
System.out.println("Error closing Statement: " + e);
}
if (connMgr != null) {
connMgr.freeConnection("odb", con);
}
}
}
回复
lliuxxingw 2006-03-07
分批写入到不同的文件 ,再已前一文件为模板的代码:
private void CreateExcelFile(String sFileName, String sSheetName,
String sTableName) {
Statement stmt = null;
CallableStatement cstmt = null ;
Connection con = null;
ResultSet rs = null;
String temp = sFileName.substring(0,sFileName.length()-4) ;
try {
connMgr = DBConnectionManager.getInstance("logfile");
con = connMgr.getConnection("odb");
stmt = con.createStatement();
cstmt = con.prepareCall("{ call PagetoPage (?,?,?,?,?)}") ;

File myfile = new File(sFileName);
if (! (myfile.exists())) {
myfile.createNewFile();
System.out.println("an new excel create successful : "+ sFileName);
}
//create an new excel
WritableWorkbook workbook = Workbook.createWorkbook(myfile);
//use first excel's sheet
WritableSheet ws = workbook.createSheet(sSheetName, 0);

String strSql = " SELECT COUNT(*) FROM " + sTableName;
rs = stmt.executeQuery(strSql) ;
int iCount = 0 ;
while (rs.next())
{
iCount = rs.getInt(1);
}
rs.close();

if (iCount < 3000)
{
strSql = " SELECT * FROM " + sTableName;
//System.out.println("strSql : " + strSql);
rs = stmt.executeQuery(strSql);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

jxl.write.Label labelTemp = null;
for (int i = 1; i <= numberOfColumns; i++) {
//System.out.println(rsmd.getColumnName(i));
labelTemp = new jxl.write.Label(i - 1, 0, rsmd.getColumnName(i));
ws.addCell(labelTemp);
}
int row = 1;
while (rs.next()) {
for (int i = 1; i <= numberOfColumns; i++) {
if (rs.getString(rsmd.getColumnName(i)) == null) {
labelTemp = new jxl.write.Label(i - 1, row, "");
}
else {
labelTemp = new jxl.write.Label(i - 1, row,
rs.getString(rsmd.getColumnName(i)));
}
ws.addCell(labelTemp);
}
row++;
}
}else
{
int row = 1 ;
int per = 1000 ;
int iRow = (iCount / per) ;
if ((iCount % per) > 0 )
iRow = (iCount / per)+1 ;

for (int i= 0 ; i< iRow ; i++)
{
cstmt.setString(1, "select * ");
cstmt.setString(2, " from " + sTableName);
cstmt.setString(3, "");
cstmt.setInt(4, i*per+1);
cstmt.setInt(5, per*(i+1));

rs = cstmt.executeQuery() ;
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
//System.out.println("numberOfColumns: " + numberOfColumns);

if (i > 0)
{
File tempFile = new File(temp+String.valueOf(i-1)+".xls") ;
Workbook w1 = Workbook.getWorkbook(myfile) ;
if (tempFile.exists())
w1 = Workbook.getWorkbook(tempFile) ;

String tempFile1 = temp+ String.valueOf(i)+".xls" ;
workbook = Workbook.createWorkbook(new File(tempFile1),w1) ;
ws = workbook.getSheet(0) ;
}

jxl.write.Label labelTemp = null;
if (i == 0 ){
for (int m = 1; m <= numberOfColumns; m++) {
//System.out.println(rsmd.getColumnName(m));
labelTemp = new jxl.write.Label(m - 1, 0, rsmd.getColumnName(m));
ws.addCell(labelTemp);
}
System.gc();
}

while (rs.next()) {
for (int m = 1; m <= numberOfColumns; m++) {
if (rs.getString(rsmd.getColumnName(m)) == null) {
labelTemp = new jxl.write.Label(m - 1, row, "");
}
else {
labelTemp = new jxl.write.Label(m - 1, row,
rs.getString(rsmd.getColumnName(m)));
}
ws.addCell(labelTemp);
}
System.out.println("row : " + row );
row++;
}
rs.close() ;
System.gc();
workbook.write();
workbook.close();

}
}
//workbook.write();
//workbook.close();
}
catch (SQLException sqle) {
sqle.printStackTrace();
System.err.println("Execute SQL Error!");
}
catch (Exception e) {
if (con == null) {
System.out.println("?????????.");
}
e.printStackTrace();
System.err.println("Exception:" + e.getMessage());
//return "00001";
}
finally {
try {
if (rs != null) {
rs.close();
}
}
catch (Exception e) {
System.out.println("Error closing ResultSet: " + e);
}

try {
if (stmt != null) {
stmt.close();
}
}
catch (Exception e) {
System.out.println("Error closing Statement: " + e);
}

try {
if (cstmt != null) {
cstmt.close();
}
}
catch (Exception e) {
System.out.println("Error closing Statement: " + e);
}

if (connMgr != null) {
connMgr.freeConnection("odb", con);
}
}
}
回复
lliuxxingw 2006-03-07
代码我想应该没有问题,
我现在采取另一种办法,就是先取1000条记录后就写入到一个EXCEL文件中,然后再已当前产生的EXCEL为模板再采入1000,再生成一个新的EXCEL文件,如此反复,当取记录数为6000时,此时已生成了6个EXCEL文件,以Excel5.xls为模板,此时便又出现此问题
我查看了Windows的内存使用,产生一个新的EXCEL文件时,当前文件增加的容量为0.7M,但是当用JXL再读入一个新的EXCEL文件为模板时此时Windows的内存使用便增加了11M多
因为记录数较多有3W,这样循环下来,Windows便增加了越来越多的内存使用而导致当前问题的产生
回复
doway 2006-03-06
应该是代码写得有问题,楼主可以用 Windows 的任务管理器查看一下实际使用了多少内存,比较一下与楼主自己想的差多少。

然后再看看代码,想想怎么会差这么多呢?:)

回复
相关推荐
发帖
Web 开发
创建于2007-09-28

8.0w+

社区成员

Java Web 开发
申请成为版主
帖子事件
创建了帖子
2006-03-06 10:48
社区公告
暂无公告