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.fluidops.iwb.wiki.WikiSQLStorageBase.java

@Override
protected void storeWikiContent(URI resource, String content, WikiRevision revision) throws IOException {
    PreparedStatement prep = null;
    try {/* ww  w.  ja  v a2  s  . c o  m*/
        Connection conn = getConnection();
        prep = conn.prepareStatement("insert into revisions values (?, ?, ?, ?, ?, ?, ?);");

        prep.setString(1, resource.stringValue());
        prep.setLong(2, revision.date.getTime());
        prep.setLong(3, revision.size);
        prep.setString(4, revision.comment);
        prep.setString(5, revision.user);
        prep.setString(6, revision.security);
        if (com.fluidops.iwb.util.Config.getConfig().getCompressWikiInDatabase())
            prep.setBytes(7, gzip(content));
        else
            prep.setString(7, content);

        prep.execute();
        SQL.monitorWrite();
    } catch (SQLException e) {
        SQL.monitorWriteFailure();
        throw new IOException("Storing wiki content failed.", e);
    } finally {
        SQL.closeQuietly(prep);
    }
}

From source file:migration.ProjektMigration.java

/**
 * Clean db.//www.  java2 s . c o  m
 * 
 * @throws SQLException
 *             the sQL exception
 */
public void cleanDB() throws SQLException {
    String store_sql;
    PreparedStatement store_prepstmt;
    final ResultSet store_rs;
    store_sql = "Delete From interesse Where true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE interesse AUTO_INCREMENT=1;";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM besteller WHERE true;";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE besteller AUTO_INCREMENT=1;";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM journalkosten WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE journalkosten AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM nutzung WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE nutzung AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM rechnung WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE rechnung AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM journal_fach WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE journal_fach AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM exemplar WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE exemplar AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM journal WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE journal AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM konsortium WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE konsortium AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM paket WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE paket AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM sigel WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE sigel AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM lizenzdetail WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE lizenzdetail AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM institution WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE institution AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM fach WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE fach AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "DELETE FROM bibliotheksmitarbeiter WHERE true";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
    store_sql = "ALTER TABLE bibliotheksmitarbeiter AUTO_INCREMENT=1";
    store_prepstmt = this.tgt_con.prepareStatement(store_sql);
    store_prepstmt.execute();
}

From source file:com.commander4j.db.JDBPrinters.java

public boolean delete() {
    PreparedStatement stmtupdate;
    boolean result = false;
    setErrorMessage("");

    try {//from  w  w  w  .j  a  v a  2 s. c  o m

        stmtupdate = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement(
                Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBPrinters.delete"));
        stmtupdate.setString(1, getPrinterID());
        stmtupdate.setString(2, getGroupID());
        stmtupdate.execute();
        stmtupdate.clearParameters();
        Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit();
        stmtupdate.close();
        result = true;

        /* Remove all links to printer */
        JDBPrinterLineMembership plm = new JDBPrinterLineMembership(getHostID(), getSessionID());
        plm.deleteForPrinterID(getPrinterID());

    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }

    return result;
}

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

private void removeSiegeClan(int clanId) {
    if (clanId <= 0)
        return;/*from   w w  w .  j  a  v  a  2  s. co m*/

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

        loadSiegeClan();
    } catch (Exception e) {
        _log.error(e.getMessage(), e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:gsn.storage.StorageManager.java

/**
 * Create a table with a index on the timed field.
 *
 * @param tableName/*from  w ww .  ja  v a 2s.com*/
 * @param structure
 * @param unique     , setting this true cause the system to create a unique index on time.
 * @param connection
 * @throws SQLException
 */
public void executeCreateTable(CharSequence tableName, DataField[] structure, boolean unique,
        Connection connection) throws SQLException {
    StringBuilder sql = getStatementCreateTable(tableName, structure, connection);
    logger.debug(new StringBuilder().append("The create table statement is : ").append(sql).toString());

    PreparedStatement prepareStatement = connection.prepareStatement(sql.toString());
    prepareStatement.execute();
    prepareStatement.close();
    sql = getStatementCreateIndexOnTimed(tableName, unique);
    logger.debug(new StringBuilder().append("The create index statement is : ").append(sql).toString());
    prepareStatement = connection.prepareStatement(sql.toString());
    prepareStatement.execute();

}

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testLobTempFiles() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, DATA CLOB)");
    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
    for (int i = 0; i < 5; i++) {
        prep.setInt(1, i);/*from w ww.  j av  a2s .  c o  m*/
        if (i % 2 == 0) {
            prep.setCharacterStream(2, new StringReader(getString(i)), -1);
        }
        prep.execute();
    }
    ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
    int check = 0;
    for (int i = 0; i < 5; i++) {
        assertTrue(rs.next());
        if (i % 2 == 0) {
            check = i;
        }
        assertEquals(getString(check), rs.getString(2));
    }
    assertFalse(rs.next());
    stat.execute("DELETE FROM TEST");
    for (int i = 0; i < 3; i++) {
        prep.setInt(1, i);
        prep.setCharacterStream(2, new StringReader(getString(i)), -1);
        prep.addBatch();
    }
    prep.executeBatch();
    rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
    for (int i = 0; i < 3; i++) {
        assertTrue(rs.next());
        assertEquals(getString(i), rs.getString(2));
    }
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
}

From source file:gsn.storage.StorageManager.java

public void executeCreateView(CharSequence viewName, CharSequence selectQuery, Connection connection)
        throws SQLException {
    StringBuilder statement = getStatementCreateView(viewName, selectQuery);
    logger.debug("Creating a view:" + statement);
    final PreparedStatement prepareStatement = connection.prepareStatement(statement.toString());
    prepareStatement.execute();
    close(prepareStatement);//  w w w. j av a2  s  . c om
}

From source file:at.alladin.rmbt.statisticServer.export.ExportResource.java

@Get
public Representation request(final String entity) {
    //Before doing anything => check if a cached file already exists and is new enough
    String property = System.getProperty("java.io.tmpdir");

    final String filename_zip;
    final String filename_csv;

    //allow filtering by month/year
    int year = -1;
    int month = -1;
    int hours = -1;
    boolean hoursExport = false;
    boolean dateExport = false;

    if (getRequest().getAttributes().containsKey("hours")) { // export by hours
        try {/*from  ww w.  jav a  2 s  .c o m*/
            hours = Integer.parseInt(getRequest().getAttributes().get("hours").toString());
        } catch (NumberFormatException ex) {
            //Nothing -> just fall back
        }
        if (hours <= 7 * 24 && hours >= 1) { //limit to 1 week (avoid DoS)
            hoursExport = true;
        }
    } else if (!hoursExport && getRequest().getAttributes().containsKey("year")) { // export by month/year 
        try {
            year = Integer.parseInt(getRequest().getAttributes().get("year").toString());
            month = Integer.parseInt(getRequest().getAttributes().get("month").toString());
        } catch (NumberFormatException ex) {
            //Nothing -> just fall back
        }
        if (year < 2099 && month > 0 && month <= 12 && year > 2000) {
            dateExport = true;
        }
    }

    if (hoursExport) {
        filename_zip = FILENAME_ZIP_HOURS.replace("%HOURS%", String.format("%03d", hours));
        filename_csv = FILENAME_CSV_HOURS.replace("%HOURS%", String.format("%03d", hours));
        cacheThresholdMs = 5 * 60 * 1000; //5 minutes
    } else if (dateExport) {
        filename_zip = FILENAME_ZIP.replace("%YEAR%", Integer.toString(year)).replace("%MONTH%",
                String.format("%02d", month));
        filename_csv = FILENAME_CSV.replace("%YEAR%", Integer.toString(year)).replace("%MONTH%",
                String.format("%02d", month));
        cacheThresholdMs = 23 * 60 * 60 * 1000; //23 hours
    } else {
        filename_zip = FILENAME_ZIP_CURRENT;
        filename_csv = FILENAME_CSV_CURRENT;
        cacheThresholdMs = 3 * 60 * 60 * 1000; //3 hours
    }

    final File cachedFile = new File(property + File.separator + ((zip) ? filename_zip : filename_csv));
    final File generatingFile = new File(
            property + File.separator + ((zip) ? filename_zip : filename_csv) + "_tmp");
    if (cachedFile.exists()) {

        //check if file has been recently created OR a file is currently being created
        if (((cachedFile.lastModified() + cacheThresholdMs) > (new Date()).getTime())
                || (generatingFile.exists()
                        && (generatingFile.lastModified() + cacheThresholdMs) > (new Date()).getTime())) {

            //if so, return the cached file instead of a cost-intensive new one
            final OutputRepresentation result = new OutputRepresentation(
                    zip ? MediaType.APPLICATION_ZIP : MediaType.TEXT_CSV) {

                @Override
                public void write(OutputStream out) throws IOException {
                    InputStream is = new FileInputStream(cachedFile);
                    IOUtils.copy(is, out);
                    out.close();
                }

            };
            if (zip) {
                final Disposition disposition = new Disposition(Disposition.TYPE_ATTACHMENT);
                disposition.setFilename(filename_zip);
                result.setDisposition(disposition);
            }
            return result;

        }
    }

    final String timeClause;

    if (dateExport)
        timeClause = " AND (EXTRACT (month FROM t.time AT TIME ZONE 'UTC') = " + month
                + ") AND (EXTRACT (year FROM t.time AT TIME ZONE 'UTC') = " + year + ") ";
    else if (hoursExport)
        timeClause = " AND time > now() - interval '" + hours + " hours' ";
    else
        timeClause = " AND time > current_date - interval '31 days' ";

    final String sql = "SELECT" + " ('P' || t.open_uuid) open_uuid,"
            + " ('O' || t.open_test_uuid) open_test_uuid,"
            + " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') time_utc,"
            + " nt.group_name cat_technology," + " nt.name network_type,"
            + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN"
            + " t.geo_lat" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_lat*1111)/1111" + " ELSE null"
            + " END) lat,"
            + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN"
            + " t.geo_long" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_long*741)/741 " + " ELSE null"
            + " END) long," + " (CASE WHEN ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN"
            + " 'rastered'" + //make raster transparent
            " ELSE t.geo_provider" + " END) loc_src,"
            + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') "
            + " THEN round(t.geo_accuracy::float * 10)/10 "
            + " WHEN (t.geo_accuracy < 100) AND ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN 100"
            + // limit accuracy to 100m
            " WHEN (t.geo_accuracy < ?) THEN round(t.geo_accuracy::float * 10)/10"
            + " ELSE null END) loc_accuracy, "
            + " (CASE WHEN (t.zip_code < 1000 OR t.zip_code > 9999) THEN null ELSE t.zip_code END) zip_code,"
            + " t.gkz gkz," + " t.country_location country_location," + " t.speed_download download_kbit,"
            + " t.speed_upload upload_kbit," + " round(t.ping_median::float / 100000)/10 ping_ms,"
            + " t.lte_rsrp," + " t.lte_rsrq," + " ts.name server_name," + " duration test_duration,"
            + " num_threads," + " t.plattform platform," + " COALESCE(adm.fullname, t.model) model,"
            + " client_software_version client_version," + " network_operator network_mcc_mnc,"
            + " network_operator_name network_name," + " network_sim_operator sim_mcc_mnc," + " nat_type,"
            + " public_ip_asn asn," + " client_public_ip_anonymized ip_anonym,"
            + " (ndt.s2cspd*1000)::int ndt_download_kbit," + " (ndt.c2sspd*1000)::int ndt_upload_kbit,"
            + " COALESCE(t.implausible, false) implausible," + " t.signal_strength" + " FROM test t"
            + " LEFT JOIN network_type nt ON nt.uid=t.network_type"
            + " LEFT JOIN device_map adm ON adm.codename=t.model"
            + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN test_ndt ndt ON t.uid=ndt.test_id"
            + " WHERE " + " t.deleted = false" + timeClause + " AND status = 'FINISHED'" + " ORDER BY t.uid";

    final String[] columns;
    final List<String[]> data = new ArrayList<>();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = conn.prepareStatement(sql);

        //insert filter for accuracy
        double accuracy = Double.parseDouble(settings.getString("RMBT_GEO_ACCURACY_DETAIL_LIMIT"));
        ps.setDouble(1, accuracy);
        ps.setDouble(2, accuracy);
        ps.setDouble(3, accuracy);
        ps.setDouble(4, accuracy);
        ps.setDouble(5, accuracy);
        ps.setDouble(6, accuracy);

        if (!ps.execute())
            return null;
        rs = ps.getResultSet();

        final ResultSetMetaData meta = rs.getMetaData();
        final int colCnt = meta.getColumnCount();
        columns = new String[colCnt];
        for (int i = 0; i < colCnt; i++)
            columns[i] = meta.getColumnName(i + 1);

        while (rs.next()) {
            final String[] line = new String[colCnt];

            for (int i = 0; i < colCnt; i++) {
                final Object obj = rs.getObject(i + 1);
                line[i] = obj == null ? null : obj.toString();
            }

            data.add(line);
        }
    } catch (final SQLException e) {
        e.printStackTrace();
        return null;
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    final OutputRepresentation result = new OutputRepresentation(
            zip ? MediaType.APPLICATION_ZIP : MediaType.TEXT_CSV) {
        @Override
        public void write(OutputStream out) throws IOException {
            //cache in file => create temporary temporary file (to 
            // handle errors while fulfilling a request)
            String property = System.getProperty("java.io.tmpdir");
            final File cachedFile = new File(
                    property + File.separator + ((zip) ? filename_zip : filename_csv) + "_tmp");
            OutputStream outf = new FileOutputStream(cachedFile);

            if (zip) {
                final ZipOutputStream zos = new ZipOutputStream(outf);
                final ZipEntry zeLicense = new ZipEntry("LIZENZ.txt");
                zos.putNextEntry(zeLicense);
                final InputStream licenseIS = getClass().getResourceAsStream("DATA_LICENSE.txt");
                IOUtils.copy(licenseIS, zos);
                licenseIS.close();

                final ZipEntry zeCsv = new ZipEntry(filename_csv);
                zos.putNextEntry(zeCsv);
                outf = zos;
            }

            final OutputStreamWriter osw = new OutputStreamWriter(outf);
            final CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);

            for (final String c : columns)
                csvPrinter.print(c);
            csvPrinter.println();

            for (final String[] line : data) {
                for (final String f : line)
                    csvPrinter.print(f);
                csvPrinter.println();
            }
            csvPrinter.flush();

            if (zip)
                outf.close();

            //if we reach this code, the data is now cached in a temporary tmp-file
            //so, rename the file for "production use2
            //concurrency issues should be solved by the operating system
            File newCacheFile = new File(property + File.separator + ((zip) ? filename_zip : filename_csv));
            Files.move(cachedFile.toPath(), newCacheFile.toPath(), StandardCopyOption.ATOMIC_MOVE,
                    StandardCopyOption.REPLACE_EXISTING);

            FileInputStream fis = new FileInputStream(newCacheFile);
            IOUtils.copy(fis, out);
            fis.close();
            out.close();
        }
    };
    if (zip) {
        final Disposition disposition = new Disposition(Disposition.TYPE_ATTACHMENT);
        disposition.setFilename(filename_zip);
        result.setDisposition(disposition);
    }

    return result;
}

From source file:csiro.pidsvc.mappingstore.ManagerJson.java

@SuppressWarnings("unchecked")
public JSONArray getSettings() throws SQLException {
    PreparedStatement pst = null;
    ResultSet rs = null;//from www . j  a va2  s . c  o m
    JSONArray ret = new JSONArray();

    try {
        pst = _connection.prepareStatement("SELECT * FROM configuration");
        if (pst.execute()) {
            for (rs = pst.getResultSet(); rs.next();)
                ret.add(JSONObjectHelper.create("name", rs.getString(1), "value", rs.getString(2)));
        }
    } finally {
        if (rs != null)
            rs.close();
        if (pst != null)
            pst.close();
    }
    return ret;
}

From source file:mysql5.MySQL5PlayerDAO.java

@Override
public void storeLastOnlineTime(final int objectId, final Timestamp lastOnline) {
    DB.insertUpdate("UPDATE players set last_online = ? where id = ?", new IUStH() {
        @Override/* w  w  w.ja v  a 2  s. c  om*/
        public void handleInsertUpdate(PreparedStatement preparedStatement) throws SQLException {
            preparedStatement.setTimestamp(1, lastOnline);
            preparedStatement.setInt(2, objectId);
            preparedStatement.execute();
        }
    });
}