Java SQLQuery.addEntity方法代碼示例

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包/類
public static void main(String[] args) {
	Configuration cfg=null;
	SessionFactory sf=null;
	Session ses=null;
	//activate the hibernate framework
		cfg=new Configuration().configure("hibernate.cfg.xml");
		//build the session factory
		sf=cfg.buildSessionFactory();
		//open/get the session
		ses=sf.openSession();
		
		//create the model class object and set the data
		Employee e=new Employee();
		
		//create then sql query
		
		String sql="SELECT * FROM EMP_NATIVE";
			SQLQuery q=ses.createSQLQuery(sql);
			q.addEntity(Employee.class);
	//execute the query
		List<Employee> emplist=q.list();
		//display the data
		emplist.forEach(row->{
			System.out.println("\t"+row.getId()+"\t"+row.getName()+"\t"+row.getSal());
		});
	
				//close the session factory and session
				ses.close();
				sf.close();
	
}
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
   public List<User> getUsersByLesson(Long lessonId, int page, int size, String sortBy, String sortOrder,
    String searchString) {

final String LOAD_LEARNERS_ORDERED_BY_NAME = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id " + " WHERE lesson.lesson_id = :lessonId "
	+ " AND lesson.class_grouping_id=g.grouping_id " + " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY CONCAT(user.last_name, ' ', user.first_name) " + sortOrder;

//when :sortBy='timeTaken'
final String LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_learner_progress progress "
	+ " ON progress.user_id=user.user_id AND progress.lesson_id=:lessonId "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY TIMEDIFF(progress.finish_date_time, progress.start_date_time) " + sortOrder;

//when :sortBy='mark'
final String LOAD_LEARNERS_ORDERED_BY_MARK = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_gradebook_user_lesson gradebookUserLesson "
	+ " ON user.user_id=gradebookUserLesson.user_id AND gradebookUserLesson.lesson_id =:lessonId "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY gradebookUserLesson.mark " + sortOrder;

//when :sortBy='feedback'
final String LOAD_LEARNERS_ORDERED_BY_FEEDBACK = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_gradebook_user_lesson gradebookUserLesson "
	+ " ON user.user_id=gradebookUserLesson.user_id AND gradebookUserLesson.lesson_id =:lessonId "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY gradebookUserLesson.feedback " + sortOrder;

String queryString;
if (sortBy.equals("timeTaken")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN;
} else if (sortBy.equals("mark")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_MARK;
} else if (sortBy.equals("feedback")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_FEEDBACK;
} else {
    queryString = LOAD_LEARNERS_ORDERED_BY_NAME;
}

SQLQuery query = getSession().createSQLQuery(queryString);
query.addEntity(User.class);
query.setLong("lessonId", lessonId);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setString("searchString", searchString);
query.setFirstResult(page * size);
query.setMaxResults(size);
return query.list();
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
   public List<User> getUsersByActivity(Long lessonId, Long activityId, int page, int size, String sortBy,
    String sortOrder, String searchString) {

final String LOAD_LEARNERS_ORDERED_BY_NAME = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id " + " WHERE lesson.lesson_id = :lessonId "
	+ " AND lesson.class_grouping_id=g.grouping_id " + " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY CONCAT(user.last_name, ' ', user.first_name) " + sortOrder;

//when :sortBy='timeTaken'
final String LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN_ACTIVITY = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_learner_progress progress "
	+ " INNER JOIN lams_progress_completed completedActivityProgress "
	+ " ON completedActivityProgress.learner_progress_id=progress.learner_progress_id "
	+ " AND completedActivityProgress.activity_id=:activityId " + " ON progress.user_id=user.user_id "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY TIMEDIFF(completedActivityProgress.completed_date_time, completedActivityProgress.start_date_time) "
	+ sortOrder;

//when :sortBy='mark'
final String LOAD_LEARNERS_ORDERED_BY_MARK_ACTIVITY = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_gradebook_user_activity gradebookUserActivity "
	+ " ON user.user_id=gradebookUserActivity.user_id AND gradebookUserActivity.activity_id =:activityId "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY gradebookUserActivity.mark " + sortOrder;

String queryString;
if (sortBy.equals("timeTaken")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN_ACTIVITY;
} else if (sortBy.equals("mark")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_MARK_ACTIVITY;
} else {
    queryString = LOAD_LEARNERS_ORDERED_BY_NAME;
}

SQLQuery query = getSession().createSQLQuery(queryString);
query.addEntity(User.class);
query.setLong("lessonId", lessonId);
if (sortBy.equals("timeTaken") || sortBy.equals("mark")) {
    query.setLong("activityId", activityId);
}
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setString("searchString", searchString);
query.setFirstResult(page * size);
query.setMaxResults(size);
return query.list();
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
@Override
   public List<User> getUsersByGroup(Long lessonId, Long activityId, Long groupId, int page, int size, String sortBy,
    String sortOrder, String searchString) {

final String LOAD_LEARNERS_ORDERED_BY_NAME = "SELECT DISTINCT user.* " + " FROM lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id " + " WHERE ug.group_id=:groupId "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY CONCAT(user.last_name, ' ', user.first_name) " + sortOrder;

//when :sortBy='timeTaken'
final String LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN_GROUP = "SELECT DISTINCT user.* " + " FROM lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_learner_progress progress "
	+ " INNER JOIN lams_progress_completed completedActivityProgress "
	+ " ON completedActivityProgress.learner_progress_id=progress.learner_progress_id "
	+ " AND completedActivityProgress.activity_id=:activityId " + " ON progress.user_id=user.user_id "
	+ " WHERE ug.group_id=:groupId "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY TIMEDIFF(completedActivityProgress.completed_date_time, completedActivityProgress.start_date_time) "
	+ sortOrder;

//when :sortBy='mark'
final String LOAD_LEARNERS_ORDERED_BY_MARK_GROUP = "SELECT DISTINCT user.* " + " FROM lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_gradebook_user_activity gradebookUserActivity "
	+ " ON user.user_id=gradebookUserActivity.user_id AND gradebookUserActivity.activity_id =:activityId "
	+ " WHERE ug.group_id=:groupId "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY gradebookUserActivity.mark " + sortOrder;

String queryString;
if (sortBy.equals("timeTaken")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN_GROUP;
} else if (sortBy.equals("mark")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_MARK_GROUP;
} else {
    queryString = LOAD_LEARNERS_ORDERED_BY_NAME;
}

SQLQuery query = getSession().createSQLQuery(queryString);
query.addEntity(User.class);
if (sortBy.equals("timeTaken") || sortBy.equals("mark")) {
    query.setLong("activityId", activityId);
}
query.setLong("groupId", groupId);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setString("searchString", searchString);
query.setFirstResult(page * size);
query.setMaxResults(size);
return query.list();
   }
 

import org.hibernate.SQLQuery; //導入方法依賴的package包/類
public static Query createSearchQuery(CriteriaInstantVO criteriaInstantVO, DBModule module, PSFVO psf, SessionFactory sessionFactory,
		CriterionTieDao criterionTieDao, CriterionPropertyDao criterionPropertyDao, CriterionRestrictionDao criterionRestrictionDao) throws Exception {
	ArrayList<QueryParameterValue> queryValues = new ArrayList<QueryParameterValue>();
	HashMap<NamedParameterValues, Object> namedParameterValuesCache = new HashMap<NamedParameterValues, Object>();
	StringBuilder sqlQuery = createSQLSetStatement(criteriaInstantVO, module, psf, sessionFactory, queryValues,
			createCriterionTieMap(criterionTieDao),
			createCriterionTieNameMap(criterionTieDao),
			createCriterionPropertyMap(module, criterionPropertyDao),
			// null, // createCriterionPropertyNameMap(module, criterionPropertyDao),
			createCriterionRestrictionMap(criterionRestrictionDao),
			null); // createCriterionRestrictionNameMap(criterionRestrictionDao));
	Class rootEntityClass = getRootEntityClass(module);
	SQLQuery query;
	if (psf != null) {
		if (psf.getUpdateRowCount()) {
			StringBuilder countStatement = new StringBuilder("select count(*) from (");
			countStatement.append(sqlQuery);
			countStatement.append(") as resultset");
			Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(countStatement.toString());
			setQueryValues(countQuery, queryValues, namedParameterValuesCache);
			psf.setRowCount(new Long(countQuery.uniqueResult().toString()));
		}
		String sortField = psf.getSortField();
		if (sortField != null && sortField.length() > 0) {
			// http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx
			sqlQuery.append(" order by ");
			sqlQuery.append(getPropertyColumnName(rootEntityClass, sortField, sessionFactory)); // sessionFactory));
			if (psf.getSortOrder()) {
				sqlQuery.append(" asc");
			} else {
				sqlQuery.append(" desc");
			}
		}
		query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery.toString());
		if (psf.getFirst() != null) {
			query.setFirstResult(psf.getFirst());
		}
		if (psf.getPageSize() != null) {
			query.setMaxResults(psf.getPageSize());
		}
	} else {
		query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery.toString());
	}
	setQueryValues(query, queryValues, namedParameterValuesCache);
	query.addEntity(rootEntityClass);
	return query;
}