Example usage for java.sql PreparedStatement executeBatch

Introduction

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

Prototype

int[] executeBatch() throws SQLException;

Source Link

Document

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Usage

From source file:org.plista.kornakapi.core.storage.MySqlStorage.java

@Override
public Iterable<String> batchDeleteCandidates(Iterator<Candidate> candidates, int batchSize)
        throws IOException {

    Set<String> modifiedLabels = Sets.newHashSet();

    Connection conn = null;//from   ww w  .j  av a2 s. co m
    PreparedStatement stmt = null;

    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(REMOVE_CANDIDATE_QUERY);

        int recordsQueued = 0;

        while (candidates.hasNext()) {

            Candidate candidate = candidates.next();

            modifiedLabels.add(candidate.getLabel());

            stmt.setString(1, candidate.getLabel());
            stmt.setLong(2, candidate.getItemID());
            stmt.addBatch();

            if (++recordsQueued % batchSize == 0) {
                stmt.executeBatch();
                log.info("deleted {} candidates in batch", recordsQueued);
            }
        }

        if (recordsQueued % batchSize != 0) {
            stmt.executeBatch();
            log.info("deleted {} candidates in batch. done.", recordsQueued);
        }

    } catch (SQLException e) {
        throw new IOException(e);
    } finally {
        IOUtils.quietClose(stmt);
        IOUtils.quietClose(conn);
    }

    return modifiedLabels;
}

---

From source file:com.iucosoft.eavertizare.dao.impl.ClientsDaoImpl.java

