英文:
OutOfMemoryError: Java heap space , while retrieving data by using Jdbctemplate : (RowCallbackHandlerResultSetExtractor.extractData)
问题
public List<Employee> getEmployeeDataByDate(Date dateCreated) throws Exception {List<Employee> listDetails = new ArrayList<>();sqlServTemplate.query(SyncQueryConstants.RETRIEVE_EMPLOYEE_RECORDS_BY_DATE, new Object[] { dateCreated },new RowCallbackHandler() {@Overridepublic void processRow(ResultSet rs) throws SQLException {Employee emp = new Employee();emp.setEmployeeID(rs.getString("employeeID"));emp.setFirstName(rs.getString("firstName"));emp.setLastName(rs.getString("lastName"));emp.setMiddleName(rs.getString("middleName"));emp.setNickName(rs.getString("nickName"));// Skipping the employeeImage retrieval to avoid memory issues// byte[] res = rs.getBytes("employeeImage");// Blob blob = new SerialBlob(res);// emp.setEmployeeImage(blob);// ......listDetails.add(emp);}});return listDetails;}
Here's the query that retrieves data based on the date:
SELECT * FROM Employees with(nolock) WHERE cast(datediff(day, 0, dateCreated) as datetime) >= ?
Since you're encountering an OutOfMemoryError when processing the employeeImage field, you might consider fetching the image data in a more memory-efficient way, especially given the large number of records you're handling. One option is to retrieve the image data separately when needed, rather than fetching it for every employee in the initial query.
If you encounter memory issues due to handling a large number of records, consider implementing a paging mechanism, even if you're doing a daily sync. You can use a combination of the LIMIT and OFFSET clauses in your SQL query to fetch records in smaller batches.
Please note that without knowing more about your specific environment and constraints, it's challenging to provide an exact solution. The code and suggestions provided here are meant to guide you in addressing the memory issue and improving the efficiency of your data retrieval process.
英文:
public List<Employee> getEmployeeDataByDate(Date dateCreated) throws Exception {List<Employee> listDetails = new ArrayList<>();sqlServTemplate.query(SyncQueryConstants.RETRIEVE_EMPLOYEE_RECORDS_BY_DATE, new Object[] { dateCreated },new RowCallbackHandler() {@Overridepublic void processRow(ResultSet rs) throws SQLException {Employee emp = new Employee();emp.setEmployeeID(rs.getString("employeeID"));emp.setFirstName(rs.getString("firstName"));emp.setLastName(rs.getString("lastName"));emp.setMiddleName(rs.getString("middleName"));emp.setNickName(rs.getString("nickName"));byte[] res = rs.getBytes("employeeImage");Blob blob = new SerialBlob(res);emp.setEmployeeImage(blob);// .....listDetails.add(emp);}});return listDetails;}
Here I'm trying to retrieving records of the employee table.Because of BLOB data It's saying OutOfMemoryError: Java heap space . Could any one help me on this ?
It's a stand-alone application, I'm doing syncing from one table to another table. So unable to use pagination. Every day 2k record will sync at mid-night by cron job. Give some idea how I can solve this issue.
SELECT * FROM Employees with(nolock) WHERE cast (datediff (day, 0, dateCreated) as datetime) >= ?
This query giving me all data based on date, (around 2k record each day).
If I'm commenting
byte[] res = rs.getBytes("employeeImage");Blob blob = new SerialBlob(res);emp.setEmployeeImage(blob);
This line then no issue. Other wise It's throwing error.
Please give some idea, if possible give some sample code.
I'm struggling from 2days in this possition.
答案1
得分: 1
正如其他评论中提到的，您可以增加堆空间，或者限制查询返回的记录数量，然后将它们分批处理。
英文:
As some other commenters have mentioned you can either increase your heap space or limit the amount of records you are returning from your query and process them in smaller batches.
答案2
得分: 1
你正在读取一个以字节表示的MB大小图像，这将消耗你的堆内存..
而是尝试使用BinaryStream:
InputStream image = rs.getBinaryStream("employeeImage");
英文:
You are reading an MB sized image in bytes, it will consume your HEAP memory..
instead try using BinaryStream:
InputStream image = rs.getBinaryStream("employeeImage");
答案3
得分: 1
与其将每个用户添加到列表中，不如逐一处理他们。在从源数据库提取记录时，将每条记录放入另一个数据库中，而不是将它们添加到导致OOM错误的列表中。如果存在一些下游处理，那么可以将一个类注入到此DAO中，以处理实际的处理/写入目标数据库的操作。
英文:
Rather then adding each user to the list you could process them one at a time. Put each record in the other database as it's pulled from the source database, rather then adding them to the List which is causing the OOM error. If there is some other processing downstream then inject a class into this DAO that handles the actual processing/writing to the target DB.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论