Example usage for java.sql Statement RETURN_GENERATED

Introduction

In this page you can find the example usage for java.sql Statement RETURN_GENERATED_KEYS.

Prototype

int RETURN_GENERATED_KEYS

To view the source code for java.sql Statement RETURN_GENERATED_KEYS.

Click Source Link

Document

The constant indicating that generated keys should be made available for retrieval.

Usage

From source file:org.meerkat.services.WebApp.java

/**
 * addEvent/*from   w w  w .jav  a2  s.c o m*/
 * 
 * @param event
 */
public final void addEvent(WebAppEvent ev) {
    if (ev.getCurrentResponse().length() > EmbeddedDB.EVENT_MAX_RESPONSE_LENGTH) {
        // truncate the size of response
        ev.setCurrentResponse(ev.getCurrentResponse().substring(0, EmbeddedDB.EVENT_MAX_RESPONSE_LENGTH));
        log.warn("Response of " + this.getName() + " bigger than " + EmbeddedDB.EVENT_MAX_RESPONSE_LENGTH
                + " chars (truncated!).");
    }

    // Get the event ID
    PreparedStatement pstat;
    ResultSet rs = null;
    int evID = -1;
    try {
        pstat = conn.prepareStatement("SELECT ID FROM MEERKAT.EVENTS_RESPONSE WHERE APPNAME = '"
                + this.getName() + "' AND RESPONSE LIKE ?");
        pstat.setString(1, ev.getCurrentResponse());
        rs = pstat.executeQuery();
        while (rs.next()) {
            evID = rs.getInt(1);
        }
        rs.close();
        pstat.close();
    } catch (SQLException e) {
        log.error("Failed query events response existence from DB ");
        log.error("", e);
    }

    if (evID < 0) { // No equal event exists, so add a new one
        PreparedStatement pstatAddEv;
        String queryInsertNewEv = "INSERT INTO MEERKAT.EVENTS_RESPONSE(APPNAME, RESPONSE) VALUES('"
                + this.getName() + "', ?) ";
        try {
            pstatAddEv = conn.prepareStatement(queryInsertNewEv, Statement.RETURN_GENERATED_KEYS);
            pstatAddEv.setString(1, ev.getCurrentResponse().toString());
            pstatAddEv.execute();

            ResultSet generatedKeys = pstatAddEv.getGeneratedKeys();
            if (generatedKeys.next()) {
                evID = (int) generatedKeys.getLong(1);
            } else {
                log.error("Error inserting event response, no generated key obtained.");
                //throw new SQLException("Error inserting event response, no generated key obtained.");
            }

            pstatAddEv.close();
            conn.commit();

        } catch (SQLException e) {
            log.error("Failed to insert event response into DB for app: " + this.getName() + "! - "
                    + e.getMessage());
            //log.error("INSERT DATA IS:"+ev.getCurrentResponse());
            //EmbeddedDB.logSQLException(e);
        }
    }

    // Add the event referencing the event response id
    PreparedStatement statement;
    String queryInsert = "INSERT INTO MEERKAT.EVENTS(APPNAME, CRITICAL, DATEEV, ONLINE, AVAILABILITY, LOADTIME, LATENCY, HTTPSTATUSCODE, DESCRIPTION, RESPONSE_ID) VALUES(";

    String queryValues = "'" + this.getName() + "', " + ev.isCritical() + ", '" + ev.getDate() + "', '"
            + ev.getStatus() + "', " + Double.valueOf(this.getAvailability()) + ", "
            + Double.valueOf(ev.getPageLoadTime()) + ", ";

    // Handle latency - may be null if host not available)
    if (ev.getLatency() == null) {
        queryValues += null;
    } else {
        queryValues += ev.getLatency();
    }

    queryValues += ", " + Integer.valueOf(ev.getHttpStatusCode()) + ", '" + ev.getDescription() + "', " + evID
            + "";

    try {
        statement = conn.prepareStatement(queryInsert + queryValues + ")");
        //statement.setInt(1, evID);
        statement.execute();
        statement.close();
        conn.commit();
    } catch (SQLException e) {
        log.error("Failed to insert event into DB! - " + e.getMessage());
    }

    this.writeWebAppVisualizationDataFile();
}

