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.logger.TrackServlet.java

private String getOrganizationId(JSONArray org) {
    String id = "1";
    String name = "";
    String address = "";
    String city = "";
    String stateProv = "";
    String postalCode = "";
    String country = "";
    for (int j = 0; j < org.length(); j++) {
        try {//from  w  w w .  j av a 2 s .co m
            JSONObject temp = org.getJSONObject(j);
            if (String.valueOf(temp.get("key")).equalsIgnoreCase("Organization")
                    || String.valueOf(temp.get("key")).equalsIgnoreCase("OrgName")
                    || String.valueOf(temp.get("key")).equalsIgnoreCase("Customer")
                    || String.valueOf(temp.get("key")).equalsIgnoreCase("CustName")
                    || String.valueOf(temp.get("key")).equalsIgnoreCase("owner")
                    || String.valueOf(temp.get("key")).equalsIgnoreCase("descr")) {
                name = String.valueOf(temp.get("value"));
            }
            if (String.valueOf(temp.get("key")).equalsIgnoreCase("Address")) {
                address = String.valueOf(temp.get("value"));
            }
            if (String.valueOf(temp.get("key")).equalsIgnoreCase("City")) {
                city = String.valueOf(temp.get("value"));
            }
            if (String.valueOf(temp.get("key")).equalsIgnoreCase("StateProv")) {
                stateProv = String.valueOf(temp.get("value"));
            }
            if (String.valueOf(temp.get("key")).equalsIgnoreCase("PostalCode")) {
                postalCode = String.valueOf(temp.get("value"));
            }
            if (String.valueOf(temp.get("key")).equalsIgnoreCase("Country")) {
                country = String.valueOf(temp.get("value"));
            }
        } catch (JSONException ex) {
            Logger.getLogger(TrackServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    java.sql.Connection con = null;
    java.sql.PreparedStatement stmt = null;
    java.sql.ResultSet rs = null;
    try {
        con = ControlPanelPool.getInstance().getConnection();
        stmt = con.prepareStatement("SELECT Id FROM LeadOrganization WHERE Name = ?");
        stmt.setString(1, name);
        rs = stmt.executeQuery();
        while (rs.next()) {
            id = rs.getString("Id");
        }
        if (!name.isEmpty() && id.equals("1")) {
            stmt = con.prepareStatement(
                    "INSERT INTO [dbo].[LeadOrganization] ([Name],[Address],[City],[StateProv],[PostalCode],[Country]) VALUES (?,?,?,?,?,?)",
                    Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, name);
            stmt.setString(2, address);
            stmt.setString(3, city);
            stmt.setString(4, stateProv);
            stmt.setString(5, postalCode);
            stmt.setString(6, country);
            stmt.execute();
            rs = stmt.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getString(1);
            }
        }
        con.close();
    } catch (IOException | SQLException | PropertyVetoException ex) {
        Logger.getLogger(TrackServlet.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        DbUtils.closeQuietly(con, stmt, rs);
    }
    return id;
}

From source file:com.nabla.wapp.server.auth.UserManager.java

public boolean initializeDatabase(final IRoleListProvider roleListProvider, final String rootPassword)
        throws SQLException {
    Assert.argumentNotNull(roleListProvider);

    final LockTableGuard lock = new LockTableGuard(conn, LOCK_USER_TABLES);
    try {/*from  w ww.j  a v  a  2s  .  co m*/
        if (!Database.isTableEmpty(conn, IRoleTable.TABLE))
            return true;
        if (log.isDebugEnabled())
            log.debug("initializing role tables");
        final Map<String, String[]> roles = roleListProvider.get();
        Assert.state(!roles.containsKey(IRootUser.NAME));
        final ConnectionTransactionGuard guard = new ConnectionTransactionGuard(conn);
        try {
            final PreparedStatement stmtRole = conn.prepareStatement(
                    "INSERT INTO role (name,uname,privilege,internal) VALUES(?,?,?,?);",
                    Statement.RETURN_GENERATED_KEYS);
            final Map<String, Integer> roleIds = new HashMap<String, Integer>();
            try {
                stmtRole.clearBatch();
                stmtRole.setBoolean(4, true);
                // add privileges and default roles
                for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                    stmtRole.setString(1, role.getKey());
                    stmtRole.setString(2, role.getKey().toUpperCase());
                    stmtRole.setBoolean(3, role.getValue() == null);
                    stmtRole.addBatch();
                }
                if (!Database.isBatchCompleted(stmtRole.executeBatch()))
                    return false;
                final ResultSet rsKey = stmtRole.getGeneratedKeys();
                try {
                    for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                        rsKey.next();
                        roleIds.put(role.getKey(), rsKey.getInt(1));
                    }
                } finally {
                    rsKey.close();
                }
            } finally {
                stmtRole.close();
            }
            final PreparedStatement stmtDefinition = conn
                    .prepareStatement("INSERT INTO role_definition (role_id,child_role_id) VALUES(?,?);");
            try {
                stmtDefinition.clearBatch();
                for (final Map.Entry<String, String[]> role : roles.entrySet()) {
                    final String[] definition = role.getValue();
                    if (definition == null)
                        continue;
                    stmtDefinition.setInt(1, roleIds.get(role.getKey()));
                    for (final String child : definition) {
                        final Integer childId = roleIds.get(child);
                        if (childId == null) {
                            if (log.isErrorEnabled())
                                log.error("child role '" + child + "' not defined!");
                            return false;
                        }
                        stmtDefinition.setInt(2, childId);
                        stmtDefinition.addBatch();
                    }
                }
                if (!Database.isBatchCompleted(stmtDefinition.executeBatch()))
                    return false;
            } finally {
                stmtDefinition.close();
            }
            // add 'root' user
            Database.executeUpdate(conn, "INSERT INTO user (name,uname,active,password) VALUES(?,?,TRUE,?);",
                    IRootUser.NAME, IRootUser.NAME.toUpperCase(),
                    getPasswordEncryptor().encryptPassword(rootPassword));
            return guard.setSuccess(true);
        } finally {
            guard.close();
        }
    } finally {
        lock.close();
    }
}

From source file:uk.ac.cam.cl.dtg.isaac.dos.eventbookings.PgEventBookings.java

@Override
public EventBooking add(final String eventId, final Long userId, final BookingStatus status,
        Map<String, String> additionalEventInformation) throws SegueDatabaseException {
    PreparedStatement pst;

    if (null == additionalEventInformation) {
        additionalEventInformation = Maps.newHashMap();
    }//from  w ww .  ja v a  2  s  . c  o  m

    try (Connection conn = ds.getDatabaseConnection()) {
        Date creationDate = new Date();
        pst = conn.prepareStatement(
                "INSERT INTO event_bookings (id, user_id, event_id, status, created, updated, additional_booking_information) VALUES (DEFAULT, ?, ?, ?, ?, ?, ?::text::jsonb)",
                Statement.RETURN_GENERATED_KEYS);
        pst.setLong(1, userId);
        pst.setString(2, eventId);
        pst.setString(3, status.name());
        pst.setTimestamp(4, new java.sql.Timestamp(creationDate.getTime()));
        pst.setTimestamp(5, new java.sql.Timestamp(creationDate.getTime()));
        pst.setString(6, objectMapper.writeValueAsString(additionalEventInformation));
        if (pst.executeUpdate() == 0) {
            throw new SegueDatabaseException("Unable to save event booking.");
        }

        try (ResultSet generatedKeys = pst.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                Long id = generatedKeys.getLong(1);
                return new PgEventBooking(ds, id, userId, eventId, status, creationDate, creationDate,
                        additionalEventInformation);
            } else {
                throw new SQLException("Creating event booking failed, no ID obtained.");
            }
        }

    } catch (SQLException e) {
        throw new SegueDatabaseException("Postgres exception", e);
    } catch (JsonProcessingException e) {
        throw new SegueDatabaseException("Unable to convert json to string for persistence.", e);
    }
}

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

