Example usage for java.sql PreparedStatement execute

List of usage examples for java.sql PreparedStatement 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.ea.core.orm.handle.impl.HibernateSqlORMHandle.java

@Override
protected Object execute(ORMParamsDTO dto) throws Exception {
    // TODO Auto-generated method stub
    Session session = this.getHibernateSessionFactory().getCurrentSession();
    final ORMParamsDTO tmp = dto;
    session.doWork(new Work() {
        @SuppressWarnings("rawtypes")
        public void execute(Connection connection) throws SQLException {
            // connectionJDBC
            // closeconnection
            System.out.println("sql:" + tmp.getSqlid());
            PreparedStatement ps = connection.prepareStatement(tmp.getSqlid());
            if (tmp.getParam() != null) {
                Object data = tmp.getParam();
                if (data instanceof Object[]) {
                    Object[] array = (Object[]) data;
                    int index = 1;
                    for (Object obj : array) {
                        setParam(ps, index++, obj);
                    }/*from ww  w  .  j  a  va  2s.  c o  m*/
                    ps.execute();
                } else if (data instanceof Collection) {
                    for (Object array : (Collection) data) {
                        if (array instanceof Object[]) {
                            int index = 1;
                            for (Object obj : (Object[]) array) {
                                setParam(ps, index++, obj);
                            }
                            ps.addBatch();
                        } else {
                            throw new SQLException("SQL?Object[]???!");
                        }

                    }
                    ps.executeBatch();
                } else {
                    throw new SQLException(
                            "SQL????Object[]???????CollectionObject[]!");
                }
            }

        }
    });
    return null;
}

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

public static void saveData() {
    Connection con = null;//from   w w  w  .ja v  a 2s.  c  o m
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement;

        statement = con.prepareStatement("Delete from tvt");
        statement.execute();
        statement.close();

        statement = con.prepareStatement(
                "INSERT INTO tvt (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 tvt_teams");
        statement.execute();
        statement.close();

        for (String teamName : _teams) {
            int index = _teams.indexOf(teamName);

            if (index == -1)
                return;
            statement = con.prepareStatement(
                    "INSERT INTO tvt_teams (teamId ,teamName, teamX, teamY, teamZ, teamColor) 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.execute();
            statement.close();
        }
    } catch (Exception e) {
        _log.error("Exception: TvT.saveData(): ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

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

public void deleteSpawn(L2Spawn spawn, boolean updateDb) {
    if (_spawnTable.remove(spawn.getId()) == null)
        return;/* ww  w.  j a  va2s.co  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:com.l2jfree.gameserver.model.restriction.ObjectRestrictions.java

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

    Connection con = null;//  w  w w.  j  av a  2 s .  c  om
    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:com.microsoftopentechnologies.azchat.web.dao.PreferenceMetadataDAOImpl.java

/**
 * This method creates the preference meta data table.
 *///ww w .  j  a v  a 2s .c o  m
@Override
public void createPreferenceMatedateTable() throws Exception {
    Connection connection = null;
    Connection connection1 = null;
    PreparedStatement preparedStatement = null;
    PreparedStatement preparedStatement1 = null;
    try {
        connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
        preparedStatement = connection.prepareStatement(AzureChatSQLConstants.CREATE_PREFERENCE_METADATA_TABLE);
        preparedStatement.execute();
        connection1 = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
        preparedStatement1 = connection1
                .prepareStatement(AzureChatSQLConstants.CREATE_PREFERENCE_METADATA_TABLE_INDEX);
        preparedStatement1.execute();
    } catch (SQLException e) {
        LOGGER.error(
                "Exception occurred while executing create user table  query on azure SQL table. Exception Mesage : "
                        + e.getMessage());
        throw new AzureChatSystemException(
                "Exception occurred while executing createUserTable query on azure sql. Exception Mesage :  "
                        + e.getMessage());
    } finally {
        AzureChatUtils.closeDatabaseResources(preparedStatement, connection);
        AzureChatUtils.closeDatabaseResources(preparedStatement1, connection1);
    }
}

From source file:com.magnet.mmx.server.plugin.mmxmgmt.api.tags.MMXTopicTagsResourceTest.java

@After
public void cleanupTags() {
    final String statementStr = "DELETE FROM mmxTag WHERE tagname LIKE '%" + "tag" + "%'";
    Connection conn = null;/* w  w w  . java 2 s . co  m*/
    PreparedStatement pstmt = null;
    try {
        conn = UnitTestDSProvider.getDataSource().getConnection();
        pstmt = conn.prepareStatement(statementStr);

        pstmt.execute();

    } catch (SQLException e) {
        LOGGER.error("cleanupDatabase : {}", e);
    } finally {
        CloseUtil.close(LOGGER, pstmt, conn);
    }
}

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

public synchronized void destroyClan(int clanId) {
    _log.info("destroy clan with id:" + clanId);
    L2Clan clan = getClan(clanId);/*ww w. j a v a2s.c o  m*/
    if (clan == null) {
        return;
    }

    clan.broadcastToOnlineMembers(SystemMessageId.CLAN_HAS_DISPERSED.getSystemMessage());

    int castleId = clan.getHasCastle();
    if (castleId == 0) {
        for (Castle castle : CastleManager.getInstance().getCastles().values()) {
            castle.getSiege().removeSiegeClan(clanId);
        }
    }
    int fortId = clan.getHasFort();
    if (fortId == 0) {
        for (FortSiege siege : FortSiegeManager.getInstance().getSieges()) {
            siege.removeSiegeClan(clanId);
        }
    }

    L2ClanMember leaderMember = clan.getLeader();
    if (leaderMember == null)
        clan.getWarehouse().destroyAllItems("ClanRemove", null, null);
    else
        clan.getWarehouse().destroyAllItems("ClanRemove", clan.getLeader().getPlayerInstance(), null);

    for (L2ClanMember member : clan.getMembers()) {
        clan.removeClanMember(member.getObjectId(), 0);
    }

    _clans.remove(clanId);
    IdFactory.getInstance().releaseId(clanId);

    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement("DELETE FROM clan_data WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
        statement.close();

        statement = con.prepareStatement("DELETE FROM clan_privs WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
        statement.close();

        statement = con.prepareStatement("DELETE FROM clan_skills WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
        statement.close();

        statement = con.prepareStatement("DELETE FROM clan_subpledges WHERE clan_id=?");
        statement.setInt(1, clanId);
        statement.execute();
        statement.close();

        statement = con.prepareStatement("DELETE FROM clan_wars WHERE clan1=? OR clan2=?");
        statement.setInt(1, clanId);
        statement.setInt(2, clanId);
        statement.execute();
        statement.close();

        statement = con.prepareStatement("DELETE FROM clan_notices WHERE clanID=?");
        statement.setInt(1, clanId);
        statement.execute();
        statement.close();

        if (castleId != 0) {
            statement = con.prepareStatement("UPDATE castle SET taxPercent = 0 WHERE id = ?");
            statement.setInt(1, castleId);
            statement.execute();
            statement.close();
        }
        if (fortId != 0) {
            Fort fort = FortManager.getInstance().getFortById(fortId);
            if (fort != null) {
                L2Clan owner = fort.getOwnerClan();
                if (clan == owner)
                    fort.removeOwner(true);
            }
        }

        if (_log.isDebugEnabled())
            _log.info("clan removed in db: " + clanId);
    } catch (Exception e) {
        _log.error("error while removing clan in db ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.splicemachine.derby.impl.sql.execute.operations.InsertOperationIT.java

@Test
public void testInsertBlob() throws Exception {
    InputStream fin = new FileInputStream(getResourceDirectory() + "order_line_500K.csv");
    PreparedStatement ps = methodWatcher.prepareStatement("insert into FILES (name, doc) values (?,?)");
    ps.setString(1, "csv_file");
    ps.setBinaryStream(2, fin);//from w  w w  . ja  v a2s  .  com
    ps.execute();
    ResultSet rs = methodWatcher.executeQuery("SELECT doc FROM FILES WHERE name = 'csv_file'");
    byte buff[] = new byte[1024];
    while (rs.next()) {
        Blob ablob = rs.getBlob(1);
        File newFile = new File(getBaseDirectory() + "/target/order_line_500K.csv");
        if (newFile.exists()) {
            newFile.delete();
        }
        newFile.createNewFile();
        InputStream is = ablob.getBinaryStream();
        FileOutputStream fos = new FileOutputStream(newFile);
        for (int b = is.read(buff); b != -1; b = is.read(buff)) {
            fos.write(buff, 0, b);
        }
        is.close();
        fos.close();
    }
    File file1 = new File(getResourceDirectory() + "order_line_500K.csv");
    File file2 = new File(getBaseDirectory() + "/target/order_line_500K.csv");
    Assert.assertTrue("The files contents are not equivalent", FileUtils.contentEquals(file1, file2));
}

From source file:com.fluidops.iwb.wiki.WikiSQLStorageBase.java

@Override
public boolean deleteAllOlder(URI resource, Date date) {
    PreparedStatement prep = null;
    try {//  w w  w. ja  v  a 2s.  c om
        Connection conn = getConnection();
        prep = conn.prepareStatement("delete from revisions where name=? and date>?;");
        prep.setString(1, resource.stringValue());
        prep.setLong(2, date.getTime());

        boolean res = prep.execute();
        SQL.monitorWrite();
        return res;
    } catch (SQLException e) {
        SQL.monitorWriteFailure();
        throw new RuntimeException("Delete revisions failed", e);
    } finally {
        SQL.closeQuietly(prep);
    }
}

From source file:com.fluidops.iwb.wiki.WikiSQLStorageBase.java

@Override
protected boolean deleteRevisionFromStorage(URI resource, WikiRevision rev) {
    PreparedStatement prep = null;
    try {/*from ww  w.  j ava2  s.c o m*/
        Connection conn = getConnection();
        prep = conn.prepareStatement("delete from revisions where name=? and date=?;");
        prep.setString(1, resource.stringValue());
        prep.setLong(2, rev.date.getTime());

        boolean res = prep.execute();
        SQL.monitorWrite();
        return res;
    } catch (SQLException e) {
        SQL.monitorWriteFailure();
        throw new RuntimeException("Delete revision failed.", e);
    } finally {
        SQL.closeQuietly(prep);
    }
}