Example usage for org.hibernate Session createSQLQuery

Introduction

In this page you can find the example usage for org.hibernate Session createSQLQuery.

Prototype

@Override
    NativeQuery createSQLQuery(String queryString);

Source Link

Usage

From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java

License:Apache License

private void dumpOrgClosureTypeTable(Session session, String tableName) {
    Query q = session.createSQLQuery("select descendant_oid, ancestor_oid, val from " + tableName)
            .addScalar("descendant_oid", StringType.INSTANCE).addScalar("ancestor_oid", StringType.INSTANCE)
            .addScalar("val", IntegerType.INSTANCE);
    List<Object[]> list = q.list();
    LOGGER.trace("{} ({} rows):", tableName, list.size());
    for (Object[] row : list) {
        LOGGER.trace(" - [d={}, a={}, val={}]", row);
    }/*from w w w  .jav a  2  s  . c o  m*/
}

---

From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java

License:Apache License

private void initializeOracleTemporaryTable() {
    Session session = baseHelper.getSessionFactory().openSession();
    Query qCheck = session.createSQLQuery("select table_name from user_tables where table_name = upper('"
            + TEMP_DELTA_TABLE_NAME_FOR_ORACLE + "')");
    if (qCheck.list().isEmpty()) {
        LOGGER.info("Creating temporary table {}", TEMP_DELTA_TABLE_NAME_FOR_ORACLE);
        session.beginTransaction();/*ww  w.j av  a2  s  . c  o  m*/
        Query qCreate = session.createSQLQuery("CREATE GLOBAL TEMPORARY TABLE "
                + TEMP_DELTA_TABLE_NAME_FOR_ORACLE + "    (descendant_oid VARCHAR2(36 CHAR), "
                + "     ancestor_oid VARCHAR2(36 CHAR), " + "     val NUMBER (10, 0), "
                + "     PRIMARY KEY (descendant_oid, ancestor_oid)) " + "  ON COMMIT DELETE ROWS");
        try {
            qCreate.executeUpdate();
            session.getTransaction().commit();
        } catch (RuntimeException e) {
            String m = "Couldn't create temporary table " + TEMP_DELTA_TABLE_NAME_FOR_ORACLE
                    + ". Please create the table manually.";
            LoggingUtils.logException(LOGGER, m, e);
            throw new SystemException(m, e);
        }
    }
    session.close();
}

---

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

License:Apache License

@Test
public void test30DeleteAssignment() throws Exception {
    //given//  w ww .  j  a  v a 2 s  .c  o  m

    //when
    //        ObjectModificationType modification = prismContext.getPrismJaxbProcessor().unmarshalObject(
    //                new File(TEST_DIR, "modify-delete-assignment.xml"), ObjectModificationType.class);
    //
    //        ObjectDelta delta = DeltaConvertor.createObjectDelta(modification, RoleType.class, prismContext);

    AssignmentType a = new AssignmentType();
    a.setId(4L);
    ObjectDelta<RoleType> delta = ObjectDelta.createModificationDeleteContainer(RoleType.class,
            "00000000-8888-6666-0000-100000000005", RoleType.F_ASSIGNMENT, prismContext, a);

    OperationResult result = new OperationResult("delete assignment");
    repositoryService.modifyObject(RoleType.class, delta.getOid(), delta.getModifications(), result);
    result.recomputeStatus();
    result.recordSuccessIfUnknown();

    //then
    AssertJUnit.assertTrue(result.isSuccess());

    result = new OperationResult("get role");
    PrismObject repoRole = repositoryService.getObject(RoleType.class, ROLE_OID, null, result);
    result.recomputeStatus();
    result.recordSuccessIfUnknown();
    AssertJUnit.assertTrue(result.isSuccess());

    PrismContainer inducement = repoRole.findContainer(new ItemPath(RoleType.F_INDUCEMENT));
    AssertJUnit.assertNotNull(inducement);
    AssertJUnit.assertEquals(3, inducement.getValues().size());

    PrismContainer assignment = repoRole.findContainer(new ItemPath(AbstractRoleType.F_ASSIGNMENT));
    AssertJUnit.assertNotNull(assignment);
    AssertJUnit.assertEquals(1, assignment.getValues().size());

    AssertJUnit.assertNotNull(assignment.getValue(1L));

    Session session = open();
    try {
        Query query = session
                .createSQLQuery("select count(*) from m_assignment where owner_oid=:oid and id=:id");
        query.setParameter("oid", delta.getOid());
        query.setParameter("id", (short) 4);
        Number number = (Number) query.uniqueResult();
        AssertJUnit.assertEquals(0, number.intValue());
    } finally {
        close(session);
    }
}