From source file:org.restlet.ext.jdbc.JdbcClientHelper.java

/**
 * Helper/* w w  w  .j av a2s. c om*/
 * 
 * @param connection
 * @param returnGeneratedKeys
 * @param sqlRequests
 * @return the result of the last executed SQL request
 */
private JdbcResult handleSqlRequests(Connection connection, boolean returnGeneratedKeys,
        List<String> sqlRequests) {
    JdbcResult result = null;
    try {
        connection.setAutoCommit(true);
        Statement statement = connection.createStatement();
        for (String sqlRequest : sqlRequests) {
            statement.execute(sqlRequest,
                    returnGeneratedKeys ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
            result = new JdbcResult(statement);
        }

        // Commit any changes to the database
        if (!connection.getAutoCommit()) {
            connection.commit();
        }
    } catch (SQLException se) {
        getLogger().log(Level.WARNING, "Error while processing the SQL requests", se);
        try {
            if (!connection.getAutoCommit()) {
                connection.rollback();
            }
        } catch (SQLException se2) {
            getLogger().log(Level.WARNING, "Error while rollbacking the transaction", se);
        }
    }
    return result;

}

From source file:capture.MySQLDatabase.java

public void loadInputUrlFromDatabase() {
    Element element;/*from ww  w  .j av  a  2s  . c o m*/
    Connection con = this.getConnection();
    Statement stmt1, stmt2;
    ResultSet rs;
    String url, url_id, honeypotid = null;
    String operationid = null;
    long count = 0;
    try {
        stmt1 = con.createStatement();
        stmt2 = con.createStatement();
        //get honeypot id
        String serverip = ConfigManager.getInstance().getConfigOption("server-listen-address");
        String serverport = ConfigManager.getInstance().getConfigOption("server-listen-port");
        rs = stmt1.executeQuery("SELECT honeypot_id FROM honeypot WHERE ipaddress=\'" + serverip + "\'");
        if (rs.next()) {
            honeypotid = rs.getString(1);
        } else {
            //insert a new honeypot 
            stmt1.executeUpdate(
                    "INSERT INTO honeypot(ipaddress, port) Values(\'" + serverip + "\', " + serverport + ")",
                    Statement.RETURN_GENERATED_KEYS);
            rs = stmt1.getGeneratedKeys();
            if (rs.next()) {
                honeypotid = rs.getString(1);
            } else {
                System.out.println("System can't find any honeypot ip=" + serverip);
                System.exit(0);
            }
        }

        setSystemStatus(true);
        //add new operation
        stmt1.executeUpdate("INSERT INTO operation(description, honeypot_id) Values (\'"
                + "Load urls from database" + "\', \'" + honeypotid + "\')", Statement.RETURN_GENERATED_KEYS);
        rs = stmt1.getGeneratedKeys();
        if (rs.next()) {
            operationid = rs.getString(1);
            setCurrentOperation(operationid);
        }
        System.out.println("The system is going to inspect urls in the new operation: " + operationid);

        //update visit start time for operation
        SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss.S");
        String date = sf.format(new Date());
        stmt1.executeUpdate("UPDATE operation SET visitstarttime=\'" + date + "\' " + "WHERE operation_id="
                + operationid + " AND visitstarttime IS NULL");

        //load urls from url table
        System.out.println("Loading urls from database....");
        rs = stmt1.executeQuery("SELECT url_id, url FROM url");
        while (rs.next()) {
            url_id = rs.getString(1);
            url = rs.getString(2);
            stmt2.executeUpdate("INSERT INTO url_operation(url_id, operation_id) Values (" + url_id + ", "
                    + operationid + ")");

            element = new Element();
            element.name = "url";
            element.attributes.put("add", "");
            element.attributes.put("id", url_id);
            element.attributes.put("url", url);
            EventsController.getInstance().notifyEventObservers(element);
            count++;
        }
        con.close();
        System.out.println("******** LOADING URL FROM DATABASE: " + count + " urls have been loaded! ********");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.noelios.restlet.ext.jdbc.JdbcClientHelper.java

/**
 * Helper//from  w w  w.j  ava 2 s .  c  o  m
 * 
 * @param connection
 * @param returnGeneratedKeys
 * @param sqlRequests
 * @return the result of the last executed SQL request
 */
private JdbcResult handleSqlRequests(Connection connection, boolean returnGeneratedKeys,
        List<String> sqlRequests) {
    JdbcResult result = null;
    try {
        connection.setAutoCommit(true);
        final Statement statement = connection.createStatement();
        for (final String sqlRequest : sqlRequests) {
            statement.execute(sqlRequest,
                    returnGeneratedKeys ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
            result = new JdbcResult(statement);
        }

        // Commit any changes to the database
        if (!connection.getAutoCommit()) {
            connection.commit();
        }
    } catch (SQLException se) {
        getLogger().log(Level.WARNING, "Error while processing the SQL requests", se);
        try {
            if (!connection.getAutoCommit()) {
                connection.rollback();
            }
        } catch (SQLException se2) {
            getLogger().log(Level.WARNING, "Error while rollbacking the transaction", se);
        }
    }
    return result;

}

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

@Override
protected MappedEntity createPreparedStatementForInserts(final DbEntity entity) throws DatabaseEngineException {

    List<String> insertInto = new ArrayList<String>();
    insertInto.add("INSERT INTO");
    insertInto.add(quotize(entity.getName()));
    List<String> insertIntoWithAutoInc = new ArrayList<String>();
    insertIntoWithAutoInc.add("INSERT INTO");
    insertIntoWithAutoInc.add(quotize(entity.getName()));
    List<String> columns = new ArrayList<String>();
    List<String> values = new ArrayList<String>();
    List<String> columnsWithAutoInc = new ArrayList<String>();
    List<String> valuesWithAutoInc = new ArrayList<String>();
    for (DbColumn column : entity.getColumns()) {
        columnsWithAutoInc.add(quotize(column.getName()));
        valuesWithAutoInc.add("?");
        if (!column.isAutoInc()) {
            columns.add(quotize(column.getName()));
            values.add("?");
        }//from  w w w.  j  a  va 2  s  .  com
    }

    insertInto.add("(" + join(columns, ", ") + ")");
    insertInto.add("VALUES (" + join(values, ", ") + ")");

    insertIntoWithAutoInc.add("(" + join(columnsWithAutoInc, ", ") + ")");
    insertIntoWithAutoInc.add("VALUES (" + join(valuesWithAutoInc, ", ") + ")");

    final String statement = join(insertInto, " ");
    final String statementWithAutoInt = join(insertIntoWithAutoInc, " ");

    logger.trace(statement);

    PreparedStatement ps, psReturn, psWithAutoInc;
    try {
        ps = conn.prepareStatement(statement);
        psReturn = conn.prepareStatement(statement, Statement.RETURN_GENERATED_KEYS);
        psWithAutoInc = conn.prepareStatement(statementWithAutoInt);

        return new MappedEntity().setInsert(ps).setInsertReturning(psReturn)
                .setInsertWithAutoInc(psWithAutoInc);
    } catch (SQLException ex) {
        throw new DatabaseEngineException("Something went wrong handling statement", ex);
    }
}

From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java

/**
 * Method for inserting deployment record.
 *
 * @param dbConnection database connection
 * @param deployment   deployment object
 * @param tenantId     id of tenant//from   ww w  . j  a v  a2s  .  c o m
 * @return deployment ID
 * @throws AppCloudException
 */
private int addDeployment(Connection dbConnection, Deployment deployment, int tenantId)
        throws AppCloudException {

    PreparedStatement preparedStatement = null;
    int deploymentId = -1;
    ResultSet resultSet = null;

    try {

        preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.ADD_DEPLOYMENT,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, deployment.getDeploymentName());
        preparedStatement.setInt(2, deployment.getReplicas());
        preparedStatement.setInt(3, tenantId);
        preparedStatement.execute();

        resultSet = preparedStatement.getGeneratedKeys();

        if (resultSet.next()) {
            deploymentId = resultSet.getInt(1);
        }

        for (Container container : deployment.getContainers()) {
            addContainer(dbConnection, container, deploymentId, tenantId);
        }

    } catch (SQLException e) {
        String msg = "Error while inserting deployment record in tenant : " + tenantId;
        throw new AppCloudException(msg, e);
    } finally {
        DBUtil.closeResultSet(resultSet);
        DBUtil.closePreparedStatement(preparedStatement);
    }
    if (deploymentId == -1) {
        throw new AppCloudException("Failed to insert deployment record in tenant : " + tenantId);
    }
    return deploymentId;
}

From source file:opengovcrawler.DB.java

/**
 * Insert articles into DB//from  w ww. j a  va 2 s  .  c o m
 *
 * @param a - The article to be stored
 * @param consID - The consultation's id the the article refers to
 * @return - Returns the article id
 * @throws java.sql.SQLException
 */
public static int InsertArticles(Article a, int consID) throws SQLException {
    // First check if article is already into db. (Consultation might be green and we crawl for new comments,
    // so articles might already exist in the db)
    String selectArticleSql = "SELECT id FROM articles WHERE consultation_id = ? AND title = ?";
    PreparedStatement preparedStatement = connection.prepareStatement(selectArticleSql);
    preparedStatement.setInt(1, consID);
    preparedStatement.setString(2, a.title);
    ResultSet result = preparedStatement.executeQuery();
    int articleID = -1;
    if (result.next()) {
        articleID = result.getInt(1);
    } else {
        String insertArticleSql = "INSERT INTO articles (consultation_id, title, body, art_order, comment_num) VALUES (?,?,?,?,?)";
        PreparedStatement prepInsertStatement = connection.prepareStatement(insertArticleSql,
                Statement.RETURN_GENERATED_KEYS);
        prepInsertStatement.setInt(1, consID);
        prepInsertStatement.setString(2, a.title);
        prepInsertStatement.setString(3, a.content);
        prepInsertStatement.setInt(4, a.order);
        prepInsertStatement.setInt(5, a.numOfComments);
        prepInsertStatement.executeUpdate();
        ResultSet rsq = prepInsertStatement.getGeneratedKeys();
        if (rsq.next()) {
            articleID = rsq.getInt(1);
        }
        prepInsertStatement.close();
    }
    preparedStatement.close();
    return articleID;
}

From source file:com.buckwa.dao.impl.excise4.Form23DaoImpl.java

@Override
public void update(final Form23 form23) {
    logger.info("update");
    String user = "";
    try {/*from  ww  w. j  a v  a  2 s  .co  m*/
        user = BuckWaUtils.getUserNameFromContext();
    } catch (BuckWaException e) {
        e.printStackTrace();
    }
    final String userName = user;
    final Timestamp currentDate = new Timestamp(System.currentTimeMillis());
    if (BeanUtils.isEmpty(form23.getCodeNo())) {
        form23.setCodeNo("" + System.currentTimeMillis());
    }

    String sqlform23 = "UPDATE `form23` SET `factory_id`=?,`update_date`=?,`update_by`=? "
            + " ,`totalScrap`=?,`part4flag`=?,`part4fullName`=?,`part4Date`=? ,"
            + " `part5flag`=?,`part5licenseNo`=?,`part5licenseDate`=?,`part5billingNo`=?,`part5billingDate`=?,`part5amount`=?,`part5Date`=?,"
            + " `part6flag`=?,`part6Date`=?,`step`=?,`codeNo`=? " + " WHERE `form23_id`=?";
    logger.info("update: " + sqlform23);
    this.jdbcTemplate.update(sqlform23,
            new Object[] { form23.getFactory().getFactoryId(), currentDate, userName, form23.getTotalScrap(),
                    form23.getPart4flag(), form23.getPart4fullName(), currentDate, form23.getPart5flag(),
                    form23.getPart5licenseNo(), getDateFormString(form23.getPart5licenseDate()),
                    form23.getPart5billingNo(), getDateFormString(form23.getPart5billingDate()),
                    form23.getPart5amount(), currentDate, form23.getPart6flag(), currentDate, form23.getStep(),
                    form23.getCodeNo(), form23.getForm23Id() });

    //ID PRODUCT

    String productSql = "UPDATE `form23_product` SET `seq`=?,`productName`=?,`size`=?,`bandColor`=?,`backgroudColor`=?,`licenseNo`=?"
            + ",`grossnumber200`=?,`grossnumber400`=?,`corkScrap`=?,`totalScrap`=?,`update_date`=?,`update_by`=? "
            + "WHERE `product_id`=?";

    final StringBuilder psql = new StringBuilder();
    psql.append(
            "INSERT INTO `form23_product`(`form23_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ")
            .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)");

    List<Product> products = form23.getProductList();
    if (products != null) {
        for (final Product p : products) {
            if (BeanUtils.isEmpty(p.getProcuctId())) {
                logger.info("create");
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection)
                            throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(psql.toString(),
                                Statement.RETURN_GENERATED_KEYS);
                        long returnidform23 = form23.getForm23Id();
                        ps.setLong(1, returnidform23);
                        ps.setString(2, p.getSeq());
                        ps.setString(3, p.getProductName());
                        ps.setString(4, p.getSize());
                        ps.setString(5, p.getBandColor());
                        ps.setString(6, p.getBackgroudColor());
                        ps.setString(7, p.getLicenseNo());
                        ps.setBigDecimal(8, p.getGrossnumber200());
                        ps.setBigDecimal(9, p.getGrossnumber400());
                        ps.setBigDecimal(10, p.getCorkScrap());
                        ps.setBigDecimal(11, p.getTotalScrap());

                        ps.setTimestamp(12, currentDate);
                        ps.setString(13, userName);
                        ps.setTimestamp(14, currentDate);
                        ps.setString(15, userName);
                        return ps;
                    }

                }, keyHolder);

                long returnidproduct = keyHolder.getKey().longValue();
                p.setProcuctId(returnidproduct);
                logger.info("returnidproduct : " + returnidproduct);
            } else {
                logger.info("update");
                this.jdbcTemplate.update(productSql,
                        new Object[] { p.getSeq(), p.getProductName(), p.getSize(), p.getBandColor(),
                                p.getBackgroudColor(), p.getLicenseNo(), p.getGrossnumber200(),
                                p.getGrossnumber400(), p.getCorkScrap(), p.getTotalScrap(), currentDate,
                                userName, p.getProcuctId() });
            }
        }
    }

}

