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:com.portfolio.data.provider.MysqlAdminProvider.java

@Override
public String postAddNodeType(int userId, Integer type, Integer nodeid, Integer parentid, Integer instance,
        String data) {//  w  w  w.j  a  va  2 s  .  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 rcupre 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:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java

public void addWebApp(WebApp app) throws AppManagementException {
    Connection connection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;/*from   w  w  w. j  a  va 2 s.c o m*/
    String businessOwnerName = app.getBusinessOwner();
    String query = "INSERT INTO APM_APP(APP_PROVIDER, TENANT_ID, APP_NAME, APP_VERSION, CONTEXT, TRACKING_CODE, "
            + "VISIBLE_ROLES, UUID, SAML2_SSO_ISSUER, LOG_OUT_URL,APP_ALLOW_ANONYMOUS, APP_ENDPOINT, TREAT_AS_SITE) "
            + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";

    try {
        String gatewayURLs = ServiceReferenceHolder.getInstance().getAPIManagerConfigurationService()
                .getAPIManagerConfiguration().getFirstProperty(GATEWAY_URL);
        String[] urlArray = gatewayURLs.split(",");
        String prodURL = urlArray[0];
        String logoutURL = app.getLogoutURL();
        if (logoutURL != null && !"".equals(logoutURL.trim())) {
            logoutURL = prodURL.concat(app.getContext()).concat("/" + app.getId().getVersion() + "/")
                    .concat(logoutURL);
        }

        int tenantId;
        String tenantDomain = MultitenantUtils
                .getTenantDomain(AppManagerUtil.replaceEmailDomainBack(app.getId().getProviderName()));
        try {
            tenantId = ServiceReferenceHolder.getInstance().getRealmService().getTenantManager()
                    .getTenantId(tenantDomain);
        } catch (UserStoreException e) {
            throw new AppManagementException(
                    "Error in retrieving Tenant Information while adding app :" + app.getId().getApiName(), e);
        }

        connection = APIMgtDBUtil.getConnection();
        connection.setAutoCommit(false);
        prepStmt = connection.prepareStatement(query, new String[] { "APP_ID" });
        prepStmt.setString(1, AppManagerUtil.replaceEmailDomainBack(app.getId().getProviderName()));
        prepStmt.setInt(2, tenantId);
        prepStmt.setString(3, app.getId().getApiName());
        prepStmt.setString(4, app.getId().getVersion());
        prepStmt.setString(5, app.getContext());
        prepStmt.setString(6, app.getTrackingCode());
        prepStmt.setString(7, app.getVisibleRoles());
        prepStmt.setString(8, app.getUUID());
        prepStmt.setString(9, app.getSaml2SsoIssuer());
        prepStmt.setString(10, logoutURL);
        prepStmt.setBoolean(11, app.getAllowAnonymous());
        prepStmt.setString(12, app.getUrl());
        prepStmt.setBoolean(13, Boolean.parseBoolean(app.getTreatAsASite()));

        prepStmt.execute();

        rs = prepStmt.getGeneratedKeys();
        int webAppId = -1;
        if (rs.next()) {
            webAppId = rs.getInt(1);
        }
        addURLTemplates(webAppId, app, connection);
        //Set default versioning details
        saveDefaultVersionDetails(app, connection);

        recordAPILifeCycleEvent(app.getId(), null, APIStatus.CREATED,
                AppManagerUtil.replaceEmailDomainBack(app.getId().getProviderName()), connection);
        if (app.getPolicyPartials() != null && !app.getPolicyPartials().isEmpty()) {
            JSONArray policyPartialIdList = (JSONArray) JSONValue.parse(app.getPolicyPartials());
            saveApplicationPolicyPartialsMappings(connection, webAppId, policyPartialIdList.toArray());
        }

        //save policy groups app wise
        if (app.getPolicyGroups() != null && !app.getPolicyGroups().isEmpty()) {
            JSONArray policyGroupIdList = (JSONArray) JSONValue.parse(app.getPolicyGroups());
            saveApplicationPolicyGroupsMappings(connection, webAppId, policyGroupIdList.toArray());
        }

        //save java policies app wise
        if (app.getJavaPolicies() != null && !app.getJavaPolicies().isEmpty()) {
            JSONArray javaPolicyIdList = (JSONArray) JSONValue.parse(app.getJavaPolicies());
            saveJavaPolicyMappings(connection, webAppId, javaPolicyIdList.toArray());
        }

        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback when adding the WebApp: " + app.getId() + " to the database", e);
            }
        }
        handleException("Error while adding the WebApp: " + app.getId() + " to the database", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(prepStmt, connection, rs);
    }
}

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

