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.cloudgraph.rdb.service.JDBCSupport.java

protected List<PropertyPair> executeInsertWithGeneratedKeys(PlasmaType type, StringBuilder sql,
        Map<String, PropertyPair> values, Connection con) {
    List<PropertyPair> resultKeys = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    ResultSet generatedKeys = null;
    try {/*w w w . j  a v  a 2 s.co  m*/

        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }

        statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);

        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();
            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }
        }

        statement.execute();
        generatedKeys = statement.getGeneratedKeys();
        ResultSetMetaData rsMeta = generatedKeys.getMetaData();
        int numcols = rsMeta.getColumnCount();
        if (log.isDebugEnabled())
            log.debug("returned " + numcols + " keys");

        if (generatedKeys.next()) {
            // FIXME; without metadata describing which properties
            // are actually a sequence, there is guess work
            // involved in matching the values returned
            // automatically from PreparedStatment as they
            // are anonymous in terms of the column names
            // making it impossible to match them to a metadata
            // property.
            List<Property> pkPropList = type.findProperties(KeyType.primary);
            if (pkPropList == null || pkPropList.size() == 0)
                throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'");
            if (pkPropList.size() > 1)
                throw new DataAccessException("multiple pri-key properties found for type '" + type.getName()
                        + "' - cannot map to generated keys");
            PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0);
            // FIXME: need to find properties per column by physical name
            // alias
            // in case where multiple generated pri-keys
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                if (log.isDebugEnabled())
                    log.debug("returned key column '" + columnName + "'");
                int columnType = rsMeta.getColumnType(i);
                Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop);
                PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                resultKeys.add(pair);
            }
        }
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
    }

    return resultKeys;
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCResourceVersionDAO.java

