List of usage examples for com.liferay.portal.kernel.dao.orm SQLQuery setLong
public Query setLong(int pos, long value);
From source file:com.crm.alarm.service.persistence.MonitorAccountFinderImpl.java
License:Open Source License
public void updateAccount(long userId, long accountId, String password, String actionType, String appMethod) throws SystemException, PortalException { Session session = null;/* ww w .j a va 2s . c o m*/ try { if (Validator.isNull(appMethod)) { return; } String customSQL = "{call " + appMethod + "(?, ?, ?, ?)}"; session = openSession(); SQLQuery query = session.createSQLQuery(customSQL); query.setLong(0, userId); query.setLong(1, accountId); query.setString(2, password); query.setString(3, actionType); query.executeUpdate(); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.edirectory.service.persistence.EDEmployeeFinderImpl.java
License:Open Source License
public List findEmployeeByParam(long groupId, String language, String name, long departmentId, long functionId, int begin, int end) throws SystemException { Session session = null;// w w w .j a va 2 s . com try { session = openSession(); String sql = CustomSQLUtil.get(FIND_BY_PARA); StringBuffer query = new StringBuffer(); query.append(sql); query.append(" WHERE "); query.append("(EDEmployee.groupId = ?)"); query.append("AND (EDEmployee.languageId = ?)"); if (Validator.isNotNull(name)) { query.append("AND"); query.append("(LOWER(EDEmployee.name) LIKE LOWER(?)) "); } if (Validator.isNotNull(String.valueOf(departmentId)) && departmentId != 0) { query.append("AND "); query.append("(EDEmployee.departmentId = ?) "); } if (Validator.isNotNull(String.valueOf(functionId)) && functionId != 0) { query.append("AND"); query.append("(EDFunctionEmployee.functionId = ?)"); } query.append("ORDER BY "); query.append("EDEmployee.name"); SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("EDEmployee", EDEmployeeImpl.class); // Truyen tham so vao cau query: tinh tu 0 int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (Validator.isNotNull(name) && !name.isEmpty()) { q.setString(queryPos++, "%" + name + "%"); } if (Validator.isNotNull(String.valueOf(departmentId)) && departmentId != 0) { q.setLong(queryPos++, departmentId); } if (Validator.isNotNull(String.valueOf(functionId)) && functionId != 0) { q.setLong(queryPos++, functionId); } return QueryUtil.list(q, getDialect(), begin, end); // return q.list(); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.edirectory.service.persistence.EDEmployeeFinderImpl.java
License:Open Source License
/** * Method search emdployee by name, departmentId, functionId *//*from w w w . ja v a2 s. c om*/ public List findEmployeeByParam(long groupId, String language, String name, long departmentId, long functionId) throws SystemException { Session session = null; try { session = openSession(); String sql = CustomSQLUtil.get(FIND_BY_PARA); StringBuffer query = new StringBuffer(); query.append(sql); query.append(" WHERE "); query.append("(EDEmployee.groupId = ?)"); query.append("AND (EDEmployee.languageId = ?)"); if (Validator.isNotNull(name)) { query.append("AND"); query.append("(LOWER(EDEmployee.name) LIKE LOWER(?)) "); } if (Validator.isNotNull(String.valueOf(departmentId)) && departmentId != 0) { query.append("AND "); query.append("(EDEmployee.departmentId = ?) "); } if (Validator.isNotNull(String.valueOf(functionId)) && functionId != 0) { query.append("AND"); query.append("(EDFunctionEmployee.functionId = ?)"); } query.append("ORDER BY "); query.append("EDEmployee.name"); SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("EDEmployee", EDEmployeeImpl.class); // Truyen tham so vao cau query: tinh tu 0 int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (Validator.isNotNull(name) && !name.isEmpty()) { q.setString(queryPos++, "%" + name + "%"); } if (Validator.isNotNull(String.valueOf(departmentId)) && departmentId != 0) { q.setLong(queryPos++, departmentId); } if (Validator.isNotNull(String.valueOf(functionId)) && functionId != 0) { q.setLong(queryPos++, functionId); } return q.list(); // return q.list(); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.edirectory.service.persistence.EDEmployeeFinderImpl.java
License:Open Source License
public int countEDEmployeByParam(long groupId, String language, String name, long departmentId, long functionId) throws SystemException { Session session = null;// w w w . j av a 2 s . com try { session = openSession(); String sql = CustomSQLUtil.get(COUNT_BY_PARA); StringBuffer query = new StringBuffer(); query.append(sql); query.append(" WHERE "); query.append("(EDEmployee.groupId = ?)"); query.append("AND (EDEmployee.languageId = ?)"); if (Validator.isNotNull(name)) { query.append("AND"); query.append("(LOWER(EDEmployee.name) LIKE LOWER(?)) "); } if (Validator.isNotNull(String.valueOf(departmentId)) && departmentId != 0) { query.append("AND "); query.append("(EDEmployee.departmentId = ?) "); } if (Validator.isNotNull(String.valueOf(functionId)) && functionId != 0) { query.append("AND"); query.append("(EDFunctionEmployee.functionId = ?)"); } SQLQuery q = session.createSQLQuery(query.toString()); q.addScalar("total", Type.LONG); // Truyen tham so vao cau query: tinh tu 0 int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (Validator.isNotNull(name) && !name.isEmpty()) { q.setString(queryPos++, "%" + name + "%"); } if (Validator.isNotNull(String.valueOf(departmentId)) && departmentId != 0) { q.setLong(queryPos++, departmentId); } if (Validator.isNotNull(String.valueOf(functionId)) && functionId != 0) { q.setLong(queryPos++, functionId); } Iterator ite = q.list().iterator(); Long count = null; while (ite.hasNext()) { count = (Long) ite.next(); } if (count == null) count = new Long(0); return count.intValue(); } catch (Exception ex) { throw new SystemException(ex); } finally { closeSession(session); } }
From source file:com.vportal.portlet.edirectory.service.persistence.EDEmployeeFinderImpl.java
License:Open Source License
public List findEmployeeByParam(long groupId, String language, long departmentId, String name) throws SystemException { Session session = null;/*from www . j a va2s .c o m*/ // List<EDEmployee> employeeList = new ArrayList<EDEmployee>(); try { session = openSession(); String sql = "SELECT * FROM EDEmployee"; StringBuffer query = new StringBuffer(); query.append(sql); query.append(" WHERE "); if (Validator.isNull(groupId)) { return null; } else { query.append("(EDEmployee.groupId = ?) "); } if (Validator.isNull(language)) { return null; } else { query.append("AND "); query.append("(EDEmployee.languageId = ?) "); } if (Validator.isNotNull(departmentId) && !Validator.equals(departmentId, 0)) { query.append("AND "); query.append("(EDEmployee.departmentId = ?) "); } if (Validator.isNotNull(name)) { query.append("AND "); query.append("LOWER(EDEmployee.name) LIKE LOWER(?) "); } SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("EDEmployee", EDEmployeeImpl.class); int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (Validator.isNotNull(departmentId) && !Validator.equals(departmentId, 0)) { q.setLong(queryPos++, departmentId); } if (Validator.isNotNull(name)) { q.setString(queryPos++, "%" + name + "%"); } // System.out.println("DepID: " + departmentId); // System.out.println("Name: " + name); // System.out.println("SQL query: " + query); // employeeList = q.list(); return q.list(); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.edirectory.service.persistence.EDEmployeeFinderImpl.java
License:Open Source License
@SuppressWarnings("unchecked") public List<EDEmployee> findEmployeeByParam(long groupId, String language, long departmentId, String name, int begin, int end) throws SystemException { Session session = null;/* w w w . ja v a 2 s . c o m*/ try { session = openSession(); String sql = "SELECT * FROM EDEmployee"; StringBuffer query = new StringBuffer(); query.append(sql); query.append(" WHERE "); if (Validator.isNull(String.valueOf(groupId))) { return null; } else { query.append("(EDEmployee.groupId = ?) "); } if (Validator.isNull(language)) { return null; } else { query.append(" AND "); query.append("(EDEmployee.languageId = ?) "); } if (Validator.isNotNull(String.valueOf(departmentId)) && !Validator.equals(departmentId, "0")) { query.append(" AND"); query.append(" (EDEmployee.departmentId = ?) "); } if (Validator.isNotNull(name)) { query.append(" AND"); query.append(" LOWER(EDEmployee.name) LIKE LOWER(?) "); } SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("EDEmployee", EDEmployeeImpl.class); int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (Validator.isNotNull(String.valueOf(departmentId)) && !Validator.equals(departmentId, "0")) { q.setLong(queryPos++, departmentId); } if (Validator.isNotNull(name)) { q.setString(queryPos++, "%" + name + "%"); } return (List<EDEmployee>) QueryUtil.list(q, getDialect(), begin, end); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.edirectory.service.persistence.EDEmployeeFinderImpl.java
License:Open Source License
public List<EDEmployee> findEmployeeByParam(long groupId, String languageId, long departmentId, String name, String phone, String email) throws SystemException { Session session = null;/* w w w .j a v a2s. com*/ try { session = openSession(); String sql = CustomSQLUtil.get(FIND_BY_DEPARTMENT_NAME_PHONE_EMAIL); StringBuffer query = new StringBuffer(); query.append(sql); query.append("WHERE "); query.append("(EDEmployee.groupId = ? )"); query.append("AND (EDEmployee.languageId = ?)"); //System.out.println("GroupId: " + groupId); //System.out.println("LanguageId: " + languageId); //System.out.println("DepId: " + departmentId); long parent = 0; if (Validator.isNotNull(departmentId) && departmentId != 0) { EDDepartment depSelected = EDDepartmentLocalServiceUtil.getEDDepartment(departmentId); parent = depSelected.getParent(); } // EDDepartment depSelected = EDDepartmentLocalServiceUtil.getEDDepartment(departmentId); // long parent = depSelected.getParent(); //System.out.println("Parent: " + parent); //Neu khong co don vi con thi tim tat ca cac nhan vien trong don vi da chon if (parent != 0 && departmentId != 0) { // query.append("AND "); // query.append("EDEmployee.departmentId = EDDepartment.id_ "); query.append("AND "); query.append("EDEmployee.departmentId = ? "); } else if (departmentId != 0) { query.append("AND "); query.append("EDEmployee.departmentId in ( Select id_ From EDDepartment WHERE parent = ? ) "); } if (Validator.isNotNull(name)) { query.append("AND"); query.append("(LOWER(EDEmployee.name) LIKE LOWER(?)) "); } if (Validator.isNotNull(phone)) { query.append("AND"); query.append("((EDEmployee.homephone LIKE ?) "); query.append("OR"); query.append("(EDEmployee.workphone LIKE ?) "); query.append("OR"); query.append("(EDEmployee.mobilephone LIKE ?)) "); } if (Validator.isNotNull(email)) { query.append("AND"); query.append("(LOWER(EDEmployee.email) LIKE LOWER(?)) "); } query.append("ORDER BY "); query.append("EDEmployee.name"); //System.out.println("Query: " + query); SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("EDEmployee", EDEmployeeImpl.class); // Truyen tham so vao cau query: tinh tu 0 int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, languageId); if (Validator.isNotNull(departmentId) && departmentId != 0) { q.setLong(queryPos++, departmentId); } if (Validator.isNotNull(name) && !name.isEmpty()) { q.setString(queryPos++, "%" + name + "%"); } if (Validator.isNotNull(phone) && !phone.isEmpty()) { q.setString(queryPos++, "%" + phone + "%"); q.setString(queryPos++, "%" + phone + "%"); q.setString(queryPos++, "%" + phone + "%"); } if (Validator.isNotNull(email) && !email.isEmpty()) { q.setString(queryPos++, "%" + email + "%"); } return q.list(); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.vdoc.service.persistence.vdocDocumentFinderImpl.java
License:Open Source License
public List findDocumentByParams(long groupId, String language, String fieldId, String orgId, String title, int status) throws SystemException { Session session = null;/*from w w w .j ava2 s. co m*/ try { session = openSession(); String sql = "SELECT vdocDocument.* FROM vdocDocument"; StringBuffer query = new StringBuffer(); query.append(sql); if (!orgId.equals("0")) { query.append(" INNER JOIN vdocDORel "); query.append(" ON (vdocDocument.docId = vdocDORel.docId)"); } query.append(" WHERE "); if (Validator.isNull(String.valueOf(groupId))) { return null; } else { query.append("(vdocDocument.groupId = ?) "); } if (Validator.isNull(language)) { return null; } else { query.append("AND "); query.append("(vdocDocument.language = ?) "); } if (Validator.isNotNull(fieldId) && !Validator.equals(fieldId, "0")) { query.append(" AND "); query.append("(vdocDocument.fieldId = ?) "); } if (Validator.isNotNull(orgId) && !Validator.equals(orgId, "0")) { query.append(" AND "); query.append("(vdocDORel.orgId = ?) "); } if (Validator.isNotNull(title)) { query.append(" AND "); query.append("(LOWER(vdocDocument.title) LIKE LOWER(?)) "); } if (status == 2 || status == 1 || status == 0) { query.append("AND "); query.append("(vdocDocument.statusDoc = ?) "); } if (status == 2) { query.append("ORDER BY "); query.append("vdocDocument.publishedDate DESC"); } else if (status == 1) { query.append("ORDER BY "); query.append("vdocDocument.approvedDate DESC"); } else if (status == 0) { query.append("ORDER BY "); query.append("vdocDocument.createdDate DESC"); } SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("vdocDocument", vdocDocumentImpl.class); int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (Validator.isNotNull(fieldId) && !Validator.equals(fieldId, "0")) q.setString(queryPos++, fieldId); if (Validator.isNotNull(orgId) && !Validator.equals(orgId, "0")) { q.setString(queryPos++, orgId); } if (Validator.isNotNull(title)) q.setString(queryPos++, "%" + title + "%"); if (status == 2 || status == 1 || status == 0) q.setInteger(queryPos++, status); return q.list(); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.vdoc.service.persistence.vdocDocumentFinderImpl.java
License:Open Source License
@SuppressWarnings("unchecked") public List<vdocDocument> findDocumentByParams(long groupId, String language, String fieldId, String orgId, String title, int status, int begin, int end) throws SystemException { Session session = null;//from w w w.j ava 2s . co m try { session = openSession(); String sql = "SELECT vdocDocument.* FROM vdocDocument"; StringBuffer query = new StringBuffer(); query.append(sql); if (!orgId.equals("0")) { query.append(" INNER JOIN vdocDORel "); query.append(" ON (vdocDocument.docId = vdocDORel.docId)"); } query.append(" WHERE "); if (Validator.isNull(String.valueOf(groupId))) { return null; } else { query.append("(vdocDocument.groupId = ?) "); } if (Validator.isNull(language)) { return null; } else { query.append("AND "); query.append("(vdocDocument.language = ?) "); } if (Validator.isNotNull(fieldId) && !Validator.equals(fieldId, "0")) { query.append(" AND "); query.append("(vdocDocument.fieldId = ?) "); } if (Validator.isNotNull(orgId) && !Validator.equals(orgId, "0")) { query.append(" AND "); query.append("(vdocDORel.orgId = ?) "); } if (Validator.isNotNull(title)) { query.append(" AND "); query.append("(LOWER(vdocDocument.title) LIKE LOWER(?)) "); } if (status == 2 || status == 1 || status == 0) { query.append("AND "); query.append("(vdocDocument.statusDoc = ?) "); } if (status == 2) { query.append("ORDER BY "); query.append("vdocDocument.publishedDate DESC"); } else if (status == 1) { query.append("ORDER BY "); query.append("vdocDocument.approvedDate DESC"); } else if (status == 0) { query.append("ORDER BY "); query.append("vdocDocument.createdDate DESC"); } SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("vdocDocument", vdocDocumentImpl.class); int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (Validator.isNotNull(fieldId) && !Validator.equals(fieldId, "0")) q.setString(queryPos++, fieldId); if (Validator.isNotNull(orgId) && !Validator.equals(orgId, "0")) { q.setString(queryPos++, orgId); } if (Validator.isNotNull(title)) q.setString(queryPos++, "%" + title + "%"); if (status == 2 || status == 1 || status == 0) q.setInteger(queryPos++, status); return (List<vdocDocument>) QueryUtil.list(q, getDialect(), begin, end); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }
From source file:com.vportal.portlet.vdoc.service.persistence.vdocDocumentFinderImpl.java
License:Open Source License
public List findDocsByPublishedDate(long groupId, String language, Date pubDateFrom, Date pubDateTo, int status, int quantity) throws SystemException { Session session = null;/* www. jav a 2 s . co m*/ try { session = openSession(); String sql = "SELECT * FROM vdocDocument"; StringBuffer query = new StringBuffer(); query.append(sql); query.append(" WHERE "); if (Validator.isNull(String.valueOf(groupId))) { return null; } else { query.append(" vdocDocument.groupId = ? "); } if (Validator.isNull(language)) { return null; } else { query.append(" AND "); query.append(" vdocDocument.language = ? "); } if (pubDateTo != null) { query.append(" AND "); query.append(" vdocDocument.publishedDate >= ? "); } if (pubDateFrom != null) { query.append(" AND "); query.append("vdocDocument.publishedDate <= ? "); } if (status == 2 || status == 1 || status == 0) { query.append(" AND "); query.append("vdocDocument.statusDoc = ? "); } query.append("ORDER BY "); query.append("vdocDocument.publishedDate DESC"); SQLQuery q = session.createSQLQuery(query.toString()); q.addEntity("vdocDocument", vdocDocumentImpl.class); int queryPos = 0; q.setLong(queryPos++, groupId); q.setString(queryPos++, language); if (pubDateFrom != null) q.setTimestamp(queryPos++, new Timestamp(pubDateFrom.getTime())); if (pubDateTo != null) q.setTimestamp(queryPos++, new Timestamp(pubDateTo.getTime())); if (status == 2 || status == 1 || status == 0) q.setInteger(queryPos++, status); return QueryUtil.list(q, getDialect(), 0, quantity); } catch (Exception e) { throw new SystemException(e); } finally { closeSession(session); } }