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.wso2telco.services.dep.sandbox.dao.hibernate.HibernateCustomerInfoDAO.java

License:Open Source License

@Override

public CustomerInfoDTO getProfileData(String msisdn, User user) throws Exception {
    Session session = getSession();/*from www .  j  a  v  a2 s .  c  om*/
    CustomerInfoDTO customerInfoDTO = null;

    try {
        StringBuilder hqlBuilder = new StringBuilder();

        hqlBuilder.append("SELECT msisdn,");
        hqlBuilder.append(" MAX(IF(column_name = 'title', value, NULL)) title, ");
        hqlBuilder.append(" MAX(IF(column_name = 'firstName',value,NULL)) firstName, ");
        hqlBuilder.append(" MAX(IF(column_name = 'lastName',value,NULL)) lastName,");
        hqlBuilder.append(" MAX(IF(column_name = 'dob', value, NULL)) dob, ");
        hqlBuilder.append(" MAX(IF(column_name = 'identificationType', value, NULL)) identificationType, ");
        hqlBuilder.append(" MAX(IF(column_name = 'identificationNumber',value,NULL)) identificationNumber, ");
        hqlBuilder.append(" MAX(IF(column_name = 'address', value, NULL)) address, ");
        hqlBuilder.append(" MAX(IF(column_name = 'additionalInfo',value,NULL)) additionalInfo, ");
        hqlBuilder.append(" MAX(IF(column_name = 'ownerType',value,NULL)) ownerType, ");
        hqlBuilder.append(" MAX(IF(column_name = 'accountType',value,NULL)) accountType, ");
        hqlBuilder.append(" MAX(IF(column_name = 'status', value, NULL)) status ");
        hqlBuilder.append(" FROM ");
        hqlBuilder.append(" (SELECT numbers.number AS msisdn, ");
        hqlBuilder.append(" attr.name AS column_name, ");
        hqlBuilder.append(" attval.value AS value ");
        hqlBuilder.append(" FROM ");
        hqlBuilder.append(" sbxapitypes api, sbxapiservicecalls serviceCalls, ");
        hqlBuilder.append(" sbtattributedistribution attdis, sbxattribute attr, ");
        hqlBuilder.append(" sbxattributevalue attval, user usr, numbers numbers ");
        hqlBuilder.append(" WHERE ");
        hqlBuilder.append(" api.apiname = '" + RequestType.CUSTOMERINFO.toString() + "'");
        hqlBuilder
                .append(" AND serviceCalls.service = '" + CustomerInfoRequestType.GETPROFILE.toString() + "'");
        hqlBuilder.append(" AND api.id = serviceCalls.apitypesdid ");
        hqlBuilder.append(" AND serviceCalls.sbxapiservicecallsdid= attdis.apiservicecallsdid ");
        hqlBuilder.append(" AND attdis.attributedid = attr.sbxattributedid ");
        hqlBuilder.append(" AND attval.attributedistributiondid = attdis.sbtattributedistributiondid ");
        hqlBuilder.append(" AND usr.user_name =:userName");
        hqlBuilder.append(" AND usr.id = attval.ownerdid ");
        hqlBuilder.append(" AND attval.tobject = '" + TableName.USER.toString() + "'");
        hqlBuilder.append(" AND numbers.user_id = usr.id ");
        hqlBuilder.append(" AND numbers.number =:msisdn ");
        hqlBuilder.append(") d GROUP BY msisdn");

        SQLQuery query = session.createSQLQuery(hqlBuilder.toString());
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

        query.setParameter("userName", user.getUserName());
        query.setParameter("msisdn", msisdn);

        List resultList = query.list();
        if (resultList.size() > 0) {
            Object result = resultList.get(0);
            customerInfoDTO = new CustomerInfoDTO();
            Map resultMap = (Map) result;
            customerInfoDTO.setMsisdn((String) resultMap.get("msisdn"));
            customerInfoDTO.setTitle((String) resultMap.get("title"));
            customerInfoDTO.setFirstName((String) resultMap.get("firstName"));
            customerInfoDTO.setLastName((String) resultMap.get("lastName"));
            //String dateOfBirth = (String) resultMap.get("dob");
            //if (CommonUtil.getNullOrTrimmedValue(dateOfBirth) != null) {
            //    Date date;
            //    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            //    date = dateFormat.parse(dateOfBirth);
            customerInfoDTO.setDob((String) resultMap.get("dob"));

            customerInfoDTO.setMsisdn((String) resultMap.get("msisdn"));
            customerInfoDTO.setAddress((String) resultMap.get("address"));
            customerInfoDTO.setAccountType((String) resultMap.get("accountType"));
            customerInfoDTO.setOwnerType((String) resultMap.get("ownerType"));
            customerInfoDTO.setAdditionalInfo((String) resultMap.get("additionalInfo"));
            customerInfoDTO.setStatus((String) resultMap.get("status"));
            customerInfoDTO.setIdentificationType((String) resultMap.get("identificationType"));
            customerInfoDTO.setIdentificationNumber((String) resultMap.get("identificationNumber"));
        }

    } catch (Exception ex) {
        LOG.error("###CUSTOMERINFO### Error in Get Profile Data", ex);
        throw ex;
    }

    return customerInfoDTO;
}

