Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

public void addSubscriber(Subscriber subscriber) throws AppManagementException {
    Connection conn = null;//from  w  w  w .  j a  v  a  2 s  .co  m
    ResultSet rs = null;
    PreparedStatement ps = null;
    try {
        conn = APIMgtDBUtil.getConnection();
        String query = "INSERT INTO APM_SUBSCRIBER (USER_ID, TENANT_ID, EMAIL_ADDRESS, "
                + "DATE_SUBSCRIBED) VALUES (?,?,?,?)";

        ps = conn.prepareStatement(query, new String[] { "subscriber_id" });

        ps.setString(1, subscriber.getName());
        ps.setInt(2, subscriber.getTenantId());
        ps.setString(3, subscriber.getEmail());
        ps.setTimestamp(4, new Timestamp(subscriber.getSubscribedDate().getTime()));
        ps.executeUpdate();

        int subscriberId = 0;
        rs = ps.getGeneratedKeys();
        if (rs.next()) {
            subscriberId = Integer.valueOf(rs.getString(1)).intValue();
        }
        subscriber.setId(subscriberId);

        // Add default application
        Application defaultApp = new Application(AppMConstants.DEFAULT_APPLICATION_NAME, subscriber);
        defaultApp.setTier(AppMConstants.UNLIMITED_TIER);
        addApplication(defaultApp, subscriber.getName(), conn);

        conn.commit();
    } catch (SQLException e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.error("Error while rolling back the failed operation", e);
            }
        }
        handleException("Error in adding new subscriber: " + e.getMessage(), e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
    }
}

From source file:com.portfolio.data.provider.MysqlAdminProvider.java

@Override
public Integer postCreateMacro(int userId, String macroName) {
    // Cration d'une nouvelle macro
    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String sql = "";
    PreparedStatement st;
    Integer output = 0;/*from w ww.  j a v  a 2s  .  c  o  m*/

    try {
        sql = "INSERT INTO rule_info(label) VALUE(?)";
        st = connection.prepareStatement(sql);
        st.setString(1, macroName);
        st.executeUpdate();

        ResultSet rs = st.getGeneratedKeys();
        if (rs.next())
            output = rs.getInt(1);

        st.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return output;
}

From source file:com.portfolio.data.provider.MysqlAdminProvider.java

/************************************/

@Override/*from   w  w  w. jav a  2 s  . co  m*/
public String postCreateType(int userId, String name) {
    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String sql = "";
    PreparedStatement st;
    Integer output = 0;

    try {
        sql = "INSERT INTO definition_info(label) VALUE(?)";
        st = connection.prepareStatement(sql);
        st.setString(1, name);
        st.executeUpdate();

        ResultSet rs = st.getGeneratedKeys();
        if (rs.next())
            output = rs.getInt(1);

        st.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return output.toString();
}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * Save the entitlement policy partial//from  w  w  w.ja  v  a  2s.co m
 *
 * @param policyPartialName policy partial name
 * @param policyPartial     policy partial content
 * @param isSharedPartial   is policy partial shared
 * @param policyAuthor      author of the policy partial
 * @param tenantId          logged users tenant Id
 * @return policy partial id
 * @throws org.wso2.carbon.appmgt.api.AppManagementException
 */
public int saveEntitlementPolicyPartial(String policyPartialName, String policyPartial, boolean isSharedPartial,
        String policyAuthor, String policyPartialDesc, int tenantId) throws AppManagementException {

    Connection connection = null;
    PreparedStatement statementToInsertRecord = null;

    int policyPartialId = -1;

    try {

        if (log.isDebugEnabled()) {
            log.debug("Saves Entitlement Policy Partial with name : " + policyPartialName + " from author : "
                    + policyAuthor);
        }
        connection = APIMgtDBUtil.getConnection();
        String queryToInsertRecord = "INSERT INTO "
                + "APM_ENTITLEMENT_POLICY_PARTIAL(NAME,CONTENT,SHARED,AUTHOR,DESCRIPTION,TENANT_ID)"
                + " VALUES (?,?,?,?,?,?)";

        statementToInsertRecord = connection.prepareStatement(queryToInsertRecord,
                new String[] { "ENTITLEMENT_POLICY_PARTIAL_ID" });
        statementToInsertRecord.setString(1, policyPartialName);
        statementToInsertRecord.setString(2, policyPartial);
        statementToInsertRecord.setBoolean(3, isSharedPartial);
        statementToInsertRecord.setString(4, policyAuthor);
        statementToInsertRecord.setString(5, policyPartialDesc);
        statementToInsertRecord.setInt(6, tenantId);

        statementToInsertRecord.executeUpdate();

        ResultSet rs = statementToInsertRecord.getGeneratedKeys();
        while (rs.next()) {
            policyPartialId = Integer.parseInt(rs.getString(1));
        }
        rs.close();

        // Finally commit transaction.
        connection.commit();

    } catch (SQLException e) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback the add entitlement policy partial with name : "
                        + policyPartialName + " from author : " + policyAuthor, e1);
            }
        }
        handleException("Failed to add entitlement policy partial with name : " + policyPartialName
                + " from author : " + policyAuthor, e);
    } finally {
        APIMgtDBUtil.closeAllConnections(statementToInsertRecord, connection, null);
    }
    return policyPartialId;
}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * Save business owner./*  w w w  .  j a  v a 2s. com*/
 *
 * @param businessOwner {@link BusinessOwner} object
 * @param tenantId      Tenant id
 * @return Saved business owner id
 * @throws AppManagementException on error while trying to save business owner
 */
