List of usage examples for java.sql Statement addBatch
void addBatch(String sql) throws SQLException;
Statement object. From source file:it.cnr.icar.eric.server.persistence.rdb.PostalAddressDAO.java
/** * @param registryObjects is a List of Organizations or Users * @throws RegistryException if the RegistryObject is not Organization or User, * or it has SQLException when inserting their PostalAddress */// ww w . ja v a 2 s .co m @SuppressWarnings("resource") public void insert(@SuppressWarnings("rawtypes") List registryObjects) throws RegistryException { Statement stmt = null; if (registryObjects.size() == 0) { return; } try { /* String sqlStr = "INSERT INTO " + getTableName() + " VALUES( " + "?, " + // city "?, " + // country "?, " + // postalCode "?, " + // state "?, " + // street "?, " + // streetNum "?)"; // Parent id PreparedStatement pstmt = context.getConnection().prepareStatement(sqlStr); */ stmt = context.getConnection().createStatement(); Iterator<?> rosIter = registryObjects.iterator(); while (rosIter.hasNext()) { Object ro = rosIter.next(); String parentId = null; PostalAddressType postalAddress = null; if (ro instanceof OrganizationType) { OrganizationType org = (OrganizationType) ro; postalAddress = org.getAddress().get(0); parentId = org.getId(); } else if (ro instanceof UserType) { UserType user = (UserType) ro; //TODO: Save extra addresses, if required postalAddress = user.getAddress().get(0); parentId = user.getId(); } else { throw new RegistryException( ServerResourceBundle.getInstance().getString("message.incorrectRegistryObject")); } /* stmt.setString(1, postalAddress.getCity()); stmt.setString(2, postalAddress.getCountry()); stmt.setString(3, postalAddress.getPostalCode()); stmt.setString(4, postalAddress.getStateOrProvince()); stmt.setString(5, postalAddress.getStreet()); stmt.setString(6, postalAddress.getStreetNumber()); stmt.setString(7, org.getId()); stmt.addBatch();*/ String city = postalAddress.getCity(); if (city != null) { city = "'" + city + "'"; } String country = postalAddress.getCountry(); if (country != null) { country = "'" + country + "'"; } String postalCode = postalAddress.getPostalCode(); if (postalCode != null) { postalCode = "'" + postalCode + "'"; } String state = postalAddress.getStateOrProvince(); if (state != null) { state = "'" + state + "'"; } String street = postalAddress.getStreet(); if (street != null) { street = "'" + street + "'"; } String streetNum = postalAddress.getStreetNumber(); if (streetNum != null) { streetNum = "'" + streetNum + "'"; } String str = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )"; log.trace("stmt = " + str); stmt.addBatch(str); } // end looping all Organizations if (registryObjects.size() > 0) { stmt.executeBatch(); } } catch (SQLException e) { RegistryException exception = new RegistryException(e); throw exception; } finally { closeStatement(stmt); } }
From source file:org.freebxml.omar.server.persistence.rdb.PostalAddressDAO.java
/** * @param registryObjects is a List of Organizations or Users * @throws RegistryException if the RegistryObject is not Organization or User, * or it has SQLException when inserting their PostalAddress */// w w w . j a va2 s .c om public void insert(List registryObjects) throws RegistryException { Statement stmt = null; if (registryObjects.size() == 0) { return; } try { /* String sqlStr = "INSERT INTO " + getTableName() + " VALUES( " + "?, " + // city "?, " + // country "?, " + // postalCode "?, " + // state "?, " + // street "?, " + // streetNum "?)"; // Parent id PreparedStatement pstmt = context.getConnection().prepareStatement(sqlStr); */ stmt = context.getConnection().createStatement(); Iterator rosIter = registryObjects.iterator(); while (rosIter.hasNext()) { Object ro = rosIter.next(); String parentId = null; PostalAddressType postalAddress = null; if (ro instanceof OrganizationType) { OrganizationType org = (OrganizationType) ro; postalAddress = (PostalAddressType) org.getAddress().get(0); parentId = org.getId(); } else if (ro instanceof UserType) { UserType user = (UserType) ro; //TODO: Save extra addresses, if required postalAddress = (PostalAddressType) user.getAddress().get(0); parentId = user.getId(); } else { throw new RegistryException( ServerResourceBundle.getInstance().getString("message.incorrectRegistryObject")); } /* stmt.setString(1, postalAddress.getCity()); stmt.setString(2, postalAddress.getCountry()); stmt.setString(3, postalAddress.getPostalCode()); stmt.setString(4, postalAddress.getStateOrProvince()); stmt.setString(5, postalAddress.getStreet()); stmt.setString(6, postalAddress.getStreetNumber()); stmt.setString(7, org.getId()); stmt.addBatch();*/ String city = postalAddress.getCity(); if (city != null) { city = "'" + city + "'"; } String country = postalAddress.getCountry(); if (country != null) { country = "'" + country + "'"; } String postalCode = postalAddress.getPostalCode(); if (postalCode != null) { postalCode = "'" + postalCode + "'"; } String state = postalAddress.getStateOrProvince(); if (state != null) { state = "'" + state + "'"; } String street = postalAddress.getStreet(); if (street != null) { street = "'" + street + "'"; } String streetNum = postalAddress.getStreetNumber(); if (streetNum != null) { streetNum = "'" + streetNum + "'"; } String str = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )"; log.trace("SQL = " + str); stmt.addBatch(str); } // end looping all Organizations if (registryObjects.size() > 0) { stmt.executeBatch(); } } catch (SQLException e) { RegistryException exception = new RegistryException(e); throw exception; } finally { closeStatement(stmt); } }
From source file:com.mysql.stresstool.RunnableQueryInsertDR.java
public void run() { BufferedReader d = null;/*from w w w.jav a 2 s. c om*/ Connection conn = null; try { if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) { conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test"); } else conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")); } catch (SQLException ex) { ex.printStackTrace(); } if (conn != null) { try { Statement stmt = null; // ResultSet rs = null; // ResultSet rs2 = null; conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); long execTime = 0; int pkStart = 0; int pkEnds = 0; int intDeleteInterval = 0; int intBlobInterval = 0; int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue(); ThreadInfo thInfo; long threadTimeStart = System.currentTimeMillis(); active = true; thInfo = new ThreadInfo(); thInfo.setId(this.ID); thInfo.setType("insert"); thInfo.setStatusActive(this.isActive()); StressTool.setInfo(this.ID, thInfo); boolean lazy = false; int lazyInterval = 0; populateLocalInfo(conn); for (int repeat = 0; repeat <= repeatNumber; repeat++) { String query = null; ArrayList insert1 = null; ArrayList insert2 = null; int pk = 0; if (repeat > 0 && lazyInterval < 500) { lazy = true; ++lazyInterval; } else { lazy = false; lazyInterval = 0; } intBlobInterval++; //IMPLEMENTING lazy Vector v = this.getTablesValues(lazy); insert1 = (ArrayList<String>) v.get(0); insert2 = (ArrayList<String>) v.get(1); // System.out.println(insert1); // System.out.println(insert2); // pk = ((Integer) v.get(2)).intValue(); int[] iLine = { 0, 0 }; // pkStart = StressTool.getNumberFromRandom(2147483647).intValue(); // pkEnds = StressTool.getNumberFromRandom(2147483647).intValue(); try { long timeStart = System.currentTimeMillis(); if (this.ignoreBinlog) stmt.execute("SET sql_log_bin=0"); stmt.execute("SET GLOBAL max_allowed_packet=1073741824"); if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE")) stmt.execute("BEGIN"); else stmt.execute("COMMIT"); // stmt.execute("SET TRANSACTION NAME 'TEST'"); { Iterator<String> it = insert1.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } } if (!this.doSimplePk) { if (intBlobInterval > intBlobIntervalLimit) { Iterator<String> it = insert2.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } intBlobInterval = 0; } } if (debug) { System.out.println("Thread " + thInfo.getId() + " Executing loop " + thInfo.getExecutedLoops() + " QUERY1==" + insert1); System.out.println("Thread " + thInfo.getId() + " Executing loop " + thInfo.getExecutedLoops() + " QUERY2==" + insert2); } iLine = stmt.executeBatch(); stmt.clearBatch(); // System.out.println("Query1 = " + insert1); // System.out.println("Query2 = " + insert2); // stmt.execute("START TRANSACTION"); // stmt.execute(insert1); // iLine = stmt.executeBatch(); // conn.commit(); long timeEnds = System.currentTimeMillis(); execTime = (timeEnds - timeStart); } catch (Exception sqle) { conn.rollback(); System.out.println("FAILED QUERY1==" + insert1); System.out.println("FAILED QUERY2==" + insert2); sqle.printStackTrace(); System.exit(1); //conn.close(); //this.setJdbcUrl(jdbcUrl); //System.out.println("Query Insert TH RE-INIZIALIZING"); } finally { // conn.commit(); stmt.execute("COMMIT"); // intDeleteInterval++; if (doLog) { System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " " + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) =" + execTime + " Running = " + repeat + " of " + repeatNumber + " to go =" + (repeatNumber - repeat) + " Using Lazy=" + lazy); } } thInfo.setExecutedLoops(repeat); if (sleepFor > 0 || this.getSleepWrite() > 0) { if (this.getSleepWrite() > 0) { Thread.sleep(getSleepWrite()); } else Thread.sleep(sleepFor); } } long threadTimeEnd = System.currentTimeMillis(); this.executionTime = (threadTimeEnd - threadTimeStart); // this.setExecutionTime(executionTime); active = false; // System.out.println("Query Insert TH = " + this.getID() + " COMPLETED! TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000)); thInfo.setExecutionTime(executionTime); thInfo.setStatusActive(false); StressTool.setInfo(this.ID, thInfo); return; } catch (Exception ex) { ex.printStackTrace(); try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
From source file:com.mysql.stresstool.RunnableQueryInsertDR.java
@Override public boolean createSchema(StressTool sTool) { // Custom schema creation this is the default for the stresstool but can be anything String DropTables1 = "Drop table IF EXISTS tbtest"; String DropTables2 = "Drop table IF EXISTS tbtest_child"; String TruncateTables1 = "Truncate table tbtest"; String TruncateTables2 = "Truncate table tbtest_child"; Connection conn = null;// w w w .j ava 2s. c o m Statement stmt = null; try { if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) { conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test"); } else conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl")); conn.setAutoCommit(false); stmt = conn.createStatement(); StringBuffer sb = new StringBuffer(); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { sb.append("CREATE TABLE IF NOT EXISTS tbtest" + iTable); // if (this.isUseAutoIncrement()){ // sb.append("`autoInc` bigint(11) AUTO_INCREMENT NOT NULL,"); // } // sb.append(" `a` int(11) NOT NULL,"); // sb.append(" `uuid` char(36) NOT NULL,"); // sb.append(" `b` varchar(100) NOT NULL,"); // sb.append(" `c` char(200) NOT NULL,"); // sb.append(" `counter` bigint(20) NULL, "); // sb.append(" `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"); // sb.append(" `partitionid` int NOT NULL DEFAULT 0,"); // sb.append(" `date` DATE NOT NULL,"); // sb.append(" `strrecordtype` char(3) NULL"); // if (this.isUseAutoIncrement()){ // if(this.partitionType.equals("range")){ // sb.append(", PRIMARY KEY (`autoInc`,`date`), INDEX `IDX_a` (a), INDEX `IDX_uuid` (uuid) "); // } // else{ // sb.append(", PRIMARY KEY (`autoInc`,`partitionid`), INDEX `IDX_a` (a), INDEX `IDX_uuid` (uuid) "); // } // } // else{ // if(!this.doSimplePk) // if(this.partitionType.equals("range")){ // sb.append(", PRIMARY KEY (`uuid`,`date`), INDEX `IDX_a` (a) "); // } // else{ // sb.append(", PRIMARY KEY (`uuid`,`partitionid`), INDEX `IDX_a` (a) "); // } // else{ // if(this.partitionType.equals("range")){ // sb.append(", PRIMARY KEY (`a`,`date`), INDEX `IDX_uuid` (uuid) "); // } // else{ // sb.append(", PRIMARY KEY (`a`,`partitionid`), INDEX `IDX_uuid` (uuid) "); // } // } // } sb.append( "(`emp_no` int(4) unsigned AUTO_INCREMENT NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL," + "`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL," + "`city_id` int(4) DEFAULT NULL,`CityName` varchar(150) DEFAULT NULL,`CountryCode` char(3) DEFAULT NULL," + "`UUID` char(36) DEFAULT NULL, PRIMARY KEY (`emp_no`)) "); sb.append(" ENGINE=" + sTool.tableEngine); if (!sb.toString().equals("")) stmt.addBatch(sb.toString()); sb.delete(0, sb.length()); } String tbts1 = sb.toString(); sb = new StringBuffer(); for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { sb.append("CREATE TABLE IF NOT EXISTS tbtest_child" + iTable); sb.append("(`a` int(11) NOT NULL,"); sb.append("`bb` int(11) AUTO_INCREMENT NOT NULL,"); sb.append(" `date` DATE NOT NULL,"); sb.append(" `partitionid` int NOT NULL DEFAULT 0,"); if (operationShort) sb.append(" `stroperation` VARCHAR(254) NULL,"); else sb.append(" `stroperation` TEXT(41845) NULL,"); sb.append(" `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"); sb.append(", PRIMARY KEY (`a`,`bb`), UNIQUE(`bb`)"); sb.append(") ENGINE=" + sTool.tableEngine); if (!sb.toString().equals("")) stmt.addBatch(sb.toString()); sb.delete(0, sb.length()); } String tbts2 = sb.toString(); System.out.println(tbts1); if (!doSimplePk) System.out.println(tbts2); if (sTool.droptable) { System.out.println( "****============================================================================*******"); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { System.out.println( "**** Please wait DROP table tbtest" + iTable + " it could take a LOT of time *******"); stmt.execute(DropTables1 + iTable); } if (!doSimplePk) { for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { System.out.println("**** Please wait DROP table tbtest_child" + iTable + " it could take a LOT of time *******"); stmt.execute(DropTables2 + iTable); } } stmt.execute("COMMIT"); System.out.println("**** DROP finished *******"); System.out.println( "****============================================================================*******"); } if (sTool.createtable) stmt.executeBatch(); if (sTool.truncate) { System.out.println( "****============================================================================*******"); for (int iTable = 1; iTable <= this.getNumberOfprimaryTables(); iTable++) { System.out.println("**** Please wait TRUNCATE table tbtest" + iTable + " it could take a LOT of time *******"); stmt.execute(TruncateTables1 + iTable); } if (!doSimplePk) { for (int iTable = 1; iTable <= this.getNumberOfSecondaryTables(); iTable++) { System.out.println("**** Please wait TRUNCATE table tbtest_child" + iTable + " it could take a LOT of time *******"); stmt.execute(TruncateTables2 + iTable); } } System.out.println("**** TRUNCATE finish *******"); System.out.println( "****============================================================================*******"); } } catch (Exception ex) { ex.printStackTrace( ); return false; } finally { try { conn.close(); return true; } catch (SQLException ex1) { ex1.printStackTrace(); return false; } } }
From source file:com.flexive.core.storage.genericSQL.GenericTreeStorage.java
/** * {@inheritDoc}/*from w w w . j a v a 2 s . com*/ */ @Override public void removeNode(Connection con, FxTreeMode mode, ContentEngine ce, long nodeId, boolean removeChildren) throws FxApplicationException { if (mode == FxTreeMode.Live) removeChildren = true; //always delete child nodes in live mode Statement stmt = null; if (nodeId == FxTreeNode.ROOT_NODE) throw new FxNoAccessException("ex.tree.delete.root"); FxTreeNodeInfo nodeInfo = getTreeNodeInfo(con, mode, nodeId); ScriptingEngine scripting = EJBLookup.getScriptingEngine(); final List<Long> scriptBeforeIds = scripting.getByScriptEvent(FxScriptEvent.BeforeTreeNodeRemoved); final List<Long> scriptAfterIds = scripting.getByScriptEvent(FxScriptEvent.AfterTreeNodeRemoved); //warning: removedNodes will only be available if script mappings for event AfterTreeNodeRemoved exist! List<FxTreeNode> removedNodes = scriptAfterIds.size() > 0 ? new ArrayList<FxTreeNode>(100) : null; final String TRUE = StorageManager.getBooleanTrueExpression(); try { stmt = con.createStatement(); if (StorageManager.isDisableIntegrityTransactional()) { stmt.execute(StorageManager.getReferentialIntegrityChecksStatement(false)); } List<FxPK> references = new ArrayList<FxPK>(50); UserTicket ticket = FxContext.getUserTicket(); // lock all affected rows final List<Long> removeNodeIds = selectAllChildNodeIds(con, mode, nodeInfo.getLeft(), nodeInfo.getRight(), true); acquireLocksForUpdate(con, mode, Iterables.concat(removeNodeIds, Arrays.asList(nodeInfo.getParentId()))); final Map<FxPK, FxContentSecurityInfo> securityInfos = Maps .newHashMapWithExpectedSize(removeNodeIds.size()); if (removeChildren) { //FX-102: edit permission checks on references ResultSet rs = stmt.executeQuery("SELECT DISTINCT REF FROM " + getTable(mode) + " WHERE " + " LFT>=" + nodeInfo.getLeft() + " AND RGT<=" + nodeInfo.getRight() + " "); while (rs != null && rs.next()) { try { if (ce != null) { final FxPK pk = new FxPK(rs.getLong(1)); final FxContentSecurityInfo info = ce.getContentSecurityInfo(pk); FxPermissionUtils.checkPermission(ticket, ACLPermission.EDIT, info, true); securityInfos.put(pk, info); } references.add(new FxPK(rs.getLong(1))); } catch (FxLoadException e) { //ignore, might have been removed meanwhile } } // call BeforeTreeNodeRemoved scripts if (scriptBeforeIds.size() > 0 || scriptAfterIds.size() > 0) { final FxScriptBinding binding = new FxScriptBinding(); for (long removedId : removeNodeIds) { final FxTreeNode n = getNode(con, mode, removedId); if (removedNodes != null) removedNodes.add(n); for (long scriptId : scriptBeforeIds) { binding.setVariable("node", n); scripting.runScript(scriptId, binding); } } } for (List<Long> removeIds : Iterables.partition(removeNodeIds, SQL_IN_PARTSIZE)) { stmt.addBatch("DELETE FROM " + getTable(mode) + " WHERE id IN (" + StringUtils.join(removeIds, ',') + ")"); } } else { //FX-102: edit permission checks on references try { if (ce != null) { final FxContentSecurityInfo info = ce.getContentSecurityInfo(nodeInfo.getReference()); FxPermissionUtils.checkPermission(FxContext.getUserTicket(), ACLPermission.EDIT, info, true); securityInfos.put(nodeInfo.getReference(), info); } references.add(nodeInfo.getReference()); } catch (FxLoadException e) { //ignore, might have been removed meanwhile } stmt.addBatch("UPDATE " + getTable(mode) + " SET PARENT=" + nodeInfo.getParentId() + " WHERE PARENT=" + nodeId); for (List<Long> part : Iterables.partition(removeNodeIds, SQL_IN_PARTSIZE)) { stmt.addBatch("UPDATE " + getTable(mode) + " SET DEPTH=DEPTH-1,DIRTY=" + StorageManager.getBooleanExpression(mode != FxTreeMode.Live) + " WHERE id IN (" + StringUtils.join(part, ',') + ") AND DEPTH>0"); } stmt.addBatch("DELETE FROM " + getTable(mode) + " WHERE ID=" + nodeId); } // Update the childcount of the parents if (removeChildren) { stmt.addBatch("UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT-1 WHERE ID=" + nodeInfo.getParentId()); } else { stmt.addBatch("UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT+" + (nodeInfo.getDirectChildCount() - 1) + " WHERE ID=" + nodeInfo.getParentId()); } // Set the dirty flag for the parent if needed if (mode != FxTreeMode.Live) { stmt.addBatch( "UPDATE " + getTable(mode) + " SET DIRTY=" + TRUE + " WHERE ID=" + nodeInfo.getParentId()); } if (mode == FxTreeMode.Live && exists(con, FxTreeMode.Edit, nodeId)) { //check if a node with the same id that has been removed in the live tree exists in the edit tree, //the node and all its children will be flagged as dirty in the edit tree FxTreeNodeInfo editNode = getTreeNodeInfo(con, FxTreeMode.Edit, nodeId); List<Long> editNodeIds = selectAllChildNodeIds(con, FxTreeMode.Edit, editNode.getLeft(), editNode.getRight(), true); acquireLocksForUpdate(con, FxTreeMode.Edit, editNodeIds); for (List<Long> part : Iterables.partition(editNodeIds, SQL_IN_PARTSIZE)) { stmt.addBatch("UPDATE " + getTable(FxTreeMode.Edit) + " SET DIRTY=" + TRUE + " WHERE ID IN (" + StringUtils.join(part, ',') + ")"); } } stmt.executeBatch(); if (ce != null) { //if the referenced content is a folder, remove it final Set<Long> folderTypeIds = Sets.newHashSet(FxSharedUtils.getSelectableObjectIdList( CacheAdmin.getEnvironment().getType(FxType.FOLDER).getDerivedTypes(true, true))); for (FxPK ref : references) { FxContentSecurityInfo si = securityInfos.get(ref); if (si == null) { si = ce.getContentSecurityInfo(ref); } if (folderTypeIds.contains(si.getTypeId())) { final int contentCount = ce.getReferencedContentCount(si.getPk()); if (contentCount == 0) { ce.remove(ref); } } } } afterNodeRemoved(con, nodeInfo, removeChildren); if (removedNodes != null) { final FxScriptBinding binding = new FxScriptBinding(); for (long scriptId : scriptAfterIds) { for (FxTreeNode n : removedNodes) { binding.setVariable("node", n); scripting.runScript(scriptId, binding); } } } } catch (SQLException exc) { String next = ""; if (exc.getNextException() != null) next = " next:" + exc.getNextException().getMessage(); throw new FxRemoveException(LOG, exc, "ex.tree.delete.failed", nodeId, exc.getMessage() + next); } finally { try { if (stmt != null) { if (StorageManager.isDisableIntegrityTransactional()) { try { stmt.execute(StorageManager.getReferentialIntegrityChecksStatement(true)); } catch (SQLException e) { LOG.error(e); } } stmt.close(); } } catch (Exception exc) { //ignore } } }
From source file:gov.nih.nci.security.dao.AuthorizationDAOImpl.java
public void maintainInstanceTables(String instanceLevelMappingElementId) throws CSObjectNotFoundException, CSDataAccessException { // Get Mapping Table Entries for Instance Level Security performance. InstanceLevelMappingElement mappingElement = new InstanceLevelMappingElement(); if (!StringUtilities.isBlank(instanceLevelMappingElementId)) { mappingElement.setMappingId(new Long(instanceLevelMappingElementId)); }//from w w w .j av a 2s . co m List<InstanceLevelMappingElement> mappingElements = getObjects( new InstanceLevelMappingElementSearchCriteria(mappingElement)); if (mappingElements == null || mappingElements.size() == 0) { // No Mapping Elements. So no tables to maintain return; } Statement statement = null; Transaction transaction = null; Session session = null; Connection connection = null; try { session = HibernateSessionFactoryHelper.getAuditSession(sf); transaction = session.beginTransaction(); connection = session.connection(); connection.setAutoCommit(false); statement = connection.createStatement(); //create view CSM_VW_ROLE_PRIV statement.addBatch(" create or replace view csm_vw_role_priv" + " as" + " select crp.role_id, substr(cp.privilege_name, 1, 30) privilege_name, cr.application_id" + " from csm_role_privilege crp, csm_privilege cp, csm_role cr" + " where crp.role_id = cr.role_id and crp.privilege_id = cp.privilege_id" + " and cr.active_flag = 1"); Iterator mappingElementsIterator = mappingElements.iterator(); while (mappingElementsIterator.hasNext()) { InstanceLevelMappingElement instanceLevelMappingEntry = (InstanceLevelMappingElement) mappingElementsIterator .next(); if (instanceLevelMappingEntry != null) { if (instanceLevelMappingEntry.getActiveFlag() == 0) { // Not active, so ignore this Object + Attribute from table/view maintain logic. continue; } if (StringUtilities.isAlphaNumeric(instanceLevelMappingEntry.getAttributeName()) && StringUtilities.isAlphaNumeric(instanceLevelMappingEntry.getObjectName())) { //Mapping Entry is valid. } else { // Mapping Entry is invalid. //ignore this mapping element. continue; } } else { //Mapping Entry is invalid. continue; //throw new Exception("Invalid Instance Level Mapping Element. Instance Level Security breach is possible."); } //mark this mappging entry is maintained. statement.addBatch("update csm_mapping set maintained_flag = '1' " + "where mapping_id = " + instanceLevelMappingEntry.getMappingId()); //get the Table Name and View Name for each object. String peiTableName, tableNameUser, viewNameUser, tableNameGroup, viewNameGroup = null; if (StringUtilities.isBlank(instanceLevelMappingEntry.getTableName())) { peiTableName = "csm_pei_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName(); } else { peiTableName = instanceLevelMappingEntry.getTableName(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getTableNameForUser())) { tableNameUser = "csm_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_user"; } else { tableNameUser = instanceLevelMappingEntry.getTableNameForUser(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getViewNameForUser())) { viewNameUser = "csm_vw_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_user"; } else { viewNameUser = instanceLevelMappingEntry.getViewNameForUser(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getTableNameForGroup())) { tableNameGroup = "csm_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_group"; } else { tableNameGroup = instanceLevelMappingEntry.getTableNameForGroup(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getViewNameForGroup())) { viewNameGroup = "csm_vw_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_group"; } else { viewNameGroup = instanceLevelMappingEntry.getViewNameForGroup(); } /* Optional: Add Additional checks regarding Table and View record count. * At the time of delete, if the MINUS is close to or greater than 50% of the records of the Table, * then truncate table instead of deleting using delete statement. * * Note: No buffering until real tests warrant buffering. */ byte activeFlag = instanceLevelMappingEntry.getActiveFlag(); if (activeFlag == 1) { //create pei table statement.addBatch("create table if not exists " + peiTableName + " (" + " application_id bigint(20) not null," + " attribute_value bigint(20) not null," + " protection_element_id bigint(20) not null," + " primary key (protection_element_id)," + " unique key uq_mp_obj_name_attri_val_app_id (protection_element_id,attribute_value,application_id)," + " key idx_application_id (application_id)," + " constraint fk_pe_application1 foreign key fk_pe_application1 (application_id) references csm_application (application_id) on delete cascade on update cascade" + " );"); //create tableNameForUser statement.addBatch("create table if not exists " + tableNameUser + " (" + " user_id bigint(20) not null," + " login_name varchar(200) not null," + " privilege_name varchar(30) not null," + " application_id bigint(20) not null," + " attribute_value bigint(20) not null," + " unique key uq_userid_aid_pri_atr (user_id,application_id, privilege_name,attribute_value)," + " unique key uq_lgnnam_aid_pri_atr (login_name,application_id, privilege_name,attribute_value)," + " key idx_user_id (user_id)," + " key idx_login_name (login_name)," + " key idx_application_id (application_id)," + " key idx_privilege_name (privilege_name)," + " key idx_attribute_value(attribute_value)" + " );"); //create tableNameForGroup statement.addBatch("create table if not exists " + tableNameGroup + " (" + " group_id bigint(20) not null," + " group_name varchar(100) not null," + " privilege_name varchar(30) not null," + " application_id bigint(20) not null," + " attribute_value bigint(20) not null," + " unique key uq_grpid_aid_pri_atr (group_id,application_id, privilege_name,attribute_value)," + " unique key grpnm_aid_pri_atr (group_name,application_id, privilege_name,attribute_value)," + " key idx_group_id (group_id)," + " key idx_group_name (group_name)," + " key idx_application_id (application_id)," + " key idx_privilege_name (privilege_name)," + " key idx_attribute_value(attribute_value)" + " );"); statement.executeBatch(); //create viewNameForUser //Note: the User level view does not consider 'Owner' users in this View/ Filter Query. statement.addBatch("create or replace view " + viewNameUser + "_temp" + " as select pr.user_id,u.login_name,pr.role_id,pe.application_id,pe.attribute_value" + " from csm_pg_pe cp, " + peiTableName + " pe, csm_user_group_role_pg pr, csm_user u" + " where cp.protection_element_id = pe.protection_element_id and cp.protection_group_id = pr.protection_group_id and pr.user_id = u.user_id;"); statement.executeBatch(); statement.addBatch("create or replace view " + viewNameUser + " as" + " select pe.user_id, pe.login_name ,pr.privilege_name,pe.application_id,pe.attribute_value" + " from " + viewNameUser + "_temp pe,csm_vw_role_priv pr" + " where pe.role_id = pr.role_id"); //create viewNameForGroup statement.addBatch("create or replace view " + viewNameGroup + "_temp" + " as" + " select pr.group_id, g.group_name,pr.role_id, pe.application_id, pe.attribute_value" + " from csm_pg_pe cp, " + peiTableName + " pe, csm_user_group_role_pg pr, csm_group g" + " where cp.protection_element_id = pe.protection_element_id" + " and cp.protection_group_id = pr.protection_group_id and pr.group_id = g.group_id"); statement.executeBatch(); statement.addBatch("create or replace view " + viewNameGroup + " as" + " select pe.group_id, pe.group_name, pr.privilege_name, pe.application_id, pe.attribute_value" + " from " + viewNameGroup + "_temp pe, csm_vw_role_priv pr" + " where pe.role_id = pr.role_id"); } } statement.executeBatch(); transaction.commit(); statement.close(); } catch (SQLException e1) { if (transaction != null) { try { transaction.rollback(); } catch (Exception ex3) { } } throw new CSDataAccessException("Unable to maintain tables/views for instance level security."); } catch (Exception e) { if (transaction != null) { try { transaction.rollback(); } catch (Exception ex3) { } } throw new CSDataAccessException("Unable to maintain tables/views for instance level security."); } finally { try { connection.close(); } catch (Exception ex2) { } try { session.close(); } catch (Exception ex2) { if (log.isDebugEnabled()) log.debug("Authorization|||maintainInstanceTables|Failure|Error in Closing Session |" + ex2.getMessage()); } } }
From source file:gov.nih.nci.security.dao.AuthorizationDAOImpl.java
public void refreshInstanceTables(boolean instanceLevelSecurityForUser) throws CSObjectNotFoundException, CSDataAccessException { //Get Mapping Table Entries for Instance Level Security performance. InstanceLevelMappingElement mappingElement = new InstanceLevelMappingElement(); List<InstanceLevelMappingElement> mappingElements = getObjects( new InstanceLevelMappingElementSearchCriteria(mappingElement)); if (mappingElements == null || mappingElements.size() == 0) { //throw new RuntimeException ("Instance Level Mappging Elements does not exist"); throw new CSObjectNotFoundException("Instance Level Mapping Elements do not exist."); }//from w ww. j av a2s. c o m Statement statement = null; Transaction transaction = null; Session session = null; Connection connection = null; try { session = HibernateSessionFactoryHelper.getAuditSession(sf); transaction = session.beginTransaction(); //transaction.setTimeout(10000); connection = session.connection(); connection.setAutoCommit(false); statement = connection.createStatement(); Iterator mappingElementsIterator = mappingElements.iterator(); while (mappingElementsIterator.hasNext()) { InstanceLevelMappingElement instanceLevelMappingEntry = (InstanceLevelMappingElement) mappingElementsIterator .next(); if (instanceLevelMappingEntry != null) { if (instanceLevelMappingEntry.getActiveFlag() == 0) { // Not active, so ignore this Object + Attribute from refresh logic. continue; } if (!StringUtilities.isAlphaNumeric(instanceLevelMappingEntry.getAttributeName()) || !StringUtilities.isAlphaNumeric(instanceLevelMappingEntry.getObjectName())) { //Mapping Entry is invalid. throw new CSObjectNotFoundException( "Invalid Instance Level Mapping Element. Instance Level Security breach is possible."); } } else { //Mapping Entry is invalid. continue; //throw new Exception("Invalid Instance Level Mapping Element. Instance Level Security breach is possible."); } //get the Table Name and View Name for each object. String applicationID = this.application.getApplicationId().toString(); String peiTableName, tableNameUser, viewNameUser, tableNameGroup, viewNameGroup = null; String peiObjectId = null; if (StringUtilities.isBlank(instanceLevelMappingEntry.getObjectPackageName())) { peiObjectId = instanceLevelMappingEntry.getObjectName().trim(); } else { peiObjectId = instanceLevelMappingEntry.getObjectPackageName().trim() + "." + instanceLevelMappingEntry.getObjectName().trim(); } String peiAttribute = instanceLevelMappingEntry.getAttributeName().trim(); if (StringUtilities.isBlank(instanceLevelMappingEntry.getTableName())) { peiTableName = "CSM_PEI_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName(); } else { peiTableName = instanceLevelMappingEntry.getTableName(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getTableNameForUser())) { tableNameUser = "CSM_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_USER"; } else { tableNameUser = instanceLevelMappingEntry.getTableNameForUser(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getViewNameForUser())) { viewNameUser = "CSM_VW_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_USER"; } else { viewNameUser = instanceLevelMappingEntry.getViewNameForUser(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getTableNameForGroup())) { tableNameGroup = "CSM_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_GROUP"; } else { tableNameGroup = instanceLevelMappingEntry.getTableNameForGroup(); } if (StringUtilities.isBlank(instanceLevelMappingEntry.getViewNameForGroup())) { viewNameGroup = "CSM_VW_" + instanceLevelMappingEntry.getObjectName() + "_" + instanceLevelMappingEntry.getAttributeName() + "_GROUP"; } else { viewNameGroup = instanceLevelMappingEntry.getViewNameForGroup(); } /* Optional: Add Additional checks regarding Table and View record count. * At the time of delete, if the MINUS is close to or greater than 50% of the records of the Table, * then truncate table instead of deleting using delete statement. * * Note: No buffering until real tests warrant buffering. */ byte activeFlag = instanceLevelMappingEntry.getActiveFlag(); if (activeFlag == 1) { //refresh PEI Table statement.addBatch("alter table " + peiTableName + " disable keys"); statement.addBatch("truncate " + peiTableName); statement.addBatch("delete from " + peiTableName + " where application_id = " + applicationID + " and protection_element_id " + " not in (" + " select pe.protection_element_id from csm_protection_element pe" + " where pe.object_id = '" + peiObjectId + "' and pe.attribute = '" + peiAttribute + "' and pe.application_id = " + applicationID + " )"); statement.executeBatch(); statement.addBatch("insert into " + peiTableName + " (protection_element_id, attribute_value, application_id) " + " select protection_element_id, attribute_value,application_id from csm_protection_element pe" + " where pe.object_id = '" + peiObjectId + "' and pe.attribute = '" + peiAttribute + "' and pe.application_id = " + applicationID + " and pe.attribute_value is not null "); //"and protection_element_id not in (select protection_element_id from "+peiTableName+" )"); statement.addBatch("alter table " + peiTableName + " enable keys"); statement.executeBatch(); if (instanceLevelSecurityForUser) { statement.addBatch("alter table " + tableNameUser + " disable keys"); statement.addBatch("truncate " + tableNameUser); /*statement.addBatch("delete from "+tableNameUser+"" + " where (user_id,privilege_name,attribute_value,application_id) " + " not in (" + " select user_id,privilege_name,attribute_value,application_id from "+viewNameUser+ ");");*/ statement.executeBatch(); statement.addBatch("insert into " + tableNameUser + " (user_id,login_name,privilege_name,attribute_value,application_id) " + " select distinct user_id,login_name,privilege_name,attribute_value,application_id from " + viewNameUser + " " + " where attribute_value is not null "); /*and (user_id,privilege_name,attribute_value,application_id) " + " not in ( select user_id,privilege_name,attribute_value,application_id from "+tableNameUser+" )");*/ statement.addBatch("alter table " + tableNameUser + " enable keys"); statement.executeBatch(); } else { statement.addBatch("alter table " + tableNameGroup + " disable keys"); statement.addBatch("truncate " + tableNameGroup); /*statement.addBatch("delete from "+tableNameGroup+"" + " where (group_id,privilege_name,attribute_value,application_id) " + " not in (" + " select group_id,privilege_name,attribute_value,application_id from "+viewNameGroup+ ")");*/ statement.addBatch("insert into " + tableNameGroup + " (group_id,group_name,privilege_name,attribute_value,application_id) " + " select distinct group_id,group_name,privilege_name,attribute_value,application_id from " + viewNameGroup + " " + " where attribute_value is not null"); /*(group_ID,privilege_name,attribute_value,application_id) " + " not in (" + " select group_id,privilege_name,attribute_value,application_id from "+tableNameGroup+" )");*/ statement.addBatch("alter table " + tableNameGroup + " enable keys"); statement.executeBatch(); } } } transaction.commit(); statement.close(); } catch (CSObjectNotFoundException e1) { if (transaction != null) { try { transaction.rollback(); } catch (Exception ex3) { } } throw new CSObjectNotFoundException(e1.getMessage()); } catch (SQLException e1) { if (transaction != null) { try { transaction.rollback(); } catch (Exception ex3) { } } throw new CSDataAccessException("Unable to perform data refresh for instance level security."); } catch (Exception e) { if (transaction != null) { try { transaction.rollback(); } catch (Exception ex3) { } } throw new CSDataAccessException("Unable to perform data refresh for instance level security."); } finally { try { connection.close(); } catch (Exception ex2) { } try { session.close(); } catch (Exception ex2) { if (log.isDebugEnabled()) log.debug("Authorization|||refreshInstanceTables|Failure|Error in Closing Session |" + ex2.getMessage()); } } }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean revokeRoleFromUser(String userName, List<String> roles) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;//from w w w . ja v a 2s.c o m ; Statement ps = null; boolean success = false; userName = userName.toLowerCase(); List<String> roleLowerCase = new ArrayList<String>(roles.size()); for (String role : roles) { roleLowerCase.add(role.toLowerCase()); } roles = roleLowerCase; try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("revoke role to user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("revoke role to user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.createStatement(); String sql = "select user_name from tdwuser where user_name='" + userName + "'"; boolean isPrivFind = false; ResultSet userSet = ps.executeQuery(sql); while (userSet.next()) { isPrivFind = true; break; } userSet.close(); if (!isPrivFind) { throw new NoSuchObjectException("can not find user:" + userName); } for (String role : roles) { sql = "select role_name from tdwrole where role_name='" + role + "'"; boolean isRoleFind = false; ResultSet roleTempSet = ps.executeQuery(sql); while (roleTempSet.next()) { isRoleFind = true; } roleTempSet.close(); if (!isRoleFind) { throw new InvalidObjectException("Role does not exist: " + role); } } for (String role : roles) { ps.addBatch( "delete from tdwuserrole where user_name='" + userName + "' and role_name='" + role + "'"); } ps.executeBatch(); ps.clearBatch(); con.commit(); success = true; } catch (SQLException ex) { LOG.error("revoke role from user error , user=" + userName + ", msg=" + ex.getMessage()); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean grantRoleToRole(String roleName, List<String> roles) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;// www. ja v a2s.com ; Statement ps = null; boolean success = false; roleName = roleName.toLowerCase(); List<String> roleLowerCase = new ArrayList<String>(roles.size()); for (String role : roles) { roleLowerCase.add(role.toLowerCase()); } try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("grant role error, role=" + roleName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("grant role error, role=" + roleName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.createStatement(); String sql = "select role_name from tdwrole where role_name='" + roleName.toLowerCase() + "'"; boolean isRoleFind = false; ResultSet roleSet = ps.executeQuery(sql); while (roleSet.next()) { isRoleFind = true; break; } roleSet.close(); if (!isRoleFind) { throw new NoSuchObjectException("can not find role:" + roleName); } Set<String> sonRoleNameSet = new HashSet<String>(); sql = "select sonrole_name from tdwsonrole where role_name='" + roleName.toLowerCase() + "'"; ResultSet sonroleSet = ps.executeQuery(sql); while (sonroleSet.next()) { sonRoleNameSet.add(sonroleSet.getString(1)); } sonroleSet.close(); List<String> needAddRoles = new ArrayList<String>(); for (String role : roles) { if (!roleName.equalsIgnoreCase(role) && !sonRoleNameSet.contains(role)) { needAddRoles.add(role); } } if (!needAddRoles.isEmpty()) { for (String role : needAddRoles) { ps.addBatch("insert into tdwsonrole(role_name, sonrole_name) values('" + roleName.toLowerCase() + "', '" + role.toLowerCase() + "')"); } ps.executeBatch(); } con.commit(); success = true; } catch (SQLException sqlex) { sqlex.printStackTrace(); LOG.error("grant role error, role=" + roleName + ", msg=" + sqlex.getMessage()); throw new MetaException(sqlex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }