Example usage for java.sql PreparedStatement executeBatch

List of usage examples for java.sql PreparedStatement executeBatch

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. 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:com.flexive.ejb.beans.PhraseEngineBean.java

/**
 * {@inheritDoc}//from   w  ww.  j av a 2s .c  o m
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void moveTreeNodeAssignment(int category, long assignmentOwner, long nodeId, long nodeMandatorId,
        long phraseId, long phraseMandator, int delta) throws FxNotFoundException, FxNoAccessException {
    if (delta == 0)
        return;
    checkMandatorAccess(assignmentOwner, FxContext.getUserTicket());
    Connection con = null;
    PreparedStatement ps = null;
    try {
        // Obtain a database connection
        con = Database.getDbConnection();

        List<Long> positionsId = Lists.newArrayListWithCapacity(50);
        List<Long> positionsMandator = Lists.newArrayListWithCapacity(50);

        //0..phrase id, 1..phrase mandator,2..pos
        ps = con.prepareStatement("SELECT PHRASEID,PMANDATOR,POS FROM " + TBL_PHRASE_MAP
                + " WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND CAT=? ORDER BY POS");
        ps.setLong(1, assignmentOwner);
        ps.setLong(2, nodeId);
        ps.setLong(3, nodeMandatorId);
        ps.setInt(4, category);
        ResultSet rs = ps.executeQuery();
        long currPos = 1;
        int index = -1;
        while (rs != null && rs.next()) {
            if (index == -1 && phraseId == rs.getLong(1))
                index = (int) currPos - 1;
            positionsId.add(rs.getLong(1));
            positionsMandator.add(rs.getLong(2));
            currPos++;
        }
        if (positionsId.size() < 2 || index == -1) //only one node or node not found, can not change position
            return;
        int newIndex = index + delta;
        if (newIndex < 0)
            newIndex = 0;
        if (delta > 0)
            newIndex++;
        if (newIndex > (positionsId.size() - 1))
            newIndex = positionsId.size();
        positionsId.add(newIndex, phraseId);
        positionsMandator.add(newIndex, phraseMandator);
        if (newIndex > index) {
            positionsId.remove(index);
            positionsMandator.remove(index);
        } else {
            positionsId.remove(index + 1);
            positionsMandator.remove(index + 1);
        }
        //write back new positionsId
        ps.close();
        ps = con.prepareStatement("UPDATE " + TBL_PHRASE_MAP
                + " SET POS=? WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND PHRASEID=? AND PMANDATOR=? AND CAT=?");
        ps.setLong(2, assignmentOwner);
        ps.setLong(3, nodeId);
        ps.setLong(4, nodeMandatorId);
        ps.setInt(7, category);
        for (int i = 1; i <= positionsId.size(); i++) {
            ps.setLong(1, i);
            ps.setLong(5, positionsId.get(i - 1));
            ps.setLong(6, positionsMandator.get(i - 1));
            ps.addBatch();
        }
        ps.executeBatch();
    } catch (SQLException exc) {
        EJBUtils.rollback(ctx);
        throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException();
    } finally {
        Database.closeObjects(PhraseEngineBean.class, con, ps);
    }
}

From source file:i5.las2peer.services.mobsos.SurveyService.java

@POST
@Consumes(MediaType.APPLICATION_JSON)/*w  w w.ja  va 2s . co m*/
@Path("surveys/{id}/responses")
@Summary("submit response data to given survey.")
@ApiResponses(value = { @ApiResponse(code = 200, message = "Survey response submitted successfully."),
        @ApiResponse(code = 400, message = "Survey response invalid -or- questionnaire form invalid. Cause: ..."),
        @ApiResponse(code = 404, message = "Survey does not exist -or- No questionnaire defined for survey."),
        @ApiResponse(code = 400, message = "Survey response already submitted."), })