---

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.");
    }/* w ww  . ja  va2  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;
}

---

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

License:Apache License

private <T extends ObjectType> PrismObject<T> getObject(Session session, Class<T> type, String oid,
        Collection<SelectorOptions<GetOperationOptions>> options, boolean lockForUpdate)
        throws ObjectNotFoundException, SchemaException, DtoTranslationException, QueryException {

    boolean lockedForUpdateViaHibernate = false;
    boolean lockedForUpdateViaSql = false;

    LockOptions lockOptions = new LockOptions();
    //todo fix lock for update!!!!!
    if (lockForUpdate) {
        if (getConfiguration().isLockForUpdateViaHibernate()) {
            lockOptions.setLockMode(LockMode.PESSIMISTIC_WRITE);
            lockedForUpdateViaHibernate = true;
        } else if (getConfiguration().isLockForUpdateViaSql()) {
            if (LOGGER.isTraceEnabled()) {
                LOGGER.trace("Trying to lock object " + oid + " for update (via SQL)");
            }//from w w  w  .j av  a2s . com
            long time = System.currentTimeMillis();
            SQLQuery q = session.createSQLQuery("select oid from m_object where oid = ? for update");
            q.setString(0, oid);
            Object result = q.uniqueResult();
            if (result == null) {
                return throwObjectNotFoundException(type, oid);
            }
            if (LOGGER.isTraceEnabled()) {
                LOGGER.trace("Locked via SQL (in " + (System.currentTimeMillis() - time) + " ms)");
            }
            lockedForUpdateViaSql = true;
        }
    }

    if (LOGGER.isTraceEnabled()) {
        if (lockedForUpdateViaHibernate) {
            LOGGER.trace("Getting object " + oid + " with locking for update (via hibernate)");
        } else if (lockedForUpdateViaSql) {
            LOGGER.trace("Getting object " + oid + ", already locked for update (via SQL)");
        } else {
            LOGGER.trace("Getting object " + oid + " without locking for update");
        }
    }

    GetObjectResult fullObject = null;
    if (!lockForUpdate) {
        Query query = session.getNamedQuery("get.object");
        query.setString("oid", oid);
        query.setResultTransformer(GetObjectResult.RESULT_TRANSFORMER);
        query.setLockOptions(lockOptions);

        fullObject = (GetObjectResult) query.uniqueResult();
    } else {
        // we're doing update after this get, therefore we load full object right now
        // (it would be loaded during merge anyway)
        // this just loads object to hibernate session, probably will be removed later. Merge after this get
        // will be faster. Read and use object only from fullObject column.
        // todo remove this later [lazyman]
        Criteria criteria = session.createCriteria(ClassMapper.getHQLTypeClass(type));
        criteria.add(Restrictions.eq("oid", oid));

        criteria.setLockMode(lockOptions.getLockMode());
        RObject obj = (RObject) criteria.uniqueResult();

        if (obj != null) {
            obj.toJAXB(getPrismContext(), null).asPrismObject();
            fullObject = new GetObjectResult(obj.getFullObject(), obj.getStringsCount(), obj.getLongsCount(),
                    obj.getDatesCount(), obj.getReferencesCount(), obj.getPolysCount());
        }
    }

    LOGGER.trace("Got it.");
    if (fullObject == null) {
        throwObjectNotFoundException(type, oid);
    }

    LOGGER.trace("Transforming data to JAXB type.");
    PrismObject<T> prismObject = updateLoadedObject(fullObject, type, options, session);
    validateObjectType(prismObject, type);

    return prismObject;
}

---

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

License:Apache License

private <T extends ObjectType> String nonOverwriteAddObjectAttempt(PrismObject<T> object, ObjectType objectType,
        RObject rObject, String originalOid, Session session)
        throws ObjectAlreadyExistsException, SchemaException, DtoTranslationException {

    // check name uniqueness (by type)
    if (StringUtils.isNotEmpty(originalOid)) {
        LOGGER.trace("Checking oid uniqueness.");
        //todo improve this table name bullshit
        Class hqlType = ClassMapper.getHQLTypeClass(object.getCompileTimeClass());
        SQLQuery query = session
                .createSQLQuery("select count(*) from " + RUtil.getTableName(hqlType) + " where oid=:oid");
        query.setString("oid", object.getOid());

        Number count = (Number) query.uniqueResult();
        if (count != null && count.longValue() > 0) {
            throw new ObjectAlreadyExistsException("Object '" + object.getCompileTimeClass().getSimpleName()
                    + "' with oid '" + object.getOid() + "' already exists.");
        }//from w ww.j  a  va 2  s  . c o  m
    }

    updateFullObject(rObject, object);

    LOGGER.trace("Saving object (non overwrite).");
    String oid = (String) session.save(rObject);

    //todo finish orgClosureManager
    //Collection<ReferenceDelta> modifications = createAddParentRefDelta(object);
    //orgClosureManager.updateOrgClosure(modifications, session, oid, object.getCompileTimeClass(),
    //        OrgClosureManager.Operation.ADD);

    if (objectType instanceof OrgType || !objectType.getParentOrgRef().isEmpty()) {
        long time = System.currentTimeMillis();
        LOGGER.trace("Org. structure closure table update started.");
        objectType.setOid(oid);
        fillHierarchy(rObject, session, true);
        LOGGER.trace("Org. structure closure table update finished ({} ms).",
                new Object[] { (System.currentTimeMillis() - time) });
    }

    return oid;
}

---

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

License:Apache License

private <T extends ObjectType> int countObjectsAttempt(Class<T> type, ObjectQuery query,
        OperationResult result) {/*from   w w w  .java  2  s .  c  om*/
    int count = 0;

    Session session = null;
    try {
        Class<? extends RObject> hqlType = ClassMapper.getHQLTypeClass(type);

        session = beginReadOnlyTransaction();
        Number longCount;
        if (query == null || query.getFilter() == null) {
            // this is 5x faster than count with 3 inner joins, it can probably improved also for queries which
            // filters uses only properties from concrete entities like RUser, RRole by improving interpreter [lazyman]
            SQLQuery sqlQuery = session.createSQLQuery("SELECT COUNT(*) FROM " + RUtil.getTableName(hqlType));
            longCount = (Number) sqlQuery.uniqueResult();
        } else {
            QueryEngine engine = new QueryEngine(getConfiguration(), getPrismContext());
            RQuery rQuery = engine.interpret(query, type, null, true, session);

            longCount = (Number) rQuery.uniqueResult();
        }
        LOGGER.trace("Found {} objects.", longCount);
        count = longCount != null ? longCount.intValue() : 0;
    } catch (QueryException | RuntimeException ex) {
        handleGeneralException(ex, session, result);
    } finally {
        cleanupSessionAndResult(session, result);
    }

    return count;
}

