0

I'm implementing a web app based on spring 3.2.8, jpa2.1 and hibernate 4.3.5. I'd like to know how I can prevent hibernate caching a specific Named Stored Procedure Query.

I'd tried entityManager.clear(), but I think it clears all cached entity values so it can't be an optimal solution!!

Notice: I used @NamedStoredProcedureQueries to define stored procedures and call it by entityManager.createNamedStoredProcedureQuery() method to get results

Added:

Table person:

person{p# as PK, ...}

Table person_value:

person_value{pv# as PK, p# as foreign key from table person, ...}

View view1:

select *,check_value(dbo.person.p#) as has_value from person

Function check_value(input):

returns 1 if there's a record in person_value with p# equals to input.

Stored procedure SPS_VIEW1:

select * from view1
where p#=@p#

Entity class of sql server view view1:

@Entity
@Table(name = "view1")
@NamedStoredProcedureQueries(
        @NamedStoredProcedureQuery(name = "getV1Records", procedureName = "SPS_VIEW1",     
        resultClasses = View1.class,
        @parameters = {@StoredProcedureParameter(name = "p#", type = String.class)))
public class View1{

  @Id
  @Column(name = "p#")       
  private String personId;     

  @Column(name = "hasValue")
  private boolean value;

  <getters and setters>

}

Service class:

@Service("personService")
public class PersonService() {
    ...
    private EntityManager entityManager;

    <getters and setters>
    ...

    public List<View1> getAll(String pno) {
       SqlParameterValue p = new SqlParameterValue(new SqlParameter("p#", Types.VARCHAR), pno);
       StoredProcedureQuery spq = entityManager.createNamedStoredProcedureQuery("getV1Records");            
       spq.setParameter(p.getName(), p.getValue());
       return list;
    }

}

Controller class:

@Controller("PersonController")
public class PersonController {

   ...
   @Autowired
   private PersonService personService;
   ...

   @RequestMapping("/person")
   public personInfo(HttpServletRequest request) {
      String pno = request.getParameter("pno");
      ModelAndView mv = new ModelAndView("person");
      List<View1> list = personService.getAll(pno);
      mv.addObject("view1_list", list);
      return mv;
   }
   ...

}

When I call getAll() with p# e.g. 'p14521' in my controller for first time, there's no record for p# 'p14521' in table person_value, it'll works correctly and every thing is ok, but when I add a record to person_value with p# 'p14521', now there is a record for p# 'p14521', and call getAll() again nothing will happen to the output list. It seems the out put list of the query is cached.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Babak Behzadi
  • 1,236
  • 2
  • 16
  • 33
  • are you using @Transactional(readOnly = true) either in your spring controller or in your service layer? In the above code did you check if the spq parameters are set correctly? If possible please provide few more details of how you call from your controller and also how you are populating the list in getAll – Manjunath Aug 18 '14 at 07:38
  • No, I'm not using @Transactional(readOnly = true). I've added controller class code to my question. – Babak Behzadi Aug 18 '14 at 12:53

2 Answers2

0

You can tell hibernate not to fetch from cache by specifying the below hint:-

hints = { @QueryHint(name = "org.hibernate.cacheable", value = "false") },

Try this:- 

@NamedStoredProcedureQueries({
      @NamedStoredProcedureQuery(
        name="proc1",
         resultClass=.....class,
         hints = { @QueryHint(name = "org.hibernate.cacheable", value = "false") },
        procedureName="Proc1",
        parameters={...
        ),
        @NamedStoredProcedureQuery(
        name="proc2",
         resultClass=.....class,
         hints = { @QueryHint(name = "org.hibernate.cacheable", value = "false") },
        procedureName="Proc2",
        parameters={...
        ))
Manjunath
  • 1,685
  • 9
  • 9
  • Thanks, I'd tried it before, but it didn't work. I have no cache configuration on hibernate. According to http://java.dzone.com/articles/pitfalls-hibernate-second-0, I think I should do cache configurations first then this query hint will work??? – Babak Behzadi Aug 17 '14 at 17:57
  • This query hint will work irrespective of whether you have specified hibernate query caching property or not which means with this query hint , hibernate has to always execute the query against database. How did you identify it doesnot work? – Manjunath Aug 17 '14 at 18:04
0

Hibernate doesn't cache query result by default. You have to enable second level cache queries and also instruct the current executing query to be cached.

Hibernate's default 1st level cache is an entity cache which kicks in whenever you load entities: find/load or HQL/Criteria queries.

I don't think your stored procedure results are cached at all.

Your problem may be related to Hibernate AUTO flush mode not triggered by your stored procedure.

Trigger a manual flush before you executed the stored procedure:

em.flush();

Also you didn't call the query getResultList in your getAll method:

So it should be like this:

em.flush();
return spq.getResultList();
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911