@Override
public Object putRole(String xmlRole, int userId, int roleId) throws Exception {
    // TODO Auto-generated method stub

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

    String result = null;/*from   w w w .j a v  a  2 s  .c  om*/
    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) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    result = "" + id;

    return result;
}

From source file:com.iana.boesc.dao.BOESCDaoImpl.java

@Override
public boolean getPopulatedDataAndInsert(EDI322Bean eb, String boescUserId, String userType, File file,
        List<String> finalErrorList, Map<Integer, Object> fileTransStatus) throws Exception {
    logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId
            + " userType ::" + userType);
    String status = "";
    logger.info("----- ISA  ............");
    String gsHeader = "";
    String gsControl = "";
    String st_control = "";
    String n7Header = "";
    String q5Header = "";
    String equip_prefix = "";
    String equip_number = "";
    String w2Header = "";
    String r4Header = "";
    String r13Header = "";
    String n9Header = "";
    String eventType = "";
    String port_qual = "";
    String iana_splc = "";

    QueryRunner qrun = new QueryRunner(getDataSource());
    logger.info("----- GE  ............");
    for (int i = 0; i < eb.getListGSDetails().size(); i++) {
        gsHeader = eb.getListGSDetails().get(i).getHeaderDetails();
        gsControl = eb.getListGSDetails().get(i).getGroupControlNumber();
        logger.info("gsControl ::" + gsControl + " gsHeader ::" + gsHeader);
        int startIndex = i + 1;
        logger.info("----- ST & SE  ............");
        for (int a = 0; a < eb.getSTDetailsMap().get(startIndex).size(); a++) {

            Connection conn = getConnection();
            conn.setAutoCommit(false);//w w  w  .j  a v  a  2 s  .co m
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            StringBuilder sbQuery = new StringBuilder(
                    "INSERT INTO BOESC_TRAN_SET (ISA_HEADER, GS_HEADER, INPUT_TYPE, SENDER_ID, SENDER_TYPE, ");
            sbQuery.append(
                    " ISA_DATETIME, GS_CONTROL, ST_CONTROL, EVENT_TYPE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, IEP_DOT, PORT_QUAL, IANA_SPLC, ");
            sbQuery.append(" POOL_ID, POOL_NAME, Q5_SEG, N7_SEG, W2_SEG, R4_SEG, N9_SEG, R13_SEG, ");

            if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) {
                sbQuery.append(" RECEIVER_ID, REC_STATUS   ");
            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) {
                sbQuery.append(" MRV_ID, MRV_STATUS   ");
            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) {
                sbQuery.append(" FO_ID, FO_STATUS ");

            } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_SU)) {

            }

            try {
                status = "";
                int changedIndex = a + 1; //very important Variable
                if (fileTransStatus != null && fileTransStatus.size() > 0) {
                    logger.info("-------------------- changedIndex ::" + changedIndex
                            + " fileTransStatus.get(startIndex)  ::" + fileTransStatus.get(changedIndex));
                    if (fileTransStatus.get(changedIndex) == null) {
                        status = GlobalVariables.STATUS_PENDING;
                    } else {
                        status = GlobalVariables.STATUS_REJECTED;
                    }
                } else {
                    status = GlobalVariables.STATUS_PENDING;
                }

                r13Header = "";
                r4Header = "";
                n9Header = "";
                port_qual = "";
                iana_splc = "";
                GIERInfoDetails gierInfo = null;

                st_control = eb.getSTDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber();
                logger.info("  st_control :" + st_control);

                /*String transactionControlNumberSE  = eb.getSEDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber();
                logger.info("  transactionControlNumberSE :"+transactionControlNumberSE );*/

                logger.info("----- N7  ............");

                for (int q = 0; q < eb.getN7DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    n7Header = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    logger.info("n7Header ::" + n7Header);
                    equip_prefix = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getEquipmentInitial();
                    equip_number = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getEquipmentNumber();

                    logger.info("equip_prefix ::" + equip_prefix);
                    logger.info("equip_number ::" + equip_number);
                    equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? ""
                            : equip_prefix;
                    equip_number = equip_number == null || equip_number.trim().length() == 0 ? ""
                            : equip_number;
                    gierInfo = getDVIRAdditionaldetails(equip_prefix, equip_number);
                    //logger.info("gierInfo ::"+gierInfo);

                }

                logger.info("----- Q5  ............");
                for (int q = 0; q < eb.getQ5DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    q5Header = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    eventType = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getStatusCode();
                    logger.info("q5Header ::" + q5Header + " eventType ::" + eventType);
                }

                logger.info("----- W2  ............");
                for (int q = 0; q < eb.getW2DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    w2Header = eb.getW2DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails();
                    logger.info("w2Header ::" + w2Header);
                }

                logger.info("----- R4  ............");
                String tempR4Header = "";
                String tempPort_qual = "";
                String tempIana_splc = "";
                for (int q = 0; q < eb.getR4DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempR4Header = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    tempPort_qual = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getLocationQualifier();
                    tempIana_splc = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getLocationIdentifier();
                    r4Header = r4Header + GlobalVariables.FIELD_SEPARATOR + tempR4Header;
                    port_qual = port_qual + GlobalVariables.FIELD_SEPARATOR + tempPort_qual;
                    iana_splc = iana_splc + GlobalVariables.FIELD_SEPARATOR + tempIana_splc;
                    logger.info("r4Header ::" + r4Header + " port_qual:: " + port_qual + " iana_splc ::"
                            + iana_splc);

                }
                r4Header = r4Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r4Header.substring(1)
                        : r4Header;
                port_qual = port_qual.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? port_qual.substring(1)
                        : port_qual;
                iana_splc = iana_splc.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? iana_splc.substring(1)
                        : iana_splc;

                logger.info("----- R13  ............");
                String tempR13Header = "";
                for (int q = 0; q < eb.getR13DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempR13Header = eb.getR13DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    r13Header = r13Header + GlobalVariables.FIELD_SEPARATOR + tempR13Header;
                    logger.info("r13Header ::" + r13Header);
                }
                r13Header = r13Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true
                        ? r13Header.substring(1)
                        : r13Header;

                logger.info("----- N9  ............");
                String tempN9Header = "";
                for (int q = 0; q < eb.getN9DetailsMap().get(startIndex).get(changedIndex).size(); q++) {
                    tempN9Header = eb.getN9DetailsMap().get(startIndex).get(changedIndex).get(q)
                            .getHeaderDetails();
                    n9Header = n9Header + GlobalVariables.FIELD_SEPARATOR + tempN9Header;
                    logger.info("n9Header ::" + n9Header);
                }
                n9Header = n9Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? n9Header.substring(1)
                        : n9Header;

                sbQuery.append(
                        " , CREATED_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

                if (gierInfo == null) {
                    gierInfo = new GIERInfoDetails(); //this situation happen when all segment are missing except : ISA,SE,ST,GE,GS,IEA
                }

                equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix;
                equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number;

                pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS);
                pstmt.setString(1, eb.getISADetails().getHeaderDetails());
                pstmt.setString(2, gsHeader);
                pstmt.setString(3, GlobalVariables.INPUT_TYPE_BOESC_322);
                pstmt.setString(4, eb.getISADetails().getInterchangeSenderId());
                pstmt.setString(5, userType);
                pstmt.setString(6, eb.getISADetails().getInterchangeDate());
                pstmt.setString(7, gsControl);
                pstmt.setString(8, st_control);
                pstmt.setString(9, eventType);
                pstmt.setString(10, equip_prefix);
                pstmt.setString(11, equip_number);
                pstmt.setString(12, equip_prefix + equip_number);
                pstmt.setString(13, gierInfo.getCompanySCACCode() == null ? "" : gierInfo.getCompanySCACCode());
                pstmt.setString(14, gierInfo.getUsDotNumber() == null ? "" : gierInfo.getUsDotNumber());
                pstmt.setString(15, port_qual);
                pstmt.setString(16, iana_splc);
                pstmt.setString(17, gierInfo.getChassisPoolId() == null ? "" : gierInfo.getChassisPoolId());
                pstmt.setString(18, gierInfo.getChassisPoolName() == null ? "" : gierInfo.getChassisPoolName());
                pstmt.setString(19, q5Header);
                pstmt.setString(20, n7Header);
                pstmt.setString(21, w2Header);
                pstmt.setString(22, r4Header);
                pstmt.setString(23, n9Header);
                pstmt.setString(24, r13Header);
                pstmt.setString(25, boescUserId);
                pstmt.setString(26, status);
                pstmt.setObject(27, DateTimeFormater.getSqlSysTimestamp());

                logger.info("query :: " + sbQuery.toString());
                int dbStat = 0;
                int boescKey = 0;
                dbStat = pstmt.executeUpdate();
                rs = pstmt.getGeneratedKeys();
                if (dbStat != 0) {
                    if (rs != null) {
                        while (rs.next()) {
                            boescKey = rs.getInt(1);
                            logger.info("boescKey: " + boescKey);
                        }
                    }

                    conn.commit();
                } else {
                    conn.rollback();
                }
                if (boescKey != 0) {
                    //Update BOESC_UNIQUE_NO : using business logic
                    String sql = "UPDATE BOESC_TRAN_SET SET BOESC_UNIQUE_NO = ? WHERE BOESC_TRAN_ID = ? ";
                    qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(boescKey, "BOESC-"), boescKey });
                    logger.info("Record Inserted successfully for BOESC..." + file.getName());
                    return true;
                } else {
                    logger.error("Failure Data insertion in BOESC..");
                }
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (pstmt != null) {
                        pstmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException ex1) {
                    logger.error("Caught SQL exception while closing prepared statement /resultset "
                            + ex1.getMessage());
                    ex1.printStackTrace();
                    throw ex1;
                } catch (Exception e) {
                    logger.error("Caught SQL exception in finally block " + e.getMessage());
                    e.printStackTrace();
                    throw e;
                }
            }
        }
    }

    return false;
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