/**
 * @see UserDatabaseEntryDAO#addUserDatabaseEntry(java.sql.PreparedStatement, String, UserDatabaseEntry, int)
 *///w  ww  . ja  v a  2  s.  c om
public int addUserDatabaseEntry(PreparedStatement nativeAttachUserStatement, String environmentName,
        UserDatabaseEntry entry, int tenantId) throws RSSDAOException, RSSDatabaseConnectionException {
    if (entry == null) {
        return -1;
    }
    Connection conn = null;
    PreparedStatement userEntryStatement = null;
    PreparedStatement userPrivilegeEntryStatement = null;
    ResultSet resultSet = null;
    int userEntryId = 0;
    try {
        conn = getDataSourceConnection();//acquire data source connection
        //start transaction with setting auto commit value to false
        conn.setAutoCommit(false);
        String userEntrySql = "INSERT INTO RM_USER_DATABASE_ENTRY(DATABASE_USER_ID, DATABASE_ID) VALUES (?,?)";
        userEntryStatement = conn.prepareStatement(userEntrySql, Statement.RETURN_GENERATED_KEYS);
        userEntryStatement.setInt(1, entry.getUserId());
        userEntryStatement.setInt(2, entry.getDatabaseId());
        userEntryStatement.executeUpdate();
        //get the result of the id inserted to the database user entry table which needs to be inserted to
        //user privilege table as a foreign key
        resultSet = userEntryStatement.getGeneratedKeys();
        while (resultSet.next()) {
            userEntryId = resultSet.getInt(1);
            UserDatabasePrivilege privileges = entry.getUserPrivileges();
            String insertTemplateEntryQuery = "INSERT INTO RM_USER_DATABASE_PRIVILEGE(USER_DATABASE_ENTRY_ID, SELECT_PRIV, "
                    + "INSERT_PRIV, UPDATE_PRIV, DELETE_PRIV, CREATE_PRIV, DROP_PRIV, GRANT_PRIV, REFERENCES_PRIV, "
                    + "INDEX_PRIV, ALTER_PRIV, CREATE_TMP_TABLE_PRIV, LOCK_TABLES_PRIV, CREATE_VIEW_PRIV, SHOW_VIEW_PRIV, "
                    + "CREATE_ROUTINE_PRIV, ALTER_ROUTINE_PRIV, EXECUTE_PRIV, EVENT_PRIV, TRIGGER_PRIV) VALUES "
                    + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            userPrivilegeEntryStatement = conn.prepareStatement(insertTemplateEntryQuery);
            //set data to be inserted
            userPrivilegeEntryStatement.setInt(1, userEntryId);
            userPrivilegeEntryStatement.setString(2, privileges.getSelectPriv());
            userPrivilegeEntryStatement.setString(3, privileges.getInsertPriv());
            userPrivilegeEntryStatement.setString(4, privileges.getUpdatePriv());
            userPrivilegeEntryStatement.setString(5, privileges.getDeletePriv());
            userPrivilegeEntryStatement.setString(6, privileges.getCreatePriv());
            userPrivilegeEntryStatement.setString(7, privileges.getDropPriv());
            userPrivilegeEntryStatement.setString(8, privileges.getGrantPriv());
            userPrivilegeEntryStatement.setString(9, privileges.getReferencesPriv());
            userPrivilegeEntryStatement.setString(10, privileges.getIndexPriv());
            userPrivilegeEntryStatement.setString(11, privileges.getAlterPriv());
            userPrivilegeEntryStatement.setString(12, privileges.getCreateTmpTablePriv());
            userPrivilegeEntryStatement.setString(13, privileges.getLockTablesPriv());
            userPrivilegeEntryStatement.setString(14, privileges.getCreateViewPriv());
            userPrivilegeEntryStatement.setString(15, privileges.getShowViewPriv());
            userPrivilegeEntryStatement.setString(16, privileges.getCreateRoutinePriv());
            userPrivilegeEntryStatement.setString(17, privileges.getAlterRoutinePriv());
            userPrivilegeEntryStatement.setString(18, privileges.getExecutePriv());
            userPrivilegeEntryStatement.setString(19, privileges.getEventPriv());
            userPrivilegeEntryStatement.setString(20, privileges.getTriggerPriv());
            userPrivilegeEntryStatement.executeUpdate();
        }
        //native user attachment to database statement is not transactional since it will executed after entry is insert
        //user entry to meta repository
        if (nativeAttachUserStatement != null) {
            nativeAttachUserStatement.executeUpdate();
        }
        conn.commit();
    } catch (SQLException e) {
        RSSDAOUtil.rollback(conn, RSSManagerConstants.ADD_USER_PRIVILEGE_TEMPLATE_ENTRY);
        String msg = "Failed to add database user entry to meta repository";
        handleException(msg, e);
    } finally {
        RSSDAOUtil.cleanupResources(null, userPrivilegeEntryStatement, null,
                RSSManagerConstants.ADD_USER_PRIVILEGE_TEMPLATE_ENTRY);
        RSSDAOUtil.cleanupResources(resultSet, userEntryStatement, conn,
                RSSManagerConstants.ADD_USER_PRIVILEGE_TEMPLATE_ENTRY);
    }
    return userEntryId;
}