@Override
public void saveLocal(Firma firma, List<Client> clientsList) {

    String query = "INSERT INTO " + firma.getTabelaClientiLocal() + " VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
    Connection con = null;//from  www. ja  va  2  s .  c o  m
    PreparedStatement ps = null;
    try {
        con = dataSource.getConnection();
        ps = con.prepareStatement(query);
        // Set auto-commit to false
        con.setAutoCommit(false);

        for (Client client : clientsList) {
            ps.setInt(1, client.getId());
            ps.setString(2, client.getNume());
            ps.setString(3, client.getPrenume());
            ps.setInt(4, client.getNrTelefon());
            ps.setString(5, client.getEmail());
            ps.setTimestamp(6, (new java.sql.Timestamp(client.getDateExpirare().getTime())));
            ps.setInt(7, firma.getId());
            ps.setInt(8, 0);

            ps.addBatch();
        }

        // Create an int[] to hold returned values
        int[] count = ps.executeBatch();
        //Explicitly commit statements to apply changes
        con.commit();

    } catch (SQLException e) {
        e.printStackTrace();
        try {
            con.rollback();
        } catch (SQLException ex) {
            Logger.getLogger(ClientsDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    } finally {
        try {
            ps.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

From source file:HSqlManager.java

private static void checkPhage(Connection connection) throws SQLException, IOException {
    List<String[]> all = INSTANCE.readFileAllStrains(INSTANCE.path);
    List<String> clusters = all.stream().map(x -> x[0]).collect(Collectors.toList());
    Set<String> phages = all.stream().map(x -> x[1]).collect(Collectors.toSet());
    List<String> strains = all.stream().map(x -> x[2]).collect(Collectors.toList());
    List<String> phageslist = all.stream().map(x -> x[1]).collect(Collectors.toList());
    Set<String> dbphages = new HashSet<>();
    Statement st = connection.createStatement();
    PreparedStatement insertPhages = connection
            .prepareStatement("INSERT INTO Primerdb.Phages(Name, Cluster, Strain)" + " values(?,?,?);");
    String sql = "SELECT * FROM Primerdb.Phages;";
    ResultSet rs = st.executeQuery(sql);
    while (rs.next()) {
        dbphages.add(rs.getString("Name"));
    }/*from   w  ww. j ava 2  s.c  o m*/
    phages.removeAll(dbphages);
    List<String[]> phageinfo = new ArrayList<>();
    if (phages.size() > 0) {
        System.out.println("Phages Added:");
        phages.forEach(x -> {
            String[] ar = new String[3];
            System.out.println(x);
            String cluster = clusters.get(phageslist.indexOf(x));
            String strain = strains.get(phageslist.indexOf(x));
            try {
                insertPhages.setString(1, x);
                insertPhages.setString(2, cluster);
                insertPhages.setString(3, strain);
                insertPhages.addBatch();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                insertPhages.executeBatch();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ar[0] = x;
            ar[1] = cluster;
            ar[2] = strain;
            phageinfo.add(ar);
        });
        newPhages = phageinfo;
    } else {
        System.out.println("No Phages added");
    }
    st.close();
    insertPhages.close();
}

---

From source file:gov.nih.nci.caarray.dao.ArrayDaoImpl.java

/**
 * {@inheritDoc}//  www  . j  av  a2s  .co  m
 */
@Override
public void createDesignElementListEntries(DesignElementList designElementList, int startIndex,
        List<Long> logicalProbeIds) {
    final Connection conn = getCurrentSession().connection();
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement("insert into designelementlist_designelement "
                + "(designelementlist_id, designelement_id, designelement_index) values (?, ?, ?)");
        int i = startIndex;
        for (final Long probeId : logicalProbeIds) {
            stmt.setLong(1, designElementList.getId());
            stmt.setLong(2, probeId);
            stmt.setInt(3, i++);
            stmt.addBatch();
        }
        stmt.executeBatch();
    } catch (final SQLException e) {
        throw new DAOException("Error inserting elements in the design element list", e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (final SQLException e) { // NOPMD - close quietly
                // close quietly
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (final SQLException e) { // NOPMD - close quietly
                // close quietly
            }
        }
    }
}

---

From source file:coral.data.DataServiceJbdcImpl.java

private long put(long id, String collection, Map<String, String> map) throws SQLException {

    if (map.size() > 0) {
        PreparedStatement prep = conn.prepareStatement("insert into datas values (?, ?, ?, ?);");

        for (Map.Entry<String, String> e : map.entrySet()) {
            prep.setString(1, Long.toString(id));
            prep.setString(2, collection);
            prep.setString(3, e.getKey());
            String v = e.getValue();
            prep.setString(4, v.substring(0, (v.length() > 1000) ? 1000 : v.length()));
            prep.addBatch();//from  w ww  . j a  v  a2s  .  c  o  m
        }

        conn.setAutoCommit(false);
        prep.executeBatch();
        conn.setAutoCommit(true);
    }

    conn.commit();

    return id;
}

---

From source file:netflow.DatabaseProxy.java

public void doAggregation() {
    //todo: the same for doAggregation(Date)
    String sql = getQuery("aggregation.insert");
    String logStr = "doAggregation(): ";
    log.info(logStr + " <<<<");
    try {//from www.  j a  v  a 2 s  .c o  m
        List<Integer> clients = getNetworkedClients();
        PreparedStatement pst = con.prepareStatement(sql);

        for (Integer client : clients) {
            Collection<AggregationRecord> records = askForData(client);
            for (AggregationRecord record : records) {
                pst.setInt(1, record.getClientId());
                pst.setTimestamp(2, record.getStamp());
                pst.setLong(3, record.getInput());
                pst.setLong(4, record.getOutput());
                pst.addBatch();
            }
        }

        pst.executeBatch();
        pst.close();
    } catch (SQLException e) {
        log.error(logStr + " Aggregation error: " + e.getMessage());
        e.printStackTrace(System.err);
    }
    log.info(logStr + " >>>>");
}

---

From source file:net.mybox.mybox.ServerClientConnection.java

private void sendServerFileList() {

    System.out.println("getting local file list for: " + localDir);

    Statement statement = null;//  w  w  w  .ja va2s  . c  o  m

    // refresh the database
    try {
        statement = connection.createStatement();
        statement.setQueryTimeout(30);

        statement.executeUpdate(
                "create table if not exists archive (name text primary key, type char(1), lastupdate integer)");
    } catch (Exception e) {
        System.out.println("SQLite error: " + e.getMessage());
    }

    try {
        List<MyFile> files = Common.getFileListing(new File(localDir));

        JSONArray jsonArray = new JSONArray();

        PreparedStatement prep = connection.prepareStatement("insert or ignore into archive values(?,?,?);");

        for (MyFile thisFile : files) {
            System.out.println(" " + thisFile.name);

            // TODO: do not insert directories that are already in the DB
            //  compare against getFilesFromDB ?

            prep.setString(1, thisFile.name);
            prep.setString(2, thisFile.getTypeChar() + "");
            prep.setLong(3, thisFile.modtime);
            prep.addBatch();

            jsonArray.add(thisFile.serialize());
        }

        prep.executeBatch();
        connection.commit();

        sendCommandToClient(Common.Signal.requestServerFileList_response);
        ByteStream.toStream(outStream, jsonArray.toJSONString());

        System.out.println("local file list: " + jsonArray.size() + " files");

    } catch (Exception e) {
        System.out.println("Error when getting local file list " + e.getMessage());
    }

}

---

From source file:org.bidtime.dbutils.QueryRunnerEx.java

/**
 * Calls update after checking the parameters to ensure nothing is null.
 * @param conn The connection to use for the batch call.
 * @param closeConn True if the connection should be closed, false otherwise.
 * @param sql The SQL statement to execute.
 * @param params An array of query replacement parameters.  Each row in
 * this array is one set of batch replacement values.
 * @return The number of rows updated in the batch.
 * @throws SQLException If there are database or parameter errors.
 *//* ww  w  .  j a v  a 2s .  com*/
private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
    if (conn == null) {
        throw new SQLException("Null connection");
    }

    if (sql == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null SQL statement");
    }

    if (params == null) {
        if (closeConn) {
            close(conn);
        }
        throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
    }

    long startTime = System.currentTimeMillis();
    PreparedStatement stmt = null;
    int[] rows = null;
    try {
        stmt = this.prepareStatement(conn, sql);
        stmt.setQueryTimeout(StmtParams.getInstance().getStmtBatchTimeOut());
        for (int i = 0; i < params.length; i++) {
            this.fillStatement(stmt, params[i]);
            stmt.addBatch();
        }
        rows = stmt.executeBatch();
    } catch (SQLException e) {
        this.rethrow(e, sql, (Object[]) params);
    } finally {
        close(stmt);
        if (closeConn) {
            close(conn);
        }
        if (LogUpdateSql.logInfoOrDebug()) {
            LogUpdateSql.logFormatTimeNow(startTime, sql, params, (rows != null ? rows.length : 0));
        }
    }
    return rows;
}

---

From source file:org.wso2.carbon.device.mgt.extensions.device.type.template.dao.PropertyBasedPluginDAOImpl.java

public boolean updateDevice(Device device) throws DeviceTypeMgtPluginException {
    Connection conn;/*from   www .j  a va  2 s  . c  o  m*/
    PreparedStatement stmt = null;
    try {
        conn = deviceTypeDAOHandler.getConnection();
        stmt = conn.prepareStatement(
                "UPDATE DM_DEVICE_PROPERTIES SET PROPERTY_VALUE = ? WHERE  DEVICE_TYPE_NAME = ? AND "
                        + "DEVICE_IDENTIFICATION = ? AND PROPERTY_NAME = ? AND TENANT_ID= ?");

        for (Device.Property property : device.getProperties()) {
            if (!deviceProps.contains(property.getName())) {
                continue;
            }
            stmt.setString(1, property.getValue());
            stmt.setString(2, deviceType);
            stmt.setString(3, device.getDeviceIdentifier());
            stmt.setString(4, property.getName());
            stmt.setInt(5, PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId(true));
            stmt.addBatch();
        }
        stmt.executeBatch();
        return true;
    } catch (SQLException e) {
        String msg = "Error occurred while modifying the device '" + device.getDeviceIdentifier() + "' data on"
                + deviceType;
        log.error(msg, e);
        throw new DeviceTypeMgtPluginException(msg, e);
    } finally {
        DeviceTypeUtils.cleanupResources(stmt, null);
    }
}

---

From source file:com.pactera.edg.am.metamanager.extractor.dao.helper.CreateDependencyHelper.java

private void myDoInPreparedStatement(long sysTime, MMDDependency dependency, PreparedStatement ps)
        throws SQLException {

    String ownerModelId = dependency.getOwnerMetadata().getClassifierId();
    String valueModelId = dependency.getValueMetadata().getClassifierId();

    try {//from  w w w . j  a v  a 2 s .co m
        // ?ID
        ps.setString(1, dependency.getOwnerMetadata().getId());
        // ?ID
        ps.setString(2, ownerModelId);
        // ?ID
        ps.setString(3, dependency.getValueMetadata().getId());
        // ?ID
        ps.setString(4, valueModelId);
        // ???
        ps.setString(5, dependency.getCode());
        // 
        ps.setLong(6, sysTime);
        ps.setString(7, "1");
        ps.setString(8, "1");

        setPs(ps, 8);

        ps.addBatch();
        // ??
        ps.clearParameters();
        if (++super.count % super.batchSize == 0) {
            ps.executeBatch();
            ps.clearBatch();
        }
    } catch (SQLException e) {
        String logMsg = new StringBuilder().append("?,?:??ID:")
                .append(dependency.getOwnerMetadata().getId()).append(",?:")
                .append(ownerModelId).append(",??ID:")
                .append(dependency.getValueMetadata().getId()).append(",?:")
                .append(valueModelId).append(", ???:").append(dependency.getCode()).toString();
        log.error(logMsg);
        AdapterExtractorContext.addExtractorLog(ExtractorLogLevel.ERROR, logMsg);
        throw e;
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »