Example usage for com.liferay.portal.kernel.dao.orm SQLQuery setString

Introduction

In this page you can find the example usage for com.liferay.portal.kernel.dao.orm SQLQuery setString.

Prototype

public Query setString(int pos, String value);

Source Link

Usage

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;//  w  w  w  .jav a2 s.  co  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;/*from  ww  w  .  ja  va 2s. c  o m*/

    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 ww  . j ava  2  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  a va  2  s.c o  m
    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  w w  w.j a  v  a2 s  .  co 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;/*from  w  ww . j av 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;//from w  w  w . j av a2 s .c o m

    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 a  v a2s .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  ww .  ja v  a2  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 (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;/*from ww  w.j  a v a2 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);
    }
}

---

• «
• 1
• 2
• 3
• »