Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

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

Introduction

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

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

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

Usage

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

public int addComment(ResourceImpl resource, String userID, Comment comment) throws RegistryException {
    JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection();
    PreparedStatement ps1 = null, ps2 = null, ps3 = null;
    int commentId = -1;
    try {//from   ww w  .j  a va2 s  . c o m
        String sql1 = "INSERT INTO REG_COMMENT (REG_COMMENT_TEXT,"
                + "REG_USER_ID, REG_COMMENTED_TIME, REG_TENANT_ID) VALUES (?, ?, ?, ?)";
        String sql2 = "SELECT MAX(REG_ID) FROM REG_COMMENT";
        String sql3 = "INSERT INTO REG_RESOURCE_COMMENT (REG_COMMENT_ID, REG_PATH_ID, "
                + "REG_RESOURCE_NAME, REG_TENANT_ID) VALUES (?, ?, ?, ?)";
        String dbProductName = conn.getMetaData().getDatabaseProductName();
        boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
        if (returnsGeneratedKeys) {
            ps1 = conn.prepareStatement(sql1, new String[] {
                    DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, DatabaseConstants.ID_FIELD) });
        } else {
            ps1 = conn.prepareStatement(sql1);
        }
        ps3 = conn.prepareStatement(sql3);

        // prepare to execute query2 for the comments
        ps1.setString(1, comment.getText());
        ps1.setString(2, userID);

        long now = System.currentTimeMillis();
        ps1.setTimestamp(3, new Timestamp(now));
        ps1.setInt(4, CurrentSession.getTenantId());
        ResultSet resultSet1;
        if (returnsGeneratedKeys) {
            ps1.executeUpdate();
            resultSet1 = ps1.getGeneratedKeys();
        } else {
            synchronized (ADD_COMMENT_LOCK) {
                ps1.executeUpdate();
                ps2 = conn.prepareStatement(sql2);
                resultSet1 = ps2.executeQuery();
            }
        }
        try {
            if (resultSet1.next()) {
                // setting the RES_COMMENTS_ID
                commentId = resultSet1.getInt(1);

                ps3.setInt(1, commentId);
                ps3.setInt(2, resource.getPathID());
                ps3.setString(3, resource.getName());
                ps3.setInt(4, CurrentSession.getTenantId());
                ps3.executeUpdate();
            }
        } finally {
            if (resultSet1 != null) {
                resultSet1.close();
            }
        }

    } catch (SQLException e) {

        String msg = "Failed to add comments to the resource " + resource.getPath() + ". " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            try {
                if (ps1 != null) {
                    ps1.close();
                }
            } finally {
                try {
                    if (ps2 != null) {
                        ps2.close();
                    }
                } finally {
                    if (ps3 != null) {
                        ps3.close();
                    }
                }
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
    return commentId;
}

From source file:com.zuoxiaolong.dao.ArticleDao.java

public Integer saveOrUpdate(String resourceId, String subject, String createDate, Status status,
        String username, Integer accessTimes, Integer goodTimes, String html, String content,
        Type articleType) {//from ww w  .j  a va  2s .  co  m
    return execute((TransactionalOperation<Integer>) connection -> {
        String selectSql = "select id,status from articles where resource_id=?";
        String insertSql = "insert into articles (resource_id,username,icon,create_date,"
                + "access_times,good_times,subject,html,content,status,type) values (?,?,?,?,?,?,?,?,?,?,?)";
        String updateSql = "update articles set subject=?,html=?,content=?,icon=?,status=?,create_date=?,type=? where resource_id=? ";
        try {
            PreparedStatement statement = connection.prepareStatement(selectSql);
            statement.setString(1, resourceId);
            ResultSet resultSet = statement.executeQuery();
            Boolean exists = false;
            Status currentStatus = Status.draft;
            Integer id = null;
            if (resultSet.next()) {
                exists = true;
                currentStatus = Status.valueOf(resultSet.getInt("status"));
                id = resultSet.getInt("id");
            }
            PreparedStatement saveOrUpdate = null;
            if (!exists) {
                saveOrUpdate = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                saveOrUpdate.setString(1, resourceId);
                saveOrUpdate.setString(2, username);
                saveOrUpdate.setString(3, ImageUtil.randomArticleImage(subject, articleType));
                saveOrUpdate.setString(4, createDate);
                saveOrUpdate.setInt(5, accessTimes);
                saveOrUpdate.setInt(6, goodTimes);
                saveOrUpdate.setString(7, subject);
                saveOrUpdate.setString(8, html);
                saveOrUpdate.setString(9, content);
                saveOrUpdate.setInt(10, status.getIntValue());
                saveOrUpdate.setInt(11, articleType.getIntValue());
            } else {
                saveOrUpdate = connection.prepareStatement(updateSql);
                saveOrUpdate.setString(1, subject);
                saveOrUpdate.setString(2, html);
                saveOrUpdate.setString(3, content);
                saveOrUpdate.setString(4, ImageUtil.randomArticleImage(subject, articleType));
                saveOrUpdate.setInt(5,
                        currentStatus == Status.published ? currentStatus.getIntValue() : status.getIntValue());
                saveOrUpdate.setString(6, createDate);
                saveOrUpdate.setInt(7, articleType.getIntValue());
                saveOrUpdate.setString(8, resourceId);
            }
            int result = saveOrUpdate.executeUpdate();
            if (!exists && result > 0) {
                ResultSet keyResultSet = saveOrUpdate.getGeneratedKeys();
                if (keyResultSet.next()) {
                    id = keyResultSet.getInt(1);
                }
            }
            return id;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    });
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public Service addService(ServiceInfo serviceInfo, String name, String metadata, String config,
        boolean bootAtStartup) {
    Service service = null;/* w ww .  j av  a  2  s  . c  o  m*/
    if (serviceInfo == null || name == null) {
        Log.e(TAG, "One cannot create a service where the serviceInfo is null, or name is null");
        return null;
    }
    try {
        checkOpenness();
        connection.setAutoCommit(false);
        String sqlInsert = "INSERT INTO " + IotHubDataHandler.TABLE_SERVICE + "("
                + IotHubDataHandler.KEY_SERVICE_NAME + "," + IotHubDataHandler.KEY_SERVICE_METADATA + ","
                + IotHubDataHandler.KEY_SERVICE_SERVICE_INFO + "," + IotHubDataHandler.KEY_SERVICE_CONFIG + ","
                + IotHubDataHandler.KEY_SERVICE_BOOT_AT_STARTUP + ") VALUES (?,?,?,?,?)";
        PreparedStatement psInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS);
        psInsert.setString(1, name);
        psInsert.setString(2, metadata);
        psInsert.setLong(3, serviceInfo.getId());
        psInsert.setString(4, config);
        psInsert.setBoolean(5, bootAtStartup);
        psInsert.executeUpdate();
        ResultSet genKeys = psInsert.getGeneratedKeys();
        if (genKeys.next()) {
            long insertId = genKeys.getLong(1);
            //At point we should have everything set so it is time to retrieve the plugin from the database
            //Log.d(TAG, "Now i will try to collect the service " + insertId + " that was just added to the db");
            service = getService(insertId);
            if (service == null) {
                Log.e(TAG, "The service " + name + " should not be null");
            }
        } else {
            Log.e(TAG, "The insert of service " + name + " did not work");
        }
        genKeys.close();
        psInsert.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        service = null;
    }
    try {
        if (service == null) {
            connection.rollback();
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return service;
}

From source file:com.adanac.module.blog.dao.ArticleDao.java

public Integer saveOrUpdate(String id, String subject, Status status, Type type, Integer updateCreateTime,
        String username, String html, String content, String icon) {
    return execute((TransactionalOperation<Integer>) connection -> {
        String insertSql = "insert into articles (subject,username,icon,create_date,"
                + "html,content,status,type) values (?,?,?,?,?,?,?,?)";
        String updateSql = "update articles set subject=?,username=?,icon=?,html=?,content=?,status=?,type=? where id=?";
        if (updateCreateTime == 1) {
            updateSql = "update articles set subject=?,username=?,icon=?,html=?,content=?,status=?,type=?,create_date=? where id=?";
        }//from ww  w .  j av a  2 s  .co m
        try {
            PreparedStatement statement = null;
            if (StringUtils.isBlank(id)) {
                statement = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                statement.setString(1, subject);
                statement.setString(2, username);
                statement.setString(3, icon == null ? ImageUtil.randomArticleImage(subject, type) : icon);
                statement.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
                statement.setString(5, html);
                statement.setString(6, content);
                statement.setInt(7, status.getIntValue());
                statement.setInt(8, type.getIntValue());
            } else {
                statement = connection.prepareStatement(updateSql);
                statement.setString(1, subject);
                statement.setString(2, username);
                statement.setString(3, icon == null ? ImageUtil.randomArticleImage(subject, type) : icon);
                statement.setString(4, html);
                statement.setString(5, content);
                statement.setInt(6, status.getIntValue());
                statement.setInt(7, type.getIntValue());
                if (updateCreateTime == 1) {
                    statement.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
                    statement.setInt(9, Integer.valueOf(id));
                } else {
                    statement.setInt(8, Integer.valueOf(id));
                }
            }
            int result = statement.executeUpdate();
            if (result > 0 && StringUtils.isBlank(id)) {
                ResultSet keyResultSet = statement.getGeneratedKeys();
                if (keyResultSet.next()) {
                    return keyResultSet.getInt(1);
                }
            }
            if (result > 0) {
                return Integer.valueOf(id);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    });
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public Enabler addEnabler(String name, String metadata, PluginInfo plugin, String pluginInfoConfig) {
    Enabler enabler = null;// w  w w.j a v  a  2 s  . co  m
    if (name == null || plugin == null) {
        Log.e(TAG, "One cannot create a enabler where name is null or with no plugin");
        return null;
    }
    try {
        checkOpenness();
        connection.setAutoCommit(false);
        String sqlEnablerInsert = "INSERT INTO " + IotHubDataHandler.TABLE_ENABLER + "("
                + IotHubDataHandler.KEY_ENABLER_NAME + "," + IotHubDataHandler.KEY_ENABLER_METADATA + ","
                + IotHubDataHandler.KEY_ENABLER_PLUGIN_INFO + ","
                + IotHubDataHandler.KEY_ENABLER_PLUGIN_INFO_CONFIG + ") VALUES (?,?,?,?)";
        PreparedStatement psEnablerInsert = connection.prepareStatement(sqlEnablerInsert,
                Statement.RETURN_GENERATED_KEYS);
        psEnablerInsert.setString(1, name);
        psEnablerInsert.setString(2, metadata);
        psEnablerInsert.setLong(3, plugin.getId());
        psEnablerInsert.setString(4, pluginInfoConfig);
        psEnablerInsert.executeUpdate();
        ResultSet genKeysEnabler = psEnablerInsert.getGeneratedKeys();
        if (genKeysEnabler.next()) {
            long insertIdEnabler = genKeysEnabler.getLong(1);
            //At point we should have everything set so it is time to retrieve the plugin from the database
            //Log.d(TAG, "Now i will try to collect the enabler that was just added to the db");
            enabler = getEnabler(insertIdEnabler);
            if (enabler == null) {
                Log.e(TAG, "The enabler should not be null");
            }
            //TODO maybe check that the plugins are the same
        } else {
            Log.e(TAG, "The insert of enabler " + name + " did not work");
        }
        genKeysEnabler.close();
        psEnablerInsert.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        enabler = null;
    }
    try {
        if (enabler == null) {
            connection.rollback();
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return enabler;
}

From source file:org.plasma.sdo.jdbc.service.JDBCSupport.java

protected List<PropertyPair> executeInsert(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values,
        Connection con) {/*from   w ww.jav a2 s .c om*/
    List<PropertyPair> resultKeys = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    ResultSet generatedKeys = null;
    try {
        statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);

        StringBuilder paramBuf = null;
        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            paramBuf = new StringBuilder();
            paramBuf.append("[");
        }

        int i = 1;
        for (PropertyPair pair : values.values()) {
            int jdbcType = converter.toJDBCDataType(pair.getProp(), pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(pair.getProp(), pair.getValue());
            statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            if (log.isDebugEnabled()) {
                if (i > 1) {
                    paramBuf.append(", ");
                }
                paramBuf.append("(");
                paramBuf.append(jdbcValue.getClass().getSimpleName());
                paramBuf.append("/");
                paramBuf.append(converter.getJdbcTypeName(jdbcType));
                paramBuf.append(")");
                paramBuf.append(String.valueOf(jdbcValue));
            }
            i++;
        }
        if (log.isDebugEnabled()) {
            paramBuf.append("]");
            log.debug("params: " + paramBuf.toString());
        }
        statement.execute();
        generatedKeys = statement.getGeneratedKeys();
        //if (generatedKeys.next()) {
        //   resultKeys.add(generatedKeys.getObject(1));
        //}
        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 us 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);

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

    return resultKeys;
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

private void addFeedFields(long id, List<FieldDescription> fields) throws SQLException {
    if (fields == null || fields.size() == 0) {
        Log.e(TAG, "One cannot create a composed feed with no fields");
        return;//w  ww .j a  v a  2  s  .  co  m
    }
    String sqlInsert = "INSERT INTO " + IotHubDataHandler.TABLE_FIELD + "("
            + IotHubDataHandler.KEY_FIELD_FEED_ID + "," + IotHubDataHandler.KEY_FIELD_NAME + ","
            + IotHubDataHandler.KEY_FIELD_METADATA + "," + IotHubDataHandler.KEY_FIELD_TYPE + ","
            + IotHubDataHandler.KEY_FIELD_OPTIONAL + ") values (?,?,?,?,?)";
    PreparedStatement psInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS);
    for (FieldDescription fd : fields) {
        psInsert.setLong(1, id);
        psInsert.setString(2, fd.getName());
        psInsert.setString(3, fd.getMetadata());
        psInsert.setString(4, fd.getType());
        psInsert.setInt(5, fd.isOptional() ? 1 : 0);
        psInsert.executeUpdate();
        ResultSet genKeysFeed = psInsert.getGeneratedKeys();
        if (genKeysFeed.next()) {
            long idField = genKeysFeed.getLong(1);
            addFieldKeywords(idField, fd.getKeywords());
        }
        genKeysFeed.close();
    }
    psInsert.close();
}

From source file:com.wso2telco.dep.mediator.dao.USSDDAO.java

/**
 * Ussd request entry./*  w w  w .  jav a 2  s.  co  m*/
 *
 * @param notifyURL
 *            the notifyURL
 * @return the integer
 * @throws Exception
 *             the exception
 */
public Integer ussdRequestEntry(String notifyURL, String consumerKey, String operatorId, String userId)
        throws SQLException, Exception {

    Connection con = null;
    PreparedStatement insert_statement = null;
    PreparedStatement select_statement = null;
    ResultSet insert_result = null;
    ResultSet select_result = null;
    Integer selectId = 0;
    Integer newId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        if (con == null) {

            throw new Exception("Connection not found");
        }

        con.setAutoCommit(false);
        StringBuilder queryString = new StringBuilder(" SELECT MAX(ussd_request_did) maxid ");
        queryString.append("FROM ");
        queryString.append(DatabaseTables.USSD_REQUEST_ENTRY.getTableName());

        select_statement = con.prepareStatement(queryString.toString());
        select_result = select_statement.executeQuery(queryString.toString());

        if (select_result.next()) {
            selectId = select_result.getInt("maxid") + 1;
        }

        StringBuilder insertQueryString = new StringBuilder(" INSERT INTO ");
        insertQueryString.append(DatabaseTables.USSD_REQUEST_ENTRY.getTableName());
        insertQueryString.append(" (ussd_request_did,notifyurl,sp_consumerKey,operatorId,userId) ");
        insertQueryString.append("VALUES (?, ? ,? ,? ,? )");

        insert_statement = con.prepareStatement(insertQueryString.toString(), Statement.RETURN_GENERATED_KEYS);

        insert_statement.setInt(1, selectId);
        insert_statement.setString(2, notifyURL);
        insert_statement.setString(3, consumerKey);
        insert_statement.setString(4, operatorId);
        insert_statement.setString(5, userId);

        log.debug("sql query in ussdRequestEntry : " + insert_statement);

        insert_statement.executeUpdate();

        insert_result = insert_statement.getGeneratedKeys();

        while (insert_result.next()) {
            newId = insert_result.getInt(1);
        }

    } catch (SQLException e) {

        log.error("database operation error in ussdRequestEntry : ", e);
        throw e;
    } catch (Exception e) {

        log.error("error in ussdRequestEntry : ", e);
        throw e;
    } finally {

        DbUtils.closeAllConnections(insert_statement, con, insert_result);
        DbUtils.closeAllConnections(select_statement, null, select_result);

    }

    return newId;
}

From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java

@Override
public Feature addFeature(Enabler enabler, String name, String type) {
    Feature feature = null;/*from   www. jav  a 2  s.c o  m*/
    if (enabler == null || name == null || type == null) {
        Log.e(TAG, "One cannot create a feature where the enable is null, name is null or with no type");
        return null;
    }
    try {
        checkOpenness();
        connection.setAutoCommit(false);
        String sqlFeatureInsert = "INSERT INTO " + IotHubDataHandler.TABLE_FEATURE + "("
                + IotHubDataHandler.KEY_FEATURE_ENABLER_ID + "," + IotHubDataHandler.KEY_FEATURE_NAME + ","
                + IotHubDataHandler.KEY_FEATURE_TYPE + "," + IotHubDataHandler.KEY_FEATURE_IS_FEED
                + ") VALUES (?,?,?,?)";
        PreparedStatement psFeatureInsert = connection.prepareStatement(sqlFeatureInsert,
                Statement.RETURN_GENERATED_KEYS);
        psFeatureInsert.setLong(1, enabler.getId());
        psFeatureInsert.setString(2, name);
        psFeatureInsert.setString(3, type);
        psFeatureInsert.setBoolean(4, false); //An added feature is never a feed
        psFeatureInsert.executeUpdate();
        ResultSet genKeysFeature = psFeatureInsert.getGeneratedKeys();
        if (genKeysFeature.next()) {
            long insertIdFeature = genKeysFeature.getLong(1);
            //At point we should have everything set so it is time to retrieve the plugin from the database
            //Log.d(TAG, "Now i will try to collect the feature that was just added to the db");
            feature = getFeature(insertIdFeature);
            if (feature == null) {
                Log.e(TAG, "The feature should not be null");
            }
        } else {
            Log.e(TAG, "The insert of feature " + name + " did not work");
        }
        genKeysFeature.close();
        psFeatureInsert.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        feature = null;
    }
    try {
        if (feature == null) {
            connection.rollback();
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return feature;
}

From source file:org.wso2.carbon.rssmanager.core.dao.impl.DatabaseUserDAOImpl.java

/**
 * @see DatabaseUserDAO#addDatabaseUser(java.sql.PreparedStatement, org.wso2.carbon.rssmanager.core.dto.restricted.DatabaseUser)
 *///from   w  w w  . ja  v  a2  s  .  co m
public void addDatabaseUser(PreparedStatement nativeAddUserStatement, DatabaseUser user)
        throws RSSDAOException, RSSDatabaseConnectionException {
    Connection conn = null;
    PreparedStatement createUserStatement = null;
    PreparedStatement createUserEntryStatement;
    ResultSet result = null;
    try {
        conn = getDataSourceConnection();//acquire data source connection
        //start transaction with setting auto commit value to false
        conn.setAutoCommit(false);
        String createDBUserQuery = "INSERT INTO RM_DATABASE_USER(USERNAME, ENVIRONMENT_ID, TYPE, TENANT_ID) VALUES(?,?,?,?)";
        if (!RSSManagerConstants.RSSManagerTypes.RM_TYPE_SYSTEM.equalsIgnoreCase(user.getType())) {
            createDBUserQuery = "INSERT INTO RM_DATABASE_USER(USERNAME, ENVIRONMENT_ID, TYPE, TENANT_ID, RSS_INSTANCE_ID) VALUES(?,?,?,?,?)";
        }

        createUserStatement = conn.prepareStatement(createDBUserQuery, Statement.RETURN_GENERATED_KEYS);
        //insert user data to the statement to insert
        createUserStatement.setString(1, user.getName());
        createUserStatement.setInt(2, user.getEnvironmentId());
        createUserStatement.setString(3, user.getType());
        createUserStatement.setInt(4, user.getTenantId());
        /*if(RSSManagerConstants.RSSManagerTypes.RM_TYPE_SYSTEM.equalsIgnoreCase(user.getType())){
           createUserStatement.setString(5, user.getType());
        } else {
           createUserStatement.setString(5, user.getInstances().iterator().next().getName());
        }*/
        if (!RSSManagerConstants.RSSManagerTypes.RM_TYPE_SYSTEM.equalsIgnoreCase(user.getType())) {
            createUserStatement.setInt(5, user.getInstances().iterator().next().getId());
        }

        createUserStatement.executeUpdate();
        //get the inserted database user id from result
        //which will be inserted as a foreign key to user rss instance entry table
        result = createUserStatement.getGeneratedKeys();
        //if user inserted to several rss instances, then add rss instances user entries as batch operation to the table
        String createDBUserEntryQuery = "INSERT INTO RM_USER_INSTANCE_ENTRY(RSS_INSTANCE_ID, DATABASE_USER_ID) VALUES(?,?)";
        createUserEntryStatement = conn.prepareStatement(createDBUserEntryQuery);
        while (result.next()) {
            for (RSSInstance rssInstance : user.getInstances()) {
                createUserEntryStatement.setInt(1, rssInstance.getId());
                createUserEntryStatement.setInt(2, result.getInt(1));
                createUserEntryStatement.addBatch();
            }
            createUserEntryStatement.executeBatch();
        }
        if (nativeAddUserStatement != null) {
            //since native user add statements are not transactional, execute add user statement will add new
            //user to the rss instance
            nativeAddUserStatement.executeUpdate();
        }
        conn.commit();
    } catch (SQLException e) {
        RSSDAOUtil.rollback(conn, RSSManagerConstants.ADD_DATABASE_USER_ENTRY);
        String msg = "Failed to add database user" + user.getName() + "in rssInstance"
                + user.getRssInstanceName() + "to meta repository";
        handleException(msg, e);
    } finally {
        RSSDAOUtil.cleanupResources(result, createUserStatement, conn,
                RSSManagerConstants.ADD_DATABASE_USER_ENTRY);
    }
}