public int saveBusinessOwner(BusinessOwner businessOwner, int tenantId) throws AppManagementException {
    Connection connection = null;
    PreparedStatement statementToInsertBusinessOwner = null;
    PreparedStatement statementToInsertBusinessOwnerDetails = null;
    String businessOwnerName = "";
    int businessOwnerId = 0;
    try {
        businessOwnerName = businessOwner.getBusinessOwnerName();
        if (log.isDebugEnabled()) {
            log.debug("Adding business owner : " + businessOwnerName);
        }
        connection = APIMgtDBUtil.getConnection();
        connection.setAutoCommit(false);
        String queryToInsertRecord = "INSERT INTO APM_BUSINESS_OWNER(OWNER_NAME,OWNER_EMAIL,OWNER_DESC,"
                + "OWNER_SITE, TENANT_ID) VALUES (?,?,?,?,?)";

        statementToInsertBusinessOwner = connection.prepareStatement(queryToInsertRecord,
                new String[] { "OWNER_ID" });
        statementToInsertBusinessOwner.setString(1, businessOwnerName);
        statementToInsertBusinessOwner.setString(2, businessOwner.getBusinessOwnerEmail());
        statementToInsertBusinessOwner.setString(3, businessOwner.getBusinessOwnerDescription());
        statementToInsertBusinessOwner.setString(4, businessOwner.getBusinessOwnerSite());
        statementToInsertBusinessOwner.setInt(5, tenantId);
        int affectedRows = statementToInsertBusinessOwner.executeUpdate();

        if (affectedRows == 0) {
            throw new AppManagementException(
                    "Saving business owner user : " + businessOwnerName + " is failed, no " + "rows affected.");
        }
        ResultSet generatedKeys = statementToInsertBusinessOwner.getGeneratedKeys();
        if (generatedKeys.next()) {
            businessOwnerId = generatedKeys.getInt(1);
        }

        String queryToInsertRecordTwo = "INSERT INTO APM_BUSINESS_OWNER_PROPERTY(OWNER_ID, NAME, VALUE, SHOW_IN_STORE) VALUES(?,?,?,?)";
        statementToInsertBusinessOwnerDetails = connection.prepareStatement(queryToInsertRecordTwo);
        List<BusinessOwnerProperty> businessOwnerPropertiesList = businessOwner
                .getBusinessOwnerPropertiesList();
        if (businessOwnerPropertiesList != null) {
            for (BusinessOwnerProperty businessOwnerProperties : businessOwnerPropertiesList) {
                String propertyId = businessOwnerProperties.getPropertyKey();
                if (!StringUtils.isEmpty(propertyId)) {
                    statementToInsertBusinessOwnerDetails.setInt(1, businessOwnerId);
                    statementToInsertBusinessOwnerDetails.setString(2, propertyId);
                    statementToInsertBusinessOwnerDetails.setString(3,
                            businessOwnerProperties.getPropertyValue());
                    statementToInsertBusinessOwnerDetails.setBoolean(4,
                            businessOwnerProperties.isShowingInStore());
                    statementToInsertBusinessOwnerDetails.executeUpdate();
                }
            }
        }
        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                log.error("Failed the rollback transaction of saving business owner with name : "
                        + businessOwnerName, e1);
            }
        }
        handleException("Failed to save business owner with name : " + businessOwnerName, e);
    } finally {
        APIMgtDBUtil.closeAllConnections(statementToInsertBusinessOwner, connection, null);
    }
    return businessOwnerId;
}