public HttpResponse submitSurveyResponseJSON(@PathParam("id") int id, @ContentParam String answerJSON) {
    Date now = new Date();
    String onAction = "submitting response to survey " + id;
    try {

        // retrieve survey by id;
        HttpResponse rs = getSurvey(id);
        if (rs.getStatus() != 200) {
            return rs;
        }

        JSONObject s = (JSONObject) JSONValue.parse(rs.getResult());

        // check if survey expired/not started
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
        df.setTimeZone(TimeZone.getTimeZone("GMT"));

        Date start = df.parse((String) s.get("start"));
        Date end = df.parse((String) s.get("end"));

        if (now.getTime() > end.getTime()) {
            HttpResponse resp = new HttpResponse("Cannot submit response. Survey expired.");
            resp.setStatus(403);
            return resp;
        } else if (now.getTime() < start.getTime()) {
            HttpResponse resp = new HttpResponse("Cannot submit response. Survey has not begun, yet.");
            resp.setStatus(403);
            return resp;
        }

        // check for questionnaire form
        int qid = Integer.parseInt(s.get("qid") + "");

        if (qid == -1) {
            HttpResponse result = new HttpResponse("No questionnaire defined for survey " + id + "!");
            result.setStatus(404);
            return result;
        }

        // retrieve questionnaire form for survey to do answer validation
        HttpResponse r = downloadQuestionnaireForm(qid);

        if (200 != r.getStatus()) {
            // if questionnaire form does not exist, pass on response containing error status
            return r;
        }

        Document form;
        JSONObject answer;

        // parse form to XML document incl. validation
        try {
            form = validateQuestionnaireData(r.getResult());
        } catch (SAXException e) {
            HttpResponse result = new HttpResponse("Questionnaire form is invalid! Cause: " + e.getMessage());
            result.setStatus(400);
            return result;
        }

        try {
            //System.out.println(answerJSON);

            answer = (JSONObject) JSONValue.parseWithException(answerJSON);
        } catch (ParseException e) {
            HttpResponse result = new HttpResponse(
                    "Survey response is not valid JSON! Cause: " + e.getMessage());
            result.setStatus(400);
            return result;
        }

        JSONObject answerFieldTable;

        // validate if answer matches form.
        try {
            answerFieldTable = validateResponse(form, answer);
        } catch (IllegalArgumentException e) {
            HttpResponse result = new HttpResponse("Survey response is invalid! Cause: " + e.getMessage());
            result.setStatus(400);
            return result;
        }

        // after all validation finally persist survey response in database
        int surveyId = id;

        String sub = (String) getActiveUserInfo().get("sub");

        if (getActiveAgent().getId() == getActiveNode().getAnonymous().getId()) {
            sub += now.getTime();
        }

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rset = null;

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(
                    "insert into " + jdbcSchema + ".response(uid,sid,qkey,qval,time) values (?,?,?,?,?)");

            Iterator<String> it = answerFieldTable.keySet().iterator();
            while (it.hasNext()) {

                String qkey = it.next();
                String qval = "" + answerFieldTable.get(qkey);

                stmt.setString(1, sub);
                stmt.setInt(2, surveyId);
                stmt.setString(3, qkey);
                stmt.setString(4, qval);
                stmt.setTimestamp(5, new Timestamp(now.getTime()));
                stmt.addBatch();

            }
            stmt.executeBatch();

            HttpResponse result = new HttpResponse("Response to survey " + id + " submitted successfully.");
            result.setStatus(200);
            return result;

        } catch (SQLException | UnsupportedOperationException e) {
            if (0 <= e.getMessage().indexOf("Duplicate")) {
                HttpResponse result = new HttpResponse("Survey response already submitted!");
                result.setStatus(409);
                return result;
            } else {
                e.printStackTrace();
                return internalError(onAction);
            }
        } finally {
            try {
                if (rset != null)
                    rset.close();
            } catch (Exception e) {
                e.printStackTrace();
                return internalError(onAction);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
                return internalError(onAction);
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
                return internalError(onAction);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        return internalError(onAction);
    }
}

From source file:org.pentaho.di.core.database.Database.java

/**
 * Close the prepared statement of the insert statement.
 *
 * @param ps/* w  w w  .  j  a  va 2 s .  c o m*/
 *          The prepared statement to empty and close.
 * @param batch
 *          true if you are using batch processing (typically true for this method)
 * @param psBatchCounter
 *          The number of rows on the batch queue
 * @throws KettleDatabaseException
 *
 * @deprecated use emptyAndCommit() instead (pass in the number of rows left in the batch)
 */
@Deprecated
public void insertFinished(PreparedStatement ps, boolean batch) throws KettleDatabaseException {
    boolean isBatchUpdate = false;
    try {
        if (ps != null) {
            if (!isAutoCommit()) {
                // Execute the batch or just perform a commit.
                if (batch && getDatabaseMetaData().supportsBatchUpdates()) {
                    // The problem with the batch counters is that you can't just
                    // execute the current batch.
                    // Certain databases have a problem if you execute the batch and if
                    // there are no statements in it.
                    // You can't just catch the exception either because you would have
                    // to roll back on certain databases before you can then continue to
                    // do anything.
                    // That leaves the task of keeping track of the number of rows up to
                    // our responsibility.
                    isBatchUpdate = true;
                    ps.executeBatch();
                    commit();
                } else {
                    commit();
                }
            }

            // Let's not forget to close the prepared statement.
            //
            ps.close();
        }
    } catch (BatchUpdateException ex) {
        throw createKettleDatabaseBatchException("Error updating batch", ex);
    } catch (SQLException ex) {
        if (isBatchUpdate) {
            throw createKettleDatabaseBatchException("Error updating batch", ex);
        } else {
            throw new KettleDatabaseException("Unable to commit connection after having inserted rows.", ex);
        }
    }
}

From source file:org.pentaho.di.core.database.Database.java

/**
 * Close the prepared statement of the insert statement.
 *
 * @param ps//from   w ww.  j a va2 s.co m
 *          The prepared statement to empty and close.
 * @param batch
 *          true if you are using batch processing
 * @param psBatchCounter
 *          The number of rows on the batch queue
 * @throws KettleDatabaseException
 */
public void emptyAndCommit(PreparedStatement ps, boolean batch, int batchCounter)
        throws KettleDatabaseException {
    boolean isBatchUpdate = false;
    try {
        if (ps != null) {
            if (!isAutoCommit()) {
                // Execute the batch or just perform a commit.
                if (batch && getDatabaseMetaData().supportsBatchUpdates() && batchCounter > 0) {
                    // The problem with the batch counters is that you can't just
                    // execute the current batch.
                    // Certain databases have a problem if you execute the batch and if
                    // there are no statements in it.
                    // You can't just catch the exception either because you would have
                    // to roll back on certain databases before you can then continue to
                    // do anything.
                    // That leaves the task of keeping track of the number of rows up to
                    // our responsibility.
                    isBatchUpdate = true;
                    ps.executeBatch();
                    commit();
                    ps.clearBatch();
                } else {
                    commit();
                }
            }

            // Let's not forget to close the prepared statement.
            //
            ps.close();
        }
    } catch (BatchUpdateException ex) {
        throw createKettleDatabaseBatchException("Error updating batch", ex);
    } catch (SQLException ex) {
        if (isBatchUpdate) {
            throw createKettleDatabaseBatchException("Error updating batch", ex);
        } else {
            throw new KettleDatabaseException("Unable to empty ps and commit connection.", ex);
        }
    }
}

From source file:org.apache.tajo.catalog.store.AbstractDBStore.java

@Override
public void addPartitions(String databaseName, String tableName,
        List<CatalogProtos.PartitionDescProto> partitions, boolean ifNotExists)
        throws UndefinedDatabaseException, UndefinedTableException, UndefinedPartitionMethodException {

    final int databaseId = getDatabaseId(databaseName);
    final int tableId = getTableId(databaseId, databaseName, tableName);
    ensurePartitionTable(tableName, tableId);

    Connection conn = null;//from   ww  w  .  j  av a 2s.  c  o m
    // To delete existing partition keys
    PreparedStatement pstmt1 = null;
    // To delete existing partition;
    PreparedStatement pstmt2 = null;
    // To insert a partition
    PreparedStatement pstmt3 = null;
    // To insert partition keys
    PreparedStatement pstmt4 = null;

    PartitionDescProto partitionDesc = null;

    try {
        conn = getConnection();
        conn.setAutoCommit(false);

        int currentIndex = 0, lastIndex = 0;

        pstmt1 = conn.prepareStatement(deletePartitionKeysSql);
        pstmt2 = conn.prepareStatement(deletePartitionSql);
        pstmt3 = conn.prepareStatement(insertPartitionSql);
        pstmt4 = conn.prepareStatement(insertPartitionKeysSql);

        // Set a batch size like 1000. This avoids SQL injection and also takes care of out of memory issue.
        int batchSize = conf.getInt(TajoConf.ConfVars.PARTITION_DYNAMIC_BULK_INSERT_BATCH_SIZE.varname, 1000);
        for (currentIndex = 0; currentIndex < partitions.size(); currentIndex++) {
            PartitionDescProto partition = partitions.get(currentIndex);

            try {
                partitionDesc = getPartition(databaseName, tableName, partition.getPartitionName());
                // Delete existing partition and partition keys
                if (ifNotExists) {
                    pstmt1.setInt(1, partitionDesc.getId());
                    pstmt1.addBatch();
                    pstmt1.clearParameters();

                    pstmt2.setInt(1, partitionDesc.getId());
                    pstmt2.addBatch();
                    pstmt2.clearParameters();
                }
            } catch (UndefinedPartitionException e) {
            }

            // Insert partition
            pstmt3.setInt(1, tableId);
            pstmt3.setString(2, partition.getPartitionName());
            pstmt3.setString(3, partition.getPath());
            pstmt3.setLong(4, partition.getNumBytes());
            pstmt3.addBatch();
            pstmt3.clearParameters();

            // Insert partition keys
            for (int i = 0; i < partition.getPartitionKeysCount(); i++) {
                PartitionKeyProto partitionKey = partition.getPartitionKeys(i);
                pstmt4.setInt(1, tableId);
                pstmt4.setString(2, partition.getPartitionName());
                pstmt4.setInt(3, tableId);
                pstmt4.setString(4, partitionKey.getColumnName());
                pstmt4.setString(5, partitionKey.getPartitionValue());

                pstmt4.addBatch();
                pstmt4.clearParameters();
            }

            // Execute batch
            if (currentIndex >= lastIndex + batchSize && lastIndex != currentIndex) {
                pstmt1.executeBatch();
                pstmt1.clearBatch();
                pstmt2.executeBatch();
                pstmt2.clearBatch();
                pstmt3.executeBatch();
                pstmt3.clearBatch();
                pstmt4.executeBatch();
                pstmt4.clearBatch();
                lastIndex = currentIndex;
            }
        }

        // Execute existing batch queries
        if (lastIndex != currentIndex) {
            pstmt1.executeBatch();
            pstmt2.executeBatch();
            pstmt3.executeBatch();
            pstmt4.executeBatch();
        }

        if (conn != null) {
            conn.commit();
        }
    } catch (SQLException se) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                LOG.error(e, e);
            }
        }
        throw new TajoInternalError(se);
    } finally {
        CatalogUtil.closeQuietly(pstmt1);
        CatalogUtil.closeQuietly(pstmt2);
        CatalogUtil.closeQuietly(pstmt3);
        CatalogUtil.closeQuietly(pstmt4);
    }
}