From source file:org.apache.hadoop.raid.DBStripeStore.java

@Override
public void putStripe(Codec codec, List<Block> parityBlks, List<Block> srcBlks) throws IOException {

    if (parityBlks.size() != codec.parityLength) {
        throw new IOException("Number of parity blocks " + parityBlks.size() + " doesn't match codec "
                + codec.id + " (" + codec.parityLength + ")");
    }//from   w ww. ja va2 s  .c  om
    if (srcBlks.size() > codec.stripeLength) {
        throw new IOException("Number of source blocks " + srcBlks.size() + " is greater than codec " + codec.id
                + " (" + codec.stripeLength + ")");
    }

    List<Object> getStripeSqlParams = constructGetStripeSqlParam(codec, parityBlks, srcBlks);
    String insertStripeSql = getInsertStripeSql(parityBlks, srcBlks);

    int waitMS = 3000; // wait for at least 3sec before next retry.
    Random rand = new Random();
    for (int i = 0; i < sqlNumRetries; ++i) {
        Connection conn = null;
        PreparedStatement getStripeStatement = null;
        ResultSet generatedKeys = null;
        PreparedStatement insertStripeStatement = null;
        String url = null;
        try {
            try {
                url = connectionFactory.getUrl(true);
            } catch (IOException ioe) {
                LOG.warn("Cannot get DB URL, fall back to the default one:" + defaultUrl, ioe);
                url = defaultUrl;
                if (url == null) {
                    throw ioe;
                }
            }
            LOG.info("Attepting connection with URL " + url);
            conn = connectionFactory.getConnection(url);
            conn.setAutoCommit(false);
            defaultUrl = url;
            getStripeStatement = DBUtils.getPreparedStatement(conn, NEW_STRIPE_ID_SQL, getStripeSqlParams,
                    true);
            int recordsUpdated = getStripeStatement.executeUpdate();
            LOG.info("rows inserted: " + recordsUpdated + " sql: " + NEW_STRIPE_ID_SQL);
            generatedKeys = getStripeStatement.getGeneratedKeys();
            List<List<Object>> results = DBUtils.getResults(generatedKeys);
            Long stripeId = (Long) results.get(0).get(0);
            List<Object> insertStripeSqlParams = constructInsertStripeSqlParam(codec, parityBlks, srcBlks,
                    stripeId);
            insertStripeStatement = DBUtils.getPreparedStatement(conn, insertStripeSql, insertStripeSqlParams,
                    false);
            recordsUpdated = insertStripeStatement.executeUpdate();
            conn.commit();
            LOG.info("rows inserted: " + recordsUpdated + " sql: " + insertStripeSql);
            StripeInfo si = new StripeInfo(codec, null, parityBlks, srcBlks);
            LOG.info("Put " + si + " into stripe store");
            Thread.sleep(putStripeSleepTime + rand.nextInt(1000));
            return;
        } catch (Exception e) {
            // We should catch a better exception than Exception, but since
            // DBConnectionUrlFactory.getUrl() defines throws Exception, it's hard
            // for us to figure out the complete set it can throw. We follow
            // DBConnectionUrlFactory.getUrl()'s definition to catch Exception.
            // It shouldn't be a big problem as after numRetries, we anyway exit.
            LOG.info("Exception " + e + ". Will retry " + (sqlNumRetries - i) + " times.");
            // Introducing a random factor to the wait time before another retry.
            // The wait time is dependent on # of failures and a random factor.
            // At the first time of getting a SQLException, the wait time
            // is a random number between [0,300] msec. If the first retry
            // still fails, we will wait 300 msec grace period before the 2nd retry.
            // Also at the second retry, the waiting window is expanded to 600 msec
            // alleviating the request rate from the server. Similarly the 3rd retry
            // will wait 600 msec grace period before retry and the waiting window
            // is
            // expanded to 1200 msec.
            if (conn != null) {
                try {
                    conn.rollback();
                    LOG.info("putStripe Transaction was rolled back");
                } catch (SQLException excep) {
                    LOG.error(excep);
                }
            }
            waitMS += waitMS;
            if (waitMS > DBUtils.RETRY_MAX_INTERVAL_SEC * 1000) {
                waitMS = DBUtils.RETRY_MAX_INTERVAL_SEC * 1000;
            }
            double waitTime = waitMS + waitMS * rand.nextDouble();
            if (i + 1 == sqlNumRetries) {
                LOG.error("Still got Exception after " + sqlNumRetries + "  retries.", e);
                throw new IOException(e);
            }
            try {
                Thread.sleep((long) waitTime);
            } catch (InterruptedException ie) {
                throw new IOException(ie);
            }
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException sqlExp) {
                LOG.warn("Fail to set AutoCommit to true", sqlExp);
            }
            DBUtils.close(generatedKeys, new PreparedStatement[] { getStripeStatement, insertStripeStatement },
                    conn);
        }
    }
}