---

From source file:com.evolveum.midpoint.repo.sql.testing.DBValidator.java

License:Apache License

private static final void validate(String sql, String message, Session session) {
    Query query = session.createSQLQuery(sql);
    List nullAssignments = query.list();
    if (!nullAssignments.isEmpty()) {
        throw new SystemException(message + Arrays.deepToString(nullAssignments.toArray()));
    }/*w w  w  .  j av  a 2s. c  o  m*/
}

---

From source file:com.evolveum.midpoint.repo.sql.testing.TestSqlRepositoryBeanPostProcessor.java

License:Apache License

@Override
public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
    if ((bean instanceof SqlRepositoryFactory) || (bean instanceof SessionFactory)
            || (bean instanceof SqlRepositoryServiceImpl)) {
        LOGGER.info("Post process: " + bean.getClass().getName());
    }/*from   w ww  .  ja v  a  2 s  . co m*/

    if (!(bean instanceof SessionFactory)) {
        return bean;
    }
    LOGGER.info("Postprocessing session factory - removing everything from database if necessary.");

    TestSqlRepositoryFactory factory = context.getBean(TestSqlRepositoryFactory.class);
    //we'll attempt to drop database objects if configuration contains dropIfExists=true and embedded=false
    SqlRepositoryConfiguration config = factory.getSqlConfiguration();
    if (!config.isDropIfExists() || config.isEmbedded()) {
        LOGGER.info("We're not deleting objects from DB, drop if exists=false or embedded=true.");
        return bean;
    }

    LOGGER.info("Deleting objects from database.");

    SessionFactory sessionFactory = (SessionFactory) bean;
    Session session = sessionFactory.openSession();
    try {
        session.beginTransaction();

        Query query;
        if (useProcedure(factory.getSqlConfiguration())) {
            LOGGER.info("Using truncate procedure.");
            query = session.createSQLQuery("{ call " + TRUNCATE_PROCEDURE + "() }");
            query.executeUpdate();
        } else {
            LOGGER.info("Using truncate function.");
            query = session.createSQLQuery("select " + TRUNCATE_FUNCTION + "();");
            query.uniqueResult();
        }

        session.getTransaction().commit();
    } catch (Exception ex) {
        LOGGER.error("Couldn't cleanup database, reason: " + ex.getMessage(), ex);

        if (session != null && session.isOpen()) {
            Transaction transaction = session.getTransaction();
            if (transaction != null && transaction.isActive()) {
                transaction.rollback();
            }
        }
        throw new BeanInitializationException("Couldn't cleanup database, reason: " + ex.getMessage(), ex);
    } finally {
        if (session != null && session.isOpen()) {
            session.close();
        }
    }

    return bean;
}

---

From source file:com.exilant.GLEngine.CoaCache.java

License:Open Source License

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void loadAccountData() {

    /*/*from ww  w .j  av  a  2  s  . c  o m*/
     * 1.Loads all the account codes and details of that as GLAccount objects in theGLAccountCode,theGLAccountId HashMap's
     */

    // Temporary place holders
    final HashMap glAccountCodes = new HashMap();
    final HashMap glAccountIds = new HashMap();
    final HashMap accountDetailType = new HashMap();

    String sql = "select id as \"id\",name as \"name\",tableName as \"tableName\","
            + "description as \"description\",columnName as \"columnName\",attributeName as \"attributeName\""
            + ",nbrOfLevels as  \"nbrOfLevels\" from AccountDetailType";

    final Session currentSession = persistenceService.getSession();
    SQLQuery createSQLQuery = currentSession.createSQLQuery(sql);
    createSQLQuery.addScalar("id", IntegerType.INSTANCE).addScalar("name").addScalar("tableName")
            .addScalar("description").addScalar("columnName").addScalar("attributeName")
            .setResultTransformer(Transformers.aliasToBean(AccountDetailType.class));
    List<AccountDetailType> accountDetailTypeList = new ArrayList<AccountDetailType>();
    List<GLAccount> glAccountCodesList = new ArrayList<GLAccount>();
    new ArrayList<GLAccount>();

    accountDetailTypeList = createSQLQuery.list();
    for (final AccountDetailType type : accountDetailTypeList)
        accountDetailType.put(type.getAttributeName(), type);
    sql = "select ID as \"ID\", glCode as \"glCode\" ,name as \"name\" ,"
            + "isActiveForPosting as \"isActiveForPosting\" ,classification as \"classification\", functionReqd as \"functionRequired\" from chartofaccounts ";
    createSQLQuery = currentSession.createSQLQuery(sql);
    createSQLQuery.addScalar("ID", IntegerType.INSTANCE).addScalar("glCode").addScalar("name")
            .addScalar("isActiveForPosting", BooleanType.INSTANCE)
            .addScalar("classification", LongType.INSTANCE).addScalar("functionRequired", BooleanType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(GLAccount.class));

    glAccountCodesList = createSQLQuery.list();
    for (final GLAccount type : glAccountCodesList)
        glAccountCodes.put(type.getCode(), type);
    for (final GLAccount type : glAccountCodesList)
        glAccountIds.put(type.getId(), type);
    loadParameters(glAccountCodes, glAccountIds);
    try {
        final HashMap<String, HashMap> hm = new HashMap<String, HashMap>();
        hm.put(ACCOUNTDETAILTYPENODE, accountDetailType);
        hm.put(GLACCCODENODE, glAccountCodes);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("Loading size:" + glAccountCodes.size());
        hm.put(GLACCIDNODE, glAccountIds);
        applicationCacheManager.put(ROOTNODE, hm);
    } catch (final Exception e) {
        throw e;
    }

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »