在应用中,将数据表导出到EXCEL中,使用“select * from table1” ,使用while (rs.next )循环写到一个EXCEL文件中,表的记录数》10000的时候就出现 java.lang.OutOfMemoryError错误
后面考虑一次只取一千条记录,也就是使用一个例示分页的存储过程每次取1000条记录,再通过
while (rs.next ) 写到EXCEL中,写入一千条后再取1000条记录,到12000左右又开始出现了
java.lang.OutOfMemoryError错误,真不知道 怎么增解决?
后面考虑一次只取一千条记录,也就是使用一个例示分页的存储过程每次取1000条记录,再通过
while (rs.next ) 写到EXCEL中,写入一千条后再取1000条记录,到12000左右又开始出现了
java.lang.OutOfMemoryError错误,真不知道 怎么增解决?
解决方案 »
- HTTP Status 404 - Servlet dispatcherServlet is not available
- 登录自动返回上一个页面
- http 500 错误
- 表单里的数据传递给两个frame的先后次序问题,急
- 我的java程序为什么放到jsp程序中就不能运行呢?谁能帮我看看
- easyui怎么用json和后台交互啊?
- j2sdk1.4.x+iis5.x+resin2.1.x的问题?!
- 这样的错误咋解决?在线等待!!!!!马上给分
- socket通信问题请教
- Java对pdf文件进行赋值,复选框样式怎么修改
- .jsp文件调用javaBean时,非要把.class放到这个文件夹啊吗?
- servlet如何使改动的登陆配置文件立即生效?
我现在采取另一种办法,就是先取1000条记录后就写入到一个EXCEL文件中,然后再已当前产生的EXCEL为模板再采入1000,再生成一个新的EXCEL文件,如此反复,当取记录数为6000时,此时已生成了6个EXCEL文件,以Excel5.xls为模板,此时便又出现此问题
我查看了Windows的内存使用,产生一个新的EXCEL文件时,当前文件增加的容量为0.7M,但是当用JXL再读入一个新的EXCEL文件为模板时此时Windows的内存使用便增加了11M多
因为记录数较多有3W,这样循环下来,Windows便增加了越来越多的内存使用而导致当前问题的产生
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);
}
}
}
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);
}
}
}