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.geodetix.geo.dao.PostGisGeometryDAOImpl.java

/**
 * PostGIS implementation of the /* ww  w .j a v  a  2  s .co m*/
 * {@link com.geodetix.geo.interfaces.GeometryLocalHome#makeDbTable(java.lang.String, int, int)}
 * method creating a NON-OpenGis compliant table in the PostGIS database.
 * The table will contain the geometry EJBs.
 */
public void makeDbTable() {

    PreparedStatement pstm = null;
    Connection con = null;

    try {

        con = this.dataSource.getConnection();

        System.out.println("Creating non-OpenGIG Bean table... ");

        pstm = con.prepareStatement(PostGisGeometryDAO.HOME_CREATE_NON_OPENGIS_TABLE_STATEMENT);
        pstm.execute();

        System.out.println("...done!");

    } catch (SQLException e) {
        throw new EJBException(e);

    } finally {

        try {
            if (pstm != null) {
                pstm.close();
            }
        } catch (Exception e) {
        }

        try {
            if (con != null) {
                con.close();
            }

        } catch (Exception e) {
        }
    }
}

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

public static synchronized RatingBean save(Connection db, int userId, int projectId, int objectId, String vote,
        String table, String uniqueField, int setInappropriateColumn) throws SQLException {
    boolean commit = false;
    try {/*from   www  .j  av  a 2 s.c o m*/
        commit = db.getAutoCommit();
        if (commit) {
            db.setAutoCommit(false);
        }
        // Determine the current value
        int existingVote = queryUserRating(db, userId, objectId, table, uniqueField);
        int newVote = Integer.parseInt(vote);
        PreparedStatement pst = null;
        ResultSet rs = null;
        // Determine if an update, insert, or delete is required
        if (existingVote == -1) {
            // Perform an insert
            pst = db.prepareStatement("INSERT INTO " + table + "_rating " + "(project_id, "
                    + (Project.PRIMARY_KEY.equals(uniqueField) ? "" : uniqueField + ", ")
                    + ((setInappropriateColumn != Constants.UNDEFINED) ? "inappropriate, " : "")
                    + "rating, enteredby) " + "VALUES (?, "
                    + (Project.PRIMARY_KEY.equals(uniqueField) ? "" : "?, ")
                    + ((setInappropriateColumn != Constants.UNDEFINED) ? "?, " : "") + "?, ?)");
            int i = 0;
            pst.setInt(++i, projectId);
            if (!Project.PRIMARY_KEY.equals(uniqueField)) {
                pst.setInt(++i, objectId);
            }
            if (setInappropriateColumn != Constants.UNDEFINED) {
                pst.setBoolean(++i, (setInappropriateColumn == Constants.TRUE));
            }
            pst.setInt(++i, newVote);
            pst.setInt(++i, userId);
            pst.execute();
            pst.close();
        } else if (existingVote != newVote) {
            // Try an update
            pst = db.prepareStatement("UPDATE " + table + "_rating " + "SET rating = ?, entered = "
                    + DatabaseUtils.getCurrentTimestamp(db) + " "
                    + ((setInappropriateColumn != Constants.UNDEFINED) ? ", inappropriate = ? " : "") + "WHERE "
                    + uniqueField + " = ? AND enteredby = ? ");
            int i = 0;
            pst.setInt(++i, newVote);
            if (setInappropriateColumn != Constants.UNDEFINED) {
                pst.setBoolean(++i, (setInappropriateColumn == Constants.TRUE));
            }
            pst.setInt(++i, objectId);
            pst.setInt(++i, userId);
            pst.execute();
            pst.close();
        }
        if (existingVote != newVote) {
            // Update the object count and value
            pst = db.prepareStatement("UPDATE " + table + " "
                    + "SET rating_count = rating_count + ?, rating_value = rating_value + ?, "
                    + "rating_avg = ((rating_value + ?) / (rating_count + ?)) " + "WHERE " + uniqueField
                    + " = ? ");
            int i = 0;
            if (existingVote == -1) {
                if (newVote == INAPPROPRIATE_COMMENT) {
                    //rating count is incremented, but no change in rating value, therefore, rating average decreases
                    pst.setInt(++i, 1);
                    pst.setInt(++i, 0);
                    pst.setInt(++i, 0);
                    pst.setInt(++i, 1);
                } else {
                    pst.setInt(++i, 1);
                    pst.setInt(++i, newVote);
                    pst.setInt(++i, newVote);
                    pst.setInt(++i, 1);
                }
            } else {
                if (newVote == INAPPROPRIATE_COMMENT || existingVote == INAPPROPRIATE_COMMENT) {
                    if (newVote == INAPPROPRIATE_COMMENT) {
                        //The effects of the previous rating are reversed.
                        pst.setInt(++i, 0);
                        pst.setInt(++i, (-1) * existingVote);
                        pst.setInt(++i, (-1) * existingVote);
                        pst.setInt(++i, 0);
                    } else if (existingVote == INAPPROPRIATE_COMMENT) {
                        //The new rating by the user is recorded,
                        //as an existing inappropriate comment was never considered towards rating value, no additional math is required
                        pst.setInt(++i, 0);
                        pst.setInt(++i, newVote);
                        pst.setInt(++i, newVote);
                        pst.setInt(++i, 0);
                    }
                } else {
                    pst.setInt(++i, 0);
                    pst.setInt(++i, newVote - existingVote);
                    pst.setInt(++i, newVote - existingVote);
                    pst.setInt(++i, 0);
                }
            }
            pst.setInt(++i, objectId);
            //System.out.println(pst);
            pst.execute();
            pst.close();
        }

        if (setInappropriateColumn != Constants.UNDEFINED) {
            int inappropriateCount = 0;
            pst = db.prepareStatement("SELECT count(*) AS ic " + "FROM " + table + "_rating " + "WHERE "
                    + uniqueField + " = ? AND inappropriate = ?");
            int i = 0;
            pst.setInt(++i, objectId);
            pst.setBoolean(++i, true);
            rs = pst.executeQuery();
            if (rs.next()) {
                inappropriateCount = rs.getInt("ic");
            }
            rs.close();
            pst.close();

            pst = db.prepareStatement("UPDATE " + table + " " + "SET  inappropriate_count = ? " + "WHERE "
                    + uniqueField + " = ? ");
            i = 0;
            pst.setInt(++i, inappropriateCount);
            pst.setInt(++i, objectId);
            pst.execute();
            pst.close();
        }

        // Retrieve the values
        pst = db.prepareStatement(
                "SELECT rating_count, rating_value " + "FROM " + table + " WHERE " + uniqueField + " = ?");
        pst.setInt(1, objectId);
        rs = pst.executeQuery();
        int count = 0;
        int value = 0;
        if (rs.next()) {
            count = rs.getInt("rating_count");
            value = rs.getInt("rating_value");
        }
        rs.close();
        pst.close();
        if (commit) {
            db.commit();
        }
        // Share the rating bean
        RatingBean rating = new RatingBean();
        rating.setItemId(objectId);
        rating.setCount(count);
        rating.setValue(value);
        return rating;
    } catch (Exception e) {
        if (commit) {
            db.rollback();
        }
        LOG.error("save", e);
        throw new SQLException(e.getMessage());
    } finally {
        if (commit) {
            db.setAutoCommit(true);
        }
    }
}

From source file:com.l2jfree.gameserver.model.clan.L2ClanMember.java

public void saveApprenticeAndSponsor(int apprentice, int sponsor) {
    Connection con = null;/*w w w .ja v  a2  s.  c  o  m*/

    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con
                .prepareStatement("UPDATE characters SET apprentice=?,sponsor=? WHERE charId=?");
        statement.setInt(1, apprentice);
        statement.setInt(2, sponsor);
        statement.setInt(3, getObjectId());
        statement.execute();
        statement.close();
    } catch (SQLException e) {
        _log.warn("could not set apprentice/sponsor:", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:net.sf.l2j.gameserver.model.entity.L2JOneoRusEvents.CTF.java

public static void saveData() {
    java.sql.Connection con = null;
    try {//from  w w  w  . ja va 2  s .  c  o m
        con = L2DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement;
        statement = con.prepareStatement("Delete from ctf");
        statement.execute();
        statement.close();
        statement = con.prepareStatement(
                "INSERT INTO ctf (eventName, eventDesc, joiningLocation, minlvl, maxlvl, npcId, npcX, npcY, npcZ, npcHeading, rewardId, rewardAmount, teamsCount, joinTime, eventTime, minPlayers, maxPlayers) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        statement.setString(1, _eventName);
        statement.setString(2, _eventDesc);
        statement.setString(3, _joiningLocationName);
        statement.setInt(4, _minlvl);
        statement.setInt(5, _maxlvl);
        statement.setInt(6, _npcId);
        statement.setInt(7, _npcX);
        statement.setInt(8, _npcY);
        statement.setInt(9, _npcZ);
        statement.setInt(10, _npcHeading);
        statement.setInt(11, _rewardId);
        statement.setInt(12, _rewardAmount);
        statement.setInt(13, _teams.size());
        statement.setInt(14, _joinTime);
        statement.setInt(15, _eventTime);
        statement.setInt(16, _minPlayers);
        statement.setInt(17, _maxPlayers);
        statement.execute();
        statement.close();
        statement = con.prepareStatement("Delete from ctf_teams");
        statement.execute();
        statement.close();
        for (String teamName : _teams) {
            int index = _teams.indexOf(teamName);
            if (index == -1)
                return;
            statement = con.prepareStatement(
                    "INSERT INTO ctf_teams (teamId ,teamName, teamX, teamY, teamZ, teamColor, flagX, flagY, flagZ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
            statement.setInt(1, index);
            statement.setString(2, teamName);
            statement.setInt(3, _teamsX.get(index));
            statement.setInt(4, _teamsY.get(index));
            statement.setInt(5, _teamsZ.get(index));
            statement.setInt(6, _teamColors.get(index));
            statement.setInt(7, _flagsX.get(index));
            statement.setInt(8, _flagsY.get(index));
            statement.setInt(9, _flagsZ.get(index));
            statement.execute();
            statement.close();
        }
    } catch (Exception e) {
        _log.error("Exception: CTF.saveData(): " + e.getMessage());
    } finally {
        try {
            con.close();
        } catch (Exception e) {
        }
    }
}

From source file:net.mindengine.oculus.frontend.service.customization.JdbcCustomizationDAO.java

@Override
public Long addCustomization(Customization customization) throws Exception {
    PreparedStatement ps = getConnection()
            .prepareStatement("insert into customizations " + "(unit, " + "project_id, " + "name, "
                    + "description, " + "type, " + "subtype, " + "group_name) values (?,?,?,?,?,?,?)");

    ps.setString(1, customization.getUnit());
    ps.setLong(2, customization.getProjectId());
    ps.setString(3, customization.getName());
    ps.setString(4, customization.getDescription());
    ps.setString(5, customization.getType());
    ps.setString(6, customization.getSubtype());
    ps.setString(7, customization.getGroupName());
    logger.info(ps);//w w  w.  j a va  2s .c  o m
    ps.execute();

    ResultSet rs = ps.getGeneratedKeys();
    if (rs.next()) {
        return rs.getLong(1);
    }
    return null;
}

From source file:com.surfs.storage.common.datasource.jdbc.JdbcDao.java

@Override
public Object insert(String poolName, String sql, Object... params) throws Exception {
    Connection conn = null;//from   ww w  .ja v a2 s .co m
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = getConnection(poolName);
        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < params.length; i++) {
            if (params[i] instanceof String)
                ps.setString(i + 1, (String) params[i]);
            else if (params[i] instanceof Integer)
                ps.setInt(i + 1, (Integer) params[i]);
            else if (params[i] instanceof Long)
                ps.setLong(i + 1, (Long) params[i]);
            else if (params[i] instanceof Timestamp)
                ps.setTimestamp(i + 1, (Timestamp) params[i]);
        }
        ps.execute();
        rs = ps.getGeneratedKeys();
        if (rs.next())
            return rs.getObject(1);
        return null;
    } catch (Exception e) {
        throw e;
    } finally {
        JdbcUtils.closeResultset(rs);
        JdbcUtils.closeStatement(ps);
        JdbcUtils.closeConnect(conn);
    }
}

From source file:com.l2jfree.gameserver.model.entity.events.TvT.java

public static void teleportFinish() {
    AnnounceToPlayers(false, _eventName + "(TvT): Teleport back to participation NPC in 20 seconds!");

    ThreadPoolManager.getInstance().scheduleGeneral(new Runnable() {
        @Override/*from  w ww .j  a  v  a2s  . c  om*/
        public void run() {
            for (L2Player player : _players) {
                if (player != null) {
                    if (player.isOnline() != 0)
                        player.teleToLocation(_npcX, _npcY, _npcZ, false);
                    else {
                        Connection con = null;
                        try {
                            con = L2DatabaseFactory.getInstance().getConnection(con);

                            PreparedStatement statement = con
                                    .prepareStatement("UPDATE characters SET x=?, y=?, z=? WHERE char_name=?");
                            statement.setInt(1, _npcX);
                            statement.setInt(2, _npcY);
                            statement.setInt(3, _npcZ);
                            statement.setString(4, player.getName());
                            statement.execute();
                            statement.close();
                        } catch (SQLException se) {
                            _log.error(se.getMessage(), se);
                        } finally {
                            L2DatabaseFactory.close(con);
                        }
                    }
                }
            }
            _log.info("TvT: Teleport done.");
            cleanTvT();
        }
    }, 20000);
}

From source file:com.concursive.connect.web.modules.productcatalog.beans.OrderBean.java

/**
 * Description of the Method//from   www .  ja  va 2  s . c  o m
 *
 * @param db Description of the Parameter
 * @return Description of the Return Value
 * @throws SQLException Description of the Exception
 */
public boolean insert(Connection db) throws SQLException {
    try {
        db.setAutoCommit(false);
        // Insert the base order
        PreparedStatement pst = db.prepareStatement("INSERT INTO customer_order "
                + "(ipaddress, browser, total_price, order_by) VALUES (?,?,?,?) ");
        int i = 0;
        pst.setString(++i, ipAddress);
        pst.setString(++i, browser);
        pst.setDouble(++i, productList.getTotalPrice());
        DatabaseUtils.setInt(pst, ++i, userId);
        pst.execute();
        pst.close();
        id = DatabaseUtils.getCurrVal(db, "customer_order_order_id_seq", -1);
        // Insert the products
        productList.setOrderId(id);
        productList.insert(db);
        // Insert the contact info
        if (contactInformation.isValid()) {
            contactInformation.setOrderId(id);
            contactInformation.insert(db);
        }
        // Insert the addresses
        if (billing.isValid()) {
            billing.setOrderId(id);
            billing.insert(db);
        }
        if (shipping.isValid()) {
            shipping.setOrderId(id);
            shipping.insert(db);
        }
        // Insert the payment info
        if (payment.isValid()) {
            payment.setOrderId(id);
            payment.setChargeAmount(getChargeAmount());
            payment.insert(db);
        }
        db.commit();
        // Finalize
        saved = true;
        return true;
    } catch (Exception e) {
        db.rollback();
        LOG.error("insert", e);
        throw new SQLException("Could not save");
    } finally {
        db.setAutoCommit(true);
    }
}

From source file:dbcount.DbCountInitializeJob.java

private int populateAccess(final Connection conn) throws SQLException {
    final Random random = new Random();

    final int PROBABILITY_PRECISION = 100; // 1 / 100
    final int NEW_PAGE_PROBABILITY = 15; // 15 / 100

    // Pages in the site :
    final String[] pages = { "/a", "/b", "/c", "/d", "/e", "/f", "/g", "/h", "/i", "/j" };
    // linkMatrix[i] is the array of pages(indexes) that page_i links to.
    final int[][] linkMatrix = { { 1, 5, 7 }, { 0, 7, 4, 6, }, { 0, 1, 7, 8 }, { 0, 2, 4, 6, 7, 9 }, { 0, 1 },
            { 0, 3, 5, 9 }, { 0 }, { 0, 1, 3 }, { 0, 2, 6 }, { 0, 2, 6 } };

    int totalPageview = 0;
    PreparedStatement statement = null;
    try {/*from  w w w  . java  2  s.  co  m*/
        statement = conn.prepareStatement("INSERT INTO Access(url, referrer, time) VALUES (?, ?, ?)");

        int currentPage = random.nextInt(pages.length);
        String referrer = null;

        final int time = random.nextInt(50) + 50;
        for (int i = 0; i < time; i++) {
            statement.setString(1, pages[currentPage]);
            if (referrer == null) {
                statement.setNull(2, Types.VARCHAR);
            } else {
                statement.setString(2, referrer);
            }
            statement.setLong(3, i);
            statement.execute();
            ++totalPageview;

            // go to a new page with probability NEW_PAGE_PROBABILITY /
            // PROBABILITY_PRECISION
            int action = random.nextInt(PROBABILITY_PRECISION);
            if (action < NEW_PAGE_PROBABILITY) {
                currentPage = random.nextInt(pages.length); // a random page
                referrer = null;
            } else {
                referrer = pages[currentPage];
                action = random.nextInt(linkMatrix[currentPage].length);
                currentPage = linkMatrix[currentPage][action];
            }
        }

        conn.commit();

    } catch (SQLException ex) {
        conn.rollback();
        throw ex;
    } finally {
        if (statement != null) {
            statement.close();
        }
    }
    return totalPageview;
}

From source file:eu.optimis_project.monitoring.storage.MySQLStorageManager.java

@Override
public boolean storeData(Measurement measurement) throws IOException {
    final String query = "INSERT INTO " + tableName + " VALUES (?,?,?,?)";

    Connection connection = null;
    try {//from   w w w .j a v a 2s . c o m
        connection = getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query);
            statement.setString(ENTRIES_COLUMNINDEX_SERVICEID, measurement.getServiceID());
            statement.setString(ENTRIES_COLUMNINDEX_NAME, measurement.getName());
            statement.setString(ENTRIES_COLUMNINDEX_DATA, measurement.getData());
            statement.setLong(ENTRIES_COLUMNINDEX_TIMESTAMP, measurement.getTimestamp());
            statement.execute();
        } catch (SQLException e) {
            log.debug("Failed to add measurement: " + measurement, e);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.warn("Failed to close statement.");
            }
        }
    } catch (SQLException e) {
        throw new IOException(e);
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
        }
    }

    return true;
}