From source file:org.osaf.cosmo.migrate.ZeroPointSixZeroOneToZeroPointSixOneMigration.java

private void migrateEvents(Connection conn, String dialect) throws Exception {
    PreparedStatement stmt = null;
    PreparedStatement insertStampStmt1 = null;
    PreparedStatement insertStampStmt2 = null;
    PreparedStatement insertItemStmt1 = null;
    PreparedStatement insertItemStmt2 = null;
    PreparedStatement insertParentStmt = null;
    PreparedStatement insertEventStmt = null;
    PreparedStatement insertAttributeStmt1 = null;
    PreparedStatement insertAttributeStmt2 = null;
    PreparedStatement updateEventStmt = null;
    PreparedStatement selectParentsStmt = null;

    ResultSet rs = null;//from  ww w.j ava  2s.  c  o  m
    long count = 0;
    long modCount = 0;
    long modProblemCount = 0;
    long duplicateModCount = 0;
    long badEventCount = 0;
    long validationErrorCount = 0;

    System.setProperty("ical4j.unfolding.relaxed", "true");
    CalendarBuilder calBuilder = new CalendarBuilder();

    log.debug("begin migrateEvents()");

    try {
        stmt = conn.prepareStatement(
                "select i.id, i.ownerid, i.icaluid, es.icaldata, i.displayName, i.uid, s.id from item i, stamp s, event_stamp es where i.id=s.itemid and s.id=es.stampid");

        insertItemStmt1 = conn.prepareStatement(
                "insert into item (itemtype, ownerid, modifiesitemid, itemname, displayname, version, uid, icaluid, isactive, createdate, modifydate, isautotriage) values (?,?,?,?,?,0,?,?,1,?,?,1)");
        insertItemStmt2 = conn.prepareStatement(
                "insert into item (itemtype, ownerid, modifiesitemid, itemname, displayname, version, uid, icaluid, isactive, createdate, modifydate, id, isautotriage) values (?,?,?,?,?,0,?,?,1,?,?,?,1)");
        insertItemStmt1.setString(1, "note");
        insertItemStmt2.setString(1, "note");

        insertItemStmt1.setLong(8, System.currentTimeMillis());
        insertItemStmt2.setLong(8, System.currentTimeMillis());

        insertItemStmt1.setLong(9, System.currentTimeMillis());
        insertItemStmt2.setLong(9, System.currentTimeMillis());

        insertStampStmt1 = conn.prepareStatement(
                "insert into stamp (stamptype, itemid, createdate, modifydate, isactive) values (?,?,?,?,1)");
        insertStampStmt1.setString(1, "eventexception");
        insertStampStmt1.setLong(3, System.currentTimeMillis());
        insertStampStmt1.setLong(4, System.currentTimeMillis());
        insertStampStmt2 = conn.prepareStatement(
                "insert into stamp (stamptype, itemid, id, createdate, modifydate, isactive) values (?,?,?,?,?,1)");
        insertStampStmt2.setString(1, "eventexception");
        insertStampStmt2.setLong(4, System.currentTimeMillis());
        insertStampStmt2.setLong(5, System.currentTimeMillis());

        insertAttributeStmt1 = conn.prepareStatement(
                "insert into attribute (attributetype, namespace, localname, itemid, textvalue) values (?,?,?,?,?)");
        insertAttributeStmt2 = conn.prepareStatement(
                "insert into attribute (attributetype, namespace, localname, itemid, textvalue, id) values (?,?,?,?,?,?)");
        insertAttributeStmt1.setString(1, "text");
        insertAttributeStmt2.setString(1, "text");
        insertAttributeStmt1.setString(2, "org.osaf.cosmo.model.NoteItem");
        insertAttributeStmt2.setString(2, "org.osaf.cosmo.model.NoteItem");
        insertAttributeStmt1.setString(3, "body");
        insertAttributeStmt2.setString(3, "body");

        updateEventStmt = conn.prepareStatement("update event_stamp set icaldata=? where stampid=?");

        insertEventStmt = conn.prepareStatement("insert into event_stamp (stampid, icaldata) values (?,?)");

        insertParentStmt = conn
                .prepareStatement("insert into collection_item (collectionid, itemid) values (?,?)");

        selectParentsStmt = conn.prepareStatement("select collectionid from collection_item where itemid=?");

        rs = stmt.executeQuery();

        while (rs.next()) {
            count++;
            long itemId = rs.getLong(1);
            long ownerId = rs.getLong(2);
            String icalUid = rs.getString(3);
            String icalData = rs.getString(4);
            String displayName = rs.getString(5);
            String parentUid = rs.getString(6);
            long stampId = rs.getLong(7);
            Calendar calendar = null;

            // Because we are using an updated ical4j, there might
            // be cases where parsing will fail.  If this happens,
            // the server will fail when loading the calendar, so
            // we have to fix it.  For now, fix by creating a replacement
            // calendar.
            try {
                calendar = calBuilder.build(new StringReader(icalData));
            } catch (ParserException e) {
                badEventCount++;
                log.debug("cannot parse .ics for item " + itemId);
                log.debug("parse error for:  " + icalData);
                log.debug("error: " + e.getMessage());
                calendar = createReplacementCalendar(icalUid, e.getMessage());
                calendar.validate();
                updateEventStmt.setString(1, calendar.toString());
                updateEventStmt.setLong(2, stampId);
                updateEventStmt.executeUpdate();
                log.debug("replaced with: " + calendar.toString());
                continue;
            }

            try {
                calendar.validate();
            } catch (ValidationException ve) {
                validationErrorCount++;
            }

            ComponentList comps = calendar.getComponents().getComponents(Component.VEVENT);
            Vector<VEvent> mods = new Vector<VEvent>();

            // find event exceptions
            for (Iterator<VEvent> it = comps.iterator(); it.hasNext();) {
                VEvent event = it.next();
                if (event.getRecurrenceId() != null && !"".equals(event.getRecurrenceId().getValue()))
                    mods.add(event);
            }

            // if no event exceptions, no migration needed
            if (mods.size() == 0)
                continue;

            modCount++;
            HashMap<String, VEvent> exceptionMap = new HashMap<String, VEvent>();

            boolean hasDuplicateMods = false;

            // Add item for each event exception
            for (VEvent mod : mods) {
                calendar.getComponents().remove(mod);
                Calendar modCalendar = createBaseCalendar(mod);
                long newItemId = 0;
                RecurrenceId recurrenceId = mod.getRecurrenceId();
                Property summary = mod.getProperties().getProperty(Property.SUMMARY);
                Property description = mod.getProperties().getProperty(Property.DESCRIPTION);
                String eventSummary = null;
                String eventDescription = null;
                String uid = parentUid + ":" + fromDateToStringNoTimezone(recurrenceId.getDate());

                if (exceptionMap.containsKey(uid)) {
                    if (!hasDuplicateMods) {
                        hasDuplicateMods = true;
                        modProblemCount++;
                    }
                    duplicateModCount++;
                    log.debug("already processed exception " + uid + " skipping...");
                    continue;
                }

                exceptionMap.put(uid, mod);

                if (summary != null)
                    eventSummary = summary.getValue();

                // Make sure we can fit summary in displayname column
                if (eventSummary != null && eventSummary.length() >= 255)
                    eventSummary = eventSummary.substring(0, 254);

                if (description != null)
                    eventDescription = description.getValue();

                String itemName = uid;

                insertItemStmt1.setLong(2, ownerId);
                insertItemStmt2.setLong(2, ownerId);

                insertItemStmt1.setLong(3, itemId);
                insertItemStmt2.setLong(3, itemId);

                insertItemStmt1.setString(4, itemName);
                insertItemStmt2.setString(4, itemName);

                if (eventSummary != null) {
                    insertItemStmt1.setString(5, eventSummary);
                    insertItemStmt2.setString(5, eventSummary);
                } else {
                    insertItemStmt1.setNull(5, Types.VARCHAR);
                    insertItemStmt2.setNull(5, Types.VARCHAR);
                }

                insertItemStmt1.setString(6, uid);
                insertItemStmt2.setString(6, uid);

                insertItemStmt1.setString(7, icalUid);
                insertItemStmt2.setString(7, icalUid);

                // insert item
                if ("Derby".equals(dialect)) {
                    newItemId = hibernateHelper.getNexIdUsingHiLoGenerator(conn);
                    insertItemStmt2.setLong(10, newItemId);
                    insertItemStmt2.executeUpdate();
                } else {
                    insertItemStmt1.executeUpdate();
                    ResultSet generatedKeysRs = insertItemStmt1.getGeneratedKeys();
                    generatedKeysRs.next();
                    newItemId = generatedKeysRs.getLong(1);
                    generatedKeysRs.close();
                }

                // insert parents
                selectParentsStmt.setLong(1, itemId);
                ResultSet parentRs = selectParentsStmt.executeQuery();
                while (parentRs.next()) {
                    long parentId = parentRs.getLong(1);
                    insertParentStmt.setLong(1, parentId);
                    insertParentStmt.setLong(2, newItemId);
                    insertParentStmt.executeUpdate();
                }

                // insert attribute for Note body
                if (eventDescription != null) {
                    if ("MySQL5".equals(dialect)) {
                        insertAttributeStmt1.setLong(4, newItemId);
                        insertAttributeStmt1.setString(5, eventDescription);
                        insertAttributeStmt1.executeUpdate();
                    } else {
                        long attributeId = hibernateHelper.getNexIdUsingHiLoGenerator(conn);
                        insertAttributeStmt2.setLong(4, newItemId);
                        insertAttributeStmt2.setString(5, eventDescription);
                        insertAttributeStmt2.setLong(6, attributeId);
                        insertAttributeStmt2.executeUpdate();
                    }
                }

                // insert stamp for event exception
                long newStampId = 0;
                if ("MySQL5".equals(dialect)) {
                    insertStampStmt1.setLong(2, newItemId);
                    insertStampStmt1.executeUpdate();
                    ResultSet generatedKeysRs = insertStampStmt1.getGeneratedKeys();
                    generatedKeysRs.next();
                    newStampId = generatedKeysRs.getLong(1);
                    generatedKeysRs.close();
                } else {
                    newStampId = hibernateHelper.getNexIdUsingHiLoGenerator(conn);
                    insertStampStmt2.setLong(2, newItemId);
                    insertStampStmt2.setLong(3, newStampId);
                    insertStampStmt2.executeUpdate();
                }

                // insert event_stamp
                insertEventStmt.setLong(1, newStampId);
                insertEventStmt.setString(2, modCalendar.toString());
                insertEventStmt.executeUpdate();
            }

            // update event_stamp for master event
            updateEventStmt.setString(1, calendar.toString());
            updateEventStmt.setLong(2, stampId);
            updateEventStmt.executeUpdate();
        }

    } finally {
        if (rs != null)
            rs.close();

        if (stmt != null)
            stmt.close();

        if (insertStampStmt1 != null)
            insertStampStmt1.close();

        if (insertStampStmt2 != null)
            insertStampStmt2.close();

        if (insertAttributeStmt1 != null)
            insertAttributeStmt1.close();

        if (insertAttributeStmt2 != null)
            insertAttributeStmt2.close();

        if (insertItemStmt1 != null)
            insertItemStmt1.close();

        if (insertParentStmt != null)
            insertParentStmt.close();

        if (updateEventStmt != null)
            updateEventStmt.close();

        if (insertEventStmt != null)
            insertEventStmt.close();

        if (selectParentsStmt != null)
            selectParentsStmt.close();

    }

    log.debug("processed " + count + " events");
    log.debug(modCount + " events had event exceptions");
    log.debug(duplicateModCount + " exceptions ignored due to duplicates");
    log.debug(modProblemCount + " events contain duplicate exceptions");
    log.debug(badEventCount + " bad events encountered");
    log.debug(validationErrorCount + " events failed validation");
}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

/**
 * @param application/*from   w ww. j ava  2s  .  c  om*/
 *            Application
 * @param userId
 *            User Id
 * @throws org.wso2.carbon.appmgt.api.AppManagementException
 *             if failed to add Application
 */
public int addApplication(Application application, String userId, Connection conn)
        throws AppManagementException, SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    int applicationId = 0;
    try {
        int tenantId;

        try {
            tenantId = IdentityTenantUtil.getTenantIdOfUser(userId);
        } catch (IdentityRuntimeException e) {
            String msg = "Failed to get tenant id of user : " + userId;
            log.error(msg, e);
            throw new AppManagementException(msg, e);
        }
        // Get subscriber Id
        Subscriber subscriber = getSubscriber(userId, tenantId, conn);
        if (subscriber == null) {
            String msg = "Could not load Subscriber records for: " + userId;
            log.error(msg);
            throw new AppManagementException(msg);
        }
        // This query to update the APM_APPLICATION table
        String sqlQuery = "INSERT "
                + "INTO APM_APPLICATION (NAME, SUBSCRIBER_ID, APPLICATION_TIER, CALLBACK_URL, DESCRIPTION, APPLICATION_STATUS)"
                + " VALUES (?,?,?,?,?,?)";
        // Adding data to the APM_APPLICATION table
        // ps = conn.prepareStatement(sqlQuery);
        ps = conn.prepareStatement(sqlQuery, new String[] { "APPLICATION_ID" });
        if (conn.getMetaData().getDriverName().contains("PostgreSQL")) {
            ps = conn.prepareStatement(sqlQuery, new String[] { "application_id" });
        }

        ps.setString(1, application.getName());
        ps.setInt(2, subscriber.getId());
        ps.setString(3, application.getTier());
        ps.setString(4, application.getCallbackUrl());
        ps.setString(5, application.getDescription());

        if (application.getName().equals(AppMConstants.DEFAULT_APPLICATION_NAME)) {
            ps.setString(6, AppMConstants.ApplicationStatus.APPLICATION_APPROVED);
        } else {
            ps.setString(6, AppMConstants.ApplicationStatus.APPLICATION_CREATED);
        }
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        while (rs.next()) {
            applicationId = Integer.parseInt(rs.getString(1));
        }

        ps.close();
    } catch (SQLException e) {
        handleException("Failed to add Application", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, null, rs);
    }
    return applicationId;

}

