How to get return from doWork() method

Question

here is my code given below for dowork() i m not able to get return count is there any way to get return from this method and doreturnwork() is not working for this please help me out i m trying it but there is no method like doreturnwork please help

sessionFactory.getCurrentSession().doWork(  

            new Work()
            {   
                public void execute(Connection connection) throws SQLException   
                {
                    String contactQueryCounts = "";
                    String contactQueryIds = "";
                    int index = 1;
                    StringBuilder builder = new StringBuilder();
                    CustomerUser user = (CustomerUser) userService.getLoggedInUser();
                    String inClause = "";
                    for (Long id :ids ) {
                        builder.append("?,");
                    }
                    if(builder.length()>0){
                        inClause = builder.substring(0, builder.length()-1);
                    }

                    if(inClause.length()>0){
                        contactQueryCounts= "select count(id) from ( select *,@num := if(@company_id = company_id, @num + 1, 1) as row_number," +
                        "@company_id := company_id as dummy from salebuild_ctl.company_contact where id in ("+inClause+") " +
                        " order by company_id,  date_created asc  ) as x where x.row_number <= ?";

                        contactQueryIds= "select id from ( select *,@num := if(@company_id = company_id, @num + 1, 1) as row_number," +
                        "@company_id := company_id as dummy from salebuild_ctl.company_contact where id in ("+inClause+") " +
                        " order by company_id,  date_created asc  ) as x where x.row_number <= ?";
                    }else{
                        contactQueryCounts= "select count(id) from ( select *,@num := if(@company_id = company_id, @num + 1, 1) as row_number," +
                        "@company_id := company_id as dummy from salebuild_ctl.company_contact  " +
                        " order by company_id,  date_created asc  ) as x where x.row_number <= ?";

                        contactQueryIds= "select id from ( select *,@num := if(@company_id = company_id, @num + 1, 1) as row_number," +
                        "@company_id := company_id as dummy from salebuild_ctl.company_contact  " +
                        " order by company_id,  date_created asc  ) as x where x.row_number <= ?";
                    }

                    java.sql.PreparedStatement sCount;
                    java.sql.PreparedStatement sIds;
                    try {
                        sCount = connection.prepareStatement(contactQueryCounts);
                        sIds = connection.prepareStatement(contactQueryIds);
                        for(Long id : ids){
                            sCount.setLong(index, id);
                            sIds.setLong(index, id);
                            index=index+1;
                        }
                        sCount.setInt(index,user.getAccount().getCurrentSubscription().getNumberofcontactspercompany());
                        sIds.setInt(index,user.getAccount().getCurrentSubscription().getNumberofcontactspercompany());
                        ResultSet rs  =  sCount.executeQuery();
                        int c = rs.getInt(1);

                        String contactLevelCountsQuery="select c.name,count(a.id) from company_contact a left join  " +
                        "title b on a.corporate_title_id=b.id left join title_level c on b.title_level_id=c.id where a.id  in" +
                        " ("+sIds.toString()+") and c.name is not null group by c.name";

                        java.sql.Statement s = connection.createStatement();
                        ResultSet rsIds = s.executeQuery(contactLevelCountsQuery);

                        SearchService.levelToContactCount.clear();

                        while (rsIds.next()) {
                            if(rsIds.getString(1) == null){
                                SearchService.levelToContactCount.put("No Level",rsIds.getInt(2));
                            }else{
                                SearchService.levelToContactCount.put(rsIds.getString(1),rsIds.getInt(2));
                            }
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    catch (Exception e) {
                        e.printStackTrace();
                    }
                }    
            }
    );

Answer 1

Initialize an empty collection or holder object, and fill this collection or holder inside the Work instance:

public List<Foo> searchFoos() {
    final List<Foo> result = new ArrayList<Foo>();
    sessionFactory.getCurrentSession().doWork(new Work() {
        public void execute(Connection connection) throws SQLException {
            // do some work
            result.add(foo1);
            // do some work
            result.add(foo2);
        }
    });
    return result;
}

Or even cleaner, create a named subclass of Work:

private static class FooWork implements Work {
    private Foo result;

    public void execute(Connection connection) throws SQLException {
        // do some work
        result = new Foo(...);
    }

    public Foo getResult() {
        return result;
    }
}

public Foo searchFoo() {
    FooWork fooWork = new FooWork();        
    sessionFactory.getCurrentSession().doWork(fooWork);
    return fooWork.getResult();
}

Answer 2

From Hibernate4, You can also use session.doReturningWork(ReturningWork work) as follow,

ReturningWork<Long> maxReturningWork = new ReturningWork<Long>() {

                @Override
                public Long execute(Connection connection) throws SQLException {
                    PreparedStatement preparedStatement = null;
                    ResultSet resultSet = null;
                    try {
                        preparedStatement = connection.prepareStatement("select max(salary) from employee");
                        resultSet = preparedStatement.executeQuery();
                        resultSet.next();
                        return resultSet.getLong(1);
                    }catch (SQLException e) {
                        throw e;
                    } finally {
                        if(preparedStatement != null) {
                            preparedStatement.close();
                        }
                        if(resultSet != null) {
                            resultSet.close();
                        }
                    }

                }
            };
            Long maxRecord = getSession().doReturningWork(maxReturningWork);