Talk:Hibernate

From BC$ MobileTV Wiki
Jump to: navigation, search

Batch Processing

Three ways to do date range lookups

1. Old-school way, using String Builder and regular HQL query:

	        	//date formatter required for converting start and end dates from Java to DB and vice versa
         		SimpleDateFormat theDate = new SimpleDateFormat("yyyy.MM.dd");
		
		        //track whether to use WHERE or AND
	        	boolean isFirst = true; 
		
         		//convert case of table name as passed in via command-line or properties file
        		String theTable = toCamelCase(tableName);

                        StringBuilder query = new StringBuilder("from " + theTable + " ");

			//figure out which statement comes first and lead with WHERE, otherwise use AND condition
			if(startDate != null) {
				if(isFirst) {
					query.append(" where adjudicationDate >= '" + theDate.format(startDate) + "'");
				} else {
					query.append(" and adjudicationDate >= '" + theDate.format(startDate) + "'");
				}
				isFirst = false;
			} 
			if(endDate != null) {
				if(isFirst) {
					query.append(" where adjudicationDate <= '" + theDate.format(endDate) + "'");
				} else {
					query.append(" and adjudicationDate <= '" + theDate.format(endDate) + "'");
				}
				isFirst = false;
			}

			//create query and ORDER BY ADJUDICATION_DATE DESC
			query.append(" order by adjudicationDate asc");
			Query sql = session.createQuery(query.toString());
			ScrollableResults itemCursor = sql.scroll();



2. Slightly better way; using parameterized query and "between" filter:

                        StringBuilder query = new StringBuilder("from " + theTable + " ");

                        //only works if startDate and endDate are actually set
                        Date startDate = (null != start) ? start : DEFAULT_START_DATE;
                        Date endDate = (null != end) ? end : DEFAULT_END_DATE; //this could be anything, for "today" use ''new Date();''

			query.append(" where adjudicationDate between :startDate and :endDate order by adjudicationDate asc");
			Query sql = session.createQuery(query.toString());
		   	      sql.setDate("startDate", startDate);
		              sql.setDate("endDate", endDate);
			ScrollableResults itemCursor = sql.scroll();


3. Best way; Criteria API and Restrictions:

			Criteria criteria = session.createCriteria(XtdClaimHistory.class);
			if(startDate!=null) {
				 criteria.add(Restrictions.ge("adjudicationDate",startDate));
			}
			else if(endDate!=null) {
				 criteria.add(Restrictions.le("adjudicationDate",endDate));
			}
		                 criteria.addOrder(Order.asc("adjudicationDate"));
			ScrollableResults itemCursor = criteria.scroll();


Scollable results are used because they are better for high-volume or batch. After each of these, you can loop on the Results of the query as follows:

			int count = 0;
			while(itemCursor.next()) {														
				//get claim data from XTD_CLAIMS_HISTORY table
				Claim claim = (Claim) itemCursor.get(0);
                                Long id = claim.getId();
                                Date date = claim.getDate();
                                String name = claim.getName();
                        }