public void addSubscriber(Subscriber subscriber, String groupingId) throws APIManagementException {
    Connection conn = null;//from w w  w.ja  v a  2 s  .c  o m
    ResultSet rs = null;
    PreparedStatement ps = null;
    try {
        conn = APIMgtDBUtil.getConnection();
        conn.setAutoCommit(false);

        String query = SQLConstants.ADD_SUBSCRIBER_SQL;
        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.setString(5, subscriber.getName());
        ps.setTimestamp(6, new Timestamp(subscriber.getSubscribedDate().getTime()));
        ps.executeUpdate();

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

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Add a API level throttling policy to database.
 * <p>//from   w  w w. j  a  v  a  2 s . c o  m
 * If valid policy Id (not -1) is present in the <code>policy</code> object,
 * policy will be inserted with that policy Id.
 * Otherwise policy Id will be auto incremented.
 * </p>
 *
 * @param policy policy object defining the throttle policy
 * @throws SQLException
 */
private void addAPIPolicy(APIPolicy policy, Connection conn) throws SQLException {
    ResultSet resultSet = null;
    PreparedStatement policyStatement = null;
    String addQuery;
    int policyId = policy.getPolicyId();

    try {

        // Valid policyId is available means policy should be inserted with 'policyId'. (Policy update request)
        if (policyId == -1) {
            addQuery = SQLConstants.ThrottleSQLConstants.INSERT_API_POLICY_SQL;
        } else {
            addQuery = SQLConstants.ThrottleSQLConstants.INSERT_API_POLICY_WITH_ID_SQL;
        }
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        policyStatement = conn.prepareStatement(addQuery,
                new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "POLICY_ID") });
        setCommonParametersForPolicy(policyStatement, policy);
        policyStatement.setString(12, policy.getUserLevel());
        if (policyId != -1) {

            // Assume policy is deployed if update request is recieved
            policyStatement.setBoolean(10, true);
            policyStatement.setInt(13, policyId);
        }
        policyStatement.executeUpdate();
        resultSet = policyStatement.getGeneratedKeys(); // Get the inserted POLICY_ID (auto incremented value)

        // Returns only single row
        if (resultSet.next()) {

            /*Not sure about below comment :-) (Dhanuka)
             *  H2 doesn't return generated keys when key is provided (not generated).
               Therefore policyId should be policy parameter's policyId when it is provided.
             */
            if (policyId == -1) {
                policyId = resultSet.getInt(1);
            }
            List<Pipeline> pipelines = policy.getPipelines();
            if (pipelines != null) {
                for (Pipeline pipeline : pipelines) { // add each pipeline data to AM_CONDITION_GROUP table
                    addPipeline(pipeline, policyId, conn);
                }
            }
        }
    } finally {
        APIMgtDBUtil.closeAllConnections(policyStatement, null, resultSet);
    }
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

public void addAPI(API api, int tenantId) throws APIManagementException {
    Connection connection = null;
    PreparedStatement prepStmt = null;
    ResultSet rs = null;//w  w w. j ava 2  s.  c  o  m

    String query = SQLConstants.ADD_API_SQL;
    try {
        connection = APIMgtDBUtil.getConnection();
        connection.setAutoCommit(false);

        prepStmt = connection.prepareStatement(query, new String[] { "api_id" });
        prepStmt.setString(1, APIUtil.replaceEmailDomainBack(api.getId().getProviderName()));
        prepStmt.setString(2, api.getId().getApiName());
        prepStmt.setString(3, api.getId().getVersion());
        prepStmt.setString(4, api.getContext());
        String contextTemplate = api.getContextTemplate();
        //If the context template ends with {version} this means that the version will be at the end of the context.
        if (contextTemplate.endsWith("/" + APIConstants.VERSION_PLACEHOLDER)) {
            //Remove the {version} part from the context template.
            contextTemplate = contextTemplate.split(Pattern.quote("/" + APIConstants.VERSION_PLACEHOLDER))[0];
        }
        prepStmt.setString(5, contextTemplate);
        prepStmt.setString(6, APIUtil.replaceEmailDomainBack(api.getId().getProviderName()));
        prepStmt.setTimestamp(7, new Timestamp(System.currentTimeMillis()));
        prepStmt.setString(8, api.getApiLevelPolicy());
        prepStmt.execute();

        rs = prepStmt.getGeneratedKeys();
        int applicationId = -1;
        if (rs.next()) {
            applicationId = rs.getInt(1);
        }

        connection.commit();

        if (api.getScopes() != null) {
            addScopes(api.getScopes(), applicationId, tenantId);
        }
        addURLTemplates(applicationId, api, connection);
        String tenantUserName = MultitenantUtils
                .getTenantAwareUsername(APIUtil.replaceEmailDomainBack(api.getId().getProviderName()));
        recordAPILifeCycleEvent(api.getId(), null, APIStatus.CREATED.toString(), tenantUserName, tenantId,
                connection);
        //If the api is selected as default version, it is added/replaced into AM_API_DEFAULT_VERSION table
        if (api.isDefaultVersion()) {
            addUpdateAPIAsDefaultVersion(api, connection);
        }
        connection.commit();
    } catch (SQLException e) {
        handleException("Error while adding the API: " + api.getId() + " to the database", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(prepStmt, connection, rs);
    }
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

public void addScopes(Set<?> objects, int api_id, int tenantID) throws APIManagementException {

    Connection conn = null;//from  www  . jav a 2 s.  c o m
    PreparedStatement ps = null, ps2 = null;
    ResultSet rs = null;

    String scopeEntry = SQLConstants.ADD_SCOPE_ENTRY_SQL;
    String scopeLink = SQLConstants.ADD_SCOPE_LINK_SQL;
    try {
        conn = APIMgtDBUtil.getConnection();
        conn.setAutoCommit(false);

        String scopeId = "SCOPE_ID";
        if (conn.getMetaData().getDriverName().contains("PostgreSQL")) {
            scopeId = "scope_id";
        }

        if (objects != null) {
            for (Object object : objects) {
                ps = conn.prepareStatement(scopeEntry, new String[] { scopeId });
                ps2 = conn.prepareStatement(scopeLink);

                if (object instanceof URITemplate) {
                    URITemplate uriTemplate = (URITemplate) object;

                    if (uriTemplate.getScope() == null) {
                        continue;
                    }
                    ps.setString(1, uriTemplate.getScope().getKey());
                    ps.setString(2, uriTemplate.getScope().getName());
                    ps.setString(3, uriTemplate.getScope().getDescription());
                    ps.setInt(4, tenantID);
                    ps.setString(5, uriTemplate.getScope().getRoles());
                    ps.execute();
                    rs = ps.getGeneratedKeys();
                    if (rs.next()) {
                        uriTemplate.getScope().setId(rs.getInt(1));
                    }

                    ps2.setInt(1, api_id);
                    ps2.setInt(2, uriTemplate.getScope().getId());
                    ps2.execute();
                    conn.commit();
                } else if (object instanceof Scope) {
                    Scope scope = (Scope) object;
                    ps.setString(1, scope.getKey());
                    ps.setString(2, scope.getName());
                    ps.setString(3, scope.getDescription());
                    ps.setInt(4, tenantID);
                    ps.setString(5, scope.getRoles());
                    ps.execute();
                    rs = ps.getGeneratedKeys();
                    if (rs.next()) {
                        scope.setId(rs.getInt(1));
                    }
                    ps2.setInt(1, api_id);
                    ps2.setInt(2, scope.getId());
                    ps2.execute();
                    conn.commit();
                }
            }
        }
    } catch (SQLException e) {
        try {
            if (conn != null) {
                conn.rollback();
            }
        } catch (SQLException e1) {
            handleException("Error occurred while Rolling back changes done on Scopes Creation", e1);
        }
        handleException("Error occurred while creating scopes ", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, conn, rs);
        APIMgtDBUtil.closeAllConnections(ps2, null, null);
    }
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Add throttling policy pipeline to database
 *
 * @param pipeline condition pipeline/*from w w w  . j a  v  a2s.c  o  m*/
 * @param policyID id of the policy to add pipeline
 * @param conn     database connection. This should be provided inorder to rollback transaction
 * @throws SQLException
 */
private void addPipeline(Pipeline pipeline, int policyID, Connection conn) throws SQLException {
    PreparedStatement conditionStatement = null;
    ResultSet rs = null;

    try {
        String sqlAddQuery = SQLConstants.ThrottleSQLConstants.INSERT_CONDITION_GROUP_SQL;
        List<Condition> conditionList = pipeline.getConditions();

        // Add data to the AM_CONDITION table
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        conditionStatement = conn.prepareStatement(sqlAddQuery, new String[] {
                DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "CONDITION_GROUP_ID") });
        conditionStatement.setInt(1, policyID);
        conditionStatement.setString(2, pipeline.getQuotaPolicy().getType());

        if (PolicyConstants.REQUEST_COUNT_TYPE.equals(pipeline.getQuotaPolicy().getType())) {
            conditionStatement.setLong(3,
                    ((RequestCountLimit) pipeline.getQuotaPolicy().getLimit()).getRequestCount());
            conditionStatement.setString(4, null);
        } else if (PolicyConstants.BANDWIDTH_TYPE.equals(pipeline.getQuotaPolicy().getType())) {
            BandwidthLimit limit = (BandwidthLimit) pipeline.getQuotaPolicy().getLimit();
            conditionStatement.setLong(3, limit.getDataAmount());
            conditionStatement.setString(4, limit.getDataUnit());
        }

        conditionStatement.setLong(5, pipeline.getQuotaPolicy().getLimit().getUnitTime());
        conditionStatement.setString(6, pipeline.getQuotaPolicy().getLimit().getTimeUnit());
        conditionStatement.setString(7, pipeline.getDescription());
        conditionStatement.executeUpdate();
        rs = conditionStatement.getGeneratedKeys();

        // Add Throttling parameters which have multiple entries
        if (rs != null && rs.next()) {
            int pipelineId = rs.getInt(1); // Get the inserted
            // CONDITION_GROUP_ID (auto
            // incremented value)
            pipeline.setId(pipelineId);
            for (Condition condition : conditionList) {
                if (condition == null) {
                    continue;
                }
                String type = condition.getType();
                if (PolicyConstants.IP_RANGE_TYPE.equals(type)
                        || PolicyConstants.IP_SPECIFIC_TYPE.equals(type)) {
                    IPCondition ipCondition = (IPCondition) condition;
                    addIPCondition(ipCondition, pipelineId, conn);
                }

                if (PolicyConstants.HEADER_TYPE.equals(type)) {
                    addHeaderCondition((HeaderCondition) condition, pipelineId, conn);
                } else if (PolicyConstants.QUERY_PARAMETER_TYPE.equals(type)) {
                    addQueryParameterCondition((QueryParameterCondition) condition, pipelineId, conn);
                } else if (PolicyConstants.JWT_CLAIMS_TYPE.equals(type)) {
                    addJWTClaimsCondition((JWTClaimsCondition) condition, pipelineId, conn);
                }
            }
        }
    } finally {
        APIMgtDBUtil.closeAllConnections(conditionStatement, null, rs);
    }
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Adds a comment for an API/*from   ww  w.  j  a  v a  2s  .com*/
 *
 * @param identifier  API Identifier
 * @param commentText Commented Text
 * @param user        User who did the comment
 * @return Comment ID
 */
public int addComment(APIIdentifier identifier, String commentText, String user) throws APIManagementException {

    Connection connection = null;
    ResultSet resultSet = null;
    ResultSet insertSet = null;
    PreparedStatement getPrepStmt = null;
    PreparedStatement insertPrepStmt = null;
    int commentId = -1;
    int apiId = -1;

    try {
        connection = APIMgtDBUtil.getConnection();
        connection.setAutoCommit(false);

        String getApiQuery = SQLConstants.GET_API_ID_SQL;
        getPrepStmt = connection.prepareStatement(getApiQuery);
        getPrepStmt.setString(1, APIUtil.replaceEmailDomainBack(identifier.getProviderName()));
        getPrepStmt.setString(2, identifier.getApiName());
        getPrepStmt.setString(3, identifier.getVersion());
        resultSet = getPrepStmt.executeQuery();
        if (resultSet.next()) {
            apiId = resultSet.getInt("API_ID");
        }

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

        /*This query to update the AM_API_COMMENTS table */
        String addCommentQuery = SQLConstants.ADD_COMMENT_SQL;

        /*Adding data to the AM_API_COMMENTS table*/
        String dbProductName = connection.getMetaData().getDatabaseProductName();
        insertPrepStmt = connection.prepareStatement(addCommentQuery,
                new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "comment_id") });

        insertPrepStmt.setString(1, commentText);
        insertPrepStmt.setString(2, user);
        insertPrepStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()), Calendar.getInstance());
        insertPrepStmt.setInt(4, apiId);

        insertPrepStmt.executeUpdate();
        insertSet = insertPrepStmt.getGeneratedKeys();
        while (insertSet.next()) {
            commentId = Integer.parseInt(insertSet.getString(1));
        }
        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                log.error("Failed to rollback the add comment ", e1);
            }
        }
        handleException(
                "Failed to add comment data, for  " + identifier.getApiName() + '-' + identifier.getVersion(),
                e);
    } finally {
        APIMgtDBUtil.closeAllConnections(getPrepStmt, connection, resultSet);
        APIMgtDBUtil.closeAllConnections(insertPrepStmt, null, insertSet);
    }
    return commentId;
}