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.corundumstudio.core.extensions.hibernate.InsertDeleteListResultTest.java

License:Apache License

private List listQueryResult() {
    Session session = sessionFactory.openSession();
    SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE address = :address");
    query.addScalar("id", LongType.INSTANCE);
    query.setCacheable(true);/*w ww .j av a2  s .c o m*/
    query.setCacheRegion(multiCacheRegion);
    query.setParameter("address", addressValue);
    List res = query.list();
    session.close();
    return res;
}

From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteUniqueResultTest.java

License:Apache License

private void checkQueryResult(SimpleEntity entity, Long expected) {
    Session session = sessionFactory.openSession();
    SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE phone = :phone");
    query.addScalar("id", LongType.INSTANCE);
    query.setCacheable(true);//from  w w  w  .  j av a 2 s. c  o m
    query.setCacheRegion(cacheRegion);
    query.setParameter("phone", entity.getPhone());
    Long res = (Long) query.uniqueResult();
    Assert.assertEquals(expected, res);
    session.close();
}

From source file:com.daro.persistence.generic.dao.GenericDaoImpl.java

License:GNU General Public License

/**
 * Get Entity T using a SQL query string and SqlQuery object with parameters list.
 * /* w  w  w. ja  va 2s .  c o m*/
 * @param sql For example "SELECT * FROM table WHERE id = ?"
 * @param params Parameters list to match with ? in SQL query
 * @return Entity T type
 * @throws PersistenceException
 */
@SuppressWarnings("unchecked")
public T executeSqlQuery(String sql, List<String> params) throws PersistenceException {
    T t = null;

    SQLQuery query = createSqlQuery(sql);

    try {
        query.addEntity(clazz);

        //inserts parameters into sql string replacing ? symbols by order
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i, params.get(i));
        }

        t = (T) query.uniqueResult();
    } catch (Exception e) {
        throw new PersistenceException(e);
    }

    return t;
}

From source file:com.datacentre.model.dao.impl.CardDaoImpl.java

@Override
public void add(RegistrationCard card) {
    card.setDateRegistration(Calendar.getInstance().getTime());
    Session session = sessionFactory.openSession();

    ValidatorFactory vf = Validation.buildDefaultValidatorFactory();
    Validator validator = vf.getValidator();

    try {//from  w  w  w .j  a v a  2 s.c  om
        validate(card, validator);
    } catch (ValidationException validException) {
        return;
    }

    session.beginTransaction();
    SQLQuery insertQuery = session.createSQLQuery(
            "" + "INSERT INTO registration_card(mobile,full_name,age,date_registration)VALUES(?,?,?,?)");
    insertQuery.setParameter(0, card.getMobile());
    insertQuery.setParameter(1, card.getFullName());
    insertQuery.setParameter(2, card.getAge());
    insertQuery.setParameter(3, card.getDateRegistration());
    insertQuery.executeUpdate();
    session.getTransaction().commit();
}

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

public List<BriefServerInfo> getAccessiblePoolServers(final long userId, final String poolId) {
    try {/*ww  w  .j a va  2  s  .  co m*/
        return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
            @Override
            public List<BriefServerInfo> run(Session session) {
                Map<String, Object> keyValues = new HashMap<>();

                SQLQuery query;
                if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_GLOBAL_ID.equals(poolId)) {
                    query = session
                            .createSQLQuery(GLOBAL_POOL_QUERY + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                } else if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_ALL_ID.equals(poolId)
                        || poolId == null) {

                    // system user has access to all servers in the pool
                    if (userId == DBInit.SYSTEM_USER_ID) {
                        query = session
                                .createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_SYSTEM_USER_QUERY
                                        + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                    } else {
                        query = session.createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_QUERY
                                + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                        query.setParameter("userId", userId);
                    }
                } else {
                    StringBuilder sql;
                    if (userId == DBInit.SYSTEM_USER_ID) {
                        sql = new StringBuilder(SPECIFIC_POOL_SYSTEM_USER_QUERY + " AND g.seq_id = :poolId");
                    } else {
                        sql = new StringBuilder(SPECIFIC_POOL_QUERY + " AND g.seq_id = :poolId");
                    }
                    keyValues.put("poolId", Long.valueOf(poolId));

                    sql.append(POOL_GROUP_BY_CLAUSE);
                    sql.append(POOL_ORDER_BY_CLAUSE);

                    query = session.createSQLQuery(sql.toString());
                    if (userId != DBInit.SYSTEM_USER_ID)
                        query.setParameter("userId", userId);
                }

                for (Map.Entry<String, Object> entry : keyValues.entrySet()) {
                    query.setParameter(entry.getKey(), entry.getValue());
                }

                return buildBriefServerInfoList(query.list());
            }

            @Override
            public List<BriefServerInfo> failed(SQLException e) throws SQLException {
                logger.error("Failed to look up servers from pool " + poolId + " for user " + userId, e);
                throw e;
            }
        });
    } catch (SQLException e) {
        throw new AsmManagerRuntimeException(e);
    }
}

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