From source file:edu.umass.cs.gigapaxos.SQLPaxosLogger.java

private/* synchronized */Set<String> pauseLogIndex(Map<String, LogIndex> toCommit) {
    if (isClosed())
        return null;
    if (!USE_CHECKPOINTS_AS_PAUSE_TABLE)
        return this.pauseLogIndexIndividually(toCommit);
    String updateCmd = "update " + (USE_CHECKPOINTS_AS_PAUSE_TABLE ? getCTable() : getPTable())
            + " set logindex=? where  paxos_id=?";

    PreparedStatement pstmt = null;
    Connection conn = null;/* w ww  . j a  va  2  s  .  c o m*/
    Set<String> paused = new HashSet<String>();
    Set<String> batch = new HashSet<String>();
    synchronized (this.messageLog) {
        try {
            int i = 0;
            for (String paxosID : toCommit.keySet()) {
                LogIndex logIndex = toCommit.get(paxosID);
                if (conn == null) {
                    conn = this.getDefaultConn();
                    conn.setAutoCommit(false);
                    pstmt = conn.prepareStatement(updateCmd);
                }

                byte[] logIndexBytes = logIndex != null ? deflate(logIndex.toString().getBytes(CHARSET)) : null;
                if (logIndexBytes != null && ENABLE_INSTRUMENTATION && Util.oneIn(Integer.MAX_VALUE))
                    DelayProfiler.updateMovAvg("logindex_size", logIndexBytes.length);
                Blob blob = conn.createBlob();
                if (logIndexBytes != null)
                    blob.setBytes(1, logIndexBytes);
                pstmt.setBlob(1, logIndexBytes != null ? blob : null);
                pstmt.setString(2, paxosID);
                pstmt.addBatch();
                batch.add(paxosID);
                if ((i + 1) % MAX_DB_BATCH_SIZE == 0 || (i + 1) == toCommit.size()) {
                    pstmt.executeBatch();
                    conn.commit();
                    pstmt.clearBatch();
                    paused.addAll(batch);

                    log.log(Level.FINE, "{0} paused logIndex batch {1}",
                            new Object[] { this, Util.truncatedLog(batch, 16) });
                    batch.clear();
                }
                i++;
            }
        } catch (SQLException | IOException sqle) {
            log.severe(this + " failed to pause logIndex batch");
            sqle.printStackTrace();
        } finally {
            cleanup(pstmt);
            cleanup(conn);
        }
        // free up memory
        for (String paxosID : paused)
            this.messageLog.uncache(paxosID);
    }
    if (paused.size() != toCommit.size())
        paused.addAll(this.pauseLogIndexIndividually(diffLI(toCommit, paused)));
    return paused;
}