From source file:code.Servlet.java

private void register(BufferedReader reader, HttpServletResponse response, Statement stmt, PrintWriter out)
        throws IOException, SQLException {
    ResultSet rs;/* w w  w .  j  a va  2s. com*/
    System.out.println("register");

    String line = null;
    String email = null;
    String password = null;
    String message = null;

    String sql;
    line = reader.readLine();
    email = line.substring(10, line.length() - 2);
    line = reader.readLine();
    password = line.substring(13, line.length() - 2);
    line = reader.readLine();
    String name = line.substring(9, line.length() - 2);
    line = reader.readLine();
    String surname = line.substring(12, line.length() - 2);
    sql = "INSERT INTO `mydb_treasure`.`User`(`name`, `surname`, `points`,  `email`, `password`) VALUES ('"
            + name + "', '" + surname + "', '" + "0" + "', '" + email + "', '" + password + "')";
    stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet newIdUser = stmt.getGeneratedKeys();
    if (newIdUser.next()) {
        idUser = newIdUser.getString(1);
        System.out.println(idUser);

    } else {
        ; // exception!!! ***
    }
    message = "ok register";
    response.setContentType("text/plain");
    response.setContentLength(message.length());
    PrintWriter reply = response.getWriter();
    reply.println(message);
    out.close(); //non so se serve out
    out.flush();

}

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

