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

[lliuxxingw]

在应用中，将数据表导出到EXCEL中，使用“select * from table1” ，使用while (rs.next )循环写到一个EXCEL文件中，表的记录数》10000的时候就出现 java.lang.OutOfMemoryError错误
后面考虑一次只取一千条记录，也就是使用一个例示分页的存储过程每次取1000条记录，再通过
while (rs.next ) 写到EXCEL中，写入一千条后再取1000条记录，到12000左右又开始出现了
java.lang.OutOfMemoryError错误，真不知道 怎么增解决？

[doway]

应该是代码写得有问题，楼主可以用 Windows 的任务管理器查看一下实际使用了多少内存，比较一下与楼主自己想的差多少。

然后再看看代码，想想怎么会差这么多呢？：）

• 打赏
• 举报

回复 赞

[lliuxxingw]

代码我想应该没有问题，
我现在采取另一种办法，就是先取1000条记录后就写入到一个EXCEL文件中，然后再已当前产生的EXCEL为模板再采入1000，再生成一个新的EXCEL文件，如此反复，当取记录数为6000时，此时已生成了6个EXCEL文件，以Excel5.xls为模板，此时便又出现此问题
我查看了Windows的内存使用，产生一个新的EXCEL文件时，当前文件增加的容量为0.7M，但是当用JXL再读入一个新的EXCEL文件为模板时此时Windows的内存使用便增加了11M多
因为记录数较多有3W，这样循环下来，Windows便增加了越来越多的内存使用而导致当前问题的产生

• 打赏
• 举报

回复 赞

[lliuxxingw]

分批写入到不同的文件 ，再已前一文件为模板的代码：
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]

这是分每批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);
}
}
}

• 打赏
• 举报

回复 赞

[yown]

这个问题我以前遇到过,你可以试试将tomcat内存调大些看看!!!

在bin/catalina.bat 中找到 rem JAVA_OPTS='-Xms256m -Xmx512m' 将rem去掉试试了!!

• 打赏
• 举报

回复 赞

[cuiyingfeng]

同意楼上的做法，楼主可能是使用jxl吧，我以前也遇到过，内存调大能够降低出问题的机会，但是彻底解决问题可能还需要高人出现支招。

• 打赏
• 举报

回复 赞