From source file:com.yize.broadcast.core.hibernate.SimpleHibernateDao.java

License:Apache License

/**
 * ?HQL?Query. find()???.//from  w  w w .  ja v a2  s  .  c om
 *
 * @param values
 *            ????,?.
 */
public SQLQuery createSQLQuery(final String queryString, final Object... values) {
    Assert.hasText(queryString, "queryString?");
    SQLQuery query = getSession().createSQLQuery(queryString);
    if (values != null) {
        for (int i = 0; i < values.length; i++) {
            query.setParameter(i, values[i]);
        }
    }
    return query;
}

From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java

@Override
public PageFinder<ApiKey> queryApiKey(ApiKeyMetadata apiKeyMetadata, Query query) throws Exception {
    StringBuilder sqlPrefixBuilder = new StringBuilder();
    sqlPrefixBuilder.append(" select ");
    sqlPrefixBuilder.append(/*from  w w  w . java2  s.  com*/
            " t1.id, t1.app_key as appKey, t1.app_secret as appSecret, t1.status, t1.update_user as updateUser, t1.update_time as updateTime ");
    StringBuilder sqlSuffixBuilder = new StringBuilder();
    sqlSuffixBuilder.append(" from ");
    sqlSuffixBuilder.append(" tbl_merchant_api_key t1 ");
    sqlSuffixBuilder.append(" left join ");
    sqlSuffixBuilder.append(" tbl_merchant_api_key_metadata t2 ");
    sqlSuffixBuilder.append(" on(t1.id = t2.key_id) ");
    sqlSuffixBuilder.append(" left join ");
    sqlSuffixBuilder.append(" ( ");
    sqlSuffixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement());
    sqlSuffixBuilder.append(" ) t3 ");
    sqlSuffixBuilder.append(" on (t2.metadata_val = t3.metadata_val) ");
    sqlSuffixBuilder.append(" where 1 = 1 ");

    // ?
    Map<String, Object> properties = new HashMap<String, Object>();
    if (apiKeyMetadata != null) {
        if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataTag())) {
            sqlSuffixBuilder.append(" and t3.metadata_tag like :metadata_tag");
            properties.put("metadata_tag", '%' + apiKeyMetadata.getMetadataTag() + '%');
        }
        if (apiKeyMetadata.getMetadataKey() != null) {
            sqlSuffixBuilder.append(" and t2.metadata_key = :metadata_key ");
            properties.put("metadata_key", apiKeyMetadata.getMetadataKey().name());
        }
        if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataVal())) {
            sqlSuffixBuilder.append(" and t2.metadata_val = :metadata_val ");
            properties.put("metadata_val", apiKeyMetadata.getMetadataVal());
        }
        if (apiKeyMetadata.getApiKey() != null) {
            if (apiKeyMetadata.getApiKey().getStatus() != null) {
                sqlSuffixBuilder.append(" and t1.status = :status ");
                properties.put("status", apiKeyMetadata.getApiKey().getStatus().ordinal());
            }
        }
    }

    Session session = null;
    PageFinder<ApiKey> pageFinder = null;
    try {
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery("select count(1) " + sqlSuffixBuilder.toString());
        sqlQuery.setProperties(properties);
        int rowCount = ((Number) sqlQuery.uniqueResult()).intValue();
        pageFinder = new PageFinder<ApiKey>(query.getPage(), query.getPageSize(), rowCount);
        if (rowCount > 0) {
            // ?
            sqlSuffixBuilder.append(" group by ");
            sqlSuffixBuilder
                    .append(" t1.id, t1.app_key, t1.app_secret, t1.status, t1.update_user, t1.update_time ");
            sqlSuffixBuilder.append(" order by ");
            sqlSuffixBuilder.append(" t1.update_time desc ");
            // ?APP
            sqlQuery = session.createSQLQuery(sqlPrefixBuilder.append(sqlSuffixBuilder).toString());
            sqlQuery.setFirstResult(pageFinder.getStartOfPage());
            sqlQuery.setMaxResults(pageFinder.getPageSize());
            sqlQuery.setProperties(properties);
            sqlQuery.addScalar("id", Hibernate.STRING);
            sqlQuery.addScalar("appKey", Hibernate.STRING);
            sqlQuery.addScalar("appSecret", Hibernate.STRING);
            sqlQuery.addScalar("status", customApiKeyStatus);
            sqlQuery.addScalar("updateTime", Hibernate.STRING);
            sqlQuery.addScalar("updateUser", Hibernate.STRING);
            sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKey.class));
            List<ApiKey> merchantApiKeys = sqlQuery.list();
            // ?APP?
            sqlPrefixBuilder.setLength(0);
            sqlPrefixBuilder.append(" select ");
            sqlPrefixBuilder.append(
                    " t1.metadata_key as metadataKey, t1.metadata_val as metadataVal, t2.metadata_tag as metadataTag ");
            sqlPrefixBuilder.append(" from ");
            sqlPrefixBuilder.append(" tbl_merchant_api_key_metadata t1 ");
            sqlPrefixBuilder.append(" left join ");
            sqlPrefixBuilder.append(" ( ");
            sqlPrefixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement());
            sqlPrefixBuilder.append(" ) t2 ");
            sqlPrefixBuilder.append(" on(t1.metadata_val = t2.metadata_val) ");
            sqlPrefixBuilder.append(" where ");
            sqlPrefixBuilder.append(" t1.key_id = ? ");
            sqlQuery = session.createSQLQuery(sqlPrefixBuilder.toString());
            sqlQuery.addScalar("metadataKey", customAppType);
            sqlQuery.addScalar("metadataVal", Hibernate.STRING);
            sqlQuery.addScalar("metadataTag", Hibernate.STRING);
            sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class));
            for (ApiKey merchantApiKey : merchantApiKeys) {
                sqlQuery.setParameter(0, merchantApiKey.getId());
                merchantApiKey.setApiKeyMetadatas(new HashSet<ApiKeyMetadata>(sqlQuery.list()));
            }
            pageFinder.setData(merchantApiKeys);
        }
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
    return pageFinder;
}

