List of usage examples for java.sql PreparedStatement getGeneratedKeys
ResultSet getGeneratedKeys() throws SQLException;
Statement object. From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public String postRoleUser(int userId, int grid, Integer userid2) throws SQLException { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String label = null;//from ww w . j a v a 2 s . co m String portfolio_id; String change_rights; int owner = 0; int gid = 0; PreparedStatement st; String sql; ResultSet res; ResultSet res1; PreparedStatement stInsert; String sqlInsert; sql = "SELECT * FROM group_info WHERE grid = ?"; st = connection.prepareStatement(sql); st.setInt(1, grid); res = st.executeQuery(); /// Vrifie si un groupe existe, dj associ un rle if (!res.next()) { sql = "SELECT * FROM group_right_info WHERE grid = ?"; st = connection.prepareStatement(sql); st.setInt(1, grid); res1 = st.executeQuery(); if (res1.next()) { label = res1.getString("label"); portfolio_id = res1.getString("portfolio_id"); change_rights = res1.getString("change_rights"); owner = res1.getInt("owner"); } /// Synchronise les valeurs du rle avec le groupe d'utilisateur sqlInsert = "REPLACE INTO group_info(grid, owner, label) VALUES (?, ?, ?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO group_info d using (SELECT ? grid,? owner,? label from dual) s ON (1=2) WHEN NOT MATCHED THEN INSERT (d.grid, d.owner, d.label) values (s.grid, s.owner, s.label)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "gid" }); } stInsert.setInt(1, grid); stInsert.setInt(2, owner); stInsert.setString(3, label); stInsert.executeUpdate(); ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { gid = rs.getInt(1); } // Ajoute la personne sqlInsert = "REPLACE INTO group_user(gid, userid) VALUES (?, ?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO group_user d using (SELECT ? gid,? userid FROM DUAL) s ON (d.gid=s.gid AND d.userid=s.userid) WHEN NOT MATCHED THEN INSERT (d.gid, d.userid) VALUES (s.gid, s.userid)"; stInsert = connection.prepareStatement(sqlInsert); } stInsert.setInt(1, gid); stInsert.setInt(2, userid2); stInsert.executeUpdate(); } else { gid = res.getInt("gid"); sqlInsert = "REPLACE INTO group_user(gid, userid) VALUES (?, ?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO group_user d using (SELECT ? gid,? userid FROM DUAL) s ON (d.gid=s.gid AND d.userid=s.userid) WHEN NOT MATCHED THEN INSERT (d.gid, d.userid) VALUES (s.gid, s.userid)"; stInsert = connection.prepareStatement(sqlInsert); } stInsert.setInt(1, gid); stInsert.setInt(2, userid2); stInsert.executeUpdate(); } return "user " + userid2 + " rajout au groupd gid " + gid + " pour correspondre au groupRight grid " + grid; }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public String postAddNodeType(int userId, Integer type, Integer nodeid, Integer parentid, Integer instance, String data) {//w ww. j av a2s. c o m if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); /** * Format que l'on reoit: * <asm*> * <asmResource xsi_type='nodeRes'>{node_data}</asmResource> * <asmResource xsi_type='context'>{node_data}</asmResource> * <asmResource xsi_type='*'>{node_data}</asmResource> * </asm*> */ String sql = ""; PreparedStatement st; Integer output = 0; Integer parentId = 0; String asmtype = ""; String xsitype = ""; try { /// Prpare les donnes pour les requtes // Parse DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder(); Document document = documentBuilder.parse(new ByteArrayInputStream(data.getBytes("UTF-8"))); // Traite le noeud racine des donnes, retourne l'identifiant du noeud racine Element nodeData = document.getDocumentElement(); asmtype = nodeData.getNodeName(); connection.setAutoCommit(true); // Utilise parentid si on rattache un autre groupe de noeud en dessous d'un noeud existant sql = "INSERT INTO definition_type(def_id,asm_type,parent_node,instance_rule) " + "VALUE(?,?,?,?)"; st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { st = connection.prepareStatement(sql, new String[] { "node_id" }); } st.setInt(1, type); st.setString(2, asmtype); if (parentid == null) st.setNull(3, Types.BIGINT); else st.setInt(3, parentid); if (instance == null) st.setNull(4, Types.BIGINT); else st.setInt(4, instance); output = st.executeUpdate(); ResultSet key = st.getGeneratedKeys(); // On rcure l'identifiant du noeud 'racine' des donnes ajouts if (key.next()) parentId = key.getInt(1); st.close(); // Soit 2 ou 3 resources asmtype = "asmResource"; NodeList resources = document.getElementsByTagName("asmResource"); sql = "INSERT INTO definition_type(def_id,asm_type,xsi_type,parent_node,node_data,instance_rule) " + "VALUE(?,?,?,?,?,?)"; st = connection.prepareStatement(sql); st.setInt(1, type); st.setString(2, asmtype); st.setInt(4, parentId); for (int i = 0; i < resources.getLength(); ++i) { Element resource = (Element) resources.item(i); xsitype = resource.getAttribute("xsi_type"); String resContent = DomUtils.getInnerXml(resource); st.setString(3, xsitype); st.setString(5, resContent); if (instance == null) st.setNull(6, Types.BIGINT); else st.setInt(6, instance); // On ajoute les donnes des ressources restante output = st.executeUpdate(); } st.close(); } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { try { connection.setAutoCommit(true); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return output.toString(); }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public int postShareGroup(String portfolio, int user, Integer userId, String write) { int status = -1; String sql = ""; PreparedStatement st; ResultSet rs;/* w w w. j av a2s. com*/ int gid = 0, grid = 0; int output; int wr = 0; if (write != null || "y".equals(write)) wr = 1; /// Check if portfolio is owner by the user sending this command if (!credential.isOwner(userId, portfolio) && !credential.isAdmin(userId)) return -2; // Not owner try { connection.setAutoCommit(false); // Check if shared group exist sql = "SELECT gi.gid, gri.grid " + "FROM group_right_info gri, group_info gi " + "WHERE gi.grid=gri.grid " + "AND gri.label=? " + "AND gri.portfolio_id=uuid2bin(?)"; st = connection.prepareStatement(sql); st.setString(1, "shared"); st.setString(2, portfolio); rs = st.executeQuery(); if (rs.next()) { gid = rs.getInt("gid"); grid = rs.getInt("grid"); st.close(); } else { //// FIXME: Move group creation in separate method I guess st.close(); /// Create shared group if not exist sql = "INSERT INTO group_right_info(owner, label, portfolio_id) VALUES(?,?,uuid2bin(?))"; st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { st = connection.prepareStatement(sql, new String[] { "grid" }); } st.setInt(1, userId); st.setString(2, "shared"); st.setString(3, portfolio); output = st.executeUpdate(); ResultSet key = st.getGeneratedKeys(); if (key.next()) grid = key.getInt(1); st.close(); sql = "INSERT INTO group_info(grid, owner, label) VALUES(?,?,?)"; st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { st = connection.prepareStatement(sql, new String[] { "gid" }); } st.setInt(1, grid); st.setInt(2, userId); st.setString(3, "shared"); output = st.executeUpdate(); key = st.getGeneratedKeys(); if (key.next()) gid = key.getInt(1); } st.close(); /// Insert user in this shared group sql = "INSERT IGNORE INTO group_user(gid, userid) VALUES(?,?)"; if (dbserveur.equals("oracle")) { sql = "INSERT /*+ ignore_row_on_dupkey_index(group_user,group_user_PK)*/ INTO group_user(gid, userid) VALUES(?,?)"; } st = connection.prepareStatement(sql); st.setInt(1, gid); st.setInt(2, user); st.executeUpdate(); st.close(); /// Flush and insert all rights info in created group sql = "DELETE FROM group_rights WHERE grid=?"; st = connection.prepareStatement(sql); st.setInt(1, grid); st.executeUpdate(); st.close(); sql = "INSERT INTO group_rights(grid, id, RD, WR) " + "SELECT ?, node_uuid, 1, ? FROM node WHERE portfolio_id=uuid2bin(?)"; st = connection.prepareStatement(sql); /// With parameter, add default WR, DL st.setInt(1, grid); st.setInt(2, wr); /// Flag to select if we write too st.setString(3, portfolio); st.executeUpdate(); status = 0; } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { try { connection.setAutoCommit(true); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return status; }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public Object putRole(String xmlRole, int userId, int roleId) throws Exception { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String result = null;/*from w w w. jav a 2 s. c o m*/ String username = null; String password = null; String firstname = null; String lastname = null; String email = null; String label = null; int id = 0; //On prepare les requetes SQL PreparedStatement stInsert; String sqlInsert; //On recupere le body Document doc; doc = DomUtils.xmlString2Document(xmlRole, new StringBuffer()); Element role = doc.getDocumentElement(); NodeList children = null; children = role.getChildNodes(); // On parcourt une premire fois les enfants pour rcuperer la liste crire en base //On verifie le bon format if (role.getNodeName().equals("role")) { for (int i = 0; i < children.getLength(); i++) { if (children.item(i).getNodeName().equals("label")) { label = DomUtils.getInnerXml(children.item(i)); } } } else { result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML"; } //On ajoute l'utilisateur dans la base de donnees try { sqlInsert = "REPLACE INTO credential(login, display_firstname, display_lastname,email, password) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)))"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "INSERT INTO credential(login, display_firstname, display_lastname,email, password) VALUES (?, ?, ?, ?, crypt(?))"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setString(1, username); stInsert.setString(2, firstname); stInsert.setString(3, lastname); stInsert.setString(4, email); stInsert.setString(5, password); stInsert.executeUpdate(); ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } result = "" + id; return result; }
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public Object postUser(String in, int userId) throws Exception { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String result = null;//from w ww . ja va2 s . co m String login = null; String firstname = null; String lastname = null; String label = null; String password = null; String active = "1"; Integer uuid = 0; Integer newId = 0; //On prepare les requetes SQL PreparedStatement stInsert; String sqlInsert; //On recupere le body Document doc = DomUtils.xmlString2Document(in, new StringBuffer()); Element etu = doc.getDocumentElement(); //On verifie le bon format if (etu.getNodeName().equals("user")) { //On recupere les attributs try { if (etu.getAttributes().getNamedItem("uid") != null) { login = etu.getAttributes().getNamedItem("uid").getNodeValue(); if (getMysqlUserUid(login) != null) { uuid = Integer.parseInt(getMysqlUserUid(login)); } } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("firstname") != null) { firstname = etu.getAttributes().getNamedItem("firstname").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("lastname") != null) { lastname = etu.getAttributes().getNamedItem("lastname").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("label") != null) { label = etu.getAttributes().getNamedItem("label").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("password") != null) { password = etu.getAttributes().getNamedItem("password").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("active") != null) { active = etu.getAttributes().getNamedItem("active").getNodeValue(); } } catch (Exception ex) { } } else { result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML"; } //On ajoute l'utilisateur dans la base de donnees if (etu.getAttributes().getNamedItem("firstname") != null && etu.getAttributes().getNamedItem("lastname") != null && etu.getAttributes().getNamedItem("label") == null) { sqlInsert = "REPLACE INTO credential(userid, login, display_firstname, display_lastname, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO credential d USING (SELECT ? userid,? login,? display_firstname,? display_lastname,crypt(?) password,? active FROM DUAL) s ON (d.userid=s.userid) WHEN MATCHED THEN UPDATE SET d.login=s.login, d.display_firstname = s.display_firstname, d.display_lastname = s.display_lastname, d.password = s.password, d.active = s.active WHEN NOT MATCHED THEN INSERT (d.userid, d.login, d.display_firstname, d.display_lastname, d.password, d.active) VALUES (s.userid, s.login, s.display_firstname, s.display_lastname, s.password, s.active)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setInt(1, uuid); stInsert.setString(2, login); stInsert.setString(3, firstname); stInsert.setString(4, lastname); stInsert.setString(5, password); stInsert.setString(6, active); stInsert.executeUpdate(); } else { sqlInsert = "REPLACE INTO credential(userid, login, display_firstname, display_lastname, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO credential d USING (SELECT ? userid,? login,? display_firstname,? display_lastname,crypt(?) password,? active FROM DUAL) s ON (d.userid=s.userid) WHEN MATCHED THEN UPDATE SET d.login=s.login, d.display_firstname = s.display_firstname, d.display_lastname = s.display_lastname, d.password = s.password, d.active = s.active WHEN NOT MATCHED THEN INSERT (d.userid, d.login, d.display_firstname, d.display_lastname, d.password, d.active) VALUES (s.userid, s.login, s.display_firstname, s.display_lastname, s.password, s.active)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setInt(1, uuid); stInsert.setString(2, login); stInsert.setString(3, " "); stInsert.setString(4, label); stInsert.setString(5, password); stInsert.setString(6, active); stInsert.executeUpdate(); } ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { newId = rs.getInt(1); } //On renvoie le body pour qu'il soit stock dans le log result = "<user "; result += DomUtils.getXmlAttributeOutput("uid", login) + " "; result += DomUtils.getXmlAttributeOutput("firstname", firstname) + " "; result += DomUtils.getXmlAttributeOutput("lastname", lastname) + " "; result += DomUtils.getXmlAttributeOutput("label", label) + " "; result += DomUtils.getXmlAttributeOutput("password", password) + " "; result += DomUtils.getXmlAttributeOutputInt("uuid", newId) + " "; result += ">"; result += "</user>"; return result; }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
public int addSubscription(APIIdentifier identifier, String context, int applicationId, String status, String subscriber) throws APIManagementException { Connection conn = null;//from ww w. j a v a 2 s . c o m ResultSet resultSet = null; PreparedStatement ps = null; PreparedStatement preparedStForInsert = null; ResultSet rs = null; int subscriptionId = -1; int apiId; try { conn = APIMgtDBUtil.getConnection(); conn.setAutoCommit(false); apiId = getAPIID(identifier, conn); //Query to check if this subscription already exists String checkDuplicateQuery = SQLConstants.CHECK_EXISTING_SUBSCRIPTION_SQL; ps = conn.prepareStatement(checkDuplicateQuery); ps.setInt(1, apiId); ps.setInt(2, applicationId); resultSet = ps.executeQuery(); //If the subscription already exists if (resultSet.next()) { String subStatus = resultSet.getString("SUB_STATUS"); String subCreationStatus = resultSet.getString("SUBS_CREATE_STATE"); String applicationName = getApplicationNameFromId(applicationId); if ((APIConstants.SubscriptionStatus.UNBLOCKED.equals(subStatus) || APIConstants.SubscriptionStatus.ON_HOLD.equals(subStatus) || APIConstants.SubscriptionStatus.REJECTED.equals(subStatus)) && APIConstants.SubscriptionCreatedStatus.SUBSCRIBE.equals(subCreationStatus)) { //Throw error saying subscription already exists. log.error("Subscription already exists for API " + identifier.getApiName() + " in Application " + applicationName); throw new SubscriptionAlreadyExistingException("Subscription already exists for API " + identifier.getApiName() + " in Application " + applicationName); } else if (APIConstants.SubscriptionStatus.UNBLOCKED.equals(subStatus) && APIConstants.SubscriptionCreatedStatus.UN_SUBSCRIBE.equals(subCreationStatus)) { deleteSubscriptionByApiIDAndAppID(apiId, applicationId, conn); } else if (APIConstants.SubscriptionStatus.BLOCKED.equals(subStatus) || APIConstants.SubscriptionStatus.PROD_ONLY_BLOCKED.equals(subStatus)) { log.error("Subscription to API " + identifier.getApiName() + " through application " + applicationName + " was blocked"); throw new APIManagementException("Subscription to API " + identifier.getApiName() + " through " + "application " + applicationName + " was blocked"); } } //This query to update the AM_SUBSCRIPTION table String sqlQuery = SQLConstants.ADD_SUBSCRIPTION_SQL; //Adding data to the AM_SUBSCRIPTION table //ps = conn.prepareStatement(sqlQuery, Statement.RETURN_GENERATED_KEYS); preparedStForInsert = conn.prepareStatement(sqlQuery, new String[] { "SUBSCRIPTION_ID" }); if (conn.getMetaData().getDriverName().contains("PostgreSQL")) { preparedStForInsert = conn.prepareStatement(sqlQuery, new String[] { "subscription_id" }); } preparedStForInsert.setString(1, identifier.getTier()); preparedStForInsert.setInt(2, apiId); preparedStForInsert.setInt(3, applicationId); preparedStForInsert.setString(4, status != null ? status : APIConstants.SubscriptionStatus.UNBLOCKED); preparedStForInsert.setString(5, APIConstants.SubscriptionCreatedStatus.SUBSCRIBE); preparedStForInsert.setString(6, subscriber); preparedStForInsert.setTimestamp(7, new Timestamp(System.currentTimeMillis())); preparedStForInsert.setString(8, UUID.randomUUID().toString()); preparedStForInsert.executeUpdate(); rs = preparedStForInsert.getGeneratedKeys(); while (rs.next()) { //subscriptionId = rs.getInt(1); subscriptionId = Integer.parseInt(rs.getString(1)); } // finally commit transaction conn.commit(); } catch (SQLException e) { if (conn != null) { try { conn.rollback(); } catch (SQLException e1) { log.error("Failed to rollback the add subscription ", e1); } } handleException("Failed to add subscriber data ", e); } finally { APIMgtDBUtil.closeAllConnections(ps, conn, resultSet); APIMgtDBUtil.closeAllConnections(preparedStForInsert, null, rs); } return subscriptionId; }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
/** * * @param userName User name.// ww w.j av a2s .c om * @param emailList Comma separated email list. * @param alertTypesIDList Comma separated alert types list. * @param stakeHolder if pram value = p we assume those changes from publisher if param value = s those data belongs to * subscriber. * @throws APIManagementException * @throws SQLException */ public void addAlertTypesConfigInfo(String userName, String emailList, String alertTypesIDList, String stakeHolder) throws APIManagementException, SQLException { Connection connection; PreparedStatement ps = null; ResultSet rs = null; connection = APIMgtDBUtil.getConnection(); connection.setAutoCommit(false); try { String alertTypesQuery = SQLConstants.ADD_ALERT_TYPES_VALUES; String deleteAlertTypesByUserNameAndStakeHolderQuery = SQLConstants.DELETE_ALERTTYPES_BY_USERNAME_AND_STAKE_HOLDER; ps = connection.prepareStatement(deleteAlertTypesByUserNameAndStakeHolderQuery); ps.setString(1, userName); ps.setString(2, stakeHolder); ps.executeUpdate(); if (!StringUtils.isEmpty(alertTypesIDList)) { List<String> alertTypeIdList = Arrays.asList(alertTypesIDList.split(",")); for (String alertTypeId : alertTypeIdList) { PreparedStatement psAlertTypeId = null; try { psAlertTypeId = connection.prepareStatement(alertTypesQuery); psAlertTypeId.setInt(1, Integer.parseInt(alertTypeId)); psAlertTypeId.setString(2, userName); psAlertTypeId.setString(3, stakeHolder); psAlertTypeId.execute(); } catch (SQLException e) { handleException("Error while adding alert types", e); } finally { APIMgtDBUtil.closeAllConnections(psAlertTypeId, null, null); } } } String getEmailListIdByUserNameAndStakeHolderQuery = SQLConstants.GET_EMAILLISTID_BY_USERNAME_AND_STAKEHOLDER; ps = connection.prepareStatement(getEmailListIdByUserNameAndStakeHolderQuery); ps.setString(1, userName); ps.setString(2, stakeHolder); rs = ps.executeQuery(); int emailListId = 0; while (rs.next()) { emailListId = rs.getInt(1); } if (emailListId != 0) { String deleteEmailListDetailsByEmailListId = SQLConstants.DELETE_EMAILLIST_BY_EMAIL_LIST_ID; ps = connection.prepareStatement(deleteEmailListDetailsByEmailListId); ps.setInt(1, emailListId); ps.executeUpdate(); if (!StringUtils.isEmpty(emailList)) { List<String> extractedEmailList = Arrays.asList(emailList.split(",")); String saveEmailListDetailsQuery = SQLConstants.SAVE_EMAIL_LIST_DETAILS_QUERY; for (String email : extractedEmailList) { PreparedStatement extractedEmailListPs = null; try { extractedEmailListPs = connection.prepareStatement(saveEmailListDetailsQuery); extractedEmailListPs.setInt(1, emailListId); extractedEmailListPs.setString(2, email); extractedEmailListPs.execute(); } catch (SQLException e) { handleException("Error while save email list.", e); } finally { APIMgtDBUtil.closeAllConnections(extractedEmailListPs, null, null); } } } } else { String emailListSaveQuery = SQLConstants.ADD_ALERT_EMAIL_LIST; String dbProductName = connection.getMetaData().getDatabaseProductName(); ps = connection.prepareStatement(emailListSaveQuery, new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "EMAIL_LIST_ID") }); ps.setString(1, userName); ps.setString(2, stakeHolder); ps.execute(); rs = ps.getGeneratedKeys(); if (rs.next()) { int generatedEmailIdList = rs.getInt(1); if (!StringUtils.isEmpty(emailList)) { List<String> extractedEmailList = Arrays.asList(emailList.split(",")); String saveEmailListDetailsQuery = SQLConstants.SAVE_EMAIL_LIST_DETAILS_QUERY; for (String email : extractedEmailList) { PreparedStatement elseExtractedEmailListPS = null; try { elseExtractedEmailListPS = connection.prepareStatement(saveEmailListDetailsQuery); elseExtractedEmailListPS.setInt(1, generatedEmailIdList); elseExtractedEmailListPS.setString(2, email); elseExtractedEmailListPS.execute(); } catch (SQLException e) { handleException("Error while save email list.", e); } finally { APIMgtDBUtil.closeAllConnections(elseExtractedEmailListPS, null, null); } } } } } connection.commit(); } catch (SQLException e) { handleException("Failed to save alert preferences", e); } finally { APIMgtDBUtil.closeAllConnections(ps, connection, rs); } }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public String postUsers(String in, int userId) throws Exception { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String result = null;/*from w w w. j a v a2s . co m*/ String username = null; String password = null; String firstname = null; String lastname = null; String email = null; String designerstr = null; String active = "1"; int id = 0; int designer; //On prepare les requetes SQL PreparedStatement stInsert; String sqlInsert; //On recupere le body Document doc; doc = DomUtils.xmlString2Document(in, new StringBuffer()); Element users = doc.getDocumentElement(); NodeList children = null; children = users.getChildNodes(); // On parcourt une premire fois les enfants pour rcuperer la liste crire en base //On verifie le bon format if (users.getNodeName().equals("users")) { for (int i = 0; i < children.getLength(); i++) { if (children.item(i).getNodeName().equals("user")) { NodeList children2 = null; children2 = children.item(i).getChildNodes(); for (int y = 0; y < children2.getLength(); y++) { if (children2.item(y).getNodeName().equals("username")) { username = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("password")) { password = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("firstname")) { firstname = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("lastname")) { lastname = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("email")) { email = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("active")) { active = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("designer")) { designerstr = DomUtils.getInnerXml(children2.item(y)); } } } } } else { result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML"; } //On ajoute l'utilisateur dans la base de donnees try { sqlInsert = "REPLACE INTO credential(login, display_firstname, display_lastname,email, password, active, is_designer) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?,?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "INSERT INTO credential(login, display_firstname, display_lastname,email, password, active, is_designer) VALUES (?, ?, ?, ?, crypt(?),?,?)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setString(1, username); if (firstname == null) { firstname = " "; stInsert.setString(2, firstname); } else { stInsert.setString(2, firstname); } if (lastname == null) { lastname = " "; stInsert.setString(3, lastname); } else { stInsert.setString(3, lastname); } if (email == null) { email = " "; stInsert.setString(4, email); } else { stInsert.setString(4, email); } stInsert.setString(5, password); if (active == null) { active = " "; stInsert.setString(6, active); } else { stInsert.setString(6, active); } if (designerstr == null) { designer = 0; stInsert.setInt(7, designer); } else { designer = Integer.parseInt(designerstr); stInsert.setInt(7, designer); } stInsert.executeUpdate(); ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } //On renvoie le body pour qu'il soit stock dans le log result = "<users>"; result += "<user "; result += DomUtils.getXmlAttributeOutputInt("id", id); result += ">"; result += DomUtils.getXmlElementOutput("username", username); result += DomUtils.getXmlElementOutput("password", password); result += DomUtils.getXmlElementOutput("firstname", firstname); result += DomUtils.getXmlElementOutput("lastname", lastname); result += DomUtils.getXmlElementOutput("email", email); result += DomUtils.getXmlElementOutput("active", active); result += DomUtils.getXmlElementOutput("designer", designerstr); result += "</user>"; result += "</users>"; return result; }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public Object postUser(String in, int userId) throws Exception { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String result = null;//w w w . j a v a 2 s .c o m String login = null; String firstname = null; String lastname = null; String label = null; String password = null; String active = "1"; Integer uuid = 0; Integer newId = 0; //On prepare les requetes SQL PreparedStatement stInsert; String sqlInsert; //On recupere le body Document doc = DomUtils.xmlString2Document(in, new StringBuffer()); Element etu = doc.getDocumentElement(); //On verifie le bon format if (etu.getNodeName().equals("user")) { //On recupere les attributs try { if (etu.getAttributes().getNamedItem("uid") != null) { login = etu.getAttributes().getNamedItem("uid").getNodeValue(); if (getMysqlUserUid(login) != null) { uuid = Integer.parseInt(getMysqlUserUid(login)); } } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("firstname") != null) { firstname = etu.getAttributes().getNamedItem("firstname").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("lastname") != null) { lastname = etu.getAttributes().getNamedItem("lastname").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("label") != null) { label = etu.getAttributes().getNamedItem("label").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("password") != null) { password = etu.getAttributes().getNamedItem("password").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("active") != null) { active = etu.getAttributes().getNamedItem("active").getNodeValue(); } } catch (Exception ex) { } } else { result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML"; } //On ajoute l'utilisateur dans la base de donnees if (etu.getAttributes().getNamedItem("firstname") != null && etu.getAttributes().getNamedItem("lastname") != null && etu.getAttributes().getNamedItem("label") == null) { sqlInsert = "REPLACE INTO credential(userid, login, display_firstname, display_lastname, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO credential d USING (SELECT ? userid,? login,? display_firstname,? display_lastname,crypt(?) password,? active FROM DUAL) s ON (d.userid=s.userid) WHEN MATCHED THEN UPDATE SET d.login=s.login, d.display_firstname = s.display_firstname, d.display_lastname = s.display_lastname, d.password = s.password, d.active = s.active WHEN NOT MATCHED THEN INSERT (d.userid, d.login, d.display_firstname, d.display_lastname, d.password, d.active) VALUES (s.userid, s.login, s.display_firstname, s.display_lastname, s.password, s.active)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setInt(1, uuid); stInsert.setString(2, login); stInsert.setString(3, firstname); stInsert.setString(4, lastname); stInsert.setString(5, password); stInsert.setString(6, active); stInsert.executeUpdate(); } else { sqlInsert = "REPLACE INTO credential(userid, login, display_firstname, display_lastname, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO credential d USING (SELECT ? userid,? login,? display_firstname,? display_lastname,crypt(?) password,? active FROM DUAL) s ON (d.userid=s.userid) WHEN MATCHED THEN UPDATE SET d.login=s.login, d.display_firstname = s.display_firstname, d.display_lastname = s.display_lastname, d.password = s.password, d.active = s.active WHEN NOT MATCHED THEN INSERT (d.userid, d.login, d.display_firstname, d.display_lastname, d.password, d.active) VALUES (s.userid, s.login, s.display_firstname, s.display_lastname, s.password, s.active)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setInt(1, uuid); stInsert.setString(2, login); stInsert.setString(3, " "); stInsert.setString(4, label); stInsert.setString(5, password); stInsert.setString(6, active); stInsert.executeUpdate(); } ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { newId = rs.getInt(1); } //On renvoie le body pour qu'il soit stock dans le log result = "<user "; result += DomUtils.getXmlAttributeOutput("uid", login) + " "; result += DomUtils.getXmlAttributeOutput("firstname", firstname) + " "; result += DomUtils.getXmlAttributeOutput("lastname", lastname) + " "; result += DomUtils.getXmlAttributeOutput("label", label) + " "; result += DomUtils.getXmlAttributeOutput("password", password) + " "; result += DomUtils.getXmlAttributeOutputInt("uuid", newId) + " "; result += ">"; result += "</user>"; return result; }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public Object postInstanciatePortfolio(MimeType inMimeType, String portfolioUuid, String srcCode, String newCode, int userId, int groupId, boolean copyshared, String portfGroupName) throws Exception { String sql = ""; PreparedStatement st; String newPortfolioUuid = UUID.randomUUID().toString(); try {/* ww w. j a va 2s .c om*/ /// Find source code if (srcCode != null) { /// Find back portfolio uuid from source code sql = "SELECT bin2uuid(portfolio_id) AS uuid FROM node WHERE code=?"; st = connection.prepareStatement(sql); st.setString(1, srcCode); ResultSet res = st.executeQuery(); if (res.next()) portfolioUuid = res.getString("uuid"); } if (portfolioUuid == null) return ""; ///// Cration des tables temporaires /// Pour la copie de la structure if (dbserveur.equals("mysql")) { sql = "CREATE TEMPORARY TABLE t_data(" + "new_uuid binary(16) NOT NULL, " + /// Pour la copie d'une nouvelle structure "node_uuid binary(16) NOT NULL, " + "node_parent_uuid binary(16) DEFAULT NULL, " + "node_children_uuid blob, " + "node_order int(12) NOT NULL, " + "metadata text NOT NULL, " + "metadata_wad text NOT NULL, " + "metadata_epm text NOT NULL, " + "res_node_uuid binary(16) DEFAULT NULL, " + "res_res_node_uuid binary(16) DEFAULT NULL, " + "res_context_node_uuid binary(16) DEFAULT NULL, " + "shared_res int(1) NOT NULL, " + "shared_node int(1) NOT NULL, " + "shared_node_res int(1) NOT NULL, " + "shared_res_uuid BINARY(16) NULL, " + "shared_node_uuid BINARY(16) NULL, " + "shared_node_res_uuid BINARY(16) NULL, " + "asm_type varchar(50) DEFAULT NULL, " + "xsi_type varchar(50) DEFAULT NULL, " + "semtag varchar(250) DEFAULT NULL, " + "semantictag varchar(250) DEFAULT NULL, " + "label varchar(250) DEFAULT NULL, " + "code varchar(250) DEFAULT NULL, " + "descr varchar(250) DEFAULT NULL, " + "format varchar(30) DEFAULT NULL, " + "modif_user_id int(12) NOT NULL, " + "modif_date timestamp NULL DEFAULT NULL, " + "portfolio_id binary(16) DEFAULT NULL) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; st = connection.prepareStatement(sql); st.execute(); st.close(); } else if (dbserveur.equals("oracle")) { String v_sql = "CREATE GLOBAL TEMPORARY TABLE t_data(" + "new_uuid RAW(16) NOT NULL, " + /// Pour la copie d'une nouvelle structure "node_uuid RAW(16) NOT NULL, " + "node_parent_uuid RAW(16) DEFAULT NULL, " + "node_children_uuid CLOB, " + "node_order NUMBER(12) NOT NULL, " + "metadata CLOB DEFAULT NULL, " + "metadata_wad CLOB DEFAULT NULL, " + "metadata_epm CLOB DEFAULT NULL, " + "res_node_uuid RAW(16) DEFAULT NULL, " + "res_res_node_uuid RAW(16) DEFAULT NULL, " + "res_context_node_uuid RAW(16) DEFAULT NULL, " + "shared_res NUMBER(1) NOT NULL, " + "shared_node NUMBER(1) NOT NULL, " + "shared_node_res NUMBER(1) NOT NULL, " + "shared_res_uuid RAW(16) DEFAULT NULL, " + "shared_node_uuid RAW(16) DEFAULT NULL, " + "shared_node_res_uuid RAW(16) DEFAULT NULL, " + "asm_type VARCHAR2(50 CHAR) DEFAULT NULL, " + "xsi_type VARCHAR2(50 CHAR) DEFAULT NULL, " + "semtag VARCHAR2(250 CHAR) DEFAULT NULL, " + "semantictag VARCHAR2(250 CHAR) DEFAULT NULL, " + "label VARCHAR2(250 CHAR) DEFAULT NULL, " + "code VARCHAR2(250 CHAR) DEFAULT NULL, " + "descr VARCHAR2(250 CHAR) DEFAULT NULL, " + "format VARCHAR2(30 CHAR) DEFAULT NULL, " + "modif_user_id NUMBER(12) NOT NULL, " + "modif_date timestamp DEFAULT NULL, " + "portfolio_id RAW(16) DEFAULT NULL) ON COMMIT PRESERVE ROWS"; sql = "{call create_or_empty_table('t_data','" + v_sql + "')}"; CallableStatement ocs = connection.prepareCall(sql); ocs.execute(); ocs.close(); } /// Pour la copie des donnes if (dbserveur.equals("mysql")) { sql = "CREATE TEMPORARY TABLE t_res(" + "new_uuid binary(16) NOT NULL, " + /// Pour la copie d'une nouvelle structure "node_uuid binary(16) NOT NULL, " + "xsi_type varchar(50) NOT NULL, " + "content text, " + "user_id int(11) DEFAULT NULL, " + "modif_user_id int(12) NOT NULL, " + "modif_date timestamp NULL DEFAULT NULL) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; st = connection.prepareStatement(sql); st.execute(); st.close(); } else if (dbserveur.equals("oracle")) { String v_sql = "CREATE GLOBAL TEMPORARY TABLE t_res(" + "new_uuid RAW(16) NOT NULL, " + /// Pour la copie d'une nouvelle structure "node_uuid RAW(16) NOT NULL, " + "xsi_type VARCHAR2(50 CHAR) NOT NULL, " + "content CLOB, " + "user_id NUMBER(11) DEFAULT NULL, " + "modif_user_id NUMBER(12) NOT NULL, " + "modif_date timestamp DEFAULT NULL) ON COMMIT PRESERVE ROWS"; sql = "{call create_or_empty_table('t_res','" + v_sql + "')}"; CallableStatement ocs = connection.prepareCall(sql); ocs.execute(); ocs.close(); } /// Pour la mise jour de la liste des enfants/parents if (dbserveur.equals("mysql")) { sql = "CREATE TEMPORARY TABLE t_struc(" + "node_order int(12) NOT NULL, " + "new_uuid binary(16) NOT NULL, " + "uuid binary(16) UNIQUE NOT NULL, " + "node_parent_uuid binary(16), " + "t_level INT) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; st = connection.prepareStatement(sql); st.execute(); st.close(); } else if (dbserveur.equals("oracle")) { String v_sql = "CREATE GLOBAL TEMPORARY TABLE t_struc(" + "node_order NUMBER(12) NOT NULL, " + "new_uuid RAW(16) NOT NULL, " + "uuid RAW(16) NOT NULL, " + "node_parent_uuid RAW(16), " + "t_level NUMBER(10,0)" + ", CONSTRAINT t_struc_UK_uuid UNIQUE (uuid)) ON COMMIT PRESERVE ROWS"; sql = "{call create_or_empty_table('t_struc','" + v_sql + "')}"; CallableStatement ocs = connection.prepareCall(sql); ocs.execute(); ocs.close(); } /// Pour l'histoire des shared_node a filtrer if (dbserveur.equals("mysql")) { sql = "CREATE TEMPORARY TABLE t_struc_2(" + "node_order int(12) NOT NULL, " + "new_uuid binary(16) NOT NULL, " + "uuid binary(16) UNIQUE NOT NULL, " + "node_parent_uuid binary(16), " + "t_level INT) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; st = connection.prepareStatement(sql); st.execute(); st.close(); } else if (dbserveur.equals("oracle")) { String v_sql = "CREATE GLOBAL TEMPORARY TABLE t_struc_2(" + "node_order NUMBER(12) NOT NULL, " + "new_uuid RAW(16) NOT NULL, " + "uuid RAW(16) NOT NULL, " + "node_parent_uuid RAW(16), " + "t_level NUMBER(10,0)" + ", CONSTRAINT t_struc_2_UK_uuid UNIQUE (uuid)) ON COMMIT PRESERVE ROWS"; sql = "{call create_or_empty_table('t_struc_2','" + v_sql + "')}"; CallableStatement ocs = connection.prepareCall(sql); ocs.execute(); ocs.close(); } /// Pour les nouveaux ensembles de droits if (dbserveur.equals("mysql")) { sql = "CREATE TEMPORARY TABLE t_rights(" + "grid BIGINT NOT NULL, " + "id binary(16) NOT NULL, " + "RD BOOL NOT NULL, " + "WR BOOL NOT NULL, " + "DL BOOL NOT NULL, " + "SB BOOL NOT NULL, " + "AD BOOL NOT NULL, " + "types_id TEXT, " + "rules_id TEXT) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; st = connection.prepareStatement(sql); st.execute(); st.close(); } else if (dbserveur.equals("oracle")) { String v_sql = "CREATE GLOBAL TEMPORARY TABLE t_rights(" + "grid NUMBER(19,0) NOT NULL, " + "id RAW(16) NOT NULL, " + "RD NUMBER(1) NOT NULL, " + "WR NUMBER(1) NOT NULL, " + "DL NUMBER(1) NOT NULL, " + "SB NUMBER(1) NOT NULL, " + "AD NUMBER(1) NOT NULL, " + "types_id VARCHAR2(2000 CHAR), " + "rules_id VARCHAR2(2000 CHAR)) ON COMMIT PRESERVE ROWS"; sql = "{call create_or_empty_table('t_rights','" + v_sql + "')}"; CallableStatement ocs = connection.prepareCall(sql); ocs.execute(); ocs.close(); } /// Copie de la structure sql = "INSERT INTO t_data(new_uuid, node_uuid, node_parent_uuid, node_children_uuid, node_order, metadata, metadata_wad, metadata_epm, res_node_uuid, res_res_node_uuid, res_context_node_uuid , shared_res, shared_node, shared_node_res, shared_res_uuid, shared_node_uuid, shared_node_res_uuid, asm_type, xsi_type, semtag, semantictag, label, code, descr, format, modif_user_id, modif_date, portfolio_id) "; if (dbserveur.equals("mysql")) { sql += "SELECT uuid2bin(UUID()), "; } else if (dbserveur.equals("oracle")) { sql += "SELECT sys_guid(), "; } sql += "node_uuid, node_parent_uuid, node_children_uuid, node_order, metadata, metadata_wad, metadata_epm, res_node_uuid, res_res_node_uuid, res_context_node_uuid , shared_res, shared_node, shared_node_res, shared_res_uuid, shared_node_uuid, shared_node_res_uuid, asm_type, xsi_type, semtag, semantictag, label, code, descr, format, modif_user_id, modif_date, portfolio_id " + "FROM node n " + "WHERE portfolio_id=uuid2bin(?)"; st = connection.prepareStatement(sql); st.setString(1, portfolioUuid); st.executeUpdate(); st.close(); if (!copyshared) { /// Liste les noeud a filtrer sql = "INSERT INTO t_struc(node_order, new_uuid, uuid, node_parent_uuid, t_level) " + "SELECT node_order, new_uuid, node_uuid, node_parent_uuid, 0 FROM t_data WHERE shared_node=1"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); int level = 0; int added = 1; if (dbserveur.equals("mysql")) { sql = "INSERT IGNORE INTO t_struc_2(node_order, new_uuid, uuid, node_parent_uuid, t_level) "; } else if (dbserveur.equals("oracle")) { sql = "INSERT /*+ ignore_row_on_dupkey_index(t_struc_2,t_struc_2_UK_uuid)*/ INTO t_struc_2(node_order, new_uuid, uuid, node_parent_uuid, t_level) "; } sql += "SELECT d.node_order, d.new_uuid, d.node_uuid, d.node_parent_uuid, ? " + "FROM t_data d WHERE d.node_parent_uuid IN (SELECT uuid FROM t_struc t " + "WHERE t.t_level=?)"; String sqlTemp = null; if (dbserveur.equals("mysql")) { sqlTemp = "INSERT IGNORE INTO t_struc SELECT * FROM t_struc_2;"; } else if (dbserveur.equals("oracle")) { sqlTemp = "INSERT /*+ ignore_row_on_dupkey_index(t_struc,t_struc_UK_uuid)*/ INTO t_struc SELECT * FROM t_struc_2"; } PreparedStatement stTemp = connection.prepareStatement(sqlTemp); st = connection.prepareStatement(sql); while (added != 0) { st.setInt(1, level + 1); st.setInt(2, level); st.executeUpdate(); added = stTemp.executeUpdate(); // On s'arrte quand rien t ajout level = level + 1; // Prochaine tape } st.close(); stTemp.close(); // Retire les noeuds en dessous du shared sql = "DELETE FROM t_struc WHERE uuid IN (SELECT node_uuid FROM t_data WHERE shared_node=1)"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); sql = "DELETE FROM t_data WHERE node_uuid IN (SELECT uuid FROM t_struc)"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); sql = "DELETE FROM t_struc"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); } /// Copie les uuid pour la rsolution des parents/enfants sql = "INSERT INTO t_struc(node_order, new_uuid, uuid, node_parent_uuid) " + "SELECT node_order, new_uuid, node_uuid, node_parent_uuid FROM t_data"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); if (!copyshared) { /// Cas spcial pour shared_node=1 // Le temps qu'on refasse la liste des enfants, on va enlever le noeud plus tard sql = "UPDATE t_data SET shared_node_uuid=node_uuid WHERE shared_node=1"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); // Met a jour t_struc pour la redirection. C'est pour la list des enfants // FIXME: A vrifier les appels qui modifie la liste des enfants. if (dbserveur.equals("mysql")) { sql = "UPDATE t_struc s INNER JOIN t_data d ON s.uuid=d.node_uuid " + "SET s.new_uuid=d.node_uuid WHERE d.shared_node=1"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_struc s SET s.new_uuid=(SELECT d.node_uuid FROM t_struc s2 INNER JOIN t_data d ON s2.uuid=d.node_uuid WHERE d.shared_node=1) WHERE EXISTS (SELECT 1 FROM t_struc s2 INNER JOIN t_data d ON s2.uuid=d.node_uuid WHERE d.shared_node=1)"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); } /// Copie des donnes non partags (shared=0) // Specific sql = "INSERT INTO t_res(new_uuid, node_uuid, xsi_type, content, user_id, modif_user_id, modif_date) "; if (dbserveur.equals("mysql")) { sql += "SELECT uuid2bin(UUID()), "; } else if (dbserveur.equals("oracle")) { sql += "SELECT sys_guid(), "; } sql += "r.node_uuid, r.xsi_type, r.content, r.user_id, r.modif_user_id, r.modif_date " + "FROM t_data d " + "LEFT JOIN resource_table r ON d.res_node_uuid=r.node_uuid " + "WHERE "; if (!copyshared) sql += "shared_res=0 AND "; if (dbserveur.equals("mysql")) { sql += "d.res_node_uuid <> 0x0000000000000000000000000000000"; // Binaire non null } else if (dbserveur.equals("oracle")) { sql += "d.res_node_uuid <> '00000000000000000000000000000000'"; // Binaire non null } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); sql = "INSERT INTO t_res(new_uuid, node_uuid, xsi_type, content, user_id, modif_user_id, modif_date) "; if (dbserveur.equals("mysql")) { sql += "SELECT uuid2bin(UUID()), "; } else if (dbserveur.equals("oracle")) { sql += "SELECT sys_guid(), "; } sql += "r.node_uuid, r.xsi_type, r.content, r.user_id, r.modif_user_id, r.modif_date " + "FROM t_data d " + "LEFT JOIN resource_table r ON d.res_context_node_uuid=r.node_uuid " + "WHERE "; if (!copyshared) sql += "shared_node=0 AND "; if (dbserveur.equals("mysql")) { sql += "d.res_context_node_uuid <> 0x0000000000000000000000000000000"; } else if (dbserveur.equals("oracle")) { sql += "d.res_context_node_uuid <> '00000000000000000000000000000000'"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); // nodeRes sql = "INSERT INTO t_res(new_uuid, node_uuid, xsi_type, content, user_id, modif_user_id, modif_date) "; if (dbserveur.equals("mysql")) { sql += "SELECT uuid2bin(UUID()), "; } else if (dbserveur.equals("oracle")) { sql += "SELECT sys_guid(), "; } sql += "r.node_uuid, r.xsi_type, r.content, r.user_id, r.modif_user_id, r.modif_date " + "FROM t_data d " + "LEFT JOIN resource_table r ON d.res_res_node_uuid=r.node_uuid " + "WHERE "; if (!copyshared) sql += "shared_node_res=0 AND "; if (dbserveur.equals("mysql")) { sql += "d.res_res_node_uuid <> 0x0000000000000000000000000000000"; } else if (dbserveur.equals("oracle")) { sql += "d.res_res_node_uuid <> '00000000000000000000000000000000'"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); /// Changement du uuid du portfolio sql = "UPDATE t_data t SET t.portfolio_id=uuid2bin(?)"; st = connection.prepareStatement(sql); st.setString(1, newPortfolioUuid); st.executeUpdate(); st.close(); /// Rsolution des nouveaux uuid avec les parents // Avec la structure (et droits sur la structure) if (dbserveur.equals("mysql")) { sql = "UPDATE t_rights ri, t_data d SET ri.id=d.new_uuid WHERE ri.id=d.node_uuid AND d.shared_node=0"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_rights ri SET ri.id=(SELECT new_uuid FROM t_data d WHERE ri.id=d.node_uuid AND d.shared_node=0) WHERE EXISTS (SELECT 1 FROM t_data d WHERE ri.id=d.node_uuid AND d.shared_node=0)"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); sql = "UPDATE t_data t " + "SET t.node_parent_uuid = (SELECT new_uuid FROM t_struc s WHERE s.uuid=t.node_parent_uuid)"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); // Avec les ressources (et droits des ressources) if (dbserveur.equals("mysql")) { sql = "UPDATE t_rights ri, t_res re SET ri.id = re.new_uuid WHERE re.node_uuid=ri.id"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_rights ri SET ri.id=(SELECT new_uuid FROM t_res re WHERE re.node_uuid=ri.id) WHERE EXISTS (SELECT 1 FROM t_res re WHERE re.node_uuid=ri.id)"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); if (dbserveur.equals("mysql")) { sql = "UPDATE t_data d INNER JOIN t_res r ON d.res_node_uuid=r.node_uuid " + "SET d.res_node_uuid=r.new_uuid"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_data d SET d.res_node_uuid=(SELECT r.new_uuid FROM t_res r WHERE d.res_node_uuid=r.node_uuid) WHERE EXISTS (SELECT 1 FROM t_res r WHERE d.res_node_uuid=r.node_uuid)"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); if (dbserveur.equals("mysql")) { sql = "UPDATE t_data d INNER JOIN t_res r ON d.res_res_node_uuid=r.node_uuid " + "SET d.res_res_node_uuid=r.new_uuid"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_data d SET d.res_res_node_uuid=(SELECT r.new_uuid FROM t_res r WHERE d.res_res_node_uuid=r.node_uuid) WHERE EXISTS (SELECT 1 FROM t_res r WHERE d.res_res_node_uuid=r.node_uuid)"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); if (dbserveur.equals("mysql")) { sql = "UPDATE t_data d INNER JOIN t_res r ON d.res_context_node_uuid=r.node_uuid " + "SET d.res_context_node_uuid=r.new_uuid"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_data d SET d.res_context_node_uuid=(SELECT r.new_uuid FROM t_res r WHERE d.res_context_node_uuid=r.node_uuid) WHERE EXISTS (SELECT 1 FROM t_res r WHERE d.res_context_node_uuid=r.node_uuid)"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); /// Mise jour de la liste des enfants (! requte particulire) /// L'ordre dtermine le rendu visuel final du xml if (dbserveur.equals("mysql")) { sql = "UPDATE t_data d, (" + "SELECT node_parent_uuid, GROUP_CONCAT(bin2uuid(s.new_uuid) ORDER BY s.node_order) AS value " + "FROM t_struc s GROUP BY s.node_parent_uuid) tmp " + "SET d.node_children_uuid=tmp.value " + "WHERE tmp.node_parent_uuid=d.node_uuid"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_data d SET d.node_children_uuid=(SELECT value FROM (SELECT node_parent_uuid, LISTAGG(bin2uuid(s.new_uuid), ',') WITHIN GROUP (ORDER BY s.node_order) AS value FROM t_struc s GROUP BY s.node_parent_uuid) tmp WHERE tmp.node_parent_uuid=d.node_uuid) WHERE EXISTS (SELECT 1 FROM t_struc WHERE node_parent_uuid=d.node_uuid)"; } st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); // Mise jour du code dans le contenu du noeud (blech) if (dbserveur.equals("mysql")) { sql = "UPDATE t_data d " + "LEFT JOIN t_res r ON d.res_res_node_uuid=r.new_uuid " + // Il faut utiliser le nouveau uuid "SET r.content=REPLACE(r.content, d.code, ?) " + "WHERE d.asm_type='asmRoot'"; } else if (dbserveur.equals("oracle")) { sql = "UPDATE t_res r SET r.content=(SELECT REPLACE(r2.content, d.code, ?) FROM t_data d LEFT JOIN t_res r2 ON d.res_res_node_uuid=r2.new_uuid WHERE d.asm_type='asmRoot') WHERE EXISTS (SELECT 1 FROM t_data d WHERE d.res_res_node_uuid=r.new_uuid AND d.asm_type='asmRoot')"; } st = connection.prepareStatement(sql); st.setString(1, newCode); st.executeUpdate(); st.close(); // Mise jour du code dans le code interne de la BD sql = "UPDATE t_data d SET d.code=? WHERE d.asm_type='asmRoot'"; st = connection.prepareStatement(sql); st.setString(1, newCode); st.executeUpdate(); st.close(); /// temp class class right { int rd = 0; int wr = 0; int dl = 0; int sb = 0; int ad = 0; String types = ""; String rules = ""; String notify = ""; } ; class groupright { right getGroup(String label) { right r = rights.get(label.trim()); if (r == null) { r = new right(); rights.put(label, r); } return r; } void setNotify(String roles) { Iterator<right> iter = rights.values().iterator(); while (iter.hasNext()) { right r = iter.next(); r.notify = roles.trim(); } } HashMap<String, right> rights = new HashMap<String, right>(); } ; class resolver { groupright getUuid(String uuid) { groupright gr = resolve.get(uuid); if (gr == null) { gr = new groupright(); resolve.put(uuid, gr); } return gr; }; HashMap<String, groupright> resolve = new HashMap<String, groupright>(); HashMap<String, Integer> groups = new HashMap<String, Integer>(); } ; resolver resolve = new resolver(); /// Cre les groupes de droits en les copiants dans la table d'origine // Slectionne les groupes concerns /* sql = "SELECT login FROM credential c WHERE c.userid=?"; st = connection.prepareStatement(sql); st.setInt(1, userId); ResultSet res = st.executeQuery(); String login=""; if( res.next() ) login = res.getString("login"); //*/ // Selection des metadonnes sql = "SELECT bin2uuid(t.new_uuid) AS uuid, bin2uuid(t.portfolio_id) AS puuid, t.metadata, t.metadata_wad, t.metadata_epm " + "FROM t_data t"; st = connection.prepareStatement(sql); ResultSet res = st.executeQuery(); DocumentBuilder documentBuilder; DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); documentBuilder = documentBuilderFactory.newDocumentBuilder(); while (res.next()) { String uuid = res.getString("uuid"); // String puuid = res.getString("puuid"); String meta = res.getString("metadata_wad"); // meta = meta.replaceAll("user", login); String nodeString = "<?xml version='1.0' encoding='UTF-8' standalone='no'?><transfer " + meta + "/>"; groupright role = resolve.getUuid(uuid); try { /// parse meta InputSource is = new InputSource(new StringReader(nodeString)); Document doc = documentBuilder.parse(is); /// Process attributes Element attribNode = doc.getDocumentElement(); NamedNodeMap attribMap = attribNode.getAttributes(); String nodeRole; Node att = attribMap.getNamedItem("access"); if (att != null) { //if(access.equalsIgnoreCase("public") || access.contains("public")) // credential.postGroupRight("all",uuid,Credential.READ,portfolioUuid,userId); } att = attribMap.getNamedItem("seenoderoles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.rd = 1; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("showtoroles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.rd = 0; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("delnoderoles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.dl = 1; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("editnoderoles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.wr = 1; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("submitnoderoles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.sb = 1; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("seeresroles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.rd = 1; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("delresroles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.dl = 1; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("editresroles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.wr = 1; resolve.groups.put(nodeRole, 0); } } att = attribMap.getNamedItem("submitresroles"); if (att != null) { StringTokenizer tokens = new StringTokenizer(att.getNodeValue(), " "); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); right r = role.getGroup(nodeRole); r.sb = 1; resolve.groups.put(nodeRole, 0); } } Node actionroles = attribMap.getNamedItem("actionroles"); if (actionroles != null) { /// Format pour l'instant: actionroles="sender:1,2;responsable:4" StringTokenizer tokens = new StringTokenizer(actionroles.getNodeValue(), ";"); while (tokens.hasMoreElements()) { nodeRole = tokens.nextElement().toString(); StringTokenizer data = new StringTokenizer(nodeRole, ":"); String nrole = data.nextElement().toString(); String actions = data.nextElement().toString().trim(); right r = role.getGroup(nrole); r.rules = actions; resolve.groups.put(nrole, 0); } } Node menuroles = attribMap.getNamedItem("menuroles"); if (menuroles != null) { /// Pour les diffrents items du menu StringTokenizer menuline = new StringTokenizer(menuroles.getNodeValue(), ";"); while (menuline.hasMoreTokens()) { String line = menuline.nextToken(); /// Format pour l'instant: mi6-parts,mission,Ajouter une mission,secret_agent StringTokenizer tokens = new StringTokenizer(line, ","); String menurolename = null; for (int t = 0; t < 4; ++t) menurolename = tokens.nextToken(); if (menurolename != null) resolve.groups.put(menurolename.trim(), 0); } } Node notifyroles = attribMap.getNamedItem("notifyroles"); if (notifyroles != null) { /// Format pour l'instant: notifyroles="sender responsable" StringTokenizer tokens = new StringTokenizer(notifyroles.getNodeValue(), " "); String merge = ""; if (tokens.hasMoreElements()) merge = tokens.nextElement().toString().trim(); while (tokens.hasMoreElements()) merge += "," + tokens.nextElement().toString().trim(); role.setNotify(merge); } // No need to set public on multiple portoflio /* meta = res.getString("metadata"); nodeString = "<?xml version='1.0' encoding='UTF-8' standalone='no'?><transfer "+meta+"/>"; is = new InputSource(new StringReader(nodeString)); doc = documentBuilder.parse(is); attribNode = doc.getDocumentElement(); attribMap = attribNode.getAttributes(); boolean isPublic = false; try { String publicatt = attribMap.getNamedItem("public").getNodeValue(); if( "Y".equals(publicatt) ) isPublic = true; } catch(Exception ex) {} setPublicState(userId, puuid, isPublic); //*/ } catch (Exception e) { e.printStackTrace(); } } res.close(); st.close(); /* sql = "SELECT grid FROM group_right_info " + "WHERE portfolio_id=uuid2bin(?)"; st = connection.prepareStatement(sql); st.setString(1, portfolioUuid); ResultSet res = st.executeQuery(); /// Pour chaque grid, on en cr un nouveau et met jour nos nouveaux droits sql = "INSERT INTO group_right_info(owner, label, change_rights, portfolio_id) " + "SELECT owner, label, change_rights, uuid2bin(?) FROM group_right_info WHERE grid=?"; st = connection.prepareStatement(sql); st.setString(1, newPortfolioUuid); if (dbserveur.equals("mysql")){ sql = "UPDATE t_rights SET grid=LAST_INSERT_ID() WHERE grid=?"; } else if (dbserveur.equals("oracle")){ sql = "UPDATE t_rights SET grid=group_right_info_SEQ.CURRVAL WHERE grid=?"; } PreparedStatement stUpd = connection.prepareStatement(sql); while( res.next() ) { int grid = res.getInt("grid"); st.setInt(2, grid); st.executeUpdate(); // Ajout du nouveau rrg stUpd.setInt(1, grid); stUpd.executeUpdate(); /// Met a jour la table de droit temporaire } st.close(); //*/ connection.setAutoCommit(false); /// On insre les donnes pr-compil Iterator<String> entries = resolve.groups.keySet().iterator(); // Cr les groupes, ils n'existent pas String grquery = "INSERT INTO group_info(grid,owner,label) " + "VALUES(?,?,?)"; PreparedStatement st2 = connection.prepareStatement(grquery); String gri = "INSERT INTO group_right_info(owner, label, change_rights, portfolio_id) " + "VALUES(?,?,?,uuid2bin(?))"; st = connection.prepareStatement(gri, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { st = connection.prepareStatement(gri, new String[] { "grid" }); } while (entries.hasNext()) { String label = entries.next(); st.setInt(1, 1); st.setString(2, label); st.setInt(3, 0); st.setString(4, newPortfolioUuid); st.execute(); ResultSet keys = st.getGeneratedKeys(); keys.next(); int grid = keys.getInt(1); resolve.groups.put(label, grid); st2.setInt(1, grid); st2.setInt(2, 1); st2.setString(3, label); st2.execute(); } /// Ajout des droits des noeuds String insertRight = "INSERT INTO group_rights(grid, id, RD, WR, DL, SB, AD, types_id, rules_id, notify_roles) " + "VALUES(?,uuid2bin(?),?,?,?,?,?,?,?,?)"; st = connection.prepareStatement(insertRight); Iterator<Entry<String, groupright>> rights = resolve.resolve.entrySet().iterator(); while (rights.hasNext()) { Entry<String, groupright> entry = rights.next(); String uuid = entry.getKey(); groupright gr = entry.getValue(); Iterator<Entry<String, right>> rightiter = gr.rights.entrySet().iterator(); while (rightiter.hasNext()) { Entry<String, right> rightelem = rightiter.next(); String group = rightelem.getKey(); int grid = resolve.groups.get(group); right rightval = rightelem.getValue(); st.setInt(1, grid); st.setString(2, uuid); st.setInt(3, rightval.rd); st.setInt(4, rightval.wr); st.setInt(5, rightval.dl); st.setInt(6, rightval.sb); st.setInt(7, rightval.ad); st.setString(8, rightval.types); st.setString(9, rightval.rules); st.setString(10, rightval.notify); st.execute(); } } /// On copie tout dans les vrai tables /// Structure sql = "INSERT INTO node(node_uuid, node_parent_uuid, node_children_uuid, node_order, metadata, metadata_wad, metadata_epm, res_node_uuid, res_res_node_uuid, res_context_node_uuid, shared_res, shared_node, shared_node_res, shared_res_uuid, shared_node_uuid, shared_node_res_uuid, asm_type, xsi_type, semtag, semantictag, label, code, descr, format, modif_user_id, modif_date, portfolio_id) " + "SELECT new_uuid, node_parent_uuid, node_children_uuid, node_order, metadata, metadata_wad, metadata_epm, res_node_uuid, res_res_node_uuid, res_context_node_uuid, shared_res, shared_node, shared_node_res, shared_res_uuid, shared_node_uuid, shared_node_res_uuid, asm_type, xsi_type, semtag, semantictag, label, code, descr, format, modif_user_id, modif_date, portfolio_id " + "FROM t_data"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); /// Resources sql = "INSERT INTO resource_table(node_uuid, xsi_type, content, user_id, modif_user_id, modif_date) " + "SELECT new_uuid, xsi_type, content, user_id, modif_user_id, modif_date " + "FROM t_res"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); /// Ajout du portfolio dans la table sql = "INSERT INTO portfolio(portfolio_id, root_node_uuid, user_id, model_id, modif_user_id, modif_date, active) " + "SELECT d.portfolio_id, d.new_uuid, p.user_id, p.model_id, p.modif_user_id, p.modif_date, p.active " + "FROM t_data d INNER JOIN portfolio p " + "ON d.node_uuid=p.root_node_uuid"; st = connection.prepareStatement(sql); st.executeUpdate(); st.close(); /// Ajout du portfolio dans le groupe de portfolio if (null == portfGroupName || "".equals(portfGroupName)) portfGroupName = "default"; sql = "INSERT INTO portfolio_group(owner, portfolio_id, group_name) VALUES(?,uuid2bin(?),?)"; st = connection.prepareStatement(sql); st.setInt(1, userId); st.setString(2, newPortfolioUuid); st.setString(3, portfGroupName); st.executeUpdate(); st.close(); /// Finalement on cre un rle designer int groupid = postCreateRole(newPortfolioUuid, "designer", userId); /// Ajoute la personne dans ce groupe putUserGroup(Integer.toString(groupid), Integer.toString(userId)); } catch (Exception e) { try { newPortfolioUuid = "erreur: " + e.getMessage(); if (connection.getAutoCommit() == false) connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { try { connection.setAutoCommit(true); // Les 'pooled connection' ne se ferment pas vraiment. On nettoie manuellement les tables temporaires... if (dbserveur.equals("mysql")) { sql = "DROP TEMPORARY TABLE IF EXISTS t_data, t_res, t_struc, t_struc_2, t_rights"; st = connection.prepareStatement(sql); st.execute(); st.close(); } connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return newPortfolioUuid; }