From source file:com.softberries.klerk.dao.DocumentItemDao.java

public void create(DocumentItem c, QueryRunner run, Connection conn, ResultSet generatedKeys)
        throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_INSERT_DOCUMENTITEM, Statement.RETURN_GENERATED_KEYS);
    st.setString(1, c.getPriceNetSingle());
    st.setString(2, c.getPriceGrossSingle());
    st.setString(3, c.getPriceTaxSingle());
    st.setString(4, c.getPriceNetAll());
    st.setString(5, c.getPriceGrossAll());
    st.setString(6, c.getPriceTaxAll());
    st.setString(7, c.getTaxValue());/*from ww  w.jav a  2  s  .  c om*/
    st.setString(8, c.getQuantity());
    if (c.getProduct().getId().longValue() == 0 && c.getDocument_id().longValue() == 0) {
        throw new SQLException(
                "For DocumentItem corresponding product and document it belongs to need to be specified");
    }
    if (c.getProduct().getId() != 0) {
        st.setLong(9, c.getProduct().getId());
    } else {
        st.setNull(9, java.sql.Types.NUMERIC);
    }
    if (c.getDocument_id().longValue() != 0) {
        st.setLong(10, c.getDocument_id());
    } else {
        st.setNull(10, java.sql.Types.NUMERIC);
    }
    st.setString(11, c.getProduct().getName());
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_INSERT_DOCUMENTITEM);
    }
    generatedKeys = st.getGeneratedKeys();
    if (generatedKeys.next()) {
        c.setId(generatedKeys.getLong(1));
    } else {
        throw new SQLException("Creating user failed, no generated key obtained.");
    }
}