From source file:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

public int addSubscription(APIIdentifier identifier, String subscriptionType, String context, int applicationId,
        String status, String trustedIdps) throws AppManagementException {

    Connection conn = null;//w  w w .j  a  v a2s  . co m
    ResultSet resultSet = null;
    ResultSet rs = null;
    PreparedStatement ps = null;
    int subscriptionId = -1;
    int apiId = -1;

    try {
        conn = APIMgtDBUtil.getConnection();
        String getApiQuery = "SELECT APP_ID FROM APM_APP API WHERE APP_PROVIDER = ? AND APP_NAME = ? AND "
                + "APP_VERSION = ?";
        ps = conn.prepareStatement(getApiQuery);
        ps.setString(1, AppManagerUtil.replaceEmailDomainBack(identifier.getProviderName()));
        ps.setString(2, identifier.getApiName());
        ps.setString(3, identifier.getVersion());
        resultSet = ps.executeQuery();
        if (resultSet.next()) {
            apiId = resultSet.getInt("APP_ID");
        }
        ps.close();

        if (apiId == -1) {
            String msg = "Unable to get the WebApp ID for: " + identifier;
            log.error(msg);
            throw new AppManagementException(msg);
        }

        // This query to update the APM_SUBSCRIPTION table
        String sqlQuery = "INSERT INTO APM_SUBSCRIPTION (TIER_ID,SUBSCRIPTION_TYPE, APP_ID, "
                + "APPLICATION_ID,SUB_STATUS, TRUSTED_IDP, SUBSCRIPTION_TIME) " + "VALUES (?,?,?,?,?,?,?)";

        // Adding data to the APM_SUBSCRIPTION table
        // ps = conn.prepareStatement(sqlQuery,
        // Statement.RETURN_GENERATED_KEYS);
        ps = conn.prepareStatement(sqlQuery, new String[] { "SUBSCRIPTION_ID" });
        if (conn.getMetaData().getDriverName().contains("PostgreSQL")) {
            ps = conn.prepareStatement(sqlQuery, new String[] { "subscription_id" });
        }

        byte count = 0;
        ps.setString(++count, identifier.getTier());
        ps.setString(++count, subscriptionType);
        ps.setInt(++count, apiId);
        ps.setInt(++count, applicationId);
        ps.setString(++count, status != null ? status : AppMConstants.SubscriptionStatus.UNBLOCKED);
        ps.setString(++count, trustedIdps);
        ps.setTimestamp(++count, new Timestamp(new java.util.Date().getTime()));

        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        while (rs.next()) {
            // subscriptionId = rs.getInt(1);
            subscriptionId = Integer.valueOf(rs.getString(1)).intValue();
        }
        ps.close();

        // 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 ", e);
            }
        }
        handleException("Failed to add subscriber data ", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, resultSet);
        APIMgtDBUtil.closeAllConnections(null, null, rs);
    }
    return subscriptionId;
}