From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java

@Override
public List<ApiKeyMetadata> queryApiKeyCustomers(String apiKeyId) throws Exception {
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.append(" select ");
    sqlBuilder.append(//from   ww  w  .j av  a  2  s.c  o  m
            " t1.metadata_key as metadataKey, t1.metadata_val as metadataVal, t1.metadata_tag as metadataTag ");
    sqlBuilder.append(" from ");
    sqlBuilder.append(" ( ");
    sqlBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement());
    sqlBuilder.append(" ) t1 ");
    sqlBuilder.append(" inner join ");
    sqlBuilder.append(" tbl_merchant_api_key_metadata t2 ");
    sqlBuilder.append(" on(t1.metadata_val = t2.metadata_val) ");
    sqlBuilder.append(" where ");
    sqlBuilder.append(" t2.key_id = ? ");

    Session session = null;
    try {
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
        sqlQuery.setParameter(0, apiKeyId);
        sqlQuery.addScalar("metadataKey", customAppType);
        sqlQuery.addScalar("metadataVal", Hibernate.STRING);
        sqlQuery.addScalar("metadataTag", Hibernate.STRING);
        sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class));
        return sqlQuery.list();
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
}

From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java

@Override
public List<ApiKeyMetadata> queryApiKeyByType(String type) throws Exception {
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.append(" SELECT km.metadata_key as metadataKey,km.metadata_val as metadataVal ");
    sqlBuilder.append(" FROM tbl_merchant_api_key_metadata km ");
    sqlBuilder.append(" WHERE km.metadata_key = ? ");

    Session session = null;//w  w w.j  a v  a2 s  .  co  m
    try {
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
        sqlQuery.setParameter(0, type);
        sqlQuery.addScalar("metadataKey", customAppType);
        sqlQuery.addScalar("metadataVal", Hibernate.STRING);
        sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class));
        return sqlQuery.list();
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
}

From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java

@Override
public List<ApiKeyMetadata> queryApiKeyByapiKeyIdAndType(String apiKeyId, String type) throws Exception {
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.append(" SELECT km.metadata_key as metadataKey,km.metadata_val as metadataVal ");
    sqlBuilder.append(" FROM tbl_merchant_api_key_metadata km ");
    sqlBuilder.append(" WHERE km.key_id = ? ");
    sqlBuilder.append(" AND km.metadata_key = ? ");

    Session session = null;//from w ww .j  av a 2  s .c o m
    try {
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
        sqlQuery.setParameter(0, apiKeyId);
        sqlQuery.setParameter(1, type);
        sqlQuery.addScalar("metadataKey", customAppType);
        sqlQuery.addScalar("metadataVal", Hibernate.STRING);
        sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class));
        return sqlQuery.list();
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
}

From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java

/**
 * ?metadataValApiKeyMetadata?/*w w  w . j  a  v  a 2s  .  com*/
 * @param metadataVal
 * @return
 * @throws Exception
 */
@Override
public ApiKeyMetadata queryApiKeyByMetadataVal(String metadataVal) throws Exception {
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.append(" SELECT km.metadata_key as metadataKey,km.metadata_val as metadataVal,km.key_id as id ");
    sqlBuilder.append(" FROM tbl_merchant_api_key_metadata km ");
    sqlBuilder.append(" WHERE km.metadata_key = 'MERCHANTS' ");
    sqlBuilder.append(" AND km.metadata_val = ? ");

    Session session = null;
    try {
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
        sqlQuery.setParameter(0, metadataVal);
        sqlQuery.addScalar("metadataKey", customAppType);
        sqlQuery.addScalar("metadataVal", Hibernate.STRING);
        sqlQuery.addScalar("id", Hibernate.STRING);
        sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class));
        List<ApiKeyMetadata> list = sqlQuery.list();
        return list.size() == 0 ? null : list.get(0);
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
}

From source file:Configuration.UserHelper.java