@Override
public ComposedFeed addComposedFeed(String name, String metadata, boolean storage, boolean readable,
        boolean writable, List<String> keywords, List<FieldDescription> fields) {
    ComposedFeed composedFeed = null;/*from   ww w.  j a  va  2 s .c  o  m*/
    if (fields == null || fields.size() == 0) {
        Log.e(TAG, "One cannot create a composed feed with no fields");
        return null;
    }
    try {
        checkOpenness();
        connection.setAutoCommit(false);
        //First things first, insert the feed's values to the feed table
        String sqlFeedInsert = "INSERT INTO " + IotHubDataHandler.TABLE_FEED + "("
                + IotHubDataHandler.KEY_FEED_NAME + "," + IotHubDataHandler.KEY_FEED_METADATA + ","
                + IotHubDataHandler.KEY_FEED_TYPE + "," + IotHubDataHandler.KEY_FEED_STORAGE + ","
                + IotHubDataHandler.KEY_FEED_READABLE + "," + IotHubDataHandler.KEY_FEED_WRITABLE
                + ") VALUES (?,?,?,?,?,?)";
        PreparedStatement psFeedInsert = connection.prepareStatement(sqlFeedInsert,
                Statement.RETURN_GENERATED_KEYS);
        psFeedInsert.setString(1, name);
        psFeedInsert.setString(2, metadata);
        psFeedInsert.setString(3, IotHubDataHandler.COMPOSED_FEED);
        psFeedInsert.setInt(4, storage ? 1 : 0);
        psFeedInsert.setInt(5, readable ? 1 : 0);
        psFeedInsert.setInt(6, writable ? 1 : 0);
        psFeedInsert.executeUpdate();
        ResultSet genKeysFeed = psFeedInsert.getGeneratedKeys();
        if (genKeysFeed.next()) {
            long insertIdFeed = genKeysFeed.getLong(1);
            //Now we add the keywords
            addFeedKeywords(insertIdFeed, keywords);
            //Now we add the fields
            addFeedFields(insertIdFeed, fields);
            //At point we should have everything set so it is time to retrieve the composed feed from the database
            //Log.d(TAG, "Now i will try to collect the composed feed that was just added to the db");
            composedFeed = getComposedFeed(insertIdFeed);
            if (composedFeed == null) {
                Log.e(TAG, "The feed should not be null");
            }
            //Now I want to make some checks
            if (!compareComposeFeeds(composedFeed, name, metadata, storage, readable, writable, keywords,
                    fields)) {
                Log.e(TAG, "Retrieving feed " + name + " did not work");
                composedFeed = null;
            }
        } else {
            Log.e(TAG, "The insert of feed " + name + " did not work");
        }
        genKeysFeed.close();
        psFeedInsert.close();
    } catch (SQLException | IotHubDatabaseException e) {
        e.printStackTrace();
        composedFeed = null;
    }
    try {
        if (composedFeed == null) {
            connection.rollback();
        }
        connection.commit();
        connection.setAutoCommit(true);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return composedFeed;
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage