public void AverageRatingByProductName(String prodName) {
SessionFactory sf = HibernateUtility.getSessionfactory();
Session session = sf.openSession();
session.beginTransaction();
String sql1 = "SELECT * FROM product where productName = :productName";
SQLQuery query1 = session.createSQLQuery(sql1);
query1.setParameter("productName", prodName);
query1.addEntity(Product.class);
Product product = (Product) query1.uniqueResult();
String sql2 = "SELECT * FROM ratingdetails where productId = :productId";
SQLQuery query2 = session.createSQLQuery(sql2);
query2.setParameter("productId", product.getProductId());
query2.addEntity(RatingDetails.class);
List ratings = query2.list();
session.getTransaction().commit();
float count = ratings.size();
int sum = 0;
for (Object r : ratings) {
RatingDetails rate = (RatingDetails) r;
sum = sum + rate.getRating();
}
float averageRating = (sum / count);
System.out.println("pid " + product + " avg " + averageRating);
}
public List<DeviceList> getDeviceList(String location) {
SessionFactory sessionFactory = HibernateSessionFactory.getSessionFactory();
List<DeviceList> results = new ArrayList<DeviceList>();
Session session = sessionFactory.openSession();
if (session != null) {
SQLQuery query =
session.createSQLQuery(
"select d.*,da.*,du.* "
+ "from device_app.device d, device_app.device_availability da, device_app.device_user du "
+ "where d.id=da.device_id and "
+ "da.checked_out_user = du.email_id and "
+ "d.device_status='active' and "
+ "du.user_location=:location");
query.addEntity(Device.class);
query.addEntity(DeviceAvailability.class);
query.addEntity(DeviceUser.class);
query.setString("location", location);
List<Object[]> resultList = query.list();
if (resultList != null && resultList.size() > 0) {
System.out.println("results size is " + resultList.size());
for (Object[] array : resultList) {
DeviceList row = new DeviceList();
for (Object resultObject : array) {
System.out.println(resultObject.getClass());
if (resultObject instanceof Device) {
Device device = (Device) resultObject;
row.setBarCodeIdentifier(device.getBarCodeId());
row.setDeviceCapacity(device.getDeviceCapacity());
row.setDeviceName(device.getDeviceName());
row.setDeviceNickname(device.getDeviceNickName());
row.setDeviceOs(device.getDeviceOs());
row.setDeviceOsVersion(device.getDeviceOsVersion());
row.setDeviceSerialNumber(device.getDeviceSerialNumber());
row.setDeviceStatus(device.getDeviceStatus());
row.setDeviceType(device.getDeviceType());
row.setId(device.getId());
row.setOwnedBy(device.getDeviceOwner());
} else if (resultObject instanceof DeviceAvailability) {
DeviceAvailability availability = (DeviceAvailability) resultObject;
//
// row.setCheckedOutAt(DateUtils.truncate(availability.getModifiedDate(),Calendar.MINUTE).toString());
row.setCheckedOutAt(
DateFormatUtils.format(availability.getModifiedDate(), "yyyy-MM-dd HH:mm:ss"));
row.setCurrentlyCheckedOutById(availability.getDeviceUser().getEmailId());
row.setIsCheckedOut(availability.isIsCheckedOut());
row.setLocation(location);
} else {
DeviceUser user = (DeviceUser) resultObject;
}
}
results.add(row);
}
}
}
return results;
}
public List nativeSQLQuery(final String queryString, Class clazz) {
SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
if (clazz != null) {
sqlQuery = sqlQuery.addEntity(clazz);
}
return sqlQuery.list();
}
/**
* 根据用户ID查询该用户允许访问的所有菜单列表
*
* @param userId
* @return
*/
@SuppressWarnings("unchecked")
public List<Menu> getAllowedAccessMenu(Long userId) {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("select * from (");
// 获取Sec_Menu表中定义且未关联资源表Sec_Resource的所有菜单列表
sqlBuffer.append(" select m.id,m.name,m.parent_menu,m.description,m.orderby from sec_menu m ");
sqlBuffer.append(" where not exists (select re.id from sec_resource re where re.menu = m.id)");
sqlBuffer.append(" union all ");
// 获取Sec_Resource表中已关联且未设置权限的菜单列表
sqlBuffer.append(
" select m.id,m.name,m.parent_menu,re.source as description,m.orderby from sec_resource re ");
sqlBuffer.append(" left outer join sec_menu m on re.menu = m.id ");
sqlBuffer.append(
" where re.menu is not null and not exists (select ar.authority_id from sec_authority_resource ar where ar.resource_id = re.id)");
sqlBuffer.append(" union all ");
// 获取Sec_Resource表中已关联且设置权限，并根据当前登录账号拥有相应权限的菜单列表
sqlBuffer.append(
" select m.id,m.name,m.parent_menu,re.source as description,m.orderby from sec_user u ");
sqlBuffer.append(" left outer join sec_role_user ru on u.id=ru.user_id ");
sqlBuffer.append(" left outer join sec_role r on ru.role_id=r.id ");
sqlBuffer.append(" left outer join sec_role_authority ra on r.id = ra.role_id ");
sqlBuffer.append(" left outer join sec_authority a on ra.authority_id = a.id ");
sqlBuffer.append(" left outer join sec_authority_resource ar on a.id = ar.authority_id ");
sqlBuffer.append(" left outer join sec_resource re on ar.resource_id = re.id ");
sqlBuffer.append(" left outer join sec_menu m on re.menu = m.id ");
sqlBuffer.append(" where u.id=? and re.menu is not null ");
sqlBuffer.append(") tbl order by orderby");
SQLQuery query = menuDao.createSQLQuery(sqlBuffer.toString(), userId);
query.addEntity(Menu.class);
return query.list();
}
@SuppressWarnings("unchecked")
public static <E> List<E> find(
Session session, String sql, Class<E> clazz, PageInfo pageInfo, Object... params) {
List<E> list = new ArrayList<E>();
SQLQuery sqlQuery = session.createSQLQuery(sql);
setParameter(sqlQuery, params);
if (pageInfo != null) {
Integer first = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize();
// if(ValidateUtil.isEmpty(first)){
// first = 1;
// }
sqlQuery.setFirstResult(first);
@SuppressWarnings("unused")
Integer pageSize = pageInfo.getPageSize();
// if(Validator.isEmpty(pageSize)){
// pageSize = PageInfoUtil.getDefaultPagesize();
// }
sqlQuery.setMaxResults(pageInfo.getPageSize());
Integer total = getCount(session, sql, params);
pageInfo.setPageTotal(total);
}
sqlQuery = sqlQuery.addEntity("model", clazz);
list = sqlQuery.list();
return list;
}
/*
Gets the LoginID ignoring case.
*/
private Login findByIdOracleInsensitive(org.openiam.idm.srvc.auth.dto.LoginId id) {
log.debug("findByIdOracleInsensitive..");
String select =
" select /*+ INDEX(IDX_LOGIN_UPPER) */ "
+ " SERVICE_ID, LOGIN, MANAGED_SYS_ID, IDENTITY_TYPE, CANONICAL_NAME, USER_ID, PASSWORD, "
+ " PWD_EQUIVALENT_TOKEN, PWD_CHANGED, PWD_EXP, RESET_PWD, FIRST_TIME_LOGIN, IS_LOCKED, STATUS, "
+ " GRACE_PERIOD, CREATE_DATE, CREATED_BY, CURRENT_LOGIN_HOST, AUTH_FAIL_COUNT, LAST_AUTH_ATTEMPT, "
+ " LAST_LOGIN, IS_DEFAULT, PWD_CHANGE_COUNT, LAST_LOGIN_IP, PREV_LOGIN, PREV_LOGIN_IP, "
+ " PSWD_RESET_TOKEN, PSWD_RESET_TOKEN_EXP, LOGIN_ATTR_IN_TARGET_SYS "
+ " FROM LOGIN "
+ " WHERE SERVICE_ID = :serviceId AND UPPER(LOGIN) = :login AND MANAGED_SYS_ID = :managedSysId ";
log.debug("SQL to get Identity: " + select);
Session session = sessionFactory.getCurrentSession();
SQLQuery qry = session.createSQLQuery(select);
qry.addEntity(Login.class);
qry.setString("serviceId", id.getDomainId());
qry.setString("login", id.getLogin().toUpperCase());
qry.setString("managedSysId", id.getManagedSysId());
try {
return (Login) qry.uniqueResult();
} catch (Exception e) {
log.error(e.toString());
}
return null;
}
/**
* 拿到所有的根类别
*
* @return
*/
public List<Category> getTopCategories() {
String sql = "select * from znzn_category where grade=1";
Session session = sessionFactory.getCurrentSession();
SQLQuery q = session.createSQLQuery(sql);
List<Category> list = q.addEntity(Category.class).list();
return list;
}
public static List<Agreement> findFiltered(
String agreementId,
String billUcn,
String agreementLinkId,
String agreementTypeCode,
String note,
char neStatus) {
try {
String sqlQuery = "SELECT agreement.* FROM agreement WHERE `status` <> '" + neStatus + "' ";
if (agreementId != null && agreementId.length() > 0)
sqlQuery += " AND id like '%" + agreementId + "%' ";
if (billUcn != null && billUcn.length() > 0)
sqlQuery += " AND billUcn like '%" + billUcn + "%'";
if (agreementLinkId != null && agreementLinkId.length() > 0)
sqlQuery += " AND agreementLinkId like '%" + agreementLinkId + "%'";
if (agreementTypeCode != null && agreementTypeCode.length() > 0)
sqlQuery += " AND agreementTypeCode like '%" + agreementTypeCode + "%'";
if (note != null && note.length() > 0) sqlQuery += " AND note like '%" + note + "%'";
sqlQuery += " order by id";
SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery);
query.addEntity(getObjectReference(objectName));
@SuppressWarnings("unchecked")
List<Agreement> objects = query.list();
return objects;
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
}
return new ArrayList<Agreement>();
}
/**
* Get user details based on the email\password combo. Used in case of login. TODO: Add check for
* access Token from session
*/
public User getUserDetails(String email, String passWord) {
User user = null;
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
user = new User();
String sql = "SELECT * FROM USER WHERE EMAIL = :email_id && PASSW = :password";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(User.class);
query.setParameter("email_id", email);
query.setParameter("password", passWord);
java.util.List results = (java.util.List) query.list();
for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) {
user = (User) iterator.next();
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return user;
}
public ArrayList<com.application.baatna.bean.Session> getNearbyUsers(int userId) {
ArrayList<com.application.baatna.bean.Session> users = null;
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
users = new ArrayList<com.application.baatna.bean.Session>();
String sql = "SELECT * FROM SESSION WHERE USERID = :user_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("user_id", userId);
java.util.List results = (java.util.List) query.list();
for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) {
users.add((com.application.baatna.bean.Session) iterator.next());
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return users;
}
public boolean editProfilePic(int userId, String profilePic) {
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql =
"UPDATE USER SET PROFILE_PIC = :profilePic, MODIFIED = :modified WHERE USERID = :user_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(User.class);
query.setParameter("profilePic", profilePic);
query.setParameter("user_id", userId);
query.setParameter("modified", System.currentTimeMillis());
int result = query.executeUpdate();
User user = getUserDetails(userId);
user.setModified(System.currentTimeMillis());
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
e.printStackTrace();
System.out.println("error");
return false;
} finally {
if (session != null && session.isOpen()) session.close();
}
return true;
}
/**
* 拿到某一个目录的下一级子类别
*
* @return
*/
public List<Category> getChilds(int id) {
String sql = "select * from znzn_category where pid=" + id;
Session session = sessionFactory.getCurrentSession();
SQLQuery q = session.createSQLQuery(sql);
List<Category> list = q.addEntity(Category.class).list();
return list;
}
/** Removes the push notification Id */
public int nullifyPushId(int userId) {
Session session = null;
int pushId = 0;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql = "DELETE FROM SESSION WHERE PUSHID = :pushId && USERID = :user_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("pushId", pushId);
query.setParameter("user_id", userId);
int result = query.executeUpdate();
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
e.printStackTrace();
System.out.println("error");
return -1;
} finally {
if (session != null && session.isOpen()) session.close();
}
return pushId;
}
/** Delete an accessToken for a particular user */
public boolean nullifyAccessToken(int userId, String accessToken) {
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql = "DELETE FROM SESSION WHERE ACCESS_TOKEN = :access_token && USERID = :user_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("access_token", accessToken);
query.setParameter("user_id", userId);
int result = query.executeUpdate();
transaction.commit();
session.close();
return true;
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return false;
}
public com.application.baatna.bean.Session getSession(int userId) {
com.application.baatna.bean.Session loginSession = null;
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
loginSession = new com.application.baatna.bean.Session();
// finding user info
System.out.println("Getting Record");
String sql = "SELECT * FROM SESSION WHERE USERID = :userid";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("userid", userId);
java.util.List results = (java.util.List) query.list();
for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) {
loginSession = (com.application.baatna.bean.Session) iterator.next();
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return loginSession;
}
public boolean addUserToBlockedList(int blockingUserId, int userId) {
Session session = null;
boolean retVal = false;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql =
"INSERT INTO BLOCKING(BLOCKING_USERID,BLOCKED_USERID) VALUES(:blockingUserId,:userId)";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Blocking.class);
query.setParameter("userId", userId);
query.setParameter("blockingUserId", blockingUserId);
query.executeUpdate();
retVal = true;
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
e.printStackTrace();
} finally {
if (session != null && session.isOpen()) session.close();
}
return retVal;
}
public List<ChatMessageKeywords> loadBySQL(String sql) {
Session session = sessionFactory.getCurrentSession();
SQLQuery q = session.createSQLQuery(sql);
List<ChatMessageKeywords> list = q.addEntity(ChatMessageKeywords.class).list();
return list;
}
@Override
public List<LanguageProficiency> findByMemberId(int memberId) {
SQLQuery sqlQuery =
getNamedSQLQuery("resume.query.updateResume.getLanguageProficiencyByMemberId");
sqlQuery.setParameter("memberId", memberId);
sqlQuery.addEntity(LanguageProficiency.class);
return (List<LanguageProficiency>) sqlQuery.list();
}
public List<Category> getAllChilds(List<Category> list, String name) {
String sql = "select * from znzn_category where name='" + name + "'";
Session session = sessionFactory.getCurrentSession();
SQLQuery q = session.createSQLQuery(sql);
Category category = (Category) q.addEntity(Category.class).uniqueResult();
int id = category.getId();
return getAllChilds(list, id);
}
/** Get the user details based on the userId */
public Object[] getRating(int currentUserId, int userId) {
Object[] objects = new Object[2];
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql =
"SELECT count(*) FROM USERWISH WHERE ( USERID = :currentUserId and USER_TWO_ID =:userId) or (USERID = :currentUserId1 and USER_TWO_ID =:userId1)";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(User.class);
query.setParameter("currentUserId", currentUserId);
query.setParameter("userId", userId);
query.setParameter("currentUserId1", userId);
query.setParameter("userId1", currentUserId);
java.util.List results = (java.util.List) query.list();
// check if the current user can rate the userId.
if (results.get(0) instanceof BigInteger && ((BigInteger) results.get(0)).intValue() > 0) {
objects[0] = true;
// fetch the previous rating value
String userRatingSql =
"SELECT Rating FROM UserRating WHERE Reviewer =:reviewer and Reviewed = :reviewed";
SQLQuery userRatingQuery = session.createSQLQuery(userRatingSql);
userRatingQuery.addEntity(User.class);
userRatingQuery.setParameter("reviewer", currentUserId);
userRatingQuery.setParameter("reviewed", userId);
java.util.List userRatingResults = (java.util.List) query.list();
if (userRatingResults.get(0) instanceof BigInteger) {
objects[1] = ((BigInteger) results.get(0)).intValue();
}
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return objects;
}
public List<Member> loadBySQL(String sql) {
SessionFactory sf = HibernateUtil.getSessionFactory();
Session session = sf.getCurrentSession();
session.beginTransaction();
SQLQuery q = session.createSQLQuery(sql);
List<Member> list = q.addEntity(Member.class).list();
session.getTransaction().commit();
return list;
}
public Query createNativeQuery(String sqlString, Class resultClass) {
try {
SQLQuery q = getSession().createSQLQuery(sqlString);
q.addEntity("alias1", resultClass.getName(), LockMode.READ);
return new QueryImpl(q, this);
} catch (HibernateException he) {
throw convert(he);
}
}
public boolean enameExists(String ename) {
String sql = "select * from znzn_category where ename=?;";
Session session = sessionFactory.getCurrentSession();
SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.setString(0, ename);
List<Category> list = sqlQuery.addEntity(Category.class).list();
if (list.size() > 0) return true;
return false;
}
public void calulateAverageRating() {
SessionFactory sf = HibernateUtility.getSessionfactory();
Session session = sf.openSession();
session.beginTransaction();
String sql = "SELECT * FROM product";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Product.class);
List listProductIds = query.list();
for (Object pro : listProductIds) {
Product p = (Product) pro;
String sql1 = "SELECT * FROM ratingdetails where productId = :productId";
SQLQuery query1 = session.createSQLQuery(sql1);
query1.setParameter("productId", p.getProductId());
query1.addEntity(RatingDetails.class);
List ratings = query1.list();
session.getTransaction().commit();
float count = ratings.size();
int sum = 0;
for (Object r : ratings) {
RatingDetails rate = (RatingDetails) r;
sum = sum + rate.getRating();
}
float averageRating = (sum / count);
avgProdList.put(p, averageRating);
System.out.println("pid " + p.getProductId() + " avg " + averageRating);
}
System.out.println(avgProdList);
System.out.println();
sortAverageRating();
/*for (Iterator iterator = listProductIds.iterator(); iterator.hasNext();) {
RatingDetails ratingobj = (RatingDetails) iterator.next();
System.out.print("First Name: " + employee.getFirstName());
System.out.print(" Last Name: " + employee.getLastName());
System.out.println(" Salary: " + employee.getSalary());
}*/
}
/**
* 使用原生sql根据应用专题分页查询应用信息列表
*
* @param page
* @param sql
* @return [参数说明]
* @return List<DpAppInfo> [返回类型说明]
* @exception throws [违例类型] [违例说明]
*/
@SuppressWarnings("unchecked")
@Override
public List<DpAppInfo> queryForPageBySql(Page<AppSubjectType> page, String sql) {
Session session = getSession();
SQLQuery sq = session.createSQLQuery(sql);
sq.addEntity(DpAppInfo.class);
sq.setFirstResult(page.getBeginCount());
sq.setMaxResults(page.getPageSize());
return sq.list();
}
/**
* 原生SQL查询, 没有完善，慎用。。。
*
* @return List<T>
*/
public List<T> findByJdbc(String sql, Object... values) {
SQLQuery sqlQuery = getSession().createSQLQuery(sql);
if (values != null) {
for (int i = 0; i < values.length; i++) {
sqlQuery.setString(i, values[i].toString());
}
}
List<T> results = sqlQuery.addEntity(entityClass).list();
return results;
}
/**
* Given a <code>Filter</code> object describing any/all of the following fields:
*
* <ul>
* <li>gene chromosome
* <li>gene ID
* <li>gene name
* <li>gene symbol
* <li>MGI reference
* </ul>
*
* , this method performs a query, ANDing all non-empty fields in a WHERE clause against the genes
* table. The result is a <code>List<GenesDAO
* ></code> of qualifying results. A list is always returned, even if there are no results.
*
* @param filter values to filter by
* @return a list of <code>GenesDAO</code>.
*/
public List<GenesDAO> getFilteredGenesList(Filter filter) {
String chromosomeWhere = "";
String geneIdWhere = "";
String geneNameWhere = "";
String geneSymbolWhere = "";
String mgiReferenceWhere = "";
List<GenesDAO> targetList = new ArrayList();
int geneId = -1;
String queryString = "SELECT * FROM genes\nWHERE (1 = 1)";
if ((filter.getChromosome() != null) && (!filter.getChromosome().isEmpty())) {
chromosomeWhere = " AND (chromosome = :chromosome)\n";
queryString += chromosomeWhere;
}
Integer iGeneId = Utils.tryParseInt(filter.getGeneId());
if ((iGeneId != null) && (iGeneId.intValue() > 0)) {
geneId = iGeneId.intValue();
geneIdWhere = " AND (id_gene = :id_gene)\n";
queryString += geneIdWhere;
}
if ((filter.getGeneName() != null) && (!filter.getGeneName().isEmpty())) {
geneNameWhere = " AND (name LIKE :name)\n";
queryString += geneNameWhere;
}
if ((filter.getGeneSymbol() != null) && (!filter.getGeneSymbol().isEmpty())) {
geneSymbolWhere = " AND (symbol LIKE :symbol)\n";
queryString += geneSymbolWhere;
}
if ((filter.getMgiReference() != null) && (!filter.getMgiReference().isEmpty())) {
mgiReferenceWhere = " AND (mgi_ref LIKE :mgi_ref)\n";
queryString += mgiReferenceWhere;
}
queryString += "ORDER BY name\n";
Session session = factory.getCurrentSession();
try {
session.beginTransaction();
SQLQuery query = session.createSQLQuery(queryString);
if (!chromosomeWhere.isEmpty()) query.setParameter("chromosome", filter.getChromosome());
if (!geneIdWhere.isEmpty()) query.setParameter("id_gene", geneId);
if (!geneNameWhere.isEmpty()) query.setParameter("name", "%" + filter.getGeneName() + "%");
if (!geneSymbolWhere.isEmpty())
query.setParameter("symbol", "%" + filter.getGeneSymbol() + "%");
if (!mgiReferenceWhere.isEmpty())
query.setParameter("mgi_ref", "%" + filter.getMgiReference() + "%");
targetList = query.addEntity(GenesDAO.class).list();
session.getTransaction().commit();
} catch (HibernateException e) {
session.getTransaction().rollback();
throw e;
}
return targetList;
}
public void setUserDayRating() {
Session session = null;
double rating = 0;
int userId;
double avg1;
double avg2;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
// String sql = "SELECT er.Reviewed, (Select avg(Rating) as
// AvgRating from UserRating ur where ur.Reviewed = er.Reviewed)
// temp from UserRating er";
String sql =
"SELECT er.USERID,(SELECT avg(U2RATEDU1) AS AvgRating from USERWISH uw where uw.USERID=er.USERID and uw.Wish_Status=er.Wish_Status and uw.U2RATEDU1<>:b) avg1, (SELECT avg(U1RATEDU2) AS AvgRatingTwo from USERWISH aw WHERE aw.USER_TWO_ID=er.USERID and aw.Wish_Status=er.Wish_Status and aw.U1RATEDU2<>:a) avg2 from USERWISH er WHERE er.WISH_STATUS= :status";
SQLQuery query = session.createSQLQuery(sql);
query.setParameter("a", 0);
query.setParameter("b", 0);
query.setParameter("status", CommonLib.STATUS_FULLFILLED);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List results = query.list();
for (Object object : results) {
Map row = (Map) object;
userId = (int) row.get("USERID");
if (row.get("avg1") != null) avg1 = (double) row.get("avg1");
else avg1 = 0;
if (row.get("avg2") != null) avg2 = (double) row.get("avg2");
else avg2 = 0;
if (avg1 != 0 && avg2 != 0) rating = (avg1 + avg2) / 2;
else rating = Math.max(avg1, avg2);
if (rating != 0) {
String sql2 = "UPDATE USER SET RATING= :rating WHERE USERID= :userId";
SQLQuery query2 = session.createSQLQuery(sql2);
query2.addEntity(User.class);
query2.setParameter("rating", rating);
query2.setParameter("userId", userId);
query2.executeUpdate();
}
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
e.printStackTrace();
} finally {
if (session != null && session.isOpen()) session.close();
}
}
/**
* 设置查询结果类型
*
* @param query
* @param resultClass
*/
private void setResultTransformer(SQLQuery query, Class<?> resultClass) {
if (resultClass != null) {
if (resultClass == Map.class) {
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
} else if (resultClass == List.class) {
query.setResultTransformer(Transformers.TO_LIST);
} else {
query.addEntity(resultClass);
}
}
}
public static List<Match> getAllMatchesForSummoner(long id) {
Session session = HibernateUtils.getSessionFactory().openSession();
SQLQuery query =
session.createSQLQuery(
" SELECT * FROM `match` m, participantstats ps, participantidentity pi, player p, matchparticipant mp WHERE m.matchId = mp.matchId "
+ " AND m.matchId = pi.matchId AND pi.dbId = p.dbId AND mp.dbId = ps.dbId AND p.summonerId = :id AND mp.participantId = pi.participantId");
query.setParameter("id", id);
query.addEntity(Match.class);
session.close();
return query.list();
}