public List<BriefServerInfo> getAccessibleServers(final long userId, final List<String> serverRefIds) {
    try {/*from   ww w . java 2s  .c  o  m*/
        return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
            private StringBuilder appendServerRefIds(StringBuilder sql, Map<String, Object> keyValues) {
                if (serverRefIds != null && serverRefIds.size() > 0) {
                    int i = 0;
                    sql.append(" AND i.ref_id IN (");
                    for (String serverRefId : serverRefIds) {
                        String name = "ref" + i++;
                        sql.append(':').append(name).append(", ");
                        keyValues.put(name, serverRefId);
                    }
                    sql.deleteCharAt(sql.length() - 1);
                    sql.deleteCharAt(sql.length() - 1);
                    sql.append(")");
                }
                return sql;
            }

            @Override
            public List<BriefServerInfo> run(Session session) {
                Map<String, Object> keyValues = new HashMap<>();

                StringBuilder sql = new StringBuilder(GLOBAL_POOL_QUERY);
                if (userId == DBInit.SYSTEM_USER_ID) {
                    appendServerRefIds(sql, keyValues).append(" UNION ")
                            .append(SPECIFIC_POOL_SYSTEM_USER_QUERY);
                } else {
                    appendServerRefIds(sql, keyValues).append(" UNION ").append(SPECIFIC_POOL_QUERY);
                }
                appendServerRefIds(sql, keyValues).append(POOL_GROUP_BY_CLAUSE).append(POOL_ORDER_BY_CLAUSE);
                SQLQuery query = session.createSQLQuery(sql.toString());

                if (userId != DBInit.SYSTEM_USER_ID)
                    query.setParameter("userId", userId);

                for (Map.Entry<String, Object> entry : keyValues.entrySet()) {
                    query.setParameter(entry.getKey(), entry.getValue());
                }

                return buildBriefServerInfoList(query.list());
            }

            @Override
            public List<BriefServerInfo> failed(SQLException e) throws SQLException {
                logger.error("Failed to look up servers " + serverRefIds + " for user " + userId, e);
                throw e;
            }
        });
    } catch (SQLException e) {
        throw new AsmManagerRuntimeException(e);
    }
}

From source file:com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

License:Open Source License

/**
 * Returns the number of servers available in the pool.
 * /*from www  . j  a  v  a 2s  .c o m*/
 * @param poolId the id of the pool whose server count will be returned.
 * @return the number of servers available in the pool.
 */
public int getNumberOfServersInPool(final String poolId) {
    int numberOfServersInPool = 0;
    if (poolId != null && poolId.trim().length() > 0) {
        // Initialize locals.
        Session session = null;
        Transaction tx = null;
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();
            SQLQuery query = session.createSQLQuery(POOL_SERVERS_COUNT);
            query.setParameter("poolId", Long.valueOf(poolId));
            numberOfServersInPool = ((BigInteger) query.uniqueResult()).intValue();
            tx.commit();
        } catch (Exception e) {
            logger.warn("Caught exception during getNumberOfServersInPool: " + e);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during getNumberOfServersInPool: " + ex);
            }
        } finally {
            try {
                if (session != null) {
                    session.close();
                }
            } catch (Exception ex) {
                logger.warn("Unable to close session during getNumberOfServersInPool: " + ex);
            }
        }
    }
    return numberOfServersInPool;
}

