Example usage for java.sql PreparedStatement close

List of usage examples for java.sql PreparedStatement close

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

Introduction

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

Prototype

void close() throws SQLException;

Source Link

Document

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

Usage

From source file:com.l2jfree.gameserver.model.restriction.ObjectRestrictions.java

public void shutdown() {
    System.out.println("ObjectRestrictions: storing started:");

    Connection con = null;//from   ww w .  j a  v a 2  s  .  co  m
    try {
        con = L2DatabaseFactory.getInstance().getConnection();

        // Clean up old table data
        PreparedStatement statement = con.prepareStatement(DELETE_RESTRICTIONS);
        statement.execute();
        statement.close();

        System.out.println("ObjectRestrictions: storing permanent restrictions.");
        // Store permanent restrictions
        for (Entry<Integer, EnumSet<AvailableRestriction>> entry : _restrictionList.entrySet()) {
            for (AvailableRestriction restriction : entry.getValue()) {
                statement = con.prepareStatement(INSERT_RESTRICTIONS);

                statement.setInt(1, entry.getKey());
                statement.setString(2, restriction.name());
                statement.setLong(3, -1);
                statement.setString(4, "");

                statement.execute();
                statement.close();
            }
        }

        System.out.println("ObjectRestrictions: storing paused events.");
        // Store paused restriction events
        for (Entry<Integer, List<PausedTimedEvent>> entry : _pausedActions.entrySet()) {
            for (PausedTimedEvent paused : entry.getValue()) {
                statement = con.prepareStatement(INSERT_RESTRICTIONS);

                statement.setInt(1, entry.getKey());
                statement.setString(2, paused.getAction().getRestriction().name());
                statement.setLong(3, paused.getRemainingTime());
                statement.setString(4, paused.getAction().getMessage());

                statement.execute();
                statement.close();
            }
        }

        System.out.println("ObjectRestrictions: stopping and storing running events.");
        // Store running restriction events
        for (Entry<Integer, List<TimedRestrictionAction>> entry : _runningActions.entrySet()) {
            for (TimedRestrictionAction action : entry.getValue()) {
                // Shutdown task
                action.getTask().cancel(true);

                statement = con.prepareStatement(INSERT_RESTRICTIONS);

                statement.setInt(1, entry.getKey());
                statement.setString(2, action.getRestriction().name());
                statement.setLong(3, action.getRemainingTime());
                statement.setString(4, action.getMessage());

                statement.execute();
                statement.close();
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        L2DatabaseFactory.close(con);
    }

    System.out.println("ObjectRestrictions: All data saved.");
}

From source file:org.castor.jpa.functional.AbstractSpringBaseTest.java

/**
 * This helper method executes a prepared statement including the given
 * parameters./*from   ww w  . ja  va 2 s.  c  om*/
 * 
 * @param query
 *            is a native SQL query.
 * @param parameters
 *            is an array of {@link Object}s used as parameters.
 * @return the same as {@link PreparedStatement#executeUpdate()}.
 * @throws SQLException
 *             in case execution fails.
 */
protected final int executeUpdate(String query, Object... parameters) throws SQLException {
    try {
        Connection connection = this.dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        for (int parameterIndex = 0; parameterIndex < parameters.length; parameterIndex++) {
            Object parameter = parameters[parameterIndex];
            preparedStatement.setObject(parameterIndex + 1, parameter);
        }

        // Execute query.
        int numberAffected = preparedStatement.executeUpdate();
        // Release resources.
        preparedStatement.close();
        connection.close();

        return numberAffected;

    } catch (SQLException e) {
        throw e;
    }

}

From source file:com.l2jfree.gameserver.datatables.SpawnTable.java

public void deleteSpawn(L2Spawn spawn, boolean updateDb) {
    if (_spawnTable.remove(spawn.getId()) == null)
        return;/*  w  w  w . j a  va2  s  .  c o  m*/

    if (updateDb) {
        Connection con = null;

        try {
            con = L2DatabaseFactory.getInstance().getConnection(con);
            PreparedStatement statement = con.prepareStatement(
                    "DELETE FROM " + (spawn.isCustom() ? "custom_spawnlist" : "spawnlist") + " WHERE id=?");
            statement.setInt(1, spawn.getDbId());
            statement.execute();
            statement.close();
        } catch (Exception e) {
            // problem with deleting spawn
            _log.warn("SpawnTable: Spawn " + spawn.getDbId() + " could not be removed from DB: ", e);
        } finally {
            L2DatabaseFactory.close(con);
        }
    }
}

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

@Override
public void delete(int id) {
    Connection conn = HarvestDBConnection.get();
    PreparedStatement stmt = null;
    try {/*from   ww  w  .  j a va  2 s.c o  m*/
        conn.setAutoCommit(false);
        // First delete the list.
        stmt = conn.prepareStatement(DELETE_TRAPLIST_STMT);
        stmt.setInt(1, id);
        stmt.executeUpdate();
        stmt.close();
        // Then delete all its expressions.
        stmt = conn.prepareStatement(DELETE_EXPR_STMT);
        stmt.setInt(1, id);
        stmt.executeUpdate();
        conn.commit();
    } catch (SQLException e) {
        String message = "Error deleting trap list: '" + id + "'\n" + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new UnknownID(message, e);
    } finally {
        DBUtils.closeStatementIfOpen(stmt);
        DBUtils.rollbackIfNeeded(conn, "delete trap list", id);
        HarvestDBConnection.release(conn);
    }
}

From source file:me.doshou.admin.monitor.web.controller.SQLExecutorController.java

@PageableDefaults(pageNumber = 0, value = 10)
@RequestMapping(value = "/sql", method = RequestMethod.POST)
public String executeQL(final @RequestParam("sql") String sql, final Model model, final Pageable pageable) {

    model.addAttribute("sessionFactory", HibernateUtils.getSessionFactory(em));

    String lowerCaseSQL = sql.trim().toLowerCase();
    final boolean isDML = lowerCaseSQL.startsWith("insert") || lowerCaseSQL.startsWith("update")
            || lowerCaseSQL.startsWith("delete");
    final boolean isDQL = lowerCaseSQL.startsWith("select");

    if (!isDML && !isDQL) {
        model.addAttribute(Constants.ERROR,
                "SQL????insert?update?delete?select");
        return showSQLForm();
    }/*from  www.j  av a2 s  . c  o  m*/
    try {
        new TransactionTemplate(transactionManager).execute(new TransactionCallback<Void>() {
            @Override
            public Void doInTransaction(TransactionStatus status) {

                if (isDML) {
                    Query query = em.createNativeQuery(sql);
                    int updateCount = query.executeUpdate();
                    model.addAttribute("updateCount", updateCount);
                } else {
                    String findSQL = sql;
                    String countSQL = "select count(*) count from (" + findSQL + ") o";
                    Query countQuery = em.createNativeQuery(countSQL);
                    Query findQuery = em.createNativeQuery(findSQL);
                    findQuery.setFirstResult(pageable.getOffset());
                    findQuery.setMaxResults(pageable.getPageSize());

                    Page page = new PageImpl(findQuery.getResultList(), pageable,
                            ((BigInteger) countQuery.getSingleResult()).longValue());

                    model.addAttribute("resultPage", page);

                    em.unwrap(Session.class).doWork(new Work() {
                        @Override
                        public void execute(final Connection connection) throws SQLException {
                            PreparedStatement psst = connection.prepareStatement(sql);
                            ResultSetMetaData metaData = psst.getMetaData();

                            List<String> columnNames = Lists.newArrayList();
                            for (int i = 1, l = metaData.getColumnCount(); i <= l; i++) {
                                columnNames.add(metaData.getColumnLabel(i));
                            }
                            psst.close();
                            model.addAttribute("columnNames", columnNames);
                        }
                    });
                }

                return null;
            }
        });
    } catch (Exception e) {
        StringWriter sw = new StringWriter();
        e.printStackTrace(new PrintWriter(sw));
        model.addAttribute(Constants.ERROR, sw.toString());
    }

    return showSQLForm();
}

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

@Override
public void loadInstances() {
    Connection con = null;// w  w  w  .ja  v a2s . c o  m
    try {
        PreparedStatement statement;
        ResultSet rs;

        con = L2DatabaseFactory.getInstance().getConnection(con);

        statement = con.prepareStatement("SELECT id FROM castle ORDER BY id");
        rs = statement.executeQuery();

        while (rs.next()) {
            int id = rs.getInt("id");
            getCastles().put(id, new Castle(id));
        }

        statement.close();

        _log.info("Loaded: " + getCastles().size() + " castles");
    } catch (SQLException e) {
        _log.warn("Exception: loadCastleData(): " + e.getMessage(), e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.redhat.victims.database.VictimsSQL.java

/**
 * Given a hash get the first occurance's record id.
 *
 * @param hash//from  ww  w  .  j  a  va2  s. c  o m
 * @return
 * @throws SQLException
 */
protected int selectRecordId(String hash) throws SQLException {
    int id = -1;
    Connection connection = getConnection();
    try {
        PreparedStatement ps = setObjects(connection, Query.GET_RECORD_ID, hash);
        ResultSet rs = ps.executeQuery();
        try {
            while (rs.next()) {
                id = rs.getInt("id");
                break;
            }
        } finally {
            rs.close();
            ps.close();
        }
    } finally {
        connection.close();
    }
    return id;
}

From source file:mayoapp.migrations.V0075_0003__update_tenant_configurations.java

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);/* www  .ja  v  a2s.  com*/

    Statement queryIdsStatement = connection.createStatement();
    ResultSet tenants = queryIdsStatement.executeQuery("SELECT entity_id, slug, configuration FROM entity "
            + "INNER JOIN tenant ON entity.id = tenant.entity_id");

    Map<UUID, ConfigurationAndName> tenantsData = Maps.newHashMap();

    while (tenants.next()) {
        String json = tenants.getString("configuration");
        String name = tenants.getString("slug");
        ObjectMapper objectMapper = new ObjectMapper();
        Map<String, Object> configuration = objectMapper.readValue(json,
                new TypeReference<Map<String, Object>>() {
                });
        if (configuration.containsKey("general")) {
            Map<String, Object> generalConfiguration = (Map<String, Object>) configuration.get("general");
            if (generalConfiguration.containsKey("name")) {
                name = (String) generalConfiguration.get("name");
                ((Map<String, Object>) configuration.get("general")).remove("name");
                json = objectMapper.writeValueAsString(configuration);
            }

        }
        ConfigurationAndName configurationAndName = new ConfigurationAndName(json, name);
        tenantsData.put((UUID) tenants.getObject("entity_id"), configurationAndName);
    }

    queryIdsStatement.close();

    PreparedStatement statement = connection
            .prepareStatement("UPDATE tenant SET name=?, configuration=? WHERE entity_id =?");

    for (UUID id : tenantsData.keySet()) {
        statement.setString(1, tenantsData.get(id).getName());
        statement.setString(2, tenantsData.get(id).getConfiguration());
        statement.setObject(3, new PG_UUID(id));
        statement.addBatch();
    }

    try {
        statement.executeBatch();
    } finally {
        statement.close();
    }
}

From source file:com.sap.dirigible.runtime.scripting.DbUtils.java

private void deleteSequence(Connection connection, String sequenceName) throws SQLException {
    PreparedStatement preparedStatement = null;
    try {/*from  w w w . j  ava  2s  .  c o m*/
        String sql = DELETE_FROM + DGB_SEQUENCES + WHERE_SEQ_NAME;
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, sequenceName);
        preparedStatement.executeUpdate();
    } finally {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
    }
}

From source file:com.dbsvg.models.SQLiteInternalDataDAO.java

/**
 * Deletes a TableView//from   w  w  w  . j  a va2 s .  com
 * 
 * @param tv
 * @param conn
 */
public void deleteTablePosition(TableView tv, Connection conn) throws SQLException {
    String insertTableViewSQL = "DELETE FROM table_page_position WHERE pageid=? AND tableid=?;";
    PreparedStatement ps = conn.prepareStatement(insertTableViewSQL);
    ps.setString(1, tv.getPage().getId().toString());
    ps.setString(2, tv.getTable().getId().toString());
    ps.executeUpdate();
    ps.close();

    LOG.info("DELETED TableView {}", tv);
}