Example usage for java.sql PreparedStatement execute

List of usage examples for java.sql PreparedStatement execute

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

Introduction

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

Prototype

boolean execute() throws SQLException;

Source Link

Document

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

Usage

From source file:com.keybox.common.db.DBInitServlet.java

/**
 * task init method that created DB and generated public/private keys
 *
 * @param config task config/*  w w  w .  ja v  a  2  s  .  c om*/
 * @throws ServletException
 */
public void init(ServletConfig config) throws ServletException {

    super.init(config);

    Connection connection = null;
    Statement statement = null;
    //check if reset ssh application key is set
    boolean resetSSHKey = "true".equals(AppConfig.getProperty("resetApplicationSSHKey"));

    //if DB password is empty generate a random
    if (StringUtils.isEmpty(AppConfig.getProperty("dbPassword"))) {
        String dbPassword = null;
        String dbPasswordConfirm = null;
        //prompt for password and confirmation
        while (dbPassword == null || !dbPassword.equals(dbPasswordConfirm)) {
            dbPassword = new String(System.console().readPassword("Please enter database password: "));
            dbPasswordConfirm = new String(System.console().readPassword("Please confirm database password: "));
            if (!dbPassword.equals(dbPasswordConfirm)) {
                System.out.println("Passwords do not match");
            }
        }
        //set password
        if (StringUtils.isNotEmpty(dbPassword)) {
            AppConfig.encryptProperty("dbPassword", dbPassword);
            //if password not set generate a random
        } else {
            System.out.println("Generating random database password");
            AppConfig.encryptProperty("dbPassword", RandomStringUtils.randomAscii(32));
        }
        //else encrypt password if plain-text
    } else if (!AppConfig.isPropertyEncrypted("dbPassword")) {
        AppConfig.encryptProperty("dbPassword", AppConfig.getProperty("dbPassword"));
    }

    try {
        connection = DBUtils.getConn();
        statement = connection.createStatement();

        ResultSet rs = statement.executeQuery(
                "select * from information_schema.tables where upper(table_name) = 'USERS' and table_schema='PUBLIC'");
        if (!rs.next()) {
            resetSSHKey = true;

            //create DB objects
            statement.executeUpdate(
                    "create table if not exists users (id INTEGER PRIMARY KEY AUTO_INCREMENT, first_nm varchar, last_nm varchar, email varchar, username varchar not null, password varchar, auth_token varchar, enabled boolean not null default true, auth_type varchar not null default '"
                            + Auth.AUTH_BASIC + "', user_type varchar not null default '" + Auth.ADMINISTRATOR
                            + "', salt varchar, otp_secret varchar)");
            statement.executeUpdate(
                    "create table if not exists user_theme (user_id INTEGER PRIMARY KEY, bg varchar(7), fg varchar(7), d1 varchar(7), d2 varchar(7), d3 varchar(7), d4 varchar(7), d5 varchar(7), d6 varchar(7), d7 varchar(7), d8 varchar(7), b1 varchar(7), b2 varchar(7), b3 varchar(7), b4 varchar(7), b5 varchar(7), b6 varchar(7), b7 varchar(7), b8 varchar(7), foreign key (user_id) references users(id) on delete cascade) ");
            statement.executeUpdate(
                    "create table if not exists system (id INTEGER PRIMARY KEY AUTO_INCREMENT, display_nm varchar not null, user varchar not null, host varchar not null, port INTEGER not null, authorized_keys varchar not null, status_cd varchar not null default 'INITIAL')");
            statement.executeUpdate(
                    "create table if not exists profiles (id INTEGER PRIMARY KEY AUTO_INCREMENT, nm varchar not null, desc varchar not null)");
            statement.executeUpdate(
                    "create table if not exists system_map (profile_id INTEGER, system_id INTEGER, foreign key (profile_id) references profiles(id) on delete cascade , foreign key (system_id) references system(id) on delete cascade, primary key (profile_id, system_id))");
            statement.executeUpdate(
                    "create table if not exists user_map (user_id INTEGER, profile_id INTEGER, foreign key (user_id) references users(id) on delete cascade, foreign key (profile_id) references profiles(id) on delete cascade, primary key (user_id, profile_id))");
            statement.executeUpdate(
                    "create table if not exists application_key (id INTEGER PRIMARY KEY AUTO_INCREMENT, public_key varchar not null, private_key varchar not null, passphrase varchar)");

            statement.executeUpdate(
                    "create table if not exists status (id INTEGER, user_id INTEGER, status_cd varchar not null default 'INITIAL', foreign key (id) references system(id) on delete cascade, foreign key (user_id) references users(id) on delete cascade, primary key(id, user_id))");
            statement.executeUpdate(
                    "create table if not exists scripts (id INTEGER PRIMARY KEY AUTO_INCREMENT, user_id INTEGER, display_nm varchar not null, script varchar not null, foreign key (user_id) references users(id) on delete cascade)");

            statement.executeUpdate(
                    "create table if not exists public_keys (id INTEGER PRIMARY KEY AUTO_INCREMENT, key_nm varchar not null, type varchar, fingerprint varchar, public_key varchar, enabled boolean not null default true, create_dt timestamp not null default CURRENT_TIMESTAMP(),  user_id INTEGER, profile_id INTEGER, foreign key (profile_id) references profiles(id) on delete cascade, foreign key (user_id) references users(id) on delete cascade)");

            statement.executeUpdate(
                    "create table if not exists session_log (id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INTEGER, session_tm timestamp default CURRENT_TIMESTAMP, foreign key (user_id) references users(id) on delete cascade )");
            statement.executeUpdate(
                    "create table if not exists terminal_log (session_id BIGINT, instance_id INTEGER, system_id INTEGER, output varchar not null, log_tm timestamp default CURRENT_TIMESTAMP, foreign key (session_id) references session_log(id) on delete cascade, foreign key (system_id) references system(id) on delete cascade)");

            //insert default admin user
            String salt = EncryptionUtil.generateSalt();
            PreparedStatement pStmt = connection.prepareStatement(
                    "insert into users (username, password, user_type, salt) values(?,?,?,?)");
            pStmt.setString(1, "admin");
            pStmt.setString(2, EncryptionUtil.hash("changeme" + salt));
            pStmt.setString(3, Auth.MANAGER);
            pStmt.setString(4, salt);
            pStmt.execute();
            DBUtils.closeStmt(pStmt);

        }
        DBUtils.closeRs(rs);

        //if reset ssh application key then generate new key
        if (resetSSHKey) {

            //delete old key entry
            PreparedStatement pStmt = connection.prepareStatement("delete from application_key");
            pStmt.execute();
            DBUtils.closeStmt(pStmt);

            //generate new key and insert passphrase
            System.out.println("Setting KeyBox SSH public/private key pair");

            //generate application pub/pvt key and get values
            String passphrase = SSHUtil.keyGen();
            String publicKey = SSHUtil.getPublicKey();
            String privateKey = SSHUtil.getPrivateKey();

            //insert new keys
            pStmt = connection.prepareStatement(
                    "insert into application_key (public_key, private_key, passphrase) values(?,?,?)");
            pStmt.setString(1, publicKey);
            pStmt.setString(2, EncryptionUtil.encrypt(privateKey));
            pStmt.setString(3, EncryptionUtil.encrypt(passphrase));
            pStmt.execute();
            DBUtils.closeStmt(pStmt);

            System.out.println("KeyBox Generated Global Public Key:");
            System.out.println(publicKey);

            //set config to default
            AppConfig.updateProperty("publicKey", "");
            AppConfig.updateProperty("privateKey", "");
            AppConfig.updateProperty("defaultSSHPassphrase", "${randomPassphrase}");

            //set to false
            AppConfig.updateProperty("resetApplicationSSHKey", "false");

        }

        //delete ssh keys
        SSHUtil.deletePvtGenSSHKey();

    } catch (Exception ex) {
        log.error(ex.toString(), ex);
    } finally {
        DBUtils.closeStmt(statement);
        DBUtils.closeConn(connection);
    }

    RefreshAuthKeyUtil.startRefreshAllSystemsTimerTask();
}