From source file:org.forgerock.openidm.repo.jdbc.impl.OracleTableHandler.java

@Override
public void create(String fullId, String type, String localId, Map<String, Object> obj, Connection connection)
        throws SQLException, IOException, InternalServerErrorException {
    connection.setAutoCommit(true);/*  w ww.  jav  a 2  s.  c  om*/
    long typeId = getTypeId(type, connection);

    connection.setAutoCommit(false);

    PreparedStatement createStatement = null;
    try {
        // Since ORACLE returns the ROWID instead of an autoincremented column, we have to tell the PreparedStatement to
        // return the value of the "id-column" instead of the rowid. This is done by passing the following array to the PreparedStatement
        String generatedColumns[] = { "id" };
        createStatement = queries.getPreparedStatement(connection, queryMap.get(QueryDefinition.CREATEQUERYSTR),
                generatedColumns);

        logger.debug("Create with fullid {}", fullId);
        String rev = "0";
        obj.put("_id", localId); // Save the id in the object
        obj.put("_rev", rev); // Save the rev in the object, and return the changed rev from the create.
        String objString = mapper.writeValueAsString(obj);

        logger.trace("Populating statement {} with params {}, {}, {}, {}",
                new Object[] { createStatement, typeId, localId, rev, objString });
        createStatement.setLong(1, typeId);
        createStatement.setString(2, localId);
        createStatement.setString(3, rev);
        createStatement.setString(4, objString);
        logger.debug("Executing: {}", createStatement);
        int val = createStatement.executeUpdate();

        ResultSet keys = createStatement.getGeneratedKeys();
        boolean validKeyEntry = keys.next();
        if (!validKeyEntry) {
            throw new InternalServerErrorException(
                    "Object creation for " + fullId + " failed to retrieve an assigned ID from the DB.");
        }

        // Should now contain the value of the autoincremented column
        long dbId = keys.getLong(1);

        logger.debug("Created object for id {} with rev {}", fullId, rev);
        JsonValue jv = new JsonValue(obj);
        writeValueProperties(fullId, dbId, localId, jv, connection);
    } finally {
        CleanupHelper.loggedClose(createStatement);
    }
}

From source file:com.feedzai.commons.sql.abstraction.engine.impl.SqlServerEngine.java

