List of usage examples for org.hibernate Session createSQLQuery
@Override
NativeQuery createSQLQuery(String queryString);
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private int quickCheck(Session session) { Query q = session .createSQLQuery("select count(m_org.oid) as problems from m_org left join m_org_closure cl " + "on cl.descendant_oid = m_org.oid and cl.ancestor_oid = m_org.oid " + "where cl.descendant_oid is null") .addScalar("problems", IntegerType.INSTANCE); List problemsList = q.list(); if (problemsList == null || problemsList.size() != 1) { throw new IllegalStateException("Unexpected return value from the closure check query: " + problemsList + " (a 1-item list of Integer expected)"); }/*from w w w .j a va 2 s. com*/ return (int) problemsList.get(0); }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void addEdgeSimple(String oid, String parent, Session session) { if (parent != null) { long start = System.currentTimeMillis(); Query addToClosureQuery = session .createSQLQuery("insert into " + CLOSURE_TABLE_NAME + " (descendant_oid, ancestor_oid, val) " + "select :oid as descendant_oid, CL.ancestor_oid as ancestor_oid, CL.val as val " + "from " + CLOSURE_TABLE_NAME + " CL " + "where CL.descendant_oid = :parent"); addToClosureQuery.setString("oid", oid); addToClosureQuery.setParameter("parent", parent); int count = addToClosureQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("addEdges simplified: Added {} records to closure table ({} ms).", count, System.currentTimeMillis() - start); }// w ww .j av a 2 s . c o m session.flush(); session.clear(); }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void addIndependentEdgesInternal(List<Edge> edges, Context context, Session session) { checkForCycles(edges, session);/* w w w. ja v a 2s .c o m*/ String deltaTempTableName = computeDeltaTable(edges, context, session); try { int count; if (isMySQL() || isOracle() || isSQLServer()) { long startUpsert = System.currentTimeMillis(); String upsertQueryText; if (isMySQL()) { upsertQueryText = "insert into " + CLOSURE_TABLE_NAME + " (descendant_oid, ancestor_oid, val) " + "select descendant_oid, ancestor_oid, val from " + deltaTempTableName + " delta " + "on duplicate key update " + CLOSURE_TABLE_NAME + ".val = " + CLOSURE_TABLE_NAME + ".val + values(val)"; } else if (isSQLServer()) { // TODO try if this one (without prefixes in INSERT clause does not work for Oracle) upsertQueryText = "merge into " + CLOSURE_TABLE_NAME + " closure " + "using (select descendant_oid, ancestor_oid, val from " + deltaTempTableName + ") delta " + "on (closure.descendant_oid = delta.descendant_oid and closure.ancestor_oid = delta.ancestor_oid) " + "when matched then update set closure.val = closure.val + delta.val " + "when not matched then insert (descendant_oid, ancestor_oid, val) " + "values (delta.descendant_oid, delta.ancestor_oid, delta.val);"; } else { // Oracle upsertQueryText = "merge into " + CLOSURE_TABLE_NAME + " closure " + "using (select descendant_oid, ancestor_oid, val from " + deltaTempTableName + ") delta " + "on (closure.descendant_oid = delta.descendant_oid and closure.ancestor_oid = delta.ancestor_oid) " + "when matched then update set closure.val = closure.val + delta.val " + "when not matched then insert (closure.descendant_oid, closure.ancestor_oid, closure.val) " + "values (delta.descendant_oid, delta.ancestor_oid, delta.val)"; } Query upsertQuery = session.createSQLQuery(upsertQueryText); int countUpsert = upsertQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("Added/updated {} records to closure table ({} ms)", countUpsert, System.currentTimeMillis() - startUpsert); if (DUMP_TABLES) dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME); } else { // separate update and insert long startUpdate = System.currentTimeMillis(); String updateInClosureQueryText; if (isH2()) { updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set val = val + (select val from " + deltaTempTableName + " td " + "where td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid) " + "where (descendant_oid, ancestor_oid) in (select (descendant_oid, ancestor_oid) from " + deltaTempTableName + ")"; } else if (isPostgreSQL()) { updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set val = val + (select val from " + deltaTempTableName + " td " + "where td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid) " + "where (descendant_oid, ancestor_oid) in (select descendant_oid, ancestor_oid from " + deltaTempTableName + ")"; } else { throw new UnsupportedOperationException("implement other databases"); } Query updateInClosureQuery = session.createSQLQuery(updateInClosureQueryText); int countUpdate = updateInClosureQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("Updated {} records to closure table ({} ms)", countUpdate, System.currentTimeMillis() - startUpdate); if (DUMP_TABLES) dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME); long startAdd = System.currentTimeMillis(); String addQuery = "insert into " + CLOSURE_TABLE_NAME + " (descendant_oid, ancestor_oid, val) " + "select descendant_oid, ancestor_oid, val from " + deltaTempTableName + " delta "; if (countUpdate > 0) { if (isH2()) { addQuery += " where (descendant_oid, ancestor_oid) not in (select (descendant_oid, ancestor_oid) from " + CLOSURE_TABLE_NAME + ")"; } else if (isPostgreSQL()) { addQuery += " where not exists (select 1 from " + CLOSURE_TABLE_NAME + " cl where cl.descendant_oid=delta.descendant_oid and cl.ancestor_oid=delta.ancestor_oid)"; } else { throw new UnsupportedOperationException("implement other databases"); } } Query addToClosureQuery = session.createSQLQuery(addQuery); count = addToClosureQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("Added {} records to closure table ({} ms)", count, System.currentTimeMillis() - startAdd); if (DUMP_TABLES) dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME); } } finally { dropDeltaTableIfNecessary(session, deltaTempTableName); } }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void checkForCycles(List<Edge> edges, Session session) { String queryText = "select descendant_oid, ancestor_oid from " + CLOSURE_TABLE_NAME + " where " + getWhereClauseForCycleCheck(edges); Query query = session.createSQLQuery(queryText).addScalar("descendant_oid", StringType.INSTANCE) .addScalar("ancestor_oid", StringType.INSTANCE); long start = System.currentTimeMillis(); List list = query.list();//from www . j av a 2s. c o m LOGGER.trace("Cycles checked in {} ms, {} conflicts found", System.currentTimeMillis() - start, list.size()); if (!list.isEmpty()) { throw new IllegalArgumentException( "Modification couldn't be executed, because a cycle in org structure graph would be created. Cycle-creating edges being added: " + formatList(list)); } }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void dropDeltaTableIfNecessary(Session session, String deltaTempTableName) { // postgresql deletes the table automatically on commit // in H2 we delete the table after whole closure operation (after commit) if (isMySQL()) { Query dropQuery = session.createSQLQuery("drop temporary table " + deltaTempTableName); dropQuery.executeUpdate();//from www . ja v a 2s .com } else if (isSQLServer()) { // TODO drop temporary if using SQL Server Query dropQuery = session .createSQLQuery("if (exists (" + "select * " + "from sys.tables " + "where name like '" + deltaTempTableName + "%'))\n" + "drop table " + deltaTempTableName + ";"); dropQuery.executeUpdate(); } }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void handleDelete(String oid, Context context, Session session) { List<String> livingChildren = getChildren(oid, session); if (livingChildren.isEmpty()) { handleDeleteLeaf(oid, session);//from w w w. j a va 2 s . co m return; } // delete all edges "<child> -> OID" from the closure removeChildrenEdges(oid, livingChildren, context, session); if (LOGGER.isTraceEnabled()) LOGGER.trace("Deleted {} 'child' links.", livingChildren.size()); // delete all edges "OID -> <parent>" from the closure List<String> livingParents = retainExistingOids(getParents(oid, session), session); removeParentEdges(oid, livingParents, context, session); if (LOGGER.isTraceEnabled()) LOGGER.trace("Deleted {} 'parent' links.", livingParents.size()); // delete (OID, OID) record Query deleteSelfQuery = session.createSQLQuery( "delete from " + CLOSURE_TABLE_NAME + " " + "where descendant_oid=:oid and ancestor_oid=:oid"); deleteSelfQuery.setString("oid", oid); int count = deleteSelfQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("Removed {} self-record from closure table.", count); }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void handleDeleteLeaf(String oid, Session session) { Query removeFromClosureQuery = session .createSQLQuery("delete from " + CLOSURE_TABLE_NAME + " " + "where descendant_oid = :oid"); removeFromClosureQuery.setString("oid", oid); int count = removeFromClosureQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("DeleteLeaf: Removed {} records from closure table.", count); }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void removeIndependentEdgesInternal(List<Edge> edges, Context context, Session session) { String deltaTempTableName = computeDeltaTable(edges, context, session); try {/*from ww w .j a v a 2 s . c o m*/ int count; String deleteFromClosureQueryText, updateInClosureQueryText; if (isH2()) { // delete with join is not supported by H2 // and the "postgresql/oracle version" does not work for some reasons deleteFromClosureQueryText = "delete from " + CLOSURE_TABLE_NAME + " cl " + "where exists (" + "select 0 from " + deltaTempTableName + " delta " + "where cl.descendant_oid = delta.descendant_oid and cl.ancestor_oid = delta.ancestor_oid and cl.val = delta.val)"; updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set val = val - (select val from " + deltaTempTableName + " td " + "where td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid) " + "where (descendant_oid, ancestor_oid) in (select (descendant_oid, ancestor_oid) from " + deltaTempTableName + ")"; } else if (isPostgreSQL() || isOracle()) { deleteFromClosureQueryText = "delete from " + CLOSURE_TABLE_NAME + " " + "where (descendant_oid, ancestor_oid, val) in " + "(select descendant_oid, ancestor_oid, val from " + deltaTempTableName + ")"; updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set val = val - (select val from " + deltaTempTableName + " td " + "where td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid) " + "where (descendant_oid, ancestor_oid) in (select descendant_oid, ancestor_oid from " + deltaTempTableName + ")"; } else if (isSQLServer()) { // delete is the same as for MySQL deleteFromClosureQueryText = "delete " + CLOSURE_TABLE_NAME + " from " + CLOSURE_TABLE_NAME + " " + "inner join " + deltaTempTableName + " td on " + "td.descendant_oid = " + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid = " + CLOSURE_TABLE_NAME + ".ancestor_oid and " + "td.val = " + CLOSURE_TABLE_NAME + ".val"; // update is also done via inner join (as in MySQL), but using slightly different syntax updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set " + CLOSURE_TABLE_NAME + ".val = " + CLOSURE_TABLE_NAME + ".val - td.val " + "from " + CLOSURE_TABLE_NAME + " " + "inner join " + deltaTempTableName + " td " + "on td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and " + "td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid"; } else if (isMySQL()) { // http://stackoverflow.com/questions/652770/delete-with-join-in-mysql // TODO consider this for other databases as well deleteFromClosureQueryText = "delete " + CLOSURE_TABLE_NAME + " from " + CLOSURE_TABLE_NAME + " " + "inner join " + deltaTempTableName + " td on " + "td.descendant_oid = " + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid = " + CLOSURE_TABLE_NAME + ".ancestor_oid and " + "td.val = " + CLOSURE_TABLE_NAME + ".val"; // it is not possible to use temporary table twice in a query // TODO consider using this in postgresql as well... updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " join " + deltaTempTableName + " td " + "on td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid " + "set " + CLOSURE_TABLE_NAME + ".val = " + CLOSURE_TABLE_NAME + ".val - td.val"; } else { throw new UnsupportedOperationException("implement other databases"); } long startDelete = System.currentTimeMillis(); Query deleteFromClosureQuery = session.createSQLQuery(deleteFromClosureQueryText); count = deleteFromClosureQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("Deleted {} records from closure table in {} ms", count, System.currentTimeMillis() - startDelete); if (DUMP_TABLES) dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME); long startUpdate = System.currentTimeMillis(); Query updateInClosureQuery = session.createSQLQuery(updateInClosureQueryText); count = updateInClosureQuery.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("Updated {} records in closure table in {} ms", count, System.currentTimeMillis() - startUpdate); if (DUMP_TABLES) dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME); } finally { dropDeltaTableIfNecessary(session, deltaTempTableName); } }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private void lockClosureTable(Session session) { long start = System.currentTimeMillis(); LOGGER.trace("Locking closure table"); if (isH2()) { Query q = session.createSQLQuery("SELECT * FROM " + CLOSURE_TABLE_NAME + " WHERE 1=0 FOR UPDATE"); q.list();//from w w w . j a va 2 s . c o m } else if (isOracle()) { Query q = session.createSQLQuery("LOCK TABLE " + CLOSURE_TABLE_NAME + " IN EXCLUSIVE MODE"); q.executeUpdate(); } else if (isPostgreSQL()) { // currently not used Query q = session.createSQLQuery("LOCK TABLE " + CLOSURE_TABLE_NAME + " IN EXCLUSIVE MODE"); q.executeUpdate(); } else if (isSQLServer()) { Query q = session .createSQLQuery("SELECT count(*) FROM " + CLOSURE_TABLE_NAME + " WITH (TABLOCK, XLOCK)"); q.list(); } LOGGER.trace("...locked in {} ms", System.currentTimeMillis() - start); }
From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java
License:Apache License
private String computeDeltaTable(List<Edge> edges, Context context, Session session) { if (edges.isEmpty()) { throw new IllegalArgumentException("No edges to add/remove"); }//from w w w .j av a 2 s .co m String deltaTempTableName; if (context.temporaryTableName != null) { deltaTempTableName = context.temporaryTableName; // table was created on the beginning of trasaction } else if (isOracle()) { deltaTempTableName = TEMP_DELTA_TABLE_NAME_FOR_ORACLE; // table definition is global } else { deltaTempTableName = generateDeltaTempTableName(); // table will be created now } if (COUNT_CLOSURE_RECORDS && LOGGER.isTraceEnabled()) { Query q = session.createSQLQuery("select count(*) from " + CLOSURE_TABLE_NAME); List list = q.list(); LOGGER.trace("OrgClosure has {} rows", list.toString()); } long start; int count; String selectClause = "select t1.descendant_oid as descendant_oid, t2.ancestor_oid as ancestor_oid, " + "sum(t1.val*t2.val) as val " + "from " + CLOSURE_TABLE_NAME + " t1, " + CLOSURE_TABLE_NAME + " t2 " + "where " + getWhereClause(edges) + " " + "group by t1.descendant_oid, t2.ancestor_oid"; if (isSQLServer()) { // we create the table manually, because we want to have an index on it, and // with serializable transactions it is not possible to create index within the transaction (after inserting data) start = System.currentTimeMillis(); final String createTableSql = "create table " + deltaTempTableName + " (" + "descendant_oid NVARCHAR(36) COLLATE database_default, " + "ancestor_oid NVARCHAR(36) COLLATE database_default, " + "val INT, " + "PRIMARY KEY (descendant_oid, ancestor_oid))"; // Query createTableQuery = session.createSQLQuery(createTableSql); // createTableQuery.executeUpdate(); <--- this does not work because the temporary table gets deleted when the command terminates (preparedStatement issue - maybe something like this: https://support.microsoft.com/en-us/kb/280134 ?) session.doWork(new Work() { @Override public void execute(Connection connection) throws SQLException { connection.createStatement().execute(createTableSql); } }); if (LOGGER.isTraceEnabled()) LOGGER.trace("Empty delta table created in {} ms", System.currentTimeMillis() - start); Query insertQuery = session.createSQLQuery("insert into " + deltaTempTableName + " " + selectClause); start = System.currentTimeMillis(); count = insertQuery.executeUpdate(); } else { String createTablePrefix; if (isPostgreSQL()) { createTablePrefix = "create local temporary table " + deltaTempTableName + " on commit drop as "; } else if (isH2()) { // todo skip if this is first in this transaction Query q = session.createSQLQuery("delete from " + deltaTempTableName); int c = q.executeUpdate(); LOGGER.trace("Deleted {} rows from temporary table {}", c, deltaTempTableName); createTablePrefix = "insert into " + deltaTempTableName + " "; } else if (isMySQL()) { createTablePrefix = "create temporary table " + deltaTempTableName + " engine=memory as "; // engine=memory is questionable because of missing tansactionality (but the transactionality is needed in the main table, not the delta table...) } else if (isOracle()) { // todo skip if this is first in this transaction Query q = session.createSQLQuery("delete from " + deltaTempTableName); int c = q.executeUpdate(); LOGGER.trace("Deleted {} rows from temporary table {}", c, deltaTempTableName); createTablePrefix = "insert into " + deltaTempTableName + " "; } else { throw new UnsupportedOperationException("define other databases"); } Query query1 = session.createSQLQuery(createTablePrefix + selectClause); start = System.currentTimeMillis(); count = query1.executeUpdate(); } if (LOGGER.isTraceEnabled()) LOGGER.trace("Added {} records to temporary delta table {} ({} ms).", new Object[] { count, deltaTempTableName, System.currentTimeMillis() - start }); if (isPostgreSQL()) { start = System.currentTimeMillis(); Query qIndex = session.createSQLQuery("CREATE INDEX " + deltaTempTableName + "_idx " + " ON " + deltaTempTableName + " USING btree " + " (descendant_oid, ancestor_oid)"); qIndex.executeUpdate(); if (LOGGER.isTraceEnabled()) LOGGER.trace("Index created in {} ms", System.currentTimeMillis() - start); } // TODO index for MySQL !!! if (DUMP_TABLES) dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME); if (DUMP_TABLES) dumpOrgClosureTypeTable(session, deltaTempTableName); // TODO drop delta table in case of exception return deltaTempTableName; }