From source file:com.ephesoft.dcma.core.hibernate.DynamicHibernateDao.java

License:Open Source License

/**
 * To create queries./*from  ww w . ja v  a 2s.com*/
 * 
 * @param queryString String
 * @param params Object
 * @return SQLQuery
 */
public SQLQuery createQuery(String queryString, Object... params) {
    SQLQuery sqlQuery = sessionFactory.openStatelessSession().createSQLQuery(queryString);

    if (params != null) {
        int pos = 1;
        for (Object p : params) {
            sqlQuery.setParameter(pos++, p);
        }
    }
    return sqlQuery;
}

From source file:com.ephesoft.dcma.core.hibernate.DynamicHibernateDao.java

License:Open Source License

/**
 * To create, update or insert query.//from  w  w w. j a va 2 s. co m
 * 
 * @param session StatelessSession
 * @param queryString String
 * @param params Object
 * @return SQLQuery
 */
public SQLQuery createUpdateOrInsertQuery(StatelessSession session, String queryString, Object... params) {
    SQLQuery sqlQuery = session.createSQLQuery(queryString);

    if (params != null) {
        int pos = 1;
        for (Object p : params) {
            sqlQuery.setParameter(pos++, p);
        }
    }
    return sqlQuery;
}

From source file:com.evolveum.midpoint.repo.sql.SqlAuditServiceImpl.java

License:Apache License

protected int cleanupAuditAttempt(Date minValue, Session session) {
    final Dialect dialect = Dialect.getDialect(getSessionFactoryBean().getHibernateProperties());
    if (!dialect.supportsTemporaryTables()) {
        LOGGER.error("Dialect {} doesn't support temporary tables, couldn't cleanup audit logs.",
                new Object[] { dialect });
        throw new SystemException(
                "Dialect " + dialect + " doesn't support temporary tables, couldn't cleanup audit logs.");
    }//from  w  ww .j  a  v a  2 s .  co m

    //create temporary table
    final String tempTable = dialect.generateTemporaryTableName(RAuditEventRecord.TABLE_NAME);
    createTemporaryTable(session, dialect, tempTable);
    LOGGER.trace("Created temporary table '{}'.", new Object[] { tempTable });

    //fill temporary table, we don't need to join task on object on container, oid and id is already in task table
    StringBuilder sb = new StringBuilder();
    sb.append("insert into ").append(tempTable).append(' ');
    sb.append("select a.id as id from ").append(RAuditEventRecord.TABLE_NAME).append(" a");
    sb.append(" where a.").append(RAuditEventRecord.COLUMN_TIMESTAMP).append(" < ?");

    SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter(0, new Timestamp(minValue.getTime()));
    int insertCount = query.executeUpdate();
    LOGGER.trace("Inserted {} audit record ids ready for deleting.", new Object[] { insertCount });

    //drop records from m_task, m_object, m_container
    session.createSQLQuery(createDeleteQuery(RObjectDeltaOperation.TABLE_NAME, tempTable,
            RObjectDeltaOperation.COLUMN_RECORD_ID)).executeUpdate();
    session.createSQLQuery(createDeleteQuery(RAuditEventRecord.TABLE_NAME, tempTable, "id")).executeUpdate();

    //drop temporary table
    if (dialect.dropTemporaryTableAfterUse()) {
        LOGGER.debug("Dropping temporary table.");
        sb = new StringBuilder();
        sb.append(dialect.getDropTemporaryTableString());
        sb.append(' ').append(tempTable);

        session.createSQLQuery(sb.toString()).executeUpdate();
    }

    return insertCount;
}