public long createSnapshot(int pathId, String name, InputStream versionsStream) throws RegistryException {

    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet result = null;//  w w  w  . java  2 s  .  com
    try {
        String sql = "INSERT INTO REG_SNAPSHOT (REG_PATH_ID, REG_RESOURCE_NAME, "
                + "REG_RESOURCE_VIDS, REG_TENANT_ID) VALUES (?, ?, ?, ?)";
        String sql1 = "SELECT MAX(REG_SNAPSHOT_ID) FROM REG_SNAPSHOT";

        int size = versionsStream.available();
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_SNAPSHOT_ID") });
        } else {
            ps = conn.prepareStatement(sql);
        }
        ps.setInt(1, pathId);
        ps.setString(2, name);
        ps.setBinaryStream(3, versionsStream, size);
        ps.setInt(4, CurrentSession.getTenantId());
        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            result = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_SNAPSHOT_LOCK) {
                ps.executeUpdate();
                if (dbProductName.equals("OpenEdge RDBMS")) {
                    String sql2 = "UPDATE REG_SNAPSHOT SET REG_SNAPSHOT_ID = "
                            + "PUB.REG_SNAPSHOT_SEQUENCE.NEXTVAL WHERE REG_SNAPSHOT_ID = 0";
                    PreparedStatement ps2 = null;
                    try {
                        ps2 = conn.prepareStatement(sql2);
                        ps2.executeUpdate();
                    } finally {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    }
                }
                ps1 = conn.prepareStatement(sql1);
                result = ps1.executeQuery();
            }
        }
        long snapshotID = -1;
        if (result.next()) {
            snapshotID = result.getLong(1);
        }
        return snapshotID;
    } catch (Exception e) {

        String msg = "Failed to write resource content to the database. " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCResourceVersionDAO.java

public long createSnapshot(int pathId, String name, InputStream versionsStream) throws RepositoryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet result = null;//from  w w  w .  j av a 2s  .  com

    try {
        String sql = "INSERT INTO REG_SNAPSHOT (REG_PATH_ID, REG_RESOURCE_NAME, "
                + "REG_RESOURCE_VIDS, REG_TENANT_ID) VALUES (?, ?, ?, ?)";
        String sql1 = "SELECT MAX(REG_SNAPSHOT_ID) FROM REG_SNAPSHOT";

        int size = versionsStream.available();
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);

        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_SNAPSHOT_ID") });
        } else {
            ps = conn.prepareStatement(sql);
        }

        ps.setInt(1, pathId);
        ps.setString(2, name);
        ps.setBinaryStream(3, versionsStream, size);
        ps.setInt(4, CurrentContext.getTenantId());

        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            result = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_SNAPSHOT_LOCK) {
                ps.executeUpdate();
                if (dbProductName.equals("OpenEdge RDBMS")) {
                    String sql2 = "UPDATE REG_SNAPSHOT SET REG_SNAPSHOT_ID = "
                            + "PUB.REG_SNAPSHOT_SEQUENCE.NEXTVAL WHERE REG_SNAPSHOT_ID = 0";
                    PreparedStatement ps2 = null;
                    try {
                        ps2 = conn.prepareStatement(sql2);
                        ps2.executeUpdate();
                    } finally {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    }
                }
                ps1 = conn.prepareStatement(sql1);
                result = ps1.executeQuery();
            }
        }

        long snapshotID = -1;

        if (result.next()) {
            snapshotID = result.getLong(1);
        }

        return snapshotID;
    } catch (Exception e) {
        String msg = "Failed to write resource content to the database. " + e.getMessage();
        log.error(msg, e);
        throw new RepositoryDBException(msg, e);
    } finally {
        try {
            try {
                if (result != null) {
                    result.close();
                }
            } finally {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:mil.army.usace.data.nativequery.rdbms.NativeRdbmsQuery.java

private GeneratedKey insertWithGeneratedKeyReturn(Object record, boolean useDeclaredOnly) {
    PreparedStatement st = null;
    boolean inTrans = inTransaction();
    String command = null;//from  w  w w . j a  v  a2 s .  co  m
    int batchCount = 0;
    GeneratedKey gk = null;
    if (!inTrans)
        startTransaction();
    try {
        Class objClass = record.getClass();
        String schema = getEntitySchema(objClass);
        Boolean isCamelCased = useCamelCase(objClass);
        HashMap<Method, String> fieldMapping = getFieldMapping(objClass, GET, isCamelCased, useDeclaredOnly);
        String idFieldName = getIdFieldName(fieldMapping);
        HashMap<Integer, Method> indexMapping = new HashMap();
        String tableName = getTableName(objClass);
        if (tableName == null)
            tableName = getDbName(isCamelCased, objClass.getSimpleName(), null);
        command = getInsertCommand(tableName, schema, fieldMapping, indexMapping);
        if (idFieldName != null) {
            st = conn.prepareStatement(command, new String[] { idFieldName });
        } else {
            st = conn.prepareStatement(command);
        }
        for (int index : indexMapping.keySet()) {
            Object value = indexMapping.get(index).invoke(record, null);
            if (value instanceof java.util.Date) {
                value = new java.sql.Date(((java.util.Date) value).getTime());
            }
            st.setObject((Integer) index, value);
        }
        st.execute();
        ResultSet rs = st.getGeneratedKeys();
        if (rs.next()) {
            gk = new GeneratedKey(idFieldName, rs.getObject(1));
        }
        if (!inTrans)
            commitTransaction();
        return gk;
    } catch (Exception ex) {
        ex.printStackTrace();
        if (!inTrans)
            rollbackTransaction();
        throw new NativeQueryException(command, "insertWithGeneratedKeyReturn", ex);
    } finally {
        if (st != null) {
            try {
                st.close();
            } catch (Exception ex) {
            }
        }
    }
}

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public BigInteger getNewSimulationGroupId()
        throws ApolloDatabaseRecordNotInsertedException, ApolloDatabaseException {
    String query = "INSERT INTO simulation_groups VALUES ()";

    try (Connection conn = datasource.getConnection()) {
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        pstmt.execute();/*from w  w  w  .  j a  va2  s.co m*/
        ResultSet rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            return new BigInteger(rs.getString(1));
        } else {
            throw new ApolloDatabaseRecordNotInsertedException(
                    "Unable to create new simulation group, insert failed.");
        }

    } catch (SQLException ex) {
        throw new ApolloDatabaseException("SQLException getting new simulation group ID: " + ex.getMessage());
    }
}

From source file:com.skilrock.lms.coreEngine.scratchService.inventoryMgmt.common.SalesReturnHelper.java

public String doTransaction(int game_id, int org_id, String orgName, List<PackBean> packlist,
        List<BookBean> booklist, String rootPath, int userOrgId, int userId, String newBookStatus,
        Connection conn) throws LMSException {

    int receipt_id = 0;
    int transaction_id = 0;
    double ticket_price = 0, book_price = 0;
    int nbr_of_tickets_per_book = 0, nbr_of_books_per_pack = 0;
    double agent_sale_comm_rate = 0;
    double prizePayOutPer = 0.0;
    double vat = 0.0;
    double govtComm = 0.0;
    double vatAmt = 0.0;
    double bookTaxableSale = 0.0;
    double govtCommAmt = 0.0;
    double commAmt = 0.0;
    double netAmt = 0.0;
    double taxableSale = 0.0;
    double vatBalance = 0.0;
    long ticketsInScheme = 0;
    String govtCommRule = null;/*from   w w w  . j a va 2  s.  c o m*/
    double fixedAmt = 0.0;
    double netAmtOrg = 0.0;
    int DCId = 0;
    String bookNumber = "";
    boolean isBookActivated = true;

    List<Integer> trnIdList = new ArrayList<Integer>();

    try {
        // get books list from packlist and copy to booklist

        // new book status on sales return
        // String newBookStatus = "INACTIVE";
        logger.info("***Return Book Status Should be**************" + newBookStatus);
        if (packlist.size() != 0) {
            PackBean packbean = null;
            BookBean bookbean = null;
            Iterator<PackBean> packListItr = packlist.iterator();
            while (packListItr.hasNext()) {
                packbean = packListItr.next();
                String packNbr = packbean.getPackNumber();
                String querytoGetBookFrmPack = QueryManager.getST4BookNbrOfPackNbr();
                PreparedStatement stmtbookFrmPack = conn.prepareStatement(querytoGetBookFrmPack);
                stmtbookFrmPack.setString(1, packNbr);
                ResultSet set = stmtbookFrmPack.executeQuery();
                while (set.next()) {

                    String bookNbrfromPack = set.getString("book_nbr");

                    bookbean = new BookBean();
                    bookbean.setBookNumber(bookNbrfromPack);
                    bookbean.setValid(true);
                    bookbean.setStatus("Book Is Valid");
                    booklist.add(bookbean);
                }

            }

        }

        // Getting Game Details using game id
        String querytoGameDetail = QueryManager.getST4GameDetailsUsingGameId();
        PreparedStatement stmtgamedeatil = conn.prepareStatement(querytoGameDetail);
        stmtgamedeatil.setInt(1, game_id);
        ResultSet rsGameDetail = stmtgamedeatil.executeQuery();
        while (rsGameDetail.next()) {
            ticket_price = rsGameDetail.getDouble("ticket_price");
            nbr_of_tickets_per_book = rsGameDetail.getInt("nbr_of_tickets_per_book");
            nbr_of_books_per_pack = rsGameDetail.getInt("nbr_of_books_per_pack");
            agent_sale_comm_rate = rsGameDetail.getDouble("agent_sale_comm_rate");
            prizePayOutPer = rsGameDetail.getDouble("prize_payout_ratio");
            vat = rsGameDetail.getDouble("vat_amt");
            govtComm = rsGameDetail.getDouble("govt_comm_rate");
            vatBalance = rsGameDetail.getDouble("vat_balance");
            ticketsInScheme = rsGameDetail.getLong("tickets_in_scheme");
            govtCommRule = rsGameDetail.getString("govt_comm_type");
            fixedAmt = rsGameDetail.getDouble("fixed_amt");
        }
        book_price = ticket_price * nbr_of_tickets_per_book;

        BookSaleReturnBean bookSaleRetBean;
        ArrayList<BookSaleReturnBean> bookSaleReturnList = new ArrayList<BookSaleReturnBean>();
        String bookNbr, packNbr = null;
        double commVariance = 0.0;
        double govtCommRate = 0.0;
        ResultSet rsCommVar;
        Iterator iteratorCommVar = booklist.iterator();
        while (iteratorCommVar.hasNext()) {
            bookSaleRetBean = new BookSaleReturnBean();
            bookNbr = ((BookBean) iteratorCommVar.next()).getBookNumber();
            String commVarianceQuery = "select transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_date from st_se_game_inv_detail where current_owner=? and current_owner_id=? and game_id=? and book_nbr=? and transaction_at=? order by transaction_date desc limit 1 ";
            PreparedStatement commVariaceStmt = conn.prepareStatement(commVarianceQuery);
            commVariaceStmt.setString(1, "AGENT");
            commVariaceStmt.setInt(2, org_id);
            commVariaceStmt.setInt(3, game_id);
            commVariaceStmt.setString(4, bookNbr);
            commVariaceStmt.setString(5, "BO");
            rsCommVar = commVariaceStmt.executeQuery();
            while (rsCommVar.next()) {
                commVariance = rsCommVar.getDouble("transacrion_sale_comm_rate");
                govtCommRate = rsCommVar.getDouble("transaction_gov_comm_rate");
            }
            commAmt = commAmt + book_price * commVariance * 0.01;
            netAmt = netAmt + book_price * (1 - commVariance * 0.01);
            govtCommAmt = book_price * govtCommRate * .01;

            vatAmt = vatAmt + CommonMethods.calculateVat(book_price, commVariance, prizePayOutPer, govtCommRate,
                    vat, govtCommRule, fixedAmt, ticketsInScheme);
            /*
             * bookTaxableSale = (((book_price * (100 - (commVariance +
             * prizePayOutPer + govtCommRate))) / 100) * 100) / (100 + vat);
             */
            // bookTaxableSale=vatAmt/(vat * 0.01);
            bookTaxableSale = CommonMethods.calTaxableSale(book_price, commVariance, prizePayOutPer,
                    govtCommRate, vat);

            bookSaleRetBean.setBookNumber(bookNbr);
            bookSaleRetBean.setBookCommVariance(commVariance);
            bookSaleRetBean.setDefaultCommVariance(agent_sale_comm_rate);
            bookSaleRetBean.setTotalSaleComm(commVariance);
            bookSaleRetBean.setTotalSaleGovtComm(govtCommRate);
            bookSaleRetBean.setGovtCommAmt(govtCommAmt);
            bookSaleRetBean.setBookVatAmt(vatAmt);
            bookSaleRetBean.setBookCommAmt(commAmt);
            bookSaleRetBean.setBookNetAmt(netAmt);
            bookSaleRetBean.setBookTaxableSale(bookTaxableSale);
            bookSaleReturnList.add(bookSaleRetBean);
            commVariance = 0.0;
            govtCommRate = 0.0;
            bookTaxableSale = 0.0;
            govtCommAmt = 0.0;
            vatAmt = 0.0;
            commAmt = 0.0;
            netAmt = 0.0;

        }

        // get comm variance history

        List<CommVarGovtCommBean> commVariancelist = new ArrayList<CommVarGovtCommBean>();
        CommVarGovtCommBean commVarGovtCommBean;

        String queryCommVarHistory = "select DISTINCT transacrion_sale_comm_rate,transaction_gov_comm_rate from st_se_game_inv_detail where current_owner_id="
                + org_id + " and game_id=" + game_id;
        PreparedStatement stmtCommVarHistory = conn.prepareStatement(queryCommVarHistory);
        ResultSet rsCommVarHistory = stmtCommVarHistory.executeQuery();
        while (rsCommVarHistory.next()) {
            commVarGovtCommBean = new CommVarGovtCommBean();
            commVarGovtCommBean.setCommVariance(rsCommVarHistory.getDouble("transacrion_sale_comm_rate"));
            commVarGovtCommBean.setGovtComm(rsCommVarHistory.getDouble("transaction_gov_comm_rate"));
            commVariancelist.add(commVarGovtCommBean);
            // commVarianceSet.add(rsCommVarHistory.getDouble("transacrion_sale_comm_rate"));
        }
        System.out.println(" 22222222222222222222222size for comm var history " + commVariancelist.size()
                + "pstmt " + stmtCommVarHistory);
        Iterator iteratorBookSaleReturn;
        // Iterator iteratorCommVarHistory= commVarianceSet.iterator();
        Iterator iteratorCommVarHistory = commVariancelist.iterator();
        while (iteratorCommVarHistory.hasNext()) {
            boolean bookCommVarMatch = false;
            // logger.info("comm var from history--------------------
            // ");
            Double totCommAmt = 0.0;
            Double totVatAmt = 0.0;
            Double totNetAmt = 0.0;
            Double totTaxableSale = 0.0;
            Double totGovtComm = 0.0;

            List bookListforSingleTrn = null;
            bookListforSingleTrn = new ArrayList<String>();
            double commFromHistory = 0.0;
            double govtCommFromHistory = 0.0;
            // commFromHistory=(Double)iteratorCommVarHistory.next();

            CommVarGovtCommBean commBean = (CommVarGovtCommBean) iteratorCommVarHistory.next();
            commFromHistory = commBean.getCommVariance();
            govtCommFromHistory = commBean.getGovtComm();

            // logger.info("comm var from history--------------------
            // "+commFromHistory);
            iteratorBookSaleReturn = bookSaleReturnList.iterator();
            while (iteratorBookSaleReturn.hasNext()) {
                bookSaleRetBean = (BookSaleReturnBean) iteratorBookSaleReturn.next();
                double bookCommVariance = 0.0;
                double bookGovtCommVariance = 0.0;
                bookCommVariance = bookSaleRetBean.getTotalSaleComm();
                bookGovtCommVariance = bookSaleRetBean.getTotalSaleGovtComm();
                // logger.info("commFromHistory " + commFromHistory +
                // "bookCommVariance " + bookCommVariance);
                // logger.info("GovtcommFromHistory " +
                // govtCommFromHistory + "bookGovtCommVariance " +
                // bookGovtCommVariance);
                if (commFromHistory == bookCommVariance && govtCommFromHistory == bookGovtCommVariance) {
                    // logger.info("inside
                    boolean isSaleTransactionExist = true;
                    // if%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%");
                    if ("YES".equals(Utility.getPropertyValue("IS_SCRATCH_NEW_FLOW_ENABLED"))) {
                        bookNumber = bookSaleRetBean.getBookNumber();
                        String saleTransactionQuery = "select current_owner_id from st_se_game_inv_status where book_nbr = '"
                                + bookSaleRetBean.getBookNumber() + "' and agent_invoice_id IS NOT NULL";
                        Statement saleTransactionQueryStmt = conn.createStatement();
                        ResultSet saleTransactionQueryResultSet = saleTransactionQueryStmt
                                .executeQuery(saleTransactionQuery);
                        if (saleTransactionQueryResultSet.next()) {
                            isSaleTransactionExist = true;
                        } else {
                            isSaleTransactionExist = false;
                        }
                    }
                    if (isSaleTransactionExist) {
                        bookCommVarMatch = true;
                        isBookActivated = false;
                        totCommAmt = totCommAmt + bookSaleRetBean.getBookCommAmt();
                        totVatAmt = totVatAmt + bookSaleRetBean.getBookVatAmt();
                        totTaxableSale = totTaxableSale + bookSaleRetBean.getBookTaxableSale();
                        // logger.info("hello :::::::: " + totTaxableSale
                        // + "history detail "
                        // +bookSaleRetBean.getBookTaxableSale());
                        totGovtComm = totGovtComm + bookSaleRetBean.getGovtCommAmt();
                        totNetAmt = totNetAmt + bookSaleRetBean.getBookNetAmt();
                        bookListforSingleTrn.add(bookSaleRetBean.getBookNumber());
                    }
                }

            }
            netAmtOrg = netAmtOrg + totNetAmt;

            if (bookCommVarMatch) {

                // insert in LMS transaction master
                String QueryLMSTransMaster = QueryManager.insertInLMSTransactionMaster();
                PreparedStatement stmtLMSTransmas = conn.prepareStatement(QueryLMSTransMaster);
                stmtLMSTransmas.setString(1, "BO");
                stmtLMSTransmas.executeUpdate();

                // Get Transaction Id From the table.
                ResultSet rsTransId = stmtLMSTransmas.getGeneratedKeys();
                while (rsTransId.next()) {
                    transaction_id = rsTransId.getInt(1);
                    trnIdList.add(transaction_id);
                }

                logger.info("transaction_id:  " + transaction_id);

                // 1. Insert Entry in st_lms_bo_transaction_master Table.
                String queryTranMas = QueryManager.insertInBOTransactionMaster();
                PreparedStatement stmtTransmas = conn.prepareStatement(queryTranMas);

                stmtTransmas.setInt(1, transaction_id);
                stmtTransmas.setInt(2, userId);
                stmtTransmas.setInt(3, userOrgId);
                stmtTransmas.setString(4, "AGENT");
                stmtTransmas.setInt(5, org_id);
                stmtTransmas.setTimestamp(6, new java.sql.Timestamp(new java.util.Date().getTime()));
                stmtTransmas.setString(7, "SALE_RET");

                /*
                 * stmtTransmas.setString(1, "AGENT");
                 * stmtTransmas.setInt(2, org_id);
                 * stmtTransmas.setTimestamp(3, new java.sql.Timestamp(new
                 * java.util.Date().getTime())); stmtTransmas.setString(4,
                 * "SALE_RET");
                 */

                stmtTransmas.executeUpdate();

                // 2. Insert Entry in st_se_bo_agent_transaction table.
                String queryBoAgtTrans = QueryManager.getST4InsertBoAgentTransaction();
                PreparedStatement stmtBoAgtTrans = conn.prepareStatement(queryBoAgtTrans);
                stmtBoAgtTrans.setInt(1, transaction_id);
                stmtBoAgtTrans.setInt(2, bookListforSingleTrn.size());
                stmtBoAgtTrans.setInt(3, game_id);
                stmtBoAgtTrans.setInt(4, org_id);
                stmtBoAgtTrans.setDouble(5, book_price * bookListforSingleTrn.size());
                stmtBoAgtTrans.setDouble(6, totCommAmt);
                stmtBoAgtTrans.setDouble(7, totNetAmt);
                stmtBoAgtTrans.setString(8, "SALE_RET");
                stmtBoAgtTrans.setDouble(9, totVatAmt);
                stmtBoAgtTrans.setDouble(10, totTaxableSale);
                stmtBoAgtTrans.setDouble(11, totGovtComm);
                stmtBoAgtTrans.executeUpdate();

                String detHistoryInsQuery = "insert into st_se_game_ticket_inv_history(game_id, book_nbr, "
                        + " current_owner, current_owner_id, date, done_by_oid, done_by_uid, cur_rem_tickets, "
                        + " active_tickets_upto, sold_tickets, status) values (?,?,?,?,?,?,?,?,?,?,?)";
                PreparedStatement detHistoryInsPstmt = conn.prepareStatement(detHistoryInsQuery);

                // fetch game details from game master
                String fetchGameDetQuery = "select nbr_of_tickets_per_book from st_se_game_master where game_id ="
                        + game_id;
                Statement fetchGameDetStmt = conn.createStatement();
                ResultSet fetchGameDetRs = fetchGameDetStmt.executeQuery(fetchGameDetQuery);
                int noOfTktPerBooks = -1;
                if (fetchGameDetRs.next()) {
                    noOfTktPerBooks = fetchGameDetRs.getInt("nbr_of_tickets_per_book");
                }

                // 6. Insert in to st_se_game_inv_detail table.
                for (int i = 0; i < bookListforSingleTrn.size(); i++) {
                    String bknbr = (String) bookListforSingleTrn.get(i);
                    // logger.info("//Get the pack number of book
                    // number. ");
                    // Get the pack number of book number.
                    String pknbr = null;
                    String queryTogetPackNbr = QueryManager.getST4PackNbrOfBookNbr();
                    PreparedStatement stm = conn.prepareStatement(queryTogetPackNbr);
                    stm.setString(1, bknbr);
                    ResultSet resultSet = stm.executeQuery();
                    while (resultSet.next()) {
                        pknbr = resultSet.getString("pack_nbr");
                    }

                    String queryGameInvDtl = "insert into st_se_game_inv_detail (transaction_id,game_id,pack_nbr, book_nbr,current_owner,current_owner_id,transaction_date,transaction_at,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status) select ?,?,?,?,?,?,?,?,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status from st_se_game_inv_detail where book_nbr=? and transaction_at=? order by transaction_date desc limit 1";
                    PreparedStatement stmtGameInvDtl = conn.prepareStatement(queryGameInvDtl);
                    stmtGameInvDtl.setInt(1, transaction_id);
                    stmtGameInvDtl.setInt(2, game_id);
                    stmtGameInvDtl.setString(3, pknbr);
                    stmtGameInvDtl.setString(4, bknbr);
                    stmtGameInvDtl.setString(5, "BO");
                    stmtGameInvDtl.setInt(6, userOrgId);
                    stmtGameInvDtl.setTimestamp(7, new java.sql.Timestamp(new java.util.Date().getTime()));
                    stmtGameInvDtl.setString(8, "BO");
                    stmtGameInvDtl.setString(9, bknbr);
                    stmtGameInvDtl.setString(10, "BO");

                    stmtGameInvDtl.executeUpdate();

                    // insert into detail history table Added by arun
                    detHistoryInsPstmt.setInt(1, game_id);
                    detHistoryInsPstmt.setString(2, bknbr);
                    detHistoryInsPstmt.setString(3, "BO");
                    detHistoryInsPstmt.setInt(4, userOrgId);
                    detHistoryInsPstmt.setTimestamp(5, new java.sql.Timestamp(new java.util.Date().getTime()));
                    detHistoryInsPstmt.setInt(6, userOrgId);
                    detHistoryInsPstmt.setInt(7, userId);
                    detHistoryInsPstmt.setInt(8, noOfTktPerBooks);
                    // logger.info("detHistoryInsPstmt ==
                    // "+detHistoryInsPstmt);
                    if ("ACTIVE".equalsIgnoreCase(newBookStatus.trim())) {
                        detHistoryInsPstmt.setInt(9, noOfTktPerBooks);
                    } else {
                        detHistoryInsPstmt.setInt(9, 0);
                    }
                    detHistoryInsPstmt.setInt(10, 0);
                    detHistoryInsPstmt.setString(11, newBookStatus);

                    detHistoryInsPstmt.execute();
                    // ---------------------

                }
            }
            if (isBookActivated) {
                String bknbr = bookNumber;
                // System.out.println("//Get the pack number of book
                // number. ");
                // Get the pack number of book number.
                String pknbr = null;
                String queryTogetPackNbr = QueryManager.getST4PackNbrOfBookNbr();
                PreparedStatement stm = conn.prepareStatement(queryTogetPackNbr);
                stm.setString(1, bknbr);
                ResultSet resultSet = stm.executeQuery();
                while (resultSet.next()) {
                    pknbr = resultSet.getString("pack_nbr");
                }

                String queryGameInvDtl = "insert into st_se_game_inv_detail (transaction_id,game_id,pack_nbr, book_nbr,current_owner,current_owner_id,transaction_date,transaction_at,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status) select ?,?,?,?,?,?,?,?,transacrion_sale_comm_rate,transaction_gov_comm_rate,transaction_purchase_comm_rate,warehouse_id,book_status from st_se_game_inv_detail where book_nbr=? and transaction_at=? order by transaction_date desc limit 1";
                PreparedStatement stmtGameInvDtl = conn.prepareStatement(queryGameInvDtl);
                stmtGameInvDtl.setInt(1, transaction_id);
                stmtGameInvDtl.setInt(2, game_id);
                stmtGameInvDtl.setString(3, pknbr);
                stmtGameInvDtl.setString(4, bknbr);
                stmtGameInvDtl.setString(5, "BO");
                stmtGameInvDtl.setInt(6, userOrgId);
                stmtGameInvDtl.setTimestamp(7, new java.sql.Timestamp(new java.util.Date().getTime()));
                stmtGameInvDtl.setString(8, "BO");
                stmtGameInvDtl.setString(9, bknbr);
                stmtGameInvDtl.setString(10, "BO");

                stmtGameInvDtl.executeUpdate();
            }
        }

        logger.info("5. Update st_se_game_inv_status Table.   ");
        // 5. Update st_se_game_inv_status Table.
        for (int i = 0; i < bookSaleReturnList.size(); i++) {
            String bknbr = ((BookSaleReturnBean) bookSaleReturnList.get(i)).getBookNumber();
            String queryGameInvStatus = QueryManager.getST4UdateGameInvStatusForBook();
            PreparedStatement stmtGameInvStatus = conn.prepareStatement(queryGameInvStatus);
            stmtGameInvStatus.setString(1, newBookStatus);
            stmtGameInvStatus.setInt(2, userOrgId);
            stmtGameInvStatus.setString(3, null);
            stmtGameInvStatus.setInt(4, game_id);
            stmtGameInvStatus.setString(5, bknbr);
            stmtGameInvStatus.executeUpdate();

        }

        // get auto generated treciept number
        Statement autoGenRecptPstmtBOCRNote = null;
        // String getLatestRecieptNumberBO="SELECT * from
        // st_bo_receipt_gen_mapping where receipt_type=? ORDER BY
        // generated_id DESC LIMIT 1 ";
        String queryRcpt = "SELECT * from st_lms_bo_receipts where receipt_type like ('CR_NOTE%')  ORDER BY generated_id DESC LIMIT 1";
        autoGenRecptPstmtBOCRNote = conn.createStatement();
        // autoGenRecptPstmtBO.setString(1, "CR_NOTE");
        logger.info("queryRcpt--" + queryRcpt);
        ResultSet recieptRsBO = autoGenRecptPstmtBOCRNote.executeQuery(queryRcpt);
        String lastRecieptNoGeneratedBO = null;

        while (recieptRsBO.next()) {
            lastRecieptNoGeneratedBO = recieptRsBO.getString("generated_id");
        }

        String autoGeneRecieptNoBO = GenerateRecieptNo.getRecieptNo("CR_NOTE", lastRecieptNoGeneratedBO, "BO");

        // get auto generated delivery Challan number
        // String getLatestDSRCNumber="SELECT * from
        // st_bo_receipt_gen_mapping where receipt_type=? ORDER BY
        // generated_id DESC LIMIT 1 ";
        // autoGenRecptPstmtBO=conn.prepareStatement(getLatestDSRCNumber);
        PreparedStatement autoGenRecptPstmtBO = null;
        autoGenRecptPstmtBO = conn.prepareStatement(QueryManager.getBOLatestReceiptNb());
        autoGenRecptPstmtBO.setString(1, "DSRCHALLAN");
        ResultSet DCRs = autoGenRecptPstmtBO.executeQuery();
        String lastDSRCNoGenerated = null;

        while (DCRs.next()) {
            lastDSRCNoGenerated = DCRs.getString("generated_id");
        }

        String autoGeneDCNo = null;
        autoGeneDCNo = GenerateRecieptNo.getRecieptNo("DSRCHALLAN", lastDSRCNoGenerated, "BO");

        // insert into receipts master

        PreparedStatement stmtRecptId = conn.prepareStatement(QueryManager.insertInReceiptMaster());
        stmtRecptId.setString(1, "BO");
        stmtRecptId.executeUpdate();

        ResultSet rsRecptId = stmtRecptId.getGeneratedKeys();
        while (rsRecptId.next()) {
            receipt_id = rsRecptId.getInt(1);
        }

        // Insert Entry in st_bo_receipt table.
        // String queryRecptId=QueryManager.getST4InsertBoReceipts();
        stmtRecptId = conn.prepareStatement(QueryManager.insertInBOReceipts());

        stmtRecptId.setInt(1, receipt_id);
        stmtRecptId.setString(2, "CR_NOTE");
        stmtRecptId.setInt(3, org_id);
        stmtRecptId.setString(4, "AGENT");
        stmtRecptId.setString(5, autoGeneRecieptNoBO);
        stmtRecptId.setTimestamp(6, Util.getCurrentTimeStamp());

        /*
         * stmtRecptId.setString(1, "CR_NOTE"); stmtRecptId.setInt(2,
         * org_id);
         */

        stmtRecptId.executeUpdate();

        // insert reciept id for delivery challan
        stmtRecptId = conn.prepareStatement(QueryManager.insertInReceiptMaster());
        stmtRecptId.setString(1, "BO");
        stmtRecptId.executeUpdate();

        ResultSet rsDC = stmtRecptId.getGeneratedKeys();
        while (rsDC.next()) {
            DCId = rsDC.getInt(1);
        }
        stmtRecptId = conn.prepareStatement(QueryManager.insertInBOReceipts());
        stmtRecptId.setInt(1, DCId);
        stmtRecptId.setString(2, "DSRCHALLAN");
        stmtRecptId.setInt(3, org_id);
        stmtRecptId.setString(4, "AGENT");
        stmtRecptId.setString(5, autoGeneDCNo);
        stmtRecptId.setTimestamp(6, Util.getCurrentTimeStamp());
        stmtRecptId.execute();

        // 4. Insert Entry in st_lms_bo_receipts_trn_mapping table.
        PreparedStatement stmtRcptTrnMapping = conn.prepareStatement(QueryManager.insertBOReceiptTrnMapping());
        for (int i = 0; i < trnIdList.size(); i++) {
            stmtRcptTrnMapping.setInt(1, receipt_id);
            stmtRcptTrnMapping.setInt(2, (Integer) trnIdList.get(i));
            stmtRcptTrnMapping.executeUpdate();
        }

        // 4. Insert Entry in st_lms_bo_receipts_trn_mapping table for
        // delivery
        // challan
        for (int i = 0; i < trnIdList.size(); i++) {
            stmtRcptTrnMapping.setInt(1, DCId);
            stmtRcptTrnMapping.setInt(2, (Integer) trnIdList.get(i));
            stmtRcptTrnMapping.executeUpdate();
        }

        // insert into invoice and delivery challan mapping table
        String insertCRNoteDCMapping = "insert into st_se_bo_invoice_delchallan_mapping(id,generated_invoice_id,generated_del_challan_id) values(?,?,?)";
        PreparedStatement boCRNoteDCMappingStmt = conn.prepareStatement(insertCRNoteDCMapping);
        boCRNoteDCMappingStmt.setInt(1, receipt_id);
        boCRNoteDCMappingStmt.setString(2, autoGeneRecieptNoBO);
        boCRNoteDCMappingStmt.setString(3, autoGeneDCNo);
        boCRNoteDCMappingStmt.executeUpdate();

        boolean isValid = OrgCreditUpdation.updateOrganizationBalWithValidate(netAmtOrg, "TRANSACTION",
                "SALE_RET", org_id, 0, "AGENT", 0, conn);

        if (!isValid)
            throw new LMSException();

        /*boolean isUpdateDone = OrgCreditUpdation.updateCreditLimitForAgent(
              org_id, "SALE_RET", netAmtOrg, conn);*/

        //         session.setAttribute("DEL_CHALLAN_ID", DCId);
        //         if (receipt_id > 0) {
        //            GraphReportHelper graphReportHelper = new GraphReportHelper();
        //            graphReportHelper.createTextReportBO(receipt_id, orgName,
        //                  userOrgId, rootPath);
        //         }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        throw new LMSException(e);
    }
    return String.valueOf(DCId) + "Nxt" + String.valueOf(receipt_id);
}

From source file:org.apache.ctakes.ytex.uima.mapper.DocumentMapperServiceImpl.java

private BiMap<Annotation, Integer> saveAnnoBase(final JCas jcas, final Set<String> setTypesToIgnore,
        final int docId) {
    final AnnotationIndex<Annotation> annoIdx = jcas.getAnnotationIndex(Annotation.typeIndexID);
    final List<Annotation> listAnno = new ArrayList<Annotation>(annoIdx.size());
    final BiMap<Annotation, Integer> mapAnnoToId = HashBiMap.create();
    final FSIterator<Annotation> annoIterator = annoIdx.iterator();
    this.sessionFactory.getCurrentSession().doWork(new Work() {

        @Override//from w  ww  .  j  a  v  a2s  .c  o m
        public void execute(Connection conn) throws SQLException {
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement("insert into " + getTablePrefix()
                        + "anno_base (document_id, span_begin, span_end, uima_type_id) values (?, ?, ?, ?)",
                        Statement.RETURN_GENERATED_KEYS);
                while (annoIterator.hasNext()) {
                    Annotation anno = (Annotation) annoIterator.next();
                    String annoClass = anno.getClass().getName();
                    if (!setTypesToIgnore.contains(annoClass) && uimaTypeMap.containsKey(annoClass)) {
                        // should not ignore, and we know how to map this
                        // annotation
                        listAnno.add(anno);
                        ps.setInt(1, docId);
                        ps.setInt(2, anno.getBegin());
                        ps.setInt(3, anno.getEnd());
                        ps.setInt(4, uimaTypeMap.get(annoClass).getUimaTypeID());
                        ps.addBatch();
                    }
                }
                ps.executeBatch();
                rs = ps.getGeneratedKeys();
                int annoIndex = 0;
                while (rs.next()) {
                    mapAnnoToId.put(listAnno.get(annoIndex), rs.getInt(1));
                    annoIndex++;
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                    }
                }
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (SQLException e) {
                    }
                }
            }
        }
    });
    return mapAnnoToId;
}

