Java SQLQuery.list方法代碼示例

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
public List<demo> getList() {
	//create the session
	Session ses=null;
	//get the session
	ses=HibernateUtil.getSession();
		//create the list of class object
		List<demo> list=null;
			//create the SQLquery
	SQLQuery query=ses.createSQLQuery("select * from product");
	query.addEntity(demo.class);
		//execute the query
		list=query.list();

			//close the session
		HibernateUtil.closeSession(ses);
		//return the list
		return list;
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@SuppressWarnings("unchecked")
public List<T> findBySql(String sql, Map<String, Object> map,
		PageBean pageBean, String order) {
	//sql = filter(sql, map);
	sql += (StringUtils.isEmpty(order)) ? "" : " order by " + order;
	System.out.println("sql->" + sql);
	SQLQuery query = hibernateTemplate.getSessionFactory()
			.getCurrentSession().createSQLQuery(sql);
	setPage(query, pageBean);
	setQuery(map, query);
	return query.list();
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
public List<Map> queryForMapList(String querySql, Object... params) {
    Query query = entityManager.createNativeQuery(querySql);
    SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
    nativeQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    if (params != null) {
        for (int i = 0; i < params.length; i++) {
            nativeQuery.setParameter(i, params[i]);
        }
    }
    return nativeQuery.list();
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
public <T> List<T> findBySql(String sql, Object[] vals, int from_id, int len) {
	Session session = hibernateTemplate.getSessionFactory().openSession();
	org.hibernate.Transaction tx = session.beginTransaction(); 
	
	SQLQuery query = session.createSQLQuery(sql);
	if(vals != null) {
		for(int i=0;i<vals.length;i++) {
			if(vals[i] instanceof Integer) {
				query.setInteger(i, (Integer) vals[i]);
			} else {
				query.setString(i, vals[i].toString());
			}
		}
	}
	
	if(len>0) {
		query.setFirstResult(from_id);
		query.setMaxResults(len-1);
	}
	
	try {
		tx.commit();
		return query.list();
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();
		return null;
	} finally {
		session.close(); 
	}
	
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
   @SuppressWarnings("unchecked")
   /** Returns < [surveySession, numUsers] ... [surveySession, numUsers]> */
   public List<Object[]> getStatisticsBySession(final Long contentId) {

SQLQuery query = getSession().createSQLQuery(GET_STATISTICS);
query.addEntity(SurveySession.class).addScalar("numUsers", IntegerType.INSTANCE).setLong("contentId",
	contentId);
return query.list();
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@SuppressWarnings("unchecked")
   @Override
   public List<Number> getRawUserMarksBySession(Long sessionId) {

SQLQuery query = getSession().createSQLQuery(LOAD_MARKS_FOR_SESSION);
query.setLong("sessionId", sessionId);
List<Number> list = query.list();
return list;
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
public String newId(IdType idType) {
    Session session = sessionFactory.getCurrentSession();
    Calendar calendar = Calendar.getInstance();
    String year = calendar.get(Calendar.YEAR) + "";
    int month = calendar.get(Calendar.MONTH) + 1;
    int day = calendar.get(Calendar.DAY_OF_MONTH);

    String prefix = year + (month < 10 ? "0" + month : month) + (day < 10 ? "0" + day : day) + idType.getValue();


    StringBuilder builder = new StringBuilder();
    String sql = builder.append("SELECT MAX(orders_ID) FROM orders WHERE orders_ID LIKE '")
            .append(prefix)
            .append("%' ")
            .toString();
    SQLQuery l = session.createSQLQuery(sql);
    List list = l.list();
    String id = (String) list.get(0);
    if (id == null || "null".equals(id)) {
        //當天沒有，生成新的訂單號
        id = prefix + "00000";
        return id;
    } else {
        long idd = Long.valueOf(id);
        String newid = String.valueOf(idd + 1);
        System.out.println(newid);
        return newid;
    }
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
public List<ForfeitInfo> getForfeitByReader(Reader reader) {
	// TODO Auto-generated method stub
	String hql = "SELECT f.borrowId,f.forfeit,f.isPay,f.aid FROM forfeitinfo  f,borrowinfo  b where  b.borrowId = f.borrowId and b.readerId =?";
	List list = null;
	try{
		Session session = this.getSession();
		SQLQuery createSQLQuery = session.createSQLQuery(hql);
		createSQLQuery.setInteger(0, reader.getReaderId());
		list = createSQLQuery.list();
		if(list!=null){
			List<ForfeitInfo> infos = new ArrayList<ForfeitInfo>();
			for(int i = 0;i<list.size();i++){
				Object[] objects = (Object[]) list.get(i);
				Integer borrowId = (Integer) objects[0];
				Double forfeit = (Double) objects[1];
				Integer isPay = (Integer) objects[2];
				Integer aid = (Integer) objects[3];
				Admin admin = new Admin();
				admin.setAid(aid);
				BorrowInfo info = new BorrowInfo();
				info.setBorrowId(borrowId);
				ForfeitInfo forfeitInfo = new ForfeitInfo();
				forfeitInfo.setAdmin(admin);
				forfeitInfo.setBorrowId(borrowId);
				forfeitInfo.setForfeit(forfeit);
				forfeitInfo.setIsPay(isPay);
				infos.add(forfeitInfo);
			}
			return infos;
		}
	}catch (Throwable e) {
		e.printStackTrace();
		throw new RuntimeException(e.getMessage());
	}
	return null;
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
   @SuppressWarnings("unchecked")
   /**
    * Gets the basic details about an attempt for a nomination. questionUid must not be null, sessionUid may be NULL.
    * This is
    * unusual for these methods - usually sessionId may not be null. In this case if sessionUid is null then you get
    * the values for the whole class, not just the group.
    *
    * Will return List<[login (String), fullname(String), attemptTime(Timestamp]>
    */
   public List<Object[]> getUserAttemptsForTablesorter(Long sessionUid, Long questionUid, int page, int size,
    int sorting, String searchString, IUserManagementService userManagementService) {
String sortingOrder;
switch (sorting) {
    case VoteAppConstants.SORT_BY_NAME_ASC:
	sortingOrder = "user.fullname ASC";
	break;
    case VoteAppConstants.SORT_BY_NAME_DESC:
	sortingOrder = "user.fullname DESC";
	break;
    case VoteAppConstants.SORT_BY_DATE_ASC:
	sortingOrder = "attempt.attempt_time ASC";
	break;
    case VoteAppConstants.SORT_BY_DATE_DESC:
	sortingOrder = "attempt.attempt_time DESC";
	break;
    default:
	sortingOrder = "user.uid";
}

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

// Basic select for the user records
StringBuilder queryText = new StringBuilder(FIND_USER_ANSWERS_BY_QUESTION_UID_SELECT)
	.append(portraitStrings[0])
	.append(FIND_USER_ANSWERS_BY_QUESTION_UID_FROM)
	.append(portraitStrings[1]);

if (sessionUid != null) {
    queryText.append(FIND_USER_ANSWERS_BY_QUESTION_UID_SESSION_ADDITION);
}

// If filtering by name add a name based where clause
buildNameSearch(searchString, queryText, true);

// Now specify the sort based on the switch statement above.
queryText.append(" ORDER BY " + sortingOrder);

SQLQuery query = getSession().createSQLQuery(queryText.toString());
query.addScalar("user_id", IntegerType.INSTANCE).addScalar("username", StringType.INSTANCE)
	.addScalar("fullname", StringType.INSTANCE)
	.addScalar("attemptTime", TimestampType.INSTANCE)
	.addScalar("portraitId", IntegerType.INSTANCE)
	.setLong("questionUid", questionUid.longValue())
	.setFirstResult(page * size).setMaxResults(size);
if (sessionUid != null) {
    query.setLong("sessionUid", sessionUid.longValue());
}

return query.list();
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
   @SuppressWarnings("rawtypes")
   public int getCountUsersForOpenTextEntries(Long sessionUid, Long toolContentId, String searchStringVote,
    String searchStringUsername) {

SQLQuery query;
StringBuilder queryText = new StringBuilder(COUNT_USERS_OPEN_TEXT_BY_SESSION_UID);

if (sessionUid != null) {
    // get all the users who did an open text reply, restricting by session
    queryText.append(FIND_USER_OPEN_TEXT_SESSION_UID_ADD);
    buildCombinedSearch(searchStringVote, searchStringUsername, queryText);
    query = getSession().createSQLQuery(queryText.toString());
    query.setLong("sessionUid", sessionUid);

} else {

    // get all the users for this content (more than one session potentially)
    queryText.append(FIND_USER_OPEN_TEXT_CONTENT_UID_ADD);
    buildCombinedSearch(searchStringVote, searchStringUsername, queryText);
    query = getSession().createSQLQuery(queryText.toString());
    query.setLong("toolContentId", toolContentId);

}

List list = query.list();
if (list == null || list.size() == 0) {
    return 0;
}
return ((Number) list.get(0)).intValue();
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@SuppressWarnings("unchecked")
   @Override
   public List<McUserMarkDTO> getPagedUsersBySession(Long sessionId, int page, int size, String sortBy,
    String sortOrder, String searchString, IUserManagementService userManagementService) {

String[] portraitStrings = userManagementService.getPortraitSQL("user.que_usr_id");

StringBuilder bldr = new StringBuilder(LOAD_USERS_SELECT)
	.append(portraitStrings[0])
	.append(LOAD_USERS_FROM)
	.append(portraitStrings[1])
	.append(LOAD_USERS_JOINWHERE)
	.append(sortOrder);

SQLQuery query = getSession().createSQLQuery(bldr.toString());
query.setLong("sessionId", sessionId);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setString("searchString", searchString);
query.setString("sortBy", sortBy);
query.setFirstResult(page * size);
query.setMaxResults(size);
List<Object[]> list = query.list();

ArrayList<McUserMarkDTO> userDtos = new ArrayList<McUserMarkDTO>();
if (list != null && list.size() > 0) {
    for (Object[] element : list) {

	Long userUid = ((Number) element[0]).longValue();
	Long userId = ((Number) element[1]).longValue();
	String fullName = (String) element[2];
	Integer totalMark = element[3] == null ? 0 : ((Number) element[3]).intValue();
	Long portraitId = element[4] == null ? null : ((Number) element[4]).longValue();

	McUserMarkDTO userDto = new McUserMarkDTO();
	userDto.setQueUsrId(userUid.toString());
	userDto.setUserId(userId.toString());
	userDto.setFullName(fullName);
	userDto.setTotalMark(new Long(totalMark));
	userDto.setPortraitId(portraitId==null ? null : portraitId.toString());
	userDtos.add(userDto);
    }

}

return userDtos;
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
public PageBean<Integer> getBorrowIdList(String iSBN, String paperNO,int borrowId,int pageCode, int pageSize) {
	PageBean<Integer> pb = new PageBean<Integer>();	//pageBean對象，用於分頁
	//根據傳入的pageCode當前頁碼和pageSize頁麵記錄數來設置pb對象
	pb.setPageCode(pageCode);//設置當前頁碼
	pb.setPageSize(pageSize);//設置頁麵記錄數
	
	List<Integer> integers = new ArrayList<Integer>();
	StringBuilder sb = new StringBuilder();
	StringBuilder sb_sql = new StringBuilder();
	String sql = "select count(*) from BackInfo ba ,BorrowInfo bo,Book bk,Reader r "
			+"where ba.borrowId=bo.borrowId and Bk.bookId=Bo.bookId and bo.readerId=r.readerId ";
	//不支持limit分頁
	String hql= "select ba.borrowId from BackInfo ba ,BorrowInfo bo,Book bk,Reader r "
			+"where ba.borrowId=bo.borrowId and Bk.bookId=Bo.bookId and bo.readerId=r.readerId ";
	sb.append(hql);
	sb_sql.append(sql);
	if(!"".equals(iSBN.trim())){
		sb.append(" and bk.ISBN like '%" + iSBN +"%'");
		sb_sql.append(" and bk.ISBN like '%" + iSBN +"%'");
	}
	if(!"".equals(paperNO.trim())){
		sb.append(" and r.paperNO like '%" + paperNO +"%'");
		sb_sql.append(" and r.paperNO like '%" + paperNO +"%'");
	}
	if(borrowId!=0){
		sb.append(" and bo.borrowId like '%" + borrowId +"%'");
		sb_sql.append(" and bo.borrowId like '%" + borrowId +"%'");
	}
	
	try {
		SQLQuery createSQLQuery1 = this.getSession().createSQLQuery(sb_sql.toString());
		List list = createSQLQuery1.list();
		int totalRecord = Integer.parseInt(list.get(0).toString()); //得到總記錄數
		pb.setTotalRecord(totalRecord);	//設置總記錄數
		this.getSession().close();
		
		//不支持limit分頁
		//分頁查詢
		List list2 = doLimitBackInfo(sb.toString(),pageCode,pageSize);
		for(Object object : list2){
			Integer i = new Integer(object.toString());
			integers.add(i);
		}
		pb.setBeanList(integers);
	}catch (Throwable e1) {
		e1.printStackTrace();
		throw new RuntimeException(e1.getMessage());
	}
	

	return pb;
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
   @SuppressWarnings("unchecked")
   /**
    * Will return List<[SurveyUser, String (notebook entry)], [SurveyUser, String (notebook entry)], ... , [SurveyUser,
    * String (notebook entry)]>
    */
   public List<Object[]> getUserReflectionsForTablesorter(final Long sessionId, int page, int size, int sorting,
    String searchString, ICoreNotebookService coreNotebookService, IUserManagementService userManagementService) {
String sortingOrder;
switch (sorting) {
    case SurveyConstants.SORT_BY_NAME_ASC:
	sortingOrder = "user.last_name ASC, user.first_name ASC";
	break;
    case SurveyConstants.SORT_BY_NAME_DESC:
	sortingOrder = "user.last_name DESC, user.first_name DESC";
	break;
    default:
	sortingOrder = "user.uid";
}

// If the session uses notebook, then get the sql to join across to get the entries
String[] notebookEntryStrings = coreNotebookService.getNotebookEntrySQLStrings(sessionId.toString(),
	SurveyConstants.TOOL_SIGNATURE, "user.user_id");

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

// Basic select for the user records
StringBuilder queryText = new StringBuilder();
queryText.append("SELECT user.* ");
queryText.append(notebookEntryStrings[0]);
queryText.append(portraitStrings[0]);
queryText.append(" FROM tl_lasurv11_user user ");
queryText.append(
	" JOIN tl_lasurv11_session session ON user.session_uid = session.uid and session.session_id = :sessionId ");

// Add the notebook & portrait join
queryText.append(notebookEntryStrings[1]);
queryText.append(portraitStrings[1]);

// If filtering by name add a name based where clause
buildNameSearch(searchString, queryText);

// Now specify the sort based on the switch statement above.
queryText.append(" ORDER BY " + sortingOrder);

SQLQuery query = getSession().createSQLQuery(queryText.toString());
query.addEntity("user", SurveyUser.class).addScalar("notebookEntry", StringType.INSTANCE)
	.addScalar("portraitId", IntegerType.INSTANCE)
	.setLong("sessionId", sessionId.longValue()).setFirstResult(page * size).setMaxResults(size);
return query.list();
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@SuppressWarnings("unchecked")
   @Override
   public List<AssessmentUserDTO> getPagedUsersBySession(Long sessionId, int page, int size, String sortBy,
    String sortOrder, String searchString, IUserManagementService userManagementService) {

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

StringBuilder bldr = new StringBuilder(LOAD_USERS_ORDERED_BY_SESSION_SELECT)
	.append(portraitStrings[0])
	.append(LOAD_USERS_ORDERED_BY_SESSION_FROM)
	.append(portraitStrings[1])
	.append(LOAD_USERS_ORDERED_BY_SESSION_JOIN);
if ( "total".equalsIgnoreCase(sortBy) )
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_TOTAL);
else
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_NAME);
bldr.append(sortOrder);

SQLQuery query = getSession().createSQLQuery(bldr.toString());
query.setLong("sessionId", sessionId);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setString("searchString", searchString);
query.setFirstResult(page * size);
query.setMaxResults(size);
List<Object[]> list = query.list();

ArrayList<AssessmentUserDTO> userDtos = new ArrayList<AssessmentUserDTO>();
if (list != null && list.size() > 0) {
    for (Object[] element : list) {

	Long userId = ((Number) element[0]).longValue();
	String firstName = (String) element[1];
	String lastName = (String) element[2];
	String login = (String) element[3];
	float grade = element[4] == null ? 0 : ((Number) element[4]).floatValue();
	Long portraitId = element[5] == null ? null : ((Number) element[5]).longValue();

	AssessmentUserDTO userDto = new AssessmentUserDTO();
	userDto.setUserId(userId);
	userDto.setFirstName(firstName);
	userDto.setLastName(lastName);
	userDto.setLogin(login);
	userDto.setGrade(grade);
	userDto.setPortraitId(portraitId);
	userDtos.add(userDto);
    }

}

return userDtos;
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@SuppressWarnings("unchecked")
   @Override
   public List<AssessmentUserDTO> getPagedUsersBySessionAndQuestion(Long sessionId, Long questionUid, int page,
    int size, String sortBy, String sortOrder, String searchString, IUserManagementService userManagementService) {

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

StringBuilder bldr = new StringBuilder(LOAD_USERS_ORDERED_BY_SESSION_QUESTION_SELECT)
	.append(portraitStrings[0])
	.append(LOAD_USERS_ORDERED_BY_SESSION_QUESTION_FROM)
	.append(portraitStrings[1])
	.append(LOAD_USERS_ORDERED_BY_SESSION_QUESTION_JOIN);
if ( "grade".equalsIgnoreCase(sortBy) )
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_RESULT);
else
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_NAME);
bldr.append(sortOrder);

SQLQuery query = getSession().createSQLQuery(bldr.toString());
query.setLong("sessionId", sessionId);
query.setLong("questionUid", questionUid);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setString("searchString", searchString);
query.setFirstResult(page * size);
query.setMaxResults(size);
List<Object[]> list = query.list();

ArrayList<AssessmentUserDTO> userDtos = new ArrayList<AssessmentUserDTO>();
if (list != null && list.size() > 0) {
    for (Object[] element : list) {

	Long questionResultUid = ((Number) element[0]).longValue();
	String firstName = (String) element[1];
	String lastName = (String) element[2];
	String login = (String) element[3];
	float grade = element[4] == null ? 0 : ((Number) element[4]).floatValue();
	Long portraitId  = element[5] == null ? null : ((Number) element[5]).longValue();

	AssessmentUserDTO userDto = new AssessmentUserDTO();
	userDto.setQuestionResultUid(questionResultUid);
	;
	userDto.setFirstName(firstName);
	userDto.setLastName(lastName);
	userDto.setLogin(login);
	userDto.setGrade(grade);
	userDto.setPortraitId(portraitId);
	userDtos.add(userDto);
    }

}

return userDtos;
   }