From source file:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java

/**
 * @param applicationId/*from   w w w . j  a v  a 2s.com*/
 * @param inBoundAuthenticationConfig
 * @param connection
 * @throws SQLException
 */
private void updateInboundAuthRequestConfiguration(int applicationId,
        InboundAuthenticationConfig inBoundAuthenticationConfig, Connection connection) throws SQLException {
    int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId();

    PreparedStatement inboundAuthReqConfigPrepStmt = null;

    try {
        if (inBoundAuthenticationConfig == null
                || inBoundAuthenticationConfig.getInboundAuthenticationRequestConfigs() == null
                || inBoundAuthenticationConfig.getInboundAuthenticationRequestConfigs().length == 0) {
            // no in-bound authentication requests defined.
            return;
        }

        inboundAuthReqConfigPrepStmt = connection.prepareStatement(ApplicationMgtDBQueries.STORE_CLIENT_INFO);
        InboundAuthenticationRequestConfig[] authRequests = inBoundAuthenticationConfig
                .getInboundAuthenticationRequestConfigs();

        for (InboundAuthenticationRequestConfig authRequest : authRequests) {
            if (authRequest == null || authRequest.getInboundAuthKey() == null
                    || authRequest.getInboundAuthType() == null) {
                log.warn("Invalid in-bound authentication request");
                // not a valid authentication request. Must have client and a type.
                continue;
            }
            // TENANT_ID, INBOUND_AUTH_KEY,INBOUND_AUTH_TYPE,PROP_NAME, PROP_VALUE, APP_ID

            Property[] properties = authRequest.getProperties();

            if (properties != null && properties.length > 0) {
                for (Property prop : properties) {
                    inboundAuthReqConfigPrepStmt.setInt(1, tenantID);
                    inboundAuthReqConfigPrepStmt.setString(2,
                            CharacterEncoder.getSafeText(authRequest.getInboundAuthKey()));
                    inboundAuthReqConfigPrepStmt.setString(3,
                            CharacterEncoder.getSafeText(authRequest.getInboundAuthType()));
                    inboundAuthReqConfigPrepStmt.setString(4, CharacterEncoder.getSafeText(prop.getName()));
                    inboundAuthReqConfigPrepStmt.setString(5, CharacterEncoder.getSafeText(prop.getValue()));
                    inboundAuthReqConfigPrepStmt.setInt(6, applicationId);
                    inboundAuthReqConfigPrepStmt.addBatch();
                }
            } else {
                inboundAuthReqConfigPrepStmt.setInt(1, tenantID);
                inboundAuthReqConfigPrepStmt.setString(2,
                        CharacterEncoder.getSafeText(authRequest.getInboundAuthKey()));
                inboundAuthReqConfigPrepStmt.setString(3,
                        CharacterEncoder.getSafeText(authRequest.getInboundAuthType()));
                inboundAuthReqConfigPrepStmt.setString(4, null);
                inboundAuthReqConfigPrepStmt.setString(5, null);
                inboundAuthReqConfigPrepStmt.setInt(6, applicationId);
                inboundAuthReqConfigPrepStmt.addBatch();
            }

            if (debugMode) {
                log.debug("Updating inbound authentication request configuration of the application "
                        + applicationId + "inbound auth key: " + authRequest.getInboundAuthKey()
                        + " inbound auth type: " + authRequest.getInboundAuthType());
            }
        }

        inboundAuthReqConfigPrepStmt.executeBatch();
    } finally {
        IdentityApplicationManagementUtil.closeStatement(inboundAuthReqConfigPrepStmt);
    }
}