From source file:lineage2.loginserver.accounts.Account.java

/**
 * Method save.//w  w  w. ja  va  2 s  .c  om
 */
public void save() {
    Connection con = null;
    PreparedStatement statement = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection();
        statement = con.prepareStatement("INSERT INTO accounts (login, password) VALUES(?,?)");
        statement.setString(1, getLogin());
        statement.setString(2, getPasswordHash());
        statement.execute();
    } catch (Exception e) {
        _log.error("", e);
    } finally {
        DbUtils.closeQuietly(con, statement);
    }
}

From source file:com.l2jfree.gameserver.model.entity.Auction.java

/** Remove auctions */
public void deleteAuctionFromDB() {
    AuctionManager.getInstance().getAuctions().remove(this);
    Connection con = null;/* ww  w .j a v  a  2 s.  com*/
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement;
        statement = con.prepareStatement("DELETE FROM auction WHERE itemId=?");
        statement.setInt(1, _itemId);
        statement.execute();
        statement.close();
    } catch (Exception e) {
        _log.fatal("Exception: Auction.deleteFromDB(): " + e.getMessage(), e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.l2jfree.gameserver.handler.admincommands.AdminTeleport.java

private void changeCharacterPosition(L2Player activeChar, String name) {
    Connection con = null;/*from   w  w  w  .j  a  v a 2s  . c  o  m*/
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement("UPDATE characters SET x=? WHERE char_name=?");
        statement.setInt(1, activeChar.getX());
        statement.setString(2, name);
        statement.execute();
        statement = con.prepareStatement("UPDATE characters SET y=? WHERE char_name=?");
        statement.setInt(1, activeChar.getY());
        statement.setString(2, name);
        statement.execute();
        statement = con.prepareStatement("UPDATE characters SET z=? WHERE char_name=?");
        statement.setInt(1, activeChar.getZ());
        statement.setString(2, name);
        statement.execute();
        int count = statement.getUpdateCount();
        statement.close();
        if (count == 0)
            activeChar.sendMessage("Character not found or position unaltered.");
        else {
            activeChar.sendMessage("Character's position is now set to (" + activeChar.getX() + ","
                    + activeChar.getY() + "," + activeChar.getZ() + ")");
        }
    } catch (SQLException se) {
        activeChar.sendMessage("SQLException while changing offline character's position");
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:dk.netarkivet.harvester.datamodel.DerbySpecifics.java

/** Get a temporary table for short-time use.  The table should be
 * disposed of with dropTemporaryTable.  The table has two columns
 * domain_name varchar(Constants.MAX_NAME_SIZE)
 + config_name varchar(Constants.MAX_NAME_SIZE)
 * All rows in the table must be deleted at commit or rollback.
 *
 * @param c The DB connection to use./*w ww  . j ava  2  s  . co  m*/
 * @throws SQLException if there is a problem getting the table.
 * @return The name of the created table
 */
public String getJobConfigsTmpTable(Connection c) throws SQLException {
    ArgumentNotValid.checkNotNull(c, "Connection c");
    PreparedStatement s = c.prepareStatement("DECLARE GLOBAL TEMPORARY TABLE " + "jobconfignames "
            + "( domain_name varchar(" + Constants.MAX_NAME_SIZE + "), " + " config_name varchar("
            + Constants.MAX_NAME_SIZE + ") )" + " ON COMMIT DELETE ROWS NOT LOGGED ON ROLLBACK DELETE ROWS");
    s.execute();
    s.close();
    return "session.jobconfignames";
}

From source file:fr.gael.dhus.database.liquibase.CopyProductImagesBlobToFile.java

/**
 * This method executes://from w ww .j av  a2  s . c om
 *  - extraction of quicklooks and thumbnails from the database to files,
 *  - references these files into the data base.
 *  
 *  remove/update processes are let to liquibase scripts.
 */
@Override
public void execute(Database database) throws CustomChangeException {
    PreparedStatement products = null;
    ResultSet products_res = null;
    JdbcConnection db_connection = (JdbcConnection) database.getConnection();
    try {
        products = db_connection.prepareStatement("SELECT PRODUCT.ID ID,"
                + "       PRODUCT.DOWNLOAD_PATH DWN_PATH, " + "       PRODUCT.PATH PRODUCT_PATH,"
                + "       IMAGE.QUICKLOOK QUICKLOOK," + "       IMAGE.THUMBNAIL THUMBNAIL "
                + "FROM PRODUCTS PRODUCT, PRODUCT_IMAGES IMAGE " + "WHERE PRODUCT.IMAGES_ID=IMAGE.ID");
        products_res = products.executeQuery();
        while (products_res.next()) {
            Blob ql = (Blob) products_res.getObject("QUICKLOOK");
            Blob th = (Blob) products_res.getObject("THUMBNAIL");
            long id = products_res.getLong("ID");
            String download_path = products_res.getString("DWN_PATH");
            String product_path = products_res.getString("PRODUCT_PATH");

            if (download_path == null) {
                logger.error("No download path for product '" + product_path + "': product images not managed");
                continue;
            }

            // copy blobs into files and update products table
            if (ql != null) {
                // Copy file
                String ql_path = download_path.replaceAll("(?i)(.*).zip", "$1-ql.gif");
                blobToFile(ql, ql_path);

                // Update products table
                PreparedStatement product_flags_stmt = null;
                // Add related flags
                try {
                    product_flags_stmt = db_connection
                            .prepareStatement("UPDATE PRODUCTS SET QUICKLOOK_PATH=? WHERE ID=?");
                    product_flags_stmt.setString(1, ql_path);
                    product_flags_stmt.setLong(2, id);
                    product_flags_stmt.execute();
                } finally {
                    if (product_flags_stmt != null)
                        try {
                            product_flags_stmt.close();
                        } catch (Exception e) {
                            logger.warn("Cannot close Statement !");
                        }
                }
            }

            if (th != null) {
                String th_path = download_path.replaceAll("(?i)(.*).zip", "$1-th.gif");
                blobToFile(th, th_path);
                // Update products table
                PreparedStatement product_flags_stmt = null;
                // Add related flags
                try {
                    product_flags_stmt = db_connection
                            .prepareStatement("UPDATE PRODUCTS SET THUMBNAIL_PATH=? WHERE ID=?");
                    product_flags_stmt.setString(1, th_path);
                    product_flags_stmt.setLong(2, id);
                    product_flags_stmt.execute();
                } finally {
                    if (product_flags_stmt != null)
                        try {
                            product_flags_stmt.close();
                        } catch (Exception e) {
                            logger.warn("Cannot close Statement !");
                        }
                }
            }
        }
        // RUN CHECKPOINT TO clean lob data
        PreparedStatement product_flags_stmt = null;
        try {
            product_flags_stmt = db_connection.prepareStatement("CHECKPOINT DEFRAG");
            product_flags_stmt.execute();
        } catch (Exception e) {
            logger.error("Cannot perform database checkpoint defrag command", e);
        } finally {
            if (product_flags_stmt != null)
                try {
                    product_flags_stmt.close();
                } catch (Exception e) {
                    logger.warn("Cannot close Statement !", e);
                }
        }
    } catch (Exception e) {
        throw new CustomChangeException("Cannot move Blobs from product", e);
    } finally {
        if (products_res != null) {
            try {
                products_res.close();
            } catch (Exception e) {
                logger.warn("Cannot close ResultSet !");
            }
        }
        if (products != null) {
            try {
                products.close();
            } catch (Exception e) {
                logger.warn("Cannot close Statement !");
            }
        }
        //if (db_connection!=null) try { db_connection.close (); }
        // catch (Exception e) {}
    }
}

From source file:com.l2jfree.gameserver.model.entity.Couple.java

public Couple(L2Player player1, L2Player player2) {
    int _tempPlayer1Id = player1.getObjectId();
    int _tempPlayer2Id = player2.getObjectId();

    _player1Id = _tempPlayer1Id;/*  ww w. j  ava  2 s . c o m*/
    _player2Id = _tempPlayer2Id;

    _affiancedDate = System.currentTimeMillis();
    _weddingDate = System.currentTimeMillis();

    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement;
        _id = IdFactory.getInstance().getNextId();
        statement = con.prepareStatement(
                "INSERT INTO couples (id, player1Id, player2Id, maried, affiancedDate, weddingDate) VALUES (?, ?, ?, ?, ?, ?)");
        statement.setInt(1, _id);
        statement.setInt(2, _player1Id);
        statement.setInt(3, _player2Id);
        statement.setBoolean(4, false);
        statement.setLong(5, _affiancedDate);
        statement.setLong(6, _weddingDate);
        statement.execute();
        statement.close();
    } catch (Exception e) {
        _log.error("", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.concursive.connect.web.modules.common.social.rating.dao.Rating.java

/**
 * Deletes just a specific user's rating, and updates the parent table with a proper calculation
 *
 * @param db//from  www  . ja  v  a2 s  .  co  m
 * @param userId
 * @param objectId
 * @param table
 * @param uniqueField
 * @throws SQLException
 */
public static synchronized void delete(Connection db, int userId, int objectId, String table,
        String uniqueField) throws SQLException {
    boolean commit = false;
    try {
        commit = db.getAutoCommit();
        if (commit) {
            db.setAutoCommit(false);
        }

        // Get the project's rating
        int ratingCount = queryObjectRatingCount(db, objectId, table, uniqueField);

        // Get the user's rating
        int thisRating = queryUserRating(db, userId, objectId, table, uniqueField);

        // Delete the user's rating
        PreparedStatement pst = db.prepareStatement(
                "DELETE FROM " + table + "_rating " + "WHERE " + uniqueField + " = ? " + "AND enteredby = ? ");
        pst.setInt(1, objectId);
        pst.setInt(2, userId);
        int deleteCount = pst.executeUpdate();
        pst.close();

        if (deleteCount > 0 && thisRating != INAPPROPRIATE_COMMENT) {
            // Update the parent table's rating information
            // NOTE: make sure not to divide by 0
            pst = db.prepareStatement("UPDATE " + table + " "
                    + "SET rating_count = rating_count - ?, rating_value = rating_value - ?, "
                    + (ratingCount == 0 ? "rating_avg = 0 "
                            : "rating_avg = ((rating_value - ?) / (rating_count - ?)) ")
                    + "WHERE " + uniqueField + " = ? ");
            int i = 0;
            pst.setInt(++i, 1);
            pst.setInt(++i, thisRating);
            if (ratingCount > 1) {
                pst.setInt(++i, thisRating);
                pst.setInt(++i, 1);
            }
            pst.execute();
            pst.close();
        }
    } catch (Exception e) {
        if (commit) {
            db.rollback();
        }
        throw new SQLException(e.getMessage());
    } finally {
        if (commit) {
            db.setAutoCommit(true);
        }
    }
}

From source file:com.l2jfree.gameserver.model.entity.Auction.java

/** Remove bids */
private void removeBids() {
    Connection con = null;//w  w  w .j a v  a  2  s.c o  m
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement;

        statement = con.prepareStatement("DELETE FROM auction_bid WHERE auctionId=?");
        statement.setInt(1, getId());
        statement.execute();

        statement.close();
    } catch (Exception e) {
        _log.fatal("Exception: Auction.deleteFromDB(): " + e.getMessage(), e);
    } finally {
        L2DatabaseFactory.close(con);
    }

    for (Bidder b : _bidders.values()) {
        if (ClanTable.getInstance().getClanByName(b.getClanName()).getHasHideout() == 0)
            returnItem(b.getClanName(), PlayerInventory.ADENA_ID, b.getBid(), true); // 10 % tax
        else {
            L2Player bidder = L2World.getInstance().getPlayer(b.getName());
            if (bidder != null)
                bidder.sendMessage("Congratulations! You have won a ClanHall!");
        }
        ClanTable.getInstance().getClanByName(b.getClanName()).setAuctionBiddedAt(0, true);
    }
    _bidders.clear();
}

From source file:net.mindengine.oculus.frontend.service.issue.JdbcIssueDAO.java

@Override
public void linkTestsToIssue(IssueCollation issueCollation) throws Exception {
    /*//from   ww  w .  ja  v a 2 s  .c  om
     * Inserting into issue_collations table first because then we will use
     * its generated index
     */
    PreparedStatement ps = getConnection()
            .prepareStatement("insert into issue_collations (issue_id, reason_pattern) values (?,?)");
    ps.setLong(1, issueCollation.getIssueId());
    ps.setString(2, issueCollation.getReasonPattern());

    logger.info(ps);

    ps.execute();
    ResultSet rs = ps.getGeneratedKeys();
    if (rs.next()) {
        issueCollation.setId(rs.getLong(1));
    } else
        throw new Exception("An error appeared while linking tests to issue");

    /*
     * Inserting all tests into issue_collation_tests table
     */
    for (IssueCollationTest test : issueCollation.getTests()) {
        update("insert into issue_collation_tests (issue_collation_id, test_id, test_name) values ("
                + issueCollation.getId() + "," + test.getTestId() + ", :testName)", "testName",
                test.getTestName());
    }

    update("update issues set dependent_tests = dependent_tests + :size where id = :id", "size",
            issueCollation.getTests().size(), "id", issueCollation.getIssueId());

    /*
     * Inserting all conditions into issue_collation_conditions table
     */
    for (IssueCollationCondition condition : issueCollation.getConditions()) {
        update("insert into issue_collation_conditions (issue_collation_id, trm_property_id, value) values (:issueCollationId, :trmPropertyId, :value)",
                "issueCollationId", issueCollation.getId(), "trmPropertyId", condition.getTrmPropertyId(),
                "value", condition.getValue());

    }
}