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.github.brandtg.switchboard.TestMysqlReplicationApplier.java

@Test
public void testRestoreFromBinlog() throws Exception {
    MysqlReplicationApplier applier = null;
    try (Connection conn = DriverManager.getConnection(jdbc, "root", "")) {
        // Write some rows, so we have binlog entries
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO simple VALUES(?, ?)");
        for (int i = 0; i < 10; i++) {
            pstmt.setInt(1, i);/*from w  w w .  j  a va 2  s .  co m*/
            pstmt.setInt(2, i);
            pstmt.execute();
        }

        // Copy the binlog somewhere
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("SHOW BINARY LOGS");
        rset.next();
        String binlogName = rset.getString("Log_name");
        rset = stmt.executeQuery("SELECT @@datadir");
        rset.next();
        String dataDir = rset.getString("@@datadir");
        File copyFile = new File(System.getProperty("java.io.tmpdir"),
                TestMysqlReplicationApplier.class.getName());
        FileUtils.copyFile(new File(dataDir + binlogName), copyFile);

        // Clear everything in MySQL
        resetMysql();

        // Get input stream, skipping and checking binlog magic number
        InputStream inputStream = new FileInputStream(copyFile);
        byte[] magic = new byte[MySQLConstants.BINLOG_MAGIC.length];
        int bytesRead = inputStream.read(magic);
        Assert.assertEquals(bytesRead, MySQLConstants.BINLOG_MAGIC.length);
        Assert.assertTrue(CodecUtils.equals(magic, MySQLConstants.BINLOG_MAGIC));

        // Restore from binlog
        PoolingDataSource<PoolableConnection> dataSource = getDataSource();
        applier = new MysqlReplicationApplier(inputStream, dataSource);
        ExecutorService executorService = Executors.newSingleThreadExecutor(new ThreadFactory() {
            @Override
            public Thread newThread(Runnable r) {
                Thread t = new Thread(r);
                t.setDaemon(true);
                return t;
            }
        });
        executorService.submit(applier);

        // Poll until we have restored
        long startTime = System.currentTimeMillis();
        long currentTime = startTime;
        do {
            stmt = conn.createStatement();
            rset = stmt.executeQuery("SELECT COUNT(*) FROM test.simple");
            rset.next();
            long count = rset.getLong(1);
            if (count == 10) {
                return;
            }
            Thread.sleep(1000);
            currentTime = System.currentTimeMillis();
        } while (currentTime - startTime < 10000);
    } finally {
        if (applier != null) {
            applier.shutdown();
        }
    }

    Assert.fail("Timed out when polling");
}

From source file:com.l2jfree.gameserver.communitybbs.bb.Post.java

public void deleteme(Topic t) {
    PostBBSManager.getInstance().delPostByTopic(t);
    Connection con = null;//  w  ww  .j av  a  2s  . c o m
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con
                .prepareStatement("DELETE FROM posts WHERE post_forum_id=? AND post_topic_id=?");
        statement.setInt(1, t.getForumID());
        statement.setInt(2, t.getID());
        statement.execute();
        statement.close();
    } catch (Exception e) {
        _log.error(e.getMessage(), e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:pl.edu.agh.iosr.lsf.dao.DatabaseHelper.java

public int insert(PreparedStatement ps) throws SQLException {
    ps.execute();
    try (ResultSet rs = ps.getGeneratedKeys()) {
        while (rs.next()) {
            return rs.getInt(1);
        }/*from   w  w w  .  j  a va2s  .c o  m*/
    }
    return -1;
}

From source file:com.srotya.tau.wraith.silo.sql.TestSQLRulesStore.java

@Before
public void before() throws SQLException, IOException {
    System.setProperty("derby.stream.error.field", DerbyUtil.class.getCanonicalName() + ".DEV_NULL");
    File db = new File(TARGET_RULES_DB);
    if (db.exists()) {
        System.out.println("Deleting database");
        FileUtils.deleteDirectory(db);//from  w  w  w.  ja v  a 2 s .c  om
    }
    String createTable = "create table testRules(" + SQLRulesStore.COLUMN_RULE_ID + " smallint primary key,"
            + SQLRulesStore.COLUMN_RULE_GROUP_ID + " varchar(100)," + SQLRulesStore.COLUMN_RULE_CONTENT
            + " varchar(3000))";
    runSQL(CONNECTION_STRING, createTable);

    Condition condition = new JavaRegexCondition("tst", "\\d+");
    Action action = new TemplatedAlertAction((short) 2, (short) 2);
    Rule testRule = new SimpleRule((short) 1233, "testRule", true, condition, action);

    Connection conn = DriverManager.getConnection(CONNECTION_STRING);
    PreparedStatement insert = conn.prepareStatement("insert into testRules values(?, ?, ?)");
    insert.setShort(1, testRule.getRuleId());
    insert.setString(2, "all");
    insert.setString(3, RuleSerializer.serializeRuleToJSONString(testRule, false));
    insert.execute();
    conn.close();
}

From source file:com.l2jfree.gameserver.instancemanager.ItemsOnGroundManager.java

private void load() {
    // If SaveDroppedItem is false, may want to delete all items previously stored to avoid add old items on reactivate
    if (!Config.SAVE_DROPPED_ITEM && Config.CLEAR_DROPPED_ITEM_TABLE)
        emptyTable();/*from  w w  w.  j a v  a 2 s .c  o m*/

    if (!Config.SAVE_DROPPED_ITEM)
        return;

    // if DestroyPlayerDroppedItem was previously  false, items curently protected will be added to ItemsAutoDestroy
    if (Config.DESTROY_DROPPED_PLAYER_ITEM) {
        Connection con = null;
        try {
            String str = null;
            if (!Config.DESTROY_EQUIPABLE_PLAYER_ITEM) // Recycle misc. items only
                str = "UPDATE itemsonground SET drop_time=? WHERE drop_time=-1 AND equipable=0";
            else if (Config.DESTROY_EQUIPABLE_PLAYER_ITEM) // Recycle all items including equipable
                str = "UPDATE itemsonground SET drop_time=? WHERE drop_time=-1";
            con = L2DatabaseFactory.getInstance().getConnection(con);
            PreparedStatement statement = con.prepareStatement(str);
            statement.setLong(1, System.currentTimeMillis());
            statement.execute();
            statement.close();
        } catch (Exception e) {
            _log.fatal("error while updating table ItemsOnGround " + e, e);
        } finally {
            L2DatabaseFactory.close(con);
        }
    }

    // Add items to world
    Connection con = null;
    try {
        try {
            con = L2DatabaseFactory.getInstance().getConnection(con);
            Statement s = con.createStatement();
            ResultSet result;
            int count = 0;
            result = s.executeQuery(
                    "SELECT object_id,item_id,count,enchant_level,x,y,z,drop_time,equipable FROM itemsonground");
            while (result.next()) {
                L2ItemInstance item = new L2ItemInstance(result.getInt(1), result.getInt(2));
                L2World.getInstance().storeObject(item);
                item.setCount(result.getLong(3));
                item.setEnchantLevel(result.getInt(4));
                item.getPosition().setXYZ(result.getInt(5), result.getInt(6), result.getInt(7));
                item.setDropTime(result.getLong(8));
                if (result.getLong(8) == -1)
                    item.setProtected(true);
                else
                    item.setProtected(false);
                L2World.getInstance().addVisibleObject(item);
                _items.add(item);
                count++;
                // Add to ItemsAutoDestroy only items not protected
                if (result.getLong(8) > -1) {
                    ItemsAutoDestroyManager.tryAddItem(item);
                }
            }
            result.close();
            s.close();
            if (count > 0)
                _log.info("ItemsOnGroundManager: restored " + count + " items.");
            else
                _log.info("Initializing ItemsOnGroundManager.");
        } catch (Exception e) {
            _log.fatal("error while loading ItemsOnGround " + e, e);
        }
    } finally {
        L2DatabaseFactory.close(con);
    }

    if (Config.EMPTY_DROPPED_ITEM_TABLE_AFTER_LOAD)
        emptyTable();
}

From source file:com.googlecode.psiprobe.controllers.sql.ExecuteSqlController.java

protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    String resourceName = ServletRequestUtils.getStringParameter(request, "resource");
    String sql = ServletRequestUtils.getStringParameter(request, "sql", null);

    if (sql == null || sql.equals("") || sql.trim().equals("")) {
        request.setAttribute("errorMessage",
                getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.required"));

        return new ModelAndView(getViewName());
    }//  w  ww .  j  a v  a2  s.c o m

    int maxRows = ServletRequestUtils.getIntParameter(request, "maxRows", 0);
    int rowsPerPage = ServletRequestUtils.getIntParameter(request, "rowsPerPage", 0);
    int historySize = ServletRequestUtils.getIntParameter(request, "historySize", 0);

    // store current option values and query history in a session attribute

    HttpSession sess = request.getSession();
    DataSourceTestInfo sessData = (DataSourceTestInfo) sess.getAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR);

    synchronized (sess) {
        if (sessData == null) {
            sessData = new DataSourceTestInfo();
            sess.setAttribute(DataSourceTestInfo.DS_TEST_SESS_ATTR, sessData);
        }

        sessData.setMaxRows(maxRows);
        sessData.setRowsPerPage(rowsPerPage);
        sessData.setHistorySize(historySize);
        sessData.addQueryToHistory(sql);
    }

    DataSource dataSource = null;

    try {
        dataSource = getContainerWrapper().getResourceResolver().lookupDataSource(context, resourceName,
                getContainerWrapper());
    } catch (NamingException e) {
        request.setAttribute("errorMessage", getMessageSourceAccessor()
                .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName }));
    }

    if (dataSource == null) {
        request.setAttribute("errorMessage", getMessageSourceAccessor()
                .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName }));
    } else {
        List results = null;
        int rowsAffected = 0;

        try {
            // TODO: use Spring's jdbc template?
            Connection conn = dataSource.getConnection();

            try {
                conn.setAutoCommit(true);
                PreparedStatement stmt = conn.prepareStatement(sql);

                try {
                    boolean hasResultSet = stmt.execute();

                    if (!hasResultSet) {
                        rowsAffected = stmt.getUpdateCount();
                    } else {
                        results = new ArrayList();
                        ResultSet rs = stmt.getResultSet();

                        try {
                            ResultSetMetaData metaData = rs.getMetaData();

                            while (rs.next() && (maxRows < 0 || results.size() < maxRows)) {
                                Map record = new LinkedHashMap();

                                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                                    String value = rs.getString(i);

                                    if (rs.wasNull()) {
                                        value = getMessageSourceAccessor()
                                                .getMessage("probe.src.dataSourceTest.sql.null");
                                    } else {
                                        value = HtmlUtils.htmlEscape(value);
                                    }

                                    // a work around for IE browsers bug of not displaying
                                    // a border around an empty table column

                                    if (value.equals("")) {
                                        value = "&nbsp;";
                                    }

                                    // Pad the keys of columns with existing labels so they are distinct
                                    String key = metaData.getColumnLabel(i);
                                    while (record.containsKey(key)) {
                                        key += " ";
                                    }
                                    record.put(HtmlUtils.htmlEscape(key), value);
                                }

                                results.add(record);
                            }
                        } finally {
                            rs.close();
                        }

                        rowsAffected = results.size();
                    }
                } finally {
                    stmt.close();
                }
            } finally {
                conn.close();
            }

            // store the query results in the session attribute in order
            // to support a result set pagination feature without re-executing the query

            synchronized (sess) {
                sessData.setResults(results);
            }

            ModelAndView mv = new ModelAndView(getViewName(), "results", results);
            mv.addObject("rowsAffected", String.valueOf(rowsAffected));
            mv.addObject("rowsPerPage", String.valueOf(rowsPerPage));

            return mv;
        } catch (SQLException e) {
            String message = getMessageSourceAccessor().getMessage("probe.src.dataSourceTest.sql.failure",
                    new Object[] { e.getMessage() });
            logger.error(message, e);
            request.setAttribute("errorMessage", message);
        }
    }

    return new ModelAndView(getViewName());
}

From source file:edu.umd.cs.psl.database.rdbms.RDBMSDataStoreMetadata.java

public int getMaxPartition() {
    int max = 0;//ww  w.j  a  v  a2s.  co  m
    try {
        PreparedStatement stmt = conn.prepareStatement("SELECT MAX(CAST(value as INT)) from " + mdTableName
                + " WHERE namespace = 'Partition' AND keytype = 'name'");
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        if (rs.next()) {
            max = Integer.parseInt(rs.getString(1));
        }
    } catch (Exception e) {
        log.error("Could not get max partition - " + e.getMessage());
        return 0;
    }
    return max;
}

From source file:at.becast.youploader.account.Account.java

public void updateCookie(int id) throws IOException {
    ObjectMapper mapper = new ObjectMapper();
    LOG.info("Updating account");
    try {/*w  ww  .  j a v  a2  s .  co m*/
        PreparedStatement stmt = c.prepareStatement("UPDATE `accounts` SET `cookie`=? WHERE `id`=?");
        stmt.setString(1, mapper.writeValueAsString(this.cdata));
        stmt.setInt(2, id);
        stmt.execute();
        stmt.close();
    } catch (SQLException e) {
        LOG.error("Could not update account Ex:", e);
    }
}

From source file:net.mindengine.oculus.frontend.service.report.filter.JdbcFilterDAO.java

@Override
public long createFilter(Filter filter) throws Exception {
    String sql = "insert into filters (name, description, user_id, date, filter) values (?,?,?,?,?)";
    PreparedStatement ps = getConnection().prepareStatement(sql);

    ps.setString(1, filter.getName());//from ww  w.jav a 2  s . c  o m
    ps.setString(2, filter.getDescription());
    ps.setLong(3, filter.getUserId());
    ps.setTimestamp(4, new Timestamp(filter.getDate().getTime()));
    ps.setString(5, filter.getFilter());

    logger.info(ps);
    ps.execute();

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

From source file:com.wso2telco.dep.verificationhandler.verifier.DatabaseUtils.java

/**
 * Subscribe user./*ww  w .  j  a v a2  s  .com*/
 *
 * @param subscriber the subscriber
 * @param app the app
 * @param api the api
 * @param msisdn the msisdn
 * @throws APIManagementException the API management exception
 */
public static void SubscribeUser(String subscriber, String app, String api, String msisdn)
        throws APIManagementException {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet results = null;

    String sql = "INSERT INTO `dialg_stats`.`subscriptionmsisdn` (`userID`, `api`, `application`, `MSISDN`) VALUES (?, ?, ?, ?);";
    try {
        try {
            connection = getStatsDBConnection();
        } catch (NamingException ex) {
            Logger.getLogger(DatabaseUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
        ps = connection.prepareStatement(sql);

        ps.setString(1, subscriber);
        ps.setString(2, api);
        ps.setString(3, app);
        ps.setString(4, msisdn);

        ps.execute();

    } catch (SQLException e) {
        handleException("Error occurred while getting Invocation count for Application", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, connection, results);
    }

}