public boolean loginUser(String username, String password)
        throws NoSuchAlgorithmException, InvalidKeySpecException {
    try {//from  w  ww.j  av  a 2 s. c o  m
        tx = session.beginTransaction();
        SQLQuery q = session.createSQLQuery(
                "Select UserName, UserPassword from Users where UserName =? and UserPassword =?");
        q.addEntity(Users.class);
        q.setParameter(0, username);
        q.setParameter(1, password);
        List result = q.list();
        Users tmpuser = (Users) result.get(0);

        System.out.println("tmpuser" + tmpuser);

        //System.out.println(tmpuser.getFirstName());

        //System.out.println("yyyyyyyy " + result.get(0));

        //q.setParameter(1, byteArrayToHexString(getEncryptedPassword(password, salt)));
        //            byte[] saltByte=hexStringtoByteArry();
        //            Users user = (Users) q.
        //            String pass = user.getUserPassword();
        // session.close();
    } catch (HibernateException e) {
        e.printStackTrace();
        try {
            tx.rollback();
        } catch (RuntimeException r) {
            System.out.println("Can't rollback transaction");

        }
        throw e;
    } finally {
        if (session != null) {
            session.close();
        }
    }
    return false;
}

From source file:controlers.AdminControler.java

public AdminControler() {
    Session session = null;//from   w ww . j a v a  2 s . c o m
    Transaction tx = null;
    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        String sql = "SELECT R.idRez, K.username, F.naziv AS nazivFestivala, R.paket, D.redniBroj AS kojiDan, R.brojUlaznica, R.vremeRez, F.cenaPaket, F.cenaDan "
                + "FROM `rezervacija` R, `korisnik` K, `festival` F, `Dan` D "
                + "WHERE R.username = K.username " + "AND R.idFest = F.idFest " + "AND R.idDan = D.idDan "
                + "AND R.status = 'rezervacija' " + "AND R.vremeRez < :current_date " + "GROUP BY R.idRez "
                + "ORDER BY R.vremeRez DESC;";

        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter("current_date", new Date());
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        sveRezervacije = query.list();

        Criteria cr = session.createCriteria(Festival.class);
        cr.add(Restrictions.ne("status", "prosao"));
        cr.add(Restrictions.ne("status", "otkazan"));
        cr.addOrder(Order.asc("datumVremeDo"));
        List result = cr.list();

        aktuelniFestivali = new HashMap<String, Long>();
        for (Object obj : result) {
            Festival festival = (Festival) obj;
            aktuelniFestivali.put(festival.getNaziv(), festival.getIdFest());
        }

    } catch (Exception ex) {
        if (tx != null)
            tx.rollback();
        ex.printStackTrace();
    } finally {
        if (tx != null)
            tx.commit();
        session.close();
    }
}

From source file:controlers.AdminControler.java

public void buyReserve(Long idRes) {

    Session session = null;//from w w  w.  jav  a 2  s  .  co  m
    Transaction tx = null;
    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        Criteria cr = session.createCriteria(Rezervacija.class);
        cr.add(Restrictions.eq("idRez", idRes));
        cr.setMaxResults(1);
        List result = cr.list();
        Rezervacija rezervacija = (Rezervacija) result.get(0);

        rezervacija.setStatus("kupljeno");

        session.save(rezervacija);

        tx.commit();
        session.close();

        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        String sql = "SELECT R.idRez, K.username, F.naziv AS nazivFestivala, R.paket, D.redniBroj AS kojiDan, R.brojUlaznica, R.vremeRez, F.cenaPaket, F.cenaDan "
                + "FROM `rezervacija` R, `korisnik` K, `festival` F, `Dan` D "
                + "WHERE R.username = K.username " + "AND R.idFest = F.idFest " + "AND R.idDan = D.idDan "
                + "AND R.status = 'rezervacija' " + "AND R.vremeRez < :current_date " + "GROUP BY R.idRez "
                + "ORDER BY R.vremeRez DESC;";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter("current_date", new Date());
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        sveRezervacije = query.list();

    } catch (Exception ex) {
        if (tx != null)
            tx.rollback();
        ex.printStackTrace();
    } finally {
        if (tx != null)
            tx.commit();
        session.close();
    }

}