From source file:mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java

private GeneratedKey insertWithGeneratedKeyReturn(Object record, boolean useDeclaredOnly) {
    PreparedStatement st = null;
    boolean inTrans = inTransaction();
    String command = null;/*  w w w.  j  a v a  2 s.c  o m*/
    int batchCount = 0;
    GeneratedKey gk = null;
    if (!inTrans)
        startTransaction();
    try {
        Class objClass = record.getClass();
        String schema = getEntitySchema(objClass);
        Boolean isCamelCased = useCamelCase(objClass);
        HashMap<Method, String> fieldMapping = getFieldMapping(objClass, GET, isCamelCased, useDeclaredOnly);
        String idFieldName = getIdFieldName(fieldMapping);
        HashMap<Integer, Method> indexMapping = new HashMap();
        String tableName = getTableName(objClass);
        if (tableName == null)
            tableName = getDbName(isCamelCased, objClass.getSimpleName(), null);
        command = getInsertCommand(tableName, schema, fieldMapping, indexMapping);
        if (idFieldName != null) {
            st = conn.prepareStatement(command, new String[] { idFieldName });
        } else {
            st = conn.prepareStatement(command);
        }
        for (int index : indexMapping.keySet()) {
            Object value = indexMapping.get(index).invoke(record, null);
            if (value instanceof java.util.Date) {
                value = new java.sql.Date(((java.util.Date) value).getTime());
            }
            st.setObject((Integer) index, value);
        }
        st.execute();
        ResultSet rs = st.getGeneratedKeys();
        if (rs.next()) {
            gk = new GeneratedKey(idFieldName, rs.getObject(1));
        }
        if (!inTrans)
            commitTransaction();
        return gk;
    } catch (Exception ex) {
        ex.printStackTrace();
        if (!inTrans)
            rollbackTransaction();
        throw new DataQueryException(command, "insertWithGeneratedKeyReturn", ex);
    } finally {
        if (st != null) {
            try {
                st.close();
            } catch (Exception ex) {
            }
        }
    }
}

From source file:org.ut.biolab.medsavant.server.serverapi.VariantManager.java

@Override
public UserCommentGroup createUserCommentGroup(String sessID, int projectId, int refId, String chrom,
        long start_position, long end_position, String ref, String alt)
        throws RemoteException, SQLException, SessionExpiredException, IllegalArgumentException {
    UserCommentGroup lcg = getUserCommentGroup(sessID, projectId, refId, chrom, start_position, end_position,
            ref, alt, true);/*from   w w w . java2s .  c  om*/
    if (lcg != null) {
        throw new IllegalArgumentException("A comment group already exists at chrom=" + chrom + ", start="
                + start_position + " end=" + end_position + " ref=" + ref + " alt=" + alt);
    }

    //insert, get groupId and modDate
    TableSchema lcgTable = MedSavantDatabase.UserCommentGroupTableSchema;
    InsertQuery iq = new InsertQuery(lcgTable.getTable());
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_REFERENCE_ID), refId);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_CHROMOSOME), chrom);
    iq.addColumn(
            MedSavantDatabase.UserCommentGroupTableSchema
                    .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_START_POSITION),
            start_position);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema.getDBColumn(
            MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_END_POSITION), end_position);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_REF), ref);
    iq.addColumn(MedSavantDatabase.UserCommentGroupTableSchema
            .getDBColumn(MedSavantDatabase.UserCommentGroupTableSchema.COLUMNNAME_OF_ALT), alt);
    PooledConnection conn = ConnectionController.connectPooled(sessID);
    PreparedStatement stmt = null;
    ResultSet res = null;
    int groupId = -1;
    try {
        LOG.info(iq.toString());
        stmt = conn.prepareStatement(iq.toString(), Statement.RETURN_GENERATED_KEYS);
        stmt.execute();
        res = stmt.getGeneratedKeys();
        res.next();
        groupId = res.getInt(1);
    } catch (SQLException sqe) {
        LOG.error("SQL Error ", sqe);
        throw sqe;
    } finally {
        if (conn != null) {
            conn.close();
        }
        if (res != null) {
            res.close();
        }
        if (stmt != null) {
            stmt.close();
        }
    }

    if (groupId < 0) {
        throw new SQLException("Unable to create new group - invalid insertion id");
    }

    //The modification time is the time when a change was made to the last comment.  Since there are 
    //no comments yet, modstamp is null.
    //Date modStamp = DBUtils.getCurrentDatabaseTime(sessID);
    Date modStamp = null;
    lcg = new UserCommentGroup(groupId, projectId, refId, chrom, start_position, end_position, ref, alt,
            modStamp, null);
    return lcg;
}

From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCPathCache.java

/**
 * Method to add resource path entry to the database.
 *
 * @param path         the path to add./*from  w  ww  .j av  a  2s .com*/
 * @param parentPathId the parent path's id.
 *
 * @return the path's id.
 * @throws RepositoryException if the data access manager was invalid.
 * @throws SQLException      if an error occurs while adding the entry.
 */
public int addEntry(String path, int parentPathId) throws SQLException, RepositoryException {
    ResultSet results = null;
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    DataAccessManager dataAccessManager;

    if (CurrentContext.getRespository() != null
            && InternalUtils.getRepositoryContext(CurrentContext.getRespository()) != null) {
        dataAccessManager = InternalUtils.getRepositoryContext(CurrentContext.getRespository())
                .getDataAccessManager();
    } else {
        dataAccessManager = RepositoryContext.getBaseInstance().getDataAccessManager();
    }

    if (!(dataAccessManager instanceof JDBCDataAccessManager)) {
        String msg = "Failed to add path entry. Invalid data access manager.";
        log.error(msg);
        throw new RepositoryServerException(msg);
    }

    DataSource dataSource = ((JDBCDataAccessManager) dataAccessManager).getDataSource();
    Connection conn = dataSource.getConnection();

    if (conn != null) {
        if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }
        conn.setAutoCommit(false);
    } else {
        log.error("Unable to acquire connection to database.");
        return -1;
    }

    boolean success = false;
    int pathId = 0;

    try {
        String sql = "INSERT INTO REG_PATH(REG_PATH_VALUE, REG_PATH_PARENT_ID, REG_TENANT_ID) VALUES (?, ?, ?)";
        String sql1 = "SELECT MAX(REG_PATH_ID) FROM REG_PATH";

        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);

        if (returnsGeneratedKeys) {
            ps = conn.prepareStatement(sql,
                    new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_PATH_ID") });
        } else {
            ps = conn.prepareStatement(sql);
        }

        ps.setString(1, path);
        ps.setInt(2, parentPathId);
        ps.setInt(3, CurrentContext.getTenantId());

        if (returnsGeneratedKeys) {
            ps.executeUpdate();
            results = ps.getGeneratedKeys();
        } else {
            synchronized (ADD_ENTRY_LOCK) {
                ps.executeUpdate();
                ps1 = conn.prepareStatement(sql1);
                results = ps1.executeQuery();
            }
        }

        if (results.next()) {
            pathId = results.getInt(1);
            if (pathId > 0) {
                success = true;
                return pathId;
            }
        }
    } catch (SQLException e) {
        // we have to be expecting an exception with the duplicate value for the path value
        // which can be further checked from here..
        String msg = "Failed to insert resource to " + path + ". " + e.getMessage();
        log.error(msg, e);
        throw e;
    } finally {
        if (success) {
            try {
                conn.commit();
                RepositoryCacheEntry e = new RepositoryCacheEntry(pathId);
                String connectionId = null;

                if (conn.getMetaData() != null) {
                    connectionId = InternalUtils.getConnectionId(conn);
                }

                RepositoryCacheKey key = InternalUtils.buildRegistryCacheKey(connectionId,
                        CurrentContext.getTenantId(), path);
                getCache().put(key, e);
            } catch (SQLException e) {
                String msg = "Failed to commit transaction. Inserting " + path + ". " + e.getMessage();
                log.error(msg, e);
            } finally {
                try {
                    try {
                        if (results != null) {
                            results.close();
                        }
                    } finally {
                        try {
                            if (ps1 != null) {
                                ps1.close();
                            }
                        } finally {
                            try {
                                if (ps != null) {
                                    ps.close();
                                }
                            } finally {
                                conn.close();
                            }
                        }
                    }
                } catch (SQLException e) {
                    String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage();
                    log.error(msg, e);
                }
            }
        } else {
            try {
                conn.rollback();

            } catch (SQLException e) {
                String msg = "Failed to rollback transaction. Inserting " + path + ". " + e.getMessage();
                log.error(msg, e);
            } finally {
                try {
                    try {
                        if (results != null) {
                            results.close();
                        }
                    } finally {
                        try {
                            if (ps != null) {
                                ps.close();
                            }
                        } finally {
                            if (conn != null) {
                                conn.close();
                            }
                        }
                    }
                } catch (SQLException e) {
                    String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR + e.getMessage();
                    log.error(msg, e);
                }
            }
        }
    }
    return -1;
}