From source file:com.portfolio.data.provider.MysqlAdminProvider.java

@Override
public String postRRGCreate(int userId, String data) {
    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String value = "erreur";
    /// Parse data
    DocumentBuilder documentBuilder;
    Document document = null;//from w w w  .  j  a  va  2  s.  co m
    try {
        DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
        documentBuilder = documentBuilderFactory.newDocumentBuilder();
        InputSource is = new InputSource(new StringReader(data));
        document = documentBuilder.parse(is);
    } catch (Exception e) {
        e.printStackTrace();
    }

    /// Problme de parsage
    if (document == null)
        return value;

    try {
        connection.setAutoCommit(false);
        NodeList rrgNodes = document.getElementsByTagName("rolerightsgroup");

        String sqlRRG = "INSERT INTO group_right_info(owner,label,portfolio_id) VALUES(?,?,uuid2bin(?))";
        PreparedStatement rrgst = connection.prepareStatement(sqlRRG);
        rrgst.setInt(1, userId);
        String sqlGU = "INSERT INTO group_info(grid,owner,label) VALUES(?,?,?)";
        PreparedStatement gust = connection.prepareStatement(sqlGU);
        gust.setInt(2, userId);
        for (int i = 0; i < rrgNodes.getLength(); ++i) {
            Element rrgNode = (Element) rrgNodes.item(i);
            NodeList labelNodes = rrgNode.getElementsByTagName("label");
            Node labelNode = labelNodes.item(0);
            String label = "";
            if (labelNode != null) {
                Node labelText = labelNode.getFirstChild();
                if (labelText != null)
                    label = labelText.getNodeValue();
            }

            NodeList portfolioNodes = rrgNode.getElementsByTagName("portfolio");
            Element portfolioNode = (Element) portfolioNodes.item(0);
            String pid = "";
            if (portfolioNode != null)
                pid = portfolioNode.getAttribute("id");

            /// Cration du groupe de droit
            rrgst.setString(2, label);
            rrgst.setString(3, pid);
            rrgst.executeUpdate();

            /// Rcupre l'identifiant du RRG,  utiliser lors de l'ajout d'utilisateurs
            ResultSet rs = rrgst.getGeneratedKeys();
            Integer rrgid = 0;
            if (rs.next())
                rrgid = rs.getInt(1);
            rs.close();
            /// Met  jour le noeud pour plus tard
            rrgNode.setAttribute("id", rrgid.toString());

            /// Cration du groupe d'utilisateur
            gust.setInt(1, rrgid);
            gust.setString(3, label);
            gust.executeUpdate();
            rs = gust.getGeneratedKeys();
            Integer gid = 0;
            if (rs.next())
                gid = rs.getInt(1);
            rs.close();

            /// Ajout des utilisateurs au groupe d'utilisateur
            NodeList users = rrgNode.getElementsByTagName("user");
            String sqlUser = "INSERT INTO group_user(gid,userid) VALUES(?,?)";
            PreparedStatement st = connection.prepareStatement(sqlUser);
            st.setInt(1, gid);
            for (int j = 0; j < users.getLength(); ++j) {
                Element user = (Element) users.item(j);
                String uidl = user.getAttribute("id");
                Integer uid = Integer.valueOf(uidl);
                st.setInt(2, uid);
                st.executeUpdate();
            }
            st.close();
        }
        rrgst.close();

        /// Rcupre les donnes avec identifiant mis--jour
        StringWriter stw = new StringWriter();
        Transformer serializer = TransformerFactory.newInstance().newTransformer();
        DOMSource source = new DOMSource(document);
        StreamResult stream = new StreamResult(stw);
        serializer.transform(source, stream);
        value = stw.toString();
    } 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 value;
}

From source file:com.portfolio.data.provider.MysqlAdminProvider.java

@Override
public String postRoleUser(int userId, int grid, Integer userid2) throws SQLException {
    // TODO Auto-generated method stub

    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String label = null;/*from  w w w . j  a v  a  2s  .  c o 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();

    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");
        }

        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);
        }

        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;
}