From source file:org.jamwiki.db.AnsiQueryHandler.java

/**
 *
 *///from w w  w  .j  av a2  s .c  om
public void insertTopicVersions(List<TopicVersion> topicVersions, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    boolean useBatch = (topicVersions.size() > 1);
    try {
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION);
        } else if (useBatch) {
            // generated keys don't work in batch mode
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT);
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        int topicVersionId = -1;
        if (!this.autoIncrementPrimaryKeys() || useBatch) {
            // manually retrieve next topic version id when using batch
            // mode or when the database doesn't support generated keys.
            topicVersionId = this.nextTopicVersionId(conn);
        }
        for (TopicVersion topicVersion : topicVersions) {
            if (!this.autoIncrementPrimaryKeys() || useBatch) {
                // FIXME - if two threads update the database simultaneously then
                // it is possible that this code could set the topic version ID
                // to a value that is different from what the database ends up
                // using.
                topicVersion.setTopicVersionId(topicVersionId++);
            }
            this.prepareTopicVersionStatement(topicVersion, stmt);
            if (useBatch) {
                stmt.addBatch();
            } else {
                stmt.executeUpdate();
            }
            if (this.autoIncrementPrimaryKeys() && !useBatch) {
                rs = stmt.getGeneratedKeys();
                if (!rs.next()) {
                    throw new SQLException("Unable to determine auto-generated ID for database record");
                }
                topicVersion.setTopicVersionId(rs.getInt(1));
            }
        }
        if (useBatch) {
            stmt.executeBatch();
        }
    } finally {
        // close only the statement and result set - leave the connection open for further use
        DatabaseConnection.closeConnection(null, stmt, rs);
        stmt = null;
        rs = null;
    }
}

