Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. setParameter

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setParameter.

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

From source file:com.krawler.customFieldMaster.fieldManager.java

License:Open Source License

public boolean storeCustomFields(JSONArray jarray, String modulename, boolean isNew, String modulerecid) {
    Session session = null;/*from   w w  w. j ava2  s . c o  m*/
    Transaction tx = null;
    try {
        session = HibernateUtil.getCurrentSession();
        tx = session.beginTransaction();
        for (int i = 0; i < jarray.length(); i++) {
            try {
                JSONObject jobj = jarray.getJSONObject(i);
                Iterator ittr = jobj.keys();
                String fieldid = "";
                String fieldName = "";
                String fieldValue = "";
                while (ittr.hasNext()) {
                    Object obj = ittr.next();
                    if (obj.toString().equals("filedid")) {
                        fieldid = jobj.getString("filedid");
                    } else {
                        fieldName = obj.toString();

                    }
                }

                String query1 = "show tables ";
                SQLQuery sql1 = session.createSQLQuery(query1);
                ArrayList l = new ArrayList();
                l = (ArrayList) sql1.list();
                if (!l.contains(modulename + "cstm")) {
                    query1 = "CREATE TABLE  `" + modulename
                            + "cstm`(`modulerecid` varchar(36) NOT NULL,`fieldparamid` int(11) NOT NULL, `fieldvalue` varchar(255) NOT NULL,   `fieldname` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
                    sql1 = session.createSQLQuery(query1);
                    sql1.executeUpdate();
                }
                fieldValue = jobj.getString(fieldName);
                String query = "SELECT modulerecid FROM " + modulename
                        + "cstm where modulerecid = ? and fieldparamid = ?";
                SQLQuery sql = session.createSQLQuery(query);
                sql.setParameter(0, modulerecid);
                sql.setParameter(1, fieldid);
                ArrayList list = (ArrayList) sql.list();
                boolean recExists = false;
                if (list.size() > 0) {
                    recExists = true;
                }

                if ((isNew || !recExists) && (!fieldValue.equals("undefined"))) {
                    query = "insert into " + modulename + "cstm values(?,?,?,?)";
                } else {
                    query = "update " + modulename
                            + "cstm set fieldvalue=? where modulerecid=? and fieldparamid = ?";
                }
                sql = session.createSQLQuery(query);
                if ((isNew || !recExists) && (!fieldValue.equals("undefined"))) {
                    sql.setParameter(0, modulerecid);
                    sql.setParameter(1, fieldid);
                    sql.setParameter(2, fieldValue);
                    sql.setParameter(3, fieldName);
                } else {
                    sql.setParameter(0, fieldValue);
                    sql.setParameter(1, modulerecid);
                    sql.setParameter(2, fieldid);

                }
                sql.executeUpdate();

            } catch (Exception ex) {
                Logger.getLogger(fieldManager.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
    } catch (Exception e) {
        if (tx != null)
            tx.rollback();
        Logger.getLogger(fieldManager.class.getName()).log(Level.SEVERE, null, e);
    } finally {
        HibernateUtil.closeSession(session);
    }
    return true;
}

From source file:com.krawler.customFieldMaster.fieldManager.java

License:Open Source License

public boolean storeCustomFields(JSONArray jarray, String modulename, boolean isNew, String modulerecid,
        ArrayList ll) {//from   w w w  .ja va  2s . co  m
    Session session = null;
    Transaction tx = null;
    try {
        session = HibernateUtil.getCurrentSession();
        tx = session.beginTransaction();
        for (int i = 0; i < jarray.length(); i++) {
            try {
                JSONObject jobj = jarray.getJSONObject(i);
                Iterator ittr = jobj.keys();
                String fieldid = "";
                String fieldName = "";
                String fieldValue = "";
                while (ittr.hasNext()) {
                    Object obj = ittr.next();
                    if (obj.toString().equals("filedid")) {
                        fieldid = jobj.getString("filedid");
                    } else {
                        fieldName = obj.toString();

                    }
                }

                String query1 = "show tables ";
                SQLQuery sql1 = session.createSQLQuery(query1);
                ArrayList l = new ArrayList();
                l = (ArrayList) sql1.list();
                if (!l.contains(modulename + "cstm")) {
                    query1 = "CREATE TABLE  `" + modulename
                            + "cstm`(`modulerecid` varchar(36) NOT NULL,`fieldparamid` int(11) NOT NULL, `fieldvalue` varchar(255) NOT NULL,   `fieldname` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
                    sql1 = session.createSQLQuery(query1);
                    sql1.executeUpdate();
                }
                fieldValue = jobj.getString(fieldName);
                String query = "SELECT modulerecid FROM " + modulename
                        + "cstm where modulerecid = ? and fieldparamid = ?";
                SQLQuery sql = session.createSQLQuery(query);
                sql.setParameter(0, modulerecid);
                sql.setParameter(1, fieldid);
                ArrayList list = (ArrayList) sql.list();
                boolean recExists = false;
                if (list.size() > 0) {
                    recExists = true;
                }
                for (int j = 0; j < ll.size(); j++) {
                    Object[] temp = (Object[]) ll.get(j);
                    if ((isNew || !recExists) && (temp[0].toString().equals(fieldName))) {
                        query = "insert into " + modulename + "cstm values(?,?,?,?)";
                        sql = session.createSQLQuery(query);
                        sql.setParameter(0, modulerecid);
                        sql.setParameter(1, fieldid);
                        sql.setParameter(2, fieldValue);
                        sql.setParameter(3, fieldName);
                        sql.executeUpdate();
                    }
                }

            } catch (Exception ex) {
                Logger.getLogger(fieldManager.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
    } catch (Exception e) {
        if (tx != null)
            tx.rollback();
        Logger.getLogger(fieldManager.class.getName()).log(Level.SEVERE, null, e);
    } finally {
        HibernateUtil.closeSession(session);
    }
    return true;
}

From source file:com.krawler.customFieldMaster.fieldManager.java

License:Open Source License

public String createNewField(String fieldlabel, int isessential, int maxlength, int validationtype,
        int fieldtype, int moduleid, String customregex, String companyid, String combodata, String formulae,
        String iseditable, HttpServletRequest request) {
    int result = 0;
    String msg = "";
    Session session = null;//from  ww w .  ja va2s .  co  m
    try {
        String query = "insert into fieldparams(moduleid,fieldname,fieldlabel,isessential,maxlength,validationtype,fieldtype,customregex,companyid,iseditable) values(?,?,?,?,?,?,?,?,?,?)";
        session = HibernateUtil.getCurrentSession();
        Transaction tx = session.beginTransaction();
        SQLQuery sql = session.createSQLQuery(query);
        sql.setParameter(0, moduleid);
        sql.setParameter(1, fieldlabel.replaceAll(" ", "_"));
        sql.setParameter(2, fieldlabel);
        sql.setParameter(3, isessential);
        sql.setParameter(4, maxlength);
        sql.setParameter(5, validationtype);
        sql.setParameter(6, fieldtype);
        sql.setParameter(7, StringUtil.checkForNull(customregex));
        sql.setParameter(8, companyid);
        sql.setParameter(9, iseditable);

        result = sql.executeUpdate();
        tx.commit();
        if (!StringUtil.isNullOrEmpty(combodata)) {

            String[] combovalues = combodata.split(";");
            query = "SELECT max(id) as fieldid from fieldparams";
            SQLQuery sql1 = session.createSQLQuery(query);
            int fieldid = 0;
            ArrayList list = (ArrayList) sql1.list();
            if (list.size() > 0) {

                fieldid = Integer.parseInt(list.get(0).toString());
            }
            for (int cnt = 0; cnt < combovalues.length; cnt++) {
                if (!StringUtil.isNullOrEmpty(combovalues[cnt])) {
                    tx = session.beginTransaction();
                    query = "insert into fieldComboData(name,fieldid) values(?,?)";
                    SQLQuery sql2 = session.createSQLQuery(query);

                    sql2.setParameter(0, combovalues[cnt]);
                    sql2.setParameter(1, fieldid);
                    sql2.executeUpdate();
                    tx.commit();
                }
            }

        }

        if (!StringUtil.isNullOrEmpty(formulae)) {
            tx = session.beginTransaction();
            setCustomColumnFormulae(session, request);
            tx.commit();
        }
    } catch (ConstraintViolationException e) {
        msg = messageSource.getMessage("hrms.commonlib.new.custom.column.created", null,
                RequestContextUtils.getLocale(request));
    } catch (Exception e) {
        msg = messageSource.getMessage("hrms.commonlib.new.custom.column.cannot.created", null,
                RequestContextUtils.getLocale(request));
    } finally {
        HibernateUtil.closeSession(session);
    }

    if (result == 1) {
        msg = messageSource.getMessage("hrms.commonlib.new.custom.column.created", null,
                RequestContextUtils.getLocale(request));
        return msg;
    } else {
        if (StringUtil.isNullOrEmpty(msg)) {
            msg = messageSource.getMessage("hrms.commonlib.new.custom.column.cannot.created", null,
                    RequestContextUtils.getLocale(request));
        }
        return msg;
    }

}

From source file:com.krawler.customFieldMaster.fieldManager.java

License:Open Source License

public ArrayList getColumnModel(String moduleid, String companyid) {
    ArrayList colmodel = new ArrayList();
    Session session = HibernateUtil.getCurrentSession();
    try {/*from   w ww.j a v  a 2 s .com*/
        String query = "SELECT fieldname,fieldlabel,isessential,maxlength,validationtype,id,fieldtype,iseditable FROM fieldparams where moduleid = ? and companyid = ? ";
        SQLQuery sql = session.createSQLQuery(query);
        sql.setParameter(0, moduleid);
        sql.setParameter(1, companyid);
        colmodel = (ArrayList) sql.list();
    } catch (Exception e) {

    } finally {
        HibernateUtil.closeSession(session);
    }

    return colmodel;
}

From source file:com.krawler.customFieldMaster.fieldManager.java

License:Open Source License

public ArrayList getComboData(String fieldid, String companyid) {
    ArrayList colmodel = new ArrayList();
    Session session = HibernateUtil.getCurrentSession();
    try {//from ww w  . j  a  va  2  s.  c  o m
        String query = "SELECT `id`,`name` from fieldComboData  where fieldid = ?";
        SQLQuery sql = session.createSQLQuery(query);
        sql.setParameter(0, fieldid);
        colmodel = (ArrayList) sql.list();
    } catch (Exception e) {

    } finally {
        HibernateUtil.closeSession(session);
    }

    return colmodel;

}

From source file:com.krawler.esp.hibernate.impl.HibernateUtil.java

License:Open Source License

public static int executeSQLUpdate(Session session, String hql, Object[] params) throws ServiceException {
    int numRows = 0;
    try {/*from   w w  w .  ja  v  a2 s.c om*/
        SQLQuery query = session.createSQLQuery(hql);

        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        numRows = query.executeUpdate();
    } catch (HibernateException e) {
        String message = "SQL: '" + hql + "'";
        throw ServiceException.FAILURE(message, e);
    } catch (Exception e) {
        String message = "SQL: '" + hql + "'";
        throw ServiceException.FAILURE(message, e);
    } finally {
        //         DbPool.closeStatement(stmt);
    }

    return numRows;
}

From source file:com.krawler.spring.importFunctionality.ImportImpl.java

License:Open Source License

@Override
public List getCustomComboID(String fetchColumn, ArrayList filterNames, ArrayList filterValues)
        throws ServiceException, DataInvalidateException {
    try {//  ww w  .  ja v  a2 s.c  o m
        //            "SELECT id FROM fieldComboData where name = ? and fieldid = ?"
        String query = "SELECT " + fetchColumn + " FROM fieldComboData ";
        String filter = StringUtil.filterQuery(filterNames, "where");
        query += filter;
        SQLQuery sql = hibernateTemplate.getSessionFactory().getCurrentSession().createSQLQuery(query);
        if (filterValues != null) {
            for (int i = 0; i < filterValues.size(); i++) {
                sql.setParameter(i, filterValues.get(i));
            }
        }
        return sql.list();
    } catch (Exception e) {
        throw ServiceException.FAILURE("ImportImpl. : getCustomComboID" + e.getMessage(), e);
    }
}

From source file:com.liyablieva.jaxws.dao.EmployeeDAOImpl.java

@Override
@Transactional(value = "transactionManager")
public int putEmployee(String department, String name, String gender, String license) {

    System.out.println("In put employee DAO");
    System.out.println("Department = " + department + ", name = " + name + ", gender = " + gender
            + ", license = " + license);

    int departmentId = getDepartmentId(department);

    String sql = "INSERT INTO EMPLOYEE(EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_GENDER, EMPLOYEE_LICENSE, DEPARTMENT_ID)"
            + " VALUES ((SELECT max(employee_id) FROM employee)+1,:name,:gender,:license,:department_id);";
    // SHOULD NOT USE (SELECT max(employee_id) FROM employee)+1 but GenerationType.SEQUENCE does not work!
    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.setParameter("name", name);
    query.setParameter("gender", gender);
    query.setParameter("license", license);
    query.setParameter("department_id", departmentId);
    int result = query.executeUpdate();

    System.out.println(license);/*from   ww w  .  ja  v a2 s.c  o m*/
    System.out.println("result = " + result);

    return result;
}

From source file:com.liyablieva.jaxws.dao.EmployeeDAOImpl.java

@SuppressWarnings("unchecked")
@Override/* w  ww.j av a 2s  .co m*/
@Transactional(value = "transactionManager")
public List<Employee> getEmployees(String department) {

    System.out.println("In get employees DAO");
    int departmentId = getDepartmentId(department);
    System.out.println("Department = " + department + ", departmentId = " + departmentId);

    List<Employee> employees = Collections.emptyList();

    String sql = "SELECT * FROM employee WHERE department_id = :department_id";
    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.addEntity(Employee.class);
    query.setParameter("department_id", departmentId);
    employees = query.list();

    return employees;
}

From source file:com.liyablieva.jaxws.dao.EmployeeDAOImpl.java

@Override
public int getDepartmentId(String department) {

    System.out.println("In getDepartment method.");
    String[] departments = department.split(";");
    int parentId = 0;
    Object id;/*from   w  ww .  ja v a2  s  .co  m*/

    for (int i = 0; i < departments.length; i++) {
        String sql = "SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE department_name = :department_name AND parent_id = :parent_id";
        SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql);
        query.setParameter("department_name", departments[i]);
        query.setParameter("parent_id", parentId);
        id = query.uniqueResult();
        //uniqueResult returns null if object was not found
        if (id == null) {
            throw new IllegalArgumentException("Department was not found!");
        }
        //cast if id != null
        parentId = (int) id;
    }

    System.out.println("Department id = " + parentId);

    return parentId;
}