@Override
public synchronized Long persist(String name, EntityEntry entry, boolean useAutoInc)
        throws DatabaseEngineException {
    ResultSet generatedKeys = null;
    MappedEntity me = null;//w  w  w . j a  va 2s  .co  m
    try {
        getConnection();
        me = entities.get(name);

        if (me == null) {
            throw new DatabaseEngineException(String.format("Unknown entity '%s'", name));
        }
        PreparedStatement ps = null;
        if (useAutoInc) {
            ps = entities.get(name).getInsertReturning();
        } else {
            ps = entities.get(name).getInsertWithAutoInc();
        }

        entityToPreparedStatement(me.getEntity(), ps, entry, useAutoInc);

        if (!useAutoInc) {
            // Only SET IDENTITY_INSERT for tables that have an identity column
            if (hasIdentityColumn(me.getEntity())) {
                executeUpdateSilently("SET IDENTITY_INSERT \"" + name + "\" ON");
            }
            ps.execute();
        } else {
            ps.execute();
        }

        long ret = 0;
        if (useAutoInc) {
            generatedKeys = ps.getGeneratedKeys();

            if (generatedKeys.next()) {
                ret = generatedKeys.getLong(1);
            }
        }

        return ret == 0 ? null : ret;
    } catch (Exception ex) {
        throw new DatabaseEngineException("Something went wrong persisting the entity", ex);
    } finally {
        try {
            if (generatedKeys != null) {
                generatedKeys.close();
            }
            if (!useAutoInc) {
                // Only SET IDENTITY_INSERT for tables that have an identity column
                if (me != null && hasIdentityColumn(me.getEntity())) {
                    getConnection().createStatement().execute("SET IDENTITY_INSERT \"" + name + "\" OFF");
                }
            }
        } catch (Exception e) {
            logger.trace("Error closing result set.", e);
        }
    }
}

From source file:org.wso2.carbon.la.database.internal.LADatabaseService.java

@Override
public int createLogGroup(LogGroup logGroup) throws DatabaseHandlerException {
    Connection connection = null;
    PreparedStatement createLogGroupStatement = null;
    int tenantId = logGroup.getTenantId();
    String username = logGroup.getUsername();
    String logGroupName = logGroup.getName();

    if (getLogGroup(logGroup.getName(), tenantId, username) != null) {
        throw new DatabaseHandlerException(String.format("Log Group [name] %s already exists.", logGroupName));
    }//from  w  ww .  j  av a  2s.  c o m
    try {
        connection = dbh.getDataSource().getConnection();
        connection.setAutoCommit(false);
        createLogGroupStatement = connection.prepareStatement(SQLQueries.CREATE_LOG_GROUP,
                Statement.RETURN_GENERATED_KEYS);
        createLogGroupStatement.setString(1, logGroupName);
        createLogGroupStatement.setInt(2, tenantId);
        createLogGroupStatement.setString(3, username);
        int affectedRow = createLogGroupStatement.executeUpdate();
        connection.commit();

        if (affectedRow == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        try (ResultSet generatedKeys = createLogGroupStatement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                if (logger.isDebugEnabled()) {
                    logger.debug("Successfully created log group: " + logGroupName);
                }
                return generatedKeys.getInt(1);
            } else {
                throw new SQLException("Log Group creation failed, no ID obtained.");
            }
        }
    } catch (SQLException e) {
        LADatabaseUtils.rollBack(connection);
        throw new DatabaseHandlerException("Error occurred while inserting details of log group: "
                + logGroupName + " to the database: " + e.getMessage(), e);
    } finally {
        // enable auto commit
        LADatabaseUtils.enableAutoCommit(connection);
        // close the database resources
        LADatabaseUtils.closeDatabaseResources(connection, createLogGroupStatement);
    }
}

From source file:org.wso2.carbon.device.mgt.core.dao.impl.DeviceDAOImpl.java

@Override
public int addDevice(int typeId, Device device, int tenantId) throws DeviceManagementDAOException {
    Connection conn;//from   ww w.  j  a  v a2s  .c  om
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int deviceId = -1;
    try {
        conn = this.getConnection();
        String sql = "INSERT INTO DM_DEVICE(DESCRIPTION, NAME, DEVICE_TYPE_ID, DEVICE_IDENTIFICATION, TENANT_ID, GROUP_ID) "
                + "VALUES (?, ?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, device.getDescription());
        stmt.setString(2, device.getName());
        stmt.setInt(3, typeId);
        stmt.setString(4, device.getDeviceIdentifier());
        stmt.setInt(5, tenantId);
        if (device.getGroupId() == 0) {
            stmt.setNull(6, java.sql.Types.INTEGER);
        } else {
            stmt.setInt(6, device.getGroupId());
        }
        stmt.executeUpdate();

        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            deviceId = rs.getInt(1);
        }
        return deviceId;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException(
                "Error occurred while enrolling device '" + device.getName() + "'", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, rs);
    }
}