From source file:org.apache.tajo.catalog.store.AbstractDBStore.java

@Override
public void createTable(final CatalogProtos.TableDescProto table)
        throws UndefinedDatabaseException, DuplicateTableException {

    Connection conn = null;/*w ww .  j a  v  a  2s  .c  o m*/
    PreparedStatement pstmt = null;
    ResultSet res = null;

    final String[] splitted = IdentifierUtil.splitTableName(table.getTableName());
    if (splitted.length == 1) {
        throw new TajoInternalError(
                "createTable() requires a qualified table name, but it is '" + table.getTableName() + "'");
    }
    final String databaseName = splitted[0];
    final String tableName = splitted[1];

    if (existTable(databaseName, tableName)) {
        throw new DuplicateTableException(tableName);
    }
    final int dbid = getDatabaseId(databaseName);

    try {
        conn = getConnection();
        conn.setAutoCommit(false);

        String sql = "INSERT INTO TABLES (DB_ID, " + COL_TABLES_NAME
                + ", TABLE_TYPE, PATH, DATA_FORMAT, HAS_SELF_DESCRIBE_SCHEMA) VALUES(?, ?, ?, ?, ?, ?) ";

        if (LOG.isDebugEnabled()) {
            LOG.debug(sql);
        }

        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, dbid);
        pstmt.setString(2, tableName);
        if (table.getIsExternal()) {
            pstmt.setString(3, TableType.EXTERNAL.name());
        } else {
            pstmt.setString(3, TableType.MANAGED.name());
        }
        pstmt.setString(4, table.getPath());
        pstmt.setString(5, table.getMeta().getDataFormat());
        pstmt.setBoolean(6, table.getSchema() == null);
        pstmt.executeUpdate();
        pstmt.close();

        String tidSql = "SELECT TID from " + TB_TABLES + " WHERE " + COL_DATABASES_PK + "=? AND "
                + COL_TABLES_NAME + "=?";
        pstmt = conn.prepareStatement(tidSql);
        pstmt.setInt(1, dbid);
        pstmt.setString(2, tableName);
        res = pstmt.executeQuery();

        if (!res.next()) {
            throw new TajoInternalError("There is no TID matched to '" + table.getTableName() + '"');
        }

        int tableId = res.getInt("TID");
        res.close();
        pstmt.close();

        String colSql = "INSERT INTO " + TB_COLUMNS +
        // 1    2            3                 4
                " (TID, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE)" + " VALUES(?, ?, ?, ?) ";

        if (LOG.isDebugEnabled()) {
            LOG.debug(colSql);
        }

        pstmt = conn.prepareStatement(colSql);
        for (int i = 0; i < table.getSchema().getFieldsCount(); i++) {
            ColumnProto col = table.getSchema().getFields(i);
            org.apache.tajo.type.Type type = TypeProtobufEncoder.decode(col.getType());

            pstmt.setInt(1, tableId);
            pstmt.setString(2, extractSimpleName(col.getName()));
            pstmt.setInt(3, i);
            pstmt.setString(4, TypeStringEncoder.encode(type));
            pstmt.addBatch();
            pstmt.clearParameters();
        }
        pstmt.executeBatch();
        pstmt.close();

        if (table.getMeta().hasParams()) {
            String propSQL = "INSERT INTO " + TB_OPTIONS + "(TID, KEY_, VALUE_) VALUES(?, ?, ?)";

            if (LOG.isDebugEnabled()) {
                LOG.debug(propSQL);
            }

            pstmt = conn.prepareStatement(propSQL);
            for (KeyValueProto entry : table.getMeta().getParams().getKeyvalList()) {
                pstmt.setInt(1, tableId);
                pstmt.setString(2, entry.getKey());
                pstmt.setString(3, entry.getValue());
                pstmt.addBatch();
                pstmt.clearParameters();
            }
            pstmt.executeBatch();
            pstmt.close();
        }

        if (table.hasStats()) {

            String statSql = "INSERT INTO " + TB_STATISTICS + " (TID, NUM_ROWS, NUM_BYTES) VALUES(?, ?, ?)";

            if (LOG.isDebugEnabled()) {
                LOG.debug(statSql);
            }

            pstmt = conn.prepareStatement(statSql);
            pstmt.setInt(1, tableId);
            pstmt.setLong(2, table.getStats().getNumRows());
            pstmt.setLong(3, table.getStats().getNumBytes());
            pstmt.executeUpdate();
            pstmt.close();
        }

        if (table.hasPartition()) {
            String partSql = "INSERT INTO PARTITION_METHODS (TID, PARTITION_TYPE, EXPRESSION, EXPRESSION_SCHEMA) VALUES(?, ?, ?, ?)";

            if (LOG.isDebugEnabled()) {
                LOG.debug(partSql);
            }

            pstmt = conn.prepareStatement(partSql);
            pstmt.setInt(1, tableId);
            pstmt.setString(2, table.getPartition().getPartitionType().name());
            pstmt.setString(3, table.getPartition().getExpression());
            pstmt.setBytes(4, table.getPartition().getExpressionSchema().toByteArray());
            pstmt.executeUpdate();
        }

        // If there is no error, commit the changes.
        conn.commit();
    } catch (SQLException se) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                LOG.error(e, e);
            }
        }
        throw new TajoInternalError(se);
    } finally {
        CatalogUtil.closeQuietly(pstmt, res);
    }
}

From source file:com.flexive.ejb.beans.PhraseEngineBean.java

/**
 * {@inheritDoc}/*from   w  ww  . j  a v  a 2 s . co m*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void moveTreeNode(int category, long nodeId, long mandatorId, int delta)
        throws FxNoAccessException, FxNotFoundException {
    if (delta == 0)
        return;
    checkMandatorAccess(mandatorId, FxContext.getUserTicket());
    Connection con = null;
    PreparedStatement ps = null;
    try {
        // Obtain a database connection
        con = Database.getDbConnection();
        ps = con.prepareStatement("SELECT PARENTID, PARENTMANDATOR FROM " + TBL_PHRASE_TREE
                + " WHERE ID=? AND MANDATOR=? AND CAT=?");
        ps.setLong(1, nodeId);
        ps.setLong(2, mandatorId);
        ps.setInt(3, category);
        ResultSet rs = ps.executeQuery();
        if (rs == null || !rs.next())
            throw new FxNotFoundException("ex.phrases.node.notFound.id", nodeId, mandatorId);
        long parentId = rs.getLong(1);
        if (rs.wasNull())
            parentId = -1L;
        long parentMandatorId = rs.getLong(2);
        if (rs.wasNull())
            parentMandatorId = -1L;
        rs.close();
        ps.close();
        //0..node id, 1..pos
        List<Long> positions = Lists.newArrayListWithCapacity(10);

        if (parentId == -1L) {
            ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE_TREE
                    + " WHERE PARENTID IS NULL AND MANDATOR=? AND CAT=? ORDER BY POS");
            ps.setLong(1, mandatorId);
            ps.setInt(2, category);
        } else {
            ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE_TREE
                    + " WHERE PARENTID=? AND PARENTMANDATOR=? AND MANDATOR=? AND CAT=? ORDER BY POS");
            ps.setLong(1, parentId);
            ps.setLong(2, parentMandatorId);
            ps.setLong(3, mandatorId);
            ps.setInt(4, category);
        }
        rs = ps.executeQuery();
        long currPos = 1;
        int index = -1;
        while (rs != null && rs.next()) {
            if (index == -1 && nodeId == rs.getLong(1))
                index = (int) currPos - 1;
            positions.add(rs.getLong(1));
            currPos++;
        }
        if (positions.size() < 2) //only one node, can not change position
            return;
        int newIndex = index + delta;
        if (newIndex < 0)
            newIndex = 0;
        if (delta > 0)
            newIndex++;
        if (newIndex > (positions.size() - 1))
            newIndex = positions.size();
        positions.add(newIndex, nodeId);
        if (newIndex > index)
            positions.remove(index);
        else
            positions.remove(index + 1);
        //write back new positions
        ps.close();
        ps = con.prepareStatement(
                "UPDATE " + TBL_PHRASE_TREE + " SET POS=? WHERE ID=? AND MANDATOR=? AND CAT=?");
        ps.setLong(3, mandatorId);
        ps.setInt(4, category);
        for (int i = 1; i <= positions.size(); i++) {
            ps.setLong(1, i);
            ps.setLong(2, positions.get(i - 1));
            ps.addBatch();
        }
        ps.executeBatch();
    } catch (SQLException exc) {
        EJBUtils.rollback(ctx);
        throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException();
    } finally {
        Database.closeObjects(PhraseEngineBean.class, con, ps);
    }
}